Uploaded image for project: 'ZK Spreadsheet'
  1. ZK Spreadsheet
  2. ZSS-670

A dot is replaced with colon in a formula causes exported Excel file corruption under German (Polish) locale

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: 2.6.0
    • Fix Version/s: 2.6.0, 3.5.0
    • Component/s: None
    • Security Level: Jimmy
    • Labels:
      None

      Description

      Problem Description:

      Customer reported that:

      We use ZK's Apache POI library to export reports to Excel files(*.XLSX).
      We have found a bug in implementation of the Cell.setCellFormula method.
      Formula "A1*0.8" (in Polish default system settings) is written as "A1*0,8"

      • Dot is replaced by colon. - We have executed "getCellFormula" in the next
        line of code.
        Trying to open such file we have been told that the file is corrupted. - MS
        Excel expects a dot in formula. Manual change in xml file have proved this.

      What are we doing wrong ? Are there any options to set to do this right ?
      It looks like setCellFormula method acquires decimal system settings for
      presentation. In my opinion, it should not.

      For now we have a workaround: Constant "0.8" is replaced by "(8/10)".

      Steps to Reproduce:

      1. run with attached zul and composer.
      2. Click the "Set formula with Range" button(or "Set formula" button )
      3. click "Export to Excel" button to export
      4. open the exported Excel file with Excel 2007

      Actual:

      Excel show messages said "Excel found unreadable content in 'corruptFormula.xlsx'. Do you want to recover the contents of this workbook?..."

      If you open the corrupted file with "Open XML SDK 2.5 Productivity Tool for Microsoft Office" to see value in XML file, you can see:

      <x:row r="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <x:c r="A2">
          <x:f>A1*0,8</x:f>
        </x:c>
      </x:row>
      

      Exception printed in console

      嚴重的: error evaluating formula at A3
      java.lang.IllegalStateException: evaluation stack not empty
      at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:595)
      at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:313)
      at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:252)
      at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:267)
      at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:120)
      at org.zkoss.zss.model.impl.BookHelper.evaluate(BookHelper.java:469)
      at org.zkoss.zss.model.impl.BookHelper.getFormatText(BookHelper.java:1021)
      at org.zkoss.zss.model.impl.RangeImpl.getFormatText(RangeImpl.java:228)
      at org.zkoss.zss.ui.impl.Utils.getFormatText(Utils.java:869)
      at org.zkoss.zss.ui.fn.UtilFns.getCellFormatText(UtilFns.java:99)
      at org.zkoss.zss.ui.Spreadsheet$ExtraCtrl.getCellAttr(Spreadsheet.java:3111)
      at org.zkoss.zss.ui.Spreadsheet$ExtraCtrl.getRangeAttrs(Spreadsheet.java:2933)
      at org.zkoss.zss.ui.Spreadsheet.responseUpdateCell(Spreadsheet.java:2557)
      at org.zkoss.zss.ui.Spreadsheet.updateCell(Spreadsheet.java:2526)
      at org.zkoss.zss.ui.Spreadsheet.access$4100(Spreadsheet.java:179)
      at org.zkoss.zss.ui.Spreadsheet$InnerDataListener.onContentChange(Spreadsheet.java:2240)
      at org.zkoss.zss.ui.Spreadsheet$InnerDataListener.access$1100(Spreadsheet.java:1989)
      at org.zkoss.zss.ui.Spreadsheet$InnerDataListener$7.onEvent(Spreadsheet.java:2032)
      at org.zkoss.zss.engine.event.EventDispatchListener.onEvent(EventDispatchListener.java:43)
      at org.zkoss.zssex.engine.impl.RefBookImpl$DelegateListener.onEvent(Unknown Source)
      at org.zkoss.zk.ui.event.impl.DesktopEventQueue$QueueListener.onEvent(DesktopEventQueue.java:145)
      at org.zkoss.zk.ui.AbstractComponent.onEvent(AbstractComponent.java:2732)
      at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2703)
      at org.zkoss.zk.ui.AbstractComponent.service(AbstractComponent.java:2644)
      at org.zkoss.zk.ui.impl.EventProcessor.process(EventProcessor.java:136)
      at org.zkoss.zk.ui.impl.UiEngineImpl.processEvent(UiEngineImpl.java:1710)
      at org.zkoss.zk.ui.impl.UiEngineImpl.process(UiEngineImpl.java:1495)
      at org.zkoss.zk.ui.impl.UiEngineImpl.execUpdate(UiEngineImpl.java:1205)
      at org.zkoss.zk.au.http.DHtmlUpdateServlet.process(DHtmlUpdateServlet.java:583)
      at org.zkoss.zk.au.http.DHtmlUpdateServlet.doGet(DHtmlUpdateServlet.java:481)
      at org.zkoss.zk.au.http.DHtmlUpdateServlet.doPost(DHtmlUpdateServlet.java:489)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
      at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
      at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:401)
      at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
      at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
      at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:766)
      at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:450)
      at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
      at org.mortbay.jetty.Server.handle(Server.java:326)
      at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
      at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:945)
      at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
      at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
      at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
      at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
      at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

      1. corruptFormula.xlsx
        6 kB
        hawk
      2. exporttoexcel.zul
        0.5 kB
        hawk
      3. ExportToExcelComposer.java
        1 kB
        hawk

        Activity

        Show
        henrichen henrichen added a comment - 2.6: https://github.com/zkoss/zkspreadsheet/commit/9c12b12e1f4b7e7f8b1246320b6abb87f743e841 http://gitlab.potix.com:3000/zpoi/commit/79e84bd20554260f9775230a487dcc6ab0c9cc99
        Show
        henrichen henrichen added a comment - 3.5 https://github.com/zkoss/zkspreadsheet/commit/2c7f444bdbc36f3cdff511e155fbc01bc4af2880 http://gitlab.potix.com:3000/zpoi/commit/c7a7afda6c9fd15817bae0af5e8b9d65ee17c75d
        Hide
        henrichen henrichen added a comment -

        Fixed.

        Show
        henrichen henrichen added a comment - Fixed.

          People

          • Assignee:
            Jenkins Jenkins
            Reporter:
            hawk hawk
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: