Reading data from an Excel spreadsheet

Sample 2 shows how to read data from an existing Excel spreadsheet. We will use the spreadsheet generated by Sample 1 as the source document. To output the contents of column 2 to the console, this is all we need:

using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
  // get the first worksheet in the workbook
  ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
  int iCol = 2;  // the column to read

  // output the data in column 2
  for (int iRow = 1; iRow < 6; iRow++)
    Console.WriteLine("Cell({0},{1}).Value={2}", iRow, iCol, 
      worksheet.Cell(iRow, iCol).Value);

  // output the formula in row 6
  Console.WriteLine("Cell({0},{1}).Formula={2}", 6, iCol, 
    worksheet.Cell(6, iCol).Formula);
			
} // the using statement calls Dispose() which closes the package.


So here is the output of this sample.

Output of Sample 2

Last edited Jan 4, 2007 at 7:53 PM by JohnTunnicliffe, version 4

Comments

zins Tue at 2:28 PM 
For the existingFile parameter, you can pass a FileInfo object containing the path to your excel file or a stream containing the excel data. There are quite a many overloads of the constructor. I usually use these two.

zins Tue at 2:26 PM 
The number of rows can be found by saying 'worksheet.Dimension.End.Row'. The number of columns can found in a similar way.

akansh Jan 30 at 10:34 AM 
Hi,
I want to know that what should be passed in existingFile parameter.

Thanks,
Akansh

ittechsathish Oct 19, 2012 at 1:37 PM 
I have logo in my Excel sheet, How can i read that image.

antiRev Mar 28, 2012 at 12:45 PM 
Because the underlying XmlDocument is visible the following will work:

private String getMaximumRowNumber(ExcelWorksheet worksheet)
{
XPathNavigator nav = worksheet.WorksheetXml.CreateNavigator();
XPathExpression exp = nav.Compile("//*[name()='row']/@r");
exp.AddSort("../@r", XmlSortOrder.Descending, XmlCaseOrder.None, "", XmlDataType.Number);
XmlNode node = nav.SelectSingleNode(exp).UnderlyingObject as XmlNode;
return node.InnerText;
}

private String getMaximumCellColumn(ExcelWorksheet worksheet)
{
XPathNavigator nav = worksheet.WorksheetXml.CreateNavigator();
XPathExpression exp = nav.Compile("//*[name()='c']/@colNumber");
exp.AddSort("../@colNumber", XmlSortOrder.Descending, XmlCaseOrder.None, "", XmlDataType.Number);
XmlNode node = nav.SelectSingleNode(exp).UnderlyingObject as XmlNode;
return node.InnerText;
}

petka82 Sep 7, 2011 at 3:58 PM 
Why no one answered this question ? I feel disappointed now when I want to iterate through rows... that function is so obvious and everyone use it I guess. Well time to look for something else.

algocode Sep 5, 2011 at 8:37 PM 
I regret wasting my time with this package because this is how I feel after realizing that there is no method for getting the number of rows or the used range. In order to parse data, you have to know in advance the number of rows and columns apparently.

podjunk Jun 1, 2011 at 6:40 PM 
Hello... no one has answered these questions yet?

CtrlAltDel May 9, 2011 at 6:49 AM 
Bump! How can we iterate through a worksheet if we don't know the number of rows/columns?

lanting Aug 6, 2010 at 2:49 AM 
I also want to know how to retrieve the count of Rows and Columns?

billyjacobs Oct 1, 2009 at 3:35 AM 
How do you get the number of rows and columns?