一般情況下都不需要特殊的 API 的函式庫便可以利用純文字編輯器編製
但 CSV 這類資料格式只能包含資料並不包含樣式,需要以樣式顯示 CSV 顯然不足
雖然在下有提及使用 OpenDocument Spreadsheet 的製作方法
遺憾的是 OpenDocument 的使用者還是比 Microsoft Office 少
基本上 Microsoft Excel 以 XLS 格式儲存是一種二進制檔案,要使用純文字編輯器製作二進制檔案一般而言都不太可能
但 Microsoft Excel 除了 XLS 格式外還可以儲存為 Microsoft Excel XML 的格式
由於 XML 是一種純文字文件﹐這樣便可以利用純文字編輯器製作可讓 Microsoft Excel 顯示的試算表同時擁有樣式
事實上 Microsoft Excel XML 的結構並非想像上複雜,結構大致上與 HTML 的 Table 相似,比 ODS 還要簡單
Microsoft Excel XML 的結構大致如下
1 2 3 4 5 6 7 8 | <? xml version = "1.0" ?> <? mso-application progid = "Excel.Sheet" ?> < Workbook xmlns = "urn:schemas-microsoft-com:office:spreadsheet" xmlns:o = "urn:schemas-microsoft-com:office:office" xmlns:x = "urn:schemas-microsoft-com:office:excel" xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet" xmlns:html = "http://www.w3.org/TR/REC-html40" > < Styles > </ Styles > < Worksheet ss:Name = "Sheet1" > </ Worksheet > </ Workbook > |
Worksheet 保存試算表的資料,而 ss:Name 就是試算表的名稱
主要保存資料格的樣式 (除了資料格樣式還有欄、列、表的樣式,但在下在這裡不作太多介紹)
資料格的樣式大致如下
1 2 3 4 5 6 7 8 9 10 11 | < Style ss:ID = "ce-0-0" > < Alignment ss:Horizontal = "Left" ss:Vertical = "Center" /> < Borders > < Border ss:Position = "Top" ss:LineStyle = "Continuous" ss:Weight = "1" ss:Color = "#000000" /> < Border ss:Position = "Left" ss:LineStyle = "Dash" ss:Weight = "1" ss:Color = "#000000" /> < Border ss:Position = "Right" ss:LineStyle = "Dot" ss:Weight = "1" ss:Color = "#000000" /> < Border ss:Position = "Bottom" ss:LineStyle = "Continuous" ss:Weight = "1" ss:Color = "#000000" /> </ Borders > < Interior ss:Color = "#ffffff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Bold = "1" ss:Italic = "1" ss:Color = "#000000" /> </ Style > |
Alignment 為設定對應資料格中的文字的垂直及水平位置
ss:Horizontal 為水平位置,可選擇 Left, Center, Right
ss:Vertical 為垂值位置,可選擇 Top, Center, Bottom
Borders 為邊框,包含 4 個 Border 必須指定 ss:Position 分別有 Top Left Right Bottom
Border 的 ss:LineStyle 為 Continuous (實線), Dash (虛線), Dot (點線)
ss:Weight 為厚度,為正數值,但不能大於 3,沒有單位
ss:Color 為顏色,為十六進制 RGB 值,必須加上前綴 #
Interior 為背景
ss:Color 為顏色,為十六進制 RGB 值,必須加上前綴 # 與邊框的 ss:Color 相同
ss:Pattern 為填色效果,一般會使用 Solid 即填滿 (其實還可以使用點格等填色效果,但在下在這裡不作太多介紹
Font 是文字
x:CharSet 為編碼,一般會使用 136
ss:Size 為大小,為正數值
ss:Bold 為粗體設定,為 1 生效
ss:Italic 為斜體設定,為 1 生效
ss:Color 為顏色,為十六進制 RGB 值,必須加上前綴 # 與邊框的 ss:Color 相同
資料大致如下
1 2 3 4 5 6 | < Table > < Column ss:Width = "20" /> < Row ss:Height = "20" > < Cell ss:StyleID = "ce-0-0" >< Data ss:Type = "String" > <![CDATA[data]]> </ Data ></ Cell > </ Row > </ Table > |
ss:Height 為列高度,為正數值,沒有單位
ss:StyleID 為資料格的樣式,匹配對應樣式的 ID ,但若沒有對應樣式的 ID 則不能提供不存在的 ID 否則會解析錯誤
ss:Type 為資料格式型態,除了 String 還有 Number, Date, Time (但這裡不作詳細說明)
使用 Column 樣式時,需要匹配試算表的欄數量
另外還可以設定分割試算表,分割資料存放於對應的 Worksheet 中
分割大致如下
1 2 3 4 5 6 7 | < WorksheetOptions xmlns = "urn:schemas-microsoft-com:office:excel" > < FreezePanes /> < SplitHorizontal >1</ SplitHorizontal > < TopRowBottomPane >1</ TopRowBottomPane > < SplitVertical >1</ SplitVertical > < LeftColumnRightPane >1</ LeftColumnRightPane > </ WorksheetOptions > |
SplitHorizontal 及 TopRowBottomPane 分別為水平分割的列數及顯示開始的列數量,一般情況兩者的值數都是相等
SplitVertical 及 LeftColumnRightPane 分別為垂直分割的欄數及顯示開始的欄數量,一般情況兩者的值數都是相等
簡單例子
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | <? xml version = "1.0" ?> <? mso-application progid = "Excel.Sheet" ?> < Workbook xmlns = "urn:schemas-microsoft-com:office:spreadsheet" xmlns:o = "urn:schemas-microsoft-com:office:office" xmlns:x = "urn:schemas-microsoft-com:office:excel" xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet" xmlns:html = "http://www.w3.org/TR/REC-html40" > < Styles > < Style ss:ID = "ce-0-0" > < Alignment ss:Horizontal = "Left" ss:Vertical = "Top" /> < Interior ss:Color = "#ffffff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Bold = "1" ss:Color = "#000000" /> </ Style > < Style ss:ID = "ce-0-1" > < Alignment ss:Horizontal = "Center" ss:Vertical = "Top" /> < Interior ss:Color = "#ffff00" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Italic = "1" ss:Color = "#0000ff" /> </ Style > < Style ss:ID = "ce-0-2" > < Alignment ss:Horizontal = "Right" ss:Vertical = "Top" /> < Interior ss:Color = "#ff00ff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Color = "#00ff00" /> </ Style > < Style ss:ID = "ce-1-0" > < Alignment ss:Horizontal = "Left" ss:Vertical = "Center" /> < Interior ss:Color = "#00ffff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Bold = "1" ss:Color = "#ff0000" /> </ Style > < Style ss:ID = "ce-1-1" > < Alignment ss:Horizontal = "Center" ss:Vertical = "Center" /> < Borders > < Border ss:Position = "Top" ss:LineStyle = "Continuous" ss:Weight = "3" ss:Color = "#00ffff" /> < Border ss:Position = "Left" ss:LineStyle = "Dash" ss:Weight = "3" ss:Color = "#0000ff" /> < Border ss:Position = "Right" ss:LineStyle = "Dot" ss:Weight = "3" ss:Color = "#000000" /> < Border ss:Position = "Bottom" ss:LineStyle = "Continuous" ss:Weight = "3" ss:Color = "#ffffff" /> </ Borders > < Interior ss:Color = "#ff0000" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Italic = "1" ss:Color = "#00ffff" /> </ Style > < Style ss:ID = "ce-1-2" > < Alignment ss:Horizontal = "Right" ss:Vertical = "Center" /> < Interior ss:Color = "#00ff00" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Color = "#ff00ff" /> </ Style > < Style ss:ID = "ce-2-0" > < Alignment ss:Horizontal = "Left" ss:Vertical = "Bottom" /> < Interior ss:Color = "#0000ff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Color = "#ffff00" /> </ Style > < Style ss:ID = "ce-2-1" > < Alignment ss:Horizontal = "Center" ss:Vertical = "Bottom" /> < Interior ss:Color = "#000000" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Color = "#ffffff" /> </ Style > < Style ss:ID = "ce-2-2" > < Alignment ss:Horizontal = "Right" ss:Vertical = "Bottom" /> < Interior ss:Color = "#ffffff" ss:Pattern = "Solid" /> < Font x:CharSet = "136" ss:Size = "8" ss:Color = "#000000" /> </ Style > </ Styles > < Worksheet ss:Name = "Sheet1" > < WorksheetOptions xmlns = "urn:schemas-microsoft-com:office:excel" > < FreezePanes /> < SplitHorizontal >1</ SplitHorizontal > < TopRowBottomPane >1</ TopRowBottomPane > < SplitVertical >1</ SplitVertical > < LeftColumnRightPane >1</ LeftColumnRightPane > </ WorksheetOptions > < Table > < Column ss:Width = "100" /> < Column ss:Width = "100" /> < Column ss:Width = "100" /> < Row ss:Height = "100" > < Cell ss:StyleID = "ce-0-0" >< Data ss:Type = "String" > <![CDATA[row=1,column=A]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-0-1" >< Data ss:Type = "String" > <![CDATA[row=1,column=B]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-0-2" >< Data ss:Type = "String" > <![CDATA[row=1,column=C]]> </ Data ></ Cell > </ Row > < Row ss:Height = "100" > < Cell ss:StyleID = "ce-1-0" >< Data ss:Type = "String" > <![CDATA[row=2,column=A]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-1-1" >< Data ss:Type = "String" > <![CDATA[row=2,column=B]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-1-2" >< Data ss:Type = "String" > <![CDATA[row=2,column=C]]> </ Data ></ Cell > </ Row > < Row ss:Height = "100" > < Cell ss:StyleID = "ce-2-0" >< Data ss:Type = "String" > <![CDATA[row=3,column=A]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-2-1" >< Data ss:Type = "String" > <![CDATA[row=3,column=B]]> </ Data ></ Cell > < Cell ss:StyleID = "ce-2-2" >< Data ss:Type = "String" > <![CDATA[row=3,column=C]]> </ Data ></ Cell > </ Row > </ Table > </ Worksheet > </ Workbook > |

透過這種 XML 格式便可以只使用純文字編輯器來製作 Microsoft Excel 文件
雖然不是十分充足的資料,但如果需要匯出 Microsoft Excel 格式的資料
這種方法便可以在不同平台上不需使用特定函式庫便可以製作 Microsoft Excel 文件
但必須留意,假如 XML 不乎合 Microsoft Excel XML 結構,會發生錯誤,導致不能正常開啟該檔案
沒有留言 :
張貼留言