Trek Innovations

In order to achieve this first, you need to add a component known as Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references.

To do above follow below instructions:

Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:

a. On the Project menu, click Add Reference.

b. On the COM tab, locate Microsoft Excel 11.0 Object Library, and then click Select.

c. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click yes.

The codes of the function to export data to Excel file is like below:-

private void ExportToExcelFile(System.Data.DataTable dt)

{

Excel.Application xlsApp = new Excel.ApplicationClass();

Excel.Workbook xlsWorkbook;

Excel.Worksheet xlsWorksheet;

string strhdr;

int row;

string strFile = “file1.xls”;

string filename = Server.MapPath(strFile);

if (dt.Rows.Count > 0)

{

//Create new workbook

xlsWorkbook = xlsApp.Workbooks.Add(true);

//Get the first worksheet

xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

//Activate current worksheet

xlsWorksheet.Activate();

//Set header row to row 1

row = 1;

//Add table headers to worksheet

xlsWorksheet.Cells[row, 1] = “Enquiry Type”;

xlsWorksheet.Cells[row, 2] = “Topic Area”;

xlsWorksheet.Cells[row, 3] = “Status”;

xlsWorksheet.Cells[row, 4] = “Received Date”;

xlsWorksheet.Cells[row, 5] = “Enquiry”;

xlsWorksheet.Cells[row, 6] = “Customer Name”;

xlsWorksheet.Cells[row, 7] = “Agency location”;

xlsWorksheet.Cells[row, 8] = “Timetaken”;

//Format header row (bold, extra row height, autofit width)

xlsWorksheet.get_Range(“A” + row.ToString(), “C” + row.ToString()).Font.Bold = true;

xlsWorksheet.get_Range(“A” + row.ToString(), “C” + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;

xlsWorksheet.get_Range(“A” + row.ToString(), “C” + row.ToString()).EntireRow.AutoFit();

//Freeze the columm headers

xlsWorksheet.get_Range(“A” + (row + 1).ToString(), “C” + (row + 1).ToString()).Select();

xlsApp.ActiveWindow.FreezePanes = true;

//Write data to Excel worksheet

foreach (DataRow dr in dt.Rows)

{

row += 1;

if (dr["Enquiry_type"] != null)

xlsWorksheet.Cells[row, 1] = dr["Enquiry_type"];

if (dr["Topic_area"] != null)

xlsWorksheet.Cells[row, 2] = dr["Topic_area"];

if (dr["Status"] != null)

xlsWorksheet.Cells[row, 3] = dr["Status"];

if (dr["Entered_DateTime"] != null)

xlsWorksheet.Cells[row, 4] = dr["Entered_DateTime"];

if (dr["Enquiry_Description"] != null)

xlsWorksheet.Cells[row, 5] = dr["Enquiry_Description"];

if (dr["Firstname"] != null)

xlsWorksheet.Cells[row, 6] = dr["Firstname"];

if (dr["Agency_Location"] != null)

xlsWorksheet.Cells[row, 7] = dr["Agency_Location"];

if (dr["TimeTaken"] != null)

xlsWorksheet.Cells[row, 8] = dr["TimeTaken"];

}

//Format data rows (align to center and left, autofit width and height)

xlsWorksheet.get_Range(“A2″, “C” + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

xlsWorksheet.get_Range(“A2″, “C” + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

xlsWorksheet.get_Range(“A2″, “c” + row.ToString()).EntireColumn.AutoFit();

xlsWorksheet.get_Range(“A2″, “c” + row.ToString()).EntireRow.AutoFit();

//Make excel workbook visible to user after all data has been added to worksheet.

xlsApp.DisplayAlerts = false;

xlsWorkbook.Close(true, filename, null);

//Export data to client machine

strhdr = “attachment;filename=” + strFile;

Response.Clear();

Response.ContentType = “application/vnd.ms-excel”;

Response.ContentEncoding = System.Text.Encoding.Default;

Response.AppendHeader(“Content-Disposition”, strhdr);

Response.WriteFile(filename);

Response.Flush();

Response.Clear();

Response.Close();

}

}

Happy Coding……………………

No comments