csetzkorn
asked on
faster excel sheet -> string[,]
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
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;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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."
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."
ASKER