Exporting/importing tables to/from XML and Excel files

• Storing a table as an XML file
• Reading (importing) a table from an XML file
• Exporting a table to an excel file
• Importing a table from an excel file

In all of the export/import functions introduced below, DataTable class of C#/.NET is used as a gateway between finaquant tables (of type MatrixTable) and data containers like XML or excel files.

DataTable class is an in-memory representation of table data with a complex data structure accommodating almost all kinds of data types used in most common databases like MS SQL, Oracle and MySQL (see C# DataTable for more information).

MatrixTable class of finaquant is a matrix oriented representation of table data with a much simplified data structure (see attributes and numbers). The structure of MatrixTable is the outcome of a tradeoff between mathematical clarity and flexibility.

The advantage of using the DataTable class as a gateway to data containers is obvious: All kinds of adapters and interface functions for …

• exporting tables from DataTable to data containers, or
• importing tables from data containers to DataTable

… are already available in related C#/.NET libraries. Finaquant libraries need only to convert tables of type MatrixTable to DataTable, or vice versa: DataTable to MatrixTable.

First, create e test table for exporting

Following example generates a test table by combination. You can find a demonstration of this example as a method within DemoFunctions class of the initial demonstration package you can download together with finaquant® protos and calcs.

// First, create a test table by combinations
MatrixTable table1;
MetaData md;
TableFields tfields;
// call helper function to create test table
Create_test_table_helper(out table1, out md, out tfields);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table1 = \n" + table1);
// view table
MatrixTable.View_MatrixTable(table1, "Test table for export");

Storing a table as an XML file

A table of type MatrixTable can be stored as an XML file with the function Export_table_to_XML():

// Export table to XML file
MatrixTable.Export_table_to_XML(table1, Filename: "MyTable1",
    FileDir: @"C:\Windows\Temp\", IfAddTimeStamp: false);

A time stamp will be added to the end of file if the input parameter IfAddTimeStamp is set to true:

// Export table to XML file
MatrixTable.Export_table_to_XML(table1, Filename: "MyTable1",
    FileDir: @"C:\Windows\Temp\", IfAddTimeStamp: true);

Within the function Export_table_to_XML(), table1 of type MatrixTable is first converted into an object of type DataTable:

// Step 1: Covert MatrixTable to DataTable
DataTable dt = tbl.ToDataTable();
// Step 2: Store DataTable as XML file
dt.FQ_StoreTableAsXMLfile(Filename, FileDir, IfAddTimeStamp);

Reading (importing) a table from an XML file

A table of type MatrixTable can be imported from an XML file with the function Import_table_from_XML():

// import table with default null value replacements
var table2 = MatrixTable.Import_table_from_XML(md, Filename: "MyTable1",
    FileDir: @"C:\Windows\Temp\",
    TextReplaceNull: "NULL", NumReplaceNull: 0, KeyFigReplaceNull: 0.0);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table2 = \n" + table2);

Exporting a table to an excel file

A table of type MatrixTable can be exported to an excel file with the function Export_table_to_Excel():

// Make excel visible if ExcelFilePath = null (default value)
MatrixTable.Export_table_to_Excel(tbl: table1, ExcelFilePath: null);

The excel file is saved without ever making the file visible if a valid ExcelFilePath is given:

// Step -1: Convert MatrixTable to DataTable
var dt = MatrixTable.Export_To_DataTable(tbl);
// Step-2: export DataTable to excel
dt.FQ_ExportToExcelFile(ExcelFilePath);

As explained above at the beginning, a table of type MatrixTable is exported to the first sheet (Sheet1) of an excel file in two steps using the gateway class DataTable:

// Step -1: Convert MatrixTable to DataTable
var dt = MatrixTable .Export_To_DataTable(tbl);
// Step-2: export DataTable to excel
dt.FQ_ExportToExcelFile(ExcelFilePath);

Functions and data adapters for exporting a DataTable object into an excel file into a (2. step above) are already available in C#/.NET libraries. The correct (error-free) implementation of this second step may depend on the individual environment of the user (excel version etc.). So, check this error-prone step and your environment if you get an error message from the one-step export function.

Importing a table from an excel file

A table of type MatrixTable can be imported from an excel file with the function Import_table_from_Excel().

The excel sheet to be imported must contain valid field names in the header line that are already defined in MetaData with proper field types.

// Define all fields in MetaData
md = MetaData.CreateEmptyMasterData();
MetaData.AddNewField(md, "country", FieldType.TextAttribute);
MetaData.AddNewField(md, "car", FieldType.TextAttribute);
MetaData.AddNewField(md, "modelyear", FieldType.IntegerAttribute);
MetaData.AddNewField(md, "date", FieldType.DateAttribute);
MetaData.AddNewField(md, "sales_eur", FieldType.KeyFigure);

// 1-step import: Excel --> MatrixTable
MatrixTable table2 = MatrixTable.Import_table_from_Excel(md,
    ExcelFilePath: @"C:\Windows\Temp\MyTable.xlsx", SheetName: "Sheet1",
    TextReplaceNull: "NULL", NumReplaceNull: 0, KeyFigReplaceNull: 0.0);
// display table in immediate window
System.Diagnostics.Debug.WriteLine("table2 = \n" + table2);

The same table could be imported in two steps as follows:

// Step 1: excel --> DataTable
DataTable dt = new DataTable();
dt = dt.FQ_ImportFromExcelFile(Filepath: @"C:\Window\Temp\MyTable.xlsx", SheetName: "Sheet1");
// Step 2: DataTable --> MatrixTable
var table2 = MatrixTable.Import_from_DataTable(dt, md);

Functions and data adapters for reading an excel file into a DataTable object (1. step above) are already available in C#/.NET libraries. An error-free implementation of this first step may depend on the individual environment of the user (excel version etc.). So, check this error-prone step and your environment if you get an error message from the one-step import function.

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu