Click here to Skip to main content
Click here to Skip to main content

Excel XML Writer /Reader

, 2 Aug 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Excel XML Writer /Reader - Enables to generate Excel XML with hidden columns and lookups


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.



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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior) Cognizant Technology Solutions
India India
Expertise in Asp.Net,Worked in variety of domains.
Have fair knowledge in using design patterns.
Presently he is working as Senior developer with cognizant India.His interest is to develop tools using Sqlserver,C#.Net.

Comments and Discussions

QuestionSee also this: PinprofessionalDietmar Schoder29-Jul-14 6:25 
GeneralMy vote of 5 PinmemberMember 917615427-Jun-12 20:51 
Questionwhat about Excel format? Pinmemberbatsword13-Sep-11 16:47 
AnswerRe: what about Excel format? PinmemberFilipKrnjic24-Oct-11 23:10 
you could try this Excel C# / VB.NET component to easily export / import DataTable to Excel (XLS, XLSX).
Here is a sample Excel C# code:
var ef = new ExcelFile();
var dataTable = ef.Worksheets[0].CreateDataTable(ColumnTypeResolution.Auto);
ef.Worksheets.Add(ef.Worksheets[0].Name + " Copy").InsertDataTable(dataTable, "A1", true);

GeneralMy vote of 5 Pinmemberseenit6-Sep-11 19:39 
GeneralMy vote of 5 PinmemberShubha_India7-Apr-11 9:04 
GeneralPlease correct link to code PinmemberBo Vistisen1-Aug-10 22:46 
GeneralRe: Please correct link to code Pinmemberrajeshitpro2-Aug-10 8:44 
GeneralSource code link is broken PinmemberTony Bermudez30-Jul-10 19:05 
GeneralRe: Source code link is broken Pinmemberrajeshitpro2-Aug-10 8:44 
GeneralRe: Source code link is broken PinmemberGabriel X27-Jun-12 20:56 
GeneralMore Details are Needed PinmemberGil Fink25-Jul-10 9:25 
GeneralRe: More Details are Needed Pinmemberrajeshitpro30-Jul-10 18:39 
GeneralAdd more details... PinmentorSandeep Mewara25-Jul-10 8:31 
GeneralFew tips for you.. PinmvpMd. Marufuzzaman25-Jul-10 6:30 
GeneralRe: Few tips for you.. Pinmemberrajeshitpro30-Jul-10 18:40 
GeneralRe: Few tips for you.. PinmvpMd. Marufuzzaman30-Jul-10 19:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150129.1 | Last Updated 2 Aug 2010
Article Copyright 2010 by rajeshitpro
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid