Formula not working

Question

Hello,

I am currently using syncfusion_flutter_xlsio/xlsio.dart for creating Excell files.  Everything is going well, except...

It looks like the cells are not "validated".. Let me explain:
I put a formula ( C16 ) which calculates the sum ( =SUM(C2:C15)), this one displays 0.When I click on a cell (example C2), and I do "Enter", it is now taken into account..
(see image)for information: - sheet.enableSheetCalculations();- contentStyle.numberFormat = 'hh:mm';  Do you have an idea, please?
thank you in advance :)

huart · Answer

here are the parts of codes that concern the column

Ramya Sivakumar · Answer

Hi Huart,Greetings from
Syncfusion.Currently, while importing the list, formula calculation is not
supported. So, kindly use the following code snippet before saving the workbook
to get the proper values for the Formulas.Code Snippet:

int firstRow = sheet.getFirstRow();
  int firstCol = sheet.getFirstColumn();
  int lastRow = sheet.getLastRow();
  int lastCol = sheet.getLastColumn();
   
  for (int row = firstRow; row <= lastRow; row++) {
       for (int col = firstCol; col
  <= lastCol; col++) {
            if
  (sheet.getRangeByIndex(row, col).formula != null) {
                
  sheet.getRangeByIndex(row, col).calculatedValue;
            }
       }
  }
   
  final List<int> bytes = workbook.saveAsStream();
  saveAsExcel(bytes, 'Sample.xlsx');
  workbook.dispose();

kindly try this and let us know if the solution helps.Regards,Ramya.

huart · Answer

Hello and thank you for your help :)

I added the loops but that doesn't work either..

maybe the problem comes from saving the file?

I see you are using: saveAsExcel, but I don't have this method, am I missing a package?

Here is how I did:

Ramya Sivakumar · Answer

Hi Huart,There is no issue
with the file saving method. We have attached the sample which was tried
at our end. Kindly share a sample illustrating the issue or else modify the
following sample and reproduce the issue which would help us to proceed
further.Sample link - https://www.syncfusion.com/downloads/support/directtrac/general/ze/F175086903936161And kindly
confirm the Syncfusion Package version which you are using at your end.Regards,Ramya.

huart · Answer

thank you for your example,I tried and indeed, when I work with DateTime, it works correctly.The problem with this format is that it is limited to 24:00.In my case, I have to make a total calculation of hours worked, example over a week: 38:00 of work.I tried with the Duration type, but that doesn't work either.Do you have an idea ?I feel that we are getting closer to the goal :)
a big thank-you

Ramya Sivakumar · Answer

Hi Huart,We suggest you apply the [h]: mm number format to the cell to achieve your requirement.
Please find the code snippet below.Code Snippet:

sheet.getRangeByName('A4').numberFormat
  = '[h]:mm';

Kindly try and
let us know if the suggested code snippet helps.We appreciate your patience. Regards,Ramya.

huart · Answer

it doesn't work either :(If anyone has had this problem before..I reproduced the problem in the Excel file you sent me :Future _createExcel() async { // Create a new Excel Document. final Workbook workbook = Workbook(); // Accessing sheet via index. final Worksheet sheet = workbook.worksheets[0]; ///I need to add up working hours ///to calculate a weekly total sheet.getRangeByName('A1').setText("10:00"); sheet.getRangeByName('A2').setText("20:00"); sheet.getRangeByName('A3').setText("02:00"); //Creating a new style with all properties. final Style style = workbook.styles.add('Style1'); style.fontSize = 13; style.hAlign = HAlignType.right; style.vAlign = VAlignType.center; style.bold = false; style.numberFormat = 'hh:mm'; sheet.enableSheetCalculations(); sheet.getRangeByName('A1:A10').cellStyle = style; ///expected result ///A5 = 10:00 + 20:00 + 02:00 = 32:00 /// ///obtained result ///A5 = 00:00 sheet.getRangeByName('A5').setFormula("=SUM(A1:A3)"); int firstRow = sheet.getFirstRow(); int firstCol = sheet.getFirstColumn(); int lastRow = sheet.getLastRow(); int lastCol = sheet.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) { for (int col = firstCol; col <= lastCol; col++) { if (sheet.getRangeByIndex(row, col).formula != null) { sheet.getRangeByIndex(row, col).calculatedValue; } } } final List bytes = workbook.saveAsStream(); workbook.dispose();//Download the output file in web. AnchorElement( href: "data:application/octet-stream;charset=utf-16le;base64,${base64.encode(bytes)}") ..setAttribute("download", "Formula issue.xlsx") ..click(); }

Ramya Sivakumar · Answer

Hi Huart,Assigning the time values in cells setText
method considers the value as the Text. So, the sum of these cell values is 0,
which is incorrect. This is the same behavior in Microsoft Excel also. But, when you assign values to cells with
DateTime format in Microsoft Excel, the sum won’t be 0, but the result is
different from what you expect. Please look at the below screenshot.This behavior can be achieved in XlsIO
with the below code snippet.Code Snippet:

// Create a new Excel Document.
      final Workbook workbook = Workbook();
   
      // Accessing sheet via index.
      final Worksheet sheet =
  workbook.worksheets[0];
   
      ///I need to add up working hours
      ///to calculate a weekly total
      sheet.getRangeByName('A1').setDateTime(DateTime(1900,
  1, 1, 10, 0, 0));
     
  sheet.getRangeByName('A2').setDateTime(DateTime(2000, 1, 1, 20, 0, 0));
     
  sheet.getRangeByName('A3').setDateTime(DateTime(2000, 1, 1, 2, 0, 0));
   
      //Creating a new style with all
  properties.
      final Style style =
  workbook.styles.add('Style1');
      style.fontSize = 13;
      style.hAlign = HAlignType.right;
      style.vAlign = VAlignType.center;
      style.bold = false;
      style.numberFormat = 'hh:mm';
   
      sheet.enableSheetCalculations();
   
      sheet.getRangeByName('A1:A3').cellStyle
  = style;
   
      ///expected result
      ///A5 = 10:00 + 20:00 + 02:00 = 32:00
      ///
      ///obtained result
      ///A5 = 00:00
     
  sheet.getRangeByName('A5').setFormula("=SUM(A1:A3)");
      sheet.getRangeByName('A5').numberFormat = '[hh]:mm';
   
      int firstRow = sheet.getFirstRow();
      int firstCol = sheet.getFirstColumn();
      int lastRow = sheet.getLastRow();
      int lastCol = sheet.getLastColumn();
   
      for (int row = firstRow; row <= lastRow;
  row++) {
        for (int col = firstCol; col
  <= lastCol; col++) {
          if
  (sheet.getRangeByIndex(row, col).formula != null) {
           
  sheet.getRangeByIndex(row, col).calculatedValue;
          }
        }
      }
   
      final List<int> bytes = workbook.saveAsStream();
      workbook.dispose();

Kindly try this code snippet and let us know if this helps.Regards,Ramya.

huart · Answer

Hello,sorry but I don't understand how DateTime works, when I apply your code, I get a totally improbable result:on your side, do you manage to perform the calculation of the weekly working hours and have the right result?

Ramya Sivakumar · Answer

Hi Huart,We are checking the feasibility of providing the sample for your requirement and will get back to you with details on May 31, 2022.We appreciate your patience.Regards,Ramya

Ramya Sivakumar · Answer

Hi Huart,We have prepared
the sample to convert the text format to time format. Now the sum value is
proper kindly try the sample and let us know if it helps.Sample link - https://www.syncfusion.com/downloads/support/directtrac/general/ze/F175086-TimeIssue294712625Regards,Ramya.

huart · Answer

wooooow

it works !!!!a huge thank you to you <3

Ramya Sivakumar · Answer

You're welcome, Huart. We are glad!!! that the provided suggestion helped you, please feel free to contact us if you need any other assistance.Regards,Ramya