Set First row as Header

Jun 9, 2009 at 7:12 AM

With the new release of ExcelDataReader, how can i set the excel first row as datatable column names?

PS: Excel column names and format is not consistent. With older release while calling data excel reader i can specify that.

Thanks in advance.

Gnan

Jun 15, 2009 at 11:47 PM

I'm having a similar issue.  Even when I attempt to include the ColumnNames into the DataSet manually, I get empty strings from the first rows.

Stream fs = new FileStream(Path, FileMode.Open,FileAccess.Read, FileShare.ReadWrite);           
IExcelDataReader rd = Excel.Factory.CreateReader(fs, ExcelFileType.Binary);

foreach (DataTable table in rd.AsDataSet(true).Tables)
{
   foreach (DataColumn dc in Table.Columns)
   {
      string colName = Table.Rows[0][dc.Ordinal].ToString().Trim();
      if (string.IsNullOrEmpty(colName) && !Table.Columns.Contains(colName)) { Table.Columns[dc.Ordinal].ColumnName = colName; }
   }
}
           
fs.Close();

The only change I had to make from the old version was rd.WorksheetData.Tables to rd.AsDataSet(true) .

Also, the formula tryConvertOADateTime(_oValue, cell.XFormat) throws and exception when _oValue is null.

 

Jul 9, 2009 at 9:40 AM
Edited Jul 9, 2009 at 9:42 AM
               DataSet result= rd.AsDataSet()
            foreach (DataTable table in result.Tables)
                    {
                        if (table.Rows.Count == 0) continue;
                        foreach (DataColumn dc in table.Columns)
                        {
                            string colName = table.Rows[0][dc.Ordinal].ToString().Trim();
                            if (!string.IsNullOrEmpty(colName) && !table.Columns.Contains(colName))
                            {
                                table.Columns[dc.Ordinal].ColumnName = colName;
                            }
                        }
                        table.Rows[0].Delete();
                    }
                    result.AcceptChanges();
Jul 9, 2009 at 12:41 PM

hoangchau,

It appears that I was missing the call to the AcceptChanges method. I also appreciate the other suggested changes.

Thanks,
mindcore