Click here to Skip to main content
15,880,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Friends...

I m involved in project in which i need to code for reading excel file and save the data in another excel file. But Source excel file can contain any number of columns i.e Somtetimes it may contain 3 columns in file or sometimes it may contain 8 columns in file etc such as follows.

Col-A       Col-B      Col-C
1           3          5
2           4          6

The columns of Source Excel file are dynamic. But I know their datatypes. All are string. And these columns can contain any number of values. All columns have a fixed number of rows to each other. Suppose All columns may have a 2000 rows. I need to save columns in destination file as one below other with values such as follows.

Columns       Values
Col-A         1
Col-A         2
Col-B         3
Col-B         4
Col-C         5
Col-C         6

For that I need to count number of columns in Source File and i successed in counting the number of columns in source file. But I failed to create dynamic List Objects for them at runtime to store their values in respective List Objects. Please help m. I want C# code for it.
Updated 24-Apr-15 8:20am
Maciej Los 24-Apr-15 14:23pm    
What you mean by saying "List of Objects"? What kind of Object?
The answer is quite obvious: you need to loop through the collection of columns and rows.

First collect a List<string> of the column headings.
Then create an array or list of List<string> with an entry that corresponds to each column.
As you go through the file, as you get each value from a row, add it to the List<string> for the corresponding column.
When you're done reading, you can output each of the column's values as lines with the column name and a value for each value.
Share this answer
I would store the values in a Dictionary<string, List<string>>, and I would store only the Cell data for the number of row values per column that were actually used.

You could use the 'UsedRange function in Excel to determine (for the entire worksheet) what the last row used was. Note that 'UsedRange will return a Range that includes every Cell that at any point had a value, independent of whether that Cell has a value now.

And, with some programming you could (in theory ... I have not done this myself), calculate for each column what the first, and last, rows were in that column that had "real" data; see: [^].

However, that approach would probably mean you'd want to write something like this:
public Dictionary<string, List<dctColNameToColData>>; 

public class XLColumnData
    public string Name { set; get; }
    public int StartRow { set; get; }

    public int EndRow { set; get; }

    public List<string> ColumnData { set; get; }

    public XLColumnData(string name, int srow, int erow, List<string> cdata)
       // initialize here ...
I don't have Excel on this machine, so can't be more specific than this.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900