CODEWITHSUNDEEP
asp.netasp.net-mvcexcel
SeanX
SeanX

Reputation: 1861

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

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

  1. Open the report
  2. populate named cells with the correct data
  3. Use ActiveWorkbook.RefreshAll() to update queries and pivot tables
  4. 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)).

Upvotes: 0

Views: 7111

Answers (7)

Ryan
Ryan

Reputation: 119

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();`

Upvotes: 1

Todd Main
Todd Main

Reputation: 29153

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.

Upvotes: 4

Kalman Speier
Kalman Speier

Reputation: 1937

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 ?

Upvotes: 1

Bart Verkoeijen
Bart Verkoeijen

Reputation: 17723

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?

Upvotes: 1

Carlos Mu&#241;oz
Carlos Mu&#241;oz

Reputation: 17804

Take a look at NPOI

Upvotes: 2

Matthew Abbott
Matthew Abbott

Reputation: 61589

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

Upvotes: 4

Gardner
Gardner

Reputation: 939

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.

Upvotes: 0

Related Questions