Getting Started
Today, in this article we shall cover below,
Create a .NET Core application
Configure OpenXML Nuget package
Read Excel File
Write Excel file
step 1.Create a .NET Core application
step 2. Install from Nuget package manager,
Install-Package Open-XML-SDK
Once you install this NuGet package, you get full library support on Reading, Writing Excel (.xlsx), Word (.doc), or Powerpoint(.ppt) files using C# API
step 3.
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Nop.Admin.Controllers
{
/// <summary>
/// come from:https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/
/// </summary>
public class ExcelController : Controller
{
// GET: ExcelController
public ActionResult Index()
{
return View();
}
// GET: ExcelController/Details/5
public ActionResult Details()
{
try
{
//Lets open the existing excel file and read through its content . Open the excel using openxml sdk
using (SpreadsheetDocument doc = SpreadsheetDocument.Open("testdata.xlsx", false))
{
//create the object for workbook part
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
StringBuilder excelResult = new StringBuilder();
//using for each loop to get the sheet from the sheetcollection
foreach (Sheet thesheet in thesheetcollection)
{
excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name);
excelResult.AppendLine("----------------------------------------------- ");
//statement to get the worksheet object by using the sheet id
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();
foreach (Row thecurrentrow in thesheetdata)
{
foreach (Cell thecurrentcell in thecurrentrow)
{
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//code to take the string value
excelResult.Append(item.Text.Text + " ");
}
else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
else
{
excelResult.Append(Convert.ToInt16(thecurrentcell.InnerText) + " ");
}
}
excelResult.AppendLine();
}
excelResult.Append("");
Console.WriteLine(excelResult.ToString());
Console.ReadLine();
}
}
}
catch (Exception)
{
}
return View();
}
Summary
The Open XML SDK provides us full control to deal with Office Word, Excel, and PowerPoint documents. As this SDK is free and open-source from Microsoft, we don’t really need to use any other third-party tools/libraries. This SDK works best!
Useful links
1.Write code for workbook styles by giving your own font size, color, font name, border properties, cell style formats etc using openxml
https://www.c-sharpcorner.com/article/creating-excel-file-using-openxml/