CODEWITHSUNDEEP
c#.netexcelexport-to-excel
David
David

Reputation: 219027

Can an Excel Document be Loaded into Memory, Populated, and Returned as Bytes?

I've worked with Excel Interop before for the purposes of opening an Excel file, writing some data to it, and saving it. Something like this:

public void WriteAFile()
{
    var xlApp = new Excel.ApplicationClass();
    var xlWorkbook = xlApp.Workbooks.Add(Missing.Value);
    var xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);

    xlWorksheet.Name = "Some Name";

    // Write some contrived data
    for (var i = 0; i < 10; i++)
    {
        xlWorksheet.Cells[i + 1, 1] = i;
        xlWorksheet.Cells[i + 1, 2] = i + 1;
        //... and so on...
    }
    //... and so on...

    // Save the file
    xlWorkbook.SaveAs(
      @"",
      Excel.XlFileFormat.xlWorkbookNormal,
      Missing.Value,
      Missing.Value,
      false,
      false,
      Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
      Excel.XlSaveConflictResolution.xlLocalSessionChanges,
      false,
      Missing.Value,
      Missing.Value,
      Missing.Value
    );

    xlWorkbook.Close(true, Missing.Value, Missing.Value);
    xlApp.Quit();

    // release other resources, etc.
}

This works reasonably well for an application writing to a file on a local workstation. However, now I'm working on a web application which needs to return a file to the user. The catch is that the file on the file system can't be modified. So what I need to do is:

  1. Load a known Excel file into memory (note that the file is .xlsm and contains macro code, which will be run on the user's workstation when they download it).
  2. Write known data to a known location in the file (for the sake of argument, some numeric values to the first few cells on sheet 99).
  3. Stream that file from memory via the web application (the streaming part is trivial, it's getting the byte array in the first place that seems trickier to me).

I've Googled around, but haven't found much of anything. Basically just instructions and examples which do what I'm already doing in my code above. It also comes as no surprise when working with COM interop that my code above (which is pretty old) and other code I find online is all a bit dated. We're using .NET 4.0, so anything that's available there is appropriate for us.

The extent of my experience with COM is pretty much fully displayed in the code above. I'd prefer not to use COM at all for this, if that's at all possible. But right now I'm just having trouble getting off the ground. Can anybody point me in the right direction? Maybe reference some classes or methods that would be of particular use here? Thanks.

P.S. Another thing that kind of frightens me is the need for Excel on the web server, as well as the possibility of the Excel application opening on the console of the web server and hanging on a user-unseen error of some kind. So any advice there is very much appreciated.

Upvotes: 1

Views: 5573

Answers (4)

David
David

Reputation: 219027

I ended up using the ClosedXML library. (OpenXML by itself was... unwieldy.) As a simple proof of concept for opening the file, writing a piece of data, and returning a byte array:

public byte[] GetExcelApplication(Guid sessionKey)
{
    // Input checking

    using (var workbook = new XLWorkbook(_excelFile))
    {
        var worksheets = workbook.Worksheets.Where(w => w.Name == "Session Key");
        if (worksheets.Count() < 1)
            throw new Exception("Excel file does not contain a worksheet called: Session Key");
        if (worksheets.Count() > 1)
            throw new Exception("Excel file contains multiple worksheets called: Session Key");

        var worksheet = worksheets.Single();
        var cell = worksheet.Cell("A1").Value = sessionKey.ToString();

        using (var ms = new MemoryStream())
        {
            workbook.SaveAs(ms);
            return ms.ToArray();
        }
    }
}

Upvotes: 3

Kumnaa
Kumnaa

Reputation: 1

Doing something similar with COM I resorted to saving the generated Excel workbook to a temporary folder, sending it off and then deleting it. Not using COM wasn't really an option for me as the workbook also needed pivot tables and graphs generated.

Upvotes: 0

naspinski
naspinski

Reputation: 34707

Old-school, but I wrote this a long time ago that constructs a workbook on the fly and exports it. Maybe it can get you on the right track?

Upvotes: 0

Manuel
Manuel

Reputation: 2223

With c# 4.0 COM should be a bit easier since you can use dynamic. But it's then still COM. I use a thirt party product for this called aspose cells. I'm not working for them, but i realy like this product(even if it's not free). This save me a lot of trouble when dealing with excel.

Upvotes: 1

Related Questions