none
Transporting data into a formatted excel template

    Question

  • Hi all,

    I was wondering how is it possible to send data to an already created excel template already on file stored on a local disk and populate it? I have a dataset that can be filtered, and the only way I could view what is in it is by using a datagrid. The only tutorials I could find are of transporting data from a data set, then creating a new worksheet from scratch and then populating it. Other tutorials only show how to populate an excel sheet, then use VB.NET to format the spreadsheet afterwards.

    I have four different templates on file. I used excel to create those templates. Those templates will be used for each query that is executed on the dataset that is generated. There are four data sources, and each data source can use only one template.

    I also need this to be fast since some of the data collected will return more than 5000 to 10000 rows.

    Currently, I found a few websites out there that could help me, but they are a 3rd party company with expensive licensing fees. Is there any way or any sites that could help me out.

    One more question, is there any difference in populating an excel worksheet from a DataSet than from a DataGrid? I seen tutorials for each one, and Im wondering what is the advantage is picking one over the other.

    thanks.
    Friday, October 10, 2008 7:01 PM

All replies

  • [quote]
    I was wondering how is it possible to send data to an already created excel template already on file stored on a local disk and populate it?
    [/quote]

    Is it a VSTO template or a native Excel template?

    [quote]
    One more question, is there any difference in populating an excel worksheet from a DataSet than from a DataGrid?
    [/quote]

    The major difference is that with a DataGrid we can use the DataSet as a source while we for worksheets needs to iterate the DataSet and write the records to the worksheet. However, if we use classic ADO then we can dump the data into worksheets with the method CopyFromRecordset. 
    Kind regards, Dennis (http://xldennis.wordpress.com/)
    • Edited by XL-Dennis Saturday, October 11, 2008 5:20 PM
    Saturday, October 11, 2008 5:18 PM
  • Hi Dennis,

    It is a native Excel template. I don't need to recreate a template for each report. There will be a static excel template on disk, which will accept a datasource and fill it up whenever invoked. The excel template has already been created with the excel program using macros (formulas) for specific cells. However, I am not sure how to reference or point an attribute from a dataset and position it correctly in the specified cells of the excel template.

    For example, template 1 will be for datasource A, template 2 for datasource B.. it will never be template 2 for datasource A, or vice versa with template 1 and datasource B. Its a one to one.

    Which is faster? Using CopyFromRecordSet? Or iterating records from a DataSet? Is there a limit to how many rows can be copied with CopyFromRecordSet?
    Monday, October 13, 2008 12:54 PM
  • One solution I came up with earlier was to have an excel template created, and having each individual cell linked to an XML element from a newly created XML document. Like, have the dataset send the data as an xml document (temporarily stored on file), and the excel document would probably link up to that XML document. Then a new file (formatted by the template, populated by the xml document) would then be stored on the harddrive locally, which would then be sent off to the client.

    But I don't know if Im over complicating the issues by using XML. I'm trying to find ways to maximize on processing time. It would seem to be a bottleneck to use XML as an intermediary, converting all that info from a dataset to XML then to excel, when it makes more sence to do it from a dataset directly to excel.
    Monday, October 13, 2008 1:02 PM