Hi,

The method Cells.copyCellRange does not manage to copy a cell containg an array formula.

We are using Aspose.CellsV2.1.0.39

Attached you will find template and result of testprogram below:
=================================================================
import java.io.FileInputStream;
import java.io.IOException;

import com.aspose.cells.AsposeLicenseException;
import com.aspose.cells.Cells;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;

public class ExpandingArrayFormula {

    public static void main(String[] args) throws IOException, AsposeLicenseException {

        License lic = new License();
        lic.setLicense(new FileInputStream("lib/Aspose.Cells.lic"));

        Workbook workbook = new Workbook();

        workbook.open("Excel/CopyCellRange.xls");

       
        Cells cells = workbook.getWorksheets().getSheet("Test").getCells();

        int splitRow = 16;

        // Labels

        int column = 5; // F column
        cells.insertRangeShiftDown(splitRow, column, splitRow, column);
       
        cells.copyCellRange(cells, splitRow-1, column, splitRow, column, 1, 1);       

        // Formulas

        column = 6; // G column
        cells.insertRangeShiftDown(splitRow, column, splitRow, column);
       
        // This is not working!!!
        cells.copyCellRange(cells, splitRow-1, column, splitRow, column, 1, 1);       

        workbook.calculateFormula();
        workbook.save("Excel/CopyCellRangeResult.xls");
    }

}

Hi,

 

Thank you for considering Aspose.

 

Well, we have tested your issue but we are unable to find the issue. Aspose.Cells works fine as MS Excel does. Please provide some more details about your issue and we will check it soon.

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Then I think I did a bad job explaining the issue - sorry.

 Attached you will find a file where I have done the steps manually:

Pay attention to F16:G18

The manually edited spreadsheet has:

Smith	16
Smith	16
Smith	16

The output from the test program has:

Smith	16
Smith	#REF!
Smith	16

It was not able to copy the array formula from the cell above.

If you still cannot identify the problem then I think you have another version than Aspose.CellsV2.1.0.39

Hi,

 

Thank you for considering Aspose.

 

Well, I tried your latest file with your sample code and it works fine with Aspose.Cells V2.1.0.39 (version can be check by using CellsHelper.getReleaseVersion()). You may find the attached resultant file. Which JDK version are you using? Also, please provide us details about your machine & OS etc.

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

I still have the problem:
Here is additional info about the environment:
=====================================
Aspose version : 2.1.0.39    (CellsHelper.getReleaseVersion())

System property : awt.toolkit = sun.awt.windows.WToolkit
System property : file.encoding = Cp1252
System property : file.encoding.pkg = sun.io
System property : file.separator = \
System property : java.awt.graphicsenv = sun.awt.Win32GraphicsEnvironment
System property : java.awt.printerjob = sun.awt.windows.WPrinterJob
System property : java.class.path = E:\projects\Aspose\bin;E:\projects\Aspose\lib\Aspose.Cells.jar
System property : java.class.version = 49.0
System property : java.endorsed.dirs = C:\jdk1.5.0_14\jre\lib\endorsed
System property : java.ext.dirs = C:\jdk1.5.0_14\jre\lib\ext
System property : java.home = C:\jdk1.5.0_14\jre
System property : java.io.tmpdir = C:\DOCUME~1\olberegi\LOCALS~1\Temp\
System property : java.library.path = C:\jdk1.5.0_14\jre\bin;.;C:\WINDOWS\system32;C:\WINDOWS;C:/Program Files/Java/jre6/bin/client;C:/Program Files/Java/jre6/bin;c:\windows\system32;c:\windows;c:\windows\system32\wbem;c:\perl\site\bin;c:\perl\bin;c:\oraclexe\app\oracle\product\10.2.0\server\bin;c:\safeguard\sgeasy;c:\programs\ultrae~1;c:\programs\cvsnt;c:\programs\ncftp;c:\program files\windows imaging\;c:\programs\tortoisehg;c:\programs\tortoisesvn\bin;
System property : java.runtime.name = Java(TM) 2 Runtime Environment, Standard Edition
System property : java.runtime.version = 1.5.0_14-b03
System property : java.specification.name = Java Platform API Specification
System property : java.specification.vendor = Sun Microsystems Inc.
System property : java.specification.version = 1.5
System property : java.vendor = Sun Microsystems Inc.
System property : java.vendor.url = http://java.sun.com/
System property : java.vendor.url.bug = http://java.sun.com/cgi-bin/bugreport.cgi
System property : java.version = 1.5.0_14
System property : java.vm.info = mixed mode
System property : java.vm.name = Java HotSpot(TM) Client VM
System property : java.vm.specification.name = Java Virtual Machine Specification
System property : java.vm.specification.vendor = Sun Microsystems Inc.
System property : java.vm.specification.version = 1.0
System property : java.vm.vendor = Sun Microsystems Inc.
System property : java.vm.version = 1.5.0_14-b03
System property : line.separator =

System property : os.arch = x86
System property : os.name = Windows XP
System property : os.version = 5.1
System property : path.separator = ;
System property : sun.arch.data.model = 32
System property : sun.boot.class.path = C:\jdk1.5.0_14\jre\lib\rt.jar;C:\jdk1.5.0_14\jre\lib\i18n.jar;C:\jdk1.5.0_14\jre\lib\sunrsasign.jar;C:\jdk1.5.0_14\jre\lib\jsse.jar;C:\jdk1.5.0_14\jre\lib\jce.jar;C:\jdk1.5.0_14\jre\lib\charsets.jar;C:\jdk1.5.0_14\jre\classes
System property : sun.boot.library.path = C:\jdk1.5.0_14\jre\bin
System property : sun.cpu.endian = little
System property : sun.cpu.isalist = pentium_pro+mmx pentium_pro pentium+mmx pentium i486 i386 i86
System property : sun.desktop = windows
System property : sun.io.unicode.encoding = UnicodeLittle
System property : sun.java.launcher = SUN_STANDARD
System property : sun.jnu.encoding = Cp1252
System property : sun.management.compiler = HotSpot Client Compiler
System property : sun.os.patch.level = Service Pack 2
System property : user.country = NO
System property : user.dir = E:\projects\Aspose
System property : user.home = C:\Documents and Settings\olberegi
System property : user.language = no
System property : user.name = olberegi
System property : user.timezone = Europe/Berlin
System property : user.variant =

I had a look at the excel file you attached.

There the cell G17 is containing:

#REF!

But I whould have expected it to contain the formula {=AVERAGE(IF($C$4:$C$12=H$4;D$4:D$12))}

Like it does if I do the operation manually

Hi,

 

Thank you for considering Aspose.

 

Well, I think you are using MS Excel 2007, as I got the “#REF” value for G17 when opening the file using MS Excel 2007 (works fine with MS EXCEL 2003). We will look into it and get back to you soon. Your issue has been registered in our issue tracking system with issue id CELLSJAVA-11871.

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

 

Thank you for considering Aspose.

 

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue/

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

Then it is resolved. Thank you

The issues you have found earlier (filed as 11871) have been fixed in this update.

---

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

This issue is back again for Aspose version : 2.2.0.0

Hi,

I have re-opened the issue, we will figure it out soon.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

Please try the attached version, we have fixed the issue of copying array formula.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us