在Excel中打开使用OpenXML创建的Excel文件时需要修复 [英] Created Excel file using OpenXML needs repair when opened in Excel
问题描述
我正在尝试使用C#openXML插入Excel文件(.xlsx)单元格
I am trying to insert Excel file (.xlsx) cell using C# openXML,
但是当我打开创建的Excel文件时,需要通过显示以下错误来修复它,
but when I open the created Excel file it is need to be repaired by showing following error,
已修复的记录:/xl/worksheets/sheet1.xml部分中的单元格信息
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part
这是我的代码.
public void InsertText(TestModelList data)
{
var date_time = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
string OutputFileDirectory = "E:\\TEST";
string fileFullName = Path.Combine(OutputFileDirectory, "Output.xlsx");
if (File.Exists(fileFullName))
{
fileFullName = Path.Combine(OutputFileDirectory, "Output_" + date_time + ".xlsx");
}
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileFullName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbp = spreadSheet.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
InsertToCell(1, "C", "1C", sd);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet()
{
Id = wbp.GetIdOfPart(wsp),
Name = "test",
SheetId = 1
};
sheets.Append(sheet);
wb.Append(sheets);
spreadSheet.WorkbookPart.Workbook = wb;
spreadSheet.WorkbookPart.Workbook.Save();
}
}
private void InsertToCell(uint rowIndex, string col, string value, SheetData sd)
{
var row = new Row() { RowIndex = rowIndex };
var cellReference = col + rowIndex;
Cell newCell = new Cell
{
StyleIndex = (UInt32Value)1U,
CellValue = new CellValue(value),
DataType = CellValues.SharedString,
CellReference = cellReference
};
row.InsertBefore(newCell, null);
sd.Append(row);
}
任何人都可以帮助我解决此问题吗?
Can anyone please help me to fix this issue?
推荐答案
您正在创建的新单元格存在一些问题.首先,您将 StyleIndex
属性( ssml:s
属性)设置为 1U
的值,而没有 WorkbookStylesPart
,其中包含可引用的单元格样式.其次,您将 DataType
属性( ssml:t
属性)设置为 CellValues.SharedString
的值(即"s"
),而没有 SharedStringTablePart
. CellValue
属性( ssml:v
元素)应该是 SharedStringItem
( ssml:si
元素)放在 SharedStringTable
( ssml:sst
元素)中,而不是"1C"
.
There are a few issues with the new cell that you are creating. Firstly, you are setting the StyleIndex
property (ssml:s
attribute) to a value of 1U
without having a WorkbookStylesPart
that contains a referenceable cell style. Secondly, you are setting the DataType
property (ssml:t
attribute) to a value of CellValues.SharedString
(i.e., "s"
) without having a SharedStringTablePart
. The CellValue
property (ssml:v
element) should be the zero-based index of the SharedStringItem
(ssml:si
element) within the SharedStringTable
(ssml:sst
element) instead of "1C"
.
以下是一些示例代码,它们以工作单元测试的形式演示了如何完成您想要实现的目标:
Here is some sample code in the form of a working unit test that demonstrates how to do what you want to achieve:
[Fact]
public void CanInsertCell()
{
using var stream = new MemoryStream();
using (var spreadsheetDocument =
SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
// Create an empty workbook.
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook(new Sheets());
// Create an empty worksheet and add the worksheet to the workbook.
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
workbookPart.Workbook.Sheets.AppendChild(new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
Name = "Test",
SheetId = 1
});
// This example correctly inserts a cell with an inline string,
// noting that Excel always inserts shared strings as shown in
// the next example.
InsertCellWithInlineString(worksheetPart.Worksheet, 1, "C", "1C");
// This example inserts a cell with a shared string that is
// contained in the SharedStringTablePart. Note that the cell
// value is the zero-based index of the SharedStringItem
// contained in the SharedStringTable.
var sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>();
sharedStringTablePart.SharedStringTable =
new SharedStringTable(
new SharedStringItem(
new Text("2C")));
InsertCellWithSharedString(worksheetPart.Worksheet, 2, "C", 0);
}
File.WriteAllBytes("WorkbookWithNewCells.xlsx", stream.ToArray());
}
private static void InsertCellWithInlineString(
Worksheet worksheet,
uint rowIndex,
string columnName,
string value)
{
InsertCell(worksheet, rowIndex, new Cell
{
CellReference = columnName + rowIndex,
DataType = CellValues.InlineString,
InlineString = new InlineString(new Text(value)),
});
}
private static void InsertCellWithSharedString(
Worksheet worksheet,
uint rowIndex,
string columnName,
uint value)
{
InsertCell(worksheet, rowIndex, new Cell
{
CellReference = columnName + rowIndex,
DataType = CellValues.SharedString,
CellValue = new CellValue(value.ToString())
});
}
private static void InsertCell(Worksheet worksheet, uint rowIndex, Cell cell)
{
SheetData sheetData = worksheet.Elements<SheetData>().Single();
// Get or create a Row with the given rowIndex.
Row row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
if (row == null)
{
row = new Row { RowIndex = rowIndex };
// The sample assumes that the newRow can simply be appended,
// e.g., because rows are added in ascending order only.
sheetData.AppendChild(row);
}
// The sample assumes two things: First, no cell with the same cell
// reference exists. Second, cells are added in ascending order.
// If that is not the case, you need to deal with that situation.
row.AppendChild(cell);
}
这篇关于在Excel中打开使用OpenXML创建的Excel文件时需要修复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!