You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 

364 lines
11 KiB

  1. // Copyright 2016 - 2020 The excelize Authors. All rights reserved. Use of
  2. // this source code is governed by a BSD-style license that can be found in
  3. // the LICENSE file.
  4. // Package excelize providing a set of functions that allow you to write to
  5. // and read from XLSX files. Support reads and writes XLSX file generated by
  6. // Microsoft Excel™ 2007 and later. Support save file without losing original
  7. // charts of XLSX. This library needs Go version 1.10 or later.
  8. //
  9. // See https://xuri.me/excelize for more information about this package.
  10. package excelize
  11. import (
  12. "archive/zip"
  13. "bytes"
  14. "encoding/xml"
  15. "errors"
  16. "fmt"
  17. "io"
  18. "io/ioutil"
  19. "os"
  20. "path"
  21. "strconv"
  22. "strings"
  23. "golang.org/x/net/html/charset"
  24. )
  25. // File define a populated XLSX file struct.
  26. type File struct {
  27. checked map[string]bool
  28. sheetMap map[string]string
  29. CalcChain *xlsxCalcChain
  30. Comments map[string]*xlsxComments
  31. ContentTypes *xlsxTypes
  32. Drawings map[string]*xlsxWsDr
  33. Path string
  34. SharedStrings *xlsxSST
  35. Sheet map[string]*xlsxWorksheet
  36. SheetCount int
  37. Styles *xlsxStyleSheet
  38. Theme *xlsxTheme
  39. DecodeVMLDrawing map[string]*decodeVmlDrawing
  40. VMLDrawing map[string]*vmlDrawing
  41. WorkBook *xlsxWorkbook
  42. Relationships map[string]*xlsxRelationships
  43. XLSX map[string][]byte
  44. CharsetReader charsetTranscoderFn
  45. }
  46. type charsetTranscoderFn func(charset string, input io.Reader) (rdr io.Reader, err error)
  47. // OpenFile take the name of an XLSX file and returns a populated XLSX file
  48. // struct for it.
  49. func OpenFile(filename string) (*File, error) {
  50. file, err := os.Open(filename)
  51. if err != nil {
  52. return nil, err
  53. }
  54. defer file.Close()
  55. f, err := OpenReader(file)
  56. if err != nil {
  57. return nil, err
  58. }
  59. f.Path = filename
  60. return f, nil
  61. }
  62. // newFile is object builder
  63. func newFile() *File {
  64. return &File{
  65. checked: make(map[string]bool),
  66. sheetMap: make(map[string]string),
  67. Comments: make(map[string]*xlsxComments),
  68. Drawings: make(map[string]*xlsxWsDr),
  69. Sheet: make(map[string]*xlsxWorksheet),
  70. DecodeVMLDrawing: make(map[string]*decodeVmlDrawing),
  71. VMLDrawing: make(map[string]*vmlDrawing),
  72. Relationships: make(map[string]*xlsxRelationships),
  73. CharsetReader: charset.NewReaderLabel,
  74. }
  75. }
  76. // OpenReader take an io.Reader and return a populated XLSX file.
  77. func OpenReader(r io.Reader) (*File, error) {
  78. b, err := ioutil.ReadAll(r)
  79. if err != nil {
  80. return nil, err
  81. }
  82. zr, err := zip.NewReader(bytes.NewReader(b), int64(len(b)))
  83. if err != nil {
  84. identifier := []byte{
  85. // checking protect workbook by [MS-OFFCRYPTO] - v20181211 3.1 FeatureIdentifier
  86. 0x3c, 0x00, 0x00, 0x00, 0x4d, 0x00, 0x69, 0x00, 0x63, 0x00, 0x72, 0x00, 0x6f, 0x00, 0x73, 0x00,
  87. 0x6f, 0x00, 0x66, 0x00, 0x74, 0x00, 0x2e, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6e, 0x00, 0x74, 0x00,
  88. 0x61, 0x00, 0x69, 0x00, 0x6e, 0x00, 0x65, 0x00, 0x72, 0x00, 0x2e, 0x00, 0x44, 0x00, 0x61, 0x00,
  89. 0x74, 0x00, 0x61, 0x00, 0x53, 0x00, 0x70, 0x00, 0x61, 0x00, 0x63, 0x00, 0x65, 0x00, 0x73, 0x00,
  90. 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00,
  91. }
  92. if bytes.Contains(b, identifier) {
  93. return nil, errors.New("not support encrypted file currently")
  94. }
  95. return nil, err
  96. }
  97. file, sheetCount, err := ReadZipReader(zr)
  98. if err != nil {
  99. return nil, err
  100. }
  101. f := newFile()
  102. f.SheetCount, f.XLSX = sheetCount, file
  103. f.CalcChain = f.calcChainReader()
  104. f.sheetMap = f.getSheetMap()
  105. f.Styles = f.stylesReader()
  106. f.Theme = f.themeReader()
  107. return f, nil
  108. }
  109. // CharsetTranscoder Set user defined codepage transcoder function for open
  110. // XLSX from non UTF-8 encoding.
  111. func (f *File) CharsetTranscoder(fn charsetTranscoderFn) *File { f.CharsetReader = fn; return f }
  112. // Creates new XML decoder with charset reader.
  113. func (f *File) xmlNewDecoder(rdr io.Reader) (ret *xml.Decoder) {
  114. ret = xml.NewDecoder(rdr)
  115. ret.CharsetReader = f.CharsetReader
  116. return
  117. }
  118. // setDefaultTimeStyle provides a function to set default numbers format for
  119. // time.Time type cell value by given worksheet name, cell coordinates and
  120. // number format code.
  121. func (f *File) setDefaultTimeStyle(sheet, axis string, format int) error {
  122. s, err := f.GetCellStyle(sheet, axis)
  123. if err != nil {
  124. return err
  125. }
  126. if s == 0 {
  127. style, _ := f.NewStyle(`{"number_format": ` + strconv.Itoa(format) + `}`)
  128. _ = f.SetCellStyle(sheet, axis, axis, style)
  129. }
  130. return err
  131. }
  132. // workSheetReader provides a function to get the pointer to the structure
  133. // after deserialization by given worksheet name.
  134. func (f *File) workSheetReader(sheet string) (xlsx *xlsxWorksheet, err error) {
  135. var (
  136. name string
  137. ok bool
  138. )
  139. if name, ok = f.sheetMap[trimSheetName(sheet)]; !ok {
  140. err = fmt.Errorf("sheet %s is not exist", sheet)
  141. return
  142. }
  143. if xlsx = f.Sheet[name]; f.Sheet[name] == nil {
  144. xlsx = new(xlsxWorksheet)
  145. if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(name)))).
  146. Decode(xlsx); err != nil && err != io.EOF {
  147. err = fmt.Errorf("xml decode error: %s", err)
  148. return
  149. }
  150. err = nil
  151. if f.checked == nil {
  152. f.checked = make(map[string]bool)
  153. }
  154. if ok = f.checked[name]; !ok {
  155. checkSheet(xlsx)
  156. if err = checkRow(xlsx); err != nil {
  157. return
  158. }
  159. f.checked[name] = true
  160. }
  161. f.Sheet[name] = xlsx
  162. }
  163. return
  164. }
  165. // checkSheet provides a function to fill each row element and make that is
  166. // continuous in a worksheet of XML.
  167. func checkSheet(xlsx *xlsxWorksheet) {
  168. row := len(xlsx.SheetData.Row)
  169. if row >= 1 {
  170. lastRow := xlsx.SheetData.Row[row-1].R
  171. if lastRow >= row {
  172. row = lastRow
  173. }
  174. }
  175. sheetData := xlsxSheetData{Row: make([]xlsxRow, row)}
  176. for _, r := range xlsx.SheetData.Row {
  177. sheetData.Row[r.R-1] = r
  178. }
  179. for i := 1; i <= row; i++ {
  180. sheetData.Row[i-1].R = i
  181. }
  182. xlsx.SheetData = sheetData
  183. }
  184. // addRels provides a function to add relationships by given XML path,
  185. // relationship type, target and target mode.
  186. func (f *File) addRels(relPath, relType, target, targetMode string) int {
  187. rels := f.relsReader(relPath)
  188. if rels == nil {
  189. rels = &xlsxRelationships{}
  190. }
  191. var rID int
  192. for _, rel := range rels.Relationships {
  193. ID, _ := strconv.Atoi(strings.TrimPrefix(rel.ID, "rId"))
  194. if ID > rID {
  195. rID = ID
  196. }
  197. }
  198. rID++
  199. var ID bytes.Buffer
  200. ID.WriteString("rId")
  201. ID.WriteString(strconv.Itoa(rID))
  202. rels.Relationships = append(rels.Relationships, xlsxRelationship{
  203. ID: ID.String(),
  204. Type: relType,
  205. Target: target,
  206. TargetMode: targetMode,
  207. })
  208. f.Relationships[relPath] = rels
  209. return rID
  210. }
  211. // replaceWorkSheetsRelationshipsNameSpaceBytes provides a function to replace
  212. // xl/worksheets/sheet%d.xml XML tags to self-closing for compatible Microsoft
  213. // Office Excel 2007.
  214. func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte {
  215. var oldXmlns = []byte(`<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
  216. var newXmlns = []byte(`<worksheet` + templateNamespaceIDMap)
  217. workbookMarshal = bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1)
  218. return workbookMarshal
  219. }
  220. // replaceStyleRelationshipsNameSpaceBytes provides a function to replace
  221. // xl/styles.xml XML tags to self-closing for compatible Microsoft Office
  222. // Excel 2007.
  223. func replaceStyleRelationshipsNameSpaceBytes(contentMarshal []byte) []byte {
  224. var oldXmlns = []byte(`<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`)
  225. var newXmlns = []byte(`<styleSheet` + templateNamespaceIDMap)
  226. contentMarshal = bytes.Replace(contentMarshal, oldXmlns, newXmlns, -1)
  227. return contentMarshal
  228. }
  229. // UpdateLinkedValue fix linked values within a spreadsheet are not updating in
  230. // Office Excel 2007 and 2010. This function will be remove value tag when met a
  231. // cell have a linked value. Reference
  232. // https://social.technet.microsoft.com/Forums/office/en-US/e16bae1f-6a2c-4325-8013-e989a3479066/excel-2010-linked-cells-not-updating
  233. //
  234. // Notice: after open XLSX file Excel will be update linked value and generate
  235. // new value and will prompt save file or not.
  236. //
  237. // For example:
  238. //
  239. // <row r="19" spans="2:2">
  240. // <c r="B19">
  241. // <f>SUM(Sheet2!D2,Sheet2!D11)</f>
  242. // <v>100</v>
  243. // </c>
  244. // </row>
  245. //
  246. // to
  247. //
  248. // <row r="19" spans="2:2">
  249. // <c r="B19">
  250. // <f>SUM(Sheet2!D2,Sheet2!D11)</f>
  251. // </c>
  252. // </row>
  253. //
  254. func (f *File) UpdateLinkedValue() error {
  255. for _, name := range f.GetSheetMap() {
  256. xlsx, err := f.workSheetReader(name)
  257. if err != nil {
  258. return err
  259. }
  260. for indexR := range xlsx.SheetData.Row {
  261. for indexC, col := range xlsx.SheetData.Row[indexR].C {
  262. if col.F != nil && col.V != "" {
  263. xlsx.SheetData.Row[indexR].C[indexC].V = ""
  264. xlsx.SheetData.Row[indexR].C[indexC].T = ""
  265. }
  266. }
  267. }
  268. }
  269. return nil
  270. }
  271. // AddVBAProject provides the method to add vbaProject.bin file which contains
  272. // functions and/or macros. The file extension should be .xlsm. For example:
  273. //
  274. // if err := f.SetSheetPrOptions("Sheet1", excelize.CodeName("Sheet1")); err != nil {
  275. // fmt.Println(err)
  276. // }
  277. // if err := f.AddVBAProject("vbaProject.bin"); err != nil {
  278. // fmt.Println(err)
  279. // }
  280. // if err := f.SaveAs("macros.xlsm"); err != nil {
  281. // fmt.Println(err)
  282. // }
  283. //
  284. func (f *File) AddVBAProject(bin string) error {
  285. var err error
  286. // Check vbaProject.bin exists first.
  287. if _, err = os.Stat(bin); os.IsNotExist(err) {
  288. return err
  289. }
  290. if path.Ext(bin) != ".bin" {
  291. return errors.New("unsupported VBA project extension")
  292. }
  293. f.setContentTypePartVBAProjectExtensions()
  294. wb := f.relsReader("xl/_rels/workbook.xml.rels")
  295. var rID int
  296. var ok bool
  297. for _, rel := range wb.Relationships {
  298. if rel.Target == "vbaProject.bin" && rel.Type == SourceRelationshipVBAProject {
  299. ok = true
  300. continue
  301. }
  302. t, _ := strconv.Atoi(strings.TrimPrefix(rel.ID, "rId"))
  303. if t > rID {
  304. rID = t
  305. }
  306. }
  307. rID++
  308. if !ok {
  309. wb.Relationships = append(wb.Relationships, xlsxRelationship{
  310. ID: "rId" + strconv.Itoa(rID),
  311. Target: "vbaProject.bin",
  312. Type: SourceRelationshipVBAProject,
  313. })
  314. }
  315. file, _ := ioutil.ReadFile(bin)
  316. f.XLSX["xl/vbaProject.bin"] = file
  317. return err
  318. }
  319. // setContentTypePartVBAProjectExtensions provides a function to set the
  320. // content type for relationship parts and the main document part.
  321. func (f *File) setContentTypePartVBAProjectExtensions() {
  322. var ok bool
  323. content := f.contentTypesReader()
  324. for _, v := range content.Defaults {
  325. if v.Extension == "bin" {
  326. ok = true
  327. }
  328. }
  329. for idx, o := range content.Overrides {
  330. if o.PartName == "/xl/workbook.xml" {
  331. content.Overrides[idx].ContentType = "application/vnd.ms-excel.sheet.macroEnabled.main+xml"
  332. }
  333. }
  334. if !ok {
  335. content.Defaults = append(content.Defaults, xlsxDefault{
  336. Extension: "bin",
  337. ContentType: "application/vnd.ms-office.vbaProject",
  338. })
  339. }
  340. }