[EXCEL] EPPlus

 

一、EPPlus

最以前依客戶需要做匯出 EXCEL 報表時，

我使用的是 Microsoft.Office.Interop.Excel 元件來達成，

但致命的是程式必須要安裝 Microsoft office Excel 才行，

遇到只安裝 Microsoft office Excel 2003 的，還要換成呼叫 Microsoft.Jet.OLEDB 元件。

 

改用 NPOI 後，程式就不需要再配合安裝 Microsoft Excel 軟體了，

但缺點就是程式控制複雜。

 

之後有網路上的大神們推薦 EPPlus 似乎可以簡化流程不錯用，

使用方法可利用 Nuget 找尋 EPPlus 安裝

或是直接去官網拿 EEPlus.dll 檔來使用皆可。

 

來參考下面的 sample code

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Drawing;
using System.IO;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            //開檔
            using (FileStream fs = new FileStream(Directory.GetCurrentDirectory() + "\\a.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //載入Excel檔案
                using (ExcelPackage package = new ExcelPackage(fs))
                {

                    // Add a new worksheet to the empty workbook
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    //Add the headers
                    worksheet.Cells[1, 1].Value = "ID";
                    worksheet.Cells[1, 2].Value = "Product";
                    worksheet.Cells[1, 3].Value = "Quantity";
                    worksheet.Cells[1, 4].Value = "Price";
                    worksheet.Cells[1, 5].Value = "Value";

                    //Add some items...
                    worksheet.Cells["A2"].Value = 12001;
                    worksheet.Cells["B2"].Value = "Nails";
                    worksheet.Cells["C2"].Value = 37;
                    worksheet.Cells["D2"].Value = 3.99;

                    worksheet.Cells["A3"].Value = 12002;
                    worksheet.Cells["B3"].Value = "Hammer";
                    worksheet.Cells["C3"].Value = 5;
                    worksheet.Cells["D3"].Value = 12.10;

                    worksheet.Cells["A4"].Value = 12003;
                    worksheet.Cells["B4"].Value = "Saw";
                    worksheet.Cells["C4"].Value = 12;
                    worksheet.Cells["D4"].Value = 15.37;

                    //Add a formula for the value-column
                    worksheet.Cells["E2:E4"].Formula = "C2*D2";

                    //Ok now format the values;
                    using (var range = worksheet.Cells[1, 1, 1, 5])
                    {
                        range.Style.Font.Bold = true;
                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                        range.Style.Font.Color.SetColor(Color.White);
                    }

                    worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A5:E5"].Style.Font.Bold = true;

                    worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address);
                    worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
                    worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";

                    //Create an autofilter for the range
                    worksheet.Cells["A1:E4"].AutoFilter = true;

                    worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";   //Format as text

                    //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful. 
                    //For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or 
                    //you want to use the result of a formula in your program.
                    worksheet.Calculate();

                    worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells

                    // lets set the header text 
                    worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
                    // add the page number to the footer plus the total number of pages
                    worksheet.HeaderFooter.OddFooter.RightAlignedText =
                        string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                    // add the sheet name to the footer
                    worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
                    // add the file path to the footer
                    worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

                    worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
                    worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];

                    // Change the sheet view to show it in page layout mode
                    worksheet.View.PageLayoutView = true;

                    // set some document properties
                    package.Workbook.Properties.Title = "Invertory";
                    package.Workbook.Properties.Author = "Jan Källman";
                    package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";

                    // set some extended property values
                    package.Workbook.Properties.Company = "AdventureWorks Inc.";

                    // set some custom property values
                    package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
                    package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");


                    //建立檔案
                    using (FileStream createStream = new FileStream(Directory.GetCurrentDirectory() + "\\output.xlsx", FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
                    {
                        package.SaveAs(createStream);//存檔
                    }//end using 
                }//end   using 
            }//end using 

            Console.WriteLine("produce output.xlsx is OK");
            Console.ReadKey();
        }
    }
}

 

二、NOPI

這是 n 年前我所玩過的 NOPI

#Region "  Imports"
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.XSSF
Imports NPOI.XSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util
Imports NPOI.XSSF.Util
Imports System.IO
#End Region

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            DataGridView1.DataSource = ReadExcelNPOIX(OpenFileDialog1.FileName, "工作表1")
        End If
    End Sub

    '只能讀取xlsx
    Public Function ReadExcelNPOIX(ByVal filePath As String, ByVal SheetName As String)
        Try
            Dim table As New System.Data.DataTable
            Dim Fs As FileStream = File.OpenRead(filePath)
            Dim workbook As XSSFWorkbook = New XSSFWorkbook(Fs)
            Dim sheet As XSSFSheet = workbook.GetSheet(SheetName)
            Dim headerRow As XSSFRow = sheet.GetRow(0)
            Dim cellCount As Int32 = headerRow.LastCellNum
            Dim co As Int32 = 0
            For i As Int32 = headerRow.FirstCellNum To cellCount - 1
                Dim column As DataColumn
                If IsNumeric(headerRow.GetCell(i).ToString) = True Then
                    co += 1
                    column = New DataColumn("S" & co)
                    table.Columns.Add(column)
                End If
                If IsNumeric(headerRow.GetCell(i).ToString) = False Then
                    column = New DataColumn(headerRow.GetCell(i).StringCellValue)
                    table.Columns.Add(column)
                End If
            Next

            Dim rowCount As Int32 = sheet.LastRowNum
            For i As Int32 = (sheet.FirstRowNum + 1) To sheet.LastRowNum
                Dim row As XSSFRow = sheet.GetRow(i)
                If IsNothing(row) Then Continue For
                Dim dataRow As DataRow = table.NewRow()
                For j As Int32 = row.FirstCellNum To cellCount - 1
                    If Not (row.GetCell(j) Is Nothing) Then
                        dataRow(j) = row.GetCell(j).ToString()
                    End If
                Next
                table.Rows.Add(dataRow)
            Next
            Fs.Close()
            workbook = Nothing
            sheet = Nothing
            Return table
        Catch ex As Exception
            MsgBox(ex.ToString)
            Throw ex
        End Try
    End Function

End Class

程式功能為讀取一 EXCEL 檔，並將資料呈現於程式裡，如 範例檔

 

三、使用 C# 導出 EXCEL 的八種方式

1、直接使用 IO 流

2、Microsoft.Jet.OLEDB

3、Microsoft.Office.Interop.Excel

4、LinqToExcel (只能讀EXCEL檔)

5、NPOI

6、Open XML SDK

7、ClosedXML (inherit Open XML SDK)

8、EPPlus

 

四、檔案格式名稱比較

檔案格式的種類。下表可能有誤差，僅供參考

檔案格式	副檔名	典型軟體支援
Microsoft Word Binary File Format	.doc	Microsoft Word 97 – 2003
Microsoft Office XML	.xml	Microsoft Office XP Microsoft Office 2007
Office Open XML	.docx	Microsoft Office 2010 Microsoft Office 2013 Microsoft Office 2016
OpenDocument	.odt	LibreOffice

 

參考資料：

JanKallman/EPPlus

C# 導出 Excel 的各種方法總結

比NPOI更討喜的Excel元件-EPPlus!

[C#] EPPlus 讀寫(read/write) Excel檔範例懶人Code