Load Excel file

Marco Giorgi

Asked On January 23, 2019 01:40 PM UTC

I'm using the ejSpreadsheet widget to allow users to review excel files.

The Excel files are uploaded by the user to the server and then processed with Syncfusion.EJ.Export to give the data, in JSON format, to the ejSpreadsheet client widget.

The issue is that I've discovered is that the JSON data sent to the client, representing the excel file, is more than 100x times bigger than the Excel files!!

A 200KB Excel file is converted into a 21MB JSON file

A 340KB Excel file is converted into a 40MB JSON file!

The content of both examples is simply tabular data, no chart, no formula, no macro, nothing... they could be simply converted to CSV format.

The code I'm using to convert Excel to JSON to send to the client widget is the following:

using System;

using System.Web;

using System.IO;

using Syncfusion.XlsIO;

using Syncfusion.EJ.Export;

using Syncfusion.JavaScript.Models;

public class GetExcelSpreadsheet : IHttpHandler {

public void ProcessRequest(HttpContext context)

{

var filePath =HttpContext.Current.Server.MapPath("\\Uploads") + "\\Temp\\" + context.Request.Form["filename"].ToString();

ImportRequest importRequest = new ImportRequest();

Stream fileStream = File.Open(filePath, FileMode.Open, System.IO.FileAccess.Read);

importRequest.FileStream = fileStream;

importRequest.File = null;

string str = Spreadsheet.Open(importRequest);

fileStream.Close();

fileStream.Dispose();

context.Response.Write(str);

}

public bool IsReusable {

get {

return false;

}

I hope I'm doing something wrong otherwise the component is not usable on the web!

Best,

Marco

Reply | Reply with quote

Christopher Issac Sunder K [Syncfusion]

Replied On January 24, 2019 12:49 PM UTC

Hi Marco,

Thank you for contacting Syncfusion support.

Based on your provided code snippet we have checked the reported issue with excel file having table data (size ~300KB) in version ’16.3.0.29’ and we cannot reproduce the reported issue in our end.

We have demonstrated it in the below video.

Video Link: http://www.syncfusion.com/downloads/support/forum/142228/ze/sample-101805403

Excel Link: http://www.syncfusion.com/downloads/support/forum/142228/ze/Sample-1326996297

We have generated the JSON file and its size is ~1.60MB. Please check the JSON file in below link,

JSON File: http://www.syncfusion.com/downloads/support/forum/142228/ze/JSONDATA403700539

Could you please get back to us with more information (like excel file and video demonstration) to replicate this issue, so that we can analyze based on that and provide you a better solution? The information provided would be great help for us to proceed further.

Thanks,

Christo

Reply | Reply with quote

Marco Giorgi

Replied On January 24, 2019 01:00 PM UTC

attached the excel file I'm testing

Attachment: ExcelSample_f69033a7.rar

Reply | Reply with quote

Silambarasan I [Syncfusion]

Replied On January 25, 2019 11:27 AM UTC

Hi Marco,

Sorry for the inconvenience. Please ignore the provided demonstrated video link in our last update.

Before proceeding further, we would like to know the following details.

1. In which way do you have measured the returned JSON size from server (string str = Spreadsheet.Open(importRequest);)?

2. Do you have make separate requests for both uploading excel (in "\\Uploads\\Temp\\" folder) & processing (with Syncfusion.EJ.Export to get JSON) in server. Since, you have mentioned “user can upload the excel and import it in Spreadsheet after processed in server”.

Meanwhile, we have checked your reported issue with the attached excel file by using chrome developer tool network option and the returned response size is not more than 100x times bigger than the excel file size. Please the refer the below video demonstration and details.

Video demonstration:

http://www.syncfusion.com/downloads/support/forum/142228/ze/Demo-1193088358

As in the above screenshot, the excel ‘Mali_Price_Data_Nov18_BKP20190108172744.xlsx’ file (with size 67.6 KB) generate 3.74 MB JSON result size and ‘WFP Lesotho _ Traders _ 20170502(12049) (003).xlsx’ file (with size 29.0 KB) generate 135 KB JSON file size from server to client.

Saved returned JSON in file:

http://www.syncfusion.com/downloads/support/forum/142228/ze/JSON_Files-862309026

Also, we would like to let you know that the size is based on the response header plus response body as delivered by the server and its original size doesn’t get change. Please refer the below doc link.

https://developers.google.com/web/tools/chrome-devtools/network-performance/reference#requests

Could you please check and get back to us with the above information so that we can analyze based on your exact scenario & provide you a prompt solution?

Regards,

Silambarasan

Reply | Reply with quote

Marco Giorgi

Replied On January 25, 2019 12:20 PM UTC

Sorry I think I've sent the wrong files. So the issue is related to something specific in the excel files.

Please find attached the two excel files where I got this issue.

In the zip there are also:

The screenshot of the network tab od the Chrome DevTools, showing the response from the converter handler of 41MB for the 300KB file

Two text files containing the JSON returned for each file.

Regarding your questions:

1. In which way do you have measured the returned JSON size from server (string str = Spreadsheet.Open(importRequest);)?

By saving the str variable in a text file (included in the attachment), and by looking at the Network tab of the Chrome DevTools

2. Do you have make separate requests for both uploading excel (in "\\Uploads\\Temp\\" folder) & processing (with Syncfusion.EJ.Export to get JSON) in server. Since, you have mentioned “user can upload the excel and import it in Spreadsheet after processed in server”.

Sure, each file has been tested with new request and restart of the development server

Best,

Marco

Attachment: ExcelFilesCausingIssueOnSyncfusion_20e048a0.rar

Reply | Reply with quote

Deepa Loganathan [Syncfusion]

Replied On January 30, 2019 10:38 AM UTC

Hi Marco,

Thank you for your update.

We have checked the reported JSON size issue while importing for excel files (File01.xlsx and File02.xlsx) and we are able to reproduce it in our end. We suspect that you have modified the default font family and size using File->Options->General. Please check the below screenshot,

The cause of this issue is due to customizing the default font family and size (Calibri with 11pt) in these excel files into Arial with 10pt. As per Spreadsheet behavior when default font is changed, we will process these formats for all cells (including empty cells) in the sheets on importing i.e. updating formats into JSON for each cell in entire sheet.

Meanwhile, we have modified the attached (File01.xlsx) by applying font Arial and size 10pt only to the used range (excluding empty cells) of the worksheet to reduce the JSON size and the size is ~5.0mb. Please check the modified excel in below link,

http://www.syncfusion.com/downloads/support/forum/142228/ze/Modified_Excel-812463381

Could you please check the above modified excel files and let us know whether these changes will be fulfilling your requirement or not?

Regards,

Deepa L.

Reply | Reply with quote

Marco Giorgi

Replied On January 30, 2019 10:53 AM UTC

Thanks for discovering the issue.

But I think is not yet solved, as the application I made are made for external users and not me, so I'm not aware of what the users will do with the excel file and their settings.

What I need is a way to discard these settings in order to produce the smallest file size as possible (5MB is still so big in the countries where the software is used)

Is there a way?

Best,

Marco

Reply | Reply with quote

Silambarasan I [Syncfusion]

Replied On February 4, 2019 04:19 PM UTC

Hi Marco,

Sorry for the inconvenience.

As discussed earlier, in your provided excel sheet contains List Data Validation with custom cell styles as font family (Arial) and font size (10pt) for used range “A1:J4500”. As per our Spreadsheet behavior, we need to process these formats & validation for these used range on importing and based on that - the JSON string will be generated. So, that the generated JSON file size takes 5mb for your provided excel file.

Please get back to us with more information if you need any further assistance on this.

Regards,

Silambarasan

Reply | Reply with quote

Marco Giorgi

Replied On February 4, 2019 04:27 PM UTC

Do you have any code sample on how to remove these styles and formats in order to have a small json output?

Reply | Reply with quote

Christopher Issac Sunder K [Syncfusion]

Replied On February 7, 2019 10:36 AM UTC

Hi Marco,

Thank you for your patience.

We have achieved your requirement ‘To remove styles and formats in order to have small JSON file’ and it can be achieved by converting the stream into CSV format using XLSIO (.NET class library) and passing it in Spreadsheet.Open() method. Please check the below code example,

ASHX

using Syncfusion.XlsIO;

public void ProcessRequest(HttpContext context)

{

string filePath = HttpContext.Current.Server.MapPath("\\Uploads") + "\\Temp\\" + context.Request.Form["filename"].ToString();

ImportRequest importRequest = new ImportRequest();

Stream fileStream = File.Open(filePath, FileMode.Open, System.IO.FileAccess.Read);

//Loads or open an existing workbook through Open method of IWorkbooks

ExcelEngine excelEngine = new ExcelEngine();

IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileStream);

//Save the workbook as stream

MemoryStream outputStream = new MemoryStream();

workbook.SaveAs(outputStream, ",");//save as csv

outputStream.Position = 0;

workbook = excelEngine.Excel.Workbooks.Open(outputStream);

outputStream.Position = 0;

importRequest.FileStream = outputStream;

importRequest.File = null;

string str = Spreadsheet.Open(importRequest);

fileStream.Close();

fileStream.Dispose();

outputStream.Close();

outputStream.Dispose();

context.Response.Write(str);

}

On importing the excel file ‘File01.xlsx’ with above code snippet, JSON file size is reduced to ~1.3mb from 5mb. Could you please importing the excel file that we have provided in our previous update and get back to us if you need any further assistance on this?

Thanks,

Christo

Reply | Reply with quote

Thread ID:	Created:	Updated:	Platform:	Replies:
142228	Jan 23,2019 01:40 PM UTC	Feb 7,2019 10:36 AM UTC	jQuery	9

Thread ID:

Created:

Updated:

Platform:

Replies:

Please sign in to access our forum

CONTACT US