다음을 통해 공유

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

  • 아티클

Introduction

  In the Below simple 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:

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/read-excel-files-using-open-xml-sdk-in-asp-net-c-sharp/Images/image001.png

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/read-excel-files-using-open-xml-sdk-in-asp-net-c-sharp/Images/image002.jpg

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

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/read-excel-files-using-open-xml-sdk-in-asp-net-c-sharp/Images/3.jpg

Resolution for above problem is,
**
http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/read-excel-files-using-open-xml-sdk-in-asp-net-c-sharp/Images/image004.jpg
**
Add the above dll also to the project.

Namespace

  1. using DocumentFormat.OpenXml.Packaging;  
  2. using DocumentFormat.OpenXml.Spreadsheet;  

C# Code

  1. protected void insertBoqElements_Click(object sender, EventArgs e)  
  2. {  
  3.     try  
  4.     {  
  5.         //specify the file name where its actually exist   
  6.         string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";  
  7.   
  8.         //open the excel using openxml sdk  
  9.         using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))  
  10.         {  
  11.   
  12.             //create the object for workbook part  
  13.             WorkbookPart wbPart = doc.WorkbookPart;  
  14.   
  15.             //statement to get the count of the worksheet  
  16.             int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();  
  17.   
  18.             //statement to get the sheet object  
  19.             Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);  
  20.   
  21.             //statement to get the worksheet object by using the sheet id  
  22.             Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;  
  23.   
  24.             //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata  
  25.             int wkschildno = 4;  
  26.   
  27.   
  28.             //statement to get the sheetdata which contains the rows and cell in table  
  29.             SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);  
  30.   
  31.   
  32.             //getting the row as per the specified index of getitem method  
  33.             Row currentrow = (Row) Rows.ChildElements.GetItem(1);  
  34.   
  35.             //getting the cell as per the specified index of getitem method  
  36.             Cell currentcell = (Cell) currentrow.ChildElements.GetItem(1);  
  37.   
  38.             //statement to take the integer value  
  39.             string currentcellvalue = currentcell.InnerText;  
  40.   
  41.         }  
  42.     } catch (Exception Ex)  
  43.     {  
  44.   
  45.         lbldisplayerrors.Text = Ex.Message;  
  46.     }  
  47.   
  48. }  

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

  1. protected void insertBoqElements_Click(object sender, EventArgs e)   
  2. {  
  3.     try   
  4.     {  
  5.         //specify the file name where its actually exist   
  6.         string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";  
  7.   
  8.         //open the excel using openxml sdk  
  9.         using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))  
  10.         {  
  11.   
  12.             //create the object for workbook part  
  13.             WorkbookPart wbPart = doc.WorkbookPart;  
  14.   
  15.             //statement to get the count of the worksheet  
  16.             int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();  
  17.   
  18.             //statement to get the sheet object  
  19.             Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);  
  20.   
  21.             //statement to get the worksheet object by using the sheet id  
  22.             Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;  
  23.   
  24.             //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata  
  25.             int wkschildno = 4;  
  26.   
  27.   
  28.             //statement to get the sheetdata which contains the rows and cell in table  
  29.             SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);  
  30.   
  31.   
  32.             //getting the row as per the specified index of getitem method  
  33.             Row currentrow = (Row) Rows.ChildElements.GetItem(0);  
  34.   
  35.             //getting the cell as per the specified index of getitem method  
  36.             Cell currentcell = (Cell) currentrow.ChildElements.GetItem(0);  
  37.   
  38.             string currentcellvalue = string.Empty;  
  39.   
  40.   
  41.             if (currentcell.DataType != null)  
  42.             {  
  43.                 if (currentcell.DataType == CellValues.SharedString)  
  44.                 {  
  45.                     int id = -1;  
  46.   
  47.                     if (Int32.TryParse(currentcell.InnerText, out id))  
  48.                     {  
  49.                         SharedStringItem item = GetSharedStringItemById(wbPart, id);  
  50.   
  51.                         if (item.Text != null)  
  52.                         {  
  53.                             //code to take the string value  
  54.                             currentcellvalue = item.Text.Text;  
  55.                         } else if (item.InnerText != null)  
  56.                         {  
  57.                             currentcellvalue = item.InnerText;  
  58.                         } else if (item.InnerXml != null)  
  59.                         {  
  60.                             currentcellvalue = item.InnerXml;  
  61.                         }  
  62.                     }  
  63.                 }  
  64.             }  
  65.   
  66.         }  
  67.     } catch (Exception Ex)  
  68.     {  
  69.   
  70.         lbldisplayerrors.Text = Ex.Message;  
  71.     }  
  72.   
  73. }  
  74.   
  75. public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)  
  76. {  
  77.     return workbookPart.SharedStringTablePart.SharedStringTable.Elements < SharedStringItem > ().ElementAt(id);  
  78. }  

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

Downloads:

you can download the executable in from here : https://gallery.technet.microsoft.com/OpenXMLSimpleExample-a757f10c

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