export to excel sheets

Posted by Oswaldlily under ASP.NET on 10/3/2011 | Points: 10 | Views : 1635 | Status : [Member] | Replies : 1
I used following method to export to excel..
But I cant view different sheets..
All my 3 table displayin in single sheets..
Qn 1)how to separate to 3 sheets..
Qn 2)If i use this method even i cant rename my sheets

Response.Clear()
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "application/vnd.ms-excel"
Response.Buffer = False
Response.Charset = ""




Responses

Posted by: Reena_jainy2k1 on: 10/4/2011 [Member] Starter | Points: 25

Up
0
Down
Response object is available in web application and its used to get the streams as per content types, so your coding is saying that only that it can not find object called Response, you will have to deal with some reflection as well to get the excel component and than you can see your gridview data in excel, you can decide if you want header of your grid or not by passing flag to your function, just see the function below

private void ExportDataToExcel(DataGridView datagridview, bool IsShowIsCaptions)
{
object objMainApp;
object objWorkBook;
object objWorkBooks;
object objWorkSheets;
object objWorkSheet;
object objCellRange;
object[] Parameters;
string[] headers = new string[datagridview.ColumnCount - 1];
string[] columns = new string[datagridview.ColumnCount - 1];

int i = 0;
int c = 0;
for (c = 0; c < datagridview.ColumnCount - 1; c++)
{
headers[c] = datagridview.Rows[0].Cells[c].OwningColumn.Name.ToString();
columns[c] = Convert.ToString((char)i);
}
try
{
Type objClassType;
objClassType = Type.GetTypeFromProgID("Excel.Application");
objMainApp = Activator.CreateInstance(objClassType);

objWorkBooks = objMainApp.GetType().InvokeMember("Workbooks",
BindingFlags.GetProperty, null, objMainApp, null);

objWorkBook = objWorkBooks.GetType().InvokeMember("Add",
BindingFlags.InvokeMethod, null, objWorkBooks, null);

objWorkSheets = objWorkBook.GetType().InvokeMember("Worksheets",
BindingFlags.GetProperty, null, objWorkBook, null);

Parameters = new Object[1];
Parameters[0] = 1;
objWorkSheet = objWorkSheets.GetType().InvokeMember("Item",
BindingFlags.GetProperty, null, objWorkSheets, Parameters);

if (IsShowIsCaptions)
{
for (c = 0; c < datagridview.ColumnCount - 1; c++)
{
Parameters = new Object[2];
Parameters[0] = columns[c] + "1";
Parameters[1] = Missing.Value;
objCellRange = objWorkSheet.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, objWorkSheet, Parameters);

Parameters = new Object[1];
Parameters[0] = headers[c];
objCellRange.GetType().InvokeMember("Value", BindingFlags.SetProperty,null, objCellRange, Parameters);
}
}
for (i = 0; i < datagridview.RowCount; i++)
{
for (c = 0; c < datagridview.ColumnCount - 1; c++)
{
Parameters = new Object[2];
Parameters[0] = columns[c] + Convert.ToString(i + 2);
Parameters[1] = Missing.Value;
objCellRange = objWorkSheet.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, objWorkSheet, Parameters);

Parameters = new Object[1];
Parameters[0] = datagridview.Rows[i].Cells[headers[c]].Value.ToString();
objCellRange.GetType().InvokeMember("Value", BindingFlags.SetProperty,
null, objCellRange, Parameters);
}
}
Parameters = new Object[1];
Parameters[0] = true;
objMainApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty,
null, objMainApp, Parameters);
objMainApp.GetType().InvokeMember("UserControl", BindingFlags.SetProperty,
null, objMainApp, Parameters);
}
catch (Exception theException)
{
MessageBox.Show(theException.Message, "Error");
}
}

just try it at your end, it may be possible that some columns are hidden and you do not want export them so that part you will need to customize as per your need

let me know

Reena Jain
Project Manager

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response