Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


Writing an Excel File using EPPlus API
Below is a helper class for using the EPPlus API. I cannot remember where I pulled most of this from, but I extended it with more functionality and it works great.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;
using System.Drawing;
using OfficeOpenXml.Drawing;
using System.Data;

namespace Api.EPPlus
{
/// <summary>
/// Manipulate an excel file using the EPPlus API, free from http://epplus.codeplex.com/
/// License terms (Public) on http://epplus.codeplex.com/license
///
/// </summary>
public class ExcelWriter : IDisposable
{
ExcelPackage _pck;

/// <summary>
/// Open a new or existing file for editing.
/// </summary>
/// <param name="fileName"></param>
public ExcelWriter(string fileName)
{
_pck = new ExcelPackage(new FileInfo(fileName));
}

/// <summary>
/// Open a new or existing file for editing, and add a new worksheet with the data
/// </summary>
/// <param name="fileName"></param>
/// <param name="newWorksheetName">new sheet to add</param>
/// <param name="dtData">data to add</param>
public ExcelWriter(string fileName, string newWorksheetName, DataTable dtData):this(fileName)
{
var ws = this.AddWorksheet(newWorksheetName);
this.SetValues(ws, dtData);
}

/// <summary>
/// Add a new worksheet. Names must be unique.
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public ExcelWorksheet AddWorksheet(string name)
{
return _pck.Workbook.Worksheets.Add(name);
}

/// <summary>
/// Add a new picture. Names must be unique.
/// </summary>
/// <param name="image"></param>
/// <param name="imageName"></param>
/// <param name="ws"></param>
/// <param name="row"></param>
/// <param name="col"></param>
public ExcelPicture AddPicture(Image image, string imageName, ExcelWorksheet ws, int row, int col)
{
var pic = ws.Drawings.AddPicture(imageName, image);
pic.SetPosition(row, 0, col, 0);
pic.Border.LineStyle = eLineStyle.Solid;
return pic;
}

/// <summary>
/// Note: this will only perform as well as 'SetValues' if you load/write data from left to right, top to bottom. Otherwise it may perform poorly.
/// </summary>
/// <param name="ws"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="value"></param>
public void SetValue(ExcelWorksheet ws, int row, int col, object value)
{
ws.Cells[row, col].Value = value;
}

/// <summary>
/// Populate a large number of cells at once
/// </summary>
/// <param name="ws"></param>
/// <param name="value">Data to load</param>
public void SetValues(ExcelWorksheet ws, List<object[]> value)
{
ws.Cells.LoadFromArrays(value);
}

/// <summary>
/// Populate a large number of cells at once
/// </summary>
/// <param name="ws"></param>
/// <param name="value">Data to load</param>
public void SetValues(ExcelWorksheet ws, DataTable dt)
{
ws.Cells.LoadFromDataTable(dt, true);
}

public void SetDefaultRowHeight(ExcelWorksheet ws, int rowHeight)
{
ws.DefaultRowHeight = rowHeight;
}

/// <summary>
/// Saves to file and dispos
/// </summary>
public void Save()
{
_pck.Save();
}

/// <summary>
/// Release all resources
/// </summary>
public void Dispose()
{
if (_pck != null)
_pck.Dispose();
}
} // class
} // namespace

Created By: amos 3/19/2015 12:43:59 PM