EasyXLS™ library provides 49 predefined auto formats for generating nice reports, including Excel auto formats and some extra auto formats.
Using those predefined formats the user can automatically design the report header, footer, left column, right column, odd/even stripes and report data.
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, merge cells, 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
Source code sample
The below example shows how to export an Excel file with a predefined 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);
// Export Excel file
workbook.easy_WriteXLSXFile_FromDataSet("C:\\Samples\\Predefined formatting.xlsx",
dataset, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "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)
' Export Excel file
workbook.easy_WriteXLSXFile_FromDataSet("C:\Samples\Predefined formatting.xlsx", _
dataset, New ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1) , "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 that stores the query values
EasyXLS::IListPtr lstRows;
CoCreateInstance(__uuidof(EasyXLS::List), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IList), (void**) &lstRows);
// Add the report 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 query 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) ;
xlsAutoFormat->InitAs(AUTOFORMAT_EASYXLS1);
// Export Excel file
hr = workbook->easy_WriteXLSXFile_FromList_2("C:\\Samples\\Predefined 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 ^dataset = gcnew System::Data::DataSet();
adp->Fill(dataset);
// Export Excel file
workbook->easy_WriteXLSXFile_FromDataSet("C:\\Samples\\Predefined formatting.xlsx",
dataset, gcnew ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS1), "Sheet1");
Click here to see Continuous Code Listing
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
// Create the resultset used to populate the sheet
...
ResultSet resultset = pStatement.executeQuery();
// Export Excel file
workbook.easy_WriteXLSXFile_FromResultSet("C:\\Samples\\Predefined formatting.xlsx",
resultset, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "Sheet1");
.NET:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
...
// Create the list that stores the query values
$lstRows = new COM("EasyXLS.Util.List");
// Add the report 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 query 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");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
// Export Excel file
$workbook->easy_WriteXLSXFile_FromList_2("C:\Samples\Predefined 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
$xlsAutoFormat = new java("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
// Export list to Excel file
$workbook->easy_WriteXLSXFile_FromList("C:\Samples\Predefined 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 that stores the query valuesDim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")
' Add the report 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 query 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")
xlsAutoFormat.InitAs(AUTOFORMAT_EASYXLS1)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "C:\Samples\Predefined formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
...
' Create the list that stores the query valuesDim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")
' Add the report 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 query values from the database to the listDo Until objRS.EOF = True
Set 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")
xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\Predefined formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
...
' Create the list that stores the query valuesDim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")
' Add the report 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 query 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")
xlsAutoFormat.InitAs(AUTOFORMAT_EASYXLS1)
' Export Excel file
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\Predefined formatting.xlsx", _
lstRows, xlsAutoFormat, "Sheet1"
<!-- Create an instance of the class that exports Excel files --><cfobject type="java"class="EasyXLS.ExcelDocument"name="workbook"action="CREATE">
...
<!-- Create the list that stores the query values --><cfobject type="java"class="EasyXLS.Util.List"name="lstRows"action="CREATE"><!-- Add the report header row to the list --><cfobject type="java"class="EasyXLS.Util.List"name="lstHeaderRow"action="CREATE"><cfset lstHeaderRow.addElement("Order Date")><cfset lstHeaderRow.addElement("Product Name")><cfset lstHeaderRow.addElement("Price")><cfset lstHeaderRow.addElement("Quantity")><cfset lstHeaderRow.addElement("Value")><cfset lstRows.addElement(lstHeaderRow)><!-- Add the query values from the database to the list --><cfloop query="myQuery"><cfobject type="java"class="EasyXLS.Util.List"name="RowList"action="CREATE"><cfset RowList.addElement(#Order_Date#)><cfset RowList.addElement(#Product_Name#)><cfset RowList.addElement(#Price#)><cfset RowList.addElement(#Quantity#)><cfset RowList.addElement(#Value#)><cfset lstRows.addElement(RowList)></cfloop><!-- Create an instance of the class used to format the cells --><cfobject type="java"class="EasyXLS.ExcelAutoFormat"name="xlsAutoFormat"action="CREATE"><cfset xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1)><!-- Export Excel file --><cfset ret = workbook.easy_WriteXLSXFile_FromList(
"C:\Samples\Predefined 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)
# Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("c:\\Samples\\Predefined formatting.xlsx", lstRows,
ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "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)
# Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("c:\\Samples\\Predefined formatting.xlsx", lstRows,
gateway.jvm.ExcelAutoFormat(gateway.jvm.Styles.AUTOFORMAT_EASYXLS1), "Sheet1")
Click here to see Continuous Code Listing
The screen shot below represents the exported Excel file with a predefined cell range formatting applied.