Click or drag to resize

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.

Import a CSV File
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.

Numbers, Dates and Text

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;
}
Export a Worksheet to CSV
using System.Text;

// the first worksheet (index 0) to a CSV file with ';' separated fields, saved as UTF-8
ExcelWorkbook workbook = new ExcelWorkbook("ImportFromCsv.xlsx");
workbook.SaveToCsv(0, "Employees.csv", ";", Encoding.UTF8);
workbook.Close();
See Also