緣起:

最近有完成一個緊急任務，修改購物車的訂單匯出功能，那個功能的初版是我們主任寫的，它會把 sql 的訂單資料寫到 excel 檔案裡，讓用戶可以下載。

我注意到程式裡有用到一個叫 NPOI 的套件，程式都是用它來處理 Excel 檔。學起來感覺會蠻有用的，所以就特別為它寫了這篇文章來記錄。

我會先用 Console 專案來測試 NPOI 的基本功能，然後再寫一個 WebForm 的專案，讓使用者點擊頁面上的按鈕後可以下載 xls 檔。

使用 NPOI 套件:

這個套件可以在 NuGet 上找到

不過我這裡是直接參考我們專案上有的 NPOI.dll

建立一個簡單的 xls 檔:

這邊直接放 code，它會在程式當前執行路徑下創建一個單一工作表的 xls 檔

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                hSSFWorkbook.CreateSheet("mySheet1");
                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }
    }
}

15行 : 用 FileStream 來在當前程式的執行路徑創建 xls 檔。

17、18行 : HSSWorkBook 是一個 excel 物件，再呼叫它的 CreateSheet 來建立一張資料表，傳string 參數來指定資料表的名稱。

19行 : 用 HSSWorkBook 的 Write 方法，可以把資料表的內容傳到 Stream 衍生類別裡，這邊是放我們的 FileStream。

程式執行結束後，在我們專案的 Debug 下會有一個 test.xls 檔，打開來看，它確實有張叫 mySheet1 的資料表。

可以用迴圈來加入多張資料表 (不過我們目前就只會用到一張資料表就是了

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                for(int i = 1; i <= 5; i++)
                {
                    //迴圈創建資料表
                    hSSFWorkbook.CreateSheet($"mySheet{i}");
                }
                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }
    }
}

新增 Title Row:

程式如下

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections.Generic;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.CreateSheet("mySheet");

                IRow hSSFSheetTitleRow = hSSFSheet.CreateRow(0);
                List<string> listStringColumn = new List<string>() { "column1", "column2", "column3" };
                for(int i = 0; i < listStringColumn.Count; i++)
                {
                    ICell hSSFSheetCell = hSSFSheetTitleRow.CreateCell(i);
                    hSSFSheetCell.SetCellValue(listStringColumn[i]);
                }

                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }
    }
}

20行 : 用變數紀錄 HSSFSheet，CreateSheet 回傳的型態是 ISheet，需要轉型。

22行 : 用 HSSFSheet 的 CreateRow 來新增列，傳入 0 代表第一列。用 IRow 型態的變數來記錄新產生的列。使用 IRow 前需要引用 NPOI.SS.UserModel。

23行 : String List 記錄要加入的欄位名稱。

26行 : IRow 的 CreateCell 可以加入一個 Excel 儲存格，傳入的整數代表第幾行，產生的儲存格可用 ICell 類型的變數紀錄。

27行 : ICell 的 SetCellValue 可以設定儲存格的值。

執行程式後，可以看到表格有加入首列

設定儲存格欄位值的程式差不多就這樣，新增一列，然後在列裡加入儲存格。

設定儲存格格式與字型:

可以透過 HSSFWorkBook 的 CreateCellStyle 與 CreateFont 來產生格式與字型的物件。ICellStyle 有 SetFont 方法能設定字型，ICell 有 CellStyle 屬性能設定儲存格格式。

產生 ICellStyle 跟 IFont 的部份，我把它各自寫成一個方法來呼叫。

那些能設定的屬性，名稱都蠻直觀的，所以就不多解釋了。

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections.Generic;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.CreateSheet("mySheet");

                IRow hSSFSheetTitleRow = hSSFSheet.CreateRow(0);
                List<string> listStringColumn = new List<string>() { "column1", "column2", "column3" };
                for(int i = 0; i < listStringColumn.Count; i++)
                {
                    ICell hSSFSheetCell = hSSFSheetTitleRow.CreateCell(i);
                    hSSFSheetCell.CellStyle = _GenerateICellStyle(hSSFWorkbook);
                    hSSFSheetCell.SetCellValue(listStringColumn[i]);
                }

                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }

        private static ICellStyle _GenerateICellStyle(HSSFWorkbook hSSFWorkbook)
        {
            ICellStyle cellStyleReturn = hSSFWorkbook.CreateCellStyle();
            cellStyleReturn.BorderTop = BorderStyle.Medium;
            cellStyleReturn.BorderRight = BorderStyle.Medium;
            cellStyleReturn.BorderLeft = BorderStyle.Medium;
            cellStyleReturn.BorderBottom = BorderStyle.Medium;
            cellStyleReturn.VerticalAlignment = VerticalAlignment.Center;
            cellStyleReturn.Alignment = HorizontalAlignment.Center;

            cellStyleReturn.SetFont(_GenerateIFont(hSSFWorkbook));

            return cellStyleReturn;
        }

        private static IFont _GenerateIFont(HSSFWorkbook hSSFWorkbook)
        {
            IFont fontReturn = hSSFWorkbook.CreateFont();
            fontReturn.Boldweight = (short)FontBoldWeight.Bold;
            fontReturn.FontHeightInPoints = 12;
            return fontReturn;
        }
    }
}

程式跑完後，表格會長這樣

欄位看來有點擠，可以在 For 迴圈裡呼叫 HSSFSheet 的 AutoSizeColumn 方法，傳入 i 來自動調整每行的寬度

固定首列:

有看到程式用 HSSFSheet 的 CreateFreezePane 方法來固定第一列，傳入 4 個 int 參數，分別是 0、1、0、1。第一個代表 "要固定的列數"，第二個是 "固定的行數，第三個是 "首列序號"，第四個是 "首行序號"。我們只有列要固定而已，所以最後一個參數好像隨便代也沒差就是了 (?

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections.Generic;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.CreateSheet("mySheet");

                IRow hSSFSheetTitleRow = hSSFSheet.CreateRow(0);
                List<string> listStringColumn = new List<string>() { "column1", "column2", "column3" };
                for(int i = 0; i < listStringColumn.Count; i++)
                {
                    ICell hSSFSheetCell = hSSFSheetTitleRow.CreateCell(i);
                    hSSFSheetCell.SetCellValue(listStringColumn[i]);
                    hSSFSheet.AutoSizeColumn(i);
                }
                hSSFSheet.CreateFreezePane(0, 1, 0, 10);
                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }
    }
}

30行 : 固定第一列。

執行完程式，打開我們的 excel 檔，往下捲可以看到，第一列確實有被固定

合併儲存格:

HSSFSheet 有 AddMergedRegion 方法可以合併儲存格，傳入的參數是 CellRangeAddress 類型的物件，CellRangeAddress 有一個 constructor 是傳入 4 個 int 參數，第一個是 "開始列"，第二個是 "結束列"，第三個是 "開始行"，第四個是 "結束行"。

使用 CellRangeAddress 需要引用 NPOI.SS.Util。

如果我欄位是想要這樣合併

程式可以這樣寫

using System;
using System.Reflection;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Collections.Generic;

namespace ConsoleApp1
{
    class Program
    {
        public static void Main(String[] args)
        {
            //程式當前執行路徑
            string sCurrentExePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sXlsFilePath = Path.Combine(sCurrentExePath, "test.xls");
            using(FileStream fileStream = new FileStream(sXlsFilePath, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
                HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.CreateSheet("mySheet");

                IRow hSSFSheetTitleRow = hSSFSheet.CreateRow(0);
                List<string> listStringColumn = new List<string>() { "column1", "column2", "column3" };
                for(int i = 0; i < listStringColumn.Count; i++)
                {
                    ICell hSSFSheetCell = hSSFSheetTitleRow.CreateCell(i);
                    hSSFSheetCell.SetCellValue(listStringColumn[i]);
                    hSSFSheet.AutoSizeColumn(i);
                }
                hSSFSheet.AddMergedRegion(new CellRangeAddress(3, 5, 1, 2));
                hSSFWorkbook.Write(fileStream);
            }
            Console.ReadKey();
        }
    }
}

第31行 : 合併儲存格

結果

我想介紹的 NPOI 功能就以上這些，接下來進入網頁下載 excel 的部份。

WebForm 下載 Excel:

新增一個 ASP WebForm 的專案，新增一個 test.aspx 頁面。asp 頁面就放個按鈕即可，專案要記得加入 NPOI.dll 參考。

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="test.aspx.cs" Inherits="WebApplication1.test" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button runat="server" id="btnDownloadExcel" Text="下載Excel" OnClick="btnDownloadExcel_Click"/>
        </div>
    </form>
</body>
</html>

後台部分，把我們在 Console 寫的程式複製到 ButtonClick 裡，這邊就不是用 FileStream 來記錄 Excel 的輸出了，而是用 MemoryStream 來記錄。

重點就是對 Response 的操作。

using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Collections.Generic;

namespace WebApplication1
{
    public partial class test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void btnDownloadExcel_Click(object sender, EventArgs e)
        {
            HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
            HSSFSheet hSSFSheet = (HSSFSheet)hSSFWorkbook.CreateSheet("mySheet");

            IRow hSSFSheetTitleRow = hSSFSheet.CreateRow(0);
            List<string> listStringColumn = new List<string>() { "column1", "column2", "column3" };
            for (int i = 0; i < listStringColumn.Count; i++)
            {
                ICell hSSFSheetCell = hSSFSheetTitleRow.CreateCell(i);
                hSSFSheetCell.SetCellValue(listStringColumn[i]);
                hSSFSheet.AutoSizeColumn(i);
            }

            MemoryStream memoryStream = new MemoryStream();
            hSSFWorkbook.Write(memoryStream);

            Response.Expires = 0;
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Length", memoryStream.ToArray().Length.ToString());
            Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
            Response.BinaryWrite(memoryStream.ToArray());
            Response.Flush();
            Response.End();

            memoryStream.Dispose();
        }
    }
}

17~27行 : Excel 資料表的處理。

29、30行 : 新增一個 MemoryStream，並把 Excel 資料寫入。

32~42行 : Response 的設定與操作。

44行 : 釋放 MemoryStream

打開頁面，按下按鈕後就能儲存 Excel 檔了

鳥旭的技術學習部落格

搜尋此網誌

2024年3月11日星期一

C# NPOI 套件與 ASP WebForm 下載 xls 檔

緣起:

使用 NPOI 套件:

建立一個簡單的 xls 檔:

新增 Title Row:

設定儲存格格式與字型:

固定首列:

合併儲存格:

WebForm 下載 Excel:

沒有留言:

張貼留言

搜尋此網誌

2024年3月11日 星期一

C# NPOI 套件與 ASP WebForm 下載 xls 檔

緣起:

使用 NPOI 套件:

建立一個簡單的 xls 檔:

新增 Title Row:

設定儲存格格式與字型:

固定首列:

合併儲存格:

WebForm 下載 Excel:

沒有留言:

張貼留言

2024年3月11日星期一