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

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

    When you create a workbook using DsExcel .NET and save it, you automatically export it to an external location or folder. While opening or loading a file in DsExcel .NET, you can choose whether to import whole model of the target spreadsheet, or just bring in only data. DsExcel .NET provides Open method to open a file with various import flags that can be accessed through ImportFlags property of the XlsxOpenOptions class. Similarly, 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:

    Class name Property 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.
    Import Flags 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.
    FileFormat Represents the format in which the workbook is opened.
    Password The password for the xlsx file.
    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.
    FileFormat Represents the format in which the workbook is saved.
    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.

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

    C#
    Copy Code 
    // Create a new workbook.
Workbook workbook = new Workbook();

// Open xlsx file.
workbook.Open(Path.Combine("Resource", "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:

    C#
    Copy Code 
    // Create a new file stream to open a file.
using FileStream openFile = new FileStream(Path.Combine("Resource", "Basic sales report1.xlsx"), FileMode.OpenOrCreate, FileAccess.Read);

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

// Open xlsx file.
streamworkbook.Open(openFile, OpenFileFormat.Xlsx);

// Create a new file stream to save a file.
using FileStream saveFile = new FileStream("Exported-Stream.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);

// Save workbook as xlsx file.
streamworkbook.Save(saveFile, SaveFileFormat.Xlsx);

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

    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.

    C#
    Copy Code 
    //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

    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.

    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:

    C#
    Copy Code 
    // 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.