After continued testing, the "File error" returns if I perform CopyRow or CopyColumn with the "Update References" parameter set to "true".
Thanks,
Pam
Hi Pam,
I tried copy row and column with the following codes and your template file. I could not find the error.Could you post your created file?
Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\NPIMetricsTemplate.xls");
Cells cells = workbook.Worksheets[0].Cells;
//cells.CopyColumn(cells, 2, 3);
int insertRows = 3;
cells.InsertRows(2, insertRows,true);
for (int i = 0; i < insertRows; i++)
{
cells.CopyRow(cells, 1, i + 2);
}
cells.InsertColumn(CellsHelper.ColumnNameToIndex("AK") + 1, true);
cells.CopyColumn(cells, CellsHelper.ColumnNameToIndex("AK"), CellsHelper.ColumnNameToIndex("AK") + 2);
workbook.Save(@"F:\FileTemp\dest.xls");
Warren,
I tried the code you provided and found the following:
- CopyRow still does not update formula references correctly.
- CopyColumn works for the instance you have coded (inserting and copying one column). However, I have found that I can add multiple columns to one tab of the spreadsheet successfully. But when I try to perform the same operation to another tab in my spreadsheet, only the first attempt suceeds.
Given that my results are still not comparable to yours, I'm attempting to proceduralize some of my Aspose code so that I can demonstrate easily what I am seeing. This will take a bit of time as I am still trying to meet a production deadline for creation of new charts even though I am having difficulty with the recent versions that you've provided to me.
Just as general information, the dll that you provided to me on Dec 19th still behaves the best overall. It only demonstrates the "File error" problem when the spreadsheet is posted to the user from the website. I am seeing all of the CopyRow and CopyColumn issues ONLY in the newer versions that you've given me.
I hope to have some more feedback (additional code and templates) to you tomorrow sometime.
Thanks,
Pam
Warren,
I've attached a very small example of how the Dec 19th dll and the January 7th dll behave differently. The code in my example is pared down from my production code, but still maintains the basic order in which Aspose APIs are exercised.In this example, I do the follwing:
- Load a string array with 10 rows of data (simulating my data read from SQL Server)
- Open the excel template file
- Insert 8 new rows (the template already has two blank rows included) and do a CopyRow from the first template data row to the new rows so that formulas and formatting can be replicated.
- Insert two "year" columns (there are columns in the template for StartYear and MaxYear, so if I want four years of data, I need only add two more columns) and perform a CopyColumn on the first "Year" column to the two new ones.
When I use the Dec19 dll, the rows are copied correctly and the columns are copied correctly. The only error that occurs is the "File error" message box that show up when I post the file from the website to the user.
When I use the Jan 7 dll, rows are copied without updating cell references, columns are copied without copying cell reference, butI do not receive the "File error" message.
Please note: when running the two examples, the only thing I changed was the Aspose dll.
I also have noticed that while using the Jan 7 dll, alternately debugging and making modifications to my template file, there are occasions where I cannot access the template file even though my application is not running and excel is not running. Looking at Task Manager, the aspnet_wp process is still holding on to quite a bit of memory. This leads me to believe that the excel template has not been properly closed--perhaps an exception that is not handled correctly?
I've included code, copies of both spreadsheet outputs, the template file, and copies of both of the dlls that I'm currently experimenting with. Please recall that the Dec 19th dll works best, but the Jan7 dll is the one that you've most recently given me for the purposes of evaluating fixes to the File Error problem.
I am anxiously awaiting your response as I have a deadline I'm trying to meet... :)
Thanks for your attention in this matter,
Pam
Hi Pam,
Thanks for your simple project. Please try this fix.And I change your LoadReportArray method.Your method will replace the formula of inserted cells with a number value
public void LoadReportArray()
{
YEAR_DATA_END = YEAR_DATA_START + (maxYear - startYear + 1) + 1;
FORMULA_START = YEAR_DATA_END + 1;
FORMULA_END = FORMULA_START + 1;
COLUMN_COUNT = FORMULA_END + 1;
rptArray = new string[10][];
string[] sRow;
for (int j = 0; j < 10; j++)
{
sRow = new string[COLUMN_COUNT];
rptArray[j] = sRow;
sRow[VALUE1] = Convert.ToString((j + 1) * 10000);
//for (int i = 0; i <= maxYear - startYear; i++)
//{
// sRow[YEAR_DATA_START + i] = Convert.ToString(i + startYear);
//}
}
}
Warren,
Much better! Rows are added correctly and I'm seeing fewer issues with cell references being updated! THANKS!
But...After running the new dll against my production code, I still see one error with cell references. Cell references in tabs other than the one I'm inserting columns into do not appear to be updated. I've tested this in the small sample I sent you and the problem can be easily replicated.
I've attached a new TestTemplate.xls...the same content as the one I sent last week, with two small changes. On Sheet 2, I've entered a formula in cell A1:
=SUM(Sheet1!B3:C3)
I've also entered the same formula on Sheet 1 in cell A8.
After running the test code and inserting two columns, the resulting formula on Sheet 1 is
=SUM(Sheet1!B3:E3)
The resulting formula on Sheet 2 is
=SUM(Sheet1!B3:C3) Cell references NOT updated appropriately
Can you please take a look at this? I have numerous charts and associated formulas on other tabs within my production template that rely on their cell references being updated appropriately too.
Thanks!
Pam
Warren,
I hate to pester you, but I was hoping to hear from you regarding the remaining issue I have with the most recent dll that you provided (please see my previous post). I have a deliverable due on Thursday. The date is immovable, so if I don't hear from you, I will have to go into production with the Dec 19 dll that still exhibits the "File error" issue.
I'd appreciate any time you could spare to look at the remaining cell reference issue described in my previous post.
Thanks very much,
Pam
Hi Pam,
Please try this fix.
I think it's caused by that we try to auto-detect the FileFomatType of the in Open method.
Warren,
It look GREAT! I really appreciate your dedication to helping me meet my deadline!
Pam