0

I have a requirement where I need to store an excel workbook (with multiple sheets) in salesforce. Most probably in static resources or salesforce documents. Then when I click on a button on the record detail page, need to invoke apex that will retrieve this stored workbook, populate specific cells with certain values and output a fully populated workbook.

Example: Here is a simplified excel file. Note the formula in B4.

I will store this file in salesforce somewhere.

When I click a button, I have to retrieve this stored excel file and populate values like so..

Finally need to download it.

What is the approach I need to take?

closed as off-topic by Sebastian Kessel, Raul, Derek F, battery.cord, glls Aug 13 at 19:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – Sebastian Kessel, Raul, battery.cord, glls
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Nikesh, what have you tried? Where are you stuck? – Sebastian Kessel Aug 13 at 16:31
  • I have not tried anything and am looking for an approach. I am not sure what I need to do to retrieve a stored workbook with multiple sheets and plug in values into specific cells. I am looking for some direction. – Nikilesh Kopparthi Aug 13 at 16:58
  • 1
    Hey there, SFSE is a great place to get you unstuck from a problem, but you will find that people are generally not going to write code or do research for you. My recommendation is to proceed with your research and come back here for feedback or help when you "hit the wall" – Sebastian Kessel Aug 13 at 17:02
  • 2
    You need to look at document generation apps like Conga. – David Cheng Aug 13 at 17:34
1

Apex does not have the ability to read or write xls/xlsx files natively. While technically possible, it'd be a massive undertaking and end up being fairly limited in what it could do.

An xlsx file, for example, is an xml document adhering to the "openDocument" format, which is compressed as a zip file (which in turn is a combination of the LZ77 and Huffman compression techniques together with some interleaved data to tell you things like how many files there are, where one file ends and another starts, etc...).

On top of that, you need to worry about governor limits (particularly heap space and cpu time). There appear to be a few Apex zip utilities floating around on github, but I seem to recall that they can barely handle files around 1 MB in size, and that's only decompressing or compressing (not both).

The realistic solution here is to find, research, and purchase a third-party solution (likely doing the bulk of the work off of the Salesforce platform) that allows you to update values through an API.

  • Thanks for the input.. I think I will generate the entire XLS file on the fly instead of storing it and plugging in values. I was wondering how to output a sheet with formulas so users can download and plug values in. I think i got that figured out. – Nikilesh Kopparthi Aug 14 at 19:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.