xlsgen > overview > Import

xlsgen has flexible import and export facilities. You can :

 

Import an Excel 2003 XML file

The Excel 2003 XML file format is Microsoft Office's first attempt at saving Excel spreadsheets using XML. They retain much of what makes a spreadsheet, cells, formatting, formulas, a number of rich objects, but they lack some others by design, most notably charts. In 2006, Microsoft came with a much more mature file format, and that was XLSX files. Nevertheless a number of Excel customers still use this Excel 2003 XML file format because it is much richer and capable than say CSV files. xlsgen can read such file by just passing the filepath (or memory buffer) in the regular Open() method.

Imported features are :

 

Import an Excel XLSB file

The Excel XLSB file format is a binary file format that is meant to save all of the features of Excel XLSX files, but with load and save performance speed in mind. As a consequence, it is a completely different file format. xlsgen can read such file by just passing the filepath (or memory buffer) in the regular Open() method.

Imported features are :

 

Import a CSV file

There are two ways to import a CSV file. The most simple and intuitive way is to use the Open method from the IXlsEngine object, and pass it a CSV filename. The other way is to use the Import method from the IXlsWorksheet object.

The code sample import_csv, part of the install, shows how to use both ways.

 

Import a CSV file using the Open method

To import a CSV file into an arbitrary Excel workbook, you can use the Open method from the IXlsEngine object. The benefits are that it is intuitive, simple to use, and that xlsgen's automatic data type discovery is used to ensure that the data is imported using the proper cell value types (strings, numbers, dates, currencies, ...). Here is an example of how to use the method :

Java code
XlsEngine engine = new XlsEngine();

XlsWorkbook wbk = engine.Open("datasource.csv", "ResultingFile.xls");

// do some work with the workbook
...

wbk.Close();
VB code
Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("datasource.csv", "ResultingFile.xls")

' do some work with the workbook
...

wbk.Close

Set engine = Nothing
C# code
xlsgen.CoXlsEngine engine = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = engine.Open("datasource.csv", "ResultingFile.xls");

// do some work with the workbook
...

wbk.Close();
engine = null;
C/C++ code
{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"datasource.csv", L"ResultingFile.xls");

  // do some work with the workbook
  ...

  wbk->Close();
}

If you'd rather set one or more import options, then you probably want to try the other way.

 

Import a CSV file using the Import method

The important benefits of the Import method available from the IXlsWorksheet interface is that you can use one or more of the flexible import options. Those options from the IXlsCSVImportOptions interface are :

 

Here is a simple example of how to use some of those options :

Java code
XlsEngine excel = new XlsEngine();

// we create a regular Excel file, and use the import interfaces
//  data types are bound
//  a special formatting style is used for the date column and the price column

XlsWorkbook workbook = engine.New("ResultingFile.xls");
XlsWorksheet wksht = workbook.AddWorksheet("Sheet1");

XlsStyle style = wksht.NewStyle();

XlsStyle styleDate = wksht.NewStyle();
styleDate.putFormat("dd-mmm-yyyy");
styleDate.Apply();

XlsStyle stylePrice = wksht.NewStyle();
stylePrice.getPattern().putBackgroundColor(0x00FF0000);
stylePrice.putFormat("###.00 $");
stylePrice.Apply();

style.Apply();

XlsCSVImportOptions options = wksht.getImport().getCSV().getOptions();
options.putColumnStyleByIndex(3, styleDate);
options.putColumnStyleByIndex(4, stylePrice);

wksht.getImport().getCSV().ImportFile("datasource.csv");
wksht.getColumns("A1:D1").putAutoFit(true);

workbook.Close();
VB code
Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

' we create a regular Excel file, and use the import interfaces
'  data types are bound
'  a special formatting style is used for the date column and the price column


Dim wbk2 As xlsgen.IXlsWorkbook
Set wbk2 = engine.New("ResultingFile.xls")

Dim wksht2 As xlsgen.IXlsWorksheet
Set wksht2 = wbk2.AddWorksheet("Sheet1")

Dim style As xlsgen.IXlsStyle
Set style = wksht2.NewStyle()

Dim styleDate As xlsgen.IXlsStyle
Set styleDate = wksht2.NewStyle()
styleDate.Format = "dd-mmm-yyyy"
styleDate.Apply

Dim stylePrice As xlsgen.IXlsStyle
Set stylePrice = wksht2.NewStyle()
stylePrice.Pattern.BackgroundColor = &HFF0000
stylePrice.Format = "###.00 $"
stylePrice.Apply

style.Apply

Dim options As xlsgen.IXlsCSVImportOptions
Set options = wksht2.Import.CSV.options
options.ColumnStyleByIndex(3) = styleDate
options.ColumnStyleByIndex(4) = stylePrice

wksht2.Import.CSV.ImportFile ("datasource.csv")
wksht2.Columns("A1:D1").AutoFit = True

wbk2.Close

Set engine = Nothing
C# code
xlsgen.CoXlsEngine engine = new xlsgen.CoXlsEngine();

// we create a regular Excel file, and use the import interfaces
//  data types are bound
//  a special formatting style is used for the date column and the price column

IXlsWorkbook workbook = engine.New("ResultingFile.xls");
IXlsWorksheet wksht = workbook.AddWorksheet("Sheet1");

IXlsStyle style = wksht.NewStyle();

IXlsStyle styleDate = wksht.NewStyle();
styleDate.Format = "dd-mmm-yyyy";
styleDate.Apply();

IXlsStyle stylePrice = wksht.NewStyle();
stylePrice.Pattern.BackgroundColor = 0xFF0000;
stylePrice.Format = "###.00 $";
stylePrice.Apply();

style.Apply();

IXlsCSVImportOptions options = wksht.Import.CSV.Options;
options.set_ColumnStyleByIndex(3, styleDate);
options.set_ColumnStyleByIndex(4, stylePrice);

wksht.Import.CSV.ImportFile("datasource.csv");
wksht.get_Columns("A1:D1").AutoFit = 1;

workbook.Close();

engine = null;
C/C++ code
{
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

// we create a regular Excel file, and use the import interfaces
//  data types are bound
//  a special formatting style is used for the date column and the price column

xlsgen::IXlsWorkbookPtr workbook = engine->New(L"ResultingFile.xls");
xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet(L"Sheet1");

xlsgen::IXlsStylePtr style = wksht->NewStyle();

xlsgen::IXlsStylePtr styleDate = wksht->NewStyle();
styleDate->Format = L"dd-mmm-yyyy";
styleDate->Apply();

xlsgen::IXlsStylePtr stylePrice = wksht->NewStyle();
stylePrice->Pattern->BackgroundColor = 0xFF0000;
stylePrice->Format = L"###.00 $";
stylePrice->Apply();

style->Apply();

xlsgen::IXlsCSVImportOptionsPtr options = wksht->Import->CSV->Options;
options->ColumnStyleByIndex[3] = styleDate;
options->ColumnStyleByIndex[4] = stylePrice;

wksht->Import->CSV->ImportFile(L"datasource.csv");
wksht->Columns[L"A1:D1"]->AutoFit = TRUE;

workbook->Close();

}

 

And here is another example using custom data mapping :

C/C++ code
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

// we create a regular Excel file, and use the import interfaces
//  data types are custom bound
//  since we know the incoming data uses a particular data format

xlsgen::IXlsWorkbookPtr workbook = engine->New(L"ResultingFile.xls");
xlsgen::IXlsWorksheetPtr wksht = workbook->AddWorksheet(L"Sheet1");

xlsgen::IXlsCSVImportOptionsPtr options = wksht->Import->CSV->Options;
options->ColumnDataMappingFormatByIndex[1] = L"YYYY.MM.DD";
options->ColumnDataMappingFormatByIndex[2] = L"HH:MM";

wksht->Import->CSV->ImportFile(L"EURUSD_ticks.csv");

workbook->Close();

---- EURUSD_ticks.csv excerpt -----------------------------------------------
2011.10.25,03:00,1.39076,1.39092,1.39060,1.39061,41
2011.10.25,03:01,1.39062,1.39070,1.39029,1.39033,37
2011.10.25,03:02,1.39031,1.39044,1.39015,1.39042,55
2011.10.25,03:03,1.39040,1.39040,1.38997,1.38997,51
2011.10.25,03:04,1.38997,1.39028,1.38997,1.39018,48
2011.10.25,03:05,1.39017,1.39034,1.39009,1.39016,41
2011.10.25,03:06,1.39020,1.39025,1.39001,1.39009,55
2011.10.25,03:07,1.39017,1.39041,1.39014,1.39037,40
2011.10.25,03:08,1.39037,1.39078,1.39037,1.39077,40
2011.10.25,03:09,1.39079,1.39112,1.39079,1.39094,94
2011.10.25,03:10,1.39095,1.39107,1.39086,1.39091,71
2011.10.25,03:11,1.39093,1.39105,1.39087,1.39102,34
2011.10.25,03:12,1.39101,1.39102,1.39075,1.39098,57
2011.10.25,03:13,1.39098,1.39102,1.39077,1.39077,21
2011.10.25,03:14,1.39076,1.39078,1.39055,1.39061,72
---- EURUSD_ticks.csv excerpt -----------------------------------------------

If we import EURUSD_ticks.csv as is, without custom data mapping, the resulting will not read the first column as a date, but as a raw string (notice the left alignment), therefore unable to be play its role in Excel calculations and display. The second column will be read as a time which is correct but not displayed in a human-friendly manner. Here is a screen capture of what it would look like in Excel :


Importing EURUSD_ticks.csv in xlsgen with default mappings results in incorrect data

 

By applying custom data mapping for both first and second columns, the dates are correctly imported and time is correctly displayed :


Importing EURUSD_ticks.csv in xlsgen with custom mappings results in correct data

 

 

Import an XML file (indirectly)

Even though xlsgen cannot directly import an XML file, it's easy to transform an XML file into a CSV file thanks to a XSL stylesheet, and then import the CSV file itself. Here is an example of how it works. Let's say we have :

Here is books.xml :

<?xml version='1.0'?>
<!-- This file represents a fragment of a book store inventory database -->
<bookstore>
  <book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0">
    <title>The Autobiography, of Benjamin Franklin</title>
    <author>
      <first-name>Benjamin</first-name>
      <last-name>Franklin</last-name>
    </author>
    <price>8.99</price>
  </book>
  <book genre="novel" publicationdate="1967" ISBN="0-201-63361-2">
    <title>The Confidence Man</title>
    <author>
      <first-name>Herman</first-name>
      <last-name>Melville</last-name>
    </author>
    <price>11.99</price>
  </book>
  <book genre="philosophy" publicationdate="1991" ISBN="1-861001-57-6">
    <title>The Gorgias</title>
    <author>
      <name>Plato</name>
    </author>
    <price>9.99</price>
  </book>
</bookstore>

Here is the XSL stylesheet books_csv.xsl :

<!DOCTYPE stylesheet [
<!ENTITY newln "&#xD;&#xA;">
]>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

    <xsl:output method="text" encoding="utf-8"/>

    <xsl:template match="bookstore">
       <xsl:apply-templates select="book"/>
    </xsl:template>

    <xsl:template match="book">
       <xsl:if test="contains(@ISBN, ',')">&quot;</xsl:if>
       <xsl:value-of select="@ISBN"/>
       <xsl:if test="contains(@ISBN, ',')">&quot;</xsl:if>
       <xsl:text>,</xsl:text>
       <xsl:if test="contains(title, ',')">&quot;</xsl:if>
       <xsl:value-of select="title"/>
       <xsl:if test="contains(title, ',')">&quot;</xsl:if>
       <xsl:text>,</xsl:text>
       <xsl:apply-templates select="author"/>
       <xsl:text>&newln;</xsl:text>
    </xsl:template>

    <xsl:template match="author">
       <xsl:apply-templates select="last-name"/>
       <xsl:apply-templates select="name"/>
    </xsl:template>

    <xsl:template match="last-name">
       <xsl:if test="contains(., ',')">&quot;</xsl:if>
       <xsl:value-of select="."/>
       <xsl:if test="contains(., ',')">&quot;</xsl:if>
    </xsl:template>

    <xsl:template match="name">
       <xsl:if test="contains(., ',')">&quot;</xsl:if>
       <xsl:value-of select="."/>
       <xsl:if test="contains(., ',')">&quot;</xsl:if>
    </xsl:template>

</xsl:stylesheet>

Note :

XSLT processors are common on every single programming platform. In the following, we are going to use the XSLT processor that is part of the .NET run-time, but suffice to say there are many others (for instance, for C/C++ developers, there is libxslt).

The following .NET code simply processes the transform :

using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;

XPathDocument input = new XPathDocument ("books.xml"); // input XML file

XslTransform trans = new XslTransform();
trans.Load("books_csv.xsl"); // XSL stylesheet

XmlTextWriter destination = new XmlTextWriter("result.csv", null); // output CSV file
trans.Transform(input, null, destination);
destination.Close();

And the resulting CSV file is :

1-861003-11-0,"The Autobiography, of Benjamin Franklin",Franklin
0-201-63361-2,The Confidence Man,Melville
1-861001-57-6,The Gorgias,Plato

With the CSV file in hands, it's now possible to import it in xlsgen directly. How it works is explained in previous sections of this page.

 

 

Import an XML file

xlsgen imports structured data inside an XML file/stream/buffer with and without XPath selection. xlsgen provides auto-import and custom-import. Auto-import infers table columns as well as data types. Custom import lets you add personal formatting, particularly number and color formatting.

Auto-import XML files is achieved by passing an XML file (local or url) to the engine Open() method. Auto-import will find structured data inside the XML document.

For instance here is a sample XML document, a bookstore holding a number of books :

<?xml version="1.0" encoding="UTF8"?>
<Bookstore>
   <!--J&R Booksellers Database-->
   <Book Genre="" In_Stock="Yes">
      <Title>The Round Door</Title>
      <Author>Tom Evans</Author>
      <Year_Published>1996</Year_Published>
      <ISBN>0-9546-0274-3</ISBN>
      <Price>$23.00</Price>
      <Review>An Intriguing Tale Of A Round Door In A Wall</Review>
   </Book>
   <Book Genre="Non-Fiction" In_Stock="Yes">
      <Title>Creating Real Xml Applications</Title>
      <Author>Bill Eaton</Author>
      <Year_Published>1998</Year_Published>
      <ISBN>7-4562-0167-8</ISBN>
      <Price>$35.00</Price>
      <Review>A Look At How To Build Real Xml Applications</Review>
   </Book>
   <Book Genre="Fiction" In_Stock="No">
      <Title>Over The Hills Of Yukon2</Title>
      <Author>Bert Colewell</Author>
      <Year_Published>1993</Year_Published>
      <ISBN>5-6524-3054-1</ISBN>
      <Price>$22.00</Price>
      <Review>A Warm Story About A Man And A Moose In Yukon</Review>
   </Book>
   <Book Genre="Fiction" In_Stock="Yes">
      <Title>The Lion's Gold</Title>
      <Author>Daphne Griswald</Author>
      <Year_Published>1989</Year_Published>
      <ISBN>6-7896-2498-2</ISBN>
      <Price>$15.00</Price>
      <Review>One Of The Most Compelling Books Since "The Tiger's Silver".</Review>
   </Book>
</Bookstore>

In order to import the books into a spreadsheet, the following sample code can be used :

Java code
XlsEngine engine = new XlsEngine();

XlsWorkbook wbk = engine.Open("C:\\tmp\\Bookstore.xml", "output.xlsx" );
wbk.Close();
VB/VB.NET code
Dim engine
Set engine = CreateObject("ExcelGenerator.ARsTdesign")

Dim wbk As IXlsWorkbook 
Set wbk = engine.Open "C:\tmp\Bookstore.xml", "output.xlsx"
wbk.Close
C# code
CoXlsEngine engine = new CoXlsEngine();

IXlsWorkbook wbk = engine.Open("C:\\tmp\\Bookstore.xml", "output.xlsx" );
wbk.Close();
C/C++ code
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->Open(L"C:\\tmp\\Bookstore.xml", L"output.xlsx" );
wbk->Close();


XML document imported in xlsgen

 

Custom-import is available in the worksheet import interface. If for the need of an application, the structured data must be filtered or selected inside the XML document, it is possible to pass an XPath selector, which is available in the custom import interface.

Here are XPath selection examples :

And here is the corresponding source code :

Java code
XlsEngine engine = new XlsEngine();

XlsWorkbook wbk = engine.New( "output.xlsx" );

XlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );
wksht.getImport().getXML().getOptions().putXPath( "/Bookstore/Book" );
wksht.getImport().getXML().ImportFile( "C:\\tmp\\Bookstore.xml" );

wbk.Close();
VB/VB.NET code
Dim engine
Set engine = CreateObject("ExcelGenerator.ARsTdesign")

Dim wbk As IXlsWorkbook 
Set wbk = engine.New( "output.xlsx" )

Dim wksht As IXlsWorksheet 
Set wksht = wbk.AddWorksheet( "Sheet1" )

wksht.Import.XML.Options.XPath = "/Bookstore/Book"
wksht.Import.XML.ImportFile( "C:\\tmp\\Bookstore.xml" )

wbk.Close
C# code
CoXlsEngine engine = new CoXlsEngine();

IXlsWorkbook wbk = engine.New( "output.xlsx" );

IXlsWorksheet wksht = wbk.AddWorksheet( "Sheet1" );
wksht.Import.XML.Options.XPath = "/Bookstore/Book";
wksht.Import.XML.ImportFile( "C:\\tmp\\Bookstore.xml" );

wbk.Close();
C/C++ code
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"output.xlsx" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"Sheet1" );
wksht->Import->XML->Options->XPath = L"/Bookstore/Book";
wksht->Import->XML->ImportFile( L"C:\\tmp\\Bookstore.xml" );

wbk->Close();

Other custom XML import options are :

 

Import a web page

Many web pages include HTML-based table data that a business application may be interested in importing in a regular spreadsheet.

An arbitrary URL can be passed to the Open() method. It is also possible to customize the import (column styles including number formats, header style) by invoking a regular import process exposed in the IXlsWorksheetImport interface. And to import a web page stored in a memory buffer.

The internal xlsgen algorithm parses the HTML markup, finds tables, aggregates data in it, and imports the most consistent set. It looks for HTML markup like the following :

<table>
    <tr>
       <td>Column 1</td><td>Column 2</td><td>Column 3</td>
    </tr>
    <tr>
       <td>Data 1</td><td>Data 2</td><td>Data 3</td>
    </tr>
    <tr>
       <td>Data 1'</td><td>Data 2'</td><td>Data 3'</td>
    </tr>
    <tr>
     ...
    </tr>
</table>

xlsgen also looks for hierarchical div sections in the HTML markup, if looking for tables does not bring anything relevant :

<div>
    <div>
        <div>Data 1</div><div>Data 2</div><div>Data 3</div>
        <div>Data 1'</div><div>Data 2'</div><div>Data 3'</div>
        ...
    </div>
</div>

Here is an example.

The URL is http://www.insee.fr/fr/themes/info-rapide.asp?id=10&date=20150807

A screenshot of which is :


xlsgen can import a web page

 


xlsgen can import a web page in a spreadsheet

 

Here is a first sample code :

C/C++ code
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk;
wbk = engine->Open(L"http://www.insee.fr/fr/themes/info-rapide.asp?id=10&date=20150807", L"output.xlsx" );
wbk->Close();

And another sample code which uses custom html options :

C/C++ code
xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"output.xlsx" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet(L"Sheet1");

xlsgen::IXlsHTMLImportPtr html = wksht->Import->HTML;

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Pattern->BackgroundColor = 0xFF0000;

html->Options->ColumnStyleByIndex[2] = style;

html->ImportFromURL(L"http://www.insee.fr/fr/themes/info-rapide.asp?id=10&date=20150807");

wbk->Close();

Other custom HTML import options are :

 

Import a JSON stream

JSON (Javascript Object Notation) is all the rage in modern web applications and has taken over XML for storing data in streams thanks to how much it fits with the Javascript programming language. JSON is highly hierarchical in nature and defines several data types so that a consumer application knows upfront the type of data it is dealing with (with the notable exception of dates which remain application-specific).

(sample JSON)

{
    "count":2,
    "cache":false,
    "environment":"live",
    "remainingAccesses":231,
    "data":[
        {"date":"2013-12-09", "service":"Be On", "campaign_id":17088, "campaign_name":"DE Peek & Cloppenburg Review Urban Karaoke", 
"project_id":119, "websites":3, "impressions":48, "views":1, "views_paid":0, "redirects":0, "playtoends":0},
        {"date":"2013-12-09", "service":"Be On", "campaign_id":17280, "campaign_name":"DE Telekom Technischer Support",  
"project_id":137, "websites":4, "impressions":189, "views":47, "views_paid":0, "redirects":2, "playtoends":9}
    ]
}

xlsgen can import any JSON stream from a regular URL, a file or a memory buffer. The import process is exposed in the IXlsWorksheetImport interface. A JSON stream can also be read by passing the URL directly to the workbook Open() method.

The import can be customized to provide styles on a per column basis.

xlsgen automatically infers hierarchies, cardinalities and what looks like a table set.

C/C++ code
xlsgen::IXlsJSONImportPtr json = worksheet->Import->JSON;

xlsgen::IXlsStylePtr style = worksheet->NewStyle();
style->Pattern->BackgroundColor = 0xFFFF00;
style->Font->Italic = TRUE;

json->Options->ColumnStyleByName[L"City"] = style;
json->ImportFile(L"input\\jsonfile3.json");

Other custom JSON import options are :

 

Import a .NET dataset

If you are programming the xlsgen object model with a .NET programming language, chances are you are also dealing with .NET datasets (the System.Data.DataSet class). xlsgen provides no direct .NET support through the object model since it would make it .NET-dependent, but there is one code sample (called dataset) as part of the install which explains how to import a general purpose .NET dataset. The code sample shows how to preserve the dataset column data types when doing the import into the Excel workbook.

The following is the C# version of the code sample :

C# code
// this sample code reads a .NET dataset and imports it in xlsgen

string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                   @" Data Source=sampledatabase.mdb";

OleDbConnection con = new OleDbConnection(conString);

try 
{
    con.Open();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    return;
}

string strSql = "select * from Datasource";

OleDbDataAdapter dadapter = new OleDbDataAdapter();
dadapter.SelectCommand = new OleDbCommand(strSql, con);
DataSet ds = new DataSet();

dadapter.Fill(ds);

Console.WriteLine ("Database dump\n-------------");

foreach (DataTable t in ds.Tables)
{
    Console.WriteLine(t.TableName);
    foreach (DataRow r in t.Rows)
    {
        foreach (DataColumn c in t.Columns)
            if (r[c] != null)
                 Console.Write(" " + r[c]);
        Console.WriteLine();
    }
}

// sample code follows

CoXlsEngine engine = new CoXlsEngine();

IXlsWorkbook workbook = engine.New("dataset.xls");
IXlsWorksheet worksheet = workbook.AddWorksheet("Sheet1");

IXlsStyle styleDate = null;


DataTable datasource = ds.Tables[0];

int nbcolumns = datasource.Columns.Count;

foreach (DataColumn c in datasource.Columns)
{
    int row = 0;
    foreach (DataRow r in datasource.Rows)
    {
        worksheet.DefaultStyle = 16;

        switch (c.DataType.ToString())
        {
            case "System.Boolean" :
            case "System.Byte" :
            case "System.Int16" : 
            case "System.Int32" : 
            case "System.Int64" : 
            case "System.SByte" : 
            case "System.UInt16" : 
            case "System.UInt32" : 
            case "System.UInt64" : 
                {
                    worksheet.set_Number(1 + row, 1 + c.Ordinal, (int) r[c]);
                }
                break;
            case "System.Decimal" : 
            case "System.Single" : 
            case "System.Double" : 
                {
                    worksheet.set_Float(1 + row, 1 + c.Ordinal, (double) r[c]);
                }
                break;
            case "System.Char" : 
            case "System.String" : 
                {
                    worksheet.set_Label(1 + row, 1 + c.Ordinal, r[c].ToString());
                }
                break;
            case "System.DateTime" : 
            case "System.TimeSpan" : 
                {
                    if (styleDate == null)
                    {
                        styleDate = worksheet.NewStyle();
                        styleDate.Format = "dd-mmm-yyyy";
                    }

                    styleDate.Apply();

                    worksheet.set_Date(1 + row, 1 + c.Ordinal, r[c].ToString());
                }
                break;


        }

        row++;
    }

    Console.WriteLine();
}

workbook.Close();

con.Close();

 

Import a SQL data source (Access, SQL Server, Oracle, ...)

If you are manipulating a SQL data source, you probably want to use the data source queries mechanism that is built in xlsgen.

 

 

Export

Export includes the ability to save the content as a .NET dataset, CSV (semi-colon separated values), as XML (angle brackets, fixed schema) and as PDF.

 

xlsgen documentation. © ARsT Design all rights reserved.