public static void Export(DataTable dataTableExport)
{
if (dataTableExport != null)
{
//SaveFileDialog saveFileDialogMain = new SaveFileDialog();
//saveFileDialogMain.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
//saveFileDialogMain.FilterIndex = 1;
//saveFileDialogMain.RestoreDirectory = true;
//saveFileDialogMain.OverwritePrompt = true;
//if (saveFileDialogMain.ShowDialog() == DialogResult.OK)
//{
////The following run very fast, but does not smoothly, sometime it raise error with some case of data (Ex. x0020 ....)
////RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");
////objExport.ExportDetails(dataTableExport, RKLib.ExportData.Export.ExportFormat.Excel, saveFileDialogMain.FileName);
DataHelpers.ExportToExcel(dataTableExport, "ExportData");
//}
//saveFileDialogMain.Dispose();
}
}
public static void Export <T>(List <T> list)
{
DataTable dataTable = ListToDataTable(list);
DataHelpers.Export(dataTable);
}
private static void ExportToExcel(DataTable dataTableExport, string workSheetName)
{
OfficeExcel.Application excelApplication = null;// Declare variables that hold references to excel objects
OfficeExcel.Workbook excelWorkBook = null;
OfficeExcel.Worksheet targetSheet = null;
try
{
if (dataTableExport.Rows.Count <= 0)
{
throw new Exception("There is no data to show.");
}
object[,] arr = new object[dataTableExport.Rows.Count, dataTableExport.Columns.Count]; bool boolValue;
for (int rowIndex = 0; rowIndex < dataTableExport.Rows.Count; rowIndex++)
{
for (int columnIndex = 0; columnIndex < dataTableExport.Columns.Count; columnIndex++)
{
if (dataTableExport.Columns[columnIndex].DataType == typeof(System.Boolean))
{
if (bool.TryParse(dataTableExport.Rows[rowIndex][columnIndex].ToString(), out boolValue))
{
arr[rowIndex, columnIndex] = (boolValue == true ? 1 : 0);
}
else //NULL
{
arr[rowIndex, columnIndex] = 0;
}
}
else
{
arr[rowIndex, columnIndex] = dataTableExport.Rows[rowIndex][columnIndex];
}
}
}
excelApplication = new OfficeExcel.Application(); // Create an instance of Excel
excelWorkBook = excelApplication.Workbooks.Add(OfficeExcel.XlWBATemplate.xlWBATWorksheet); //Create a workbook and add a worksheet.
targetSheet = (OfficeExcel.Worksheet)(excelWorkBook.Worksheets[1]);
targetSheet.Name = workSheetName;
for (int columnIndex = 0; columnIndex < dataTableExport.Columns.Count; columnIndex++)
{
DataHelpers.SetCellValue(targetSheet, DataHelpers.GetExcelColumnName(columnIndex + 1) + "1", "'" + dataTableExport.Columns[columnIndex].ColumnName);
}
var startCell = (OfficeExcel.Range)targetSheet.Cells[2, 1];
var endCell = (OfficeExcel.Range)targetSheet.Cells[dataTableExport.Rows.Count + 1, dataTableExport.Columns.Count];
var writeRange = targetSheet.Range[startCell, endCell];
writeRange.Value2 = arr;
string shortDatePattern = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
for (int columnIndex = 0; columnIndex < dataTableExport.Columns.Count; columnIndex++)
{
if (dataTableExport.Columns[columnIndex].DataType == typeof(System.DateTime) || dataTableExport.Columns[columnIndex].DataType == typeof(System.Double) || dataTableExport.Columns[columnIndex].DataType == typeof(System.Boolean))
{
((OfficeExcel.Range)targetSheet.Cells[2, columnIndex + 1]).EntireColumn.NumberFormat = dataTableExport.Columns[columnIndex].DataType == typeof(System.DateTime) ? shortDatePattern : "#,###";
}
}
targetSheet.get_Range("A1", DataHelpers.GetExcelColumnName(dataTableExport.Columns.Count) + (dataTableExport.Rows.Count + 1).ToString()).Columns.AutoFit();
targetSheet.get_Range("A1", DataHelpers.GetExcelColumnName(dataTableExport.Columns.Count) + "1").RowHeight = targetSheet.get_Range("A1", DataHelpers.GetExcelColumnName(dataTableExport.Columns.Count) + "1").RowHeight + 8;
targetSheet.get_Range("A1", DataHelpers.GetExcelColumnName(dataTableExport.Columns.Count) + "1").VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
excelApplication.Visible = true;
}
catch (Exception exception)
{
throw exception;
}
finally
{
targetSheet = null;// Release the references to the Excel objects.
if (excelWorkBook != null)
{
excelWorkBook = null; // Release the Workbook object.
}
//// Release the ApplicationClass object.
//if (excelApplication != null)
//{
// excelApplication.Quit();
// excelApplication = null;
//}//Le Minh Hiep
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
