Import and Export from JSON without Worksheets

In This Topic

DsExcel allows you to convert skeleton of the workbook into a JSON stream. That is, you can export a workbook with just the worksheet without any data in them. This can be implemented by setting the SerializationOptions.IgnoreSheets to true and then pass it as a parameter while calling IWorkbook.ToJson interface method. You can also export the worksheet only to a separate JSON stream by using the IWorksheet.ToJson(Stream stream) method. Similarly, IWorkSheet.FromJson(Stream stream) lets you import the worksheets from JSON stream when required.

This feature is especially useful in optimizing the performance while loading large workbooks with many worksheets containing complex formula. Using the above-mentioned methods, you can load an empty workbook first with worksheet names and load the worksheet data when user selects a worksheet tab.

The example below demonstrates how you can export the workbook and worksheet to separate JSON streams and load them in a new workbook instance when required.

C#	Copy Code
//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //The old workbook. var oldWorkbook = new GrapeCity.Documents.Excel.Workbook(); var fileStream = GetResourceStream("xlsx\\12-month cash flow statement1.xlsx"); oldWorkbook.Open(fileStream); using (Stream workbookStream = new FileStream("workbook.json", FileMode.Create)) { //Export the skeleton of the workbook without worksheets to json stream. oldWorkbook.ToJson(workbookStream, new SerializationOptions { IgnoreSheets = true }); //Import the workbook stream. workbook.FromJson(workbookStream); } using (Stream worksheetStream = new FileStream("worksheet.json", FileMode.Create)) { //Export the worksheet to json stream. oldWorkbook.ActiveSheet.ToJson(worksheetStream); //Import the worksheet stream. workbook.ActiveSheet.FromJson(worksheetStream); } // Save to an excel file workbook.Save("workbooktojsonwithoutsheets.xlsx"); } static Stream GetResourceStream(string resourcePath) { string resource = "WorkbookToJsonWithoutSheets.Resource." + resourcePath.Replace("\\", "."); var assembly = typeof(JSONWithoutSheets).GetTypeInfo().Assembly; return assembly.GetManifestResourceStream(resource); }

Copy Code

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

    //The old workbook.
    var oldWorkbook = new GrapeCity.Documents.Excel.Workbook();

    var fileStream = GetResourceStream("xlsx\\12-month cash flow statement1.xlsx");
    oldWorkbook.Open(fileStream);

    using (Stream workbookStream = new FileStream("workbook.json", FileMode.Create))
    {
        //Export the skeleton of the workbook without worksheets to json stream. 
        oldWorkbook.ToJson(workbookStream, new SerializationOptions { IgnoreSheets = true });

        //Import the workbook stream.
        workbook.FromJson(workbookStream);
    }

    using (Stream worksheetStream = new FileStream("worksheet.json", FileMode.Create))
    {
        //Export the worksheet to json stream. 
        oldWorkbook.ActiveSheet.ToJson(worksheetStream);

        //Import the worksheet stream.
        workbook.ActiveSheet.FromJson(worksheetStream);
    }

    // Save to an excel file
    workbook.Save("workbooktojsonwithoutsheets.xlsx");

}

static Stream GetResourceStream(string resourcePath)
{
    string resource = "WorkbookToJsonWithoutSheets.Resource." + resourcePath.Replace("\\", ".");
    var assembly = typeof(JSONWithoutSheets).GetTypeInfo().Assembly;
    return assembly.GetManifestResourceStream(resource);
}