In Excel, you can write a formula like this =MEDIAN(IF(‘Data’!B2:B20=A2, ‘Data’!C2:C20))
and do a Shirt+Ctrl+Enter and it will make it an array but putting {} around the formla. Can this be done in Aspose.Cells? or an equivalent to it?
Additional information. The formula is stored in database and writing to Excel.
I’m familiar with the cell.formula = someformula and I also saw the cell.setarrayformula(someformula, row, col). Now, I only want to write the array formula in the first cell and then use the sharedformula to copy them across a range. The function I’ve created is generic and doesn’t know if the formula is an array or not. If I write the formula above using the cell.formula, can I use the cell.isarray to see if it’s an array formula? or do I need to enclose them in {} when I assign the cell.formula?
Hi,
When I tried that, it copied the same formula across all the cell. I’m trying to use the sharedformula with the array.
Hi,
private static void WriteFormula(Workbook ExcelWK, string wsName, string Startcell, string Endcell, string ExcelFormula, bool isArray)
{
if (ExcelWK != null)
{
try
{
Worksheet ws = ExcelWK.Worksheets[wsName];
if (ws != null)
{
int startrow = 0;
int startcol = 0;
CellsHelper.CellNameToIndex(Startcell, out startrow, out startcol);
Cell cell = ws.Cells[Startcell];
cell.Formula = ExcelFormula;
if(isArray)
cell.SetArrayFormula(ExcelFormula, 1, 1);
int row = 0;
int col = 0;
CellsHelper.CellNameToIndex(Endcell, out row, out col);
int totalrows = 0;
int totalcols = 0;
if (startcol == col)
totalcols = 1;
else
totalcols = col - startcol + 1;
totalrows = row - startrow + 1;
ws.Cells[cell.Name].SetSharedFormula(cell.Formula, totalrows, totalcols);
ExcelWK.CalculateFormula();
}
}
When I do the above, the formula that gets copy across the range isn’t the array version and just the plan standard formula. I think if there’s an overload to specify the cell object, instead of the formula to be copy across the cell, then it should keep the actual formula whether it’s standard or array.
Hi,
I did and it didn’t update the reference in the formula like the sharedformula would have done.
Hi,
Here’s a sample of the chart.
Hi,
Sample code:
Workbook excelWorkbook = new Workbook(“e:\test2\Median_CT.xlsx”);
excelWorkbook.Worksheets[1].Cells[“B16”].SetArrayFormula("=MEDIAN(IF(Sheet1!$A:$A = $A16, Sheet1!$C:$C))", 20, 1);
excelWorkbook.Save(“e:\test2\out1.xlsx”);
It sets constant “=MEDIAN(IF(Sheet1!$A:$A=$A16,Sheet1!$C:$C)” array formula to all the cells in the range.
Hi,
We have evaluated your issue further.
-
Well, MS Excel will pop-up a message that “Mutli-Cell array formulas are not allowed in the tables.” if you set the array formulas to the range of cells. We will look into whether Aspose.Cells should throw such exception too when setting the array formulas to a range of cells in the tables.
-
The reference in the array formulas must be fixed to figure it out. In your template file, each cell in the range e.g. B2:B15 contains different array formula in your template file.
If you want to get the same results as MS Excel, please try the following codes:
e.g.
Sample code:
Workbook excelWorkbook = new Workbook("e:\\test2\\Median_CT.xlsx");
excelWorkbook.Worksheets[1].Cells["B16"].SetArrayFormula("=MEDIAN(IF(Sheet1!$A:$A = $A16, Sheet1!$C:$C))", 1, 1);
for (int i = 1; i < 20; i++)
{
excelWorkbook.Worksheets[1].Cells[15 + i, 1].Copy(excelWorkbook.Worksheets[1].Cells["B16"]);
}
excelWorkbook.Save("e:\\test2\\out1.xlsx");
I have tested using Copying cells, it works fine here.
Thank you.
The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan