Calculate Formula Completion Notification

Aspose.Cells Product Family
1

Hi,

I have a Workbook which has 5 Sheets.
I am importing CSVs in Sheet1, Sheet2 and Sheet3. Sheet4 has some static data.
Sheet 5 has some formulas and lookups which are based on the data in the previous 4 Sheets.
I am using calculateFormula() method to calculate all the formulas and then I save the Sheet5 with a new name as a CSV file.
I have used following logic to get the expected functionality:

Workbook wb = new Workbook(“D:\Excel1.xlsx”);
// Logic to import all the CSVs
wb.calculateFormula();
Worksheet sheet = wb.getWorksheets().get(“Sheet5”);
wb.getWorksheets().setActiveSheetIndex(worksheet.getIndex());
wb.save(sworkingfolder + “Sheet5_new.csv”);

The issue I am facing is that in my CSV file, some cells having formulas are showing the proper values whereas some cells having formulas are showing #N/A.

Just for the verification if formulas are correct, I tried putting the data manually and all formulas were working.

Is there any solution which checks whether all the formulas in the workbook are calculated. So I can save the Sheet5_new.csv only after calculations is done.

Thank You.

2

@Louis.Coutinho,

Thanks for your query.

Please send your sample Excel file, the output CSV file, expected CSV file and identification of some cells which are showing wrong data. We will update you after investigating it on our end.

3

Hi,

I have sent you the files. I have also sent you the expected output files. So just wanted to check if it is possible to achieve the expected output??

Thank You.

4

@Louis.Coutinho,

I have not received any data. Please attach it here with your post or you may share the data in a private message. You can click on my name and find “Message” Button, in order to send a private message.

5

@Louis.Coutinho,

Your sample files and data is received. Your query is replied in the private message.

6

Hi,

Thank you for your solution. It is working fine with the latest version.

I am also facing other issue while Importing a CSV file in a Worksheet. I am using following code to import a CSV file:

worksheet.getCells().importCSV(csvfilename, csvFieldSeperator, true, 0, 0);

I have a column having following data :
2018-09-30T09:00

While importing the CSV the data gets converted to 2018-09-30 09:00:00.
Instead of getting converted, I want it to be displayed as text only.

I tried following :

worksheet.getCells().importCSV(csvfilename, csvFieldSeperator, false, 0, 0);

Somehow all the columns are imported as Text.

Is there any way to convert some columns to text and keep some columns in the format they are.

Thank You.

Regards

7

@Louis.Coutinho,

I have tested the issue using latest version Aspose.Cells for Java 18.8, but could not observe any issue. The date is not converted as mentioned by you and displayed similar to text in the source CSV. Please give it a try using latest version and provide your feedback.

Workbook wb = new Workbook();
wb.getWorksheets().get(0).getCells().importCSV("SampleByExcel.csv", ",", false,0,0);
wb.save("OutputJava.xlsx");

SampleByExcel.zip (172 Bytes)
OutputJava.zip (5.5 KB)

8

Hi,

I tested the above files. They are working fine and the date is not converted. It is seen as text only.

But the issue I am facing is:

  1. I want date column to be in text format - It worked with the above solution
  2. If there is a number column, the above solution also converts number to text. I want it to be in number format.

Please check the samples provided.

How to achieve the following scenario:
Column A in Number format but Column B in Text Format.

SampleByExcel.zip (174 Bytes)
OutputJava.zip (5.6 KB)

Thank You.

Regards

9

@Louis.Coutinho,

I have tried to import the CSV file while setting convertNumericData to true, it displays the number column as number but some problem appears with Date column. So, we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42701 - Problem while importing a CSV file
10

@Louis.Coutinho,

Please apply below changes to your code snippet and then let us know your feedback.

    TxtLoadOptions opt = new TxtLoadOptions();
    opt.setPreferredParsers(new ICustomParser[]{null, new ICustomParser(){
        private final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm");
        private boolean parsed = false;
        public Object parseObject(String str)
        {
            parsed = true;
            try
            {
                return sdf.parse(str);
            }
            catch(Exception e)
            {
                parsed = false;
                return null;
            }
        }
        public String getFormat()
        {
            return parsed ? "yyyy-mm-dd\\Thh:mm" : null;
        }
    }});
    cells.importCSV("SampleByExcel.csv", opt, 0, 0);
    ...