EasyXLS™ library allows defining custom auto formats for generating nice reports. The user can define his own auto formatting, starting from scratch or starting from a predefined auto format.
By using the custom formats the user can automatically design the report header, footer, left column, right column, odd/even stripes and report data.
The report space is split into 9 areas:
Top Left Cell
Header Row
Top Right Cell
Left Column
Table Style
Right Column
Bottom Left Cell
Footer Row
Bottom Right Cell
EasyXLS provides two more auxiliary areas for alternating row stripes:
Top Left Cell
Header Row
Top Right Cell
Odd Row Stripes
Even Row Stripes
Odd Row Stripes
Even Row Stripes
Bottom Left Cell
Footer Row
Bottom Right Cell
and two more for alternating column stripes:
Top Left Cell
Header Row
Top Right Cell
Odd Column Stripes
Even Column Stripes
Odd Column Stripes
Even Column Stripes
Bottom Left Cell
Footer Row
Bottom Right Cell
Each area can have an ExcelStyle class attached that represents the formatting for the cells in that area.
Format options for an auto format
An auto format includes settings for:
- Cell text format: number format, currency format, date format, accounting format, percentage format, fraction format, text format and other custom formats - Cell alignment: horizontal and vertical alignment, text indentation, wrap text and shrink options, text rotation and text direction - Cell font settings: font name, font size, foreground, bold ant italic options, underline style, strikethrough, superscript and subscript effects - Cell border: border line style and color - Cell fill settings: background color, pattern style, pattern color, gradient fill - Cell protection: locked cells and hidden formulas
Export Excel file with custom auto format
The below source code sample shows how to export an Excel file with a custom cell formatting applied.
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
...
// Populate the dataset
DataSet dataset = new DataSet();
adp.Fill(dataset);
// Create an instance of the class used to format the cells in the report
ExcelAutoFormat xlsAutoFormat = new ExcelAutoFormat();
// Set the formatting style of the header
ExcelStyle xlsHeaderStyle = new ExcelStyle(Color.LightGreen);
xlsHeaderStyle.setFontSize(12);
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
ExcelStyle xlsEvenRowStripesStyle = new ExcelStyle(Color.FloralWhite);
xlsEvenRowStripesStyle.setFormat("$0.00");
xlsEvenRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle);
ExcelStyle xlsOddRowStripesStyle = new ExcelStyle(Color.FromArgb(240, 247, 239));
xlsOddRowStripesStyle.setFormat("$0.00");
xlsOddRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle);
ExcelStyle xlsLeftColumnStyle = new ExcelStyle(Color.FloralWhite);
xlsLeftColumnStyle.setFormat("mm/dd/yyyy");
xlsLeftColumnStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle);
// Export Excel file
workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\Custom formatting.xlsx",
dataset, xlsAutoFormat, "Sheet1");
' Create an instance of the class that exports Excel filesDim workbook As New ExcelDocument
...
' Populate the datasetDim dataset As DataSet = New DataSet
adp.Fill(dataset)
' Create an instance of the class used to format the cells in the reportDim xlsAutoFormat As ExcelAutoFormat = New ExcelAutoFormat
' Set the formatting style of the headerDim xlsHeaderStyle As ExcelStyle = New ExcelStyle(Color.LightGreen)
xlsHeaderStyle.setFontSize(12)
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle)
' Set the formatting style of the cells (alternating style)Dim xlsEvenRowStripesStyle As ExcelStyle = New ExcelStyle(Color.FloralWhite)
xlsEvenRowStripesStyle.setFormat("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
Dim xlsOddRowStripesStyle As ExcelStyle =
New ExcelStyle(Color.FromArgb(240, 247, 239))
xlsOddRowStripesStyle.setFormat("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle)
Dim xlsLeftColumnStyle As ExcelStyle = New ExcelStyle(Color.FloralWhite)
xlsLeftColumnStyle.setFormat("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle)
' Export Excel file
workbook.easy_WriteXLSXFile_FromDataSet("c:\Samples\Custom formatting.xlsx",
ds, xlsAutoFormat, "Sheet1")
C++// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;
...
// Create the list used to store the values
EasyXLS::IListPtr lstRows;
CoCreateInstance(__uuidof(EasyXLS::List), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IList), (void**) &lstRows);
// Add the header row to the list
EasyXLS::IListPtr lstHeaderRow;
CoCreateInstance(__uuidof(EasyXLS::List), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IList), (void**) &lstHeaderRow);
lstHeaderRow->addElement("Order Date");
lstHeaderRow->addElement("Product Name");
lstHeaderRow->addElement("Price");
lstHeaderRow->addElement("Quantity");
lstHeaderRow->addElement("Value");
lstRows->addElement(_variant_t((IDispatch*)lstHeaderRow,true));
VARIANT index;
index.vt=VT_I4;
FieldPtr field;
// Add the values from the database to the listwhile (!(objRS->EndOfFile))
{
EasyXLS::IListPtr RowList;
CoCreateInstance(__uuidof(EasyXLS::List), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IList), (void**) &RowList);
VARIANT value;
for (int nIndex = 0; nIndex < 5; nIndex++)
{
index.lVal = nIndex;
objRS->Fields->get_Item(index, &field);
field->get_Value (&value);
RowList->addElement(&value);
}
lstRows->addElement(_variant_t((IDispatch*)RowList,true));
// Move to the next record
objRS->MoveNext();
}
// Create an instance of the class used to format the cells
EasyXLS::IExcelAutoFormatPtr xlsAutoFormat;
CoCreateInstance(__uuidof(EasyXLS::ExcelAutoFormat), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelAutoFormat), (void**) &xlsAutoFormat);
// Set the formatting style of the header
EasyXLS::IExcelStylePtr xlsHeaderStyle;
CoCreateInstance(__uuidof(EasyXLS::ExcelStyle), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelStyle), (void**) &xlsHeaderStyle);
xlsHeaderStyle->setBackground(COLOR_LIGHTGREEN);
xlsHeaderStyle->setFontSize(12);
xlsAutoFormat->setHeaderRowStyle(xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
EasyXLS::IExcelStylePtr xlsEvenRowStripesStyle;
CoCreateInstance(__uuidof(EasyXLS::ExcelStyle), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelStyle), (void**) &xlsEvenRowStripesStyle);
xlsEvenRowStripesStyle->setBackground(COLOR_FLORALWHITE);
xlsEvenRowStripesStyle->setFormat("$0.00");
xlsEvenRowStripesStyle->setHorizontalAlignment(ALIGNMENT_ALIGNMENT_LEFT);
xlsAutoFormat->setEvenRowStripesStyle(xlsEvenRowStripesStyle);
EasyXLS::IExcelStylePtr xlsOddRowStripesStyle;
CoCreateInstance(__uuidof(EasyXLS::ExcelStyle), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelStyle), (void**) &xlsOddRowStripesStyle);
xlsOddRowStripesStyle->setBackground(OddRowStripesStyleColor);
xlsOddRowStripesStyle->setFormat("$0.00");
xlsOddRowStripesStyle->setHorizontalAlignment (ALIGNMENT_ALIGNMENT_LEFT);
xlsAutoFormat->setOddRowStripesStyle(xlsOddRowStripesStyle);
EasyXLS::IExcelStylePtr xlsLeftColumnStyle;
CoCreateInstance(__uuidof(EasyXLS::ExcelStyle), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelStyle), (void**) &xlsLeftColumnStyle);
xlsLeftColumnStyle->setBackground(COLOR_FLORALWHITE);
xlsLeftColumnStyle->setFormat("mm/dd/yyyy");
xlsLeftColumnStyle->setHorizontalAlignment(ALIGNMENT_ALIGNMENT_LEFT);
xlsAutoFormat->setLeftColumnStyle(xlsLeftColumnStyle);
// Export Excel file
printf("Writing file C:\\Samples\\Tutorial02.xlsx.");
hr = workbook->easy_WriteXLSXFile_FromList_2("C:\\Samples\\Custom formatting.xlsx",
_variant_t((IDispatch*)lstRows,true),
_variant_t((IDispatch*)xlsAutoFormat,true), "Sheet1");
Click here to see Continuous Code ListingC++.NET// Create an instance of the class that exports Excel files
ExcelDocument ^workbook = gcnew ExcelDocument();
...
// Populate the dataset
System::Data::DataSet ^ds = gcnew System::Data::DataSet();
adp->Fill(ds);
// Create an instance of the class used to format the cells in the report
ExcelAutoFormat ^xlsAutoFormat = gcnew ExcelAutoFormat();
// Set the formatting style of the header
ExcelStyle ^xlsHeaderStyle = gcnew ExcelStyle(Color::LightGreen);
xlsHeaderStyle->setFontSize(12);
xlsAutoFormat->setHeaderRowStyle(xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
ExcelStyle ^xlsEvenRowStripesStyle = gcnew ExcelStyle(Color::FloralWhite);
xlsEvenRowStripesStyle->setFormat("$0.00");
xlsEvenRowStripesStyle->setHorizontalAlignment(Alignment::ALIGNMENT_LEFT);
xlsAutoFormat->setEvenRowStripesStyle(xlsEvenRowStripesStyle);
ExcelStyle ^xlsOddRowStripesStyle = gcnew ExcelStyle(Color::FromArgb(240, 247, 239));
xlsOddRowStripesStyle->setFormat("$0.00");
xlsOddRowStripesStyle->setHorizontalAlignment (Alignment::ALIGNMENT_LEFT);
xlsAutoFormat->setOddRowStripesStyle(xlsOddRowStripesStyle);
ExcelStyle ^xlsLeftColumnStyle = gcnew ExcelStyle(Color::FloralWhite);
xlsLeftColumnStyle->setFormat("mm/dd/yyyy");
xlsLeftColumnStyle->setHorizontalAlignment(Alignment::ALIGNMENT_LEFT);
xlsAutoFormat->setLeftColumnStyle(xlsLeftColumnStyle);
// Export Excel file
workbook->easy_WriteXLSXFile_FromDataSet("c:\\Samples\\Custom formatting.xlsx",
ds, xlsAutoFormat, "Sheet1");
Click here to see Continuous Code Listing
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
...
// Create an instance of the class used to format the cells in the report
ExcelAutoFormat xlsAutoFormat = new ExcelAutoFormat();
// Set the formatting style of the header
ExcelStyle xlsHeaderStyle = new ExcelStyle(new Color(144, 238, 144));
xlsHeaderStyle.setFontSize(12);
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
ExcelStyle xlsEvenRowStripesStyle = new ExcelStyle(new Color(255, 250, 240));
xlsEvenRowStripesStyle.setFormat("$0.00");
xlsEvenRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle);
ExcelStyle xlsOddRowStripesStyle = new ExcelStyle(new Color(240, 247, 239));
xlsOddRowStripesStyle.setFormat("$0.00");
xlsOddRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle);
ExcelStyle xlsLeftColumnStyle = new ExcelStyle(new Color(255, 250, 240));
xlsLeftColumnStyle.setFormat("mm/dd/yyyy");
xlsLeftColumnStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT);
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle);
// Export Excel file
workbook.easy_WriteXLSXFile_FromResultSet("C:\\Samples\\Custom formatting.xlsx",
rs, xlsAutoFormat, "Sheet1");
.NET:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
...
// Create the list used to store the values
$lstRows = new COM("EasyXLS.Util.List");
// Add the header row to the list
$lstHeaderRow = new COM("EasyXLS.Util.List");
$lstHeaderRow->addElement("Order Date");
$lstHeaderRow->addElement("Product Name");
$lstHeaderRow->addElement("Price");
$lstHeaderRow->addElement("Quantity");
$lstHeaderRow->addElement("Value");
$lstRows->addElement($lstHeaderRow);
// Add the values from the database to the listwhile ($row=sqlsrv_fetch_array($query_result))
{
$RowList = new COM("EasyXLS.Util.List");
$RowList->addElement("" . $row['Order Date']);
$RowList->addElement("" . $row["Product Name"]);
$RowList->addElement("" . $row["Price"]);
$RowList->addElement("" . $row["Quantity"]);
$RowList->addElement("" . $row["Value"]);
$lstRows->addElement($RowList);
}
// Create an instance of the class used to format the cells
$xlsAutoFormat = new COM("EasyXLS.ExcelAutoFormat");
// Set the formatting style of the header
$xlsHeaderStyle = new COM("EasyXLS.ExcelStyle");
$xlsHeaderStyle->setBackground((int)$COLOR_LIGHTGREEN);
$xlsHeaderStyle->setFontSize(12);
$xlsAutoFormat->setHeaderRowStyle($xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
$xlsEvenRowStripesStyle = new COM("EasyXLS.ExcelStyle");
$xlsEvenRowStripesStyle->setBackground((int)$COLOR_FLORALWHITE);
$xlsEvenRowStripesStyle->setFormat("$0.00");
$xlsEvenRowStripesStyle->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setEvenRowStripesStyle($xlsEvenRowStripesStyle);
$xlsOddRowStripesStyle = new COM("EasyXLS.ExcelStyle");
$xlsOddRowStripesStyle->setBackground((int)$OddRowStripesStyleColor);
$xlsOddRowStripesStyle->setFormat("$0.00");
$xlsOddRowStripesStyle->setHorizontalAlignment ($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setOddRowStripesStyle($xlsOddRowStripesStyle);
$xlsLeftColumnStyle = new COM("EasyXLS.ExcelStyle");
$xlsLeftColumnStyle->setBackground((int)$COLOR_FLORALWHITE);
$xlsLeftColumnStyle->setFormat("mm/dd/yyyy");
$xlsLeftColumnStyle->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setLeftColumnStyle($xlsLeftColumnStyle);
// Export Excel file
$workbook->easy_WriteXLSXFile_FromList_2("C:\Samples\Custom formatting.xlsx",
$lstRows, $xlsAutoFormat, "Sheet1");
Click here to see Continuous Code ListingJava:// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
...
// Create the list that stores the query values
$lstRows = new java("EasyXLS.Util.List");
// Add the report header row to the list
$lstHeaderRow = new java("EasyXLS.Util.List");
$lstHeaderRow->addElement("Order Date");
$lstHeaderRow->addElement("Product Name");
$lstHeaderRow->addElement("Price");
$lstHeaderRow->addElement("Quantity");
$lstHeaderRow->addElement("Value");
$lstRows->addElement($lstHeaderRow);
// Add the query values from the database to the listwhile ($row=sqlsrv_fetch_array($query_result))
{
$RowList = new java("EasyXLS.Util.List");
$RowList->addElement("" . $row['Order Date']);
$RowList->addElement("" . $row["Product Name"]);
$RowList->addElement("" . $row["Price"]);
$RowList->addElement("" . $row["Quantity"]);
$RowList->addElement("" . $row["Value"]);
$lstRows->addElement($RowList);
}
// Create an instance of the class used to format the cells in the report
$xlsAutoFormat = new java("EasyXLS.ExcelAutoFormat");
// Set the formatting style of the header
$xlsHeaderStyle = new java("EasyXLS.ExcelStyle");
$xlsHeaderStyle->setBackground(new java("java.awt.Color", 144, 238, 144));
$xlsHeaderStyle->setFontSize(12);
$xlsAutoFormat->setHeaderRowStyle($xlsHeaderStyle);
// Set the formatting style of the cells (alternating style)
$xlsEvenRowStripesStyle = new java("EasyXLS.ExcelStyle");
$xlsEvenRowStripesStyle->setBackground(new java("java.awt.Color", 255, 250, 240));
$xlsEvenRowStripesStyle->setFormat("$0.00");
$xlsEvenRowStripesStyle->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setEvenRowStripesStyle($xlsEvenRowStripesStyle) ;
$xlsOddRowStripesStyle = new java("EasyXLS.ExcelStyle");
$xlsOddRowStripesStyle->setBackground(new java("java.awt.Color", 240, 247, 239));
$xlsOddRowStripesStyle->setFormat("$0.00");
$xlsOddRowStripesStyle->setHorizontalAlignment ($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setOddRowStripesStyle($xlsOddRowStripesStyle);
$xlsLeftColumnStyle = new java("EasyXLS.ExcelStyle");
$xlsLeftColumnStyle->setBackground(new java("java.awt.Color", 255, 250, 240));
$xlsLeftColumnStyle->setFormat("mm/dd/yyyy");
$xlsLeftColumnStyle->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsAutoFormat->setLeftColumnStyle($xlsLeftColumnStyle);
// Export list to Excel file
$workbook->easy_WriteXLSXFile_FromList("C:\Samples\Custom formatting.xlsx",
$lstRows, $xlsAutoFormat, "Sheet1");
Click here to see Continuous Code Listing
' Create an instance of the class that exports Excel filesset workbook = Server.CreateObject("EasyXLS.ExcelDocument")
...
' Create the list used to store the valuesDim lstRows
set lstRows = CreateObject("EasyXLS.Util.List")
' Add the header row to the listDim lstHeaderRow
set lstHeaderRow = Server.CreateObject("EasyXLS.Util.List")
lstHeaderRow.addElement("Order Date")
lstHeaderRow.addElement("Product Name")
lstHeaderRow.addElement("Price")
lstHeaderRow.addElement("Quantity")
lstHeaderRow.addElement("Value")
lstRows.addElement(lstHeaderRow)
' Add the values from the database to the listDo Until objRS.EOF = Trueset RowList = Server.CreateObject("EasyXLS.Util.List")
RowList.addElement("" & objRS("Order Date"))
RowList.addElement("" & objRS("Product Name"))
RowList.addElement("" & objRS("Price"))
RowList.addElement("" & objRS("Quantity"))
RowList.addElement("" & objRS("Value"))
lstRows.addElement(RowList)
' Move to the next record
objRS.MoveNext
Loop' Create an instance of the class used to format the cellsDim xlsAutoFormat
set xlsAutoFormat = Server.CreateObject("EasyXLS.ExcelAutoFormat")
' Set the formatting style of the headerDim xlsHeaderStyle
set xlsHeaderStyle = Server.CreateObject("EasyXLS.ExcelStyle")
xlsHeaderStyle.setBackground(CLng(COLOR_LIGHTGREEN))
xlsHeaderStyle.setFontSize(12)
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle)
' Set the formatting style of the cells (alternating style)Dim xlsEvenRowStripesStyle
set xlsEvenRowStripesStyle = Server.CreateObject("EasyXLS.ExcelStyle")
xlsEvenRowStripesStyle.setBackground(CLng(COLOR_FLORALWHITE))
xlsEvenRowStripesStyle.setFormat("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment(ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
Dim xlsOddRowStripesStyle
set xlsOddRowStripesStyle = Server.CreateObject("EasyXLS.ExcelStyle")
xlsOddRowStripesStyle.setBackground(OddRowStripesStyleColor)
xlsOddRowStripesStyle.setFormat("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment (ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle)
Dim xlsLeftColumnStyle
set xlsLeftColumnStyle = Server.CreateObject("EasyXLS.ExcelStyle")
xlsLeftColumnStyle.setBackground(CLng(COLOR_FLORALWHITE))
xlsLeftColumnStyle.setFormat("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment (ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "C:\Samples\Custom formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
...
' Create the list used to store the valuesDim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")
' Add the header row to the listDim lstHeaderRow
Set lstHeaderRow = CreateObject("EasyXLS.Util.List")
lstHeaderRow.addElement ("Order Date")
lstHeaderRow.addElement ("Product Name")
lstHeaderRow.addElement ("Price")
lstHeaderRow.addElement ("Quantity")
lstHeaderRow.addElement ("Value")
lstRows.addElement (lstHeaderRow)
' Add the values from the database to the listDo Until objRS.EOF = TrueSet RowList = CreateObject("EasyXLS.Util.List")
RowList.addElement ("" & objRS("Order Date"))
RowList.addElement ("" & objRS("Product Name"))
RowList.addElement ("" & objRS("Price"))
RowList.addElement ("" & objRS("Quantity"))
RowList.addElement ("" & objRS("Value"))
lstRows.addElement (RowList)
' Move to the next record
objRS.MoveNext
Loop' Create an instance of the class used to format the cellsDim xlsAutoFormat
Set xlsAutoFormat = CreateObject("EasyXLS.ExcelAutoFormat")
' Set the formatting style of the headerDim xlsHeaderStyle
Set xlsHeaderStyle = CreateObject("EasyXLS.ExcelStyle")
xlsHeaderStyle.setBackground (CLng(Color.COLOR_LIGHTGREEN))
xlsHeaderStyle.setFontSize (12)
xlsAutoFormat.setHeaderRowStyle (xlsHeaderStyle)
' Set the formatting style of the cells (alternating style)Dim xlsEvenRowStripesStyle
Set xlsEvenRowStripesStyle = CreateObject("EasyXLS.ExcelStyle")
xlsEvenRowStripesStyle.setBackground (CLng(Color.COLOR_FLORALWHITE))
xlsEvenRowStripesStyle.setFormat ("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment (Alignment.ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle (xlsEvenRowStripesStyle)
Dim xlsOddRowStripesStyle
Set xlsOddRowStripesStyle = CreateObject("EasyXLS.ExcelStyle")
xlsOddRowStripesStyle.setBackground (OddRowStripesStyleColor)
xlsOddRowStripesStyle.setFormat ("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment (Alignment.ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle (xlsOddRowStripesStyle)
Dim xlsLeftColumnStyle
Set xlsLeftColumnStyle = CreateObject("EasyXLS.ExcelStyle")
xlsLeftColumnStyle.setBackground (CLng(Color.COLOR_FLORALWHITE))
xlsLeftColumnStyle.setFormat ("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment (Alignment.ALIGNMENT_ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle (xlsLeftColumnStyle)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\Custom formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
' Create an instance of the class that exports Excel filesset workbook = CreateObject("EasyXLS.ExcelDocument")
...
' Create the list used to store the valuesDim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")
' Add the header row to the listDim lstHeaderRow
Set lstHeaderRow = CreateObject("EasyXLS.Util.List")
lstHeaderRow.addElement("Order Date")
lstHeaderRow.addElement("Product Name")
lstHeaderRow.addElement("Price")
lstHeaderRow.addElement("Quantity")
lstHeaderRow.addElement("Value")
lstRows.addElement(lstHeaderRow)
' Add the values from the database to the listDo Until objRS.EOF = Trueset RowList = CreateObject("EasyXLS.Util.List")
RowList.addElement("" & objRS("Order Date"))
RowList.addElement("" & objRS("Product Name"))
RowList.addElement("" & objRS("Price"))
RowList.addElement("" & objRS("Quantity"))
RowList.addElement("" & objRS("Value"))
lstRows.addElement(RowList)
' Move to the next record
objRS.MoveNext
Loop' Create an instance of the class used to format the cellsDim xlsAutoFormat
Set xlsAutoFormat = CreateObject("EasyXLS.ExcelAutoFormat")
' Set the formatting style of the headerDim xlsHeaderStyle
Set xlsHeaderStyle = CreateObject("EasyXLS.ExcelStyle")
xlsHeaderStyle.setBackground(LightGreen)
xlsHeaderStyle.setFontSize(12)
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle)
' Set the formatting style of the cells (alternating style)Dim xlsEvenRowStripesStyle
Set xlsEvenRowStripesStyle = CreateObject("EasyXLS.ExcelStyle")
xlsEvenRowStripesStyle.setBackground(FloralWhite)
xlsEvenRowStripesStyle.setFormat("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment(ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
Dim xlsOddRowStripesStyle
Set xlsOddRowStripesStyle = CreateObject("EasyXLS.ExcelStyle")
xlsOddRowStripesStyle.setBackground(OddRowStripesStyleColor)
xlsOddRowStripesStyle.setFormat("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment (ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle)
Dim xlsLeftColumnStyle
Set xlsLeftColumnStyle = CreateObject("EasyXLS.ExcelStyle")
xlsLeftColumnStyle.setBackground(FloralWhite)
xlsLeftColumnStyle.setFormat("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment(ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\Custom formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
.NET:# Create an instance of the class that exports Excel files
workbook = ExcelDocument()
...
# Create the list that stores the query values
lstRows = List()
# Add the report header row to the list
lstHeaderRow = List()
lstHeaderRow.addElement("Invoice date")
lstHeaderRow.addElement("Customer name")
lstHeaderRow.addElement("Billing address")
lstHeaderRow.addElement("Billing city")
lstHeaderRow.addElement("Billing state")
lstHeaderRow.addElement("Billing country")
lstHeaderRow.addElement("Total")
lstRows.addElement(lstHeaderRow)
# Add the query values from the database to the listfor row in rows:
RowList = List()
RowList.addElement(row[0])
RowList.addElement(row[1])
RowList.addElement(row[2])
RowList.addElement(row[3])
RowList.addElement(row[4])
RowList.addElement(row[5])
RowList.addElement(row[6])
lstRows.addElement(RowList)
# Create an instance of the class used to format the cells in the report
xlsAutoFormat = ExcelAutoFormat()
# Set the formatting style of the header
xlsHeaderStyle = ExcelStyle(Color.LightGreen)
xlsHeaderStyle.setFontSize(12)
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle)
# Set the formatting style of the cells (alternating style)
xlsEvenRowStripesStyle = ExcelStyle(Color.FloralWhite)
xlsEvenRowStripesStyle.setFormat("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
xlsOddRowStripesStyle = ExcelStyle(Color.FromArgb(240, 247, 239))
xlsOddRowStripesStyle.setFormat("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment (Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle)
xlsLeftColumnStyle = ExcelStyle(Color.FloralWhite)
xlsLeftColumnStyle.setFormat("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment (Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle)
# Export the Excel file
workbook.easy_WriteXLSXFile_FromList(
"c:\\Samples\\Custom formatting.xlsx", lstRows, xlsAutoFormat, "Sheet1")
Click here to see Continuous Code ListingJava:# Create an instance of the class that exports Excel files
workbook = gateway.jvm.ExcelDocument()
...
# Create the list that stores the query values
lstRows = gateway.jvm.List()
# Add the report header row to the list
lstHeaderRow = gateway.jvm.List()
lstHeaderRow.addElement("Invoice date")
lstHeaderRow.addElement("Customer name")
lstHeaderRow.addElement("Billing address")
lstHeaderRow.addElement("Billing city")
lstHeaderRow.addElement("Billing state")
lstHeaderRow.addElement("Billing country")
lstHeaderRow.addElement("Total")
lstRows.addElement(lstHeaderRow)
# Add the query values from the database to the listfor row in rows:
RowList = gateway.jvm.List()
RowList.addElement(row[0])
RowList.addElement(row[1])
RowList.addElement(row[2])
RowList.addElement(row[3])
RowList.addElement(row[4])
RowList.addElement(row[5])
RowList.addElement(row[6])
lstRows.addElement(RowList)
# Create an instance of the class used to format the cells in the report
xlsAutoFormat = gateway.jvm.ExcelAutoFormat()
# Set the formatting style of the header
xlsHeaderStyle = gateway.jvm.ExcelStyle(gateway.jvm.Color(144, 238, 144))
xlsHeaderStyle.setFontSize(12)
xlsAutoFormat.setHeaderRowStyle(xlsHeaderStyle)
# Set the formatting style of the cells (alternating style)
xlsEvenRowStripesStyle = gateway.jvm.ExcelStyle(gateway.jvm.Color(255, 250, 240))
xlsEvenRowStripesStyle.setFormat("$0.00")
xlsEvenRowStripesStyle.setHorizontalAlignment(gateway.jvm.Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setEvenRowStripesStyle(xlsEvenRowStripesStyle)
xlsOddRowStripesStyle = gateway.jvm.ExcelStyle(gateway.jvm.Color(240, 247, 239))
xlsOddRowStripesStyle.setFormat("$0.00")
xlsOddRowStripesStyle.setHorizontalAlignment (gateway.jvm.Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setOddRowStripesStyle(xlsOddRowStripesStyle)
xlsLeftColumnStyle = gateway.jvm.ExcelStyle(gateway.jvm.Color(255, 250, 240))
xlsLeftColumnStyle.setFormat("mm/dd/yyyy")
xlsLeftColumnStyle.setHorizontalAlignment (gateway.jvm.Alignment.ALIGNMENT_LEFT)
xlsAutoFormat.setLeftColumnStyle(xlsLeftColumnStyle)
# Export the Excel file
workbook.easy_WriteXLSXFile_FromList(
"c:\\Samples\\Custom formatting.xlsx", lstRows, xlsAutoFormat, "Sheet1")
Click here to see Continuous Code Listing
The screen shot below represents the exported Excel file with a custom cell range formatting applied.
