利用OpenXML向Excel单元格插入内容.docx

利用OpenXML向Excel单元格插入内容编译本主题中的代码需要以下程序集指令：using System.Linq;using DocumentFormat.OpenXml;using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml.Spreadsheet;获取 SpreadsheetDocument 对象 在 Open XML SDK 中，SpreadsheetDocument 类表示 Excel 文档包。若要打开并使用 Excel 文档，请基于文档创建 SpreadsheetDocument 类的一个实例。基于文档创建实例后，即可获取对包含工作表的主工作簿部件的访问权限。在此包中，使用 SpreadsheetML 标记将文档中的文本表示为 XML 形式。若要从文档中创建类实例，请调用 Open 重载方法之一。提供了多个方法，每个方法都有不同的签名。本主题中的示例代码使用带有需要两个参数的签名的 Open(String, Boolean) 方法。第一个参数采用表示要打开的文档的完整路径字符串。第二个参数是 true 或 false，表示是否要打开文件以进行编辑。如果此参数为 false，则不会保存对该文档所做的任何更改。下面的 using 语句中显示了调用 Open 方法的代码。C#/ Open the document for editing.using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true) / Insert other code here.using 语句提供典型 .Open, .Save, .Close 序列的建议备选序列。它确保在遇到右大括号时会自动调用 Dispose 方法（Open XML SDK 用来清理资源的内部方法）。using 语句后面的块为 using 语句中创建或指定的对象设定范围，在此示例中这个范围就是 spreadSheet。SpreadsheetML 文档的基本结构 SpreadsheetML 文档的基本文档结构由引用工作簿中的工作表的 Sheets 和 Sheet 元素组成。将为每个 Worksheet 创建单独的 XML 文件。例如，具有 MySheet1 和 MySheet2 这两张工作表的 Workbook 的 SpreadsheetML 位于 Workbook.xml 文件中，并且显示在以下代码示例中。XML 工作表 XML 文件包含一个或多个块级元素（如 SheetData）。sheetData 表示单元格表，并且包含一个或多个 Row 元素。一个 row 包含一个或多个 Cell 元素。每个单元格包含一个表示相应单元格值的 CellValue 元素。例如，工作簿中只在单元格 A1 中具有值 100 的第一张工作表的 SpreadsheetML 位于 Sheet1.xml 文件中，并且显示在以下代码示例中。XML 100 示例代码的工作方式 打开 SpreadsheetDocument 文档进行编辑后，代码将空的 Worksheet 对象插入到 SpreadsheetDocument 文档包中。然后，将新的 Cell 对象插入到新工作表中并将指定的文本插入到该单元格中。C#/ Given a document name and text, / inserts a new worksheet and writes the text to cell A1 of the new worksheet.public static void InsertText(string docName, string text) / Open the document for editing. Imports (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true) / Get the SharedStringTablePart. If it does not exist, create a new one. SharedStringTablePart shareStringPart; if (spreadSheet.WorkbookPart.GetPartsOfType().Count() 0) shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First(); else shareStringPart = spreadSheet.WorkbookPart.AddNewPart(); / Insert the text into the SharedStringTablePart. int index = InsertSharedStringItem(text, shareStringPart); / Insert a new worksheet. WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart); / Insert cell A1 into the new worksheet. Cell cell = InsertCellInWorksheet(A, 1, worksheetPart); / Set the value of cell A1. cell.CellValue = new CellValue(index.ToString(); cell.DataType = new EnumValue(CellValues.SharedString); / Save the new worksheet. worksheetPart.Worksheet.Save(); 代码传入一个表示要插入到单元格中的文本的参数和一个表示电子表格的 SharedStringTablePart 对象的参数。如果 ShareStringTablePart 对象不包含 SharedStringTable 对象，则代码创建一个。如果文本已存在于 ShareStringTable 对象中，则代码返回表示文本的 SharedStringItem 对象的索引。否则，它创建表示文本的新 SharedStringItem 对象。下面的代码验证指定的文本是否存在于 SharedStringTablePart 对象中，并在不存在时添加文本。C#/ Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text / and inserts it into the SharedStringTablePart. If the item already exists, returns its index.private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) / If the part does not contain a SharedStringTable, create one. if (shareStringPart.SharedStringTable = null) shareStringPart.SharedStringTable = new SharedStringTable(); int i = 0; / Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements() if (item.InnerText = text) return i; i+; / The text does not exist in the part. Create the SharedStringItem and return its index. shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text); shareStringPart.SharedStringTable.Save(); return i;代码使用 AddNewPart 方法向 WorkbookPart 对象添加新 WorksheetPart 对象。然后向 WorksheetPart 对象添加新 Worksheet 对象，并通过以下方式获取新工作表的唯一 ID：选择在电子表格文档中使用的最大 SheetId 对象并加 1 以创建新工作表 ID。它通过将“Sheet”一词和工作表 ID 连接在一起来指定工作表的名称。它然后将新 Sheet 对象追加到 Sheets 集合。下面的代码通过将新的 WorksheetPart 对象添加到 WorkbookPart 对象中来插入新的 Worksheet 对象。C#/ Given a WorkbookPart, inserts a new worksheet.private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) / Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart(); newWorksheetPart.Worksheet = new Worksheet(new SheetData(); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); / Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements().Count() 0) sheetId = sheets.Elements().Select(s = s.SheetId.Value).Max() + 1; string sheetName = Sheet + sheetId; / Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() Id = relationshipId, SheetId = sheetId, Name = sheetName ; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart;为了将单元格插入到工作表中，代码通过按顺序循环访问行元素以查找紧跟在指定行后面的单元格来确定要将新单元格插入到列中的位置。它将此行保存在 refCell 变量中。然后使用 InsertBefore 方法将新单元格插入到 refCell 方法引用的单元格之前。在下面的代码中，将新的 Cell 对象插入到 Worksheet 对象中。C# / Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. / If the cell already exists, returns it. private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild(); string cellReference = columnName + rowIndex; / If the worksheet does not contain a row with the specified row index, insert one. Row row; if (sheetData.Elements().Where(r = r.RowIndex = rowIndex).Count() != 0) row = sheetData.Elements().Where(r = r.RowIndex = rowIndex).First(); else row = new Row() RowIndex = rowIndex ; sheetData.Append(row); / If there is not a cell with the specified column name, insert one. if (row.Elements().Where(c = c.CellReference.Value = columnName + rowIndex).Count() 0) return row.Elements().Where(c = c.CellReference.Value = cellReference).First(); else / Cells must be in sequential order according to CellReference. Determine where to insert the new cell. Cell refCell = null; foreach (Cell cell in row.Elements() if (string.Compare(cell.CellReference.Value, cellReference, true) 0) refCell = cell; break; Cell newCell = new Cell() CellReference = cellReference ; row.InsertBefore(newCell, refCell); worksheet.Save(); return newCell; 示例代码 以下代码示例用于插入新工作表并将文本写入到名为“Sheet8.xlsx”的特定电子表格文档的新工作表的单元格“A1”中。若要调用 InsertText 方法，可以使用以下代码作为示例。 C#InsertText(C:UsersPublicDocumentsSheet8.xlsx, Inserted Text);以下是使用 C# 和 Visual Basic 编写的完整示例代码。C#/ Given a document name and text, / inserts a new work sheet and writes the text to cell A1 of the new worksheet. public static void InsertText(string docName, string text) / Open the document for editing. using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true) / Get the SharedStringTablePart. If it does not exist, create a new one. SharedStringTablePart shareStringPart; if (spreadSheet.WorkbookPart.GetPartsOfType().Count() 0) shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First(); else shareStringPart = spreadSheet.WorkbookPart.AddNewPart(); / Insert the text into the SharedStringTablePart. int index = InsertSharedStringItem(text, shareStringPart); / Insert a new worksheet. WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart); / Insert cell A1 into the new worksheet. Cell cell = InsertCellInWorksheet(A, 1, worksheetPart); / Set the value of cell A1. cell.CellValue = new CellValue(index.ToString(); cell.DataType = new EnumValue(CellValues.SharedString); / Save the new worksheet. worksheetPart.Worksheet.Save(); / Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text / and inserts it into the SharedStringTablePart. If the item already exists, returns its index. private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) / If the part does not contain a SharedStringTable, create one. if (shareStringPart.SharedStringTable = null) shareStringPart.SharedStringTable = new SharedStringTable(); int i = 0; / Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements() if (item.InnerText = text) return i; i+; / The text does not exist in the part. Create the SharedStringItem and return its index. shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text); shareStringPart.SharedStringTable.Save(); return i; / Given a WorkbookPart, inserts a new worksheet. private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) / Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart(); newWorksheetPart.Worksheet = new Worksheet(new SheetData(); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); / Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements().Count() 0) sheetId = sheets.Elements().Select(s = s.SheetId.Value).Max() + 1; string sheetName = Sheet + sheetId; / Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() Id = relationshipId, SheetId = sheetId, Name = sheetName ; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; / Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. / If the cell already exists, returns it. private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart workshe