Taking lot of time to open a workbook

Question

I am trying to read a excel file(xlsm as have macros) everything seem to be correct but somehow code taking lot of time to execute. Here is my code:

public Map<String, ReportParameter> getParameters(String excelFileName) {

        File excelFile = new File(repo.getRepositoryPath() + File.separator + excelFileName);
        try (FileInputStream input_document = new FileInputStream(excelFile);
             XSSFWorkbook workbook = new XSSFWorkbook(input_document)) {
            XSSFSheet sheet = workbook.getSheet("parametre");
            Map<String, String> resultMap = new HashMap<>();
            Map<String, Integer> indexMap = new HashMap<>();

            int pos = 1;
            for (Row row : sheet) {
                Cell cell = row.getCell(0);
                if(cell == null) {
                    continue;
                }

                indexMap.put(cell.getStringCellValue(), pos++);
                resultMap.put(cell.getStringCellValue(), row.getCell(1).getStringCellValue());

            }

            return convertParameterTypesToReportParameters(resultMap, indexMap);
        } catch (Exception e) {
             throw new RuntimeException(e);
        }

I also try to use OPCPackage but have same result. Can someone give me suggestion why it is taking lot of time and is there anything which can improve performance.

First determine what exactly takes the long time. If even creating the `XSSFWorkbook` using `XSSFWorkbook workbook = new XSSFWorkbook(input_document)` takes too long, then there are not much options for optimizing. Since you seems only need reading, maybe [Excel Streaming Reader](https://github.com/monitorjbl/excel-streaming-reader) is faster. — Axel Richter, Jan 04 '19 at 17:37
Yes creating a XSSFWorkbook() instance taking a lot of time. Excel Streaming Reader seems to be interesting i will try with that as not found any solution yet. — user565, Jan 04 '19 at 17:53

Lord Elrond · Answer 1 · 2019-01-04T17:03:44.057

0

Try this:

sub test()

Dim x as long, y as int 
'all of your Dims here


Application.ScreenUpdating = False

'
'
'your code here
'
'

Application.ScreenUpdating = True

end sub

edited Jan 04 '19 at 17:03

answered Jan 04 '19 at 16:56

Lord Elrond

13,430
7
40
80

Sorry i don't understand properly. Do you mean in before start reading and after end i need to use these ? – user565 Jan 04 '19 at 16:59
After reading more about "Application.ScreenUpdating", well i don't think so this is a case because there is no screen updating involve when POI read an excel file programmatically. – user565 Jan 04 '19 at 17:05

Taking lot of time to open a workbook

1 Answers1