Import/Export

xlsgen > overview > Import / Export

xlsgen has flexible import and export facilities. You can :

import directly a CSV file (with or without options)
import indirectly an XML file (through an XSL stylesheet)
import a .NET dataset
import a SQL data source
export as CSV
export as XML (data-only)
export as PDF

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, ...). 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.CoXlsEngineClass engine = new xlsgen.CoXlsEngineClass();

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 are :

the separation character between fields in the CSV file. By default it's semi-colon. (SeparationCharacter)
the decimal character used to represent decimal numbers. By default, xlsgen automatically handles the comma character and the dot character. (DecimalCharacter)
whether or not you'd like to import the column headers. By default it is yes. (ImportHeaders)
if you'd like to import the column headers, then how many of such rows. By default it imports all rows of the column headers. (HeaderRows)
the formatting style of the imported column headers. By default, the font is bold. (HeaderStyle)
whether you'd like to preserve or trim the spaces on the left and right of each imported data value. By default spaces are preserved. (PreserveWhitespace)
the encoding of the data is UTF8. By default, it is assumed the data is encoded using the user locale code page (see Windows, regional settings). (UTF8Encoding)
whether to create more worksheets if the CSV file falls off the Excel boundaries, 65536 rows, 256 columns. By default it is no. If this option is not enabled, huge CSV files will be truncated to whatever fits the current worksheet. (MultipleWorksheets)
whether the importer should abort if invalid data is getting imported. By default it is no. An example of invalid data is a non-date string in a date column. (AbortIfInvalidData)
where a column should be imported in the worksheet. Default is first row of i-th column of the worksheet. Column index starts at 1. (ColumnPlacementByIndex)
the data type of a column used to bind the imported data and the actual data in the Excel workbook. Default is automatic inference of the data type. Column index starts at 1. (ColumnDataTypeByIndex)
the style of a column. Default is default style. Column index starts at 1. (ColumnStyleByIndex)
whether the importer should fail on error if there is an empty field in the given column. Default is no. Column index starts at 1. (ColumnFailIfEmptyByIndex)

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.CoXlsEngineClass engine = new xlsgen.CoXlsEngineClass();

// 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();

}

Import an XML file

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 :

An input XML file called books.xml, which as the name suggests is a collection of book titles
An XSL stylesheet called books_csv.xsl, which transforms the XML file into a CSV file
We are not interested in all fields of books.xml, rather we are interested in the ISBN, the title and the author's last name
The CSV file will use the comma (,) as a field separator. We need to make sure during the XML-to-CSV transform that whenever data includes a comma, it has to be enclosed in double-quotes so that it meets the rules of a regular CSV file (disambiguates the separator and actual content).

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 :

This stylesheet is obviously designed to process books.xml. If the XML file used a different schema, a specifically designed stylesheet would be needed. There is no general purpose stylesheet to be used unfortunately, which means anyone willing to transform XML into CSV will have to get decent knowledge on Xpath expressions and XSLT transforms.
This stylesheet has many xls:if statements in it that are meant to try to catch whether the content uses commas, the field separator, in which case the content must be enclosed in double-quotes.
The stylesheet takes into account the fact that not every author's name has a last name, sometimes it's just a name.
We insert a newline after all fields of the current record are done.

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 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 as part of the install which explains how to import a general purpose .NET dataset. The code samples shows how to preserve the dataset column data types whe doing the import into the Excel workbook.

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 XML (angle brackets, fixed schema), as CSV (semi-colon separated values) and as PDF.

Export as XML

The XML export provides a cross-platform way to save content. XML content follows a schema. The schema used cannot be controlled by the developer, although this could happen in the future based on demand. The schema is conside and self-descriptive to maximize the usefulness of using XML in the first place. The encoding defaults to UTF-8 which means strings are encoded in ways understandable in any country of the world.

The XML output is made of a header followed by the content of the worksheet. The content of the worksheet is a collection of rows. Each rows contains a collection of columns. Each column contains the content of a cell. At this point, the developer can optionally choose to add what is known as data types. Data types are quite verbose but provide a great way to know which data type identifies a given cell, for instance a time type. After all, the more typed the content is, the more accurate are operations made on them. Here is an example of XML content :

<?xml version="1.0"?>
<WORKSHEET NAME="Feuil1">
<DIMENSIONS>
 <ROWMIN>4</ROWMIN>
 <ROWMAX>4</ROWMAX>
 <COLMIN>3</COLMIN>
 <COLMAX>5</COLMAX>
</DIMENSIONS>
<ROWS>
  <R>
    <C T="number">2</C>
    <C T="number">3</C>
    <C T="number">4</C>
  </R>
 </ROWS>
</WORKSHEET>

Producing the XML for a given worksheet is achieved by requesting the Export object from the worksheet (see the IXlsWorksheetExport interface), and then call the exporter of your choice with appropriate parameters.

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' get the second worksheet
Dim wksht002 As IXlsWorksheet
Set wksht002 = wbk.WorksheetByIndex(2)

' export the worksheet as xml (export the content and data types)
wksht002.Export.ExportAsXML("filename.xml", True)

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"" );

// get the second worksheet
IXlsWorksheet wksht002 = wbk.get_WorksheetByIndex(2);

// export the worksheet as xml (export the content and data types)
wksht002.Export.ExportAsXML("filename.xml", 1 /*datatype*/);

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the second worksheet
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];

  // export the worksheet as xml (export the content and data types)
  wksht002->Export->ExportAsXML(L"filename.xml", TRUE /*datatype*/);

  wbk->Close();
}

In the content, some characters are replaced with XML entities. These are :

& ==> &
< ==> <
> ==> >
" ==> "

Export as CSV

CSV is a semi-colon separated file format. CSV actually stands for comma-separated values, but for many years in the software industry commas have proven to be problematic to disambiguate floating point separators, locales and content. The worksheet is a grid whose content is exported as CSV where each row of the grid is expressed as semi-colon separated fields, ending with a carriage return character. Whenever the content contains a semi-colon, the content is surrounded by double quotes. Double quotes in content are themselves doubled. By definition, a CSV file does not define the dimensions of the worksheet. It's raw content. That said, the width and height can be computed by reading the file and incrementing an appropriate counter.

Exporting the content of a worksheet as CSV is analogous to XML.See the IXlsWorksheetExport interface. The difference is that the developer can choose the encoding between UTF-8 and the current local charset.

Here is an example CSV file :

hghhhgf;fghgf hgfhgf;fh gfhfh
ttryty;yrtytryt;tytry tryrtyrty
tt;trtrettrtrettetertret;rtrt
ytuuyuu;tuuuututyuy;tyuytuytuuyt

And here is how to export a worksheet as CSV with code :

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' get the second worksheet
Dim wksht002 As IXlsWorksheet
Set wksht002 = wbk.WorksheetByIndex(2)

' export the worksheet as csv (export the content, UTF-8 encoding)
wksht002.Export.ExportAsCSV("filename.xml", True)

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngineClass excel = new xlsgen.CoXlsEngineClass();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"" );

// get the second worksheet
IXlsWorksheet wksht002 = wbk.get_WorksheetByIndex(2);

// export the worksheet as csv (export the content, UTF-8 encoding)
wksht002.Export.ExportAsCSV("filename.xml", 1 /*use UTF8 encoding*/);

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the second worksheet
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];

  // export the worksheet as csv (export the content, UTF-8 encoding)
  wksht002->Export->ExportAsCSV(L"filename.xml", TRUE /*use UTF8 encoding*/);

  wbk->Close();
}

Export as PDF

The PDF file format makes xlsgen even more suitable for reporting purposes. Very often, Adobe's PDF documents are the best choice to ensure 1) same rendering on any operating system 2) read-only document 3) compact size.

The PDF file output supported natively by xlsgen is compatible with Adobe Acrobat Reader 5.0 and above, and other products from the Acrobat family.

How to export a worksheet as PDF is straight forward : (here in C/C++)

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the second worksheet
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];

  // export the worksheet as pdf
  wksht002->Export->ExportAsPDF(L"filename.pdf");

  wbk->Close();
}

Another way to create PDF files is to pass .pdf filenames directly when making a New(), Open(), NewInMemory(), OpenInMemory() call. Doing so orders xlsgen to create a PDF output file representing the entire spreadsheet (not just the current worksheet of the spreadsheet as in above). This mechanism makes xlsgen as much a genuine PDF generator than an Excel generator :

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

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

  xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( "samplesheet" );
  wksht->Label[1][2] = L"Hello world!";

  wbk->Close();
}