.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Post New Resource Bookmark and Share   

Uploading from Excel using OpenOffice XML into Asp .net Application

Posted By:Raga       Posted Date: November 12, 2010    Points: 50    Category:    URL: http://www.dotnetspark.com  

This article has the code for using open office XML. This is supported by all servers.

Office Open XML (also informally known as OOXML or OpenXML) is a ZIP-based file format originally developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents.

To access Office documents using Open office XML, you need to install the sdk.

Click here to Download Open XML Format SDK 1.0

Follow the below steps:

1. Open your Application and Add Reference to the dll (Document.OpenXml.dll)
2. Add WindowsBase.dll as refernce to the bin folder.
3. Add the below name spaces in your Code behind.

  • using DocumentFormat.OpenXml.Packaging;
  • using DocumentFormat.OpenXml.Spreadsheet;
  • using DocumentFormat.OpenXml;

4. public static DataTable ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName)
DataTable dt = new DataTable();
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart worksheetPart = null;
if (!string.IsNullOrEmpty(sheetName))
Sheet ss = workbookPart.Workbook.Descendants().Where(s => s.Name == sheetName).SingleOrDefault();
worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
if (worksheetPart != null)
Row lastRow = worksheetPart.Worksheet.Descendants().LastOrDefault();
Row firstRow = worksheetPart.Worksheet.Descendants().FirstOrDefault();
if (firstRow != null)
foreach (Cell c in firstRow.ChildElements)
string value = ExtractValue(c, stringTablePart);
if (lastRow != null)
for (int i = 2; i <= lastRow.RowIndex; i++)
DataRow dr = dt.NewRow();
bool empty = true;
Row row = worksheetPart.Worksheet.Descendants().Where(r => i == r.RowIndex).FirstOrDefault();
int j = 0;
if (row != null)
foreach (Cell c in row.ChildElements)
//Get cell value
string value = GetValue(c, stringTablePart);
if (!string.IsNullOrEmpty(value) && value != "")
empty = false;
dr[j] = value;
if (j == dt.Columns.Count)
if (empty)
return dt;

public static string ExtractValue(Cell cell, SharedStringTablePart stringTablePart)
if (cell.ChildElements.Count == 0)
return null;
string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
return value;

5. use both the above functions to get Datatable in return.

6. ds.Tables.Add(ExtractExcelSheetValuesToDataTable(filePath, "Sheet1?)); => This will load all datatable values into a dataset and you can query the dataset for further operations.


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend