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.

335 lines
11KB

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