none
How to make excel formulas reflect according to row insert operation RRS feed

  • Question

  • Hi, everyone.

    I have a website, user can design and upload excel template file for data filling and downloading. The backgroud program will read data from database and fill data into the excel. Everything is ok but the formulas in excel, it cannot reflect according to row insert operation. 

    For example, a user can design and upload such a excel template in which A1 is heading 'Count', A2 is data fields [count]', A3 is blank, A4 is a sum cell with formula '=sum(A2:A3)'. If the background program find just one row of data from database, fill it in A2, all is done. But if the program find 3 rows of data, it should copy A2 and insert two times to make 3 row of data line, and the sum cell should be A6 with formula '=sum(A2:A5)'.
    If I use office automation solution, thing is simple, because excel application will automatically update the formula after row inserted. But with OpenXML solution, I need to update the formula by myself's code, I need to parse all of the formula elements completely and compose them again. What the more worse is, the formula is not hard-coded, it's set by user. So parsing it is absolutely a large task.

    Is there any mechanism to make formulas automatically reflect according to row insert in openxml SDK?

    Thanks a lot. 

    Thursday, August 2, 2018 7:03 AM

All replies

  • Hello bhguo,

    As far as I know, there is no such mechanism in OpenXML. I think you have to pare all the formula elements completely and compose them again.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 3, 2018 4:24 AM
  • hi,Terry

    Thanks for your reply. I realize the biggest problem is the lack of such mechanism, is there any possibility to provide in next version of openxml sdk? just like office automation does.   

    Monday, August 6, 2018 6:29 AM
  • Hello bhguo,

    We are not the development team of OpenXML SDK. If you are have suggestion for OpenXML, you could also try to go to below link to make a suggestion/post an issue.

    https://github.com/OfficeDev/Open-XML-SDK/issues

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 6, 2018 6:52 AM