Predefined formatting for cell ranges

// 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 files
Dim workbook As New ExcelDocument

...

' Populate the dataset
Dim 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 list
while (!(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 Listing


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, 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 list
while ($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 Listing


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);
			
// Add the query values from the database to the list
while ($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 files
Set workbook = Server.CreateObject("EasyXLS.ExcelDocument")

...

' Create the list that stores the query values
Dim lstRows 
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the report header row to the list
Dim 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 list
Do Until objRS.EOF = True
    set 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 cells
Dim 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 files
Set workbook = CreateObject("EasyXLS.ExcelDocument")

...

' Create the list that stores the query values
Dim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the report header row to the list
Dim 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 list
Do 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 cells
Dim 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 files
Set workbook = CreateObject("EasyXLS.ExcelDocument")

...

' Create the list that stores the query values
Dim lstRows 
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the report header row to the list
Dim 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 list
Do 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 cells
Dim 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 list
for 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 Listing


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)

# Add the query values from the database to the list
for 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