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:
Important note
Once you load this dll and write the code, you will get the following 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
- {
-
- string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";
-
-
- using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
- {
-
-
- WorkbookPart wbPart = doc.WorkbookPart;
-
-
- int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
-
-
- Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);
-
-
- Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;
-
-
- int wkschildno = 4;
-
-
-
- SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);
-
-
-
- Row currentrow = (Row) Rows.ChildElements.GetItem(1);
-
-
- Cell currentcell = (Cell) currentrow.ChildElements.GetItem(1);
-
-
- 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
- {
-
- string filepath = @ "D:\TPMS\Uploaded_Boq\test.xlsx";
-
-
- using(SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false))
- {
-
-
- WorkbookPart wbPart = doc.WorkbookPart;
-
-
- int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
-
-
- Sheet mysheet = (Sheet) doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0);
-
-
- Worksheet Worksheet = ((WorksheetPart) wbPart.GetPartById(mysheet.Id)).Worksheet;
-
-
- int wkschildno = 4;
-
-
-
- SheetData Rows = (SheetData) Worksheet.ChildElements.GetItem(wkschildno);
-
-
-
- Row currentrow = (Row) Rows.ChildElements.GetItem(0);
-
-
- 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)
- {
-
- 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.