Document Solutions for Excel, Java Edition | Document Solutions
File Operations / Import and Export Excel Options
In This Topic
    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 XlsxOpenOptionsXlsmOpenOptions / 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 XlsxSaveOptionsXlsmSaveOptions / 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);

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