Data inserted by OLAP datasource dataquery in database sheet. and used in main sheet as name range won’t appear as proper design
Aspose.docx (171.1 KB)
Use of
CalculateFormula()
SetSharedFormula(…)
CreateCellArea(…) and InsertRange(…)
is not working in this case. Attached here code and explanatory document for more details.
@manikya.rao
By using sample files and code for testing, we can reproduce the issue. Found data shift failed after calling InsertRange method.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-55122
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
The formula in the template file is neither shared formula nor common(legacy) array formula, but is dynamic array formula. For such kind of formula, please use SetDynamicArrayFormula methods to set it. And after changing the cells data, such as import new data, or insert/delete rows/columns/ranges, please use RefreshDynamicArrayFormulas to refresh/spill the formula to proper cell range according to the new data.
Currently there is a bug for insert/delete operations when the influenced range intersects with the range of the dynamic array formula(we take it as an invalid operation and give “Cannot change part of an array” exception). By our test such kind of operations is valid in ms excel. From next version, we will fix this bug to allow such kind of operations.
@manikya.rao
We have fixed the issue mentioned in previous post now. With the fix and the updated code(using Cell.SetDynamicArrayFormula() instead of Cell.SetSharedFormula() and Cell.Formula):
Workbook workbook = new Workbook(pathCase);
WorksheetCollection worksheets = workbook.Worksheets;
// Autofit range in excel
workbook.CalculateFormula();
worksheets[0].AutoFitRows();
worksheets[0].AutoFitColumns();
workbook.Save("SavedDataQueryExcel_1.xlsx");
// calculate using re-adding formula set shared
foreach (Worksheet worksheet in worksheets)
{
if (worksheet.Cells.FirstCell.Formula == "=CDMQRTM1DataQuery_1")
{
//worksheet.Cells[worksheet.Cells.FirstCell.Name].SetSharedFormula(worksheet.Cells.FirstCell.Formula, 1, 1);
worksheet.Cells[worksheet.Cells.FirstCell.Name].SetDynamicArrayFormula(worksheet.Cells.FirstCell.Formula,
new FormulaParseOptions(), true);
workbook.CalculateFormula();
}
}
worksheets[0].AutoFitRows();
worksheets[0].AutoFitColumns();
workbook.Save("SavedDataQueryExcel_2.xlsx");
//Add range in another sheet
worksheets.Add();
int index2 = worksheets.Names.Add("CDMQRTM1DataQuery_2");
Name name2 = worksheets.Names[index2];
name2.RefersTo = "=Database!$B$2";
//worksheets[2].Cells["A1"].Formula = "CDMQRTM1DataQuery_2";
worksheets[2].Cells["A1"].SetDynamicArrayFormula("CDMQRTM1DataQuery_2", new FormulaParseOptions(), true);
workbook.CalculateFormula();
int index3 = worksheets.Names.Add("CDMQRTM1DataQuery_3");
Name name3 = worksheets.Names[index3];
name3.RefersTo = "=Database!$A$2:$F$7";
//worksheets[2].Cells["A5"].Formula = "CDMQRTM1DataQuery_3";
worksheets[2].Cells["A5"].SetDynamicArrayFormula("CDMQRTM1DataQuery_3", new FormulaParseOptions(), true);
workbook.CalculateFormula();
worksheets[2].AutoFitRows();
worksheets[2].AutoFitColumns();
workbook.Save("SavedDataQueryExcel_3.xlsx");
// re-insert and create cell area
foreach (Worksheet worksheet in worksheets)
{
if (worksheet.Cells.FirstCell.Formula == "=CDMQRTM1DataQuery_1")
{
CellArea cellArea = CellArea.CreateCellArea("Database!A2", "Database!F6");
worksheet.Cells.InsertRange(cellArea, ShiftType.Down);
}
}
worksheets[0].AutoFitRows();
worksheets[0].AutoFitColumns();
workbook.Save("SavedDataQueryExcel_4.xlsx");
we can get the generated files in the attachment. Please check whether they are the expected results for you.
SavedResults.zip (29.8 KB)
The issues you have found earlier (filed as CELLSNET-55122) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi