Nevar pievienot vairāk kā 25 tēmas Tēmai ir jāsākas ar burtu vai ciparu, tā var saturēt domu zīmes ('-') un var būt līdz 35 simboliem gara.
 
 
 
 

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