Log in to start a topic

Possible to automate export from Excel to SSJSOn

Posted by: jhoppe on 8 September 2017, 1:10 am EST

  • jhoppe

    • Post Options:
    • Link
      Copy

    Posted 8 September 2017, 1:10 am EST

    Is it possible automate the exporting from an Excel file to SSJSON? Right now I have 9 Excel files, and whenever one gets updated, I have to manually open the designer. Click export to SSJSON, and specify the file name. It is also prone to human error. Is it possible through C#? If not, JavaScript?

    I am using SpreadJS 9.

  • reeva

    Posted 8 September 2017, 1:10 am EST

    Hello,

    For the Server Side Import Export, please have a look at codes below:-

    If the Excel is on server, it’s possible to open it directly. But if the file is local file then the browser cannot open local file without dialog.

    For opening server file, you can use Server side ExcelIO, or download file then open it by client side ExcelIO.

    [csharp] // Download Excel file

    var excelFilePath = ‘http://path/template.xlsx’;

    var xhr = new XMLHttpRequest();

    xhr.open(‘GET’, excelFilePath, true);

    xhr.responseType = ‘blob’;

            xhr.onload = function(e) {
          if (this.status == 200) {
            // get binary data as a response
            var blob = this.response;
            // convert Excel to JSON
            excelIo.open(blob, function (json) {
                var workbookObj = json;
                spread.fromJSON(workbookObj);
            }, function (e) {
                // process error
                alert(e.errorMessage);
            }, {});
          }
        };
       
        xhr.send();[/csharp]

    In order to save the modified JSON file into the server as Excel. You Upload the exported blob object to server which is already an excel file. In server we have a “saveExport” API or hander to get excel stream, then you can save it:-

    [csharp]/form>

    function exportData(){

    var json = spread.toJSON({includeBindingSource: true});

    var excelIo = new GC.Spread.Excel.IO();

    // here is excel IO API

    excelIo.save(json, function (blob) {

    var fd = new FormData(document.forms.namedItem(“myform”));

    fd.append(“test.xlsx”,blob);

    // you can insert your code to save it to SSJSON

        $.ajax({
        url: "saveExport",
        type:"POST",
        contentType:false,
        processData: false,
        data:fd,
        success: function (data) {
            if(data.isSuccess == 1){
                alert("Success！");
            }else{
                alert(data.errorMessage);
            }
        },
        error: function (ex) {
            alert("Error："+ex);
        }
    });
}, function (e) {
    alert(e);
});

    }[/csharp]

    Hope it helps.

    Thanks,

    Reeva

  • jhoppe

    Posted 8 September 2017, 1:10 am EST

    So this is not possible to do without using the Excel IO service? I was hoping to do it directly in C#, as opposed to having C# call the service.

    I have SpreadJS 9.40.20153.0. Where can I find the installer for Excel IO? I have installed SpreadJS ExcelIO Server Component.exe, but I don’t know where the service is. I don’t see it in IIS.

  • reeva

    Posted 8 September 2017, 1:10 am EST

  • karen.sellers

    Posted 15 August 2019, 4:23 am EST

    I’m on SpreadJS 12. Can you provide server side api code to receive this blob in C#? I attempted to use IFormFile as my request body on the API, but am getting an error 400 from the ajax post. Any thoughts as to what I’m doing wrong? Can’t seem to find examples on this anywhere.

      ```

    [HttpPost]

    public void Post([FromBody] IFormFile formFile)

    {

    if (formFile.Length > 0)

    {

    using (var memoryStream = new MemoryStream())

    {

    formFile.CopyTo(memoryStream);

    var spreadSheetBytes = memoryStream.ToArray();

    }

    }

        }

  • diwakar.s.maurya

    Posted 5 June 2024, 5:42 am EST

    I want to convert excel file to ssjon on server. I have been following this https://developer.mescius.com/blogs/how-to-import-export-excel-xlsx-using-node-js but unable to do it. Neither of the callbacks of excelIO.open get called. Kindly help in making this work again.

    I am using nodejs v22.2.0, spreadjs 17.0.10

    var fs = require('fs')
var fileReader = require('filereader');
var mockBrowser = require('mock-browser').mocks.MockBrowser;

global.window = mockBrowser.createWindow();
global.document = window.document;
global.navigator = window.navigator;
global.HTMLCollection = window.HTMLCollection;
global.getComputedStyle = window.getComputedStyle;
global.FileReader = fileReader;
global.self = window;
global.canvas = window.canvas


var GC = require('@mescius/spread-sheets');
var SJSExcel = require('@mescius/spread-excelio');

var excelIO = new SJSExcel.IO();

async function main() {

    try {
        var file = fs.readFileSync('./file.xlsx');
        excelIO.open(file.buffer, (data) => {
            // this somehow never gets called, neither throws any error //
            console.log(data)
        }, (error) => {
            console.error(error)
        });
    } catch (e) {
        console.error("** Error in spreadsheet **", e);
    }
}

main()

  • ankit.kumar

    Posted 5 June 2024, 5:03 pm EST

    Hi,

    Kindly refer to the attached sample that implements correct usage of loading the excel file and then exporting to the “.ssjson” file.

    You could refer to the following code in the attached sample:

    	var wb1 = new GC.Spread.Sheets.Workbook();
	const data1 = fs.readFileSync('./blank.xlsx');
	const blob1 = new Blob([data1.buffer], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
	blob1.name = "blank.xlsx";
	blob1.stream = fs.createReadStream('blank.xlsx');
	wb1.import(blob1, function () {
		console.log('-------load excel file---------');
		let sheet = wb1.getActiveSheet();
		console.log(sheet.name());
		console.log(sheet.getArray(0, 0, 3, 1));
		// Save json to the file with '.ssjson' extension
		let json = wb1.toJSON();
		 // Save JSON to the file with '.ssjson' extension
		 fs.writeFileSync('./export.ssjson', JSON.stringify(json));
		 console.log('JSON data saved to export.ssjson');

	}, function (msg) {
		console.log(msg); // error callback
	}, {
		fileType: GC.Spread.Sheets.FileType.excel,
	});

    Please note that with SpreadJS V16, we have introduced a new module for import/export i.e, SJSIO. ExcelIO is old module and the devs recommended to use the new SJSIO module rather than the previous ExcelIO module.

    You could refer to the following demo on the new SJSIO module: https://developer.mescius.com/spreadjs/demos/features/spreadjs-file-format/overview/purejs

    Also, the error might occur because you have not set the License Key or an invalid license key is set for V17. You don’t seem to have set the License Key in the code snippet that you have shared.

    Please set a valid license key in the line no. 20 of ‘index.js’ file, else the code won’t work and file won’t be generated.

    To run the application, perform the following steps:

    1. Install the dependencies using the ‘npm install’ command.
    2. Run the application using the ‘node index.js’ command.

    Regards,

    Ankit

  • ankit.kumar

    Posted 5 June 2024, 5:04 pm EST

    Sample: sjs_node.zip

  • diwakar.s.maurya

    Posted 7 June 2024, 5:33 pm EST

    I am unable to run the provided sample code. I throws this error

    { errorCode: 1, errorMessage: 'Incorrect file format.' }

    Here is the code https://codesandbox.io/p/devbox/broken-star-yv6q3r?file=%2Findex.js

  • diwakar.s.maurya

    Posted 9 June 2024, 6:00 am EST

    Sorry. the sample code works. Thank you very much. I forgot to add the license key that is why it was not working.

    It would have been better if the error message was in right direction. If license is missed, it says “Incorrect file format” which is a misleading error message.

  • chandan.rawat

    Posted 9 June 2024, 8:46 pm EST

    Hi,

    We are glad that your issue has been resolved.

    Regarding the error message, we have informed the dev team about this. The internal tracking ID for this is SJS-24730. We will keep you updated on the progress of this issue.

    Regards

  • chandan.rawat

    Posted 18 June 2024, 3:26 pm EST - Updated 18 June 2024, 3:31 pm EST

    Hi,

    The dev team has replied to the mentioned issue.

    It is important to note that when SpreadJS is used in a browser and the license key is not applied/set(excluding the localhost), it shows the license not found the message on the canvas which points out that the license is not set. Refer to the attached image.

    However, in the case of NodeJs. This is not the case. So, when the workbook is initialized without license, no error/message is shown. The error is thrown when the APIs are used(e.g. import APIs) because the license is not set. You may refer to the code snippet below in which an error is thrown when the license is not set.

    var wb1 = new GC.Spread.Sheets.Workbook();
    try {
        var sheet = wb1.getActiveSheet();
        sheet.setValue(0,0,1);
    } catch {
        throw 'Missing License';
    }

    Please note that the current behavior is by design. Additionally, it is important to note that SpreadJS does not support the NodeJs environment officially.

    950×367 5.6 KB

    Regards,

    Chandan

  • diwakar.s.maurya

    Posted 25 June 2024, 1:14 am EST - Updated 25 June 2024, 1:23 am EST

    When excel file is exported to SJS format using code similar to above, the charts disappear when sjs file is imported back in Designer https://developer.mescius.com/spreadjs/designer/index.html.

    Kindly help in finding the issue why the charts go missing on importing sjs file.

    Sample file: https://create.microsoft.com/en-us/template/cost-analysis-with-pareto-chart-f79af0fb-8ddb-4dfa-aae1-22f8ae7de1e3

    Sample code: https://codesandbox.io/p/devbox/broken-star-forked-2k43cn?file=%2Findex.js%3A32%2C18

    Charts not visible after SJS file imported that has been generated from above code:-

    1496×783 94.4 KB

    Charts visible in MS excel:-

    1801×907 130 KB

  • diwakar.s.maurya

    Posted 25 June 2024, 1:32 am EST

    I found my mistake :slight_smile:

    I was missing these require statements for these modules. Once I added these, it worked.

    require('@mescius/spread-sheets-io');
require('@mescius/spread-sheets-designer-resources-en')
require("@mescius/spread-sheets-tablesheet")
require("@mescius/spread-sheets-charts")
require("@mescius/spread-sheets-print")
require("@mescius/spread-sheets-barcode")
require("@mescius/spread-sheets-languagepackages")
require("@mescius/spread-sheets-shapes")
require("@mescius/spread-sheets-pivot-addon")

  • chandan.rawat

    Posted 25 June 2024, 3:51 pm EST

    Hi,

    We are glad that your issue has been resolved. It is important to note that for charts to work correctly, @mescius/spread-sheets-shapes and @mescius/spread-sheets-charts packages should be imported.

    Also, as it can be seen that the original issue of ticket or thread has been resolved. So, we encourage you to create a separate ticket or thread for a different issue than the previous one. It will help us as well as you to better manage issues and keep track of them.

    Thanks for your understanding.

    Regards

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.