• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Reference Material for using C#.net to generate Excel Reports

At work, our department is starting to use C#.net for web development.  C#.net, 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 C#.net 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.

0
rjgraper
Asked:
rjgraper
1 Solution
 
devsolnsCommented:
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.
0
 
rjgraperAuthor Commented:
devsolns,

       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 C#.net.  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 C#.net, 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.



0
 
anand2kCommented:
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];
                  XLWS.Activate();

// Close XL
XLWB.Close(Type.Missing,Type.Missing,Type.Missing);

// Save XL
XLWB.SaveAs(@sFileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlShared,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

// Set value in cell
XLWS.Cells.set_Item(iRow,iCol,sVal);

// Get value in Cell
XLApp.get_Range(sCellAddress,sCellAddress).Select();
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.

Anand

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rjgraperAuthor Commented:
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 C#.net 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 C#.net 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.Clear()
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        this.EnableViewState = false;
        Response.Write("Hello World!");
        Response.End();

        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.
0
 
rjgraperAuthor Commented:
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
                 Excel
- 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/vnd.ms-excel”
   - 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:
      Examples:
            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
      anyway.

**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.
0
 
GranModCommented:
Closed, 200 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now