Populate an Excel template and stream the results to the browser?

[johnlohse]

I am currently working on an N-Tier web application in .Net using C#. I have an Excel spreadsheet which I need to populate with data from a SQL Server database. My goal is to open the spreadsheet, insert the database values into the appropriate cells and stream it back to the browser without saving the spreadsheet back to the server. I'm still a bit new to the .Net environment and have no idea where to even start. Any help would be greatly appreciated.

 

[jmeckley]

1st you need to access the xls document. you either need to use a library that accesses xls files, or use the Excel Interop objects, which required Excel being installed on the server.

working with xls in memory depends on the library you choose. I use Carlos' XLS XML Writer to create xls on the server.

once this is done you need to read the file to an array of bytes or an IO.Stream. once you have the stream/bytes you can output this to the Response output.
I would recommend an asp.net handler (ashx) not a page (aspx) object to handel this request. it would look something like this

public class getreport : IHttpHandler 
{ 
   public void ProcessRequest (HttpContext context) 
   {
      context.Response.Buffer = true;
      context.Response.Clear();
      context.Response.ClearContent();
      context.Response.ClearHeaders();
      context.Response.ContentType = "application/vnd.ms-excel";

      //get parameters from session or querystrings
      //get data from db
      //get xls template
      //populate template

      context.Response.AddHeader("Content-Disposition", "inline; filename=" + [file name here]);

      context.Response.BinaryWrite(bytes[]);
      //or
      XLS.Save(Response.OutputStream, Enum.Type);

      context.Response.Flush();
      context.Response.End();
   }
}
 
public bool IsReusable { get { return true; } }

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[johnlohse]

Thanks for the quick reply Jason. Unfortunately installing Excel on the server is not an option, so Excel Interop is out of the picture. I looked at Carlos' XLS XML Writer, but did not see where it had the ability to open an existing spreadsheet and populate specific cells. For dynamic reporting I am currently using nFOP to generate PDF documents. This particular report, however is an agency standard Excel document with the values needing to be inserted into specific cells. The result set will only contain a single record. Are there any alternatives to Excel Interop, or did i maybe overlook this functionality in the XLS XML Writer?

 

[jmeckley]

i have only used this assembly to design new workbooks. if the xls is not too complex, could you recreate the xls and just output a new file instead of loading a template?

a quick search returned SoftArtisans' Office Writer.

Jason Meckley
Programmer
Specialty Bakers, Inc.