我正在从代码生成excel模板 . 当我运行这个部分来创建我的WorkBook时,我没有在代码中出错,但是当我打开Excel文档时,我收到一个错误,指示该文件是不可读的 . 无论如何我能够点击打开它,我收到以下消息
已删除的记录:/xl/workbook.xml部分(工作簿)中的工作表属性
知道我的代码中可能有什么问题吗?
public void CreatePackage()
{
using (SpreadsheetDocument package = SpreadsheetDocument.Create(FilePath, SpreadsheetDocumentType.Workbook))
{
CreateParts(package);
}
}
private void CreateParts(SpreadsheetDocument document)
{
ExcelWorkBook excelworkbook = new ExcelWorkBook();
ExcelSheetHelper excelworksheet = new ExcelSheetHelper();
ExcelSharedStringsTable excelsharedtable = new ExcelSharedStringsTable();
ExcelWorkSheetPartBuilder excelworksheetbuilder = new ExcelWorkSheetPartBuilder();
ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
ExcelWorkSheetPartBuilder.GenerateExtendedFilePropertiesPart1Content(extendedFilePropertiesPart1);
WorkbookPart workbookPart1 = document.AddWorkbookPart();
excelworkbook.GenerateWorkbookPartContent(workbookPart1);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId5");
ExcelWorkBook.GenerateWorkbookStylesPart1Content(workbookStylesPart1);
SetPackageProperties(document);
}
public void GenerateWorkbookPartContent(WorkbookPart workbookPart1)
{
Workbook workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", rNameSpace);
FileVersion fileVersion1 = GenerateFileVersion();
WorkbookProperties workbookProperties1 = GenerateWorkbookProperties();
BookViews bookViews1 = GenerateBookViews();
Sheets sheets1 = GenerateSheets();
DefinedNames definedNames1 = GenerateDefinedNames();
CalculationProperties calculationProperties1 = GenerateCalculationProperties();
CustomWorkbookViews customWorkbookViews1 = GenerateCustomWorkbookViews();
workbook.Append(fileVersion1);
workbook.Append(workbookProperties1);
workbook.Append(bookViews1);
workbook.Append(sheets1);
workbook.Append(definedNames1);
workbook.Append(calculationProperties1);
workbook.Append(customWorkbookViews1);
workbookPart1.Workbook = workbook;
}
// Creates an FileVersion instance and adds its children.
public FileVersion GenerateFileVersion()
{
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
return fileVersion1;
}
// Creates an WorkbookProperties instance and adds its children.
public WorkbookProperties GenerateWorkbookProperties()
{
WorkbookProperties workbookProperties1 = new WorkbookProperties() { HidePivotFieldList = true };
return workbookProperties1;
}
// Creates an BookViews instance and adds its children.
public BookViews GenerateBookViews()
{
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = -75, YWindow = 270, WindowWidth = (UInt32Value)15435U, WindowHeight = (UInt32Value)6930U };
bookViews1.Append(workbookView1);
return bookViews1;
}
// Creates an Sheets instance and adds its children.
public Sheets GenerateSheets()
{
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = String.Format("{0}", worksheetname), SheetId = (UInt32Value)8U, Id = "rId1" };
sheets1.Append(sheet1);
return sheets1;
}
// Creates an DefinedNames instance and adds its children.
public DefinedNames GenerateDefinedNames()
{
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName() { Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName1.Text = String.Format("\'{0}\'!$A$6:$EO$1269", worksheetname);
DefinedName definedName2 = new DefinedName() { Name = "Z_32BE30F1_B609_44A0_A38A_666CEFFB64E2_.wvu.Cols", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName2.Text = String.Format("\'{0}\'!#REF!", worksheetname);
DefinedName definedName3 = new DefinedName() { Name = "Z_32BE30F1_B609_44A0_A38A_666CEFFB64E2_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName3.Text = String.Format("\'{0}\'!#REF!", worksheetname);
DefinedName definedName4 = new DefinedName() { Name = "Z_5098B70B_692A_450A_8DAE_5172C296966E_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName4.Text = String.Format("\'{0}\'!#REF!", worksheetname);
DefinedName definedName5 = new DefinedName() { Name = "Z_7C00A233_927A_41FE_802C_48F5F9E9D5B6_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName5.Text = String.Format("\'{0}\'!#REF!",worksheetname);
DefinedName definedName6 = new DefinedName() { Name = "Z_AC112ED6_0017_40BF_884A_9B7959C37BF0_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName6.Text = String.Format("\'{0}\'!#REF!", worksheetname);
DefinedName definedName7 = new DefinedName() { Name = "Z_E444BF53_6DCE_4910_823C_F60AE88C96EE_.wvu.FilterData", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName7.Text = String.Format("\'{0}\'!#REF!",worksheetname);
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
definedNames1.Append(definedName4);
definedNames1.Append(definedName5);
definedNames1.Append(definedName6);
definedNames1.Append(definedName7);
return definedNames1;
}
// Creates an CalculationProperties instance and adds its children.
public CalculationProperties GenerateCalculationProperties()
{
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
return calculationProperties1;
}
// Creates an CustomWorkbookViews instance and adds its children.
public CustomWorkbookViews GenerateCustomWorkbookViews()
{
CustomWorkbookViews customWorkbookViews1 = new CustomWorkbookViews();
CustomWorkbookView customWorkbookView1 = new CustomWorkbookView() { Name = "A - Personal View", Guid = "{5098B70B-692A-450A-8DAE-5172C296966E}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1366U, WindowHeight = (UInt32Value)494U, ActiveSheetId = (UInt32Value)3U };
CustomWorkbookView customWorkbookView2 = new CustomWorkbookView() { Name = "B - Personal View", Guid = "{7C00A233-927A-41FE-802C-48F5F9E9D5B6}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1024U, WindowHeight = (UInt32Value)487U, ActiveSheetId = (UInt32Value)3U };
CustomWorkbookView customWorkbookView3 = new CustomWorkbookView() { Name = "C - Personal View", Guid = "{32BE30F1-B609-44A0-A38A-666CEFFB64E2}", MergeInterval = (UInt32Value)0U, PersonalView = true, Maximized = true, XWindow = 1, YWindow = 1, WindowWidth = (UInt32Value)1280U, WindowHeight = (UInt32Value)481U, ActiveSheetId = (UInt32Value)3U };
customWorkbookViews1.Append(customWorkbookView1);
customWorkbookViews1.Append(customWorkbookView2);
customWorkbookViews1.Append(customWorkbookView3);
return customWorkbookViews1;
}
public void ValidateDocument()
{
try
{
OpenXmlValidator validator = new OpenXmlValidator();
int count = 0;
IDictionary<String, String> ErrorLog = new Dictionary<String, String>();
using (StreamWriter f = new StreamWriter("Errolog.txt"))
{
foreach (ValidationErrorInfo error in validator.Validate(WordprocessingDocument.Open(FilePath, true)))
{
count++;
f.WriteLine("Error " + count);
f.WriteLine("Description: " + error.Description);
f.WriteLine("Path: " + error.Path.XPath);
f.WriteLine("Part: " + error.Part.Uri);
f.WriteLine("-------------------------------------------");
f.WriteLine("-------------------------------------------");
f.WriteLine("-------------------------------------------");
}
f.Flush();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
4 回答
在我的具体情况下,由于工作表名称太长,我遇到了这个问题 . 这就是发生在我身上的事情:
在我的程序的第一次运行中,我试图创建具有非常大名称的工作表 .
Excel自动将大名称裁剪为31个字符 . 没有例外被抛出 . 也就是说,我以为我正在保存原来的大牌,但我实际上是在保存31个字符的长裁剪字符串 .
在我的程序的第二次运行中,我正在检查这个特定的工作表是否已经存在,但我认为它不存在,因为现有的工作表被裁剪了 .
我再次保存工作表 . 没有异常抛出,但是,新的也被裁剪,现在XML包含同一工作表的2个定义 .
这会导致Excel尝试修复生成的电子表格 . 即使它工作正常,我认为它只是丢掉第二个并使用第一个,这不是我想要的 .
我通过在所有比较之前预先裁剪31个字符来解决问题 . 现在它完美无缺
我知道很久以前就问过这个问题,我认为我的经验会解决某些问题 . 所以我在这里发布我的答案 .
我遇到了类似的问题 . 这是因为没有 . 工作表名称中的字符超出限制31.工作表名称字符必须<= 31 .
创建时不会抛出任何异常,但在Microsoft Excel中打开时会出错 .
Open XML SDK不会限制您生成有效文档 . 但是,您可以使用
OpenXmlValidator
类来报告生成的文档中的任何错误 . 见this,这是一个很好的例子 .我也有这个错误,因为我使用
0
作为我的SheetId
,类似于:Excel从1开始计数,而不是0.这不仅适用于行号,也适用于工作表编号 .