/// <summary> /// Same as importData(dtSource as dataTable) except that this method can be called /// without instantiating an excelexportHelper object. /// This method automatically sizes the columns, adds borders around each cell and adds /// an alternating row style. /// </summary> /// <param name="dtSource">The datatable to export</param> /// <param name="strHeader">The left header for the worsheet</param> /// <remarks></remarks> public static void ExportTable(DataTable dtSource, string strHeader = "") { // // export data of a datatable to a new XLS (use of imortData sub defined above) // Use to export the datatable in one line (example Exporttable(dtResults) // ------------------------------------------------------------------------- ExcelExportHelper xlExporter = null; System.Globalization.CultureInfo originalCulture = GetCurrentCulture(); try { if (dtSource.Rows.Count == 0) { throw new Exception("There is no row to export. Operation aborted"); } SetExcelCulture(); xlExporter = new ExcelExportHelper(); { xlExporter.SetHeader(strHeader); xlExporter.ImportData(dtSource); xlExporter.AutoFitColumns(); xlExporter.SetBorders(); xlExporter.Application.visible = true; } } catch (Exception ex) { if (xlExporter != null) { xlExporter.Close(); } throw new Exception("Excel Export Error", ex); } finally { RestoreCulture(originalCulture); } }
/// <summary> /// This method process the value into the cell of the Excel file. /// </summary> /// <param name="excelExport">New Excel File.</param> /// <param name="filePath">Path of the file</param> /// <param name="currentMonth">Current Month.</param> /// <param name="migration">Migration data.</param> private void CityProcess(ExcelExportHelper excelExport, string filePath, string currentMonth, List <MigrationElement> migration, string sales) { int excelRowCount = NomberOfRowUsed(filePath, currentMonth); excelExport.ChangeSheet(currentMonth); foreach (var item in migration) { CoreBuiness(excelExport, item, excelRowCount, sales); excelRowCount++; } string directory = Path.GetDirectoryName(filePath); string fileName = Path.GetFileNameWithoutExtension(filePath); excelExport.Save(directory + @"\tempbx.xlsx"); excelExport.Close(); excelExport.Dispose(); BackupOldExcelFile(filePath); File.Copy(directory + @"\tempbx.xlsx", directory + @"\" + fileName + ".xlsx", true); File.Delete(directory + @"\tempbx.xlsx"); }