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)
exportToExcel
is a method name. source
is a datatable
you need to pass to the ExcelXML
writer component. 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. 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. lstLookupCounts
– This is to show the lookup source column data counts. 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 11th 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