因專案需引用使用者提供的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

# by 小宇

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

# by Jeffrey

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

# by 追尋

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

# by Jeffrey

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

# by Steven

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

# by Jeffrey

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

# by 阿喵

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

# by Mark Flayd

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

# by Ellis

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

# by Egg

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

# by Jeffrey

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

Post a comment