Hi, I have written a simple function (based on your sample code) to import an excel file using a Workbook and convert it to a DataSet of string columns.  This all works fairly well except that for one excel file it seems to get the result of a formula wrong! :(

The formula is: =IF(C80="","",1+A79), which is in column A and should produce an empty cell if the corresponding cell in column C is empty, otherwise it will result in a value one greater than cell A in the previous row.  The effect of this is that column A will contain an incrementing number for each row (1,2,3,etc.) as long as there is a corresponding value in column C.

The problem I have is that this column should stop at row 80 (where the first empty value is for column C) for the sheet (and does in Excel), but when I iterate the cells for row 80 in the Workbook it gives me a value for cell A, even though cell C on the same row has a value of null!  This problem continues all the way up to row 142.

I have attched the problem excel file, and the function I use to load it is shown below.  Any ideas what's wrong?

Thanks.

Brian

internal static DataSet DataSetFromExcel(String filePath)
{
 // Load the supplied excel file into a data set of strings

 String fileName = "Unknown";
 try
 {
  fileName = System.IO.Path.GetFileName(filePath);
 }
 catch (System.Exception)
 {
 }

 DataSet ds = new DataSet(String.IsNullOrEmpty(fileName) ? "Unknown" : fileName);

 // read the Workbook from an .XLS file
 Workbook book = Workbook.Load(filePath);

 // update any formulas
 book.Recalculate();

 // parse each Worksheet into a DataTable
 foreach( Worksheet workSheet in book.Worksheets)
 {
  // one table per worksheet
  DataTable table = ds.Tables.Add(workSheet.Name);
  foreach (WorksheetColumn col in workSheet.Columns)
  {
   DataColumn dataCol = new DataColumn(IndexToColIdent(col.Index));
   table.Columns.Add(dataCol);
  }

  // NB: The rows and columns collections appear to be sparse, so we need
  // to make sure we fill in the missing bits!

  ArrayList cellList = new ArrayList();
  foreach( WorksheetRow row in workSheet.Rows )
  {
   Int32 rowIdx = row.Index;
   // fill in any missing rows
   while (table.Rows.Count < rowIdx)
   {
    table.Rows.Add(table.NewRow());
   }

   foreach (WorksheetCell cell in row.Cells)
   {
    Int32 colIdx = cell.ColumnIndex;
    // fill in any missing cols
    while (cellList.Count < colIdx)
    {
     cellList.Add(null);
    }

    cellList.Add(cell.Value != null ? cell.Value.ToString() : null);
   }

   table.Rows.Add(cellList.ToArray());
   cellList.Clear();
  }
 }

 return ds;
}