Excel XML Writer /Reader

• Download test - 8.13 KB

Introduction

This is an Excel XML Writer/Reader which enables to generate Excel XML with hidden columns and look-ups.

The main objective of this tool is to generate the Excel datasheet from data-table and to read the Excel sheet back to the data-table (Duplex communication between data-table and Excel sheet). Since we are going to use XML to get the Excel data, you no longer need to reference Excel interop component, so the performance should be good.

See the screen-shot below to get a quick view of this tool.

Background

Basic ASP.NET 2.0, Generics, XML knowledge required

Using the Component

Here, I will explain how to consume the component to generate the Excel sheet and to read the Excel sheet back to the data-table (I mean .NET datatable). In a later section, I will explain the code flow in detail.

Excel XML Writer Component

public static void exportToExcel(DataTable source, int? nHdnColumnStart, 
    int? nLookupSrcColStart, List<int> lstLookupCounts, List<int> lstDestLookupCols)

Excel XML ReaderComponent

public static DataTable ReadExcelXML(string ExcelXmlFile)

Client Call to Excel XML Writer

XMLExcelWriter.exportToExcel(ds.Tables[0], 11, 11, lstLookupCounts, lstDestLookupCols);

Client Call to Excel XML Reader

DataTable dt=XMLExcelReader.ReadExcelXML("D:\ExcelFile.xls");		  

Explanation on Component Call

public static void exportToExcel(DataTable source, int? nHdnColumnStart, 
    int? nLookupSrcColStart, List<int> lstLookupCounts, List<int> lstDestLookupCols)

1. exportToExcel is a method name.
2. source is a datatable you need to pass to the ExcelXML writer component.
3. nHdnColumnStart is the int parameter that expects the start column to hide a range of columns. For Example: if you provide 5 then 5,6,7., will be hidden in the Excel sheet.
4. nLookupSrcColStart is an interesting feature in the Writer component. It allows us to configure the lookup source columns in Excel. Here you need to tell the start column of the lookup source. The system will take the contiguous columns as the lookup sources. For example: if you provide 8 then 8,9,10., will be accounted as lookup sources.
5. lstLookupCounts – This is to show the lookup source column data counts.
6. lstDestLookupCols – This list will have the destination point for the source lookups. Please make sure that the sequence of this range is matching the sequence of nLookupSrcColStart. Example: I want to have the Name, SHOBBY, SJOB to be configured as lookup for a name column, then I will give the lstDestLookupCols as 1,7,8 and nLookupSrcColStart 11 (from 11^th column onwards I am taking the lookup sources, see the db script to get the clarity).

Points of Interest

Preparing tools using sqlserver and ASP.NET, application performance tuning