Populate an excel crosstab

Question

I'm attempting to populate an excel spreadsheet using Softartisans ExcelWriter (part of Office Writer), it fair easy if you need to "load" only one record, or a tabular table.

I need to fill a "Crosstab Table", something like this:

        01-may-2013        02-may-2013       03-may-2013 etc...

name

address

age

etc

etc

etc 

all data (date, name, address,...) are on the same record, as seen above, I need to use the date field as column header.

We can see it as instead of listing the data horizontally, I need to do it vertically. all data comes from a single table, anyone achieved this before?

I can populate the first column, but after more than a week of reading the documentation, and beggin google for the correct response, I'm really desperate.

If this is not possible in ExcelWriter, can you please recommend me how to generate a crosstab report from the web, it can be in xls or pdf. And easy enough for intermediate programmers.

Answer 1

Note: I work for SoftArtisans, makers of OfficeWriter.

If your data has unique values and all you need to do is transpose the data, then there are several options.

For example, if your data looks like this:

Date          Name          Address          Age
05/01/2013    Bob           70 Random Dr     54
05/02/2013    Carl          50 Unique Rd     76
05/03/2013    Fred          432 Tiger Lane   56
05/04/2013    Amy           123 Think Ave    23
05/05/2013    Dana          58 Turtle Path   67

And you want to import the data so that it looks like this:

Date     05/01/2013     05/02/2013     05/03/2013     05/04/2013     05/05/2013
Name     Bob            Carl           Fred           Amy            Dana
Address  70 Random Dr   50 Unique Rd   432 Tiger Lane 123 Think Ave  58 Turtle Path
Age      54             76             56             23             67

Easiest option - Import with ExcelApplication

The easiest option is to use the ExcelApplication object's Worksheet.ImportData method to programmatically import the data. To customize how the data is imported, you will need to set some of the DataImportProperties.

//Open or create a file with ExcelApplication
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create(ExcelApplication.FileFormat.Xlsx);

//Get a handle on the worksheet where you want to import the data
//You can also create a new worksheet instead
Worksheet ws = wb.Worksheets.CreateWorksheet("ImportedData");

//Create a DataImportProperties object
//Set the data import to transpose the data
DataImportProperties dataImportProps = wb.CreateDataImportProperties();
dataImportProps.Transpose = true;

//Import the data
DataTable dt = GetData(); //random method that returns some data
ws.ImportData(dt, ws.Cells["A1"], dataImportProps);

//Stream the output back to the client
xla.Save(wb, Page.Response, "Output.xlsx", false);

ImportData will not automatically import the header names of the data set. So you may also want to set DataImportProperties.UseColumnNames to TRUE to import the header names (Date, Name, Address, Age).

If you are importing numerical data, such as ages or dates, you may also want to set DataImportProperties.ConvertStrings to TRUE to make sure that they are imported as numbers and not as text.

Alternate method - Import with ExcelTemplate

An alternative method would be to use the ExcelTemplate object to import the data into an existing template file that contains placeholder data markers to indicate where the data should be imported.

ExcelTemplate also has DataBindingProperties which control how the data is imported when calling ExcelTemplate.BindData. One of the properties, DataBindingProperties.Transpose, will pivot the data. This property only takes affect if the data source is a two-dimensional array.

//Open a template with placeholder data markers
ExcelTemplate xlt = new ExcelTemplate();
xlt.Open("template.xlsx");

//Create DataBindingProperties and set it to transpose the data
DataBindingProperties dataBindProps = xlt.CreateDataBindingProperties();
dataBindProps.Transpose = true;

//Bind data to the template
//data is of type object[][]
//colNames is of type string[] e.g {"Date", "Name", "Address", "Age"}
xlt.BindData(data, colNames, "DataSource", dataBindProps);

//Process and save the template
xlt.Process();
xlt.Save(Page.Response, "Output.xlsx", false);

By default, ExcelTemplate does not import column names. To transpose and import the column names, you will need a separate data marker in the template (i.e. %%=$HeaderNames) and make a separate call to ExcelTemplate.BindColumnData to import the header names into the column.

//headerNames is of type object[]
//dataBindProps2 is a second DataBindingProperties that is not set to transpose
xlt.BindColumnData(headerNames, "HeaderNames", dataBindProps2);