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(); } }