Closed XML To Read Excel Files In ASP.NET C#

Prerequisites

The first step is to download the Closed XML SDK from the official site.

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

dll

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

  1. Read Excel Files Using Open XML SDK In ASP.NET C#
  2. Open XML SDK To Read Workbook With Multiple Worksheets
Namespace

using ClosedXML.Excel;

Code
  1. public void StartBoqWorkBookImport()   
  2. {  
  3.     workbookProcessing("Maram_Section.xlsx");  
  4. }  
  5. private void workbookProcessing(string workbookname) {  
  6.     SqlDatabase objdb = new SqlDatabase(OSMC.constring_Property);  
  7.     string fullfilename = System.Web.HttpContext.Current.Server.MapPath(OneStopMethods_Common.boqserverfilepath + workbookname);  
  8.     XLWorkbook theWorkBook = new XLWorkbook(fullfilename);  
  9.     int worksheetcount = theWorkBook.Worksheets.Count;  
  10.     foreach(IXLWorksheet theWorkSheet in theWorkBook.Worksheets) {  
  11.         foreach(IXLRow therow in theWorkSheet.Rows()) {  
  12.             foreach(IXLCell thecell in therow.Cells()) {  
  13.                 int tenderid = 1001;  
  14.                 int Activity_Section_objseq = tenderosm.generateNextTenderObjSequenceNo(tenderid, "tender_boq_activity_section");  
  15.                 string boqactivitysectionInsquery = " insert into tender_boq_activity_section(fk_tender_id,obj_seq_no,parent_obj_seq_no,activity_section_no,workbook_name,worksheet_name,row_index,cell_reference,element_type,element_description) values(" + tenderid + "," + Activity_Section_objseq + ",' 10 ','20','" + workbookname + "','" + theWorkSheet.Name + "'," + therow.RowNumber() + ",'" + thecell.Address + "','activity','" + thecell.Value + "');";  
  16.                 objdb.ExecuteNonQuery(CommandType.Text, boqactivitysectionInsquery);  
  17.             }  
  18.         }  
  19.     }  
  20. }  
Diagrammatic Representation for Collections Hierarchy in ClosedXML to access the data:

Collections Hierarchy

When you compare it to the OpenXML, it is similar (just for your additional information).

Collections Hierarchy

Kindly refer here. Read Excel files, using OpenXML to know the basics about OpenXML.

Conclusion

I hope this will be useful for the first time users of closed XML. Kindly, let me know your feedback or thoughts.


Similar Articles