C# Excel 范围.公式数组不起作用
本文关键字:数组 不起作用 Excel 范围 | 更新日期: 2023-09-27 18:25:54
这是我的C#代码,用于将ArrayFormula写入excel中的某个单元格。
我正在使用 UT(统一功能测试(,它使用 C# 进行自定义代码。
String sheetName = "xyz";
String wsMethodName = "abc";
int i = 2;
Excel.Application xlApp = null;
xlApp = new Excel.ApplicationClass();
wb = xlApp.Workbooks.Open(srcFile,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
worksheet = (Excel.Worksheet)wb.Worksheets[sheetName];
Excel.Range excelCell = (Excel.Range)worksheet.get_Range("B2", "B21");
foreach (Excel.Range c in excelCell)
{
//
strAvgFormula =
"=AVERAGEIFS(" +
"(OFFSET(''" + sheetName + "''!$A$1,2,2,COUNTA(''" + sheetName + "''!$A:$A)-2,1))," +
"OFFSET(''" + sheetName + "''!$A$1,2,16382,COUNTA(''" + sheetName + "''!$A:$A)-2,1)," +
"(MID(C" + i + ",1,(FIND('"-'",C" + i + "))-2))," +
"OFFSET(''" + sheetName + "''!$A$1,2,16383,COUNTA(''" + sheetName + "''!$A:$A)-2,1)," +
"(MID(C" + i + ",(FIND('"-'",C" + i + ")+1),(FIND('"/'",C" + i + "))-(FIND('"-'",C" + i + ")+1))))";
this.CodeActivity16.Report("strAvgFormula",strAvgFormula);
//
strMaxFormula =
"=MAX(" +
"IF((OFFSET(''" + sheetName + "''!$A$1,2,16382,COUNTA(''" + sheetName + "''!$A:$A)-2,1)=MID(C" + i + ",1,(FIND('"-'",C" + i + "))-2))*" +
"(OFFSET(''" + sheetName + "''!$A$1,2,16383,COUNTA(''" + sheetName + "''!$A:$A)-2,1)=MID(C" + i + ",(FIND('"-'",C" + i + ")+2)," +
"(FIND('"/'",C" + i + "))-(FIND('"-'",C" + i + ")+2)))," +
"OFFSET(''" + sheetName + "''!$A$1,2,2,COUNTA(''" + sheetName + "''!$A:$A)-2,1)))";
this.CodeActivity16.Report("strMaxFormula",strMaxFormula);
if (c.Value2.ToString() == wsMethodName)
{
newExcelCell = (Excel.Range)worksheet.get_Range("F" + i, "F" + i);
newExcelCell.Clear();
newExcelCell.FormulaArray = strAvgFormula; //Failing @ this line, error is mentioned below
//newExcelCell.Value = strAvgFormula;
newExcelCell = (Excel.Range)worksheet.get_Range("G" + i, "G" + i);
newExcelCell.Clear();
newExcelCell.FormulaArray = strMaxFormula;
//newExcelCell.Value = strMaxFormula;
break;
}
i ++;
}
wb.Save();
xlApp.Workbooks.Close();
xlApp.Quit();
releaseObject(newExcelCell);
releaseObject(excelCell);
releaseObject(worksheet);
releaseObject(wb);
releaseObject(xlApp);
private void releaseObject(object obj)
{
try
{
Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
CodeActivity16.Report("Error","Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
现在,如果我从打印的输出结果中复制相同的公式并将其粘贴到我想要的单元格中,它就可以正常工作。
转义角色正在正确地完成他们的工作。
如果我将newExcelCell.FormulaArray更改为newExcelCell.Value,那么它正在写入excel,但它可以作为普通公式而不是ArrayFormula工作(就像我们所做的Ctrl + Shift + Enter一样(。
这是我从结果文件中收到的错误:
您键入的公式包含错误。请尝试以下操作之一:
• 确保已包含所有括号和必需参数。
• 若要获取有关使用函数的帮助,请单击"公式"选项卡上的"函数向导"(在"函数库"组中(。
• 如果包含对其他工作表或工作簿的引用,请验证该引用是否正确。
• 如果不尝试输入公式,请避免使用等号 (=( 或减号 (-(,或在公式前面加上单引号 ( ' (。
• 有关常见公式问题的详细信息,请单击"帮助"。
提前感谢您的任何帮助或建议。
更新:
这是我正在尝试编写的strAvgFormula公式。
"=AVERAGEIFS(" +
"(OFFSET('1'!$A$1,2,2,COUNTA('1'!$A:$A)-2,1))," +
"OFFSET('1'!$A$1,2,16382,COUNTA('1'!$A:$A)-2,1),(MID(C2,1,(FIND("-",C2))-2))," +
"OFFSET('1'!$A$1,2,16383,COUNTA('1'!$A:$A)-2,1),(MID(C2,(FIND("-",C2)+1),(FIND("/",C2))-(FIND("-",C2)+1))))"
strMaxFormula的公式工作正常。
属性的字符限制也为 255。
解决方法是将其设置为少于 256 个字符,并替换其中的一部分。例如:
range.FormulaArray = "={1,2,3}";
range.Replace("}", ",4,5,6}", XlLookAt.xlPart);
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/
http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/
所以,最后我得到了这个问题的解决方法。
事实证明(这就是我的感觉(,要么 Excel 不接受字符串 strAvgFormula 中的公式,要么由于使用 Offset 的单元格引用而搞砸了。
我必须使用直接单元格引用。
这是我的工作公式:
int lastUsedRowDiffSheet = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;
strAvgFormula =
"=AVERAGEIFS('" + sheetName + "''!C" + iRowCount + ":C" + lastUsedRowDiffSheet + ","
+ "'" + sheetName + "'!XFC" + iRowCount + ":XFC" + lastUsedRowDiffSheet + ","
+ "MID(C" + i + ",1,(FIND('"-'",C" + i + "))-2),"
+ "'" + sheetName + "''!XFD" + iRowCount + ":XFD" + lastUsedRowDiffSheet + ","
+ "(MID(C" + i + ",(FIND('"-'",C" + i + ")+1),(FIND('"/'",C" + i + "))-(FIND('"-'",C" + i + ")+1))))";