感謝黑暗執行緒的介紹範例
比NPOI更討喜的Excel元件-EPPlus!
我這版本是WinForm版本匯出
一樣載入
EPPlus 的DDL檔案。
來源由dataGridView提供匯出成
範例如下
/// <summary>
/// EPPlus 匯出excel c#
/// </summary>
void EXCEL()
{
using (ExcelPackage p = new ExcelPackage())
{
//Here setting some document properties
//p.Workbook.Properties.Author = "Zeeshan Umar";
//p.Workbook.Properties.Title = "匯出歷史資料";
// //Create a sheet
p.Workbook.Worksheets.Add("匯出歷史資料");
ExcelWorksheet ws = p.Workbook.Worksheets[1];
ws.Name = "歷史資料"; //Setting Sheet's name
ws.Cells.Style.Font.Size = 12; //Default font size for whole sheet
// ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
//Merging cells and create a center heading for out table
//寫入資料
int ColCnt = dGV1.Columns.Count; //欄位數
int RowCnt = dGV1.Rows.Count; //資料數
int startRowNumber = 2;
int DataRowNumber = startRowNumber + 1;
ws.Cells[1, 1].Value = "歷史資料";
ws.Cells[1, 1, 1, ColCnt].Merge = true;
ws.Cells[1, 1, 1, ColCnt].Style.Font.Bold = true;
ws.Cells[1, 1, 1, ColCnt].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//寫入標題文字
for (int i = 0; i < dGV1.Columns.Count; i++)
{
//設值為欄位名稱
ws.Cells[startRowNumber, i + 1].Value = dGV1.Columns[i].Name;
//設定樣式
ws.Cells[startRowNumber, i + 1].AutoFitColumns(); //自動欄寬
//sheet.Column(i + 1).Width = 100;
ws.Cells[startRowNumber, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中
//sheet.Cells[startRowNumber, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線
}
for (int i = 0; i < RowCnt; i++)
{
for (int j = 0; j < ColCnt; j++)
{
//讀值
string cellValue = "";
if (dGV1.Rows[i].Cells[j].Value != null)
{
cellValue = dGV1.Rows[i].Cells[j].Value.ToString();
}
else
{
cellValue = "";
}
//寫值
ws.Cells[DataRowNumber, j + 1].Value = cellValue;
//樣式設定
//sheet.Cells[DataRowNumber, j + 1].AutoFitColumns(); //自動欄寬
ws.Cells[DataRowNumber, j + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中
//ws.Cells[DataRowNumber, j + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線
}
DataRowNumber++;
}
//Generate A File with Random name
Byte[] bin = p.GetAsByteArray();
if (txtExportTxt.Text.Trim().Substring(txtExportTxt.Text.Trim().Length - 1, 1) != "\\")
{
txtExportTxt.Text += "\\";
}
//string file = txtExportTxt.Text.Trim() + Guid.NewGuid().ToString() + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
string file = txtExportTxt.Text.Trim() + "歷史資料" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
File.WriteAllBytes(file, bin);
ShowLog(" 匯出路徑 " + file);
}
}
0 留言