// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
{
int i, j;
object missing = Type.Missing;
Excel.ApplicationClass excellApp;
excellApp = new Excel.ApplicationClass();
excellApp.Application.Workbooks.Add(true);
try
{
// Add columns name to excel file
for (i = 0; i < dtGridView.Columns.Count; i++)
{
excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
}
for (i = 0; i < dtGridView.Rows.Count; i++)
{
for (j = 0; j < dtGridView.Columns.Count; j++)
{
excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
}
}
//excellApp.Save(("Loinhuan.xls");
Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
worksheet.Activate();
worksheet.Name = sheetName;
worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
excellApp.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}