Export List to Excel file in .NET, Java and other programming languages

// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();

// Create the list used to store the data
EasyXLS.Util.List listRows = new EasyXLS.Util.List();

// Add the header row to the list
EasyXLS.Util.List listHeaderRow = new EasyXLS.Util.List();
listHeaderRow.Add("ID");
listHeaderRow.Add("Data");
listRows.Add(listHeaderRow);

// Populate the list
EasyXLS.Util.List listRow1 = new EasyXLS.Util.List();
EasyXLS.Util.List listRow2 = new EasyXLS.Util.List();
listRow1.Add("1");
listRow1.Add("Data 1");
listRow2.Add("2");
listRow2.Add("Data 2");
listRows.Add(listRow1);
listRows.Add(listRow2);

// Create an instance of the class used to format the cells
EasyXLS.ExcelAutoFormat xlsAutoFormat = new ExcelAutoFormat();
xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1);

// Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("C:\\Samples\\List to Excel.xlsx", 
        listRows, xlsAutoFormat, "List");

' Create an instance of the class that exports Excel files
Dim workbook As New ExcelDocument

' Create the list used to store the data
Dim listRows As New EasyXLS.Util.List

' Add the header row to the list
Dim listHeaderRow As New EasyXLS.Util.List
listHeaderRow.Add("ID")
listHeaderRow.Add("Data")
listRows.Add(listHeaderRow)

' Populate the list
Dim listRow1 As New EasyXLS.Util.List
Dim listRow2 As New EasyXLS.Util.List
listRow1.Add("1")
listRow1.Add("Data 1")
listRow2.Add("2")
listRow2.Add("Data 2")
listRows.Add(listRow1)
listRows.Add(listRow2)

' Create an instance of the class used to format the cells
Dim xlsAutoFormat As New ExcelAutoFormat
xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1)

' Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("C:\\Samples\\List to Excel.xlsx", _
        listRows, xlsAutoFormat, "List")

C++

// Connect to the database
_ConnectionPtr objConn;
objConn.CreateInstance(__uuidof(Connection));
objConn->Open("driver={sql server};server=(local);Database=Northwind;UID=sa;PWD=;", 
        (BSTR) NULL, (BSTR) NULL, -1);

WCHAR* sQueryString = L"SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + \
                       '/' + CAST(Day(ord.OrderDate) AS varchar) + \
                       '/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', \
                       P.ProductName AS 'Product Name', O.UnitPrice AS Price, \
                       CAST(O.Quantity AS varchar) AS Quantity, \
                       O.UnitPrice * O. Quantity AS Value \
                       FROM Orders AS ord, [Order Details] AS O, Products AS P \
                       WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID";
_variant_t sqlQueryString = sQueryString ;

// Create the record set object
_RecordsetPtr objRS = NULL;
objRS.CreateInstance(__uuidof(Recordset));
objRS->Open( sqlQueryString, _variant_t((IDispatch*)objConn,true), 
        adOpenStatic, adLockOptimistic, adCmdText);

// 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 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 list to Excel file
printf("Writing file C:\\Samples\\List to Excel.xlsx.");
hr = workbook->easy_WriteXLSXFile_FromList_2("C:\\Samples\\List to Excel.xlsx", 
        _variant_t((IDispatch*)lstRows,true), 
        _variant_t((IDispatch*)xlsAutoFormat,true), 
        "List");

Click here to see Continuous Code Listing


C++.NET

// Create an instance of the class that exports Excel files
ExcelDocument *workbook = new ExcelDocument();

// Create the list used to store the data
EasyXLS::Util::List *listRows = new EasyXLS::Util::List();

// Add the header row to the list
EasyXLS::Util::List *listHeaderRow = new EasyXLS::Util::List();
listHeaderRow->Add(new String("ID")); 
listHeaderRow->Add(new String("Data"));
listRows->Add(listHeaderRow);

// Populate the list
EasyXLS::Util::List *listRow1 = new EasyXLS::Util::List();
EasyXLS::Util::List *listRow2 = new EasyXLS::Util::List();
listRow1->Add(new String("1"));
listRow1->Add(new String("Data 1"));
listRow2->Add(new String("2"));
listRow2->Add(new String("Data 2"));
listRows->Add(listRow1);
listRows->Add(listRow2);

// Create an instance of the class used to format the cells
EasyXLS::ExcelAutoFormat *xlsAutoFormat = new ExcelAutoFormat();
xlsAutoFormat->InitAs(Styles::AUTOFORMAT_EASYXLS1);

// Export list to Excel file
workbook->easy_WriteXLSXFile_FromList("C:\\Samples\\List to Excel.xlsx", 
        listRows, xlsAutoFormat, "List");

// Create an instance of the class that exports Excel files
ExcelDocument workbook = new ExcelDocument();

// Create the list used to store the data
EasyXLS.Util.List listRows = new EasyXLS.Util.List();

// Add the header row to the list
EasyXLS.Util.List listHeaderRow = new EasyXLS.Util.List();
listHeaderRow.addElement("ID");
listHeaderRow.addElement("Data");
listRows.addElement(listHeaderRow);

// Populate the list
EasyXLS.Util.List listRow1 = new EasyXLS.Util.List();
EasyXLS.Util.List listRow2 = new EasyXLS.Util.List();
listRow1.addElement("1");
listRow1.addElement("Data 1");
listRow2.addElement("2");
listRow2.addElement("Data 2");
listRows.addElement(listRow1);
listRows.addElement(listRow2);

// Create an instance of the class used to format the cells
EasyXLS.ExcelAutoFormat xlsAutoFormat = new ExcelAutoFormat();
xlsAutoFormat.InitAs(Styles.AUTOFORMAT_EASYXLS1);

// Export list to Excel file
workbook.easy_WriteXLSXFile_FromList("C:\\Samples\\List to Excel.xlsx", 
        listRows, xlsAutoFormat, "List");

// Create the database connection
$serverName = "(local)"
$connectionInfo = array ("Database"=>"northwind", "UID"=>"sa", "PWD"=>"");

$db_conn = sqlsrv_connect($serverName, $connectionInfo);
if ($db_conn === false)
{
    echo "Unable to connect.";
    die(print_r(sqlsrv_errors(), true));
}

// Query the database
$query_result = sqlsrv_query( $db_conn, "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + '/' + 
                              CAST(Day(ord.OrderDate) AS varchar) + '/' + 
                              CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', 
                              P.ProductName AS 'Product Name', O.UnitPrice AS Price, 
                              CAST(O.Quantity AS varchar) AS Quantity, 
                              O.UnitPrice * O. Quantity AS Value 
                              FROM Orders AS ord, [Order Details] AS O, Products AS P 
                              WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID")
    or die( "<strong>ERROR: Query failed</strong>" );

// 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=mssql_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 list to Excel file
echo "Writing file: C:\Samples\List to Excel.xlsx<br>";
$workbook->easy_WriteXLSXFile_FromList_2("C:\Samples\List to Excel.xlsx", 
        $lstRows, $xlsAutoFormat, "List");

' Connect to the database
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = _
        "Provider=SQLOLEDB;Server=(local);Database=northwind;User ID=sa;Password=;"
objConn.Open

Dim sQueryString
sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + '/' + " & _
               "CAST(Day(ord.OrderDate) AS varchar) + '/' + " & _
               "CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " & _
               "P.ProductName AS 'Product Name', O.UnitPrice AS Price, " & _
               "CAST(O.Quantity AS varchar) AS Quantity, " & _
               "O.UnitPrice * O. Quantity AS Value " & _
               "FROM Orders AS ord, [Order Details] AS O, Products AS P " & _
               "WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID"

' Create the record set object
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset") 
objRS.Open sQueryString, objConn

' Create the list used to store the values
Dim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the 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 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 list to Excel file
response.write("Writing file: C:\Samples\List to Excel.xlsx")
workbook.easy_WriteXLSXFile_FromList_2 "C:\Samples\List to Excel.xlsx", _
        lstRows, xlsAutoFormat, "List"

' Connect to the database
Dim objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = _
        "Provider=SQLOLEDB;Server=(local);Database=northwind;User ID=sa;Password=;"
objConn.Open

Dim sQueryString
sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + " & _
               "'/' + CAST(Day(ord.OrderDate) AS varchar) + " & _
               "'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " & _
               "P.ProductName AS 'Product Name', O.UnitPrice AS Price, " & _
               "CAST(O.Quantity AS varchar) AS Quantity, " & _
               "O.UnitPrice * O. Quantity AS Value " & _
               "FROM Orders AS ord, [Order Details] AS O, Products AS P " & _
               "WHERE  O.ProductID = P.ProductID AND O.OrderID = ord.OrderID"

' Create the record set object
Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open sQueryString, objConn

' Create the list used to store the values
Dim lstRows
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the 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 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 list to Excel file
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & _
        "Writing file C:\Samples\List to Excel.xlsx."
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\List to Excel.xlsx", _
        lstRows, xlsAutoFormat, "List"

' Connect to the database
Dim objConn
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = _
        "Provider=SQLOLEDB;Server=(local);Database=northwind;User ID=sa;Password=;"
objConn.Open

Dim sQueryString
sQueryString = "SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + '/' + " & _
               "CAST(Day(ord.OrderDate) AS varchar) + '/' + " & _
               "CAST(year(ord.OrderDate) AS varchar) AS 'Order Date', " & _
               "P.ProductName AS 'Product Name', O.UnitPrice AS Price, " & _
               "CAST(O.Quantity AS varchar) AS Quantity, " & _
               "O.UnitPrice * O. Quantity AS Value " & _
               "FROM Orders AS ord, [Order Details] AS O, Products AS P " & _
               "WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID"

' Create the record set object
Dim objRS
Set objRS = CreateObject("ADODB.Recordset") 
objRS.Open sQueryString, objConn

' Create the list used to store the values
Dim lstRows 
Set lstRows = CreateObject("EasyXLS.Util.List")

' Add the 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 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 list to Excel file
WScript.StdOut.WriteLine("Writing file C:\Samples\List to Excel.xlsx.")
workbook.easy_WriteXLSXFile_FromList_2 "c:\Samples\List to Excel.xlsx", _
        lstRows, xlsAutoFormat, "List"

<!-- Extract the values from the database -->
<cfquery name="myQuery" datasource="northwind">
SELECT TOP 100 CAST(Month(ord.OrderDate) AS varchar) + 
'/' + CAST(Day(ord.OrderDate) AS varchar) + 
'/' + CAST(year(ord.OrderDate) AS varchar) AS 'Order_Date', 
P.ProductName AS 'Product_Name', O.UnitPrice AS Price, O.Quantity, 
O.UnitPrice * O. Quantity AS Value 
FROM Orders AS ord, [Order Details] AS O, Products AS P 
WHERE O.ProductID = P.ProductID AND O.OrderID = ord.OrderID
</cfquery>

<!-- Create the list used to store the values -->
<cfobject type="java" class="EasyXLS.Util.List" name="lstRows" action="CREATE">

<!-- Add the 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 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 list to Excel file -->
Writing file C:\Samples\List to Excel.xlsx
<cfset ret = workbook.easy_WriteXLSXFile_FromList("C:\Samples\List to Excel.xlsx",
        lstRows, xlsAutoFormat, "List")>

.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\\List to Excel.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\\List to Excel.xlsx", lstRows, 
  gateway.jvm.ExcelAutoFormat(gateway.jvm.Styles.AUTOFORMAT_EASYXLS1), "Sheet1")

Click here to see Continuous Code Listing