User Guide Tips and Tricks Tips for exporting large Excel file in .NET, Java and other programming languages
Tips for exporting large Excel file in .NET, Java and other programming languages EasyXLS™ library allows you to export large data to Excel file with fast exporting time. The supported Excel file formats are XLSX, XLSM, XLSB, XLS and XML Spreadsheet, CSV, TXT, HTML as text files.how to export data to Excel file in .NET , how to export data to Excel file in Java and how to export data in PHP and Classic ASP .
The below 7 tips will guide you to export large Excel files.
1. Cell formatting Use as much as possible ExcelStyle class when formatting cells. Setting the format properties for each cell increases the amount of used memory, affecting the performance when exporting large Excel files. It is recommended to create ExcelStyle objects to set the formatting style for cells.
Source code sample
Wrong:
ExcelCell xlsCell = new ExcelCell();
xlsCell.setFont("Verdana" );
xlsCell.setFontSize(8);
xlsCell.setBold(true );
xlsCell.setItalic(true );
xlsCell.setForeground(Color.Yellow);
xlsCell.setBackground(Color.Black);
Correct:
ExcelCell xlsCell = new ExcelCell();
ExcelStyle xlsStyle = new ExcelStyle("Verdana" , 8, true , true , Color.Yellow);
xlsStyle.setBackground(Color.Black);
xlsCell.setStyle(xlsStyle);
Wrong:
Dim xlsCell As New ExcelCell()
xlsCell.setFont("Verdana" )
xlsCell.setFontSize(8)
xlsCell.setBold(True )
xlsCell.setItalic(True )
xlsCell.setForeground(Color.Yellow)
xlsCell.setBackground(Color.Black)
Correct:
Dim xlsCell As New ExcelCell()
Dim xlsStyle As New ExcelStyle("Verdana" , 8, True , True , Color.Yellow)
xlsStyle.setBackground(Color.Black)
xlsCell.setStyle(xlsStyle)
C++
Wrong:
EasyXLS::IExcelCellPtr xlsCell;
hr = CoCreateInstance(__uuidof (EasyXLS::ExcelCell),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IExcelCell),
(void **) &xlsCell);
xlsCell->setFont("Verdana" );
xlsCell->setFontSize(8);
xlsCell->setBold(True);
xlsCell->setItalic(True);
xlsCell->setForeground(COLOR_YELLOW);
xlsCell->setBackground(COLOR_BLACK);
Correct:
EasyXLS::IExcelCellPtr xlsCell;
hr = CoCreateInstance(__uuidof (EasyXLS::ExcelCell),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IExcelCell),
(void **) &xlsCell);
EasyXLS::IExcelStylePtr xlsStyle;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelStyle),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IExcelStyle),
(void **) &xlsStyle) ;
xlsStyle->setFont("Verdana" );
xlsStyle->setFontSize(8);
xlsStyle->setBold(True);
xlsStyle->setItalic(True);
xlsStyle->setForeground(COLOR_YELLOW);
xlsStyle->setBackground(COLOR_BLACK);
xlsCell->setStyle(xlsStyle);
C++.NET
Wrong:
ExcelCell *xlsCell = new ExcelCell();
xlsCell->setFont("Verdana" );
xlsCell->setFontSize(8);
xlsCell->setBold(true );
xlsCell->setItalic(true );
xlsCell->setForeground(Color::Yellow);
xlsCell->setBackground(Color::Black);
Correct:
ExcelCell *xlsCell = new ExcelCell();
ExcelStyle *xlsStyle = new ExcelStyle("Verdana" , 8, true , true , Color::Yellow);
xlsStyle->setBackground(Color::Black);
xlsCell->setStyle(xlsStyle);
Wrong:
ExcelCell xlsCell = new ExcelCell();
xlsCell.setFont("Verdana" );
xlsCell.setFontSize(8);
xlsCell.setBold(true );
xlsCell.setItalic(true );
xlsCell.setForeground(Color.YELLOW);
xlsCell.setBackground(Color.BLACK);
Correct:
ExcelCell xlsCell = new ExcelCell();
ExcelStyle xlsStyle = new ExcelStyle("Verdana" , 8, true , true , Color.YELLOW);
xlsStyle.setBackground(Color.BLACK);
xlsCell.setStyle(xlsStyle);
.NET:
Wrong:
$xlsCell = new COM("EasyXLS.ExcelCell" );
$xlsCell->setFont("Verdana" );
$xlsCell->setFontSize(8);
$xlsCell->setBold(True );
$xlsCell->setItalic(True );
$xlsCell->setForeground((int )$COLOR_YELLOW);
$xlsCell->setBackground((int )$COLOR_BLACK);
Correct:
$xlsCell = new COM("EasyXLS.ExcelCell" );
$xlsStyle = new COM("EasyXLS.ExcelStyle" );
$xlsStyle->setFont("Verdana" );
$xlsStyle->setFontSize(8);
$xlsStyle->setBold(True );
$xlsStyle->setItalic(True );
$xlsStyle->setForeground((int )$COLOR_YELLOW);
$xlsStyle->setBackground((int )$COLOR_BLACK);
$xlsCell->setStyle($xlsStyle);
Java:
Wrong:
$xlsCell = new java("EasyXLS.ExcelCell" );
$xlsCell->setFont("Verdana" );
$xlsCell->setFontSize(8);
$xlsCell->setBold(True );
$xlsCell->setItalic(True );
$xlsCell->setForeground(java("java.awt.Color" )->YELLOW);
$xlsCell->setBackground(java("java.awt.Color" )->BLACK);
Correct:
$xlsCell = new java("EasyXLS.ExcelCell" );
$xlsStyle = new java("EasyXLS.ExcelStyle" );
$xlsStyle->setFont("Verdana" );
$xlsStyle->setFontSize(8);
$xlsStyle->setBold(True );
$xlsStyle->setItalic(True );
$xlsStyle->setForeground(java("java.awt.Color" )->YELLOW);
$xlsStyle->setBackground(java("java.awt.Color" )->BLACK);
$xlsCell->setStyle($xlsStyle);
Wrong:
set xlsCell = Server.CreateObject("EasyXLS.ExcelCell" )
xlsCell.setFont("Verdana" )
xlsCell.setFontSize(8)
xlsCell.setBold(True )
xlsCell.setItalic(True )
xlsCell.setForeground(CLng (COLOR_YELLOW))
xlsCell.setBackground(CLng (COLOR_BLACK))
Correct:
set xlsCell = Server.CreateObject("EasyXLS.ExcelCell" )
set xlsStyle = Server.CreateObject("EasyXLS.ExcelStyle" )
xlsStyle.setFont("Verdana" )
xlsStyle.setFontSize(8)
xlsStyle.setBold(True )
xlsStyle.setItalic(True )
xlsStyle.setForeground(CLng (COLOR_YELLOW))
xlsStyle.setBackground(CLng (COLOR_BLACK))
xlsCell.setStyle(xlsStyle)
Wrong:
Set xlsCell = CreateObject("EasyXLS.ExcelCell")
xlsCell.setFont("Verdana")
xlsCell.setFontSize(8)
xlsCell.setBold(True )
xlsCell.setItalic(True )
xlsCell.setForeground (CLng (Color.COLOR_YELLOW))
xlsCell.setBackground (CLng (Color.COLOR_BLACK))
Correct:
Set xlsCell = CreateObject("EasyXLS.ExcelCell")
Set xlsStyle = CreateObject("EasyXLS.ExcelStyle")
xlsStyle.setFont("Verdana")
xlsStyle.setFontSize(8)
xlsStyle.setBold(True )
xlsStyle.setItalic(True )
xlsStyle.setForeground (CLng (Color.COLOR_YELLOW))
xlsStyle.setBackground (CLng (Color.COLOR_BLACK))
xlsCell.setStyle(xlsStyle)
Wrong:
set xlsCell = CreateObject("EasyXLS.ExcelCell")
xlsCell.setFont("Verdana")
xlsCell.setFontSize(8)
xlsCell.setBold(True )
xlsCell.setItalic(True )
xlsCell.setForeground(CLng (YELLOW))
xlsCell.setBackground(CLng (BLACK))
Correct:
set xlsCell = CreateObject("EasyXLS.ExcelCell")
set xlsStyle = CreateObject("EasyXLS.ExcelStyle")
xlsStyle.setFont("Verdana")
xlsStyle.setFontSize(8)
xlsStyle.setBold(True )
xlsStyle.setItalic(True )
xlsStyle.setForeground(CLng (YELLOW))
xlsStyle.setBackground(CLng (BLACK))
xlsCell.setStyle(xlsStyle)
Wrong:
<cfobject type= "java" class= "EasyXLS.ExcelCell" name= "xlsCell" action= "CREATE" >
<cfset xlsCell.setFont("Verdana" )>
<cfset xlsCell.setFontSize(8 )>
<cfset xlsCell.setBold(true )>
<cfset xlsCell.setItalic(true )>
<cfset xlsCell.setForeground(Color.yellow)>
<cfset xlsCell.setBackground(Color.black)>
Correct:
<cfobject type= "java" class= "EasyXLS.ExcelCell" name= "xlsCell" action= "CREATE" >
<cfobject type= "java" class= "EasyXLS.ExcelStyle" name= "xlsStyle" action= "CREATE" >
<cfset xlsStyle.setFont("Verdana" )>
<cfset xlsStyle.setFontSize(8 )>
<cfset xlsStyle.setBold(true )>
<cfset xlsStyle.setItalic(true )>
<cfset xlsStyle.setForeground(Color.yellow)>
<cfset xlsStyle.setBackground(Color.black)>
<cfset xlsCell.setStyle(xlsStyle)>
.NET:
Wrong:
xlsCell = ExcelCell()
xlsCell.setFont("Verdana" )
xlsCell.setFontSize(8)
xlsCell.setBold(True )
xlsCell.setItalic(True )
xlsCell.setForeground(Color.Yellow)
xlsCell.setBackground(Color.Black)
Correct:
xlsCell = ExcelCell()
xlsStyle = ExcelStyle("Verdana" , 8, True , True , Color.Yellow)
xlsStyle.setBackground(Color.Black)
xlsCell.setStyle(xlsStyle)
Java:
Wrong:
xlsCell = gateway.jvm.ExcelCell()
xlsCell.setFont("Verdana")
xlsCell.setFontSize(8)
xlsCell.setBold(True)
xlsCell.setItalic(True)
xlsCell.setForeground(gateway.jvm.Color.YELLOW)
xlsCell.setBackground(gateway.jvm.Color.BLACK)
Correct:
xlsCell = gateway.jvm.ExcelCell()
xlsStyle = gateway.jvm.ExcelStyle("Verdana", 8, True, True, gateway.jvm.Color.YELLOW)
xlsStyle.setBackground(gateway.jvm.Color.BLACK)
xlsCell.setStyle(xlsStyle)
2. Formatting style and themes Do not create multiple instances of the same style or the same theme inside a loop. Define the instance of the style or theme outside the loop.
Source code sample
Wrong:
for (int row=0;row<xlsTable.RowCount();row++)
{
for (int column=0;column<xlsTable.ColumnCount();column++)
{
xlsTable.easy_getCellAt(row, column).setBackground(new ThemeColor(4,3));
}
}
Correct:
ExcelStyle xlsStyle = new ExcelStyle();
xlsStyle.setBackground(new ThemeColor(4,3));
for (int row=0;row<xlsTable.RowCount();row++)
{
for (int column=0;column<xlsTable.ColumnCount();column++)
{
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle);
}
}
Wrong:
For row As Integer = 0 To xlsTable.RowCount() - 1
For column As Integer = 0 To xlsTable.ColumnCount() - 1
xlsTable.easy_getCellAt(row, column).setBackground(New ThemeColor(4, 3))
Next
Next
Correct:
Dim xlsStyle As New ExcelStyle
xlsStyle.setBackground(New ThemeColor(4, 3))
For row As Integer = 0 To xlsTable.RowCount() - 1
For column As Integer = 0 To xlsTable.ColumnCount() - 1
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle)
Next
Next
C++
Wrong:
for (int row=0; row<xlsTable->RowCount(); row++)
{
for (int column=0; column<xlsTable->ColumnCount(); column++)
{
EasyXLS::IThemeColorPtr xlsThemeColor;
CoCreateInstance(__uuidof (EasyXLS::ThemeColor),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IThemeColor),
(void **) &xlsThemeColor);
xlsThemeColor->setColor(4,3);
xlsTable->easy_getCellAt(row, column)->setBackground_2(xlsThemeColor);
}
}
Correct:
EasyXLS::IExcelStylePtr xlsStyle;
CoCreateInstance(__uuidof (EasyXLS::ExcelStyle),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IExcelStyle),
(void **) &xlsStyle) ;
EasyXLS::IThemeColorPtr xlsThemeColor;
CoCreateInstance(__uuidof (EasyXLS::ThemeColor),
NULL,
CLSCTX_ALL,
__uuidof (EasyXLS::IThemeColor),
(void **) &xlsThemeColor);
xlsThemeColor->setColor(4,3);
xlsStyle->setBackground_2(xlsThemeColor);
for (int row=0; row<xlsTable->RowCount(); row++)
{
for (int column=0; column<xlsTable->ColumnCount(); column++)
{
xlsTable->easy_getCellAt(row, column)->setStyle(xlsStyle);
}
}
C++.NET
Wrong:
for (int row=0;row<xlsTable->RowCount();row++)
{
for (int column=0;column<xlsTable->ColumnCount();column++)
{
xlsTable->easy_getCellAt(row, column)->setBackground(new ThemeColor(4,3));
}
}
Correct:
ExcelStyle *xlsStyle = new ExcelStyle();
xlsStyle->setBackground(new ThemeColor(4,3));
for (int row=0;row<xlsTable->RowCount();row++)
{
for (int column=0;column<xlsTable->ColumnCount();column++)
{
xlsTable->easy_getCellAt(row, column)->setStyle(xlsStyle);
}
}
Wrong:
for (int row=0; row<xlsTable.RowCount(); row++)
{
for (int column=0; column<xlsTable.ColumnCount(); column++)
{
xlsTable.easy_getCellAt(row, column).setBackground(new ThemeColor(4,3));
}
}
Correct:
ExcelStyle xlsStyle = new ExcelStyle();
xlsStyle.setBackground(new ThemeColor(4,3));
for (int row=0; row<xlsTable.RowCount(); row++)
{
for (int column=0; column<xlsTable.ColumnCount(); column++)
{
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle);
}
}
.NET:
Wrong:
for ($row=0; $row<$xlsTable->RowCount(); $row++)
{
for ($column=0; $column<$xlsTable->ColumnCount(); $column++)
{
$xlsThemeColor = new COM("EasyXLS.Themes.ThemeColor" );
$xlsThemeColor->setColor(4,3);
$xlsTable->easy_getCellAt($row, $column)->setBackground_2($xlsThemeColor);
}
}
Correct:
$xlsStyle = new COM("EasyXLS.ExcelStyle" );
$xlsThemeColor = new COM("EasyXLS.Themes.ThemeColor" );
$xlsThemeColor->setColor(4,3);
$xlsStyle->setBackground_2($xlsThemeColor);
for ($row=0; $row<$xlsTable->RowCount(); $row++)
{
for ($column=0; $column<$xlsTable->ColumnCount(); $column++)
{
$xlsTable->easy_getCellAt($row, $column)->setStyle($xlsStyle);
}
}
Java:
Wrong:
for ($row=0; $row<(int )(string )$xlsTable->RowCount(); $row++)
{
for ($column=0; $column<(int )(string )$xlsTable->ColumnCount(); $column++)
{
$xlsThemeColor = new COM("EasyXLS.Themes.ThemeColor" );
$xlsThemeColor->setColor(4,3);
$xlsTable->easy_getCell($row, $column)->setBackground($xlsThemeColor);
}
}
Correct:
$xlsStyle = new java("EasyXLS.ExcelStyle" );
$xlsThemeColor = new java("EasyXLS.Themes.ThemeColor" );
$xlsThemeColor->setColor(4,3);
$xlsStyle->setBackground($xlsThemeColor);
for ($row=0; $row<(int )(string )$xlsTable->RowCount(); $row++)
{
for ($column=0; $column<(int )(string )$xlsTable->ColumnCount(); $column++)
{
$xlsTable->easy_getCell($row, $column)->setStyle($xlsStyle);
}
}
Wrong:
for row = 0 to xlsTable.RowCount()-1
for column = 0 to xlsTable.ColumnCount()-1
set xlsThemeColor = Server.Createobject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4,3
xlsTable.easy_getCellAt(row, column).setBackground_2(xlsThemeColor)
next
next
Correct:
set xlsStyle = Server.CreateObject("EasyXLS.ExcelStyle" )
set xlsThemeColor = Server.Createobject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4,3
xlsStyle.setBackground_2(xlsThemeColor)
for row = 0 to xlsTable.RowCount()-1
for column = 0 to xlsTable.ColumnCount()-1
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle)
next
next
Wrong:
For row = 0 To xlsTable.RowCount() - 1
For column = 0 To xlsTable.ColumnCount() - 1
Set xlsThemeColor = CreateObject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4, 3
xlsTable.easy_getCellAt(row, column).setBackground_2 (xlsThemeColor)
Next
Next
Correct:
Set xlsStyle = CreateObject("EasyXLS.ExcelStyle" )
Set xlsThemeColor = CreateObject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4, 3
xlsStyle.setBackground_2 (xlsThemeColor)
For row = 0 To xlsTable.RowCount() - 1
For column = 0 To xlsTable.ColumnCount() - 1
xlsTable.easy_getCellAt(row, column).setStyle (xlsStyle)
Next
Next
Wrong:
For row = 0 To xlsTable.RowCount() - 1
For column = 0 To xlsTable.ColumnCount() - 1
Set xlsThemeColor = CreateObject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4,3
xlsTable.easy_getCellAt(row, column).setBackground_2(xlsThemeColor)
Next
Next
Correct:
Set xlsStyle = CreateObject("EasyXLS.ExcelStyle" )
Set xlsThemeColor = CreateObject("EasyXLS.Themes.ThemeColor" )
xlsThemeColor.setColor 4,3
xlsStyle.setBackground_2(xlsThemeColor)
For row = 0 To xlsTable.RowCount() - 1
For column = 0 To xlsTable.ColumnCount() - 1
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle)
Next
Next
Wrong:
<cfobject type ="java" class ="EasyXLS.Themes.ThemeColor"
name ="xlsThemeColor" action ="CREATE" >
<cfset xlsThemeColor.setColor(4,3)>
<cfset rowCount = xlsTable.RowCount()>
<cfset columnCount = xlsTable.ColumnCount()>
<cfloop from ="0" to =#rowCount-1# index ="row" >
<cfloop from ="0" to =#columnCount-1# index ="column" >
<cfset xlsTable.easy_getCellAt(
evaluate (row), evaluate (column)).setBackground(xlsThemeColor)>
</cfloop>
</cfloop>
Correct:
<cfobject type ="java" class ="EasyXLS.ExcelStyle" name ="xlsStyle" action ="CREATE" >
<cfobject type ="java" class ="EasyXLS.Themes.ThemeColor"
name ="xlsThemeColor" action ="CREATE" >
<cfset xlsThemeColor.setColor(4,3)>
<cfset xlsStyle.setBackground(xlsThemeColor)>
<cfset rowCount = xlsTable.RowCount()>
<cfset columnCount = xlsTable.ColumnCount()>
<cfloop from ="0" to =#rowCount-1# index ="row" >
<cfloop from ="0" to =#columnCount-1# index ="column" >
<cfset xlsTable.easy_getCellAt(
evaluate (row), evaluate (column)).setStyle(xlsStyle)>
</cfloop>
</cfloop>
.NET:
Wrong:
for row in range(xlsTable.RowCount()):
for column in range(xlsTable.ColumnCount()):
xlsTable.easy_getCellAt(row, column).setBackground(ThemeColor(4,3))
Correct:
xlsStyle = ExcelStyle()
xlsStyle.setBackground(ThemeColor(4,3))
for row in range(xlsTable.RowCount()):
for column in range(xlsTable.ColumnCount()):
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle)
Java:
Wrong:
for row in range(xlsTable.RowCount()):
for column in range(xlsTable.ColumnCount()):
xlsTable.easy_getCellAt(row, column).setBackground(gateway.jvm.ThemeColor(4,3))
Correct:
xlsStyle = gateway.jvm.ExcelStyle()
xlsStyle.setBackground(gateway.jvm.ThemeColor(4,3))
for row in range(xlsTable.RowCount()):
for column in range(xlsTable.ColumnCount()):
xlsTable.easy_getCellAt(row, column).setStyle(xlsStyle)
3. Data type of the cells When setting the cell value, it is recommended to set the cell data type as well, otherwise the data type will remain by default as DataType.AUTOMATIC and EasyXLS will try to detect it. This action can be time consuming.
4. Recommended file formats XLSB and XLS file formats are binary files and therefore are faster by default. EasyXLS recommends to use these file formats for better performances.
5. Formula calculation Setting the formula calculation flag to false is recommended in case that calculating the formulas is not needed when writing the Excel files because, anyway, Microsoft Excel is recalculating them by default when opening the Excel files.
6. 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").
