利用OpenXml SDK创建Excel文件和给指定的worksheet填充数据

本程序分别演示了如何创建Excel文件并包含指定名称的Worksheet；如何给Excel文件中指定的Worksheet填充数据

using System;using System.Linq;using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml;using DocumentFormat.OpenXml.Spreadsheet;using System.Collections.Generic;using System.IO;namespace OpenXmlExcelFillDataCS{    class Program    {        static void Main(string[] args)        {            // create the Excel file contains a worksheet named test            CreateSpreadSheet("test");            // get the empty Data.xlsx file from bin folder            string path = AppDomain.CurrentDomain.BaseDirectory + "DataTemplate.xlsx";            if (File.Exists(path))            {                File.Copy(path, "Data.xlsx", true);                {                    FillData("Data.xlsx", "Sheet2");                }            }            else            {                Console.WriteLine("please create the template file: " + path);            }        }        private static void FillData(string path, string sheetName)        {            using (SpreadsheetDocument document = SpreadsheetDocument.Open(               path, true))            {                // find sheets by sheet name                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);                if (sheets.Count() == 0)                {                    // The specified worksheet does not exist.                    return;                }                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);                Worksheet worksheet = worksheetPart.Worksheet;                SheetData sheetData = worksheet.GetFirstChild<SheetData>();                // fill data to Cell F4                Row row1 = new Row() { RowIndex = (UInt32Value)4U };                Cell cell1 = new Cell() { CellReference = "F4" };                CellValue cellValue1 = new CellValue();                cellValue1.Text = "222";                cell1.Append(cellValue1);                row1.Append(cell1);                // fill data to Cell C5                Row row2 = new Row() { RowIndex = (UInt32Value)5U };                Cell cell2 = new Cell() { CellReference = "C5" };                CellValue cellValue2 = new CellValue();                cellValue2.Text = "111";                cell2.Append(cellValue2);                row2.Append(cell2);                // append rows to SheetData elment                sheetData.Append(row1);                sheetData.Append(row2);                // save worksheet                worksheet.Save();            }        }        private static void CreateSpreadSheet(string sheetName)        {            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(   System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"),   SpreadsheetDocumentType.Workbook))            {                // create the workbook                spreadSheet.AddWorkbookPart();                spreadSheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet                spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();                spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();                // create sheet data                spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());                // create row                spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());                // create cell with data                spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(                      new Cell() { CellValue = new CellValue("100") });                // save worksheet                spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();                // create the worksheet to workbook relation                spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());                spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()                {                    Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),                    SheetId = 1,                    Name = sheetName                });                spreadSheet.WorkbookPart.Workbook.Save();            }        }    }}

运行效果：

创建的工作簿

填充数据的工作簿