How To Write Data To An Excel Sheet in C# and how to download Excel sheets

You can use following method to write data into an excel sheet effetctively.

The accepts a dataset to be written to the excel sheet.Then saves it in the place that you specify.


using Microsoft.Office.Interop.Excel;
using System.IO;


public void ExportToExcel(DataSet dataSet, string outputPath)
{
// Create the Excel Application object
ApplicationClass excelApp = new ApplicationClass();
// Create a new Excel Workbook
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

int sheetIndex = 0;

// Copy each DataTable as a new Sheet
try
{
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);

excelSheet.Name = dt.TableName;

// Copy the column names (cell-by-cell)
for (int col = 0; col < dt.Columns.Count; col++)
{
try
{
excelSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName;
}
catch {
Response.Write("1");
}
}

((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;

// Copy the values (cell-by-cell)
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
try
{
//string temp = dt.Rows[row].ItemArray[col].ToString();
excelSheet.Cells[row + 2, col + 1] = dt.Rows[row].ItemArray[col];
}
catch {//Response.Write("2");
}
}
}

}
}
catch { //Response.Write("3");
}
// Save and Close the Workbook
try
{
excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
//excelWorkbook

excelWorkbook = null;

// Release the Application object
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch {
}
// Collect the unreferenced objects


}



You can use execute the above method and download it as follows


string cellByCellFilePath = Server.MapPath("~/ExcelSheets/") + "DeleteDetails.xls";


// Get the DataSet

ds = (DataSet)Session["DataSet"];

File.Delete(cellByCellFilePath);

ExportToExcel(ds, cellByCellFilePath);

Response.ContentType = "application/vnd.ms-excel";

string FilePath = MapPath("~/ExcelSheets/") + "DeleteDetails.xls";

Response.TransmitFile(FilePath);

Response.End();