I believe i have found a bug with the Cells.CopyColumn method when it comes to copying cells containing formulae. If i have a simple worksheet containing the following:
| A | B | C |
-----------------------------
1 | 1 | | |
2 | =A1 | | |
3 | | | |
and if in Excel i copy column A and paste it into column B, i get
| A | B | C |
-----------------------------
1 | 1 | 1 | |
2 | =A1 | =B1 | |
3 | | | |
But, if i use aspose and use the worksheet.Cells.CopyColumn method i get:
| A | B | C |
-----------------------------
1 | 1 | 1 | |
2 | =A1 | =A1 | |
3 | | | |
For some reason aspose doesn’t update the copied formulae. Is there another method i should be using for this or can you supply a fix?
I’m using version 3.7.2.7.
paul
Hi Paul,
I don't find this problem.
Please check my sample code:
Aspose.Cells.License license = new Aspose.Cells.License();
//set License
license.SetLicense("d:\\lic\\Aspose.Custom.5744.lic");
Excel excel = new Excel();
excel.Open("d:\\test\\book1.xls");
Cells source = excel.Worksheets[0].Cells;
Cells dest = excel.Worksheets[0].Cells;
dest.CopyColumn(source, 0, 1);
excel.Save("d:\\test\\abc.xls");
Attached is output file.
This is the latest version. But I don’t make any change on CopyColumn method.
Your example works fine as it appears it’s the Save method that causes this formula to be correctly derived. But, if you read the data back out before you save as in the following example, it fails:
using System;
using System.Data;
using Aspose.Cells;
namespace ConsoleApplication1
{
///
/// Summary description for Class1.
///
class Class1
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main(string[] args)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“C:\Aspose.lic”);
Excel excel = new Excel();
excel.Open(“C:\basic.xls”);
Cells source = excel.Worksheets[0].Cells;
Cells dest = excel.Worksheets[0].Cells;
dest.CopyColumn(source, 0, 1);
excel.CalculateFormula(true);
string formula1 = excel.Worksheets[0].Cells[1,1].Formula; // this line returns ‘=a1’
excel.Save(“c:\output.xls”);
excel.Open(“c:\output.xls”);
string formula2 = excel.Worksheets[0].Cells[1,1].Formula; // this line returns ‘=b1’
Console.WriteLine(string.Format("{0} - {1}", formula1, formula2));
}
}
}
paul
Thank you, paul. That does the trick.
Please try this attached fix. It's solved.
Cheers Laurence,
that worked.
paul
Laurence,
I am having this issue too, but with version 4.3.0.14.
I'm attempting a simple loop to do the following:
cells = sheet.Cells;
col = cells.FindString("CY", null, ca).Column + 1;
for (int i = 1; i < maxYear - minYear; i++)
{
cells.InsertColumn(col + 1);
cells.CopyColumn(cells, col, col + 1);
}
The formula in the first column contains a function that references a range of cells:
=COUNTIF('Source Data'!AG:AG, ">3")
on another sheet within the workbook. When the column is copied, the reference to AG:AG is not updated.
I would appreciate your assistance.
Thanks,
Pam
Hi Pam,
Since you are using the older version of Aspose.Cells, Could you try the attached version. If the problem still persists, please post your template file.
Thank you.
Amjad,
The dll you gave me results in a run-time error when attempting to "CopyColumn" on the columns I'm trying to copy. It doesn't fail on all CopyColumn operations--only on the one that is not updating the cell references appropriately.
I located a more recent Dll (4.4.0.20) attached to a forum entry regarding a bug in the COUNTIF function. This version does not crash, however, it does not correctly recalculate formula references during the CopyColumn either.
I've done some more additional testing and have determined that the problem does not exist when the cell being copied contains just a single cell reference (e.g., "=B1"). In this instance, copying the column (or cell) one column to the right will produce a "=C1" in the new cell. However, if the cell contains a formula such as SUM(A1:A3), after being copied, the new cell DOES NOT contain SUM(B1:B3). I'm trying to copy a column containing the COUNTIF formula and it too does not update cell references appropriately.
I'd appreciate your assistance with this as I have a production system that is affected by this problem.
Thanks,
Pam
Hi Pam,
Please try this fix.
If you still get error ,please post your tempalte file and sample codes.
Hi Pam,
Please ignore the previous post and try the attached fix in this post.
After testing, the fix in the previous post will cause the error of inserting column.
The version most recently provided works if I define a specific cell-range in the COUNTIF function. For example, if the formula below is copied using the copycolumn functionality, the cell references are correctly updated.
COUNTIF('Source Data'!AA1:AA200,"<1")
However, if I use a column reference in the COUNTIF function:
COUNTIF('Source Data'!AA:AA, "<1")
then the most recent dll experiences a runtime error--null reference. Earlier versions of the dll did not cause a runtime error, but the cell references were not updated correctly.
It is desireable that I be able to use a column reference (like the second example) so that I do not have to manually update the cell range specifically for the quantity of source data rows. Woudl it be possible to get this fixed?
Thanks,
Pam
I have painstakenly spent time troubleshooting a follow-on bug that has cropped up since I started using your previous attachment. Now, each time I open a spreadsheet directly from the browser,I receive a "File error: data may have been lost" message. This message does not occur if I save the file to disk and then attempt to open it.
I've tracked down the issue to a single Aspose API call: CopyColumn. Unfortunately, this is the API that I've been having trouble with all along.
Can you please take a look? Also, if you're going to have to crack opent the code again, could you please address the issue that I mentioned in my last post?
Thanks much,
Pam
Hi Pam,
Please try this fix. If the error still occures, please post your template file and codes.
The new attachment does not cuase the "File error", however, there are numerous issues with the dll that make it totally unusable.
I will attach a few things for you...template spreadsheet, spreadsheet after inserting data using the most recent dll, spreadsheet after inserting data using the dll provided on 12/19/2007 (the one that results in the "File error"), and the code snippets that I use to add data and manipulate rows and columns.
The errors that are immediately obvious to me are:
- On the "Source Data" tab, CopyRow does not preserve cell formatting (columns M through Y and AE through AS)
- On the "Source Data" tab, Copy Row does not update cell references (columns AE through AS)
- On the "Business Health" tab, CopyColumn on =COUNTIF('Source Data'!AK2:AK5000,">3") formula only updates the second part of the range--resulting formula after CopyColumn is =COUNTIF('Source Data'!AK2:AL5000,">3")
Thanks for your attention in this matter.
Pam
Hi Pam,
Thanks for the template files and code, we will check it soon. And could you provide RptUtils class, we want to check what you have written in it.
Thank you.
RptUtils simply contains numerous SQL queries to the project database to load the string arrays that are used to export data to Excel. The data extracted from the SQL db can be seen in the "source Data" tab of the spreadsheet that I sent you. This class does not reference Aspose at all.
Hi,
Please try this fix.
The spreadsheet does not post the "File Error" message, but there are still issues with the "CopyRow" and "CopyColumn" functionality. When I perform a CopyRow on the SourceData sheet to add the correct number of data rows, the formulas contained within cells AD through AL are not copied with correctly updated references. When I perform the CopyColumn on column AK (I perform this copy functionality several times to add columns representing "years"), the formula located in column AK are not copied with updated references.
Thanks,
Pam
Hi Pam,
We will further check and figure out your issue soon.
Thank you.