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.
- 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
Autoformat for data structures
The autoformat can be applied along with data structures like DataSet, ResultSet or List. The below source code sample 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.
Autoformat for Excel file with multiple sheets
EasyXLS offers the option to insert multiple data structures like DataTable, DataSet, ResultSet or List into one worksheet or into multiple worksheets. The data structure can be added into a specified worksheet. The column names of the data structure can be optionally included.
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
// Create a sheet
workbook.easy_addWorksheet("Sheet1");
ExcelWorksheet xlsWorksheet = (ExcelWorksheet)workbook.easy_getSheetAt(0);
// Add report data
...
// Apply auto-format on A1:D10 range
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(0, 0, 9, 3,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1));
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx");
' Create an instance of the class that exports Excel filesDim workbook As New ExcelDocument()
' Create a sheet
workbook.easy_addWorksheet("Sheet1")
Dim xlsWorksheet = workbook.easy_getSheetAt(0)
' Add report data
...
' Apply auto-format on A1D10 Range
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(0, 0, 9, 3,
New ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1))
' Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx");
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 a sheet
workbook->easy_addWorksheet_2("Sheet1");
EasyXLS::IExcelWorksheetPtr xlsWorksheet =
(EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheetAt(0);
// Add report data
...
// Apply auto-format on A1:D10 range
EasyXLS::IExcelAutoFormatPtr xlsAutoFormat;
CoCreateInstance(__uuidof (EasyXLS::ExcelAutoFormat), NULL, CLSCTX_ALL,
__uuidof(EasyXLS::IExcelAutoFormat), (void**)&xlsAutoFormat);
xlsAutoFormat->InitAs(AUTOFORMAT_EASYXLS1);
xlsWorksheet->easy_getExcelTable()->easy_setRangeAutoFormat(0, 0, 9, 3,
_variant_t((IDispatch*)xlsAutoFormat, true));
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx");
C++.NET// Create an instance of the class that exports Excel files
ExcelDocument ^workbook = gcnew ExcelDocument();
// Create a sheet
workbook->easy_addWorksheet("Sheet1");
ExcelWorksheet ^xlsWorksheet =
safe_cast<ExcelWorksheet^>(workbook->easy_getSheetAt(0));
// Add report data
...
// Apply auto-format on A1:D10 range
xlsWorksheet->easy_getExcelTable()->easy_setRangeAutoFormat(0, 0, 9, 3,
gcnew ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS1));
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx");
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
// Create a sheet
workbook.easy_addWorksheet("Sheet1");
ExcelWorksheet xlsWorksheet = (ExcelWorksheet)workbook.easy_getSheetAt(0);
// Add report data
...
// Apply auto-format on A1:D10 range
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(0, 0, 9, 3,
new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1));
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx");
.NET:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Create a sheet
$workbook->easy_addWorksheet_2("Sheet1");
$xlsWorksheet = $workbook->easy_getSheetAt(0);
// Add report data
...
// Apply auto-format on A1:D10 range
$xlsAutoFormat = new COM("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
$xlsWorksheet->easy_getExcelTable()->easy_setRangeAutoFormat(0, 0, 9, 3, $xlsAutoFormat);
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx");
Java:// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
// Create a sheet
$workbook->easy_addWorksheet("Sheet1");
$xlsWorksheet = $workbook->easy_getSheetAt(0);
// Add report data
...
// Apply auto-format on A1:D10 range
$xlsAutoFormat = new java("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
$xlsWorksheet->easy_getExcelTable()->easy_setRangeAutoFormat(0, 0, 9, 3, $xlsAutoFormat);
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx");
' Create an instance of the class that exports Excel filesSet workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Create a sheet
workbook.easy_addWorksheet_2("Sheet1")
Set xlsWorksheet = workbook.easy_getSheetAt(0)
' Add report data
...
' Apply auto-format on A1:D10 rangeDim xlsAutoFormat
Set xlsAutoFormat = Server.CreateObject("EasyXLS.ExcelAutoFormat")
xlsAutoFormat.InitAs(AUTOFORMAT_EASYXLS1)
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat 0, 0, 9, 3, xlsAutoFormat
' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create a sheet
workbook.easy_addWorksheet_2("Sheet1")
Set xlsWorksheet = workbook.easy_getSheetAt(0)
' Add report data
...
' Apply auto-format on A1:D10 rangeDim xlsAutoFormat
Set xlsAutoFormat = CreateObject("EasyXLS.ExcelAutoFormat")
xlsAutoFormat.InitAs(AUTOFORMAT_EASYXLS1)
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat 0, 0, 9, 3, xlsAutoFormat
' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create a sheet
workbook.easy_addWorksheet_2("Sheet1")
Set xlsWorksheet = workbook.easy_getSheetAt(0)
' Add report data
...
' Apply auto-format on A1:D10 rangeDim xlsAutoFormat
Set xlsAutoFormat = CreateObject("EasyXLS.ExcelAutoFormat")
xlsAutoFormat.InitAs(AUTOFORMAT_EASYXLS1)
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat 0, 0, 9, 3, xlsAutoFormat
' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx")
<!-- Create an instance of the class that exports Excel files --><cfobject type="java"class="EasyXLS.ExcelDocument"name="workbook"action="CREATE"><!-- Create a sheet --><cfset ret = workbook.easy_addWorksheet("Sheet1")><cfset xlsWorksheet = workbook.easy_getSheetAt(0)><!-- Add report data -->
...
<!-- Apply auto-format on A1:D10 range --><cfobject type="java"class="EasyXLS.ExcelAutoFormat"name="xlsAutoFormat"action="CREATE"><cfset xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1)><cfset ret = xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(
0, 0, 9, 3, xlsAutoFormat)><!-- Export Excel file --><cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Predefined formatting.xlsx")>
.NET:# Create an instance of the class that exports Excel files
workbook = ExcelDocument()
# Create a sheet
workbook.easy_addWorksheet("Sheet1")
xlsWorksheet = workbook.easy_getSheetAt(0)
# Add report data
...
# Apply auto-format on A1:D10 range
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(0, 0, 9, 3,
ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1))
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx")
Java:# Create an instance of the class that exports Excel files
workbook = gateway.jvm.ExcelDocument()
# Create a sheet
workbook.easy_addWorksheet("Sheet1")
xlsWorksheet = workbook.easy_getSheetAt(0)
# Add report data
...
# Apply auto-format on A1:D10 range
xlsWorksheet.easy_getExcelTable().easy_setRangeAutoFormat(0, 0, 9, 3,
gateway.jvm.ExcelAutoFormat(gateway.jvm.Styles.AUTOFORMAT_EASYXLS1))
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Predefined formatting.xlsx")
Autoformat position
EasyXLS allows you to set where the autoformat will be placed on the worksheet. All the methods that set the autoformat have method variants that permits to set the cell start position for the table autoformat.
The below source code sample shows how to insert a data structure with autoformat starting from A2 cell.
// 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, "A2", 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, "A2", 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));
...
// 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
workbook->easy_WriteXLSXFile_FromList_3("C:\\Samples\\Predefined formatting.xlsx",
_variant_t((IDispatch*)lstRows, true), "A2",
_variant_t((IDispatch*)xlsAutoFormat, true), "Sheet1");
C++.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, "A2", gcnew ExcelAutoFormat(Styles::AUTOFORMAT_EASYXLS1), "Sheet1");
// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();
...
// Populate the dataset
ResultSet resultset = pStatement.executeQuery();
// Export Excel file
workbook.easy_WriteXLSXFile_FromResultSet("C:\\Samples\\Predefined formatting.xlsx",
resultset, "A2", 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);
...
// 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_3("C:\Samples\Predefined formatting.xlsx",
$lstRows, "A2", $xlsAutoFormat, "Sheet1");
Java:// 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);
...
// Create an instance of the class used to format the cells
$xlsAutoFormat = new java("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);
// Export Excel file
$workbook->easy_WriteXLSXFile_FromList("C:\Samples\Predefined formatting.xlsx",
$lstRows, "A2", $xlsAutoFormat, "Sheet1")
' 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)
...
' 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_3 "C:\Samples\Predefined formatting.xlsx", _
lstRows, "A2", 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)
...
' 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_3 "c:\Samples\Predefined formatting.xlsx", _
lstRows, "A2", 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)
...
' 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_3 "c:\Samples\Predefined formatting.xlsx", _
lstRows, "A2", 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)>
...
<!-- 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, "A2", 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)
...
# Export Excel file
workbook.easy_WriteXLSXFile_FromList("c:\\Samples\\Predefined formatting.xlsx",
lstRows, "A2", ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "Sheet1")
Java:# 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)
...
# Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("c:\\Samples\\Predefined formatting.xlsx", lstRows,
"A2", gateway.jvm.ExcelAutoFormat(gateway.jvm.Styles.AUTOFORMAT_EASYXLS1), "Sheet1")
