Instantiating a Workbook from an input stream is very slow

Aspose.Cells Product Family
1

We have observed that instantiating a Workbook from an input stream is very slow. So is there any way that process can be faster?

For example -
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
Workbook workbook = new Workbook(inputStream);

2

@aduttaPlanful,

Please zip and attach your input Excel file. Also, share complete code on how you are initializing and getting outputStream. We will check your issue soon.

3

It is not specific to any Excel workbook, but if the Excel workbook is having more sheets with more content in it, then we are observing this issue.
Regarding the outputStream, we are storing the workbook inside our DB in the form of a stream, and using an API call we are getting the stream. We found that getting inputStream is not taking time, rather creating Workbook from the same inputStream is taking time.

4

@aduttaPlanful,

We are sorry but we need Excel file(s) to simulate the issue on our end, then we can evaluate/figure it out.

I guess the issue might be there in the module which extracts workbook from database to save into stream. For evaluation, you may use a big/large Excel file (e.g., created in MS Excel manually) having lots of sheets. Now get the file into streams in code and use Workbook object to initialize/create it if you still see the issue?

5

Please find the attached excel file. And also please check the snippet which we are using.

File initialFile = new File("D:\\New folder\\Book1.xlsx");
try {
    InputStream targetStream = new FileInputStream(initialFile);
    System.out.println("Start:: "+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss ").format(Calendar.getInstance().getTime()));
    Workbook workbook = new Workbook(targetStream);
    System.out.println("End:: "+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(Calendar.getInstance().getTime()));

} catch (FileNotFoundException e) {
    e.printStackTrace();
}

The attached workbook is taking 2 seconds to instantiate. And this time is not constant. We have only 5 sheets in this workbook. If we increase the number of sheets then the time is also getting increased.

Can we optimize this further and reduce the time. If only the workbook instantiation is taking 2 sec then it is affecting our overall performance. Please suggest to us some way we can reduce the time.SampleWorkbook.zip (3.6 MB)

6

@aduttaPlanful

Thanks for the template file.

I checked your template file and found it has huge list of formulas in different worksheets. If you open your file into MS Excel manually, you will also notice MS Excel also takes a few seconds to open the file into it. So, taking 2/3 seconds to load such a file that has huge list of inter-dependent and recursive formulas into Aspose.Cells object model is ok or expected. You may improve the timing by further filtering formulas while loading the workbook. But in certain cases, this option might not suit you when you want to resave the file with all the formulas in tact. Anyways, see the following sample code that you may try:
e.g.
Sample code:

        File initialFile = new File("f:\\files\\Book1.xlsx");
        try {
            InputStream targetStream = new FileInputStream(initialFile);
            LoadOptions options = new LoadOptions(LoadFormat.XLSX);
            //load everything except formulas
            options.setLoadFilter(new LoadFilter(~LoadDataFilterOptions.FORMULA));
            System.out.println("Start:: "+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss ").format(Calendar.getInstance().getTime()));
            Workbook workbook = new Workbook(targetStream, options);
            System.out.println("End:: "+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(Calendar.getInstance().getTime()));

        } catch (java.io.FileNotFoundException e) {
            e.printStackTrace();
        }
7

Yes, make sense.

Thank you.

8

@aduttaPlanful,

You are welcome.