Importing Data from CSV into Excel with ExpertXls Library for .NET |
The static LoadFromCsv method creates a workbook from a CSV file or stream. You give the text encoding of the CSV document, the field separator and the row and column of the worksheet where the first CSV field goes. The SaveToCsv method saves a worksheet to a CSV file or stream.
using System.Text; ExcelWorkbook workbook; using (FileStream csvStream = new FileStream(csvFile, FileMode.Open, FileAccess.Read)) { // windows-1252 text, comma separated fields, the first field goes to row 5, column 1 workbook = ExcelWorkbook.LoadFromCsv(csvStream, Encoding.GetEncoding(1252), ",", 5, 1, ExcelWorkbookFormat.Xlsx_2007, null); } ExcelWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "Imported from CSV"; // the first CSV row has the column names: make it a header that stays visible when scrolling int lastColumn = worksheet.UsedRange.RightColumnIndex; worksheet[5, 1, 5, lastColumn].Style.Font.Bold = true; worksheet["A6"].FreezePanes(); worksheet.AutofitColumns(); workbook.Save("ImportFromCsv.xlsx"); workbook.Close();
On .NET Core and .NET 5 or later, code page encodings such as windows-1252 need the code pages encoding provider; see Installation.
The CSV fields that are numbers, dates, booleans or percentages become number, date and boolean cells. They are parsed the same way as a string assigned to the Value property, with the number and date formats of the current culture. A field made only of digits and starting with a zero, like 00123, stays text. To import every field as text, set the ParseCSVStrings property of the default settings to false (it is true by default).
using System.Text; // all the fields as text, for example for product codes and identifiers that must keep their exact form ExcelWorkbookDefaultSettings settings = new ExcelWorkbookDefaultSettings(); settings.ParseCSVStrings = false; ExcelWorkbook workbook = ExcelWorkbook.LoadFromCsv(csvFile, Encoding.UTF8, ",", 1, 1, ExcelWorkbookFormat.Xlsx_2007, settings);
When the CSV file uses the number and date formats of a culture other than the culture of the application, set the culture of the thread for the import:
using System.Globalization; using System.Text; using System.Threading; // a CSV file with US numbers and dates (1,234.50 and 5/15/1972), imported on a computer with any culture CultureInfo threadCulture = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US"); ExcelWorkbook workbook; try { workbook = ExcelWorkbook.LoadFromCsv(csvFile, Encoding.UTF8, ",", 1, 1, ExcelWorkbookFormat.Xlsx_2007, new ExcelWorkbookDefaultSettings()); } finally { Thread.CurrentThread.CurrentCulture = threadCulture; }