faster excel sheet -> string[,]

csetzkorn
csetzkorn used Ask the Experts™
on
Hi,

I have attached some code to read an excel work book's sheet and transform it into a string array. This works fine but is quite slow. Is there a way to speed this up? Thanks.

Christian
public static string[,] getDataAsStringArray(string path, string file_name, int sheet_index) 
        {
            Sheets sheets = ExcelHelper.getWorkSheets(path + file_name);

            Worksheet worksheet = (Worksheet) sheets.get_Item(sheet_index);
            Range range = worksheet.UsedRange;
            string[,] strings = new string[range.Columns.Count,range.Rows.Count];
            
            for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    if( (range.Cells[rCnt, cCnt] as Range).Value2 == null )
                    {
                        strings[cCnt - 1, rCnt - 1] = "";
                    }
                    else
                    {
                        strings[cCnt - 1, rCnt - 1] = (range.Cells[rCnt, cCnt] as Range).Value2.ToString();
                    }
                }
            }

            return strings;
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I have something like this in the past. Is this the recommended/fastest way to access the data?
If your Excel sheet is has proper table, Field name  and column values, this is the best way I believe.

One of the comment in that link it says it is much much faster. Quoting it here
"
-- The COM API, where you access Excel's objects directly and manipulate them through methods and properties
-- The ODBC driver that allows to use Excel like a database.
The latter approach was much faster: reading a big table with 20 columns and 200 lines would take 30 seconds via COM, and half a second via ODBC. So I would recommend the database approach if all you need is the data."

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial