. net core uses openxml to export and import Excel

Time:2020-1-13

Exporting excel is a very common function of programs. Net core can export excel with the help of open XML SDK.

Open-XML-SDK

Open XML SDK is an open source project of Microsoft. The open XML SDK is an open source class library for open XML documents (docx, xlsx, and pptx). It can:

  • Very good performance when generating word, Excel and ppt
  • Populating content from an XML data source into a word document
  • Single word, Excel, PPT can be divided into multiple files or combined into one file
  • Docx = > HTML / CSS and HTML / CSS = > docx provides a friendly and convenient way
  • Extract data from Excel
  • Find or replace content in docx / pptx with regular expression
  • Update cached data or embedded tables in docx / pptx
  • Changes to document content, such as removing tracked changes from a file or removing unacceptable content

Depending on windowsbase or system.io.packaging

Open source address: open XML SDK

Nuget:DocumentFormat.OpenXml

Open XML SDK for office documents

Example

Add nuget package reference first

Direct code:

static void Main(string[] args)
{
 var spreadsheetDocument = SpreadsheetDocument.Create("F:\OpenXml.xlsx", SpreadsheetDocumentType.Workbook);
 var workbookpart = spreadsheetDocument.AddWorkbookPart();
 workbookpart.Workbook = new Workbook();
 WorksheetPart worksheetPart = workbookpart.AddNewPart();
 worksheetPart.Worksheet = new Worksheet(new SheetData());
 Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
 Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
 sheets.Append(sheet);
 var sheetData = worksheetPart.Worksheet.GetFirstChild();
 for (int i = 0; i < 10; i++)
 {
  Row row = new Row();
  for (int j = 0; j < 10; j++)
  {
   Cell dataCell = new Cell();
   Datacell. Cellvalue = new cellvalue ($"{I + 1} row {j + 1} column");
   dataCell.DataType = new EnumValue(CellValues.String);
   row.AppendChild(dataCell);
  }
  sheetData.Append(row);
 }


 workbookpart.Workbook.Save();
 spreadsheetDocument.Close();
}

The exported excel is as follows:

Read Excel

Reading excel code is relatively simple:


static void Main(string[] args)
{
 using (SpreadsheetDocument doc = SpreadsheetDocument.Open(@"F:\OpenXml.xlsx", false))
 {
  WorkbookPart wbPart = doc.WorkbookPart;
  Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.FirstOrDefault();
  Worksheet worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet;
  SheetData sheetData = (SheetData)worksheet.ChildElements.FirstOrDefault();

  foreach (var row in sheetData.ChildElements)
  {
   foreach (var cell in (row as Row).ChildElements)
   {
    var cellValue = (cell as Cell).CellValue;
    if (cellValue != null)
    {
     Console.WriteLine(cellValue.Text);
    }
   }
  }
 }
}

The above is the whole content of this article. I hope it will help you in your study, and I hope you can support developepaer more.