Sandip Darade

Sandip Darade

  • NA
  • 30
  • 2.7k

Reading sheet headers rom an excel file

Feb 11 2015 4:18 AM
I've to read only headers (not data)of the first sheet  form an excel document with multiple sheets . I don't want to load the complete document in the memory hence no DOM approach. The code  I'm using reads the second sheet of the file and not the first ?
internal DataTable GetSchema(string fileName)
{
int i = 0;
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; 
foreach (WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);// (
while (reader.Read())
{
if (reader.ElementType == typeof(Row))///loops through row
{
reader.ReadFirstChild();
if (i > 1)
{
return dataTable;
// we have recieved all column names till here
}
i++; //increments for the first time which means we are reading columns
do
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue = string.Empty;
if (c.CellValue != null)
{
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText)); 
cellValue = Convert.ToString(ssi.InnerText);
}
else
{
cellValue = Convert.ToString(c.CellValue.InnerText);
}
}
else
{
 
cellValue = c.InnerText;
}
 
if (i <= 1)
{
dataTable.Columns.Add(cellValue);
}
else
{
break;
}
}
} while (reader.ReadNextSibling());
}
}
}
return dataTable;
}
}
the excel file has three sheets :
1. records 
2.sa
3.da
and my code reads headers for the 2ne sheet (sa).
Can some one please  help me to read the header from 1 (records) sheet ?
 

 

Answers (2)