Import and Export CSV Files

DsExcel .NET allows you to import data from CSV files into a workbook and export a workbook or worksheet to a CSV file. Use the CsvOpenOptions and CsvSaveOptions classes to configure how CSV data is read and written.

Import and Export Options

CSV Import Options

You can open a CSV file with the default settings or use CsvOpenOptions to control how its values are imported.

The following code opens CSV files with default and customized settings.

// Open a CSV file with the default settings.
Workbook workbook = new Workbook();
workbook.Open("test.csv", OpenFileFormat.Csv);

// Open a CSV file with customized settings.
Workbook workbookWithOptions = new Workbook();

var openOptions = new CsvOpenOptions
{
    ConvertNumericData = false,
    ConvertDateTimeData = false,
    ParseStyle = false
};

workbookWithOptions.Open("test.csv", openOptions);

CSV Export Options

You can export an entire workbook or a specific worksheet to a CSV file.

The following code exports a workbook and a worksheet with the default settings.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

// Export the workbook.
workbook.Save("workbook.csv", SaveFileFormat.Csv);

// Export a specific worksheet.
worksheet.Save("worksheet.csv", SaveFileFormat.Csv);

The following code exports a CSV file using CsvSaveOptions to customize the exported CSV content.

// Create a workbook.
Workbook workbook = new Workbook();

// Configure CSV export options.
var saveOptions = new CsvSaveOptions
{
    ValueQuoteType = ValueQuoteType.Always,
    TrimLeadingBlankRowAndColumn = true
};

// Export the workbook with the specified options.
workbook.Save("export.csv", saveOptions);

Import with a Custom Parser

DsExcel .NET allows you to import CSV files using custom parsing rules to get results in a specific format. For instance, a cell with numeric type data is automatically parsed as a numeric cell. However, in some cases you want to load it as a string. In such cases, you can use this feature to define your own rules when you do not get the desired result with default parser settings of DsExcel.

You can import CSV files with customized parsing rules by implementing ICsvParser interface to define custom parsing rules using the Parse method. The method accepts objects of CsvParseResult and CsvParseContext class as parameters. As CsvParseResult class represents the parsed text, you can pass the result generated by custom parsing rules to the CsvParseResult object and specify the location and text information of the target cell through the CsvParseContext class. Once the ICsvParser interface is implemented, pass it to Parser property of the csvOpenOptions class to get the expected results on importing the CSV file.

The following code preserves values beginning with 00 as text.

// Create a new workbook.
Workbook workbook = new Workbook();

var openOptions = new CsvOpenOptions
{
    Parser = new CustomParser()
};

// Open csv file with option.
workbook.Open("test.csv", openOptions);

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

Prevent CSV Formula Injection

When a CSV file is opened in a spreadsheet application, a value beginning with a formula-like character can be interpreted as a formula instead of plain text. This behavior can introduce CSV formula injection risks when the exported values contain untrusted or user-generated data.

DsExcel .NET provides the CsvSaveOptions.EscapeFormulaLikeValues property to specify whether an apostrophe(') is prefixed to formula-like values when exporting CSV files. The default value is false.

When this property is set to true, DsExcel .NET checks the first character of each exported value. If the first character is one of the following, an apostrophe is prefixed to the value:

• Equal sign (=)

• Plus sign (+)

• Minus sign (-)

• At sign (@)

• Full-width variants of these characters

• Tab(\t)

• Carriage return(\r)

• Line feed(\n)

The following code exports formula-like values as text.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];

worksheet.Range["A1"].Value = "=1+1";
worksheet.Range["A2"].Value = "+SUM(1,1)";

// Save csv file with option.
var saveOptions = new CsvSaveOptions
{
    EscapeFormulaLikeValues = true,
    ValueQuoteType = ValueQuoteType.Always
};

workbook.Save("export.csv", saveOptions);

The value =1+1 is changed to '=1+1 before it is quoted and written to the CSV file. With ValueQuoteType.Always, the exported field is written as "'=1+1" instead of "=1+1".

Configure Custom Delimiters

DsExcel .NET allows users to open and save CSV files with custom delimiters for rows, cells and columns. You can use any custom character of your choice as a delimiter. For instance - Comma (,) , Semicolon (;) , Quotes ( ", ' ) , Braces ( (), {} ), pipes ( | ), slashes (/ ), Carat ( ^ ), Pipe ( | ), Tab ( t ) etc.

You can use the ColumnSeparator, RowSeparator, and CellSeparator properties of the CsvOpenOptions Class and CsvSaveOptions Class to import and export the following three types of custom delimiters in CSV files.

1. Column Delimiters - These are the delimiters that separate the columns of a worksheet. By default, a column delimiter is of string type.

2. Row Delimiters - These are the delimiters that separate the rows of a worksheet. By default, a row delimiter is of string type.

3. Cell Delimiters - These are the delimiters that separate the cells of a worksheet. By default, the cell delimiter is of char type.

The following code imports a CSV file with customized separator settings.

// Create a new workbook.
Workbook workbook = new Workbook();

// Open csv file with option.
var openOptions = new CsvOpenOptions
{
    ColumnSeparator = ",",
    RowSeparator = "\r\n",
    CellSeparator = '"'
};

workbook.Open("test.csv", openOptions);

The following code exports worksheet data with customized separator settings.

// Create a new workbook.
Workbook workbook = new Workbook();
        
// Fetch default worksheet .
IWorksheet worksheet = workbook.Worksheets[0];

object[,] data = new object[,]{
 {"Name", "City", "Birthday", "Sex", "Weight", "Height"},
 {"Bob", "NewYork", new DateTime(1968, 6, 8), "male", 80, 180},
 {"Betty", "NewYork", new DateTime(1972, 7, 3), "female", 72, 168},
 {"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179},
 {"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171},
 {"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161},
 { "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180}};

// Set data.
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 20;

// Set ColumnSeparator/ RowSeparator & CellOperator in Save CSV options.
var saveOption = new CsvSaveOptions();
saveOption.ColumnSeparator = ",";
saveOption.RowSeparator = "\r\n";
saveOption.CellSeparator = '"';

// Save workbook to a CSV file.
workbook.Save(@"SaveCSVDelimiterRowColumnCell.csv", saveOption);