gpt4 book ai didi

list - 如何在c#中使用OpenXml在excel文件的列/列中设置数据验证列表?

转载 作者:行者123 更新时间:2023-12-03 17:48:57 45 4
gpt4 key购买 nike

我需要使用特定列表作为使用 openXml 的 excel 文件的列/列中的源创建下拉列表。

为此,我使用以下代码,

     SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open("C:\\Users\\Harun.TV\\Desktop\\OpenXml\\1.xlsx",true);

WorkbookPart workbookpart = spreadSheetDocument.WorkbookPart;
Workbook workbook=workbookpart.Workbook;


WorksheetPart worksheetPart=workbookpart.WorksheetParts.First();


DataValidations dataValidations1 = new DataValidations();
DataValidation dataValidation2 = new DataValidation() { Formula1 = new Formula1("'mySheet'!$A$1:$A$4"), Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A4:B4" } };
Formula1 formula12 = new Formula1();
formula12.Text = "$A$1:$A$3";
dataValidations1.Append(dataValidation2);
worksheetPart.Worksheet.Append(dataValidations1);

workbookpart.Workbook.Save();


spreadSheetDocument.Close();

并且在打开excel时抛出错误。日志如下,
      <?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <logFileName>error055840_01.xml</logFileName>
<summary>Errors were detected in file 'C:\Users\Harun.TV\Desktop\OpenXml\6.xlsx'</summary>
-<removedParts summary="Following is a list of removed parts:">
<removedPart>Replaced Part: /xl/worksheets/sheet3.xml part with XML error. Load error. Line 1, column 467.</removedPart></removedParts></recoveryLog>

另外,如何将逗号分隔值列表分配给 DataValidations 并获得所需的结果,因为我们手动为 excel 列执行此操作?

最佳答案

问题可能是已经有一个“DataValidatoins”节点。
这对我有用:

        DataValidation dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
Formula1 = new Formula1("'SheetName'!$A$1:$A$3")
};

DataValidations dvs = worksheet.GetFirstChild<DataValidations>(); //worksheet type => Worksheet
if (dvs != null)
{
dvs.Count = dvs.Count + 1;
dvs.Append(dataValidation);
}
else
{
DataValidations newDVs = new DataValidations();
newDVs.Append(dataValidation);
newDVs.Count = 1;
worksheet.Append(newDVs);
}

关于list - 如何在c#中使用OpenXml在excel文件的列/列中设置数据验证列表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22808284/

45 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com