Documents for Excel, .NET Edition Documentation
File Operations / Import and Export JSON Stream
In This Topic
    Import and Export JSON Stream
    In This Topic

    GcExcel .NET supports the import and export of a json stream using .NET core.

    This topic includes the following tasks.

    Import and Export JSON Stream for Workbook

    You can export a workbook to a json string/stream using the ToJson method of the IWorkbook interface. You can also import a json string or stream to your workbook using the FromJson method of the IWorkbook interface.

    Refer to the following example code to import and export json stream.

    C#
    Copy Code
    //ToJson&FromJson can be used in combination with spreadjs product
    
    //GcExcel import an excel file.
    //change the path to real source file path.
    string source = "savingfile.xlsx";
    workbook.Open(source);
    
    //GcExcel export to a json string.
    var jsonstr = workbook.ToJson();
    //use the json string to initialize spreadjs product.
    //spreadjs will show the excel file contents.
    
    //spreadjs product export a json string.
    //GcExcel use the json string to initialize.
    workbook.FromJson(jsonstr);
    //GcExcel export workbook to an excel file.
    //change the path to real export file path.
    
    string export = "export.xlsx";
    workbook.Save(export);

    Import and Export JSON String for Worksheet

    You can export the information in a worksheet to a json string using the ToJson method of the IWorksheet interface. Similarly, you can also import a json string to your worksheet using the FromJson of the IWorksheet interface. The worksheet can also be exported or imported to the same or another workbook.

    It also enables you to view a large Excel file in SpreadJS. The Excel file can be opened in GcExcel and the json string of a worksheet can be exported using the ToJson method. Further, the json string of the worksheet can be transfered to client to be loaded in SpreadJS.

    Limitations

    Refer to the following example code to export and import json string of a worksheet.

    C#
    Copy Code
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    
    //ToJson&FromJson can be used in combination with spreadjs product:http://spread.grapecity.com/spreadjs/sheets/
    
    //GrapeCity Documents for Excel import an excel file
    string source = "ExcelJsonInput.xlsx";
    workbook.Open(source);
    
    //Open the file
    GrapeCity.Documents.Excel.Workbook new_workbook = new GrapeCity.Documents.Excel.Workbook();
    new_workbook.Open(source);
    
    foreach (IWorksheet worksheet in workbook.Worksheets)
    {
        worksheet.Range["D40:F40"].Value = new string[] { "Device", "Quantity", "Unit Price" };
        worksheet.Range["D41:F44"].Value = new object[,]
       { { "T540p", 12, 9850 },
            { "T570", 5, 7460 },
            { "Y460", 6, 5400 },
            { "Y460F", 8, 6240 } };
    
        //GrapeCity Documents for Excel export a worksheet to json string
        string json = worksheet.ToJson();
    
        //You can use the json string to initialize spreadjs product
        //Product spreadjs will show the excel file contents
        //You can use spreadjs product export a json string of worksheet
    
        //GrapeCity Documents for Excel use the json string to update content of the corresponding worksheet
        new_workbook.Worksheets[worksheet.Name].FromJson(json);
    }
    
    //GrapeCity Documents for Excel export workbook to an excel file
    string export = "ExcelJsonOutput.xlsx";
    new_workbook.Save(export);
    

    SpreadJS SSJSON Support

    GcExcel .NET provides support for SpreadJS SSJSON. You can import a SSJSON file created with SpreadJS Designer and save it back after modifying it as per your preferences.

    C#
    Copy Code
    //Create a new workbook
    Workbook workbook = new Workbook();                      
                
    //Load SSJSON file 
    var stream = new System.IO.FileStream("Chart_Spread.ssjson", System.IO.FileMode.Open);
    workbook.FromJson(stream);
                
    //Save file 
    workbook.Save("workbook_ssjson.xlsx");

    Note: Upon loading the SpreadJS JSON file, if users get the ColorIndex property of the IBorder interface in order to set an index color, it will return a valid value only if the Color property of the IBorder interface is set to any rgb color; else, it will return -2 as an invalid flag. Usually, an index color can be converted to rgb color but vice a versa is not possible. 


    Import and Export SpreadJS JSON Files with Shapes

    GcExcel .NET allows users to load and save Grapecity SpreadJS JSON files with shapes. Besides importing the existing Spread JS JSON files with shapes, users can also modify the exported Spread JS JSON files containing shapes and save them back to the original Spread JS JSON files as and when required.

    C#
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
    
    // Initialize another workbook - workbookWithShape
    Workbook workbookWithShape = new Workbook();
    
    // Fetch default worksheet of workbookWithShape
    IWorksheet worksheet = workbookWithShape.Worksheets[0];
    
    // Add a shape in the worksheet
    IShape shape = worksheet.Shapes.AddShape(AutoShapeType.Parallelogram, 1, 1, 200, 100);
    shape.Line.DashStyle = LineDashStyle.Dash;
    shape.Line.Style = LineStyle.Single;
    shape.Line.Weight = 2;
    IColorFormat color = shape.Fill.Color;
    shape.Line.Transparency = 0.3;
    color.ObjectThemeColor = ThemeColor.Accent6;
    
    // Converting workbook containing shape to JsonString
    String jsonString = workbookWithShape.ToJson();
    
    // GcExcel can load json string containing shapes
    workbook.FromJson(jsonString);
    
    // Saving the workbook
    workbook.Save(@"7-LoadSaveShapesSSJSON.xlsx");

    Retreive Errors while Importing JSON Files

    GcExcel provides the option to get JSON errors, if any, while importing the JSON file using FromJson method of IWorkbook interface. The error message is displayed by the ErrorMessage property of JsonError class. Two types of error messages are supported:

    Refer to the below example code which will display a formula JSON error as the JSON file containing formula error is imported in GcExcel.

    C#
    Copy Code
    Workbook workbook = new Workbook();
    IList<JsonError> errors = workbook.FromJson(File.OpenRead("ErrorJson.json"));
    foreach (JsonError item in errors)
    {
        if (item is FormulaJsonError)
        {
            FormulaJsonError fError = item as FormulaJsonError;
            Console.WriteLine(fError.ErrorMessage + " " + workbook.Worksheets[fError.WorksheetName].Range[fError.Row, fError.Column].ToString() + " " + fError.Formula);
        }
        if (item is DataValidationJsonError)
        {
            DataValidationJsonError dError = item as DataValidationJsonError;
            Console.WriteLine(dError.ErrorMessage + " " + workbook.Worksheets[dError.WorksheetName].Range[dError.Range.ToString()] + " " + dError.ErrorContent);
        }
    }

    Limitation

    If the data validation in JSON file has error in its formula, Data Validation JSON error will be generated.