Read Excel Files Using Open XML SDK In ASP.NET C#

Karthik Elumalai

C#ASP.NETRead Excel FilesOpen XML SDKExcel Files

25 Comments

Join the conversation! Your thoughts help the community grow.

Varadharajan Duraisamy8y ago
Hi karthick, Everything works fine but i could not read custom date from xlsx file. it is showing datatype as null and returning cellvalue/innertext as 43342. How could we determine this cell value is datetype and how to convert this to datetime.
Anjali Bhatt8y ago
Hey.. Is this possible to write data on xlsb format, instead of xlsx using openxml.
nagatejaswi bhupathi8y ago
Hi Karthik, I want to import Excel(which have data with 10 columns) using openxml and display the data in respective user controls ( C#). Thanks in advance
SHENYAUNG WANG8y ago
Hi Karthink,I want to read excel file in datatable ,and then add some data in it. export this datatable into xlsx,but i have a problem which i read is numeric and output is string like:123 turn into "123" in excel. That will cause the formula cant be use.
vijay satuluri8y ago
Hi, Do i need Microsoft office to be installed in server, if i use Open XML SDK.
- Karthik Elumalai8y ago
  Simple answer is "No", you dont require office install at server. Here is the reference FYI:https://stackoverflow.com/questions/3015272/do-we-need-office-installation-to-work-with-openxml
Ryma Khan9y ago
Hi, How can we notify c # program that more rows are added in excel sheet(open xml excel sdk)?
ringgo dejesus9y ago
Hi Karthink, I also have this task wherein the data will be exported to a template .xlsm file. It successfully exported the data in the spreadsheet but unfortunately every time I open the file I keep getting "content error" after the download is completed. I already tried all possible solution to my problem on internet but still have the same error. can you please help me as I'm still doing this for 2 weeks. :(
- Karthik Elumalai9y ago
  Yeah sure, its seems simple . Even i didnt face your scenario , but when i look at google , Hope this solution , https://stackoverflow.com/a/8718520/3397630 will surely lead you to the correct direction. Happy to help and sharing thanks
manisha yadav9y ago
Hi Karthik , I want to read excel file using open xml sdk and print values on console. Your code is working fine when all the rows nd columns has numeric value but when it is a string it just gives the index. Can u please help me out with this.
- Karthik Elumalai9y ago
  Hi manisha , no problem, its really simple. In the above article itself kindly look at the title "For taking the string Value from Cell" , its really will help you for what you want. In simple we have take it string from sharedstringtable, the code was given above,surely will guide you. if you still need any help,please let me know . Happy sharing and thanks
Simran Chauhan9y ago
Can you please tell me how to extract particular column data from various tabs in single excel sheet using openxml
- Karthik Elumalai9y ago
  Simran Chauhan I hope this article may help you for that , http://www.c-sharpcorner.com/article/open-xml-sdk-to-read-workbook-with-multiple-worksheets/
Karthik Elumalai9y ago
@Simran Chauhan I hope ,In the above article, I have mentioned starting from how to add the reference to the required dll and how to open and read content of excel with steps and screenshots. So can you please let me know for which step you want assistance? That will help me to provide details further. Happy sharing
- Simran Chauhan9y ago
  Can you please tell me how to extract particular column data from various tabs in single excel sheet using openxml
- Simran Chauhan9y ago
  And from this" how to open and read content of excel " where is the view part?
Simran Chauhan9y ago
Can you please provide me the steps of this how i write a code.
Karthik Elumalai10y ago
My pleasure ,and hearful thanks for valuable feedback and time sir.
Santhakumar Munuswamy10y ago
Thank you for nice article
Karthik Elumalai10y ago
thanks for your good feedback...:) Ekrem Tapan
Ekrem Tapan10y ago
Nice Article
Karthik Elumalai10y ago
Thanks you very much for your time and feedback Vignesh Mani
Karthik Elumalai10y ago
Thanks a lot for your time and feedbackHumayun Kabir Mamun
Vignesh Mani10y ago
Nice
Humayun Kabir Mamun10y ago
Nice...

Background

A few days back, I got a requirement to read MS Excel files and store those values in the SQL Server database. Hence, in this example, I am going to show how to get the basic four types of important data such as Excel Work Book Name, Worksheet Count in that Workbook, Name of the Worksheets, and finally the Value of the First Cell in that Worksheet.

Prerequisites

First step is to download the Openxml dll from the official site.

Second step is to kindly ensure to add the above dll, as shown in the screenshot, given below:

add

add

Important note

Once you load this dll and write the code, you will get the following error:

error

Resolution for above problem is,

Add the above dll also to the project.

Namespace

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

C# Code

protected void insertBoqElements_Click(object sender, EventArgs e)
{
try
{
//specify the file name where its actually exist
string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";
//open the excel using openxml sdk
using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
{
//create the object for workbook part
WorkbookPart wbPart = doc.WorkbookPart;
//statement to get the count of the worksheet
int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
//statement to get the sheet object
Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);
//statement to get the worksheet object by using the sheet id
Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;
//Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata
int wkschildno = 4;
//statement to get the sheetdata which contains the rows and cell in table
SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);
//getting the row as per the specified index of getitem method
Row currentrow = (Row) Rows.ChildElements.GetItem(1);
//getting the cell as per the specified index of getitem method
Cell currentcell = (Cell) currentrow.ChildElements.GetItem(1);
//statement to take the integer value
string currentcellvalue = currentcell.InnerText;
}
} catch (Exception Ex)
{
lbldisplayerrors.Text = Ex.Message;
}
}

Note

If the cell contains a string, then this value is an index into the shared string table, pointing to the actual string value. Otherwise, the value of the cell is expressed directly in this element

Source: CellValue Class

For taking the string Value from Cell

protected void insertBoqElements_Click(object sender, EventArgs e)
{
try
{
//specify the file name where its actually exist
string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";
//open the excel using openxml sdk
using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
{
//create the object for workbook part
WorkbookPart wbPart = doc.WorkbookPart;
//statement to get the count of the worksheet
int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
//statement to get the sheet object
Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);
//statement to get the worksheet object by using the sheet id
Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;
//Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata
int wkschildno = 4;
//statement to get the sheetdata which contains the rows and cell in table
SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);
//getting the row as per the specified index of getitem method
Row currentrow = (Row) Rows.ChildElements.GetItem(0);
//getting the cell as per the specified index of getitem method
Cell currentcell = (Cell) currentrow.ChildElements.GetItem(0);
string currentcellvalue = string.Empty;
if (currentcell.DataType != null)
{
if (currentcell.DataType == CellValues.SharedString)
{
int id = -1;
if (Int32.TryParse(currentcell.InnerText, out id))
{
SharedStringItem item = GetSharedStringItemById(wbPart, id);
if (item.Text != null)
{
//code to take the string value
currentcellvalue = item.Text.Text;
} else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
} else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
}
} catch (Exception Ex)
{
lbldisplayerrors.Text = Ex.Message;
}
}
public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
{
return workbookPart.SharedStringTablePart.SharedStringTable.Elements < SharedStringItem > ().ElementAt(id);
}

Other References

Link to: Read excel files using Microsoft Office Interop Assemblies in asp.net

I hope the above information was useful. Kindly let me know your thoughts.