Get column header list from Excel using Open XML SDK 2.5

There are many applications which are using to insert, update and delete data in database using Excel file. In this type of requirement, we also seen requirement where we need to get column header list from Excel.

To do that, I have used Open XML SDK 2.5. You can download it from below link.
http://www.microsoft.com/en-in/download/details.aspx?id=30425

Once you download and install Open XML SDK 2.5, you can follow below steps in Visual Studio web application to add DLLs.

1) Right click on References -> Add Reference

2) Go to Assemblies -> Framework -> Check "WindowBase" -> Click Ok

3) Go to Assemblies -> Extensions -> Check "DocumentFormat.OpenXml" -> Click OK.

Now, you can use below code to read column headers from excel Product.xlsx which is shown below and convert it to XML format.

C#.NET Code
try{
string path = Server.MapPath("Product.xlsx");

System.IO.Packaging.Package package = System.IO.Packaging.Package.Open(path, System.IO.FileMode.Open, System.IO.FileAccess.Read);

var document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(package);
var workbookPart = document.WorkbookPart;
var workbook = workbookPart.Workbook;
var sheet = workbookPart.Workbook.DescendantsSheet>().FirstOrDefault();
DocumentFormat.OpenXml.Spreadsheet.Worksheet ws = ((DocumentFormat.OpenXml.Packaging.WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = ws.GetFirstChildSheetData>();

var worksheetPart = (DocumentFormat.OpenXml.Packaging.WorksheetPart)workbookPart.GetPartById(sheet.Id);
var sharedStringPart = workbookPart.SharedStringTablePart;
var values = sharedStringPart.SharedStringTable.ElementsSharedStringItem>().ToArray();
var rows = worksheetPart.Worksheet.DescendantsRow>();

if (rows.Count() > 0)
{
XElement xeColumnList = new XElement("ColumnList");
var cells = rows.FirstOrDefault().ElementsCell>();
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in cells)
{
XElement xeColumn = new XElement("Column");
if (cell.DataType != null && cell.DataType.Value == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
xeColumn.Add(new XElement("ColumnName", values[int.Parse(cell.CellValue.Text)].InnerText));
}
else
{
xeColumn.Add(new XElement("ColumnName", cell.CellValue.Text));
}
xeColumnList.Add(xeColumn);
}
package.Close();

Response.Write(xeColumnList.ToString());
}
else
{
package.Close();
}
}
catch (Exception ex)
{
throw ex;
}


Output XML
<ColumnList>
<Column>
<ColumnName>ID</ColumnName>
</Column>
<Column>
<ColumnName>Name</ColumnName>
</Column>
<Column>
<ColumnName>Department</ColumnName>
</Column>
<Column>
<ColumnName>Salary</ColumnName>
</Column>
</ColumnList>

This will be helpful when you want to update selected columns in Database from Excel. In this case, you can generate XML for column list and pass through procedure and update only selected columns to the database.

You can also download sample excel from below location.
1. Product.xlsx