was experimenting with reading from an excel workbook and noticed it takes a long time to read a sheet with 3560 rows and 7 columns, about 1 minute and 17 seconds. All I did was loop through the whole sheet and store the values in a list.
Is this normal, or am I doing something wrong ?
static void Main(string[] args)
{
List<string> testList = new List<string>();
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(@"C:\Users\rnewell\Desktop\FxData.xlsx");
Excel.Worksheet worksheet = workbook.Sheets[1];
Excel.Range range = worksheet.UsedRange;
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
int rowCounter = 1;
int colCounter = 1;
while (rowCounter < rowCount)
{
colCounter = 1;
while (colCounter <= colCount)
{
//Console.Write(range.Cells[rowCounter, colCounter].Value2.ToString() + " ");
testList.Add(range.Cells[rowCounter, colCounter].Value2.ToString());
colCounter++;
}
Console.WriteLine();
rowCounter++;
}
Console.ReadKey();
excelApp.Workbooks.Close();
}
. NET 4+ allows C# to read and manipulate Microsoft Excel files, for computers that have Excel installed (if you do not have Excel installed, see NPOI).
Step 1 - Click "Save" icon in Excel Quick Access Toolbar (or press "Ctrl" + "S" keys together), as shown in below image. Step 2 - Excel Backstage View is opened and "Save As" menu is displayed as shown in below image. Click "Browse" button to open "Save As" Dialog Box.
@TimWilliams' comment is the correct answer. Reading a single cell takes as long as reading a range of any size. This is the overhead of talking to the COM layer, and you are incurring it thousands of times. You should write the range to an object[,]
, and then access that array cell by cell.
int rowCount = range.Rows.Count;
int colCount = range.Columns.Count;
object[,] values= range.Value2;
int rowCounter = 1;
int colCounter = 1;
while (rowCounter < rowCount)
{
colCounter = 1;
while (colCounter <= colCount)
{
// check for null?
testList.Add(values[rowCounter, colCounter].ToString());
}
}
Note that the array will be one-based instead of zero-based like normal C# arrays. The data will go from 1 to rowCount and from 1 to colCount, but Rows and Columns properties will return rowCount and colCount, not 1 + rowCount and 1 + colCount. If you want to write data back, you can use a zero-based array of the right size (in fact you have to AFAIK since you can't create a one-based array) and it will work fine.
Since you are loading data from the Open XML (*.xlsx) file format, I would suggest you use Open XML SDK. It doesn't start Excel in the background which is always a good thing, in particular if you need to run your code non-interactively.
I've also written a blog post on different methods of accessing data in Excel which you might find useful.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With