static void Main(string[] args)
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Preserve data types as per the value
application.PreserveCSVDataTypes = true;
//Read the CSV file
Stream csvStream = File.OpenRead(Path.GetFullPath(@"../../../TemplateSales.csv"));;
//Reads CSV stream as a workbook
IWorkbook workbook = application.Workbooks.Open(csvStream);
IWorksheet sheet = workbook.Worksheets[0];
//Formatting the CSV data as a Table
IListObject table = sheet.ListObjects.Create("SalesTable", sheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium6;
IRange location = table.Location;
location.AutofitColumns();
//Apply the proper latitude & longitude numerformat in the table
TryAndUpdateGeoLocation(table, "Latitude");
TryAndUpdateGeoLocation(table, "Longitude");
//Apply currency numberformat in the table column 'Price'
IRange columnRange = GetListObjectColumnRange(table, "Price");
if (columnRange != null)
{
columnRange.CellStyle.NumberFormat = "$#,##0.00";
}
//Apply Date time numberformat in the table column 'Transaction_date'
columnRange = GetListObjectColumnRange(table, "Transaction_date");
if (columnRange != null)
{
columnRange.CellStyle.NumberFormat = "m/d/yy h:mm AM/PM;@";
}
//Sort the data based on 'Products'
IDataSort sorter = table.AutoFilters.DataSorter;
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
sorter.Sort();
//Save the file in the given path
Stream excelStream;
excelStream = File.Create(Path.GetFullPath(@"../../../Output.xlsx"));
workbook.SaveAs(excelStream);
excelStream.Dispose();
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected Exception:" + e.Message);
}
}
private void ConvertToXlsx()
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Preserve data types as per the value
application.PreserveCSVDataTypes = true;
//Read my CSV file
Stream csvStream = File.OpenRead(Path.GetFullPath(saveFile.FileName));
//Reads CSV stream as a workbook
IWorkbook workbook = application.Workbooks.Open(csvStream);
IWorksheet sheet = workbook.Worksheets[0];
// Formatting CSV data as a table
IListObject table = sheet.ListObjects.Create("PortsTable", sheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleDark10;
IRange location = table.Location;
location.AutofitColumns();
// Define/apply header style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Syncfusion.Drawing.Color.LightGreen;
headerStyle.Font.FontName = "Consolas";
headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.Dark_blue;
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 16;
headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
headerStyle.EndUpdate();
// Define and apply a body style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
bodyStyle.Font.FontName = "Cambria";
bodyStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.BlackCustom;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium;
bodyStyle.EndUpdate();
// Define a few additonal tweaks
IStyle increaseStyle = workbook.Styles.Add("IncreaseStyle");
increaseStyle.BeginUpdate();
increaseStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue;
increaseStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
increaseStyle.Font.Size = 14;
increaseStyle.Font.Bold = true;
// Modify this range seperately so it doesn't get messed up
sheet.Range["A1:A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A2:A2"].DateTime.ToShortDateString();
sheet.Range["A2:A2"].CellStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue;
sheet.Range["A2:A2"].CellStyle.Font.Bold = true;
sheet.Range["A2:A2"].CellStyle.Font.Size = 14;
sheet.Range["A2:A4"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A2:A2"].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A3:A4"].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A3:A3"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["B1:B100"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["B2:B3"].CellStyle.Color = Syncfusion.Drawing.Color.Black;
// Custom widths
sheet.Columns[0].ColumnWidth = 20.00;
// Then Apply header style
sheet.Range["A1:A1"].CellStyle = headerStyle;
sheet.Range["A3:A3"].CellStyle = headerStyle;
sheet.Range["B1:B1"].CellStyle = headerStyle;
// And the body style
sheet.Range["A4:A4"].CellStyle = bodyStyle;
sheet.Range["B4:B100"].CellStyle = bodyStyle;
// Additions
sheet.Range["A2:A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A4:A4"].CellStyle = increaseStyle;
sheet.Range["A4:A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
// Apply a conditional format for cells with the text 'Open'
IConditionalFormats condition = sheet.Range["B4:B100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.SpecificText;
condition1.Text = "Open";
condition1.Operator = ExcelComparisonOperator.ContainsText;
condition1.BackColor = ExcelKnownColors.Red;
// Save file in the same directory as the initial raw CSV from StreamWriter
Stream excelStream;
string makeoverPath = Environment.ExpandEnvironmentVariables(@"C:\Users\%USERNAME%\Desktop\makeover.xlsx");
excelStream = File.Create(Path.GetFullPath(makeoverPath));
workbook.SaveAs(excelStream);
// Release all resources => IMPORTANT!
excelStream.Dispose();
MessageBox.Show("SUCCESS! Your original file " + saveFile.FileName + " has been converted to .XLSX. You can view it at: " + makeoverPath);
// Flip this on so that below I can safely delete the ugly .CSV original file
prettified = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}