使用Open XML SDK讀取Excel中的文字

2010-12-18 10:11 AM

因專案需引用使用者提供的Excel資料，我做了些嘗試。原本是用NPOI開啟xls檔案讀取資料，後來發現NPOI似乎無法提供欄位經格式化的文字結果(例如: 使用者輸入日期，轉成"yyyy年mm月dd日")，於是將xls轉為xlsx，也嘗試了Open XML SDK做法。

參考了初步教學文件，寫了簡單的範例程式:

using System;

using System.Linq;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml.Packaging;

using System.Text;

public partial class ReadExcel_Default : System.Web.UI.Page

    protected void Page_Load(object sender, EventArgs e)

        StringBuilder sb = new StringBuilder();

        sb.AppendFormat("<table border='1'>");

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(

            Server.MapPath("基本資料檔.xlsx"), true))

            WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(

                      doc.WorkbookPart.Workbook.Descendants<Sheet>().First().Id

);

            Worksheet sheet = worksheetPart.Worksheet;

            foreach (Row row in sheet.Descendants<Row>())

                sb.AppendFormat("<tr>");

                foreach (Cell cell in row.Descendants<Cell>())

                    sb.AppendFormat(

                        "<td>{0}</td>",

                        cell.CellValue == null ? "&nbsp;" :

                        Server.HtmlEncode(cell.CellValue.Text)

);

                sb.AppendFormat("</tr>");

        sb.Append("</table>");

        ltrTable.Text = sb.ToString();

咦，這是啥鬼? 怎麼CellValue.Text讀到的全是數字? 原本Excel中的文字內容全都不見了。

再深入了解，才知這是所謂Shared String Table的概念，將文字另外保存在共用字串表中，原本文字欄位所出現的數字，就是該文字在共用字串表中的序號。如此，若同一組文字在欄位中出現多次，以序號取代完整文字內容，可節省大量空間。

參考網路文章，取得WorkbookPart.SharedStringTablePart.SharedStringTable，在cell.DataType == CellValues.SharedString時，透過SharedStringTable.ChildElements[nnn].InnterText取值，就可以取得文字內容了。

using System;

using System.Linq;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml.Packaging;

using System.Text;

public partial class ReadExcel_Default2 : System.Web.UI.Page

    protected void Page_Load(object sender, EventArgs e)

        StringBuilder sb = new StringBuilder();

        sb.AppendFormat("<table border='1'>");

        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(

            Server.MapPath("基本資料檔.xlsx"), true))

            WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(

                      doc.WorkbookPart.Workbook.Descendants<Sheet>().First().Id

);

            Worksheet sheet = worksheetPart.Worksheet;

            //取得共用字串表

            SharedStringTable strTable =

                doc.WorkbookPart.SharedStringTablePart.SharedStringTable;

            foreach (Row row in sheet.Descendants<Row>())

                sb.AppendFormat("<tr>");

                foreach (Cell cell in row.Descendants<Cell>())

                    sb.AppendFormat(

                        "<td>{0}</td>",

                        GetCellText(cell, strTable)

);

                sb.AppendFormat("</tr>");

        sb.Append("</table>");

        ltrTable.Text = sb.ToString();

    //Ref http://blog.csdn.net/fzzsh/archive/2009/10/16/4682401.aspx

    private string GetCellText(Cell cell, SharedStringTable strTable)

        if (cell.ChildElements.Count == 0)

            return null;

        string val = cell.CellValue.InnerText;

        //若為共享字串時的處理邏輯

        if (cell.DataType != null && cell.DataType == CellValues.SharedString)

            val = strTable.ChildElements[int.Parse(val)].InnerText;

        return val;

不過，注意到了嗎? 我們以Cell.CellValue.Text取到的註冊日期是數字形式、積分也是未加千位號的原始數字形式... 原本在NPOI遇到問題，還是沒能解決呀!

參考一些網路文件，算是有了較深的體認。網路上絕大部分的文章都是在談怎麼用NPOI或Open XML SDK建立一份Excel或Word，交給Office程式開啟使用；再不然就是修改資料、套表應用之類，以讀取為目的的範例數量就少很多。再進一步檢視Excel文件結構，發現它其中保存的原本就只有欄位的"資料原始值"與"顯示格式"，或是"計算公式"，二者應如何呈現，在分工上是應用程式的職責。而公式運算及依格式顯示資料的邏輯頗為複雜，也算是Excel的精華所在，很難"順便"包含在Open XML SDK與NPOI的功能中，說起來也是合情合理。在MSDN論壇上看到有人發問關於透過Open XML SDK重算公式結果的需求，MS RD說明Open XML SDK原本就有其限制，並建議考慮Sharepoint的Excel Service，也差不多是類似的情境。

在應用上，我想Open XML SDK、NPOI可以充分勝任建立Excel、Word物件及一般讀取內容的需求；如果想擁有較完整的Excel、Word功能(例如: 修改某欄位的值後，立即取回經公式運算後的結果)，看來還是得回歸呼叫Office程式物件模型的路。

Comments

# 2010-12-20 12:11 AM by 小宇

請問版主，Office程式物件模型？指的是COM 元件嗎？

# 2010-12-20 12:32 AM by Jeffrey

to 小宇, Excel物件模型指的是: http://msdn.microsoft.com/zh-tw/library/wss56bz7(v=vs.80).aspx

# 2010-12-20 05:02 AM by 追尋

請問版主, 我的問題跟本篇無關抱歉請問您本站是用什麼架的? 也是.net 套裝軟體? 謝謝~

# 2010-12-20 07:40 PM by Jeffrey

to 追尋, 我Blog平台用的是Community Server 2007(有點古老了): http://goo.gl/4VJYX

# 2014-07-16 11:50 PM by Steven

這個好像就指定第一個的工作表請問如果要選不同的工作表，如何指定工作表名稱？

# 2014-07-17 12:58 AM by Jeffrey

to Steven , doc.WorkbookPart.Workbook.Descendants<Sheet>()會傳回工作表集合，再用Name屬性比對。參考: http://justgeeks.blogspot.tw/2012/02/getting-worksheet-based-on-sheet-name.html

# 2014-11-14 02:15 AM by 阿喵

基本上,根據我的經驗,修正該欄位的儲存格格式即可我常會遇到欄位值明明是數字,卻老是會抓到奇怪的字元,又不想特別針對奇怪字元去改程式,將該欄位的文字格式改成數值格式就好了日期也是,即使欄位填值,明明是明年的日期,可是,因為顯示格式只顯示月日,會因此被竄改成今年,但是,顯示格式改為年月日就好了!!!

# 2020-07-07 05:11 PM by Mark Flayd

Very interesting article! If you need any help you can use ZetExcel.com. It is very helpful

# 2021-12-17 10:53 AM by Ellis

非常感謝這篇教學，節省了許多摸索查找的時間，獻上分享教學的敬意。

# 2023-10-28 07:32 PM by Egg

最近也遇到字串讀到數字的問題，感謝版主寫了這邊文章幫我省下許多查找的時間。另外，關於數字格式的日期，現在可以用DateTime.FromOADate()去做轉換～

# 2023-10-29 12:05 PM by Jeffrey

to Egg, 感謝分享 FromOADate()，學到了。