i need to generate an excel sheet from an excel template which contains drop down lists. I need to use the template and populate the with data from datbase and select the appropriate value from the drop down lists and generate the new excel sheet. I am in very bad situation and need it asap.
2 Answers
1
votes
0
votes
You have a few options:
- Third-party component ($$$) to read, modify, and send the Excel file.
- Office COM automation on the back-end (bad idea. Really. Don't do it).
- Save your template on the server as an XML Spreadsheet file (the XML format used by Office 2002 and 2003 and still supported in 2007). Since it's a single XML file, it can be easily read by server-side code, modified on the fly, and redirected back to the user.
- Save your template on the server as an XLSX file (the newer XML format used by 2007) and modify it on the way out to the user. Much more complicated since there's a ZIP wrapper and multiple XML files involved.
- Save your template on the server as a normal Excel 97/2000 file (not 2007), and when the user requests it, make a copy of the file on the server (requires write access) with a random name, open an database connection using the Excel OLEDB driver to the Excel file, perform INSERT statements into it (goes into tabs of those names, where you store your drop-down values), close the connection, and send the file. Caveat: the Excel OLEDB driver has some limitations.
- Go the other direction. Use Excel's web data connection capabilities to access the server after the Excel file is already on the user's machine and open to grab the appropriate values from the server. Requires some user training to refresh the data.
