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 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.how to read data from Excel file in .NET and how to read data from Excel file in Java .
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
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" )
C++
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" );
C++.NET
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" );
.NET:
Wrong:
workbook = ExcelDocument()
workbook.easy_LoadXLSXFile("Excel.xlsx" )
Correct:
workbook = ExcelDocument()
dataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet("Excel.xlsx" )
Java:
Wrong:
workbook = gateway.jvm.ExcelDocument()
workbook.easy_LoadXLSXFile("Excel.xlsx" )
Correct:
workbook = gateway.jvm.ExcelDocument()
resultSet = workbook.easy_ReadXLSXActiveSheet_AsResultSet("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" );
.NET:
Wrong:
$workbook = new COM("EasyXLS.ExcelDocument" );
$workbook->easy_LoadXLSXFile("Excel.xlsx" );
Correct:
$workbook = new COM("EasyXLS.ExcelDocument" );
$rows = $workbook->easy_ReadXLSXActiveSheet_AsList("Excel.xlsx" );
Java:
Wrong:
$workbook = new java("EasyXLS.ExcelDocument");
$workbook->easy_LoadXLSXFile("Excel.xlsx" );
Correct:
$workbook = new java("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:
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" )>
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
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" )
C++
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" );
C++.NET
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" );
.NET:
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" );
Java:
Wrong:
$workbook = new java("EasyXLS.ExcelDocument" );
$rows = $workbook->easy_ReadXLSXSheet_AsList("Excel.xlsx" , "Sheet1" );
Correct:
$workbook = new java("EasyXLS.ExcelDocument" );
$rows = $workbook->easy_ReadXLSXSheet_AsList(
"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:
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" )>
.NET:
Wrong:
workbook = ExcelDocument()
dataSet = workbook.easy_ReadXLSXActiveSheet_AsDataSet("Excel.xlsx" , "Sheet1" )
Correct:
workbook = ExcelDocument()
dataSet = workbook.easy_ReadXLSXSheet_AsDataSet("Excel.xlsx" , "Sheet1" , "A1:C20" )
Java:
Wrong:
workbook = gateway.jvm.ExcelDocument()
resultSet = workbook.easy_ReadXLSXActiveSheet_AsResultSet("Excel.xlsx" , "Sheet1" )
Correct:
workbook = gateway.jvm.ExcelDocument()
resultSet = workbook.easy_ReadXLSXSheet_AsResultSet("Excel.xlsx" , "Sheet1" , "A1:C20" )
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.
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").
