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