Export to XLSB file in .NET, Java and other programming languages
Concept
EasyXLS™ library allows you to export data to XLSB file format. The data in cells can be formatted using predefined formats or user-defined formats.
EasyXLS can be successfully used inclusively to export large XLSB files having big volume of data with fast exporting time.
EasyXLS permits you to export Excel files without Excel installed, without Interop or any other additional software installed.
Concept in action
The below example is a common code about how to export to XLSB file. After that, more code samples about exporting data from various standard data structures like DataTable, GridView, DataSet and ResultSet are shown.
// Create an instance of the class that exports XLSB file, having multiple sheets
ExcelDocument workbook = new ExcelDocument(2);
// Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");
// Get the table of the first worksheet that will keep the data
ExcelTable xlsFirstTable =
((ExcelWorksheet)workbook.easy_getSheetAt(0)).easy_getExcelTable();
// Add data to header
for (int column=0; column<5; column++)
{
xlsFirstTable.easy_getCell(0,column).setValue("Column " + (column + 1));
xlsFirstTable.easy_getCell(0,column).setDataType(DataType.STRING);
}
// Add data to cells
for (int row=0; row<100; row++)
{
for (int column=0; column<5; column++)
{
xlsFirstTable.easy_getCell(row+1,column).setValue(
"Data " + (row + 1) + ", " + (column + 1));
xlsFirstTable.easy_getCell(row+1,column).setDataType(DataType.STRING);
}
}
// Export XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
// Confirm generation
String sError = workbook.easy_getError();
if (sError.Equals(""))
Console.Write("\nFile successfully created. Press Enter to Exit...");
else
Console.Write("\nError encountered: " + sError + "\nPress Enter to Exit...");
' Create an instance of the class that exports XLSB file, having multiple sheets
Dim workbook As New ExcelDocument(2)
' Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
' Get the table of the first worksheet that will keep the data
Dim xlsFirstTab As ExcelWorksheet = workbook.easy_getSheetAt(0)
Dim xlsFirstTable = xlsFirstTab.easy_getExcelTable()
' Add data to header
For column As Integer = 0 To 4
xlsFirstTable.easy_getCell(0, column).setValue("Column " & (column + 1))
xlsFirstTable.easy_getCell(0, column).setDataType(DataType.STRING)
Next
' Add data to cells
For row As Integer = 0 To 99
For column As Integer = 0 To 4
xlsFirstTable.easy_getCell(row + 1, column).setValue( _
"Data " & (row + 1) & ", " & (column + 1))
xlsFirstTable.easy_getCell(row + 1, column).setDataType(DataType.STRING)
Next
Next
' Export XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb")
C++
// Create a pointer to the interface that exports XLSB file
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;
if(SUCCEEDED(hr)){
// Create the worksheets
workbook->easy_addWorksheet_2("First tab");
workbook->easy_addWorksheet_2("Second tab");
// Get the table of the first worksheet that will keep the data
EasyXLS::IExcelWorksheetPtr xlsFirstTab =
(EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheetAt(0);
EasyXLS::IExcelTablePtr xlsFirstTable = xlsFirstTab->easy_getExcelTable();
// Add data to header
char* cellValue = (char*)malloc(11*sizeof(char));
char* columnNumber = (char*)malloc(sizeof(char));
for (int column=0; column<5; column++)
{
strcpy(cellValue, "Column ");
_itoa(column+ 1, columnNumber , 10);
xlsFirstTable->easy_getCell(0,column)->setValue(
strcat(cellValue, columnNumber));
xlsFirstTable->easy_getCell(0,column)->setDataType(DATATYPE_STRING);
}
// Add data to cells
char* rowNumber = (char*)malloc(sizeof(char));
for (int row=0; row<100; row++)
{
for (int column=0; column<5; column++)
{
strcpy(cellValue, "Data ");
_itoa(column+ 1, columnNumber , 10);
_itoa(row + 1, rowNumber , 10);
strcat(cellValue, rowNumber);
strcat(cellValue, ", ");
strcat(cellValue, columnNumber);
xlsFirstTable->easy_getCell(row+1,column)->setValue(cellValue);
xlsFirstTable->easy_getCell(row+1,column)->setDataType(DATATYPE_STRING);
}
}
// Export XLSB file
workbook->easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
//Confirm generation
_bstr_t sError = workbook->easy_getError();
if (strcmp(sError, "") == 0){
printf("\nFile successfully created. Press Enter to Exit...");
}
else{
printf("\nError encountered: %s", (LPCSTR)sError);
}
// Dispose memory
workbook->Dispose();
}
else{
printf("Object is not available!");
}
Click here to see Continuous Code ListingC++.NET
// Create an instance of the class that exports XLSB file, having multiple sheets
ExcelDocument *workbook = new ExcelDocument(2);
// Set the sheet names
workbook->easy_getSheetAt(0)->setSheetName("First tab");
workbook->easy_getSheetAt(1)->setSheetName("Second tab");
// Get the table of the first worksheet that will keep the data
ExcelWorksheet *xlsFirstTab =
__try_cast<ExcelWorksheet*>(workbook->easy_getSheetAt(0));
ExcelTable *xlsFirstTable = xlsFirstTab->easy_getExcelTable();
// Add data to header
for (int column=0; column<5; column++)
{
xlsFirstTable->easy_getCell(0,column)->setValue(
String::Concat("Column ",(column + 1).ToString()));
xlsFirstTable->easy_getCell(0,column)->setDataType(DataType::STRING);
}
// Add data to cells
for (int row=0; row<100; row++)
{
for (int column=0; column<5; column++)
{
xlsFirstTable->easy_getCell(row+1,column)->setValue(String::Concat(
"Data ", (row + 1).ToString(), ", ", (column + 1).ToString()));
xlsFirstTable->easy_getCell(row+1,column)->setDataType(DataType::STRING);
}
}
// Export XLSB file
workbook->easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
Click here to see Continuous Code Listing
// Create an instance of the class that exports XLSB file, having multiple sheets
ExcelDocument workbook = new ExcelDocument(2);
// Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");
// Get the table of the first worksheet that will keep the data
ExcelTable xlsFirstTable =
((ExcelWorksheet)workbook.easy_getSheetAt(0)).easy_getExcelTable();
// Add data to header
for (int column=0; column<5; column++)
{
xlsFirstTable.easy_getCell(0,column).setValue("Column " + (column + 1));
xlsFirstTable.easy_getCell(0,column).setDataType(DataType.STRING);
}
// Add data to cells
for (int row=0; row<100; row++)
{
for (int column=0; column<5; column++)
{
xlsFirstTable.easy_getCell(row+1,column).setValue(
"Data " + (row + 1) + ", " + (column + 1));
xlsFirstTable.easy_getCell(row+1,column).setDataType(DataType.STRING);
}
}
// Export XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
// Confirm generation
if (workbook.easy_getError().equals(""))
System.out.println("File successfully created.");
else
System.out.println("Error encountered: " + workbook.easy_getError());
// Create an instance of the class that exports XLSB file
$workbook = new COM("EasyXLS.ExcelDocument");
// Create the worksheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");
// Get the table of the first worksheet that will keep the data
$xlsFirstTable = $workbook->easy_getSheetAt(0)->easy_getExcelTable();
// Add data to header
for ($column=0; $column<5; $column++)
{
$xlsFirstTable->easy_getCell(0,$column)->setValue("Column " . ($column + 1));
$xlsFirstTable->easy_getCell(0,$column)->setDataType($DATATYPE_STRING);
}
// Add data to cells
for ($row=0; $row<100; $row++)
{
for ($column=0; $column<5; $column++)
{
$xlsFirstTable->easy_getCell($row+1,$column)->setValue(
"Data ".($row + 1).", ".($column + 1));
$xlsFirstTable->easy_getCell($row+1,$column)->setDataType($DATATYPE_STRING);
}
}
// Export XLSB file
$workbook->easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
// Confirm generation
if ($workbook->easy_getError() == "")
echo "File successfully created.";
else
echo "Error encountered: " . $workbook->easy_getError();
' Create an instance of the class that exports XLSB file
set workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Create the worksheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
' Get the table of the first worksheet that will keep the data
Set xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()
' Add data to header
for column = 0 to 4
xlsFirstTable.easy_getCell(0,column).setValue("Column " & (column + 1))
xlsFirstTable.easy_getCell(0,column).setDataType(DATATYPE_STRING)
next
' Add data to cells
for row = 0 to 99
for column = 0 to 4
xlsFirstTable.easy_getCell(row+1,column).setValue( _
"Data " & (row + 1) & ", " & (column + 1))
xlsFirstTable.easy_getCell(row+1,column).setDataType(DATATYPE_STRING)
next
next
' Export XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Export to Excel.xlsb");
' Confirm generation
if workbook.easy_getError() = "" then
response.write("File successfully created.")
else
response.write("Error encountered: " + workbook.easy_getError())
end if
' Create an instance of the class that exports XLSB file
Set workbook = CreateObject("EasyXLS.ExcelDocument")
' Create the worksheets
workbook.easy_addWorksheet_2 ("First tab")
workbook.easy_addWorksheet_2 ("Second tab")
' Get the table of the first worksheet that will keep the data
Set xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()
' Add data to header
For Column = 0 To 4
xlsFirstTable.easy_getCell(0, Column).setValue ("Column " & (Column + 1))
xlsFirstTable.easy_getCell(0, Column).setDataType (DataType.DATATYPE_STRING)
Next
' Add data to cells
For row = 0 To 99
For Column = 0 To 4
xlsFirstTable.easy_getCell(row + 1, Column).setValue ( _
"Data " & (row + 1) & ", " & (Column + 1))
xlsFirstTable.easy_getCell(row + 1, Column).setDataType ( _
DataType.DATATYPE_STRING)
Next
Next
' Export XLSB file
workbook.easy_WriteXLSBFile "C:\\Samples\\Export to Excel.xlsb"
' Confirm generation
If workbook.easy_getError() = "" Then
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "File successfully created."
Else
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Error: " & _
workbook.easy_getError()
End If
' Create an instance of the class that exports XLSB file
Set workbook = CreateObject("EasyXLS.ExcelDocument")
' Create the worksheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
' Get the table of the first worksheet that will keep the data
Set xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()
' Add data to header
For Column = 0 To 4
xlsFirstTable.easy_getCell(0,column).setValue("Column " & (Column + 1))
xlsFirstTable.easy_getCell(0,column).setDataType(DATATYPE_STRING)
Next
' Add data to cells
For row = 0 To 99
For column = 0 To 4
xlsFirstTable.easy_getCell(row+1,column).setValue( _
"Data " & (row + 1) & ", " & (column + 1))
xlsFirstTable.easy_getCell(row+1,column).setDataType(DATATYPE_STRING)
Next
Next
' Export XLSB file
workbook.easy_WriteXLSBFile "C:\\Samples\\Export to Excel.xlsb"
' Confirm generation
dim sError
sError = workbook.easy_getError()
if sError = "" then
WScript.StdOut.Write(vbcrlf & _
"File successfully created. Press Enter to exit...")
else
WScript.StdOut.Write(vbcrlf & "Error: " & sError)
end if
.NET:
# Create an instance of the class that exports Excel files, having two sheets
workbook = ExcelDocument(2)
# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()
# Add data in cells for report header
for column in range(5):
xlsFirstTable.easy_getCell(0,column).setValue("Column " + str(column + 1))
xlsFirstTable.easy_getCell(0,column).setDataType(DataType.STRING)
# Add data in cells for report values
for row in range(100):
for column in range(5):
xlsFirstTable.easy_getCell(row+1,column).setValue("Data " + str(row + 1) + ", " + str(column + 1))
xlsFirstTable.easy_getCell(row+1,column).setDataType(DataType.STRING)
# Export the XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Tutorial29 - export XLSB file.xlsb")
Click here to see Continuous Code ListingJava:
# Create an instance of the class that exports Excel files, having two sheets
workbook = gateway.jvm.ExcelDocument(2)
# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()
# Add data in cells for report header
for column in range(5):
xlsFirstTable.easy_getCell(0,column).setValue("Column " + str(column + 1))
xlsFirstTable.easy_getCell(0,column).setDataType(gateway.jvm.DataType.STRING)
# Add data in cells for report values
for row in range(100):
for column in range(5):
xlsFirstTable.easy_getCell(row+1,column).setValue("Data " + str(row + 1) + ", " + str(column + 1))
xlsFirstTable.easy_getCell(row+1,column).setDataType(gateway.jvm.DataType.STRING)
# Export the XLSB file
workbook.easy_WriteXLSBFile("C:\\Samples\\Tutorial29 - export XLSB file.xlsb")
Click here to see Continuous Code Listing
The screen shot below represents the exported data to XLSB file generated by the code sample above. The file has two worksheets (First Tab and Second Tab). The values and the data type of the cells from the first worksheet are set.
Formatting cells
EasyXLS™ enables you to format cells, rows and columns in order to set the fonts and colors.