Click here to Skip to main content
15,868,164 members
Articles / Web Development / ASP.NET

Excel XML Writer /Reader

Rate me:
Please Sign up or sign in to vote.
4.67/5 (5 votes)
2 Aug 2010CPOL2 min read 76.7K   3.9K   52   20
Excel XML Writer /Reader - Enables to generate Excel XML with hidden columns and lookups

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.

Exceler.png

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

C#
public static DataTable ReadExcelXML(string ExcelXmlFile)

Client Call to Excel XML Writer

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

Client Call to Excel XML Reader

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

Explanation on Component Call

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

License

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


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

 
QuestionExcel Reader and Writer Pin
Member 1217525011-Oct-17 15:53
Member 1217525011-Oct-17 15:53 
Question"if you provide 5 then 5,6,7., will be hidden." What? Why? Pin
tmwmott24-Apr-15 7:13
tmwmott24-Apr-15 7:13 
GeneralRe: "if you provide 5 then 5,6,7., will be hidden." What? Why? Pin
PIEBALDconsult24-Apr-15 7:50
mvePIEBALDconsult24-Apr-15 7:50 
QuestionSee also this: Pin
dietmar paul schoder29-Jul-14 5:25
professionaldietmar paul schoder29-Jul-14 5:25 
GeneralMy vote of 5 Pin
Member 917615427-Jun-12 19:51
Member 917615427-Jun-12 19:51 
Questionwhat about Excel format? Pin
batsword13-Sep-11 15:47
batsword13-Sep-11 15:47 
AnswerRe: what about Excel format? Pin
FilipKrnjic24-Oct-11 22:10
FilipKrnjic24-Oct-11 22:10 
GeneralMy vote of 5 Pin
seenit6-Sep-11 18:39
seenit6-Sep-11 18:39 
GeneralMy vote of 5 Pin
Shubha_India7-Apr-11 8:04
Shubha_India7-Apr-11 8:04 
GeneralPlease correct link to code Pin
Bo Vistisen1-Aug-10 21:46
Bo Vistisen1-Aug-10 21:46 
GeneralRe: Please correct link to code Pin
rajeshitpro2-Aug-10 7:44
rajeshitpro2-Aug-10 7:44 
GeneralSource code link is broken Pin
Tony Bermudez30-Jul-10 18:05
Tony Bermudez30-Jul-10 18:05 
The source code link is broken.
GeneralRe: Source code link is broken Pin
rajeshitpro2-Aug-10 7:44
rajeshitpro2-Aug-10 7:44 
GeneralRe: Source code link is broken Pin
Gabriel X27-Jun-12 19:56
Gabriel X27-Jun-12 19:56 
GeneralMore Details are Needed Pin
Gil Fink25-Jul-10 8:25
Gil Fink25-Jul-10 8:25 
GeneralRe: More Details are Needed Pin
rajeshitpro30-Jul-10 17:39
rajeshitpro30-Jul-10 17:39 
GeneralAdd more details... Pin
Sandeep Mewara25-Jul-10 7:31
mveSandeep Mewara25-Jul-10 7:31 
GeneralFew tips for you.. Pin
Md. Marufuzzaman25-Jul-10 5:30
professionalMd. Marufuzzaman25-Jul-10 5:30 
GeneralRe: Few tips for you.. Pin
rajeshitpro30-Jul-10 17:40
rajeshitpro30-Jul-10 17:40 
GeneralRe: Few tips for you.. Pin
Md. Marufuzzaman30-Jul-10 18:45
professionalMd. Marufuzzaman30-Jul-10 18:45 

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

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