Import and Export .xlsx Document

This section summarizes how DsExcel Java handles the spreadsheet documents(.xlsx files).

When you create a workbook using DsExcel Java and save it, you automatically export it to an external location or folder. When bringing an Excel file into DsExcel Java (importing a file or opening a file), you can either load the whole model of the imported spreadsheet, or just bring in only data. DsExcel Java provides open method to open a file with various import flags that can be accessed through setImportFlags method of the XlsxOpenOptions class. Similarly, to export a workbook as .xlsx file, you can use save method and provide various save options provided by DsExcel to specify what to skip and what to export. These options are listed below:

Class name		Option Name	Description
Import Options	XlsxOpenOptions	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. For more information, see Work with Import Flags.
		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.
		Password	The password for the xlsx file.
	OpenOptionsBase	FileFormat	Represents the format in which the workbook is opened.
Export Options	XlsxSaveOptions	IgnoreFormulas	Export formula cells of DsExcel worksheet as value cells in Excel.
		ExcludeUnusedStyles	Exclude the unused styles while exporting the file.
		ExcludeUnusedNames	Exclude the unused names while exporting the file.
		ExcludeEmptyRegionCells	Exclude empty cells, that is, the cells that lie outside the used range and have styles but do not contain data.
		IsCompactMode	Indicates whether to save workbook in compact mode. The default value is false.
		Password	The password for the xlsx file.
		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.
		ExportSharedFormula	Specifies whether to export shared formulas when saving the workbook. The default value is true.
	SaveOptionsBase	FileFormat	Represents the format in which the workbook is saved.

Refer to the following example code in order to import and export .xlsx document from the file name:

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

// Open xlsx file.
workbook.open("Basic sales report1.xlsx", OpenFileFormat.Xlsx);

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

Refer to the following example code in order to import and export .xlsx document from a file stream:

// Create a new workbook.
  var streamworkbook = new Workbook();
  // Create a new file stream to open a file.
InputStream openFile;
try {
    openFile = new FileInputStream("Basic sales report1.xlsx");
    // Open xlsx file.
    streamworkbook.open(openFile, OpenFileFormat.Xlsx);
} catch (FileNotFoundException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
}

// Create a new file stream to save a file.
 OutputStream out;
try {
    out = new FileOutputStream("Exported-Stream.xlsx");
     // Save workbook as xlsx file.
      streamworkbook.save(out, SaveFileFormat.Xlsx);
} catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

As another common scenario, you might need to import only data from a spreadsheet or a cell range. To handle such scenarios, DsExcel Java provides importData method to facilitate efficient loading from the external worksheet or a cell range. For more information about import data only, see Import Data Only section below.

Import Data Only

To import only data from a specified worksheet or a cell range, DsExcel Java 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
Workbook workbook = new Workbook();
// Open an excel file.
InputStream fileStream = getResourceStream("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".
String[] names = Workbook.getNames(fileStream);

// The InputStream of the Java platform cannot be read repeatedly, so you need to create another one.
InputStream fileStream2 = getResourceStream("AgingReport.xlsx");

// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
Object[][] data = Workbook.importData(fileStream2, names[2]);

// Assign the data to current workbook.
workbook.getWorksheets().get(0).getRange(0, 0, data.length, data[0].length).setValue(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.

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 setIncludeBindingSource method 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.
String 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.
IWorksheet dataSourceSheet = workbook.getWorksheets().add();
dataSourceSheet.setName("DataSource");
ITable table = dataSourceSheet.getTables().add(dataSourceSheet.getRange("A1:E4"), true);

// Set binding path.
table.setBindingPath("ds");
table.getColumns().get(0).setDataField("Area");
table.getColumns().get(1).setDataField("City");
table.getColumns().get(2).setDataField("Category");
table.getColumns().get(3).setDataField("Name");
table.getColumns().get(4).setDataField("Revenue");

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

// Create pivot table sheet.
IWorksheet pivotSheet = workbook.getWorksheets().get(0);
pivotSheet.setName("PivotSheet");

// Create pivot table.
IPivotCache pivotcache = workbook.getPivotCaches().create(table);
IPivotTable pivottable = pivotSheet.getPivotTables().add(pivotcache, pivotSheet.getRange("A1"), "pivottable1");

// Configure pivot table fields.
IPivotField fieldArea = pivottable.getPivotFields().get("Area");
fieldArea.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldCity = pivottable.getPivotFields().get("City");
fieldCity.setOrientation(PivotFieldOrientation.RowField);
IPivotField fieldName = pivottable.getPivotFields().get("Name");
fieldName.setOrientation(PivotFieldOrientation.ColumnField);
IPivotField fieldRevenue = pivottable.getPivotFields().get("Revenue");
fieldRevenue.setOrientation(PivotFieldOrientation.DataField);
pivotSheet.getUsedRange().autoFit();
pivottable.setColumnGrand(false);
pivottable.setRowGrand(false);
pivottable.refresh();

XlsxSaveOptions saveOptions = new XlsxSaveOptions();

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

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

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

Shared Formula Export Options

When exporting to .xlsx, DsExcel 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 setExportSharedFormula method 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 workbookWorkbook workbook = new Workbook();
IWorksheet Sheet = workbook.getActiveSheet();

Sheet.getRange("B1:B5").setFormula("IF(A1>100,SUM(A1:A5)*AVERAGE(A1:A5),MAX(A1:A5)-MIN(A1:A5))");

XlsxSaveOptions saveOptions = new XlsxSaveOptions();
saveOptions.setExportSharedFormula(false);

workbook.save("NoSharedFormula.xlsx", saveOptions);

In Office Open XML (OOXML) .xlsx files, formulas are stored as shared or individual formulas depending on whether setExportSharedFormula is set to true or false, as shown in the table below:

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>

Current Limitations

DsExcel Java does not support importing xls files.
DsExcel Java does not support exporting xlsx files to XPS format.
The following features are not supported by DsExcel Java. 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)
  - CheckBox as cell data type
- 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