Import and Export Excel Files

DsExcel .NET allows you to import and export Excel files in .xlsx, .xlsm, and .xltx formats. You can import or export the entire workbook, work with file streams, import data from a specific worksheet, table, or range, and use file-specific options to control which workbook elements are processed.

Use the Open method to import an Excel file and the Save method to export a workbook.

The following example imports and exports an .xlsx file by using a file name.

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

// Open an excel file.
workbook.Open(
    Path.Combine("Resource", "Basic sales report1.xlsx"),
    OpenFileFormat.Xlsx);

// Save the workbook as an .xlsx file.
workbook.Save("Exported.xlsx", SaveFileFormat.Xlsx);

You can also import and export Excel files by using streams:

// Create a file stream to open an Excel file.
using FileStream openFile = new FileStream(
    Path.Combine("Resource", "Basic sales report1.xlsx"),
    FileMode.Open,
    FileAccess.Read);

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

// Open the excel file.
workbook.Open(openFile, OpenFileFormat.Xlsx);

// Create a file stream for the exported file.
using FileStream saveFile = new FileStream(
    "Exported-Stream.xlsx",
    FileMode.Create,
    FileAccess.ReadWrite);

// Save the workbook.
workbook.Save(saveFile, SaveFileFormat.Xlsx);

Excel Import Options

When importing an Excel file, you can control which workbook elements are loaded and configure behaviors such as formula recalculation, row-height adjustment, password handling, and digital signature processing.

File Format	Class name	Property Name	Description
.xlsx .xlsm .xltx	XlsxOpenOptions XlsmOpenOptions XltxOpenOptions	DigitalSignatureOnly	Indicates whether to open the workbook in digital signature-only mode. In the digital signature-only mode, existing signatures will be preserved unless you call ISignature.Delete. But you can only sign existing signature lines, add invisible signatures, remove digital signatures of signed signature lines, or remove invisible signatures in this mode. Other changes will be discarded. After modifying digital signatures, you need to save the workbook to commit changes. True to open workbook in digital signature-only mode. Otherwise, use normal mode. The default value is false.
		DoNotAutoFitAfterOpened	Specify whether to automatically adjust the row height on opening an Excel file.
		DoNotRecalculateAfterOpened	Specify whether to recalculate the formula values once the Excel file has opened.
		ImportFlags	Provides various flags to import various aspects of a worksheet.
		InvalidFormulaHandling	Specifies how invalid formulas are handled when opening an excel file. The default value is `InvalidFormulaHandling.Throw`.
		Password	The password for the Excel file.
	OpenOptionsBase	FileFormat	Represents the format in which the workbook is opened.

Work with Import Flags

While opening a workbook, DsExcel .NET also provides you with several open options that can be used during the import operation.

The ImportFlags property accepts values from ImportFlags enumeration which allows users to import the workbook with the specified open options. These options are described in the table below.

Import Flag Option	Description
NoFlag	Refers to "No option". This option is used when you don't want to put any import flag while opening the Excel file. This means that all the data in the worksheet will be imported as it is.
Data	Refers to "Read the Data". This option is used when you want to import only the data in the worksheet while opening the Excel file.
Formulas	Refers to "Read the Data and Formulas". This option is used when you want to import both the data and the formulas in the worksheet while opening the Excel file.
Table	Refers to "Read the Tables". This option is used when you want to import only the tables in the worksheet while opening the Excel file.
MergeArea	Refers to "Read the Merge Cells". This option is used when you want to import only the merged cells or spanned cells in the worksheet while opening the Excel file.
Style	Refers to "Read the Styles". This option is used when you want to import only the styles applied to the cells in the worksheet while opening the Excel file.
ConditionalFormatting	Refers to "Read the Conditional Formatting". This option is used when you want to import only the conditional formatting rule applied to the worksheet while opening the Excel file.
DataValidation	Refers to "Read the Data Validation". This option is used when you want to import only the data validation rule applied to the worksheet while opening the Excel file.
PivotTable	Refers to "Read the Pivot Tables". This option is used when you want to import only the pivot tables in the worksheet while opening the Excel file.
Shapes	Refers to "Read all the Shapes". This option is used when you want to import only the shapes embedded in the worksheet while opening the Excel file.

Refer to the following example code in order to import multiple workbook elements.

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

// Configure options for opening the Excel file.
var options = new XlsxOpenOptions
{
    ImportFlags = ImportFlags.Data |
                  ImportFlags.Formulas |
                  ImportFlags.Style
};

// Open an excel file with import flags.
workbook.Open(@"Source.xlsx", options);

You can also use import flags when importing .xlsm and .xltx files.

Handle Invalid Formulas When Opening Excel Files

DsExcel .NET allows you to control how invalid formulas are handled when opening .xlsx, .xlsm, and .xltx files. This is useful when a workbook contains a small number of invalid formulas, but you still need to load the workbook and continue processing it.

Use the InvalidFormulaHandling property in XlsxOpenOptions, XlsmOpenOptions, or XltxOpenOptions to specify how DsExcel .NET should handle invalid formulas. For Preserve, DiscardFormulaOnly, and Discard, DsExcel .NET records information about the handled invalid formulas in the log. For more information about logging, see Logging.

Throw: Throws an exception when an invalid formula is encountered. This is the default behavior.
Discard: Discards the invalid formula and its cached value.
DiscardFormulaOnly: Discards the invalid formula and preserves its cached value.
Preserve: Preserves the original invalid formula text for roundtrip I/O. If the file contains a cached value, DsExcel .NET returns that value. Otherwise, DsExcel .NET returns #VALUE!.

The InvalidFormulaHandling setting applies not only to cell formulas, but also to formulas used in tables, data validation, conditional formatting, charts, and other formula-based features. However, it does not apply to defined names. DsExcel .NET does not parse formulas in defined names during file I/O, so defined names retain their existing behavior.

Refer to the following example code to open a workbook and preserve invalid formulas:

var workbook = new Workbook();
var options = new XlsxOpenOptions
{
    InvalidFormulaHandling = InvalidFormulaHandling.Preserve
};

workbook.Open("input.xlsx", options);

Note: Preserved invalid formulas are not recalculated by DsExcel .NET. If DoNotRecalculateAfterOpened is false, the cached value is still retained because DsExcel .NET cannot calculate an invalid formula.

Excel Export Options

To export a workbook as .xlsx file, you can use the Save method and provide various save option provided by DsExcel to specify what to skip and what to export. These options are listed below:

File Format	Class name	Property Name	Description
.xlsx .xlsm .xltx	XlsxSaveOptions XlsmSaveOptions XltxSaveOptions	ExcludeEmptyRegionCells	Exclude empty cells, that is, the cells that lie outside the used range and have styles but do not contain data.
		ExcludeUnusedNames	Exclude the unused names while exporting the file.
		ExcludeUnusedStyles	Exclude the unused styles while exporting the file.
		ExportSharedFormula	Specifies whether to export shared formulas when saving the workbook. The default value is true.
		IgnoreFormulas	Export formula cells of DsExcel worksheet as value cells in Excel.
		IncludeAutoMergedCells	Indicates whether to include the automatically merged cells. The default value is false.
		IncludeBindingSource	Indicates whether to include the binding source when saving the file. The default value is true.
		IsCompactMode	Indicates whether to save workbook in compact mode. The default value is false.
		Password	The password for the xlsx file.
	SaveOptionsBase	FileFormat	Represents the format in which the workbook is saved.

Optimize the Output File Size

A workbook may contain unused styles, unused defined names, or styled empty cells outside the used range. These items can increase the output file size.

Refer to the following example code to use ExcludeUnusedStyles, ExcludeUnusedNames, ExcludeEmptyRegionCells, and IsCompactMode to reduce the size of the exported file.

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

// Add names and styles to the workbook.
for (int i = 0; i < 10000; i++) 
{ 
  workbook.Names.Add($"name{i + 1}", $"=$A${i + 1}"); 
  workbook.Styles.Add($"style{i + 1}"); 
}  

// Exclude the unused styles, names and empty cell region.
XlsxSaveOptions option = new XlsxSaveOptions(); 
option.ExcludeUnusedStyles = true; 
option.ExcludeUnusedNames = true; 
option.ExcludeEmptyRegionCells = true; 
option.IsCompactMode = true;

// Save file with and without optimization options.
workbook.Save("test_optimized.xlsx", option); 
workbook.Save("test_no_optimized.xlsx");

Control Bound Data Export

DsExcel enables you to control whether to export the bound data source to the file when exporting to .xlsx file using IncludeBindingSource property of XlsxSaveOptions.

Refer to the following example code to exclude the binding source when exporting to .xlsx file:

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

// Define a JSON data source.
var dataSource = "{ \"ds\":" +
                 "[\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Chicago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"New York\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" +
                 "   {\"Area\": \"South America\",\"City\": \"Santiago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 19550},\n" +
                 "   {\"Area\": \"Europe\",\"City\": \"Berlin\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 30000},\n" +
                 "   {\"Area\": \"Asia\",\"City\": \"Tokyo\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 45000},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Los Angeles\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 60000},\n" +
                 "   {\"Area\": \"Europe\",\"City\": \"Paris\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 55000},\n" +
                 "   {\"Area\": \"Asia\",\"City\": \"Seoul\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 40000},\n" +
                 "   {\"Area\": \"South America\",\"City\": \"Buenos Aires\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 35000},\n" +
                 "   {\"Area\": \"North America\",\"City\": \"Toronto\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 50000}\n" +
                 " ]" +
                 "}";

// Add data source to worksheet.
var dataSourceSheet = workbook.Worksheets.Add();
dataSourceSheet.Name = "DataSource";
var table = dataSourceSheet.Tables.Add(dataSourceSheet.Range["A1:E4"], true);

// Set binding path.
table.BindingPath = "ds";
table.Columns[0].DataField = "Area";
table.Columns[1].DataField = "City";
table.Columns[2].DataField = "Category";
table.Columns[3].DataField = "Name";
table.Columns[4].DataField = "Revenue";

// Set data source.
dataSourceSheet.DataSource = new JsonDataSource(dataSource);

// Create pivot table sheet.
var pivotSheet = workbook.Worksheets[0];
pivotSheet.Name = "PivotSheet";

// Create pivot table.
var pivotCache = workbook.PivotCaches.Create(table);
var pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range["A1"], "pivottable1");

// Configure pivot table fields.
var fieldArea = pivotTable.PivotFields["Area"];
fieldArea.Orientation = PivotFieldOrientation.RowField;
var fieldCity = pivotTable.PivotFields["City"];
fieldCity.Orientation = PivotFieldOrientation.RowField;
var fieldName = pivotTable.PivotFields["Name"];
fieldName.Orientation = PivotFieldOrientation.ColumnField;
var fieldRevenue = pivotTable.PivotFields["Revenue"];
fieldRevenue.Orientation = PivotFieldOrientation.DataField;
pivotSheet.UsedRange.AutoFit();
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
pivotTable.Refresh();

var saveOptions = new XlsxSaveOptions();

// Set IncludeBindingSource property to false to exclude the binding source from being exported.
saveOptions.IncludeBindingSource = false;

// Save the workbook.
workbook.Save("IncludeBindingSourceOption.xlsx", saveOptions);

Note: IncludeBindingSource property will not revert the table to its original size after the DataBinding has changed its size. This property only controls whether the data is exported.

Shared Formula Export Options

When exporting to .xlsx, DsExcel .NET enables the shared formula feature by default, similar to Microsoft Excel, in order to reduce file size. If you need to use the exported file with third-party libraries that do not support shared formulas, you can disable this feature by setting the XlsxSaveOptions.ExportSharedFormula property to false. Note that disabling shared formulas will increase the size of the exported .xlsx file.

The following example demonstrates how to disable shared formulas when exporting an .xlsx file:

// Create a new workbook.
var workbook = new Workbook();
var sheet = workbook.ActiveSheet;

// Set the formula for the range B1:B5.
sheet.Range["B1:B5"].Formula = "IF(A1>100,SUM(A1:A5)*AVERAGE(A1:A5),MAX(A1:A5)-MIN(A1:A5))";

// Disable shared formulas.
var saveOptions = new XlsxSaveOptions();
saveOptions.ExportSharedFormula = false;

// Save the Excel file.
workbook.Save("NoSharedFormula.xlsx", saveOptions);

In Office Open XML (OOXML) .xlsx files, formulas are stored as shared or individual formulas depending on whether XlsxSaveOptions.ExportSharedFormula is set to true or false, as shown in the following examples.

Shared Formula Enabled

<sheetData>
    <row r="1">
        <c r="B1">
            <f t="shared" si="0" ref="B1:B5">IF(A1&gt;100,SUM(A1:A5)*AVERAGE(A1:A5),MAX(A1:A5)-MIN(A1:A5))</f>
        </c>
    </row>
    <row r="2">
        <c r="B2">
            <f t="shared" si="0"/>
        </c>
    </row>
    <row r="3">
        <c r="B3">
            <f t="shared" si="0"/>
        </c>
    </row>
    <row r="4">
        <c r="B4">
            <f t="shared" si="0"/>
        </c>
    </row>
    <row r="5">
        <c r="B5">
            <f t="shared" si="0"/>
        </c>
    </row>
</sheetData>

Shared Formula Disabled

<sheetData>
    <row r="1">
        <c r="B1">
            <f>IF(A1&gt;100,SUM(A1:A5)*AVERAGE(A1:A5),MAX(A1:A5)-MIN(A1:A5))</f>
        </c>
    </row>
    <row r="2">
        <c r="B2">
            <f>IF(A2&gt;100,SUM(A2:A6)*AVERAGE(A2:A6),MAX(A2:A6)-MIN(A2:A6))</f>
        </c>
    </row>
    <row r="3">
        <c r="B3">
            <f>IF(A3&gt;100,SUM(A3:A7)*AVERAGE(A3:A7),MAX(A3:A7)-MIN(A3:A7))</f>
        </c>
    </row>
    <row r="4">
        <c r="B4">
            <f>IF(A4&gt;100,SUM(A4:A8)*AVERAGE(A4:A8),MAX(A4:A8)-MIN(A4:A8))</f>
        </c>
    </row>
    <row r="5">
        <c r="B5">
            <f>IF(A5&gt;100,SUM(A5:A9)*AVERAGE(A5:A9),MAX(A5:A9)-MIN(A5:A9))</f>
        </c>
    </row>
</sheetData>

Import Data Only

To import only data from a specified worksheet or a cell range, DsExcel.NET provides ImportData method which simply opens the worksheet and fetches the data for you. This method is useful in scenarios where only data is required and you do not need to deal with rest of the object model. The ImportData method uses name of the file or filestream and source name as main parameters. You can specify name of a worksheet, table or a range as the source of data. To fetch names of sheets and tables used in a file or file stream, the Workbook class provides GetNames method which returns an array of possible source names.

// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

// Open an excel file.
var fileStream = GetResourceStream("xlsx\\AgingReport.xlsx");

// Get the possible import names in the file.
// The names[0] and names[1] are sheet names: "Aging Report", "Invoices".
// The names[2] and names[3] are table names: "'Aging Report'!tblAging", "Invoices!tblInvoices".
var names = GrapeCity.Documents.Excel.Workbook.GetNames(fileStream);

// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
var data = GrapeCity.Documents.Excel.Workbook.ImportData(fileStream, names[2]);

// Assign the data to current workbook.
workbook.Worksheets[0].Range[0, 0, data.GetLength(0), data.GetLength(1)].Value = data;
        
// Save to an excel file
workbook.Save("importdatafortable.xlsx");

While working with heavy files having multiple sheets, or many formulas, you may optimize the load performance by using ImportData method as it reads only data. The method also provides overloads where you can specify the range of target cells and can read that particular part only, even if your file contains huge amounts of data.

Limitation

Formula are not taken into consideration while using ImportData method, as CalcEngine does not work in such case. Hence, the cell value is set to null. In case a formula has cached value stored in the file, DsExcel returns that value.
If the worksheet name contains character !, such as "Sheet!1", the worksheetName cannot be parsed by calling ImportData(worksheetName), and this function returns null.

Note: In version v5, name of the parameter of ImportData method has been changed from worksheetName to sourceName. This has resulted into a breaking change for users using the prior version if their code used parameter name "worksheetName" while calling the ImportData method, For details, see Release Notes.

Preserve Japanese Ruby Characters

DsExcel .NET preserves Japanese Ruby characters when importing and exporting Excel files. Ruby characters are also preserved after operations such as Insert, Delete, Copy, Cut, Merge, Clear, and Sort.

Current Limitations

DsExcel .NET does not support importing xls files.
DsExcel .NET does not support exporting xlsx files to XPS format.
The following features are not supported by DsExcel .NET. If you import an xlsx file containing any of these features and then export it again, those features will not be preserved in the exported xlsx file.
- Features resulting in object loss
  - SmartArt
  - 3D Models
  - Ink
  - Stocks/Currencies/Geography (Data Types)
- Features resulting in changes in object appearance
  - Shapes with Effects applied in the workbook
  - Data tables used for What-if Analysis
- Features that may cause the exported file to become corrupted
  - Map charts
- Features resulting in internal data loss
  - Data added to the Data Model
  - Data imported via XML
- Features resulting in loss of cloud integration
  - Integration with survey forms used by the Form feature
  - Integration with scripts added through the Automation tab