C# Open XML SDK 2.0 - 如何更新电子表格中的单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/527028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Open XML SDK 2.0 - how to update a cell in a spreadsheet?
提问by cdonner
I want to update a cell in a spreadsheet that is used by a chart, using the Open XML SDK 2.0 (CTP). All the code samples I have found insert new cells. I am struggling with retrieving the right worksheet.
我想使用 Open XML SDK 2.0 (CTP) 更新图表使用的电子表格中的单元格。我发现的所有代码示例都插入了新单元格。我正在努力检索正确的工作表。
public static void InsertText(string docName, string text, uint rowIndex,
string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet =
SpreadsheetDocument.Open(docName, true))
{
Workbook workBook = spreadSheet.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workBook.WorkbookPart.
WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.
GetFirstChild<SheetData>();
// If the worksheet does not contain a row with the specified
// row index, insert one.
Row row;
if (sheetData.Elements<Row>().Where(
r => r.RowIndex == rowIndex).Count() != 0)
// At this point I am expecting a match for a row that exists
// in sheet1 but I am not getting one
When I navigate the tree in Visual Studio, I am seeing three sheets, but none of them has any children. What am I missing?
当我在 Visual Studio 中导航树时，我看到了三张纸，但没有一张有任何孩子。我错过了什么？
采纳答案by cdonner
Here is the working code. This is a prototype. For a larger number of changes, one might open the document only once. Also, there are some hard-coded things like sheet name and cell type that would have to be parameterized before this can be called production-ready. http://openxmldeveloper.org/forums/4005/ShowThread.aspxwas very helpful.
这是工作代码。这是一个原型。对于大量更改，您可能只打开文档一次。此外，还有一些硬编码的东西，如工作表名称和单元格类型，必须先进行参数化，然后才能将其称为生产就绪。 http://openxmldeveloper.org/forums/4005/ShowThread.aspx非常有帮助。
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;
using System.Diagnostics;
namespace OpenXMLWindowsApp
{
public class OpenXMLWindowsApp
{
public void UpdateSheet()
{
UpdateCell("Chart.xlsx", "20", 2, "B");
UpdateCell("Chart.xlsx", "80", 3, "B");
UpdateCell("Chart.xlsx", "80", 2, "C");
UpdateCell("Chart.xlsx", "20", 3, "C");
ProcessStartInfo startInfo = new ProcessStartInfo("Chart.xlsx");
startInfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startInfo);
}
public static void UpdateCell(string docName, string text,
uint rowIndex, string columnName)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet =
SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart =
GetWorksheetPartByName(spreadSheet, "Sheet1");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType =
new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart
GetWorksheetPartByName(SpreadsheetDocument document,
string sheetName)
{
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
// The specified worksheet does not exist.
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
// Given a worksheet, a column name, and a row index,
// gets the cell at the specified column and
private static Cell GetCell(Worksheet worksheet,
string columnName, uint rowIndex)
{
Row row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}
}
}
回答by pajics
I've been working with excel and found this helper library to be of great help (I've created my own helpers for word, would have saved at least 2 weeks if I was aware of this): https://www.nuget.org/packages/SimpleOOXML/
我一直在使用 excel 并发现这个助手库有很大帮助（我已经为 word 创建了自己的助手，如果我意识到这一点，至少可以节省 2 周时间）： https://www.nuget .org/packages/SimpleOOXML/
This is what is needed to update cell (writer.PasteText(...)):
这是更新单元格所需的内容（writer.PasteText(...)）：
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("B2", "Hello World");
//Save to the memory stream
SpreadsheetWriter.Save(doc);
byte[] result = stream.ToArray();
FileStream file = new FileStream(@"D:\x1.xlsx", FileMode.Create);
file.Write(result, 0, result.Length);
file.Close();
回答by Ashwin
The Code posted by @CDonner throws some exceptions, i have added some of the code that will take care of code, which throws an Exceptions, here it is
@CDonner 发布的代码引发了一些异常，我添加了一些处理代码的代码，这些代码会引发异常，这里是
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.IO;
using System.Diagnostics;
namespace Application.Model{
public class TempCode
{
public TempCode()
{
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "120", 1, "A");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "220", 2, "B");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "320", 3, "C");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "420", 4, "D");
UpdateCell("E:/Visual Studio Code/Book1.xlsx", "520", 5, "E");
ProcessStartInfo startInfo = new ProcessStartInfo("E:/Visual Studio Code/Book1.xlsx");
startInfo.WindowStyle = ProcessWindowStyle.Normal;
Process.Start(startInfo);
}
public static void UpdateCell(string docName, string text,uint rowIndex, string columnName){
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Sheet2");
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(text);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
}
private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName){
IEnumerable<Sheet> sheets =document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
Elements<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0){
return null;
}
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
return worksheetPart;
}
private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
Row row;
string cellReference = columnName + rowIndex;
if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
row = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
else{
row = new Row() { RowIndex = rowIndex };
worksheet.Append(row);
}
if (row == null)
return null;
if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0) {
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else{
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>()){
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0){
refCell = cell;
break;
}
}
Cell newCell = new Cell() {
CellReference = cellReference,
StyleIndex = (UInt32Value)1U
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
}
}
}
回答by Janis S.
This is SDK 2.5 though, however, was very useful code found here: http://fczaja.blogspot.dk/2013/05/how-to-read-and-write-excel-cells-with.html
不过，这是 SDK 2.5，但在这里找到了非常有用的代码：http: //fczaja.blogspot.dk/2013/05/how-to-read-and-write-excel-cells-with.html
Needed to do a slight modification for text values to add them to the SharedStringTablePart.
需要对文本值稍加修改才能将它们添加到SharedStringTablePart.
// 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<SharedStringItem>())
{
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 Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
And use it like this:
并像这样使用它：
SharedStringTablePart shareStringPart = GetSharedStringTablePart(excelDoc);
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(cellValue, shareStringPart);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
回答by zerala
I made some changes on @AZ code.
我对@AZ 代码进行了一些更改。
First, on GetCell function there is a problem on selecting the current row. Just change:
首先，在 GetCell 函数上，选择当前行有问题。只是改变：
if (worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
instead of:
代替：
if (worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
And in the section:
在该部分：
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
If you are using Columns above Z Column (as AA column, for example) will not work properly. To some this, I'm using the column numbers to determinate where insert the Cell.
如果您使用 Z 列上方的列（例如，作为 AA 列）将无法正常工作。对此，我使用列号来确定插入单元格的位置。
For this, I created a function ColumnIndex, with convert the column letters to numbers:
为此，我创建了一个函数 ColumnIndex，将列字母转换为数字：
private static int ColumnIndex(string reference)
{
int ci = 0;
reference = reference.ToUpper();
for (int ix = 0; ix < reference.Length && reference[ix] >= 'A'; ix++)
ci = (ci * 26) + ((int)reference[ix] - 64);
return ci;
}
So I changed the string compare function for this:
所以我为此更改了字符串比较函数：
string columnNew = new String(cellReference.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
foreach (Cell cell in row.Elements<Cell>())
{
string columnBase = new String(cell.CellReference.Value.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
if (ColumnIndex(columnBase) > ColumnIndex(columnNew))
{
refCell = cell;
break;
}
}
Best Regards.
此致。
回答by ravi kalkote
var sheetData = new SheetData();
var row = UpdateCell("A","Hello World", 5);
sheetData.Append(row);
worksheet.Append(sheetData);
private static Row UpdateCell(string columnName, string value, int rowIndex)
{
Row row = new Row { RowIndex = (uint)rowIndex };
Cell c1 = new TextCell(columnName, value, rowIndex);
row.Append(c1);
return row;
}
public class TextCell : Cell
{
public TextCell(string header, string text, int index)
{
this.DataType = CellValues.InlineString;
this.CellReference = header + index;
//Add text to the text cell.
this.InlineString = new InlineString { Text = new Text { Text = text } };
}
}
