ابتدا باید داده های مورد نظرتون را از بانک اطلاعاتی استخراج و در یک DataTable ذخیره کنید.
سپس روی DataTable حلقه می زنید و داده ها وارد فایل اکسل کنید.
public string Export2Excel(string saveFileName, DataTable dataTable, string reportName)
{
// Add Microsoft.Office.Interop.Excel to project references
Microsoft.Office.Interop.Excel._Application _ExcelApp;
Microsoft.Office.Interop.Excel._Workbook _WorkBook;
Microsoft.Office.Interop.Excel._Worksheet _WorkSheet = null;
string strResult = string.Empty;
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_WorkBook = _ExcelApp.Workbooks.Add(Type.Missing);
// set this to true to see the excel sheet behind the program
_ExcelApp.Visible = false;
try
{
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
_WorkSheet = _WorkBook.Sheets["Sheet1"];
_WorkSheet = _WorkBook.ActiveSheet;
// changing the name of active sheet
_WorkSheet.Name = reportName;
_WorkSheet.DisplayRightToLeft = true;
// storing header part in Excel
for (int i = 1; i < dataTable.Columns.Count + 1; i++)
{
_WorkSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
_WorkSheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j].ToString();
}
}
// save the application
_WorkBook.SaveAs(saveFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
strResult = "done";
}
catch (Exception ex)
{
strResult = ex.ToString();
}
finally
{
_WorkBook = null;
_WorkSheet = null;
_ExcelApp.Quit();
_ExcelApp = null;
}
return strResult;
}
برای خروجی گرفتن در قالب فایل csv هم از کدزیر می توانیداستفاده نمایید:
internal string ExportToSpreadsheet(DataTable dataTableT, string strfileName)
{
string strResult = string.Empty;
try
{
using (var writer = new System.IO.StreamWriter(strfileName, true))
{
//Add columns to file
foreach (DataColumn column in dataTableT.Columns)
{
writer.Write(column.ColumnName + ";");
}
//Add a line after columns to file
writer.Write(Environment.NewLine);
foreach (DataRow row in dataTableT.Rows)
{
for (int i = 0; i < dataTableT.Columns.Count; i++)
{
writer.Write(row[i].ToString().Replace(";", " ") + ";");
}
writer.Write(Environment.NewLine);
}
strResult = "done";
writer.Flush();
writer.Close();
}
}
catch (Exception exErro)
{
strResult = exErro.ToString();
}
return strResult;
}