ASP.NET Create Excel on the fly from a Template file

Hi Experts,
      My key question  is - In ASP.NET,  Can I create an excel workbook object  ( without creating a physical file)  copied from a Template file ( which is a physical file)   . If so, please provide some sample code ( preferably in VB.NET)

Details of what   I am looking for  is -
         

     # Read a Template excel file  - ( Kept in a predefined directory. There is only one sheet )
     #  Create a Excel workbook .  copy the whole workbook or the sheet from the template  (on the fly -
                                                          without a physical file )
     
      # Write data to the  workbook ( which is not an issue)
      # Convert the Excel file to PDF on the fly ( This is a different scope. But interested to know the feasibility )  


 Thanks
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Prakash SamariyaIT ProfessionalCommented:
There are several ways to do this.  For this sample, you need to add reference of "Microsoft.Office.Interop.Excel" extensions
You can find sample code below:
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' Create new Application.
        Dim excel As Application = New Application
        ' Open Excel spreadsheet - Change your Excel Template file path
        Dim wbook As Workbook = excel.Workbooks.Open("D:\TEMP\Sample.xlsx")
        Dim sheet1 As Excel.Worksheet = wbook.Sheets(1)


        'Fill New Values to it - I have presume its range
        'You need to get the existing data to manipulate it or to find the new range to write data
        Dim value_range As Excel.Range = sheet1.Range("A4", "B5")
        Dim values(,) As String = {{3, "Manju"}, {4, "Jhanvi"}}
        value_range.Value2 = values

        'Export to PDF
        wbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, "D:\TEMP\Sample.PDF")

        'Remain template as it is
        wbook.Close(SaveChanges:=False)

        MessageBox.Show("Write down into Excel and Export to PDF - Done")
    End Sub
End Class

Open in new window

0
Sam OZAuthor Commented:
Hi Prakash,

    Sorry, I think you are using the Template File  and working on it .   I may have multiple users connecting concurrently and making changes (Finally each user will see his version of the PDF )  . So this approach may not be feasible.

Also, The PDF file  should not be a physical file  

  The reason I am looking  this approach is  for the sake of  NOT to have any files  existing than the template files

  Is there a way of having the Excel work book and PDF  as objects in the memory ?
0
Prakash SamariyaIT ProfessionalCommented:
Sorry Sam OZ,

I could not understand the logic behind keep PDF in memory, i mean it is useless.

Whatever operation you perform on excel, if you need to store it, you have choice untill you save it is already in memory!!!

I feel you don't want to change template excel file (no matters how many people accessing it) and the sample code does this!

Please elaborate your question
0
Sam OZAuthor Commented:
Hi Prakash,
     I think there will be a lock kept on the file when one user opens it
     Anyway, In the case of the PDF file, it certainly not be possible as it is a physical file you are referring to  

    The idea is to avoid having physical files created every time someone is trying to run this procedure(If this doesn't work, I need to have a windows service for the cleanup)
0
Prakash SamariyaIT ProfessionalCommented:
To keep thing in memory, it is better to use DataSet and import data from your excel template file and modify according to (runtime) users need!

But Logical PDF in memory is not possible, After Converting Excel into PDF, it prompts to save/open that PDF file! That is users choice to directly open that PDF or want to save for a while!! And yes, it is his duty to clear those file!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.