I am trying to scan the excel header columns/cells and adding dynamic columns based on some conditions in between but when I loop through all the cells/columns within excel, it is only considering static data not dynamically added column so to be specific, in total count it considers newly added dynamic column and missing to scan actual column.
How can I loop through each column without need to specify static range/enumerator.
i.e. There is an excel with 10 numeric values and I want to dynamically add 1 more column after each 2nd column. so initially total cols = 10 but after dynamically adding it would be 15. So when i iterate through first 10 columns, as now more columns are there, it is missing some columns at last.
@informit123,
Thanks for providing us some details.
How do you iterate through worksheet cells? We need to check your sample code, may be it needs some tweak. Could you paste your sample code (runnable) and attach template file (if any), we will check it soon.
Moreover, if you are using formulas and data is added dynamically, you would need to call Workbook.CalculateFormula() method before iterating through cells in the worksheet to get the updated data.
Hi,
PFB Sample code. Please share solution as early as possible.
public void sampleCode()
{
Workbook workbk1 = new Workbook(sourceFile, options);
Worksheet worksht1 = workbk1.Worksheets[0];
worksht1.AutoFitColumns();
//Enumerator to traverse through all header columns
IEnumerator cellEnumerator = worksht1.Cells.Rows[0].GetEnumerator();
Cell currentCell = null;
int colNext = 1;
// Traverse cells in the collection
while (cellEnumerator.MoveNext()) //initially 100 columns are there
{
currentCell = cellEnumerator.Current as Aspose.Cells.Cell;
worksht1.Cells.InsertColumn(colNext + 1); //here columns are added dynamically
ApplyFormulaToNewCol(); //apply formula
workbk1.CalculateFormula(); //calculate
}
colNext += 1;
}
workbk1.Save(destFile, SaveFormat.Xlsx);
@informit123,
I think your code is not right, so you should debug your code by processing line by line (in the loop). Also, I am not sure about your template file and some of your method/objects in your code. Anyways, I have simply used the following code segment with a simple template file (attached). The template file has 10 columns only. I am simply adding a column after each column and add a sample formula to the newly inserted column. So, it would make 20 columns in total. The output file is included in the attached zipped archive.
e.g
Sample code:
Workbook workbk1 = new Workbook("e:\\test2\\Bk_issueiterating1.xlsx");//);
Worksheet worksht1 = workbk1.Worksheets[0];
worksht1.AutoFitColumns();
//Enumerator to traverse through all header columns
IEnumerator cellEnumerator = worksht1.Cells.Rows[0].GetEnumerator();
Cell currentCell = null;
int colNext = 1;
// Traverse cells in the collection
while (cellEnumerator.MoveNext()) //initially 10 columns are there
{
currentCell = cellEnumerator.Current as Aspose.Cells.Cell;
worksht1.Cells.InsertColumn(colNext); //here columns are added dynamically
//apply formula
worksht1.Cells[currentCell.Row, colNext].Formula = "=Rand()";
workbk1.CalculateFormula(); //calculate
colNext += 2;
}
workbk1.Save("e:\\test2\\out1.xlsx", SaveFormat.Xlsx);
If you still find any issue, kindly do create a simple console demo application, zip the project (excluding Aspose.Cells.Dll) and attach it here with template files, we will check it soon.
files1.zip (16.1 KB)
@informit123,
Furthermore, maybe you have misunderstood one thing. For the cells enumerator, it only iterates all existing cell objects in the row or sheet. When you use Cells.InsertColumn(), like the operation of inserting one column manually in MS Excel, the result is that all cells and columns behind the insert position will be shifted. There is no (and no need) new cell or column being created. So, you should not expect the count of elements in the got enumerator being increased. And one more thing, for performance consideration, the enumerator commonly only responses to the status of the cells model when the enumerator was created. While iterating elements, you should not do operations which may change the inner data of the cells model. The result of such kind of operation is not guaranteed and may be unexpected.
Hope, this helps a bit.