Import and Export CSV File

DsExcel Java 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

Note: When importing and exporting CSV files, the setSeparatorString method is obsolete. Use setColumnSeparator, setRowSeparator, and setCellSeparator instead.

CSV Import Options

Class Name	Method Name	Description
CsvOpenOptions	setConvertNumericData	This method can be used to set a value that indicates whether the string in text file is converted to numeric data.
	setConvertDateTimeData	This method can be used to set a value that indicates whether the string in text file is converted to date data.
	setEncoding	This method can be used to set the default encoding which is UTF-8.
	setParseStyle	This method can be used to specify whether the style for parsed values should be applied while converting the string values to number or date time.
	setHasFormula	This method can be used to specify whether the text is formula if it starts with "=".
	setCellSeparator	This method can be used to set the cell delimiter while opening CSV files.
	setRowSeparator	This method can be used to set the row delimiter while opening CSV files.
	setColumnSeparator	This method can be used to set the column delimiter while opening CSV files.
	setParser	This method can be used to set a custom parser used when opening CSV content.

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

CsvOpenOptions openOptions = new CsvOpenOptions();
openOptions.setConvertNumericData(false);
openOptions.setConvertDateTimeData(false);
openOptions.setParseStyle(false);

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

CSV Export Options

Class Name	Method Name	Description
CsvSaveOptions	setEncoding	This method can be used to specify the default encoding which is UTF-8.
	setValueQuoteType	This method can be used to set how to quote values in the exported text file. Note: DsExcel ignores this method when setQuoteColumns method is set.
	setTrimLeadingBlankRowAndColumn	This method can be used to specify whether the leading blank rows and columns should be trimmed like in Excel.
	setQuoteColumns	This method can be used to specify which column values will be in quotes while the values in the remaining columns are not. The column number starts at 0, and specifying an invalid column number has no effect. The column values in quotes are indicated by setCellSeparator method, which is typically set to double quote (") by default. Note: If the value contains special characters such as quotes or separators, it will be in quotes always.
	setCellSeparator	This method can be used to set the cell delimiter while saving CSV files.
	setColumnSeparator	This method can be used to set the column delimiter while saving CSV files.
	setEscapeFormulaLikeValues	This method can be used to set whether CSV values that begin with formula-like characters are escaped during export.
	setRowSeparator	This method can be used to set the row delimiter while saving CSV files.

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.getWorksheets().get(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.
CsvSaveOptions saveOptions = new CsvSaveOptions();
saveOptions.setValueQuoteType(ValueQuoteType.Always);
saveOptions.setTrimLeadingBlankRowAndColumn(true);

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

Import with a Custom Parser

DsExcel Java 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 as the argument of the setParser method of the CsvOpenOptions class to get the expected results on importing the CSV file.

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

// Create CsvOpenOptions and custom parser rules.
CsvOpenOptions csvOpenOptions = new CsvOpenOptions();
csvOpenOptions.setParser(new CustomParser());

// Open csv file with option.
workbook.open(fileStream, csvOpenOptions);

public class CustomParser implements ICsvParser {
    @Override
    public void Parse(CsvParseResult csvParseResult, CsvParseContext csvParseContext) {
        if (csvParseContext.getText().startsWith("00")) {
            csvParseResult.setValue(csvParseContext.getText());
        }
        else if(csvParseResult.getNumberFormat().equals("m/d/yyyy h:mm")){
            csvParseResult.setNumberFormat("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 Java provides the CsvSaveOptions.setEscapeFormulaLikeValues method to specify whether an apostrophe(') is prefixed to formula-like values when exporting CSV files. The default value is false.

When this method is set to true, DsExcel Java 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.getWorksheets().get(0);

worksheet.getRange("A1").setValue("=1+1");
worksheet.getRange("A2").setValue("+SUM(1,1)");

// Configure CSV export options.
CsvSaveOptions saveOptions = new CsvSaveOptions();
saveOptions.setEscapeFormulaLikeValues(true);
saveOptions.setValueQuoteType(ValueQuoteType.Always);

// Save the CSV file.
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".

Note: setEscapeFormulaLikeValues does not change the behavior of ValueQuoteType or getQuoteColumns. It sanitizes exported field text only when a dangerous leading character appears. The sanitized text then passes through the existing quoting logic.

Configure Custom Delimiters

DsExcel Java 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 method of the CsvOpenOptions Class and CsvSaveOptions Class to import and export the following three types of custom delimiters in CSV files.

Column Delimiters - These are the delimiters that separate the columns of a worksheet. By default, a column delimiter is of string type.
Row Delimiters - These are the delimiters that separate the rows of a worksheet. By default, a row delimiter is of string type.
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();
        
// Setting ColumnSeparator, RowSeparator & CellOperator in CsvOpenOptions
CsvOpenOptions openOption = new CsvOpenOptions();
openOption.setColumnSeparator(",");
openOption.setRowSeparator("\r\n");
openOption.setCellSeparator('"');

// Opening csv in workbook
workbook.open("test.csv", openOption);

// Saving workbook to csv
workbook.save("OpenCSVDelimeterRowColumnCell.csv");

The following code exports worksheet data with customized separator settings.

// Create a new workbook.
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
Object data = new Object[][] { 
{ "Name", "City", "Birthday", "Sex", "Weight", "Height" },
{ "Bob", "NewYork", new GregorianCalendar(1968, 6, 8), "male", 80, 180 },
{ "Betty", "NewYork", new GregorianCalendar(1972, 7, 3), "female", 72, 168 },
{ "Gary", "NewYork", new GregorianCalendar(1964, 3, 2), "male", 71, 179 },
{ "Hunk", "Washington", new GregorianCalendar(1972, 8, 8), "male", 80, 171 },
{ "Cherry", "Washington", new GregorianCalendar(1986, 2, 2), "female", 58, 161 },
{ "Eva", "Washington", new GregorianCalendar(1993, 2, 5), "female", 71, 180 } };

// Set data
worksheet.getRange("A1:F7").setValue(data);
worksheet.getRange("A:F").setColumnWidth(20);

// Setting ColumnSeparator/ RowSeparator & CellOperator in CSVSaveOptions
CsvSaveOptions saveOption = new CsvSaveOptions();
saveOption.setColumnSeparator(",");
saveOption.setRowSeparator("\r\n");
saveOption.setCellSeparator('"');

// Saving workbook to csv
workbook.save("SaveCSVDelimiter.csv", saveOption);