EPPlus: Quick Reference

Getting Started...

using OfficeOpenXml;
...
using (var stream = new MemoryStream())
{
    using (var xlPackage = new ExcelPackage())
    {
        var ws = xlPackage.Workbook.Worksheets.Add("Sheet1");
        var range = ws.Cells[1, 1];

        // do stuff

        xlPackage.SaveAs(stream);
    }
}

Autofilter

range.AutoFilter = true; // range should include column headers

Freeze Panes

ws.View.FreezePanes(startRow, startCol); // top left data cell

Merge Cells

range.Merge = true;

Format: Font

range.Style.Font.Name = "Cambria";
range.Style.Font.Size = (float)11.0;
range.Style.Font.Bold = true;

Border

range.Style.Border.Top.Style = ExcelBorderStyle.Thin; // must be set first
range.Style.Border.Top.Color.SetColor(Color.Black);

Formula: Divide/Multiply/Etc at the row level.

range.Formula = String.Format("E{0}/C{0}", row); // divide
range.Formula = String.Format("D{0}*E{0}", row); // multiply
range.Formula = String.Format("B{0}-C{0}", row); // subtract

Formula: SUM

range.Formula = String.Format("SUM(C{0}:C{1})", startRow, endRow);

Format: Date

range.Style.Numberformat.Format = "m/d/yyyy"
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

Format: Currency

range.Style.Numberformat.Format = "$#,###";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

Format: Percent

range.Style.Numberformat.Format = "#,###%";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

EPPlus: Creating Excel file Programmatically with .NET

Create a new Excel document using a memory stream and return the result as a file:

using (var stream = new MemoryStream())
{
    using (var xlPackage = new ExcelPackage())
    {
        var ws = xlPackage.Workbook.Worksheets.Add("Sheet1");
        ws.SetValue(1, 1, "Updated this cell");
        xlPackage.SaveAs(stream);
    }

    return File(stream.ToArray(), "application/excel", "test.xlsx");
}

This seems to work as well:

using (var xlPackage = new ExcelPackage())
{
    var ws = xlPackage.Workbook.Worksheets.Add("Sheet1");
    ws.SetValue(1, 1, "Updated this cell");
    return File(xlPackage.GetAsByteArray(), "application/excel", "test.xlsx");
}

References:
http://epplus.codeplex.com/
"Create advanced Excel 2007/2010 spreadsheets on the server" EPPlus 3.0.0.2 - Tue Jan 31 2012 at 2:00 AM

Adobe Acrobat: Javascript Sample

Subtracting Fields

var amtReceived = getField("TotalAmountReceived").value;
var amtCollected = getField("TotalFeesCollected").value;
event.value = amtReceived - amtCollected;

ASP.NET: Permissions for uploading to network share

If IIS is running under a machine account the easiest method is to add that computer to the share in lieu of a user account.

1. Right Click on the folder, choose "Sharing and Security..."
2. Click the "Share this folder" radio button
3. Give the share a name
4. Click the "Permissions" button
5. Click the "Add" button
6. Click "Object Types..." select the "Computers" box. Click "OK"
7. Type in the name of the computer. Click "OK"
8. Select the "Change" checkbox in the "Allow" column. Click "OK"
9. Repeat for the security tab. (Doesn't work without both)