我有一个现有的电子表格。我想将某些单元格从general
或重新格式化custom
为Number
. 但是出于某种原因,当我重新格式化它们以编号时,单元格值会更改为某个随机数。
这是我的代码的样子
using (var d = SpreadsheetDocument.Open(_docPath, true))
{
var workSheet = GetWorksheetPart(d.WorkbookPart.Workbook.WorkbookPart, "Project Summary");
foreach (var cellRef in _ProjectRequirementsFormService.NumberCellReferences)
{
Cell theCell = workSheet.Worksheet.Descendants<Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
if (theCell != null) {
//For some reason This line of code replaces the cell value with a value 5197
//and every iteration of the loop increments the value of the next cell found 5198, 5199, etc....
theCell.DataType = CellValues.Number;
}
}
...
}
public WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, string sheetName)
{
string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => sheetName.Equals(s.Name)).Id;
return (WorksheetPart)workbookPart.GetPartById(relId);
}
现在出于某种原因,这行代码theCell.DataType = CellValues.Number;
用值 5197 替换单元格值,并且循环的每次迭代都会增加找到的下一个单元格的值 5198、5199 等......
当我评论那条有缺陷的行时,我在单元格中得到了正确的值,但格式不是 Number
这是我添加代码行以将其更改为数字格式时的样子。为什么它会更改所有单元格值以及如何修复它?
链接到示例文件
https://drive.google.com/file/d/0B7UImeY4gR3VdnBBZmdWSHJqN2lhRmFqY1N6THJXNmIzZDhF/view?usp=sharing
Decompile your xlsx to C# using Microsoft Open XML SDK Tools.
https://www.microsoft.com/en-us/download/details.aspx?id=30425
You'll see that your cells have SharedString data type. It's a kind of id, cell stores id, while values are stored in sharedstring table. These weird numbers you see after changing datetype are these ids in sharedstring table. Changing DataType does not really change the way values are stored, it changes how they are interpreted. They still will be "Number stored as string" if you change its type to numeric. For example let's take a look at cell "E17":
Cell cell261 = new Cell(){ CellReference = "E17", StyleIndex = (UInt32Value)11U, DataType = CellValues.SharedString };
CellValue cellValue90 = new CellValue();
cellValue90.Text = "26";
... Should be 50000 isn't it?
SharedStringItem sharedStringItem27 = new SharedStringItem();
Text text45 = new Text();
text45.Text = "50000";
sharedStringItem27.Append(text45);
为了从电子表格中读取正确的值,您需要使用 ms docs 示例中的 GetCellValue 代码。
然后您可以编写以下代码来读取真实的单元格值(我对其进行了一些更改并传递了文档而不是文件路径)解析它并保存为十进制类型:
var value = GetCellValue(d, "Sheet1", cellRef.CellReference);
if (!string.IsNullOrEmpty(value))
{
if (decimal.TryParse(value, out decimal val))
{
cellRef.CellValue = new
CellValue(DocumentFormat.OpenXml.DecimalValue.FromDecimal(val));
cellRef.StyleIndex = 0U;
}
}
StyleIndex 0U 似乎是通用格式,但由于您拥有正确的单元格类型,因此无关紧要。即使我仍然不明白为什么在打开固定的 xlsx 时不计算 =sum 公式。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句