Documents for Excel .NET Edition | GCDocuments
File Operations / Import and Export .xlsx Document
In This Topic
    Import and Export .xlsx Document
    In This Topic

    This section summarizes how GcExcel .NET handles the spreadsheet documents(.xlsx files).

    When you create a workbook using GcExcel .NET and save it, you automatically export it to an external location or folder. While opening or importing a file to GcExcel.NET, you can either load the whole model of the imported spreadsheet, or just bring in only data. GcExcel.NET provides Open method to open a file with various import flags that can be accessed through ImportFlags property of the XlsxOpenOptions class. For more information about using ImportFlags, see Working with Flags.

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

    GcExcel .NET 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.

    Working With Import Flags

    While opening a workbook, GcExcel .NET 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.

    The DoNotRecalculateAfterOpened property of the XlsxOpenOptions class allows you to set a boolean value (True or False) which specifies whether or not the formulas will be recalculated when the file is being imported.

    The IgnoreFormulas property of XlsxSaveOptions class, when set to true, allows you to export formula cells in GcExcel as value cells in Excel. When workbook.Save is called, the resultant values for formula cells are calculated and are saved in Excel.

    Refer to the following example code in order to import and export .xlsx document.

    C#
    Copy Code
    // Create workbook and access its first worksheet
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
                
    // Assigning value to range
    worksheet.Range["A3"].Value = 5;
    worksheet.Range["A2"].Value = 5;
    worksheet.Range["A1"].Value = 5;
    worksheet.Range["B1"].Value = 5;
                
    // Exporting .xlsx document
    workbook.Save(@"savingfile.xlsx", SaveFileFormat.Xlsx);
    
    // Exporting .xlsx document while setting password
    XlsxSaveOptions options = new XlsxSaveOptions();
    options.Password = "Pwd";
    workbook.Save(@"savingfile.xlsx", options);
                
     // Exporting .xlsx document by ignoring cell formulas
    workbook.ActiveSheet.Range["A4"].Formula = "=Sum(A1+A2+A3)";
    XlsxSaveOptions options2 = new XlsxSaveOptions();
    options2.IgnoreFormulas = 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 options = new XlsxOpenOptions();
    options.ImportFlags = ImportFlags.Data;
    workbook.Open(@"Source.xlsx", options);
    
    // Don't recalculate after opened.
    XlsxOpenOptions options1 = new XlsxOpenOptions();
    options1.DoNotRecalculateAfterOpened = true;
    workbook.Open(@"Source.xlsx", options1);

    Import Data Only

    To import only data from a specified worksheet or a cell range, GcExcel.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 worksheet as main parameters. It also provides overloads where you can specify the range of target cells from where you want to load the data.

    C#
    Copy Code
    //create a new workbook
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    workbook.Open("AgingReport.xlsx");
    // Import data of a range from the fileStream
    var data = GrapeCity.Documents.Excel.Workbook.ImportData("AgingReport.xlsx", "Aging Report", 8, 2, 21, 7);
    // Assign the data to current workbook
    workbook.Worksheets[0].Range[0, 0, 21, 7].Value = data;
    
    //save to an excel file
    workbook.Save("importdata.xlsx");

    Limitation

    Formlua 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, GcExcel returns that value.