I’ve been using NPOI for years to create/edit XLS documents via C#, and it works perfect. If you want to work with XLS on your server without installing Office and using COM, check out NPOI and I’m sure you won’t be disappointed.  Since I migrated our entire company over to Office365, everybody is now running Office 2013+ and I’m constantly being asked why my apps are spitting out XLS files that give everybody an annoying compatibility warning and limit the rows to 65K.  Since I needed a mental break (more like a learning stretch) on a new project, I decided to try a out EPPlus for managing XLSX files via C# without COM.

Most of the time I develop reports/document/etc… with Excel, I like to start with a formatted template. This makes the initial conversations with the users easy, and it abstracts the formatting from my code (insert data into per-formatted cell). I also find that 1/2 my changes after go-live are formatting related and by using Excel I can adjust and deploy the update in seconds. I did a bit of looking around and I choose EPPlus, since the project is active and there is a NuGet package available (EPPlus, Version 4.0.4).

Here is my application UI, which needs a new excel document

2015-11-07_21-12-29

The UI is pretty simple, the workflow is based on the warehouse staff adding delivery notes (warehouse orders) to a shipment until the shipping coordinator locks the shipment and prepares it for pickup (palletize, wrap, shipping labels, etc…). The UI follows the physical process. Once the shipment is locked, the warehouse staff are unable to add additional orders to the shipment. The process of locking, will schedule the next pickup and display a link to download the Excel document. I’ve been asked to support PDF as well, as the warehouse stations are moving away from using Excel. This article follows the development of the Bill of Lading (“BOL”). The interface is 100% Ajax driven with jQuery and Web API on the back end.

Steps to Implement BOL with EPPlus & Web API

Item #1: Create Excel XLSX template with Excel 2013

Here is the excel template I created (layout, colors, formatting, formulas for totals, etc…).

2015-11-07_19-33-13

I created Warehouse_BoL.xlsx and stored it in the root of my project in a folder called /template/report/, this is the location where I keep all of my report templates.

Item #2 ( Populate Template and Return as XLSX/PDF )

Step #1: Install EPPlus via NuGet

Package Manager Console: PM> Install-Package EPPlus

Step #2: Populate Template

Issue #1 (dynamic rows):

As I started thinking about how I was going to support 300+ rows, I came up with a VERY simple solution. I created my template with rows 12-400 being allotted and formatted for data. I would then load the file with data, and delete the BLANK rows. Since I wanted my document to look pretty when printed on a single page (as this should be 90% of the documents printed), I decided to always leave up to row 35 in the excel file regardless of the number of rows of data added. This keeps the single page print, pretty.

Issue #2 (formula refresh):

When I got the export work and moved to exporting to PDF I found my formulas to total cartons/weight were showing “0” in the report. I’m not sure if this is a bug, but I found notes in the documentation that you can call “Calculate()” on the cell to force the formula to refresh. Worked perfect.

using OfficeOpenXml;
using System.IO;

public static MemoryStream GenerateBOL(int id)
{
    try
    {
        // Template File
        string templateDocument = 
            HttpContext.Current.Server.MapPath("~/templates/report/warehouse_bol.xlsx");

        // Results Output
        MemoryStream output = new MemoryStream();

        // Read Template
        using (FileStream templateDocumentStream = File.OpenRead(templateDocument))
        {
            // Create Excel EPPlus Package based on template stream
            using (ExcelPackage package = new ExcelPackage(templateDocumentStream))
            {
                // Grab the sheet with the template, sheet name is "BOL".
                ExcelWorksheet sheet = package.Workbook.Worksheets["BOL"];

                // SubSonic 2x - Data Acces, load shipment header data.
                Shipment shipment = Shipment.FetchByID(id);

                // Insert BOL header data into template
                sheet.Cells[2, 3].Value = shipment.ScheduledCarrier + " PICK-UP";
                sheet.Cells[3, 3].Value = string.Format("{0} ( {1}-{2} )",
                    shipment.CustomerName,
                    shipment.CustomerCode,
                    shipment.DelToCode);
                sheet.Cells[6, 6].Value = shipment.DestinationAddress1;
                sheet.Cells[7, 6].Value = shipment.DestinationAddress2;
                sheet.Cells[8, 6].Value = shipment.DestinationAddress3;
                sheet.Cells[9, 6].Value = shipment.DestinationAddress4;

                // Start Row for Detail Rows
                int rowIndex = 12;

                // SubSonic 2x - Data Access, load shipment details
                using (IDataReader reader = SPs.RohmPortal_GetShipmentOrderStatistics(id).GetReader())
                {
                    while (reader.Read())
                    {
                        // Cell 1, Carton Count
                        sheet.Cells[rowIndex, 1].Value = Int32.Parse(reader["Cartons"].ToString());
                        // Cell 2, Order Number (Outline around columns 2-7 make it look like 1 column)
                        sheet.Cells[rowIndex, 2].Value = reader["DNNO"].ToString().Trim();
                        // Cell 8, Weight in LBS (convert KG to LBS, and rounding to whole number)
                        sheet.Cells[rowIndex, 8].Value =
                            Math.Round(decimal.Parse(reader["GrossWeight"].ToString()) * 2.2m);

                        // Increment Row Counter
                        rowIndex++;
                    }
                }

                // Force formula to compute, or formulas are not updated when you convert XLSX / PDF.
                sheet.Cells[402, 1].Calculate();
                sheet.Cells[402, 8].Calculate();

                // Logic to keep up to row 35 when the data is short (pretty 1 page).
                if (rowIndex < 35)
                {
                    sheet.DeleteRow(36, 400 - 36);
                }
                else if (rowIndex < 400)
                {
                    sheet.DeleteRow(rowIndex, 400 - rowIndex);
                }

                package.SaveAs(output);
            }
            return output;
        }
    }
    catch (Exception e)
    {
        // Log Exception
        return null;
    }
}

Next, I needed to create a Web API route to generate the XLS file. Seeing how I planned to return at least 2 different formats XLSX/PDF, I created a generic wrapper and a method to return the data as a stream.

Generic – Return Stream as File Method

public static HttpResponseMessage ReturnStreamAsFile(MemoryStream stream, string filename)
{
    // Set HTTP Status Code
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);

    // Reset Stream Position
    stream.Position = 0;
    result.Content = new StreamContent(stream);

    // Generic Content Header
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");

    //Set Filename sent to client
    result.Content.Headers.ContentDisposition.FileName = filename;

    return result;
}

Generic – API Wrapper to Generate XLSX/PDF.

[Route("api/warehouse/schedule/bol/{id}/{format}")]
[HttpGet]
public HttpResponseMessage GetBillOfLadding(int id, string format)
{
    MemoryStream documentStream = GenerateBOL(id);

    // Make Sure Document is Loaded
    if (documentStream != null && documentStream.Length > 0)
    {
        // Generate dynamic name for BOL document "BOL-XXXXXX.PDF or .XLSX"
        string documentName = string.Format("BOL-{0}.{1}", id, format);

        // Based on format requested in URI (PDF/XLSX), call the associated method.
        switch (format)
        {
            case "PDF":
                MemoryStream pdfMemoryStream = ConvertXLSXtoPDF(documentStream);
                return FileAsAttachment(pdfMemoryStream, documentName);
                break;

            case "XLSX":
                return FileAsAttachment(documentStream, documentName);
                break;
        }
    }

    // If something fails or somebody calls invalid URI, throw error.
    return new HttpResponseMessage(HttpStatusCode.NotFound);
}

Step #3: Install FreeSpire.XLS

Package Manager Console: PM> Install-Package FreeSpire.XLS

Free version is limited to 5 sheets per workbook and 150 rows per sheet. This limitation is enforced during reading or writing XLS or PDF files. Free Spire.XLS v7.8 has no any limitation when loading and saving .xlsx file format. When converting Excel files to PDF files, you can only get the first 3 pages of PDF file.

Step #4: Create method to convert XLSX to PDF

Yep, FreeSpire.NET has a limit of 3 pages. Yuck!!! I was short on time and I’d love to find another simple open simple open source solution, but E-ICEBLUE’s Spire.XLS code really made the XLSX to PDF process very simple and I had working code in 5 minutes. The DLL is ~12MB, which is 10x larger than my entire web application, but it works!

If you know of a plugin solution or one that requires little tweaking to generate XLSX documents into PDF, please let me know!!!

using Spire.Xls;
using Spire.Pdf;
using Spire.Xls.Converter;

// Convert XLSX to PDF MemoryStream
public static MemoryStream ConvertXLSXtoPDF(MemoryStream stream)
{
    // Result Stream
    MemoryStream pdfMemoryStream = new MemoryStream();

    // Spire.XLS to open XLSX workbook stream created by EPPlus
    Workbook workbook = new Workbook();
    workbook.LoadFromStream(stream, ExcelVersion.Version2013);

    // Spire.PDF to convert XLSX to PDF, I read it has limited functionality (total pages, rows, etc...).
    PdfConverter pdfConverter = new PdfConverter(workbook);
    PdfConverterSettings settings = new PdfConverterSettings();
    PdfDocument pdfDocument = pdfConverter.Convert(settings);
    pdfDocument.SaveToStream(pdfMemoryStream);
            
    return pdfMemoryStream;
}

I found references saying to use “Workbook.SaveToFile()” to generate the PDF file, but this was resulting in errors (blank PDF files). Using PdfConverter() works, but you should expect compile warning that the method is obsolete.

THE RESULTS

XLSX Excel Document

/api/warehouse/schedule/bol/14240002/XLSX

BOL-14240002-1.XLSX__[Read-Only]_-_Excel_(Product__2015-11-07_21-16-50

PDF Document

/api/warehouse/schedule/bol/14240002/PDF

BOL-14240002.PDF_-_Adobe_Acrobat_Pro_2015-11-07_21-13-41