/// <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");
}