There is require installing of DocumentFormat.OpenXml from nuget in visual studio or download DocumentFormat.OpenXml.dll and use in the project.
Below is the code for reading Excel file and stored data in dataset.
Namespaces:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Text.RegularExpressions;
Method for Reading EXCEL file
public static DataSet ReadExcelFile(string filePath)
{
try
{
var ds = new DataSet();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string sheetName = “sheetName”;
var strSheets = workbookPart.Workbook.Descendants<Sheet>();
Sheet Plantsheet = strSheets.FirstOrDefault();
var wp = (WorksheetPart)workbookPart.GetPartById(Plantsheet.Id);
SheetData sheetData = wp.Worksheet.Elements<SheetData>().First();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
//add Columns
ds.Tables.Add(sheetName);
ds.Tables[sheetName].Columns.Add(“ColumnName1”);
ds.Tables[sheetName].Columns.Add(“ColumnName2”);
ds.Tables[sheetName].Columns.Add(“ColumnName3”);
ds.Tables[sheetName].Columns.Add(“ColumnName4”);
// add column name as per requirement of as per Excel header
//add rows
foreach (Row r in sheetData.Elements<Row>())
{
DataRow dr = ds.Tables[sheetName].NewRow();
for (int k = 0; k < r.Descendants<Cell>().Count(); k++)
{
Cell cell = r.Descendants<Cell>().ElementAt(k);
dr[k] = GetCellValue(spreadsheetDocument, cell);
}
ds.Tables[sheetName].Rows.Add(dr);
}
}
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
Method for reading Excel Cell data:
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
try
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
string value = cell.CellValue?.InnerXml;
if (!string.IsNullOrEmpty(value) && cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}
catch (Exception ex)
{
throw ex;
}
}
vkbiztalk.com 