[C#] Epplus （Excel元件）

Epplus （Excel元件）

1.      先用NuGet直接加入Epplus (可參考 NuGet網站)
PM> Install-Package EPPlus

2.      使用C# Windows Form專案，建立如下的介面。
大概的功能是先從資料庫撈一個表格的資料出來，按下匯出就可以使用Epplus的語法匯出成Excel。

3.      直接列出匯出成Excel的程式碼…

//若存在該檔案則先刪除

                if (File.Exists(sFilePath))

                {

                    File.Delete(sFilePath);

                }

                //開檔

                FileStream fs = new FileStream(sFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);

                //載入Excel檔案

                ExcelPackage ep = new ExcelPackage(fs);

                if (ep.Workbook.Worksheets[sNewSheetName] == null) //不存在該Sheet

                {

                    //加入一個WorkSheet

                    var ws = ep.Workbook.Worksheets.Add(sNewSheetName);

                    ws.View.ShowGridLines = true; //是否顯示格線

                }

                //取得WorkSheet

                ExcelWorksheet sheet = ep.Workbook.Worksheets[sNewSheetName ];

                //設定起始列為3

                int startRowNumber = 3;

                int DataRowNumber = startRowNumber+1;

                //寫入標題文字

                for (int i = 0; i < dataGridView.Columns.Count; i++)

                {

                    //設值為欄位名稱

                    sheet.Cells[startRowNumber, i + 1].Value = dataGridView.Columns[i].Name;

                    //設定樣式

                    sheet.Cells[startRowNumber, i + 1].AutoFitColumns(); //自動欄寬

                    //sheet.Column(i + 1).Width = 100;

                    sheet.Cells[startRowNumber, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中

                    sheet.Cells[startRowNumber, i + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線

                }

                //寫入資料

                int ColCnt = dataGridView.Columns.Count; //欄位數

                int RowCnt = dataGridView.Rows.Count; //資料數

                for (int i = 0; i < RowCnt; i++)

                {

                    for (int j = 0; j < ColCnt; j++)

                    {

                         //讀值

                        string cellValue = "";

                        if (dataGridView.Rows[i].Cells[j].Value != null)

                        {

                            cellValue = dataGridView.Rows[i].Cells[j].Value.ToString();

                        }

                        else

                        {

                            cellValue = "";

                        }

                        //寫值

                        sheet.Cells[DataRowNumber, j + 1].Value = cellValue;

                        //樣式設定

                        //sheet.Cells[DataRowNumber, j + 1].AutoFitColumns(); //自動欄寬

                        sheet.Cells[DataRowNumber, j + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; //欄位置中

                        sheet.Cells[DataRowNumber, j + 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //儲存格框線

                    }

                    DataRowNumber++;

                }

                //存檔

                ep.SaveAs(fs);

                fs.Close();

                //關閉資源

                ep.Dispose();

                ep = null;

                MessageBox.Show("儲存成功! ");

4.      匯出的Excel內容~

5.      參考資料
EPPlus-Create advanced Excel 2007 spreadsheets on the server
【工具】Excel利器—NPOI VS EPPLUS

[C#] EPPlus 讀寫Excel檔範例懶人Code
How to export Excel in Web using Epplus