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

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 C#.NET VB.NET C++.NET J#.NET Java PHP ASP C++ VB6 VBS Coldfusion 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)

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.get_Yellow()); xlsCell.setBackground(Color.get_Black()); Correct: ExcelCell xlsCell = new ExcelCell(); ExcelStyle xlsStyle = new ExcelStyle( "Verdana" , 8, true , true , Color.get_Yellow()); xlsStyle.setBackground(Color.get_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);

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);

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: 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);

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) >



See also:



Related methods:

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 C#.NET VB.NET C++.NET J#.NET Java PHP ASP C++ VB6 VBS Coldfusion 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

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); } }

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=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); } }

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 (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); } }

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>



See also:



Related methods:

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. See also:

Related methods:

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. See also:

Related methods:

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.

By applying this option, the formulas will no longer be computed and time-computing will be gained. See also:

Related methods:

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"). See also: