|
User Guide EasyXLS Basics Import files Import Excel file to List in .NET, Java and other programming languages
|
|
|
Import Excel file to List in .NET, Java and other programming languages
|
Concept
EasyXLS™
library allows you to import Excel data to List. The data can be imported from an Excel sheet or from the active Excel sheet. The entire sheet data or only data from ranges of cells can be imported.
Concept in action
The below example shows how to import Excel to List in .NET, Java, PHP, ASP, C++, VB6, VBS and Coldfusion.
|
Source code sample
|
|
|
|
|
|
|
// Create an instance of the class that imports Excel files
ExcelDocument workbook = new ExcelDocument();
// Import Excel file to List
EasyXLS.Util.List rows =
workbook.easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
if (workbook.easy_getError() == "")
{
// Display imported List values
EasyXLS.Util.List row;
for (int rowIndex=0; rowIndex < rows.size(); rowIndex++)
{
row = (EasyXLS.Util.List)rows.elementAt(rowIndex);
for (int cellIndex=0; cellIndex < row.size(); cellIndex++)
{
Console.WriteLine("At row " + (rowIndex + 1) +
", column " + (cellIndex + 1)
+ " the value is '" + row.elementAt(cellIndex));
}
}
}
else
Console.Write("Error importing Excel file C:\\Samples\\Tutorial09.xls " +
workbook.easy_getError());
|
|
' Create an instance of the class that imports Excel files
Dim workbook As New ExcelDocument
' Import Excel file to List
Dim rows = workbook.easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls")
' Confirm Excel file import
If (workbook.easy_getError().Equals("")) Then
' Display imported List values
Dim row
For rowIndex As Integer = 0 To rows.size() - 1
row = rows.elementAt(rowIndex)
For cellIndex As Integer = 0 To row.size() - 1
Console.WriteLine(vbCrLf & "At row " & (rowIndex + 1) & ", column " _
& (cellIndex + 1) & " the value is '" & row.elementAt(cellIndex))
Next
Next
Else
Console.Write(vbCrLf & _
"Error importing Excel file C:\\Samples\\Tutorial09.xls " & _
workbook.easy_getError())
End If
|
|
// Create an instance of the class that imports Excel files
ExcelDocument *workbook = new ExcelDocument();
// Import Excel file to List
EasyXLS::Util::List *rows =
workbook->easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
if (workbook->easy_getError()->Equals(""))
{
// Display imported List values
EasyXLS::Util::List *row;
for (int rowIndex=0; rowIndex < rows->size(); rowIndex++)
{
row = __try_cast(rows->elementAt(rowIndex));
for (int cellIndex=0; cellIndex < row->size(); cellIndex++)
{
Console::Write(String::Concat("At row ", (rowIndex + 1).ToString(),
", column ", (cellIndex + 1).ToString()));
Console::WriteLine(String::Concat(" the value is '",
row->elementAt(cellIndex)->ToString()));
}
}
}
else
Console::WriteLine(String::Concat(
"Error importing Excel file C:\\Samples\\Tutorial09.xls ",
workbook->easy_getError()));
|
|
// Create an instance of the class that imports Excel files
ExcelDocument workbook = new ExcelDocument();
// Import Excel file to List
EasyXLS.Util.List rows =
workbook.easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
if (workbook.easy_getError() == "")
{
// Display imported List values
EasyXLS.Util.List row;
for (int rowIndex=0; rowIndex < rows.size(); rowIndex++)
{
row = (EasyXLS.Util.List)rows.elementAt(rowIndex);
for (int cellIndex=0; cellIndex < row.size(); cellIndex++)
{
Console.WriteLine("At row " + (rowIndex + 1) +
", column " + (cellIndex + 1) +
" the value is '" + row.elementAt(cellIndex));
}
}
}
else
Console.Write("Error importing Excel file C:\\Samples\\Tutorial09.xls " +
workbook.easy_getError());
|
|
// Create an instance of the class that imports Excel files
ExcelDocument workbook = new ExcelDocument();
// Import Excel file to List
EasyXLS.Util.List rows =
workbook.easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
if (workbook.easy_getError().equals(""))
{
// Display the values
EasyXLS.Util.List row;
for (int rowIndex=0; rowIndex < rows.size(); rowIndex++)
{
row = (EasyXLS.Util.List)rows.elementAt(rowIndex);
for (int cellIndex=0; cellIndex < row.size(); cellIndex++)
{
System.out.println("At row " + (rowIndex + 1) +
", column " + (cellIndex + 1) +
" the value is '" + row.elementAt(cellIndex));
}
}
}
else
System.out.println("Error importing Excel file C:\\Samples\\Tutorial09.xls " +
workbook.easy_getError());
|
|
// Create an instance of the class that imports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Import Excel file to List
$rows = $workbook->easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
if ($workbook->easy_getError() == "")
{
// Display imported List values
for ($rowIndex=0; $rowIndex<$rows->size(); $rowIndex++)
{
$row = $rows->elementAt($rowIndex);
for ($cellIndex=0; $cellIndex<$row->size(); $cellIndex++)
{
echo "At row ".($rowIndex + 1).", column ".($cellIndex + 1).
" the value is '".$row->elementAt($cellIndex)."'<br>";
}
}
}
else
echo "Error importing Excel file C:\Samples\Tutorial09.xls " .
$workbook->easy_getError();
|
Click here to see Continuous Code Listing
|
|
|
|
' Create an instance of the class that imports Excel files
set workbook = Server.CreateObject("EasyXLS.ExcelDocument")
'Import Excel file to List
Set rows = workbook.easy_ReadXLSActiveSheet_AsList("C:\Samples\Tutorial09.xls")
' Confirm Excel file import
if workbook.easy_getError() = "" then
' Display imported List values
for rowIndex = 0 to rows.size() - 1
Set row = rows.elementAt(rowIndex)
for cellIndex = 0 to row.size - 1
response.write("At row " & (rowIndex + 1) & _
", column " & (cellIndex + 1) & _
" the value is '" & row.elementAt(cellIndex) & "'< br>")
next
next
else
response.Write("Error importing Excel file C:\Samples\Tutorial09.xls " & _
workbook.easy_getError())
end if
|
Click here to see Continuous Code Listing
|
|
|
|
// Create an instance of the class that imports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook);
// Import Excel file to List
EasyXLS::IListPtr rows =
workbook->easy_ReadXLSActiveSheet_AsList("C:\\Samples\\Tutorial09.xls");
// Confirm Excel file import
_bstr_t sError = workbook->easy_getError();
if (strcmp(sError, "") == 0)
{
// Display imported List values
for (int rowIndex=0; rowIndex<rows->size(); rowIndex++)
{
EasyXLS::IListPtr row = (EasyXLS::IListPtr) rows->elementAt(rowIndex);
for (int cellIndex=0; cellIndex<row->size(); cellIndex++)
{
printf("At row %d, column %d the value is '%s'\n",
(rowIndex+ 1), (cellIndex+ 1),
(LPCSTR)((_bstr_t)row->elementAt(cellIndex)));
}
}
printf("\nPress Enter to exit ...");
}
else
{
printf("\nError importing Excel file C:\\Samples\\Tutorial09.xls %s\n",
(LPCSTR)sError);
}
|
Click here to see Continuous Code Listing
|
|
|
|
' Create an instance of the class that imports Excel files
Set workbook = CreateObject("EasyXLS.ExcelDocument")
'Import Excel file to List
Set rows = workbook.easy_ReadXLSActiveSheet_AsList("C:\Samples\Tutorial09.xls")
' Confirm Excel file import
If workbook.easy_getError() = "" Then
' Display imported List values
For rowIndex = 0 To rows.Size() - 1
Set row = rows.elementAt(rowIndex)
For cellIndex = 0 To row.Size - 1
Me.Text1 = Me.Text1 & _
"At row " & (rowIndex + 1) & ", column " & (cellIndex + 1) & _
" the value is '" & row.elementAt(cellIndex) & "'" & vbCrLf
Next
Next
Else
Me.Text1 = Me.Text1 & _
vbCrLf & "Error importing Excel file C:\Samples\Tutorial09.xls " & _
vbCrLf & workbook.easy_getError()
End If
|
Click here to see Continuous Code Listing
|
|
|
|
' Create an instance of the class that imports Excel files
set workbook = CreateObject("EasyXLS.ExcelDocument")
'Import Excel file to List
Set rows = workbook.easy_ReadXLSActiveSheet_AsList("C:\Samples\Tutorial09.xls")
' Confirm Excel file import
if workbook.easy_getError() = "" then
' Display imported List values
For rowIndex = 0 To rows.Size() - 1
Set row = rows.elementAt(rowIndex)
For cellIndex = 0 To row.Size - 1
WScript.StdOut.WriteLine("At row " & (rowIndex + 1) & _
", column " & (cellIndex + 1) & _
" the value is '" & row.elementAt(cellIndex) & "'")
Next
Next
else
WScript.StdOut.Write(vbcrlf & _
"Error importing Excel file C:\Samples\Tutorial09.xls " & _
workbook.easy_getError())
end if
|
Click here to see Continuous Code Listing
|
|
|
|
<!-- Create an instance of the class that imports Excel files -->
<cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE">
<!-- Import Excel file to List -->
<cfset rows = workbook.easy_ReadXLSActiveSheet_AsList("C:\Samples\Tutorial09.xls")>
<!-- Confirm Excel file import -->
<cfset sError = workbook.easy_getError()>
<CFIF (sError IS "")>
<cfoutput>
<!-- Display imported List values -->
<cfobject type="java" class="EasyXLS.Util.List" name="row" action="CREATE">
<cfloop from="0" to="#rows.size()-1#" index="rowIndex">
<cfset row = rows.elementAt(rowIndex)>
<cfloop from="0" to="#row.size()-1#" index="cellIndex">
<cfoutput>
At row #evaluate(rowIndex + 1)#, column #evaluate(cellIndex + 1)#
the value is '#row.elementAt(cellIndex)#'<br>
</cfoutput>
</cfloop>
</cfloop>
</cfoutput>
<CFELSE>
<cfoutput>
Error importing Excel file: #sError#
</cfoutput>
</CFIF>
|
|
|
|
|
This screen shot shows the imported data from Excel sheet into List.
Import Excel file having one sheet to List
Importing the Excel file data to List, if the Excel file has only one sheet, is the easiest approach. The Excel data can be imported with one single line of code using:
- ExcelDocument.easy_ReadXLSActiveSheet_AsList method for XLS file
- ExcelDocument.easy_ReadXLSXActiveSheet_AsList method for XLSX file
- ExcelDocument.easy_ReadXLSBActiveSheet_AsList method for XLSB file
The above code sample shows how to achieve this goal.
|
Import Excel file having multiple sheets to List
There are three approaches for importing data from an Excel file with multiple sheets.
I. Usually the first sheet is the active sheet inside an Excel file. If this is your case or if you are importing data from another active sheet use:
- ExcelDocument.easy_ReadXLSActiveSheet_AsList method for XLS file
- ExcelDocument.easy_ReadXLSXActiveSheet_AsList method for XLSX file
- ExcelDocument.easy_ReadXLSBActiveSheet_AsList method for XLSB file
II. For importing data from an Excel sheet and the name of the sheet is known, EasyXLS recommends the use of:
- ExcelDocument.easy_ReadXLSSheet_AsList method for XLS file
- ExcelDocument.easy_ReadXLSXSheet_AsList method for XLSX file
- ExcelDocument.easy_ReadXLSBSheet_AsList method for XLSB file
III. For importing data from an Excel sheet and the name of the sheet is not known, the first step is to find the sheet by reading the Excel file using:
- ExcelDocument.easy_LoadXLSFile method for XLS file
- ExcelDocument.easy_LoadXLSXFile method for XLSX file
- ExcelDocument.easy_LoadXLSBFile method for XLSB file
Then, import the sheet data in List using ExcelDocument.easy_ReadExcelWorksheet_AsList method.
|
Import range of Excel cells to List
EasyXLS enables you to import Excel data to List either from the entire sheet or from a range of cells. Importing only a range of cells is a very useful option especially for large Excel files because it reduces the speed of the import process.
In order to import multiple cell ranges at once from Excel sheet, the range parameter must be passed to the method as union of ranges (multiple ranges separated by comma).
All the methods that allow importing Excel to List have parameters that permit importing only ranges of cells.
|
Import XLSX, XLSB, XLSM and XLS files to List
This code sample shows how to import XLS file to List. In a similar way, you can import XLSX, XLSM, XLSB files to List using:
- ExcelDocument.easy_ReadXLSXActiveSheet_AsList or ExcelDocument.easy_ReadXLSXSheet_AsList method for XLSX and XLSM files
- ExcelDocument.easy_ReadXLSBActiveSheet_AsList or ExcelDocument.easy_ReadXLSBSheet_AsList method for XLSB files
|
Getting started with EasyXLS Excel library
To download the trial version of EasyXLS Excel Library, press the below button:
|
|
If you already own a license key, you may login and download EasyXLS from your account.
|
|
|
|
Available for: Professional, Excel Reader
|
