Import and Export Excel Options

In This Topic

While importing and exporting .xlsx files, it may not be required to transfer everything in exactly the same way. Sometimes you might need just data, while at other times, you might just want to skip formulas etc. DsExcel Java provides various import and export options which let you choose what to keep and what to skip. These options are especially useful in dealing with large files such as those containing multiple sheets, many formulas or a lot of shapes. DsExcel Java provides following methods to import and export the .xlsx files, so that you can import or export only what is required.

	Class name	Method Name	Description
Import Options	XlsxOpenOptions / XlsmOpenOptions / XltxOpenOptions	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.
		Import Flags	Provides various flags to import various aspects of a worksheet. For more information, see Work with Import Flags.
Export Options	XlsxSaveOptions / XlsmSaveOptions / XltxSaveOptions	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.

DsExcel Java also provides support for preserving the Japanese Ruby characters while executing the import and export operations on an Excel file. Also, users can adjust cells containing Japanese Ruby characters with utmost accuracy after performing other spreadsheet tasks like Insert, Delete, Copy, Cut, Merge, Clear, Sort operations etc.

Work with Import Flags

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

The ImportFlags enumeration allows users to import the workbook with the specified open options (a total of ten options are available: NoFlag, Data and Formulas, Table, mergeArea, Style, ConditionalFormatting, DataValidation, PivotTable and Shapes) as described in the table shared 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 and export .xlsx document with import and export options:

Java	Copy Code
// Create workbook and access its first worksheet Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); // Assigning value to range worksheet.getRange("A3").setValue(5); worksheet.getRange("A2").setValue(5); worksheet.getRange("A1").setValue(5); worksheet.getRange("B1").setValue(5); // Exporting .xlsx document workbook.save("savingfile.xlsx", SaveFileFormat.Xlsx); // Exporting .xlsx document while setting password XlsxSaveOptions options = new XlsxSaveOptions(); options.setPassword("Pwd"); workbook.save("savingfile.xlsx", options); // Exporting .xlsx document by ignoring cell formulas workbook.getActiveSheet().getRange("A4").setFormula("=Sum(A1+A2+A3)"); XlsxSaveOptions options2 = new XlsxSaveOptions(); options2.setIgnoreFormulas(true); workbook.save("ignoreformulas.xlsx", options2); // Importing .xlsx document workbook.open("Source.xlsx", OpenFileFormat.Xlsx); // Importing .xlsx document with Open options // Import only data from .xlsx document. XlsxOpenOptions options3 = new XlsxOpenOptions(); options3.setImportFlags(EnumSet.of(ImportFlags.Data)); workbook.open("Source.xlsx", options3); // Don't recalculate after opened. XlsxOpenOptions options1 = new XlsxOpenOptions(); options1.setDoNotRecalculateAfterOpened(true); workbook.open("Source.xlsx", options1);

Java

Copy Code

// Create workbook and access its first worksheet
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
            
// Assigning value to range
worksheet.getRange("A3").setValue(5);
worksheet.getRange("A2").setValue(5);
worksheet.getRange("A1").setValue(5);
worksheet.getRange("B1").setValue(5);
            
// Exporting .xlsx document
workbook.save("savingfile.xlsx", SaveFileFormat.Xlsx);

// Exporting .xlsx document while setting password
XlsxSaveOptions options = new XlsxSaveOptions();
options.setPassword("Pwd");
workbook.save("savingfile.xlsx", options);
            
 // Exporting .xlsx document by ignoring cell formulas
workbook.getActiveSheet().getRange("A4").setFormula("=Sum(A1+A2+A3)");
XlsxSaveOptions options2 = new XlsxSaveOptions();
options2.setIgnoreFormulas(true);
workbook.save("ignoreformulas.xlsx", options2);

// Importing .xlsx document
workbook.open("Source.xlsx", OpenFileFormat.Xlsx);

// Importing .xlsx document with Open options

// Import only data from .xlsx document.
XlsxOpenOptions options3 = new XlsxOpenOptions();
options3.setImportFlags(EnumSet.of(ImportFlags.Data));
workbook.open("Source.xlsx", options3);

// Don't recalculate after opened.
XlsxOpenOptions options1 = new XlsxOpenOptions();
options1.setDoNotRecalculateAfterOpened(true);
workbook.open("Source.xlsx", options1);

Similarly, you can also import and export .xlsm and .xltx files.