使用Open XML SDK讀取Excel中的文字
11 | 27,783 |
因專案需引用使用者提供的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 ? " " :
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(),學到了。