本程序分别演示了如何创建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();
}
}
}
}
运行效果:
创建的工作簿
填充数据的工作簿