C# 修复记录：从头创建的工作表中的单元格信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9401508/
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
Repaired Records : Cell information from worksheet created from scratch
提问by Kulingar
I'm receiving an error when opening my OpenXML created spreadsheet. The error is as follows.
打开 OpenXML 创建的电子表格时收到错误消息。错误如下。
Repaired Records: Cell information from /xl/worksheets/sheet.xml part
Repaired Records: Cell information from /xl/worksheets/sheet2.xml part
Repaired Records: Cell information from /xl/worksheets/sheet3.xml part
The only thing I could find online that was helpful was this issue was the discussion of an algorithm which alters an individual cell multiple times causing the issue. Having said that, I'm going to link my Constructor for the SpreadsheetDocument as well as the three functions for updating a cell (which I do once).
我在网上找到的唯一有用的是这个问题是对算法的讨论，该算法多次更改单个单元格导致问题。话虽如此，我将链接电子表格文档的构造函数以及更新单元格的三个函数（我做过一次）。
I can supply any additional functions as needed, but I believe the problem is somewhere in the two listed below.
我可以根据需要提供任何附加功能，但我相信问题出在下面列出的两个功能中。
By the way,
顺便一提，
GetWorksheetPartByName
InsertCellInWorksheet
GetCell
should all working as intended.
应该都按预期工作。
The Actual Program
实际程序
static void Main(string[] args)
{
//Full path for File
const string newFile = "@C:\test.xlsx";
//Constructor creates default worksheet called "mySheet"
var spreadsheet = new XLSXHelper(newFile);
//updating some cells.
spreadsheet.UpdateCell("mySheet", "D2", "R", 2);
}
Constructor
构造函数
public XLSXHelper(string filepath)
{
newFile = filepath;
spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
this.workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
this.worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
Update Cell
更新单元格
public void UpdateCell(string worksheetName, string textToInsert, string columnName, uint rowIndex)
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(newFile, true))
{
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, worksheetName);
if (worksheetPart != null)
{
InsertCellInWorksheet(columnName, rowIndex, worksheetPart);
Cell cell = GetCell(worksheetPart.Worksheet,columnName, rowIndex);
cell.CellValue = new CellValue(textToInsert);
worksheetPart.Worksheet.Save();
}
}
}
采纳答案by Josh
If you are adding a string to a cell rather than a number (or a string that can be converted to a number) then you should use an inline string or a shared string instead of the CellValue. You can only use CellValue if the value is numeric.
如果要向单元格添加字符串而不是数字（或可以转换为数字的字符串），则应使用内联字符串或共享字符串而不是 CellValue。如果值为数字，则只能使用 CellValue。
The XML generated when using CellValue looks something like:
使用 CellValue 时生成的 XML 类似于：
<x:row>
<x:c>
<x:v>12345</x:v>
</x:c>
</x:row>
when you use an inline string it looks like:
当您使用内联字符串时，它看起来像：
<x:row>
<x:c t="inlineStr">
<x:is>
<x:t>Foo</x:t>
</x:is>
</x:c>
</x:row>
note the "is" node for inline string and that the cell type attribute is set to "inlineStr".
请注意内联字符串的“is”节点，并且单元格类型属性设置为“inlineStr”。
Here is C# code to generate correct XML for a cell containing text:
这是为包含文本的单元格生成正确 XML 的 C# 代码：
cell.DataType = CellValues.InlineString;
cell.InlineString = new InlineString() { Text = new Text(textToInsert) };
From what I have read using shared strings is preferable but using inline strings avoids the error and looks just fine when you open the file in Excel.
从我读过的内容来看，使用共享字符串更可取，但使用内联字符串可以避免错误，并且在 Excel 中打开文件时看起来很好。
回答by Steve Danner
I realize this is an old thread, but I just had the same error message pop up for a programmatically generated Excel sheet also. My issue was that I had been setting the name of the worksheet with a name that had a forward slash in it.
我意识到这是一个旧线程，但我也为以编程方式生成的 Excel 工作表弹出了相同的错误消息。我的问题是我一直在使用带有正斜杠的名称设置工作表的名称。
Hopefully this helps someone else who stumbles on this thread as it's the most popular thread on the topic.
希望这可以帮助其他人偶然发现此线程，因为它是该主题上最受欢迎的线程。
回答by Gary Walker
Another late one - but check how you're adding cells to a row. Have you cut and paste add cell code in which there's a 'simple' compare with the existing cell reference (A1 etc) in the row? In which case if you have a cell beyond column Z - AA1 onwards - then you might end up trying to insert cell (eg) AB1) before cell B1. You'll then get this error on opening the written sheet in excel. Instead, if simply adding cell after cell along each row, just go straight to insert before with the reference cell set to null - ie. add new cell to end.
另一个迟到的 - 但请检查您如何将单元格添加到行中。您是否剪切并粘贴添加了与行中现有单元格引用（A1 等）进行“简单”比较的单元格代码？在这种情况下，如果您有一个超出 Z 列的单元格 - AA1 开始 - 那么您最终可能会尝试在单元格 B1 之前插入单元格（例如）AB1）。然后，您将在 Excel 中打开书面工作表时收到此错误。相反，如果只是沿每一行在单元格后添加单元格，只需直接插入之前将参考单元格设置为空 - 即。添加新单元格以结束。
Hope that makes sense.
希望这是有道理的。
回答by Janis S.
Stumbled on this issue myself and found this thread. Difference in my case could not repair the file.
Issue was row index which I passed to InsertCellInWorksheettaken from example on MSDN.
我自己偶然发现了这个问题并找到了这个线程。在我的情况下无法修复文件的差异。问题是我InsertCellInWorksheet从 MSDN 上的示例中传递给的行索引。
Cell cell = InsertCellInWorksheet("A", lastRow.RowIndex, worksheetPart);
When inserting a row, make sure the index of the row is 1, not 0. However, 0 for the SheetDatacollection.
插入一行时，请确保该行的索引为 1，而不是 0。但是，SheetData集合的索引为 0 。
sheetData.InsertAt(new Row() { RowIndex = 1 }, 0);
lastRow = sheetData.Elements<Row>().LastOrDefault();
In hope to save someone time who goes through the same trouble.
希望能节省遇到同样麻烦的人的时间。
Interestingly enough, my integration test passed, however, when opening the file via Excel, it was showing the pop-up about corruption. Seems there is a row with index 0, however, bit secretive as you will not be able to open a file with Excel.
有趣的是，我的集成测试通过了，但是，当通过 Excel 打开文件时，它显示了关于损坏的弹出窗口。似乎有一行索引为 0，但是，由于您将无法使用 Excel 打开文件，因此有点隐秘。
回答by UzumakiL
Adding to the list of potential solutions (Year2017 :) ) :
添加到潜在解决方案列表（Year2017 :)）：
I was facing this issue because I was not using the correct workbook object to write tothe FileOutputStream.
我面临这个问题是因为我没有使用正确的工作簿对象来写入 FileOutputStream。
Hope this helps someone.
希望这可以帮助某人。
回答by Hitesh
It's an old thread, however I had the same problem and it might be useful to someone like me who landed on this thread to find answer :)
这是一个旧线程，但是我遇到了同样的问题，它可能对像我这样登陆该线程以找到答案的人有用:)
The problem with me was that my application has an export module, and exported file from one environment was working fine, however from other it was not. It was the same error:
我的问题是我的应用程序有一个导出模块，并且从一个环境导出的文件工作正常，但从另一个环境中却没有。这是同样的错误：
Repaired Records: Cell information from /xl/worksheets/sheet19.xml part
After doing a deep investigation, I found out that for one of the records in this sheet exceeding the cell limit of 32767 chars (excel limitation)and by reducing the size of texts on DB directly, solved the problem. :)
经过深入调查，我发现该表中的一条记录超过了32767个字符的单元格限制（excel限制），并通过直接减少DB上的文本大小解决了问题。:)
I hope it will help :)
我希望它会有所帮助:)
回答by Umakanth Nelige
In my case, the SSRS place holder properties had custom number.
就我而言，SSRS 占位符属性具有自定义编号。
Fix:
使固定：
- GO to all the fields one by one in SSRS designer.
- See if there are any alpha numeric values coming from backend. ( cross compare your data set with all the cells in the SSRS report.
- double click on the field -> go to 'Number' tab
- Click on the default -> ok
- SSRS设计器中的所有领域一一进入。
- 查看是否有任何来自后端的字母数字值。（将您的数据集与 SSRS 报告中的所有单元格进行交叉比较。
- 双击该字段 -> 转到“数字”选项卡
- 点击默认 -> 确定
Thanks Umakanth Nelige
感谢 Umakanth Nelige
