Import and Export .sjs Files | Document Solutions for Excel, .NET Edition

Import and Export .sjs Files

In This Topic

SpreadJS v16 introduced a new file format, .sjs, to work with large and complex files faster and generate smaller files (in size) when saved. The new .sjs format is a zipped file that contains multiple smaller JSON files and is structured similarly to the Excel XML structure.

DsExcel allows you to import and export the new .sjs file format just like the XLSX, CSV, and other file formats. You can import a .sjs file using the Open method of Workbook class. Once loaded in DsExcel, it can be exported to Excel (XLSX) or back to .sjs file using the Save method of Workbook class. While loading or saving a .sjs file, you can use the new option "Sjs" in OpenFileFormat and SaveFileFormat enums.

Refer to the following example code to import and export a .sjs file from the file name:

C#	Copy Code
// Initialize Workbook. Workbook workbook = new Workbook(); // Open .sjs file. workbook.Open("ProjectPlan.sjs", OpenFileFormat.Sjs); // Save .sjs file. workbook.Save("SaveProjectPlan.sjs", SaveFileFormat.Sjs);

Copy Code

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open .sjs file.
workbook.Open("ProjectPlan.sjs", OpenFileFormat.Sjs);

// Save .sjs file.
workbook.Save("SaveProjectPlan.sjs", SaveFileFormat.Sjs);

Refer to the following example code to import and export a .sjs file from a file stream:

C#	Copy Code
// Initialize Workbook. Workbook workbook = new Workbook(); // Open a .sjs file from stream. var importStream = new FileStream("ProjectPlan.sjs", FileMode.Open); workbook.Open(importStream, OpenFileFormat.Sjs); // Save the .sjs file to stream. var exportStream = new FileStream("SaveProjectPlan.sjs", FileMode.Create); workbook.Save(exportStream, SaveFileFormat.Sjs);

Copy Code

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open a .sjs file from stream.
var importStream = new FileStream("ProjectPlan.sjs", FileMode.Open);
workbook.Open(importStream, OpenFileFormat.Sjs);

// Save the .sjs file to stream.
var exportStream = new FileStream("SaveProjectPlan.sjs", FileMode.Create);
workbook.Save(exportStream, SaveFileFormat.Sjs);

In addition, DsExcel provides SjsOpenOptions and SjsSaveOptions classes to customize the import and export of a .sjs file. These options are especially useful in dealing with large files, such as those containing many formulas, styles, or unused names. These options are listed below:

	Class	Option	Description
Import Options	SjsOpenOptions	IncludeStyles	Indicates whether the style can be included when loading .sjs files. By default, it is true.
Import Options	SjsOpenOptions	IncludeFormulas	Indicates whether the formula can be included when loading .sjs files. By default, it is true.
Export Options	SjsSaveOptions	IncludeStyles	Indicates whether the style can be included when saving files. By default, the value is true.
		IncludeFormulas	Indicates whether the formula can be included when saving the file. By default, the value is true.
		IncludeUnusedNames	Indicates whether the unused custom name can be included when saving the file. By default, the value is true.
		IncludeEmptyRegionCells	Indicates whether any empty cells outside the used data range can be included while saving the file. By default, the value is true.
		IgnoreRangeOutOfRowCoulumnCount	Indicates whether to ignore data out of RowCount and ColumnCount while saving the file. By default, the value is false.
		IncludeAutoMergedCells	Indicates whether to include the automatically merged cells. By default, the value is false.
		IncludeBindingSource	Indicates whether to include the binding source when saving the file. By default, the value is true.

Refer to the following example code to import and export a .sjs file using SjsOpenOptions and SjsSaveOptions:

C#	Copy Code
// Initialize Workbook. Workbook workbook = new Workbook(); // Open a .sjs file with formulas. SjsOpenOptions openOptions = new SjsOpenOptions(); openOptions.IncludeFormulas = false; openOptions.IncludeStyles = false; workbook.Open("ProjectPlan.sjs", openOptions); // Save the .sjs file with styles. SjsSaveOptions saveOptions = new SjsSaveOptions(); saveOptions.IncludeStyles = false; saveOptions.IncludeFormulas = true; saveOptions.IncludeUnusedNames = false; saveOptions.IncludeEmptyRegionCells = false; workbook.Save("SaveProjectPlan.sjs", saveOptions);

Copy Code

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open a .sjs file with formulas.
SjsOpenOptions openOptions = new SjsOpenOptions();
openOptions.IncludeFormulas = false;
openOptions.IncludeStyles = false;
workbook.Open("ProjectPlan.sjs", openOptions);

// Save the .sjs file with styles.
SjsSaveOptions saveOptions = new SjsSaveOptions();
saveOptions.IncludeStyles = false;
saveOptions.IncludeFormulas = true;
saveOptions.IncludeUnusedNames = false;
saveOptions.IncludeEmptyRegionCells = false;
workbook.Save("SaveProjectPlan.sjs", saveOptions);

Import and Export .sjs JSON String or Stream

DsExcel provides ToSjsJson method that integrates all JSON files from the .sjs file into a single string or stream. Furthermore, DsExcel also provides FromSjsJson, which loads a string or stream of all JSON files generated from .sjs file. These methods also support SjsOpenOptions and SjsSaveOptions.

Methods	Description
ToSjsJson()	Generates a JSON string from a workbook. It integrates all JSON files from the .sjs file into a single string.
ToSjsJson(SjsSaveOptions options)	Generates a JSON string from a workbook using save options. It integrates all JSON files from the .sjs file into a single string.
ToSjsJson(Stream stream)	Integrates all JSON files from the .sjs file into a single string, then puts the string into the stream.
ToSjsJson(Stream stream, SjsSaveOptions options)	Integrates all JSON files from the .sjs file into a single string using save options, then puts the string into the stream.
FromSjsJson(string json)	Generates a workbook from a JSON string containing the contents of .sjs file format.
FromSjsJson(string json, SjsOpenOptions options)	Generates a workbook from a JSON string using open options containing the contents of .sjs file format.
FromSjsJson(Stream stream)	Generates a workbook from a JSON stream containing the contents of .sjs file format.
FromSjsJson(Stream stream, SjsOpenOptions options)	Generates a workbook from a JSON stream using open options containing the contents of .sjs file format.

Refer to the following example code to export and import a single JSON string generated from .sjs file:

C#	Copy Code
// Initialize Workbook. Workbook workbook = new Workbook(); // Open workbook. workbook.Open("12-month cash flow statement1.sjs"); // Generate a JSON string for .sjs file and save it to a stream. var exportStream = new FileStream("CashFlow.json", FileMode.Create); workbook.ToSjsJson(exportStream); exportStream.Close(); // Import a JSON string from the stream and save it as an Excel file. var inputStream = new FileStream("CashFlow.json", FileMode.Open); workbook.FromSjsJson(inputStream); // Save workbook. workbook.Save("CashFlow.xlsx");

Copy Code

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open workbook.
workbook.Open("12-month cash flow statement1.sjs");

// Generate a JSON string for .sjs file and save it to a stream.
var exportStream = new FileStream("CashFlow.json", FileMode.Create);
workbook.ToSjsJson(exportStream);
exportStream.Close();


// Import a JSON string from the stream and save it as an Excel file.
var inputStream = new FileStream("CashFlow.json", FileMode.Open);
workbook.FromSjsJson(inputStream);

// Save workbook.
workbook.Save("CashFlow.xlsx");

Pixel Based Column-Width

DsExcel allows you to render column width based on pixels instead of characters using PixelBasedColumnWidth property of WorkbookOptions class when exporting a .sjs file as a PDF or image.

Refer to the following example code to render column width based on pixels when exporting as a PDF or image:

C#	Copy Code
// Initialize WorkbookOptions. WorkbookOptions workbookOptions = new WorkbookOptions(); // Enable pixel-based column width for the workbook. workbookOptions.PixelBasedColumnWidth = true; var workbook = new Workbook(workbookOptions); // Open .sjs file. workbook.Open("Event budget.sjs"); IWorksheet worksheet = workbook.Worksheets[0]; // Save to a PDF and PNG file. workbook.Save("SavePDFWithPixelBasedColumnWidth.pdf"); worksheet.ToImage("SavePDFWithPixelBasedColumnWidth.png");

Copy Code

// Initialize WorkbookOptions.
WorkbookOptions workbookOptions = new WorkbookOptions();

// Enable pixel-based column width for the workbook.
workbookOptions.PixelBasedColumnWidth = true;
var workbook = new Workbook(workbookOptions);

// Open .sjs file.
workbook.Open("Event budget.sjs");
IWorksheet worksheet = workbook.Worksheets[0];

// Save to a PDF and PNG file.
workbook.Save("SavePDFWithPixelBasedColumnWidth.pdf");
worksheet.ToImage("SavePDFWithPixelBasedColumnWidth.png");

Row and Column Count

DsExcel allows you to set the count of rows and columns in a worksheet while performing .sjs I/O. RowCount and ColumnCount properties of the IWorksheet interface enable you to achieve the same. RowCount and ColumnCount properties also increase or decrease the row and column count when you insert or delete the rows or columns.

You can also use the IgnoreRangeOutOfRowColumnCount property of SjsSaveOptions class to choose whether to ignore the data outside the range of the specified row and column count. The default value of this property is false, which exports the data outside the range of the specified row and column count to .sjs.

Refer to the following example code to set the row and column count in a worksheet and export it to a .sjs file:

C#	Copy Code
// Create a new workbook. var workbook = new Workbook(); // Open .sjs file. workbook.Open("LoanDetails.sjs", OpenFileFormat.Sjs); // Access first worksheet. var worksheet = workbook.Worksheets[0]; // Adjust row and column count to 4. worksheet.RowCount = 4; worksheet.ColumnCount = 4; // Control the exclusion of content outside the row or column count from being exported. SjsSaveOptions saveOptions = new SjsSaveOptions { IgnoreRangeOutOfRowColumnCount = true }; // Save the file in .sjs file format. workbook.Save("IgnoreDataOption.sjs", saveOptions);

Copy Code

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

// Open .sjs file.
workbook.Open("LoanDetails.sjs", OpenFileFormat.Sjs);

// Access first worksheet.
var worksheet = workbook.Worksheets[0];

// Adjust row and column count to 4.
worksheet.RowCount = 4;
worksheet.ColumnCount = 4;

// Control the exclusion of content outside the row or column count from being exported.
SjsSaveOptions saveOptions = new SjsSaveOptions
{
    IgnoreRangeOutOfRowColumnCount = true
};

// Save the file in .sjs file format.
workbook.Save("IgnoreDataOption.sjs", saveOptions);

Limitations

The row and column count setting is only supported for .sjs I/O and cannot be exported to Excel or PDF file.
RowCount and ColumnCount cannot exceed the maximum row limit of 1,048,576 and the column limit of 16,384 in MS Excel.

Cell Decoration

DsExcel allows you to add decorations to cells or cell ranges in the form of corner folds or icons using Decoration property of IRange interface, which uses the instances of ICornerFold and ICellDecorationIcon interfaces. DsExcel also provides CornerPosition and IconPosition enumerations to set the position of the corner fold and icon.

You must create instances of ICornerFold and ICellDecorationIcon interfaces using CornerFold and CellDecorationIcon constructors and configure the corner fold and icon before setting the decoration of a cell or cell range.

Refer to the following example code to add cell decoration to the cells:

C#	Copy Code
// Create a new workbook. var workbook = new Workbook(); // Access first worksheet. IWorksheet worksheet = workbook.Worksheets[0]; // Add values to cell range. worksheet.Range["C4"].Value = "FY 2019"; worksheet.Range["C5"].Value = "Sales"; worksheet.Range["C6"].Value = "Monthly"; string[] months = { "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar" }; int[] monthlySales = { 188897, 208146, 226196, 277318, 263273, 259845, 241047, 256306, 195845, 204934, 257852, 227779 }; for (int i = 0; i < months.Length; i++) { worksheet.Range[$"B{7 + i}"].Value = months[i]; worksheet.Range[$"C{7 + i}"].Value = monthlySales[i]; } // Set color using string. worksheet.Range["B4:C6"].Interior.Color = Color.FromArgb(173, 216, 230); worksheet.Range["C4:C6"].Borders.Color = Color.FromArgb(0, 0, 0); worksheet.Range["B7:B18"].Interior.Color = Color.FromArgb(211, 211, 211); worksheet.Range["B7:C18"].Borders.Color = Color.FromArgb(0, 0, 0); // Set cell range style. worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin; worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin; worksheet.Range["B4:B6"].Merge(); worksheet.Range["C2:D18"].ColumnWidth = 15; worksheet.Range["B2:D18"].HorizontalAlignment = HorizontalAlignment.Center; worksheet.Range["B2:C18"].VerticalAlignment = VerticalAlignment.Center; worksheet.Range["B4:C6"].Font.Bold = true; worksheet.Range["C7:C18"].NumberFormat = "#,##0"; worksheet.Range["C2"].HorizontalAlignment = HorizontalAlignment.Right; // Hightlight highest sales using cell decoration. ICornerFold cornerFold1 = new CornerFold(Color.Red, CornerPosition.LeftTop, 8); ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon( "data:image/svg+xml;base64" + ",PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxM" + "iAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMD" + "Avc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWl" + "naHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVu" + "dCIvPgo8cGF0aCBmaWxsLXJ1bGU9I" + "mV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTT" + "cgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi" + "41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM" + "3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTI" + "gNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIg" + "MTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K", 12, 12, IconPosition.OutsideRight); worksheet.Range["C10"].Decoration = new CellDecoration(cornerFold1, new List<ICellDecorationIcon>() { cellDecorationIcon1 }); worksheet.Range["D10"].Value = "Highest"; // Hightlight lowest sales using cell decoration. ICornerFold cornerFold2 = new CornerFold(Color.Green, CornerPosition.LeftTop, 8); ICellDecorationIcon cellDecorationIcon2 = new CellDecorationIcon( "data:image/svg+xml;base64," + "PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdC" + "b3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh" + "0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnI" + "j4KPHJlY3Qgd2lkdGg9IjEyIiBoZW" + "lnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvP" + "go8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXA" + "tcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0" + "NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA" + "3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyO" + "CAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMT" + "FDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxM" + "FoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=", 12, 12, IconPosition.OutsideRight); worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2, new List<ICellDecorationIcon>() { cellDecorationIcon2 }); worksheet.Range["D7"].Value = "Lowest"; // Save the workbook to .sjs document. workbook.Save("CellDecoration.sjs");

Copy Code

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

// Access first worksheet.
IWorksheet worksheet = workbook.Worksheets[0];

// Add values to cell range.
worksheet.Range["C4"].Value = "FY 2019";
worksheet.Range["C5"].Value = "Sales";
worksheet.Range["C6"].Value = "Monthly";

string[] months = { "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar" };
int[] monthlySales = { 188897, 208146, 226196, 277318, 263273, 259845, 241047, 256306, 195845, 204934, 257852, 227779 };

for (int i = 0; i < months.Length; i++)
{
    worksheet.Range[$"B{7 + i}"].Value = months[i];
    worksheet.Range[$"C{7 + i}"].Value = monthlySales[i];
}

// Set color using string.
worksheet.Range["B4:C6"].Interior.Color = Color.FromArgb(173, 216, 230);
worksheet.Range["C4:C6"].Borders.Color = Color.FromArgb(0, 0, 0);
worksheet.Range["B7:B18"].Interior.Color = Color.FromArgb(211, 211, 211);
worksheet.Range["B7:C18"].Borders.Color = Color.FromArgb(0, 0, 0);

// Set cell range style.
worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
worksheet.Range["B4:B6"].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
worksheet.Range["B4:B6"].Merge();
worksheet.Range["C2:D18"].ColumnWidth = 15;
worksheet.Range["B2:D18"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["B2:C18"].VerticalAlignment = VerticalAlignment.Center;
worksheet.Range["B4:C6"].Font.Bold = true;
worksheet.Range["C7:C18"].NumberFormat = "#,##0";
worksheet.Range["C2"].HorizontalAlignment = HorizontalAlignment.Right;

// Hightlight highest sales using cell decoration.
ICornerFold cornerFold1 = new CornerFold(Color.Red, CornerPosition.LeftTop, 8);
ICellDecorationIcon cellDecorationIcon1 = new CellDecorationIcon(
    "data:image/svg+xml;base64" +
    ",PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxM" +
    "iAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMD" +
    "Avc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWl" + 
    "naHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVu" +
    "dCIvPgo8cGF0aCBmaWxsLXJ1bGU9I" +
    "mV2ZW5vZGQiIGNsaXAtcnVsZT0iZXZlbm9kZCIgZD0iTT" +
    "cgOUg1TDUgNS45NjA0NmUtMDhIN0w3IDlaTTYgMTBDNi" +
    "41NTIyOCAxMCA3IDEwLjQ0NzcgNyAxMUM"
      + "3IDExLjU1MjMgNi41NTIyOCAxMiA2IDEyQzUuNDQ3NzIgMTI" +
      "gNSAxMS41NTIzIDUgMTFDNSAxMC40NDc3IDUuNDQ3NzIg" +
      "MTAgNiAxMFoiIGZpbGw9InJlZCIvPgo8L3N2Zz4K",
    12,
    12,
    IconPosition.OutsideRight);
worksheet.Range["C10"].Decoration = new CellDecoration(cornerFold1, new List<ICellDecorationIcon>() { cellDecorationIcon1 });
worksheet.Range["D10"].Value = "Highest";

// Hightlight lowest sales using cell decoration.
ICornerFold cornerFold2 = new CornerFold(Color.Green, CornerPosition.LeftTop, 8);
ICellDecorationIcon cellDecorationIcon2 = new CellDecorationIcon(
    "data:image/svg+xml;base64," +
    "PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdC" +
    "b3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh" +
    "0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnI" +
    "j4KPHJlY3Qgd2lkdGg9IjEyIiBoZW" + 
    "lnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvP" +
    "go8cGF0aCBmaWxsLXJ1bGU9ImV2ZW5vZGQiIGNsaXA" +
    "tcnVsZT0iZXZlbm9kZCIgZD0iTTcgOUg1TDUgNS45NjA0" +
    "NmUtMDhIN0w3IDlaTTYgMTBDNi41NTIyOCAxMCA" +
    "3IDEwLjQ0NzcgNyAxMUM3IDExLjU1MjMgNi41NTIyO" +
    "CAxMiA2IDEyQzUuNDQ3NzIgMTIgNSAxMS41NTIzIDUgMT" +
    "FDNSAxMC40NDc3IDUuNDQ3NzIgMTAgNiAxM" +
    "FoiIGZpbGw9ImdyZWVuIi8+Cjwvc3ZnPgo=",
    12,
    12,
    IconPosition.OutsideRight);
worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2, new List<ICellDecorationIcon>() { cellDecorationIcon2 });
worksheet.Range["D7"].Value = "Lowest";

// Save the workbook to .sjs document.
workbook.Save("CellDecoration.sjs");

Remove Cell Decoration

Refer to the following example code to remove the cell decoration completely:

C#	Copy Code
// Remove cell decoration. worksheet.Range["C7"].Decoration = null;

Refer to the following example code to remove only cell icon decoration:

C#	Copy Code
// Remove icon decoration. worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2, null); // Or worksheet.Range["C7"].Decoration = new CellDecoration(cornerFold2);

Refer to the following example code to remove only cell corner fold decoration:

C#	Copy Code
// Remove corner fold decoration. worksheet.Range["C7"].Decoration = new CellDecoration(null, new List<ICellDecorationIcon>() { cellDecorationIcon2 }); // Or worksheet.Range["C7"].Decoration = new CellDecoration(new List<ICellDecorationIcon>() { cellDecorationIcon2 });

Copy Code

// Remove corner fold decoration.
worksheet.Range["C7"].Decoration = new CellDecoration(null, new List<ICellDecorationIcon>() { cellDecorationIcon2 });

// Or

worksheet.Range["C7"].Decoration = new CellDecoration(new List<ICellDecorationIcon>() { cellDecorationIcon2 });

Note: Cell decoration is a SpreadJS feature, and DsExcel only supports exporting it to .sjs and .ssjson formats.

Control Bound Data Export

DsExcel enables you to control whether to export the bound data source to the file when exporting to .sjs file using IncludeBindingSource property of SjsSaveOptions.

Refer to the following example code to exclude the binding source when exporting to .sjs 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 SjsSaveOptions(); // Set IncludeBindingSource property to false to exclude the binding source from being exported. saveOptions.IncludeBindingSource = false; // Save the workbook. workbook.Save("IncludeBindingSourceOption.sjs", saveOptions);

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 SjsSaveOptions();
            
// Set IncludeBindingSource property to false to exclude the binding source from being exported.
saveOptions.IncludeBindingSource = false;

// Save the workbook.
workbook.Save("IncludeBindingSourceOption.sjs", 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.