西夏普的部落格

1.前言:
EPPlus是一個可以在Server-side產生Microsoft Excel 2007格式的檔案，為LGPL license的開放原始碼的函式庫。

2.說明:
使用EPPlus作為伺服器端產生Excel檔案的好處是在伺服器上無需安裝Office，動態叫用EPPlus的方法產生Excel檔案，提供網頁下載Excel報表。

EPPlus的說明可參考:
http://en.wikipedia.org/wiki/ExcelPackage

EPPlus軟體下載網址:
http://epplus.codeplex.com/

本範例使用版本為EPPlus 3.1.3
軟體解壓縮後，將\EPPlus 3.1.3\EPPlus.dll的DLL檔複製到自己專案的bin目錄下

加入參考: EPPlus.dll
加入命名空間:

using OfficeOpenXml;

程式碼:

//範例一，簡單產生Excel檔案的方法
private void CreateExcelFile()
{
	System.IO.FileInfo filePath = new System.IO.FileInfo(@"d:\tmp\c.xlsx");

	if (!System.IO.File.Exists(filePath.ToString()))
	{
		ExcelPackage ep = new ExcelPackage(filePath);
		ExcelWorksheet ws = ep.Workbook.Worksheets.Add("Class");

		ws.Cells[1, 1].Value = "name";
		ws.Cells[1, 2].Value = "score";

		ws.Cells[2, 1].Value = "abey";
		ws.Cells[3, 1].Value = "tina";
		ws.Cells[4, 1].Value = "boi";
		ws.Cells[5, 1].Value = "hebe";
		ws.Cells[6, 1].Value = "paul";

		ws.Cells[2, 2].Value = "85";
		ws.Cells[3, 2].Value = "82";
		ws.Cells[4, 2].Value = "84";
		ws.Cells[5, 2].Value = "86";
		ws.Cells[6, 2].Value = "82";

		ep.Save();
	}
}

//範例二，DataTable轉成Excel檔案的方法
private void DataTableToExcelFile(DataTable dt, System.IO.FileInfo filePath)
{
	if (!System.IO.File.Exists(filePath.ToString()))
	{
		ExcelPackage ep = new ExcelPackage(filePath);
		ExcelWorksheet ws;
		if (dt.TableName != string.Empty)
		{
			ws = ep.Workbook.Worksheets.Add(dt.TableName);
		}
		else
		{
			ws = ep.Workbook.Worksheets.Add("Sheet1");
		}

		for (int i = 0; i < dt.Columns.Count; i++)
		{
			ws.Cells[1, i + 1].Value = dt.Columns[i].ColumnName;
		}

		for (int i = 0; i < dt.Rows.Count; i++)
		{
			for (int j = 0; j < dt.Columns.Count; j++)
			{
				ws.Cells[i + 2, j + 1].Value = dt.Rows[i][j].ToString();
			}
		}

		ws.Cells[1, 1, dt.Rows.Count + 2, dt.Columns.Count + 1].Style.ShrinkToFit = true;
		ws.Cells[1, 1, dt.Rows.Count + 2, dt.Columns.Count + 1].Style.Font.Size = 8;

		ep.Save();
	}
}

//範例三，DataTable轉成Excel檔案的方法
private void DataTableToExcelFile(DataTable dt, System.IO.FileInfo filePath, string startAddress)
{
	if (!System.IO.File.Exists(filePath.ToString()))
	{
		ExcelPackage ep = new ExcelPackage(filePath);
		ExcelWorksheet ws;
		if (dt.TableName != string.Empty)
		{
			ws = ep.Workbook.Worksheets.Add(dt.TableName);
		}
		else
		{
			ws = ep.Workbook.Worksheets.Add("Sheet1");
		}

		ws.Cells[startAddress].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Custom);

		ep.Save();
	}
}

3.應用:

//範例一
CreateExcelFile();
//範例二
DataTable dt = TxtConvertToDataTable(@"d:\tmp\ciexyz.csv", "data", ",");
System.IO.FileInfo filePath = new System.IO.FileInfo(@"d:\tmp\c.xlsx");
DataTableToExcelFile(dt, filePath);
//範例三
DataTable dt = TxtConvertToDataTable(@"d:\tmp\ciexyz.csv", "data", ",");
System.IO.FileInfo filePath = new System.IO.FileInfo(@"d:\tmp\c.xlsx");
DataTableToExcelFile(dt, filePath, "B2");