Introduction

In this article, I am going to show you how to read the entire Excel workbook with multiple worksheets and display the data into the Label.

Prerequisites

Kindly find the basic installation information of OpenXML in the link, given below:

Snagit of how the output will look:

Excel workbook name is Official_Details.xlsx

Excel Workbook nam

C# code

Click button event:

  1. protected void insertBoqElements_Click(object sender, EventArgs e) {
  2. try {
  3. OpenXml();
  4. } catch (Exception Ex) {
  5. lbldisplayerrors.Text = Ex.Message;
  6. }
  7. }
  8. public void OpenXml() {
  9. try {
  10. //specify the file name where its actually exist
  11. string filepath = @ "D:\TPMS\Uploaded_Boq\Official_Details.xlsx";
  12. //open the excel using openxml sdk
  13. using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) {
  14. //create the object for workbook part
  15. WorkbookPart wbPart = doc.WorkbookPart;
  16. Sheets thesheetcollection = wbPart.Workbook.GetFirstChild < Sheets > ();
  17. //Sheet thesheet = (Sheet)thesheetcollection.FirstChild;
  18. //using for each loop to get the sheet from the sheetcollection
  19. foreach(Sheet thesheet in thesheetcollection) {
  20. lbldisplayerrors.Text += "Excel Sheet Name : " + thesheet.Name + "</br> ";
  21. lbldisplayerrors.Text += "----------------------------------------------- " + "</br> ";
  22. //statement to get the worksheet object by using the sheet id
  23. Worksheet theWorksheet = ((WorksheetPart) wbPart.GetPartById(thesheet.Id)).Worksheet;
  24. SheetData thesheetdata = (SheetData) theWorksheet.GetFirstChild < SheetData > ();
  25. foreach(Row thecurrentrow in thesheetdata) {
  26. foreach(Cell thecurrentcell in thecurrentrow) {
  27. //statement to take the integer value
  28. string currentcellvalue = string.Empty;
  29. if (thecurrentcell.DataType != null) {
  30. if (thecurrentcell.DataType == CellValues.SharedString) {
  31. int id;
  32. if (Int32.TryParse(thecurrentcell.InnerText, out id)) {
  33. SharedStringItem item = GetSharedStringItemById(wbPart, id);
  34. if (item.Text != null) {
  35. //code to take the string value
  36. lbldisplayerrors.Text += item.Text.Text + " ";
  37. } else if (item.InnerText != null) {
  38. currentcellvalue = item.InnerText;
  39. } else if (item.InnerXml != null) {
  40. currentcellvalue = item.InnerXml;
  41. }
  42. } //end of if (Int32.TryParse ( thecurrentcell.InnerText, out id )
  43. } //end of if(thecurrentcell.DataType == CellValues.SharedString)
  44. } // end of if(thecurrentcell.DataType != null)
  45. else {
  46. lbldisplayerrors.Text += Convert.ToInt16(thecurrentcell.InnerText) + " ";
  47. }
  48. } // end of foreach(Cell thecurrentcell in thecurrentrow)
  49. lbldisplayerrors.Text += "</br>";
  50. } //end of foreach(Row thecurrentrow in thesheetdata)
  51. lbldisplayerrors.Text += "</br>";
  52. lbldisplayerrors.Text += "</br>";
  53. } //end of foreach(Sheet thesheet in thesheetcollection)
  54. } //end of using
  55. } //end of try
  56. catch (Exception Ex) {
  57. lbldisplayerrors.Text = Ex.Message;
  58. }
  59. }
Explanation of the code

In the code, mentioned above, most of the code was explained in the code itself and it will also give you the idea of what are the collections involved to access the data from MS Excel .

Diagrammatic representation for Collections Hierarchy in openXML to access the data is given below:

Collections Hierarchy

Conclusion

Kindly find the entire source code in the attachment section. I hope this will be useful for those, who are going to work in the OpenXML for the first time. Kindly share your feedback or thoughts.