OpenXML SDK 2.5不可读的内容
本文关键字：SDK OpenXML | 更新日期: 2023-09-27 18:10:20
我正在处理一个已经拥有所有公式和格式的现有Excel文件，我将数据添加到表格的工作表中，然后当我在Excel中打开该文件时，我得到了错误
"Excel已完成文件级验证和修复。这其中的一部分工作簿可能已被修复或丢弃。删除记录:Cell/xl/worksheets/sheet6.xml part"
然后打开一个具有相同内容的手动创建的文件，它工作得很好。我还发现了Open XML 2.5生产力工具，当我对生成的文件运行验证时，它说没有发现问题。
当我对两个文件运行比较时，我看到生成的文件看起来像这样。
<x:c r="B462" t="inlineStr">
<x:is>
<x:t>1150828</x:t>
</x:is>
</x:c>
而手动创建的文件的单元格看起来像这样。
<c s="80" r="B462">
<v>
1150828
</v>
</c>
显然这里有差异，但我不知道如何纠正它，也不知道这种差异是否是错误的实际原因。但看到其他一切看起来都一样，我不知道还会是什么。
哦，还有更多的事情这个文件不工作，但我能够使用另一个不包含表的文件，当我合并一个表时，问题发生了，所以我至少知道这么多。
如果你建议我使用ClosedXML，请不要。我用过它，它倾向于随机离开格式化，因为某些原因，我不能弄清楚，因此，为什么我已经移动到OpenXML SDk
下面是一些c#代码 dt.Load(reader);
RowCount = dt.Rows.Count;
ColumnCount = dt.Columns.Count;
workbookPart = spreadDoc.WorkbookPart;
SheetDimension sheetDimension = new SheetDimension() { Reference = "A1:" + ColumnLetters[ColumnCount - 1] + (RowCount + 1) };
worksheetPart = Program.GetWorksheetPart(workbookPart, reportStep.ExcelSheetName);
worksheetPart.Worksheet.SheetDimension = sheetDimension;
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => reportStep.ExcelSheetName.Equals(s.Name)).Id;
if (reportStep.ExcelTableExists)
{
TableDefinitionPart tableDef = null;
int looper = 0;
foreach (WorksheetPart wsp in spreadDoc.WorkbookPart.WorksheetParts)
{
if (wsp.TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(reportStep.ExcelTableName)).Count() == 1)
{
tableDef = spreadDoc.WorkbookPart.WorksheetParts.ElementAt(looper).TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(reportStep.ExcelTableName)).FirstOrDefault();
tableDef.Table.Reference.Value = "A1:" + (ColumnLetters[ColumnCount - 1] + (RowCount +1) ).ToString();
tableDef.Table.AutoFilter.Reference.Value = "A1:" + (ColumnLetters[ColumnCount - 1] + (RowCount +1)).ToString();
// tabledefinitionPart = Program.GetTablePart(wsp, reportStep.ExcelTableName, ColumnCount, RowCount);
}
looper++;
}
}
sheetData = Chef.Program.ExportDataTable(dt, sheetData);
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == reportStep.ExcelSheetName);
public static TableDefinitionPart GetTablePart(WorksheetPart worksheet, string tablename, int columnCount, int rowCount)
{
uint CellRange = (uint)(columnCount);
TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)(CellRange) };
var tableDefPart = worksheet.TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(tablename)).FirstOrDefault();
//worksheet.WorksheetPart.TableDefinitionParts.AddNewPart<TableDefinitionPart>(tablename);
var table = new Table() { HeaderRowCount = (uint)columnCount, Name = tablename, DisplayName = tablename, Reference = "A1:" + ColumnLetters[columnCount -1] + (rowCount + 1), TotalsRowShown = false };
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
table.Append(tableStyleInfo1);
// table.Append(tableColumns1);
tableDefPart.Table = table;
return tableDefPart;
}
编辑SECTION添加了额外的方法，更新于2015年5月9日
我确实删除了添加头值的代码，因为它们已经是excel文件的基本模板的一部分。还删除了指定单元格数据类型，以保留模板中已经设置的单元格数据类型。
public static SheetData ExportDataTable2(System.Data.DataTable exportData, SheetData sheetData)
{
//loop through each data row
DataRow contentRow;
int startRow = 2;
for (int i = 0; i < exportData.Rows.Count; i++)
{
contentRow = exportData.Rows[i];
sheetData.AppendChild(createContentRow(contentRow, i + startRow));
}
return sheetData;
}
private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
// cell.DataType = CellValues.Number;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
cell.CellValue = new CellValue(cellValue.ToString());
return cell;
}
private static Row createContentRow(DataRow dataRow, int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
// dataCell.DataType = CellValues.SharedString;
row.AppendChild(dataCell);
}
return row;
}
好吧，看来您已经使用了以下示例OpenXML SDK 2.0:导出一个DataTable到Excel作为您的代码的基础。下面是创建单元格的原始代码:
private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
您的原始代码完全相同，除了以下行:
cell.DataType = CellValues.String;
看到区别了吗?
那么你就把它改成:
private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
// cell.DataType = CellValues.Number;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
cell.CellValue = new CellValue(cellValue.ToString());
return cell;
}
好的，问题是你没有正确设置cell.DataType。它需要与单元格内容同步，否则你会从Excel中得到这样的错误。在前一种情况下，将内容设置为inline string，但将数据类型设置为String。在后面-数据类型为Number(没关系，你已经注释了行- Number是单元格的默认数据类型)，但内容并不总是一个数字(相同的函数用于列标题-毕竟，它被称为createTextCell)。
private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
cell.CellValue = new CellValue(cellValue.ToString());
return cell;
}
最后，如果您需要存储共享字符串、数字、日期等，请阅读文档并设置适当的属性。我同意OpenXml API不是很直观，但这就是我们所拥有的。
EDIT:根据你的评论，似乎你真正的问题并不是问题所在。下面是导出具有不同数据类型列的DataTable的高性能示例:
public static class ExcelExporter
{
public static void ExportDataTable(DataTable table, SheetData data)
{
var cellFactory = new CellFactory[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++)
cellFactory[i] = GetCellFactory(table.Columns[i].DataType);
int rowIndex = 0;
data.AppendChild(CreateHeaderRow(rowIndex++, table));
for (int i = 0; i < table.Rows.Count; i++)
data.AppendChild(CreateContentRow(rowIndex++, table.Rows[i], cellFactory));
}
private static Row CreateHeaderRow(int rowIndex, DataTable table)
{
var row = CreateRow(rowIndex);
for (int i = 0; i < table.Columns.Count; i++)
{
var cell = CreateTextCell(i, rowIndex, table.Columns[i].ColumnName);
row.AppendChild(cell);
}
return row;
}
private static Row CreateContentRow(int rowIndex, DataRow dataRow, CellFactory[] cellFactory)
{
var row = CreateRow(rowIndex);
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
var cell = cellFactory[i](i, rowIndex, dataRow[i]);
row.AppendChild(cell);
}
return row;
}
private static Row CreateRow(int index) { return new Row { RowIndex = (uint)index + 1 }; }
private delegate Cell CellFactory(int columnIndex, int rowIndex, object cellValue);
private static CellFactory GetCellFactory(Type dataType)
{
CellFactory factory;
return CellFactoryMap.TryGetValue(dataType, out factory) ? factory : TextCellFactory;
}
private static readonly CellFactory TextCellFactory = CreateTextCell;
private static readonly CellFactory DateCellFactory = CreateDateCell;
private static readonly CellFactory NumericCellFactory = CreateNumericCell;
private static readonly CellFactory BooleanCellFactory = CreateBooleanCell;
private static readonly Dictionary<Type, CellFactory> CellFactoryMap = new Dictionary<Type, CellFactory>
{
{ typeof(bool), BooleanCellFactory },
{ typeof(DateTime), DateCellFactory },
{ typeof(byte), NumericCellFactory },
{ typeof(sbyte), NumericCellFactory },
{ typeof(short), NumericCellFactory },
{ typeof(ushort), NumericCellFactory },
{ typeof(int), NumericCellFactory },
{ typeof(uint), NumericCellFactory },
{ typeof(long), NumericCellFactory },
{ typeof(ulong), NumericCellFactory },
{ typeof(float), NumericCellFactory },
{ typeof(double), NumericCellFactory },
{ typeof(decimal), NumericCellFactory },
};
private static Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue)
{
return CreateCell(CellValues.String, columnIndex, rowIndex, ToExcelValue(cellValue));
}
private static Cell CreateDateCell(int columnIndex, int rowIndex, object cellValue)
{
// NOTE: CellValues.Date is not supported in older Excel version.
// In all Excel versions dates can be stored with CellValues.Number and a format style.
// Since I have no styles, will export them just as text
//var cell = CreateCell(CellValues.Number, columnIndex, rowIndex, ToExcelDate(cellValue));
//cell.StyleIndex = ...;
//return cell;
return CreateCell(CellValues.String, columnIndex, rowIndex,
cellValue != null && cellValue != DBNull.Value ? ((DateTime)cellValue).ToShortDateString() : null);
}
private static Cell CreateNumericCell(int columnIndex, int rowIndex, object cellValue)
{
return CreateCell(CellValues.Number, columnIndex, rowIndex, ToExcelValue(cellValue));
}
private static Cell CreateBooleanCell(int columnIndex, int rowIndex, object cellValue)
{
// NOTE: CellValues.Boolean is not supported in older Excel version
//return CreateCell(CellValues.Boolean, columnIndex, rowIndex, ToExcelValue(cellValue));
return CreateCell(CellValues.String, columnIndex, rowIndex, ToExcelValue(cellValue));
}
private static Cell CreateCell(CellValues dataType, int columnIndex, int rowIndex, string cellValue)
{
var cell = new Cell();
if (dataType != CellValues.Number) cell.DataType = dataType;
cell.CellReference = GetColumnName(columnIndex) + (rowIndex + 1);
cell.CellValue = new CellValue(cellValue ?? string.Empty);
return cell;
}
private static string ToExcelValue(object value)
{
if (value == null || value == DBNull.Value) return null;
return Convert.ToString(value, CultureInfo.InvariantCulture);
}
private static DateTime ExcelBaseDate = new DateTime(1900, 1, 1);
private static string ToExcelDate(object value)
{
const int days29Feb1900 = 59;
if (value == null || value == DBNull.Value) return null;
var date = ((DateTime)value).Date;
var days = (date - ExcelBaseDate).Days + 1;
if (days >= days29Feb1900) days++;
return days.ToString(CultureInfo.InvariantCulture);
}
private static string GetColumnName(int index) { return ColumnNameTable[index]; }
private static readonly string[] ColumnNameTable = BuildColumnNameTable();
private static string[] BuildColumnNameTable()
{
var table = new string[16384];
var sb = new StringBuilder();
for (int i = 0; i < table.Length; i++)
table[i] = sb.BuildColumnName(i);
return table;
}
private static string BuildColumnName(this StringBuilder sb, int index)
{
const int startLetter = 'A';
const int letterCount = 'Z' - startLetter + 1;
sb.Clear();
while (true)
{
var letter = (char)(startLetter + (index % letterCount));
sb.Insert(0, letter);
if (index < letterCount) break;
index = (index / letterCount) - 1;
}
return sb.ToString();
}
}
我还遇到了与OpenXml Sdk一起工作的无效文件的问题。看看OpenXml Power Tools;他们已经解决了我所有的问题:)此外，你应该切换到OpenXml Sdk 2.6，以避免System.IO.Packaging的问题。我希望这对你有帮助!