Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Writing to, saving, and printing excel worksheets via C# and ASP .NET

Posted on 2009-05-18
5
Medium Priority
?
1,937 Views
Last Modified: 2013-12-17

I have a requirement for a client where I have to allow users to a web form so they can fill out form fields and submit the page. The application is then going to write the form field values to various cells in an Excel spreadsheet and save it under a new name. The newly saved workbook must then have several worksheets converted to pdf documents.

As a proof of concept, I've created a winforms C# project in VS 2005. I am able to open, read, write, and save to an excel worksheet. My issues are with the printing aspect of the process. I had intended to use Adobe's PDF printer driver to export the excel worksheets to pdf files.

The documentation on msdn isn't very descriptive in this regard. See the following code. The only line that will correctly print the excel worksheet to pdf is this one:

objBook.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", true, false, null);

The problem here is that a dialogue pops up requiring a filename to save the file to. The last paramater of the PrintOut method allows for the entry of this filename. The 3rd to last parameter is a boolean that designates whether or not the worksheet/workbook is being printed to a file. The 2nd to last parameter is true for collate and false for not. I'm not even sure what collate does.

It doesn't matter what values I toggle on some of these parameters, I always get an error from the Adboe PDFWriter saying that the file is locked. Sometimes the file is created but is corrupted and has a size of 0 bytes.

I'm open to any and all suggestions so long as they can be done strictly through C# and/or ASP .NET. I really need a solution that can work on the web but if you have a windows only solution I would accept that. I just need to get to the point where I can save to the worksheet and print/export it to a pdf. If you know of 3rd party products that you might have used or heard about that can do this, I would be very interested to know about them.

Basically any and all input is welcome. I have searched extensively on Experts Exchange and found some possibilities but nothing in C# alone and nothing in ASP .net.

The end solution will need to be able to handle the exporting of some fairly complex charts and such; however, don't let this deter you from making suggestions as I'm at an impasse at the moment.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Excel;
 
 
namespace ExcelInteropTesting
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        //Declare these two variables globally so you can access them from both
        //Button1 and Button2.
        Excel._Application objApp;
        Excel._Workbook objBook;
 
        private void button3_Click(object sender, EventArgs e)
        {
 
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
 
            try
            {
                // Instantiate Excel and start a new workbook.
                objApp = new Excel.Application();
                //Don't show dialogs if app is hidden
                objApp.DisplayAlerts = true;
                
                objBooks = objApp.Workbooks;
                objBook = objBooks.Open(@"C:\Development\ExcelInteropTesting\Test.xls",
                        Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value,
                        Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 
                objSheets = objBook.Worksheets;
                objSheet = (Excel._Worksheet)objSheets.get_Item(1);
                objSheet.Cells[1, "A"] = 8;
                objApp.Visible = true;
                //objApp.ActivePrinter = "Acrobat PDFWriter";
                objApp.UserControl = true;
                objBook.Save();
                //objBook.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", false, false, "C:\\Development\\ExcelInteropTesting\\test6.pdf");
                //objSheet.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", true, false, "test2.pdf");
                //objSheet.PrintOut(1, 1, 1, false, Missing.Value, true, false, "test2.pdf"); 
 
                //Creates corrupt file with 0 bytes of info. Adobe doesn't recognize it or know how to open it.
                //objBook.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", true, true, "C:\\Development\\ExcelInteropTesting\\test6.pdf");
                objSheet.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", true, true, "C:\\Development\\ExcelInteropTesting\\test7.pdf");
 
                //semi working line 
                objBook.PrintOut(1, 1, 1, false, "Acrobat PDFWriter", true, false, null);
               
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
 
                MessageBox.Show(errorMessage, "Error");
            }
            finally
            {
                objApp.Workbooks.Close();
                objApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objApp);
                objSheets = null;
                objSheet = null;
                objBooks = null;
                objBook = null;
                objApp = null;
                GC.Collect();
 
                Environment.Exit(0);
            }
 
 
 
        }
 
 
    }
}

Open in new window

0
Comment
Question by:baijajusav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 24414425
you should not use Excel automation on a web server. consider using http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx which will solve all your problems.
0
 
LVL 3

Author Comment

by:baijajusav
ID: 24423658

I've looked over the product in detail and that fits the bill perfectly. The price is pretty steep, but beggars can't be choosers. Thanks for the link!

I'll leave this up for another day or so and then assign points.
0
 
LVL 3

Author Comment

by:baijajusav
ID: 24432532

Okay, it doesn't look like there's any more input to be had here. I'll look into purchasing the Aspose.Cells product. From everything I am seeing, this company seems to be pretty top notch. They've custom modified code for various customers in a pretty big hurry too. In addition to that, it looks like I can open a spreadsheet, modify the cells, save it, export it to pdf, and then prompt the user to download the newly created pdf in under 20 lines of code. That's beyond wicked.

Thanks for the suggestion emoreau.
0
 
LVL 3

Author Closing Comment

by:baijajusav
ID: 31582666
Suggested product fits the bill perfectly. The price is steep, but there's always a premium on quality.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24432712
I have used some of their products and was very satisfied with my experiences and the support they provide.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

704 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