//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); #region Define CustomParser class /*public class CustomParser : ICsvParser { public void Parse(CsvParseResult csvParseResult, CsvParseContext context) { if (context.Text.StartsWith("00")) { csvParseResult.Value = context.Text; } else if (context.Column == 5 || context.Column == 6) { csvParseResult.NumberFormat = "#.00"; } else if (csvParseResult.NumberFormat.Equals("m/d/yyyy h:mm")) { csvParseResult.NumberFormat = "m/d/yyyy"; } } }*/ #endregion Stream fileStream = this.GetResourceStream("xlsx\\CustomParseOnReadingCsvFiles.csv"); // Create CsvOpenOptions and custom parser rules. CsvOpenOptions csvOpenOptions = new CsvOpenOptions(); csvOpenOptions.Parser = new CustomParser(); // Open csv file with option. workbook.Open(fileStream, csvOpenOptions); IWorksheet worksheet = workbook.ActiveSheet; // Change column width and row height int[] columnWidths = { 15, 12, 24, 15, 25, 12, 15 }; int[] rowHeights = { 19, 17, 17, 17, 17, 17, 17, 17 }; for (int i = 0; i < columnWidths.Length; i++) { worksheet.Columns[i].ColumnWidth = columnWidths[i]; } for (int i = 0; i < rowHeights.Length; i++) { worksheet.Rows[i].RowHeight = rowHeights[i]; } worksheet.SheetView.DisplayGridlines = false; worksheet.Range["A1"].Value = "PURCHASE ORDERS"; // Set style worksheet.Range["A1:G1"].Merge(); worksheet.Range["A1"].Font.Size = 14; worksheet.Range["A1"].Font.Bold = true; worksheet.Range["A1"].HorizontalAlignment = HorizontalAlignment.Center; worksheet.Range["A2:G8"].HorizontalAlignment = HorizontalAlignment.Right; worksheet.Range["C2:C8"].HorizontalAlignment = HorizontalAlignment.Left; worksheet.Range["E2:E8"].HorizontalAlignment = HorizontalAlignment.Left; worksheet.Range["A2:G2"].Font.Size = 10; worksheet.Range["A2:G2"].Font.Bold = true; worksheet.Range["A2:G2"].Interior.Color = Color.FromArgb(255, 242, 204); worksheet.Range["A3:G8"].Font.Size = 9; worksheet.Range["A1:G8"].Font.Name = "Trebuchet MS"; worksheet.Range["A1:G8"].Borders.LineStyle = BorderLineStyle.Thin; worksheet.Range["A1:G8"].Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.None; worksheet.Range["A1:G8"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.None; worksheet.Range["A1:G8"].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.None; // Save to an excel file workbook.Save("CustomParserOnReadingCsvFiles.xlsx");
' Create a new Workbook Dim workbook As New Workbook #Region "Define CustomParser class" 'Public Class CustomParser ' Implements ICsvParser ' Public Sub Parse(csvParseResult As CsvParseResult, context As CsvParseContext) Implements ICsvParser.Parse ' If context.Text.StartsWith("00") Then ' csvParseResult.Value = context.Text ' ElseIf context.Column = 5 OrElse context.Column = 6 Then ' csvParseResult.NumberFormat = "#.00" ' ElseIf csvParseResult.NumberFormat = "m/d/yyyy h:mm" Then ' csvParseResult.NumberFormat = "m/d/yyyy" ' End If ' End Sub 'End Class #End Region Dim fileStream = GetResourceStream("xlsx\CustomParseOnReadingCsvFiles.csv") ' Create CsvOpenOptions and custom parser rules. Dim csvOpenOptions As New CsvOpenOptions With { .Parser = New CustomParser } ' Open csv file with option. workbook.Open(fileStream, csvOpenOptions) Dim worksheet As IWorksheet = workbook.ActiveSheet ' Change column width and row height Dim columnWidths() As Integer = {15, 12, 24, 15, 25, 12, 15} Dim rowHeights() As Integer = {19, 17, 17, 17, 17, 17, 17, 17} For i As Integer = 0 To columnWidths.Length - 1 worksheet.Columns(i).ColumnWidth = columnWidths(i) Next i For i As Integer = 0 To rowHeights.Length - 1 worksheet.Rows(i).RowHeight = rowHeights(i) Next i worksheet.SheetView.DisplayGridlines = False worksheet.Range("A1").Value = "PURCHASE ORDERS" ' Set style worksheet.Range("A1:G1").Merge() worksheet.Range("A1").Font.Size = 14 worksheet.Range("A1").Font.Bold = True worksheet.Range("A1").HorizontalAlignment = HorizontalAlignment.Center worksheet.Range("A2:G8").HorizontalAlignment = HorizontalAlignment.Right worksheet.Range("C2:C8").HorizontalAlignment = HorizontalAlignment.Left worksheet.Range("E2:E8").HorizontalAlignment = HorizontalAlignment.Left worksheet.Range("A2:G2").Font.Size = 10 worksheet.Range("A2:G2").Font.Bold = True worksheet.Range("A2:G2").Interior.Color = Color.FromArgb(255, 242, 204) worksheet.Range("A3:G8").Font.Size = 9 worksheet.Range("A1:G8").Font.Name = "Trebuchet MS" worksheet.Range("A1:G8").Borders.LineStyle = BorderLineStyle.Thin worksheet.Range("A1:G8").Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.None worksheet.Range("A1:G8").Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.None worksheet.Range("A1:G8").Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.None ' save to an excel file workbook.Save("CustomParserOnReadingCsvFiles.xlsx")