file.go 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414
  1. package xlsx
  2. import (
  3. "archive/zip"
  4. "bytes"
  5. "encoding/xml"
  6. "errors"
  7. "fmt"
  8. "io"
  9. "os"
  10. "strconv"
  11. "strings"
  12. "unicode/utf8"
  13. )
  14. // File is a high level structure providing a slice of Sheet structs
  15. // to the user.
  16. type File struct {
  17. worksheets map[string]*zip.File
  18. referenceTable *RefTable
  19. Date1904 bool
  20. styles *xlsxStyleSheet
  21. Sheets []*Sheet
  22. Sheet map[string]*Sheet
  23. theme *theme
  24. DefinedNames []*xlsxDefinedName
  25. }
  26. const NoRowLimit int = -1
  27. // Create a new File
  28. func NewFile() *File {
  29. return &File{
  30. Sheet: make(map[string]*Sheet),
  31. Sheets: make([]*Sheet, 0),
  32. DefinedNames: make([]*xlsxDefinedName, 0),
  33. }
  34. }
  35. // OpenFile() take the name of an XLSX file and returns a populated
  36. // xlsx.File struct for it.
  37. func OpenFile(fileName string) (file *File, err error) {
  38. return OpenFileWithRowLimit(fileName, NoRowLimit)
  39. }
  40. // OpenFileWithRowLimit() will open the file, but will only read the specified number of rows.
  41. // If you save this file, it will be truncated to the number of rows specified.
  42. func OpenFileWithRowLimit(fileName string, rowLimit int) (file *File, err error) {
  43. var z *zip.ReadCloser
  44. z, err = zip.OpenReader(fileName)
  45. if err != nil {
  46. return nil, err
  47. }
  48. return ReadZipWithRowLimit(z, rowLimit)
  49. }
  50. // OpenBinary() take bytes of an XLSX file and returns a populated
  51. // xlsx.File struct for it.
  52. func OpenBinary(bs []byte) (*File, error) {
  53. return OpenBinaryWithRowLimit(bs, NoRowLimit)
  54. }
  55. // OpenBinaryWithRowLimit() take bytes of an XLSX file and returns a populated
  56. // xlsx.File struct for it.
  57. func OpenBinaryWithRowLimit(bs []byte, rowLimit int) (*File, error) {
  58. r := bytes.NewReader(bs)
  59. return OpenReaderAtWithRowLimit(r, int64(r.Len()), rowLimit)
  60. }
  61. // OpenReaderAt() take io.ReaderAt of an XLSX file and returns a populated
  62. // xlsx.File struct for it.
  63. func OpenReaderAt(r io.ReaderAt, size int64) (*File, error) {
  64. return OpenReaderAtWithRowLimit(r, size, NoRowLimit)
  65. }
  66. // OpenReaderAtWithRowLimit() take io.ReaderAt of an XLSX file and returns a populated
  67. // xlsx.File struct for it.
  68. func OpenReaderAtWithRowLimit(r io.ReaderAt, size int64, rowLimit int) (*File, error) {
  69. file, err := zip.NewReader(r, size)
  70. if err != nil {
  71. return nil, err
  72. }
  73. return ReadZipReaderWithRowLimit(file, rowLimit)
  74. }
  75. // A convenient wrapper around File.ToSlice, FileToSlice will
  76. // return the raw data contained in an Excel XLSX file as three
  77. // dimensional slice. The first index represents the sheet number,
  78. // the second the row number, and the third the cell number.
  79. //
  80. // For example:
  81. //
  82. // var mySlice [][][]string
  83. // var value string
  84. // mySlice = xlsx.FileToSlice("myXLSX.xlsx")
  85. // value = mySlice[0][0][0]
  86. //
  87. // Here, value would be set to the raw value of the cell A1 in the
  88. // first sheet in the XLSX file.
  89. func FileToSlice(path string) ([][][]string, error) {
  90. f, err := OpenFile(path)
  91. if err != nil {
  92. return nil, err
  93. }
  94. return f.ToSlice()
  95. }
  96. // FileToSliceUnmerged is a wrapper around File.ToSliceUnmerged.
  97. // It returns the raw data contained in an Excel XLSX file as three
  98. // dimensional slice. Merged cells will be unmerged. Covered cells become the
  99. // values of theirs origins.
  100. func FileToSliceUnmerged(path string) ([][][]string, error) {
  101. f, err := OpenFile(path)
  102. if err != nil {
  103. return nil, err
  104. }
  105. return f.ToSliceUnmerged()
  106. }
  107. // Save the File to an xlsx file at the provided path.
  108. func (f *File) Save(path string) (err error) {
  109. target, err := os.Create(path)
  110. if err != nil {
  111. return err
  112. }
  113. err = f.Write(target)
  114. if err != nil {
  115. return err
  116. }
  117. return target.Close()
  118. }
  119. // Write the File to io.Writer as xlsx
  120. func (f *File) Write(writer io.Writer) (err error) {
  121. parts, err := f.MarshallParts()
  122. if err != nil {
  123. return
  124. }
  125. zipWriter := zip.NewWriter(writer)
  126. for partName, part := range parts {
  127. w, err := zipWriter.Create(partName)
  128. if err != nil {
  129. return err
  130. }
  131. _, err = w.Write([]byte(part))
  132. if err != nil {
  133. return err
  134. }
  135. }
  136. return zipWriter.Close()
  137. }
  138. // AddSheet Add a new Sheet, with the provided name, to a File.
  139. // The minimum sheet name length is 1 character. If the sheet name length is less an error is thrown.
  140. // The maximum sheet name length is 31 characters. If the sheet name length is exceeded an error is thrown.
  141. // These special characters are also not allowed: : \ / ? * [ ]
  142. func (f *File) AddSheet(sheetName string) (*Sheet, error) {
  143. if _, exists := f.Sheet[sheetName]; exists {
  144. return nil, fmt.Errorf("duplicate sheet name '%s'.", sheetName)
  145. }
  146. runeLength := utf8.RuneCountInString(sheetName)
  147. if runeLength > 31 || runeLength == 0 {
  148. return nil, fmt.Errorf("sheet name must be 31 or fewer characters long. It is currently '%d' characters long", runeLength)
  149. }
  150. // Iterate over the runes
  151. for _, r := range sheetName {
  152. // Excel forbids : \ / ? * [ ]
  153. if r == ':' || r == '\\' || r == '/' || r == '?' || r == '*' || r == '[' || r == ']' {
  154. return nil, fmt.Errorf("sheet name must not contain any restricted characters : \\ / ? * [ ] but contains '%s'", string(r))
  155. }
  156. }
  157. sheet := &Sheet{
  158. Name: sheetName,
  159. File: f,
  160. Selected: len(f.Sheets) == 0,
  161. }
  162. f.Sheet[sheetName] = sheet
  163. f.Sheets = append(f.Sheets, sheet)
  164. return sheet, nil
  165. }
  166. // Appends an existing Sheet, with the provided name, to a File
  167. func (f *File) AppendSheet(sheet Sheet, sheetName string) (*Sheet, error) {
  168. if _, exists := f.Sheet[sheetName]; exists {
  169. return nil, fmt.Errorf("duplicate sheet name '%s'.", sheetName)
  170. }
  171. sheet.Name = sheetName
  172. sheet.File = f
  173. sheet.Selected = len(f.Sheets) == 0
  174. f.Sheet[sheetName] = &sheet
  175. f.Sheets = append(f.Sheets, &sheet)
  176. return &sheet, nil
  177. }
  178. func (f *File) makeWorkbook() xlsxWorkbook {
  179. return xlsxWorkbook{
  180. FileVersion: xlsxFileVersion{AppName: "Go XLSX"},
  181. WorkbookPr: xlsxWorkbookPr{ShowObjects: "all"},
  182. BookViews: xlsxBookViews{
  183. WorkBookView: []xlsxWorkBookView{
  184. {
  185. ShowHorizontalScroll: true,
  186. ShowSheetTabs: true,
  187. ShowVerticalScroll: true,
  188. TabRatio: 204,
  189. WindowHeight: 8192,
  190. WindowWidth: 16384,
  191. XWindow: "0",
  192. YWindow: "0",
  193. },
  194. },
  195. },
  196. Sheets: xlsxSheets{Sheet: make([]xlsxSheet, len(f.Sheets))},
  197. CalcPr: xlsxCalcPr{
  198. IterateCount: 100,
  199. RefMode: "A1",
  200. Iterate: false,
  201. IterateDelta: 0.001,
  202. },
  203. }
  204. }
  205. // Some tools that read XLSX files have very strict requirements about
  206. // the structure of the input XML. In particular both Numbers on the Mac
  207. // and SAS dislike inline XML namespace declarations, or namespace
  208. // prefixes that don't match the ones that Excel itself uses. This is a
  209. // problem because the Go XML library doesn't multiple namespace
  210. // declarations in a single element of a document. This function is a
  211. // horrible hack to fix that after the XML marshalling is completed.
  212. func replaceRelationshipsNameSpace(workbookMarshal string) string {
  213. newWorkbook := strings.Replace(workbookMarshal, `xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships:id`, `r:id`, -1)
  214. // Dirty hack to fix issues #63 and #91; encoding/xml currently
  215. // "doesn't allow for additional namespaces to be defined in the
  216. // root element of the document," as described by @tealeg in the
  217. // comments for #63.
  218. oldXmlns := `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`
  219. newXmlns := `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">`
  220. return strings.Replace(newWorkbook, oldXmlns, newXmlns, 1)
  221. }
  222. // Construct a map of file name to XML content representing the file
  223. // in terms of the structure of an XLSX file.
  224. func (f *File) MarshallParts() (map[string]string, error) {
  225. var parts map[string]string
  226. var refTable *RefTable = NewSharedStringRefTable()
  227. refTable.isWrite = true
  228. var workbookRels WorkBookRels = make(WorkBookRels)
  229. var err error
  230. var workbook xlsxWorkbook
  231. var types xlsxTypes = MakeDefaultContentTypes()
  232. marshal := func(thing interface{}) (string, error) {
  233. body, err := xml.Marshal(thing)
  234. if err != nil {
  235. return "", err
  236. }
  237. return xml.Header + string(body), nil
  238. }
  239. parts = make(map[string]string)
  240. workbook = f.makeWorkbook()
  241. sheetIndex := 1
  242. if f.styles == nil {
  243. f.styles = newXlsxStyleSheet(f.theme)
  244. }
  245. f.styles.reset()
  246. if len(f.Sheets) == 0 {
  247. err := errors.New("Workbook must contains atleast one worksheet")
  248. return nil, err
  249. }
  250. for _, sheet := range f.Sheets {
  251. xSheet := sheet.makeXLSXSheet(refTable, f.styles)
  252. rId := fmt.Sprintf("rId%d", sheetIndex)
  253. sheetId := strconv.Itoa(sheetIndex)
  254. sheetPath := fmt.Sprintf("worksheets/sheet%d.xml", sheetIndex)
  255. partName := "xl/" + sheetPath
  256. types.Overrides = append(
  257. types.Overrides,
  258. xlsxOverride{
  259. PartName: "/" + partName,
  260. ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"})
  261. workbookRels[rId] = sheetPath
  262. workbook.Sheets.Sheet[sheetIndex-1] = xlsxSheet{
  263. Name: sheet.Name,
  264. SheetId: sheetId,
  265. Id: rId,
  266. State: "visible"}
  267. parts[partName], err = marshal(xSheet)
  268. if err != nil {
  269. return parts, err
  270. }
  271. sheetIndex++
  272. }
  273. workbookMarshal, err := marshal(workbook)
  274. if err != nil {
  275. return parts, err
  276. }
  277. workbookMarshal = replaceRelationshipsNameSpace(workbookMarshal)
  278. parts["xl/workbook.xml"] = workbookMarshal
  279. if err != nil {
  280. return parts, err
  281. }
  282. parts["_rels/.rels"] = TEMPLATE__RELS_DOT_RELS
  283. parts["docProps/app.xml"] = TEMPLATE_DOCPROPS_APP
  284. // TODO - do this properly, modification and revision information
  285. parts["docProps/core.xml"] = TEMPLATE_DOCPROPS_CORE
  286. parts["xl/theme/theme1.xml"] = TEMPLATE_XL_THEME_THEME
  287. xSST := refTable.makeXLSXSST()
  288. parts["xl/sharedStrings.xml"], err = marshal(xSST)
  289. if err != nil {
  290. return parts, err
  291. }
  292. xWRel := workbookRels.MakeXLSXWorkbookRels()
  293. parts["xl/_rels/workbook.xml.rels"], err = marshal(xWRel)
  294. if err != nil {
  295. return parts, err
  296. }
  297. parts["[Content_Types].xml"], err = marshal(types)
  298. if err != nil {
  299. return parts, err
  300. }
  301. parts["xl/styles.xml"], err = f.styles.Marshal()
  302. if err != nil {
  303. return parts, err
  304. }
  305. return parts, nil
  306. }
  307. // Return the raw data contained in the File as three
  308. // dimensional slice. The first index represents the sheet number,
  309. // the second the row number, and the third the cell number.
  310. //
  311. // For example:
  312. //
  313. // var mySlice [][][]string
  314. // var value string
  315. // mySlice = xlsx.FileToSlice("myXLSX.xlsx")
  316. // value = mySlice[0][0][0]
  317. //
  318. // Here, value would be set to the raw value of the cell A1 in the
  319. // first sheet in the XLSX file.
  320. func (f *File) ToSlice() (output [][][]string, err error) {
  321. output = [][][]string{}
  322. for _, sheet := range f.Sheets {
  323. s := [][]string{}
  324. for _, row := range sheet.Rows {
  325. if row == nil {
  326. continue
  327. }
  328. r := []string{}
  329. for _, cell := range row.Cells {
  330. str, err := cell.FormattedValue()
  331. if err != nil {
  332. // Recover from strconv.NumError if the value is an empty string,
  333. // and insert an empty string in the output.
  334. if numErr, ok := err.(*strconv.NumError); ok && numErr.Num == "" {
  335. str = ""
  336. } else {
  337. return output, err
  338. }
  339. }
  340. r = append(r, str)
  341. }
  342. s = append(s, r)
  343. }
  344. output = append(output, s)
  345. }
  346. return output, nil
  347. }
  348. // ToSliceUnmerged returns the raw data contained in the File as three
  349. // dimensional slice (s. method ToSlice).
  350. // A covered cell become the value of its origin cell.
  351. // Example: table where A1:A2 merged.
  352. // | 01.01.2011 | Bread | 20 |
  353. // | | Fish | 70 |
  354. // This sheet will be converted to the slice:
  355. // [ [01.01.2011 Bread 20]
  356. // [01.01.2011 Fish 70] ]
  357. func (f *File) ToSliceUnmerged() (output [][][]string, err error) {
  358. output, err = f.ToSlice()
  359. if err != nil {
  360. return nil, err
  361. }
  362. for s, sheet := range f.Sheets {
  363. for r, row := range sheet.Rows {
  364. for c, cell := range row.Cells {
  365. if cell.HMerge > 0 {
  366. for i := c + 1; i <= c+cell.HMerge; i++ {
  367. output[s][r][i] = output[s][r][c]
  368. }
  369. }
  370. if cell.VMerge > 0 {
  371. for i := r + 1; i <= r+cell.VMerge; i++ {
  372. output[s][i][c] = output[s][r][c]
  373. }
  374. }
  375. }
  376. }
  377. }
  378. return output, nil
  379. }