Click here to Skip to main content
11,494,407 members (68,084 online)
Click here to Skip to main content

Excel XML Writer /Reader

, 2 Aug 2010 CPOL 40.9K 3.3K 52
Rate this:
Please Sign up or sign in to vote.
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

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

License

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

Share

About the Author

rajeshitpro
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

 
Question"if you provide 5 then 5,6,7., will be hidden." What? Why? Pin
tmwmott24-Apr-15 8:13
membertmwmott24-Apr-15 8:13 
GeneralRe: "if you provide 5 then 5,6,7., will be hidden." What? Why? Pin
PIEBALDconsult24-Apr-15 8:50
protectorPIEBALDconsult24-Apr-15 8:50 
QuestionSee also this: Pin
Dietmar Schoder29-Jul-14 6:25
professionalDietmar Schoder29-Jul-14 6:25 
GeneralMy vote of 5 Pin
Member 917615427-Jun-12 20:51
memberMember 917615427-Jun-12 20:51 
Questionwhat about Excel format? Pin
batsword13-Sep-11 16:47
memberbatsword13-Sep-11 16:47 
AnswerRe: what about Excel format? Pin
FilipKrnjic24-Oct-11 23:10
memberFilipKrnjic24-Oct-11 23:10 
GeneralMy vote of 5 Pin
seenit6-Sep-11 19:39
memberseenit6-Sep-11 19:39 
GeneralMy vote of 5 Pin
Shubha_India7-Apr-11 9:04
memberShubha_India7-Apr-11 9:04 
GeneralPlease correct link to code Pin
Bo Vistisen1-Aug-10 22:46
memberBo Vistisen1-Aug-10 22:46 
GeneralRe: Please correct link to code Pin
rajeshitpro2-Aug-10 8:44
memberrajeshitpro2-Aug-10 8:44 
GeneralSource code link is broken Pin
Tony Bermudez30-Jul-10 19:05
memberTony Bermudez30-Jul-10 19:05 
GeneralRe: Source code link is broken Pin
rajeshitpro2-Aug-10 8:44
memberrajeshitpro2-Aug-10 8:44 
GeneralRe: Source code link is broken Pin
Gabriel X27-Jun-12 20:56
memberGabriel X27-Jun-12 20:56 
GeneralMore Details are Needed Pin
Gil Fink25-Jul-10 9:25
memberGil Fink25-Jul-10 9:25 
GeneralRe: More Details are Needed Pin
rajeshitpro30-Jul-10 18:39
memberrajeshitpro30-Jul-10 18:39 
GeneralAdd more details... Pin
Sandeep Mewara25-Jul-10 8:31
mentorSandeep Mewara25-Jul-10 8:31 
GeneralFew tips for you.. Pin
Md. Marufuzzaman25-Jul-10 6:30
mvpMd. Marufuzzaman25-Jul-10 6:30 
GeneralRe: Few tips for you.. Pin
rajeshitpro30-Jul-10 18:40
memberrajeshitpro30-Jul-10 18:40 
GeneralRe: Few tips for you.. Pin
Md. Marufuzzaman30-Jul-10 19:45
mvpMd. 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.150520.1 | Last Updated 2 Aug 2010
Article Copyright 2010 by rajeshitpro
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid