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.

261 lines
7.2KB

  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. //
  5. // Package excelize providing a set of functions that allow you to write to
  6. // and read from XLSX files. Support reads and writes XLSX file generated by
  7. // Microsoft Excel™ 2007 and later. Support save file without losing original
  8. // charts of XLSX. This library needs Go version 1.10 or later.
  9. package excelize
  10. import (
  11. "archive/zip"
  12. "bytes"
  13. "fmt"
  14. "io"
  15. "log"
  16. "strconv"
  17. "strings"
  18. )
  19. // ReadZipReader can be used to read an XLSX in memory without touching the
  20. // filesystem.
  21. func ReadZipReader(r *zip.Reader) (map[string][]byte, int, error) {
  22. fileList := make(map[string][]byte, len(r.File))
  23. worksheets := 0
  24. for _, v := range r.File {
  25. fileList[v.Name] = readFile(v)
  26. if strings.HasPrefix(v.Name, "xl/worksheets/sheet") {
  27. worksheets++
  28. }
  29. }
  30. return fileList, worksheets, nil
  31. }
  32. // readXML provides a function to read XML content as string.
  33. func (f *File) readXML(name string) []byte {
  34. if content, ok := f.XLSX[name]; ok {
  35. return content
  36. }
  37. return []byte{}
  38. }
  39. // saveFileList provides a function to update given file content in file list
  40. // of XLSX.
  41. func (f *File) saveFileList(name string, content []byte) {
  42. newContent := make([]byte, 0, len(XMLHeader)+len(content))
  43. newContent = append(newContent, []byte(XMLHeader)...)
  44. newContent = append(newContent, content...)
  45. f.XLSX[name] = newContent
  46. }
  47. // Read file content as string in a archive file.
  48. func readFile(file *zip.File) []byte {
  49. rc, err := file.Open()
  50. if err != nil {
  51. log.Fatal(err)
  52. }
  53. dat := make([]byte, 0, file.FileInfo().Size())
  54. buff := bytes.NewBuffer(dat)
  55. _, _ = io.Copy(buff, rc)
  56. rc.Close()
  57. return buff.Bytes()
  58. }
  59. // SplitCellName splits cell name to column name and row number.
  60. //
  61. // Example:
  62. //
  63. // excelize.SplitCellName("AK74") // return "AK", 74, nil
  64. //
  65. func SplitCellName(cell string) (string, int, error) {
  66. alpha := func(r rune) bool {
  67. return ('A' <= r && r <= 'Z') || ('a' <= r && r <= 'z')
  68. }
  69. if strings.IndexFunc(cell, alpha) == 0 {
  70. i := strings.LastIndexFunc(cell, alpha)
  71. if i >= 0 && i < len(cell)-1 {
  72. col, rowstr := cell[:i+1], cell[i+1:]
  73. if row, err := strconv.Atoi(rowstr); err == nil && row > 0 {
  74. return col, row, nil
  75. }
  76. }
  77. }
  78. return "", -1, newInvalidCellNameError(cell)
  79. }
  80. // JoinCellName joins cell name from column name and row number.
  81. func JoinCellName(col string, row int) (string, error) {
  82. normCol := strings.Map(func(rune rune) rune {
  83. switch {
  84. case 'A' <= rune && rune <= 'Z':
  85. return rune
  86. case 'a' <= rune && rune <= 'z':
  87. return rune - 32
  88. }
  89. return -1
  90. }, col)
  91. if len(col) == 0 || len(col) != len(normCol) {
  92. return "", newInvalidColumnNameError(col)
  93. }
  94. if row < 1 {
  95. return "", newInvalidRowNumberError(row)
  96. }
  97. return fmt.Sprintf("%s%d", normCol, row), nil
  98. }
  99. // ColumnNameToNumber provides a function to convert Excel sheet column name
  100. // to int. Column name case insensitive. The function returns an error if
  101. // column name incorrect.
  102. //
  103. // Example:
  104. //
  105. // excelize.ColumnNameToNumber("AK") // returns 37, nil
  106. //
  107. func ColumnNameToNumber(name string) (int, error) {
  108. if len(name) == 0 {
  109. return -1, newInvalidColumnNameError(name)
  110. }
  111. col := 0
  112. multi := 1
  113. for i := len(name) - 1; i >= 0; i-- {
  114. r := name[i]
  115. if r >= 'A' && r <= 'Z' {
  116. col += int(r-'A'+1) * multi
  117. } else if r >= 'a' && r <= 'z' {
  118. col += int(r-'a'+1) * multi
  119. } else {
  120. return -1, newInvalidColumnNameError(name)
  121. }
  122. multi *= 26
  123. }
  124. return col, nil
  125. }
  126. // ColumnNumberToName provides a function to convert the integer to Excel
  127. // sheet column title.
  128. //
  129. // Example:
  130. //
  131. // excelize.ColumnNumberToName(37) // returns "AK", nil
  132. //
  133. func ColumnNumberToName(num int) (string, error) {
  134. if num < 1 {
  135. return "", fmt.Errorf("incorrect column number %d", num)
  136. }
  137. var col string
  138. for num > 0 {
  139. col = string((num-1)%26+65) + col
  140. num = (num - 1) / 26
  141. }
  142. return col, nil
  143. }
  144. // CellNameToCoordinates converts alphanumeric cell name to [X, Y] coordinates
  145. // or returns an error.
  146. //
  147. // Example:
  148. //
  149. // CellCoordinates("A1") // returns 1, 1, nil
  150. // CellCoordinates("Z3") // returns 26, 3, nil
  151. //
  152. func CellNameToCoordinates(cell string) (int, int, error) {
  153. const msg = "cannot convert cell %q to coordinates: %v"
  154. colname, row, err := SplitCellName(cell)
  155. if err != nil {
  156. return -1, -1, fmt.Errorf(msg, cell, err)
  157. }
  158. col, err := ColumnNameToNumber(colname)
  159. if err != nil {
  160. return -1, -1, fmt.Errorf(msg, cell, err)
  161. }
  162. return col, row, nil
  163. }
  164. // CoordinatesToCellName converts [X, Y] coordinates to alpha-numeric cell
  165. // name or returns an error.
  166. //
  167. // Example:
  168. //
  169. // CoordinatesToCellName(1, 1) // returns "A1", nil
  170. //
  171. func CoordinatesToCellName(col, row int) (string, error) {
  172. if col < 1 || row < 1 {
  173. return "", fmt.Errorf("invalid cell coordinates [%d, %d]", col, row)
  174. }
  175. colname, err := ColumnNumberToName(col)
  176. if err != nil {
  177. return "", fmt.Errorf("invalid cell coordinates [%d, %d]: %v", col, row, err)
  178. }
  179. return fmt.Sprintf("%s%d", colname, row), nil
  180. }
  181. // boolPtr returns a pointer to a bool with the given value.
  182. func boolPtr(b bool) *bool { return &b }
  183. // defaultTrue returns true if b is nil, or the pointed value.
  184. func defaultTrue(b *bool) bool {
  185. if b == nil {
  186. return true
  187. }
  188. return *b
  189. }
  190. // parseFormatSet provides a method to convert format string to []byte and
  191. // handle empty string.
  192. func parseFormatSet(formatSet string) []byte {
  193. if formatSet != "" {
  194. return []byte(formatSet)
  195. }
  196. return []byte("{}")
  197. }
  198. // namespaceStrictToTransitional provides a method to convert Strict and
  199. // Transitional namespaces.
  200. func namespaceStrictToTransitional(content []byte) []byte {
  201. var namespaceTranslationDic = map[string]string{
  202. StrictSourceRelationship: SourceRelationship,
  203. StrictSourceRelationshipChart: SourceRelationshipChart,
  204. StrictSourceRelationshipComments: SourceRelationshipComments,
  205. StrictSourceRelationshipImage: SourceRelationshipImage,
  206. StrictNameSpaceSpreadSheet: NameSpaceSpreadSheet,
  207. }
  208. for s, n := range namespaceTranslationDic {
  209. content = bytes.Replace(content, []byte(s), []byte(n), -1)
  210. }
  211. return content
  212. }
  213. // genSheetPasswd provides a method to generate password for worksheet
  214. // protection by given plaintext. When an Excel sheet is being protected with
  215. // a password, a 16-bit (two byte) long hash is generated. To verify a
  216. // password, it is compared to the hash. Obviously, if the input data volume
  217. // is great, numerous passwords will match the same hash. Here is the
  218. // algorithm to create the hash value:
  219. //
  220. // take the ASCII values of all characters shift left the first character 1 bit,
  221. // the second 2 bits and so on (use only the lower 15 bits and rotate all higher bits,
  222. // the highest bit of the 16-bit value is always 0 [signed short])
  223. // XOR all these values
  224. // XOR the count of characters
  225. // XOR the constant 0xCE4B
  226. func genSheetPasswd(plaintext string) string {
  227. var password int64 = 0x0000
  228. var charPos uint = 1
  229. for _, v := range plaintext {
  230. value := int64(v) << charPos
  231. charPos++
  232. rotatedBits := value >> 15 // rotated bits beyond bit 15
  233. value &= 0x7fff // first 15 bits
  234. password ^= (value | rotatedBits)
  235. }
  236. password ^= int64(len(plaintext))
  237. password ^= 0xCE4B
  238. return strings.ToUpper(strconv.FormatInt(password, 16))
  239. }