Reference Material for using to generate Excel Reports

Posted on 2006-04-10
Last Modified: 2008-01-09
At work, our department is starting to use for web development., being new to just about all our former VB 6 developers, means that we don't have much expertise.

The project I have been tasked with is to create Excel documents/reports from Oracle tables.  

I have had luck using Response.Write() commands getting data grids and straight text down to Excel.  But I have been unable to perform basic Excel formatting like landscape orientation or manual page breaks.

What I am really looking for is a good reference on generating Excel documents from a web application that gives examples on performing a degree of formatting.  My trips looking through Borders Books' computer section and Google/Yahoo searches have been frustrating.

Question by:rjgraper
    LVL 13

    Expert Comment

    The reason it will be difficult is because very few are interested in automating excel using c# com interop.  let me ask you this do you know how to do it in vb6?  if the answer is yes then im not sure why are you asking how to do it.  the api is exactly the same as it is when used with vb6.  the only different (and the hardest part for you) will be c# type safety and non use of default parameters.  all of the methods that you are used to calling from vb6 that you can just leave blank isnt going to work anymore.

    to be honest i would like to make use of 3rd party like crystal, sql reports, anything.

    another piece of friendly advice that i would give is training for your staff.  some of the very worst C# development that i have seen in the last few years always comes from VB6 developers.  OOP seems to be an unknown concept in vb6 world.  let me know if the above paragragh made sense.  just to recap if you know how to do it in vb6 nothing changes in c# (except types passed to methods and absence of default params.

    Author Comment


           Thank you for your response.  Allow me to address your points one by one:

    1) Using VB6 to solve problem

          We have been directed by management to stop developing in VB6.  All new development is to be in  There are two reasons for this decision.  One, management does not want all of their web developers to have to learn multiple new languages.  For example, using VB .NET is not an option.  2nd, as you know, VB6 will stop being supported some time in the near future.  We have got to migrate our applications to .NET before we are forced to perform the migration.

    2) Using a 3rd party reporting software package instead of Excel

          Again, this gets into company politics.  Our clients love Excel.  They understand Excel.  They don't want to have to learn anything new.  Also, 3rd party packages like Crystal Reports comes with a heavy price tag for Enterprise level web support that we don't have the monetary support for.. Perhaps, some day.

    3) OOP and VB6 developers

          Speaking for my development team.  We all understand that we are new to, and there is going to be a huge learning curve.  But all of us are willing to learn and make mistakes along the way.  We do have some web expertise in ASP and JAVA amoung our group.  It's just going to be challenging for a while... And no.  I don't take offense at your comment.  Sorry you've had to deal with a lot of bad former VB6 coders in your travels.

    I know some brave soul has probably written reference material on this topic.  It is just a matter of finding it.

    Thanks again for your input.

    LVL 1

    Expert Comment

    Hi rjgraper ,

    Use following sample code for XL in C#. First give reference of MS Excel dll to ur app so that Interop will be created. Then use follwoing code.

    using System;
    using Excel;

    // Declare objects
                protected Excel.Application XLApp;
                protected Excel.Workbook XLWB;
                protected Excel.Worksheet XLWS;

    // Readymade functions as follows

    // Open XL
    XLApp =  new Excel.ApplicationClass();
                      XLWB = XLApp.Workbooks.Open(@sFileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

                      XLWS = (Excel.Worksheet)XLWB.Sheets[sSheetName.Length==0?"1":sSheetName];

    // Close XL

    // Save XL

    // Set value in cell

    // Get value in Cell
    return (string) XLApp.ActiveCell.Value2;

    Note -
        I have used Office 2002, XL ver 10.2 and XP as OS.
        SaveXL may need to differ in other OS like Win2K.

    Hope this will be good starting for u guys.



    Author Comment

    Anand (anand2k),

          Thank you for your response.  Unfortunately, your instructions are for controlling an Excel application on a client machine.  This works well for a Windows application.  But this cannot be done on web applications because web browsers are designed to not allow file manipulation of a user's PC; Otherwise, browsers would allow unauthorized users access to the file system on a person's pc (i.e. - Viruses can spread more easily)

           There is a middle ground that I am partially achieving.  With web apps, you can have your main page open a 2nd browser window using Javascript (or VBscript).  Once the child window is opened, the web output can be changed to Excel by using commands like:

            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/";
            this.EnableViewState = false;
            Response.Write("Hello World!");

            The above commands will turn the child window into an Excel page where the user can pick SAVE AS from the file pull-down menu to save the Excel spreadsheet.  This, of course, is a simple example.  It will just write "Hello World" in cell A1 on the spreadsheet.  But, you can see that output can be sent to Excel.

            I'm trying to use this code to send a hard page break to Excel and change the orientation to landscape.  I'm certain that both can be done.  I just need to find a good reference.

            I appreciate your attempt to help though.

    Author Comment

    I have identified a work around that will allow me to create Excel documents in landscape with page breaks.  It's a little involved because it involves programatically generating the HTML-XML that Excel can read.  But it works.  Maintenance of future enhancements can also be a pain.  But for everyone's reference, please see below.

    - Open a new C#.web application (e.g. – ExcelApp)
    - In Excel, write a sample report:
      - Report should have:
        - page breaks (at least one)
        - orientation of landscape
        - left/right margins of 0.25inches (or minimum you can get away with)
        - maximum values in each variable column
          You want to use maximum values so that “#” symbols do not appear when the generated report is sent to
    - Save the Excel report as a web page (Output.htm)
    - Open Output.htm in NOTEPAD
    - In the HTML <head> section:
       - change the “Content-Type content=text/html” string to “Content-Type content=application/”
       - Remove any XML strings that include ‘FitToPage’ or ‘Scale’
                 These parameters will mess up the page breaks
    - Save Output.htm
    - Do a search for string ‘<x:Row>’ in the file and note the number between the ‘<x:Row>’ and ‘</x:Row>’ tags.  This number (e.g. – 22) is your page size (You will need this number later when you code—i.e. – PAGESIZE constant)
    - Create text files using subsets of Output.htm:
       - Head1.txt – Output.htm text file from beginning up to (but not including) XML “<x:PageBreaks>” string
       - Head2.txt – Output.htm text file from string “</x:ExcelWorksheet>” up to string “</head>” (i.e. – Remainder of
                           <head> HTML section)
       - Body1.txt – Output.htm text file from string “<body…>” up to (but not including) first “<tr” (i.e. – Table row)
       - Body2.txt – Output.htm file from first “<tr” up through (but not including) the next “<tr” where 2nd page of data
                          would start
       - Body3.txt – Output.htm file from string “<![if supportMisalignedColumns]>” up through the end of the file
    - Save all text files above to the ExcelApp project folder
    - Modify Body2.txt to accept parameters:
       - Add page break parameter to first ‘<tr’ file as first parameter of style
          Example --> <tr height=17 style=’|page-break|height:12.75pt’>
       - Change all variable data from the constant value it had on page 1 of your Excel report example to a parameter:
                RG99 sales rep becomes |sales-rep-id|
               Rep ‘Ronald Green’ becomes |sales-rep-name|
       - But what about dollar and percent fields?  Look at two typical table data (‘td’) lines from a sample ‘Body2.txt’ file

                        <td class=xl31 align=right width=46 style=’width:35pt’ x:num=”10000”>10,000</td>

                        <td class=xl33 align=right width=46 style=’width:35pt’ x:num=”0.35”>35%</td>

          At first glance, it looks like we need to have 2 parameters for the same value.  When, in fact, we only need one.  If
          we replace the 10000 value with a parameter like “|Jan-Base-Amt|” and the 0.35 value with a parameter like
          “|Jan-Attain-Pct|”, then we are done with the parameter.  Why?  Because Excel will use the class of xl31 or xl33 to
          decide how the numbers will be displayed.  Anything we put in place of “10,000” or “35%” would be ignored

    **ExcelApp pseudocode**
    - Create main form that requests parameter information from user (e.g. – Sales Rep Id)  For our example, the Excel
       report will print a separate page for every client within a user-supplied sales rep id
    - Main form would create/call child form while passing the parameter information (I’ve found this can be done easily
       using JAVAScript)
          You may be wondering why we supply the report parameters in a parent form and generate the actual Excel
                    report in a child form.  If we did not do this and did all the work in the parent form; Once the user closed
                    the Excel application, the web application would end (i.e. – The user would not be able to generate
                    additional reports for different sales reps without restarting the web app)
    - Within child form, do the following:
       - Create global variables to hold passed information
       - Create Constant variable for PAGESIZE (see above)
       - In Page_Load routine:
          - Initialize global variable with passed values
          - Use FileStream/StreamReader to load ‘head1.txt’ into a string (mainfile)
          - Query Oracle (e.g. – Stored Proc) to see # of pages user-requested report will generate (e.g.–intNumPages = 5)
          - Concatenate HTML-XML page information to ‘mainfile’:

               // Append page html-xml to 'mainfile' string if number of pages > 1
               int intNumPages = EntryDB.GetPageCount(mstrSalesRepId);
               if (intNumPages > 1)
                    mainfile += "    <x:PageBreaks>" + Environment.NewLine;
          mainfile += "     <x:RowBreaks>" + Environment.NewLine;
          for (int i = 1; i <= intNumPages; i++)
                mainfile += "      <x:RowBreak>" + Environment.NewLine;
                mainfile += "       <x:Row>" + (i * PAGESIZE) + "</x:Row>"
                                                                          + Environment.NewLine;
                mainfile += "      </x:RowBreak>" + Environment.NewLine;
          mainfile += "     </x:RowBreaks>" + Environment.NewLine;
          mainfile += "    </x:PageBreaks>" + Environment.NewLine;

          - Use FileStream/StreamReader to append ‘head2.txt’ and ‘body1.txt’ into string ‘mainfile’
          - Query Oracle (e.g. – Stored Proc) to get all sales rep (e.g. – Sales rep’s name and title)  and basic client
                  information (e.g. – Client’s name and business unit)
          - For each client within the sales rep, perform the following:
               - Use FileStream/StreamReader to load ‘body2.txt’ into a string (newfile)
               - Replace the ‘|field-nm|’ parameters with their correct values
                     On page 1, ‘|page-break|’ should be replaced with an empty string.  On all other pages, it should be
                           replaced with ‘page-break-before:always;
               - Query Oracle to get remaining client information
               - Replace the ‘|field-nm|’ parameters with their correct values
               - Use FileStream/StreamWriter to write updated ‘newfile’ string to file ‘body2-revised.txt’
               - Use FileStream/StreamReader to append ‘body2-revised.txt’ to string ‘mainfile’
           - Use FileStream/StreamReader to append ‘body3.txt’ to string ‘mainfile’
           - Use FileStream/StreamWriter to create file ‘output.xls’ from string ‘mainfile’
           - Issue command ‘Response.Redirect(“output.xls”)’ to render Excel document into child window

    User can now use menu to save shown Excel report as whatever they want.

    **Note** -- Files body2-revised.txt and output.xls should be read/writable by web application.

    Accepted Solution

    Closed, 200 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
    Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now