User Guide Tips and Tricks Tips for reading large Excel file in .NET, Java and other programming languages

Tips for reading large Excel file in .NET, Java and other programming languages

Concept EasyXLS™ library allows you to read large data from Excel file with fast reading time. The supported Excel file formats are XLSX, XLSM, XLSB and XLS.



If not familiar with EasyXLS Excel library, read first how to read data from Excel file in .NET and how to read data from Excel file in Java. Concept in action The below 5 tips will guide you to read large Excel files.

1. Do not load the entire Excel file, if only data needed Avoid the usage of the methods that load the entire Excel file structure and use the methods that allows reading only the data from the sheets.

Source code sample C#.NET VB.NET C++.NET J#.NET Java PHP ASP C++ VB6 VBS Coldfusion Wrong: ExcelDocument workbook = new ExcelDocument(); workbook.easy_LoadXLSXFile( "Excel.xlsx" ); Correct: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet( "Excel.xlsx" ); Wrong: Dim workbook As New ExcelDocument workbook.easy_LoadXLSXFile( "Excel.xlsx" ) Correct: Dim workbook As New ExcelDocument Dim dataSet As DataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet( "Excel.xlsx" ) Wrong: ExcelDocument *workbook = new ExcelDocument(); workbook->easy_LoadXLSXFile( "Excel.xlsx" ); Correct: ExcelDocument *workbook = new ExcelDocument(); DataSet *dataSet = workbook->easy_ReadXLSXActiveSheet_AsDataSet( "Excel.xlsx" ); Wrong: ExcelDocument workbook = new ExcelDocument(); workbook.easy_LoadXLSXFile( "Excel.xlsx" ); Correct: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet( "Excel.xlsx" ); Wrong: ExcelDocument workbook = new ExcelDocument(); workbook.easy_LoadXLSXFile( "Excel.xlsx" ); Correct: ExcelDocument workbook = new ExcelDocument(); ResultSet resultSet = workbook.easy_ReadXLSXActiveSheet_AsResultSet( "Excel.xlsx" ); Wrong: $workbook = new COM( "EasyXLS.ExcelDocument" ); $workbook->easy_LoadXLSXFile( "Excel.xlsx" ); Correct: $workbook = new COM( "EasyXLS.ExcelDocument" ); $rows = $workbook->easy_ReadXLSXActiveSheet_AsList( "Excel.xlsx" ); Wrong: set workbook = Server.CreateObject( "EasyXLS.ExcelDocument" ) workbook.easy_LoadXLSXFile( "Excel.xlsx" ) Correct: set workbook = Server.CreateObject( "EasyXLS.ExcelDocument" ) set rows = workbook.easy_ReadXLSXActiveSheet_AsList( "Excel.xlsx" ) Wrong: EasyXLS::IExcelDocumentPtr workbook; hr = CoCreateInstance( __uuidof (EasyXLS::ExcelDocument), NULL, CLSCTX_ALL, __uuidof (EasyXLS::IExcelDocument), ( void **) &workbook); workbook->easy_LoadXLSXFile( "Excel.xlsx" ); Correct: EasyXLS::IExcelDocumentPtr workbook; hr = CoCreateInstance( __uuidof (EasyXLS::ExcelDocument), NULL, CLSCTX_ALL, __uuidof (EasyXLS::IExcelDocument), ( void **) &workbook); EasyXLS::IListPtr rows = workbook->easy_ReadXLSXActiveSheet_AsList( "Excel.xlsx" );

Wrong: Set workbook = CreateObject("EasyXLS.ExcelDocument") workbook.easy_LoadXLSXFile("Excel.xlsx") Correct: Set workbook = CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXActiveSheet_AsList("Excel.xlsx") Wrong: set workbook = CreateObject("EasyXLS.ExcelDocument") workbook.easy_LoadXLSXFile("Excel.xlsx") Correct: set workbook = CreateObject("EasyXLS.ExcelDocument") set rows = workbook.easy_ReadXLSXActiveSheet_AsList("Excel.xlsx") Wrong: <cfobject type= "java" class= "EasyXLS.ExcelDocument" name= "workbook" action= "CREATE" > <cfif (workbook.easy_LoadXLSXFile( "C:\Samples\Tutorial28.xlsx" ) is True ) > Correct: <cfobject type= "java" class= "EasyXLS.ExcelDocument" name= "workbook" action= "CREATE" > <cfset rs = workbook.easy_ReadXLSXActiveSheet_AsResultSet( "Excel.xlsx" ) >

See also:



Related methods:

2. Read only ranges of Excel cells, if not entire sheet data needed Try to read only the data that is required and avoid reading excessive data. EasyXLS provides methods that permit reading data only from ranges of cells.

Source code sample C#.NET VB.NET C++.NET J#.NET Java PHP ASP C++ VB6 VBS Coldfusion Wrong: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXSheet_AsDataSet("Excel.xlsx", "Sheet1"); Correct: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXSheet_AsDataSet( "Excel.xlsx", "Sheet1", "A1:C20"); Wrong: Dim workbook As New ExcelDocument Dim dataSet As DataSet = workbook.easy_ReadXLSXSheet_AsDataSet( _ "Excel.xlsx", "Sheet1") Correct: Dim workbook As New ExcelDocument Dim dataSet As DataSet = workbook.easy_ReadXLSXSheet_AsDataSet( _ "Excel.xlsx", "Sheet1", "A1:C20") Wrong: ExcelDocument *workbook = new ExcelDocument(); DataSet *dataSet = workbook->easy_ReadXLSXSheet_AsDataSet("Excel.xlsx", "Sheet1"); Correct: ExcelDocument *workbook = new ExcelDocument(); DataSet *dataSet = workbook->easy_ReadXLSXSheet_AsDataSet( "Excel.xlsx", "Sheet1", "A1:C20"); Wrong: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXSheet_AsDataSet("Excel.xlsx", "Sheet1"); Correct: ExcelDocument workbook = new ExcelDocument(); DataSet dataSet = workbook.easy_ReadXLSXSheet_AsDataSet( "Excel.xlsx", "Sheet1", "A1:C20"); Wrong: ExcelDocument workbook = new ExcelDocument(); ResultSet resultSet = workbook.easy_ReadXLSXSheet_AsResultSet( "Excel.xlsx", "Sheet1"); Correct: ExcelDocument workbook = new ExcelDocument(); ResultSet resultSet = workbook.easy_ReadXLSXSheet_AsResultSet( "Excel.xlsx", "Sheet1", "A1:C20"); Wrong: $workbook = new COM("EasyXLS.ExcelDocument"); $rows = $workbook->easy_ReadXLSXSheet_AsList_3("Excel.xlsx", "Sheet1"); Correct: $workbook = new COM("EasyXLS.ExcelDocument"); $rows = $workbook->easy_ReadXLSXSheet_AsList_5("Excel.xlsx", "Sheet1", "A1:C20"); Wrong: set workbook = Server.CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_3("Excel.xlsx", "Sheet1") Correct: set workbook = Server.CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_5("Excel.xlsx", "Sheet1", "A1:C20") Wrong: EasyXLS::IExcelDocumentPtr workbook; hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument), NULL, CLSCTX_ALL, __uuidof(EasyXLS::IExcelDocument), (void**) &workbook); EasyXLS::IListPtr rows = workbook->easy_ReadXLSXActiveSheet_AsList_3( "Excel.xlsx", "Sheet1"); Correct: EasyXLS::IExcelDocumentPtr workbook; hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument), NULL, CLSCTX_ALL, __uuidof(EasyXLS::IExcelDocument), (void**) &workbook); EasyXLS::IListPtr rows = workbook->easy_ReadXLSXActiveSheet_AsList_5( "Excel.xlsx", "Sheet1", "A1:C20");

Wrong: Set workbook = CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_3("Excel.xlsx", "Sheet1") Correct: Set workbook = CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_5("Excel.xlsx", "Sheet1", "A1:C20") Wrong: set workbook = CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_3("Excel.xlsx", "Sheet1") Correct: set workbook = CreateObject("EasyXLS.ExcelDocument") Set rows = workbook.easy_ReadXLSXSheet_AsList_5("Excel.xlsx", "Sheet1", "A1:C20") Wrong: <cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE"> <cfset rs = workbook.easy_ReadXLSXSheet_AsResultSet("Excel.xlsx", "Sheet1")> Correct: <cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE"> <cfset rs = workbook.easy_ReadXLSXSheet_AsResultSet( "Excel.xlsx", "Sheet1", "A1:C20")>

See also:



Related methods:

3. Recommended file formats XLSB and XLS file formats are binary files and therefore are faster to read by default. EasyXLS recommends to use these file formats for better performances. See also:

Related methods:

4. Read data step by step If the Excel file is very large, the DataSet might be too big in memory when reading the entire data from the Excel file. A solution is to read data range by range and dispose the DataSet and other resources after every use.

5. Java memory heap When using Java programming language, it is recommended to add an extra parameter when running your application. JRE uses 64M of memory by default and your computer performances don't count. You need to increase the memory heap size by using -mx option (run your application using "java -mx512m"). See also: