How can I update and save an excel file from asp.net mvc?

Question

I have an existing delphi desktop app that I am rewriting as an asp.net mvc app.

The desktop app has about 120 excel reports. These reports typically have few named cells containing setup information (client id etc), some data queries going back to a sql server data and a few pivotal tables.

To produce a report on the desktop app, I use ole automation to

Open the report
populate named cells with the correct data
Use ActiveWorkbook.RefreshAll() to update queries and pivot tables
Save the report

I would like to do this in my web application. However ole automation is not supporrted on the server, and doesn't work. None of the excel components I have seen appear to support refreshing the queries and/or pivotal tables.

Currently my best option appears to be rewriting the reports using some report builder and exporting these to excel. However the resulting files take longer to write, have less functionality (no pivotal tables) and of course there are 120 of them.

Any suggestions on how I can use my existing reports?

Update

Excel is installed on the server, the same version as on the development machine.

My mvc code is something like:

objApp = new Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Open(FileName);
objApp.DisplayAlerts = false;  // don't warn if pivot table changed
objApp.ActiveWorkbook.RefreshAll();
objBook.SaveAs(newFileName);

On the dev machine it works fine, but on the server it fails on the first line

objApp = new Application();

with System.UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

score 4 · Answer 1 · answered Jan 07 '11 at 18:04

4

I've not worked with Charts, but I thoroughly recommend EPPlus which is an open source library for creating Excel workbooks.

answered Jan 07 '11 at 18:04

Matthew Abbott

60,571
9
104
129

Upvote on the EPPlus. Great library, but limited pivot table support from what I've been able to figure out. – iivel Feb 28 '11 at 21:30

score 4 · Answer 2 · edited May 23 '17 at 11:47

4

This is a pretty common error, but requires a number of items to be set on the server for use with ASP.NET. Generally, the recommendations on this thread will suffice: System.UnauthorizedAccessException: Retrieving the COM class factory for Word Interop fails with error 80070005..

It doesn't appear that these have been handled properly and thoroughly. Late-binding could also be a part of the issue.

edited May 23 '17 at 11:47

Community

1
1

answered Feb 22 '11 at 02:23

Todd Main

28,951
11
82
146

I have been through those links but I still have the error. I am using IIS 7.0. I have set the dcom Excel.Application permisions for Network Service and IIS_IUSRS. Still no joy. – SeanX Feb 24 '11 at 00:36
I have excel 2003 on the server and 2007 on my machine. However I have rewriten my code to use late binding. A test .net app can load and update excel. Asp still cannot:(. – SeanX Feb 27 '11 at 20:39
Answer was not what asker was looking for, so editing it down. – Todd Main Mar 01 '11 at 16:04

score 2 · Answer 3 · answered Feb 22 '11 at 02:40

2

Take a look at

answered Feb 22 '11 at 02:40

Carlos Muñoz

17,397
7
55
80

Doesn't look like I can refresh existing reports. – SeanX Feb 27 '11 at 19:38

score 1 · Answer 4 · answered Feb 24 '11 at 09:28

You are right, you probably do not want to do OLE Automation on the server. Just the risk of having memory leaks and Excel instances running unmanaged in the background is a no-go.

We have good experience with Aspose.Cells. Maybe it supports the refresh feature you are looking for?

score 1 · Answer 5 · edited Mar 02 '11 at 16:04

I found this class a few months ago that I use to write to excel without using excel. It works like a charm and I use it in a lot of ASP.net applications. I honestly cannot remember where I got it so I cannot give that person create even though they deserve all of it.

/// <summary>
/// Produces Excel file without using Excel
/// </summary>
public class ExcelWriter
{
private Stream stream;
private BinaryWriter writer;

private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
private ushort[] clEnd = { 0x0A, 00 };


private void WriteUshortArray(ushort[] value)
{
    for (int i = 0; i < value.Length; i++)
        writer.Write(value[i]);
}

/// <summary>
/// Initializes a new instance of the <see cref="ExcelWriter"/> class.
/// </summary>
/// <param name="stream">The stream.</param>
public ExcelWriter(Stream stream)
{
    this.stream = stream;
    writer = new BinaryWriter(stream);
}

/// <summary>
/// Writes the text cell value.
/// </summary>
/// <param name="row">The row.</param>
/// <param name="col">The col.</param>
/// <param name="value">The string value.</param>
public void WriteCell(int row, int col, string value)
{
    ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
    int iLen = value.Length;
    byte[] plainText = Encoding.ASCII.GetBytes(value);
    clData[1] = (ushort)(8 + iLen);
    clData[2] = (ushort)row;
    clData[3] = (ushort)col;
    clData[5] = (ushort)iLen;
    WriteUshortArray(clData);
    writer.Write(plainText);
}

/// <summary>
/// Writes the integer cell value.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
/// <param name="value">The value.</param>
public void WriteCell(int row, int col, int value)
{
    ushort[] clData = { 0x027E, 10, 0, 0, 0 };
    clData[2] = (ushort)row;
    clData[3] = (ushort)col;
    WriteUshortArray(clData);
    int iValue = (value << 2) | 2;
    writer.Write(iValue);
}

/// <summary>
/// Writes the double cell value.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
/// <param name="value">The value.</param>
public void WriteCell(int row, int col, double value)
{
    ushort[] clData = { 0x0203, 14, 0, 0, 0 };
    clData[2] = (ushort)row;
    clData[3] = (ushort)col;
    WriteUshortArray(clData);
    writer.Write(value);
}

/// <summary>
/// Writes the empty cell.
/// </summary>
/// <param name="row">The row number.</param>
/// <param name="col">The column number.</param>
public void WriteCell(int row, int col)
{
    ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
    clData[2] = (ushort)row;
    clData[3] = (ushort)col;
    WriteUshortArray(clData);
}

/// <summary>
/// Must be called once for creating XLS file header
/// </summary>
public void BeginWrite()
{
    WriteUshortArray(clBegin);
}

/// <summary>
/// Ends the writing operation, but do not close the stream
/// </summary>
public void EndWrite()
{
    WriteUshortArray(clEnd);
    writer.Flush();
}
}

Just copy this code into a .cs file.

Here is an example

    ExcelWriter writer = null;
    FileStream stream = null;
    string result = string.Empty;

        string filepath = path;
        DateTime sd = Convert.ToDateTime(sdate);
        DateTime ed = Convert.ToDateTime(edate);
        string daterange = sd.Month.ToString() + sd.Day.ToString() + sd.Year.ToString() + "_" + ed.Month.ToString() + ed.Day.ToString() + ed.Year.ToString();
        string xls = filepath + filename + "_" + daterange + ".xls";

        if (File.Exists(xls))
        {
            File.Delete(xls);
        }

        stream = new FileStream(xls, FileMode.Create);
        writer = new ExcelWriter(stream);
        writer.BeginWrite();

        //write header

        writer.WriteCell(0, 0, "text");
        writer.WriteCell(0, 1, "text");
        writer.WriteCell(0, 2, "text");


        //write data
        int row = 1;

        //Open Connection
        OpenDBConnection();

        //get Case List
        List<Int32> caseList = getCaseList(sdate, edate);

        foreach (Int32 caseid in caseList)
        {
            writer.WriteCell(row, 0, caseid);
            writer.WriteCell(row, 1, caseid);
            writer.WriteCell(row, 2, caseid);
            row++;
        }

        writer.EndWrite();
        stream.Close();`

This would help with new reports but won't help me with my existing excel reports. — SeanX, Feb 28 '11 at 04:01

Kalman Speier · Answer 6 · 2011-02-28T19:30:29.937

1

Probably you got the same problem what I had a few months ago. The problem is that by default Microsoft Excel as a COM object can only activated by Administrator, System or Interactive accounts.

Here is a detailed solution, worked for me: http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

Hope this helps.

ps: What about OLE DB, is it possible to change your code to use OleDbConnection ?

edited Feb 28 '11 at 19:30

answered Feb 28 '11 at 19:25

Kalman Speier

1,937
13
18

score 0 · Answer 7 · answered Jan 07 '11 at 17:49

You are correct, it is definitely not supported by MS. However, ole automation should work on the server. Here's as stupid question, do you have Excel installed on the server? Can you send your sample code? I typically use the interops. Here is a quick example: http://dotnetperls.com/excel.

How can I update and save an excel file from asp.net mvc?

7 Answers7