diff options
Diffstat (limited to 'excelize.go')
| -rw-r--r-- | excelize.go | 296 |
1 files changed, 197 insertions, 99 deletions
diff --git a/excelize.go b/excelize.go index 41fba37..3fd25aa 100644 --- a/excelize.go +++ b/excelize.go @@ -1,11 +1,13 @@ -// Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of +// Copyright 2016 - 2020 The excelize Authors. All rights reserved. Use of // this source code is governed by a BSD-style license that can be found in // the LICENSE file. // Package excelize providing a set of functions that allow you to write to -// and read from XLSX files. Support reads and writes XLSX file generated by -// Microsoft Excel™ 2007 and later. Support save file without losing original -// charts of XLSX. This library needs Go version 1.8 or later. +// and read from XLSX / XLSM / XLTM files. Supports reading and writing +// spreadsheet documents generated by Microsoft Exce™ 2007 and later. Supports +// complex components by high compatibility, and provided streaming API for +// generating or reading data from a worksheet with huge amounts of data. This +// library needs Go version 1.10 or later. // // See https://xuri.me/excelize for more information about this package. package excelize @@ -14,22 +16,25 @@ import ( "archive/zip" "bytes" "encoding/xml" + "errors" "fmt" "io" "io/ioutil" "os" + "path" "strconv" "strings" + + "golang.org/x/net/html/charset" ) -// File define a populated XLSX file struct. +// File define a populated spreadsheet file struct. type File struct { checked map[string]bool sheetMap map[string]string CalcChain *xlsxCalcChain Comments map[string]*xlsxComments ContentTypes *xlsxTypes - DrawingRels map[string]*xlsxWorkbookRels Drawings map[string]*xlsxWsDr Path string SharedStrings *xlsxSST @@ -40,13 +45,15 @@ type File struct { DecodeVMLDrawing map[string]*decodeVmlDrawing VMLDrawing map[string]*vmlDrawing WorkBook *xlsxWorkbook - WorkBookRels *xlsxWorkbookRels - WorkSheetRels map[string]*xlsxWorkbookRels + Relationships map[string]*xlsxRelationships XLSX map[string][]byte + CharsetReader charsetTranscoderFn } -// OpenFile take the name of an XLSX file and returns a populated XLSX file -// struct for it. +type charsetTranscoderFn func(charset string, input io.Reader) (rdr io.Reader, err error) + +// OpenFile take the name of an spreadsheet file and returns a populated +// spreadsheet file struct for it. func OpenFile(filename string) (*File, error) { file, err := os.Open(filename) if err != nil { @@ -61,7 +68,23 @@ func OpenFile(filename string) (*File, error) { return f, nil } -// OpenReader take an io.Reader and return a populated XLSX file. +// newFile is object builder +func newFile() *File { + return &File{ + checked: make(map[string]bool), + sheetMap: make(map[string]string), + Comments: make(map[string]*xlsxComments), + Drawings: make(map[string]*xlsxWsDr), + Sheet: make(map[string]*xlsxWorksheet), + DecodeVMLDrawing: make(map[string]*decodeVmlDrawing), + VMLDrawing: make(map[string]*vmlDrawing), + Relationships: make(map[string]*xlsxRelationships), + CharsetReader: charset.NewReaderLabel, + } +} + +// OpenReader read data stream from io.Reader and return a populated +// spreadsheet file. func OpenReader(r io.Reader) (*File, error) { b, err := ioutil.ReadAll(r) if err != nil { @@ -70,6 +93,17 @@ func OpenReader(r io.Reader) (*File, error) { zr, err := zip.NewReader(bytes.NewReader(b), int64(len(b))) if err != nil { + identifier := []byte{ + // checking protect workbook by [MS-OFFCRYPTO] - v20181211 3.1 FeatureIdentifier + 0x3c, 0x00, 0x00, 0x00, 0x4d, 0x00, 0x69, 0x00, 0x63, 0x00, 0x72, 0x00, 0x6f, 0x00, 0x73, 0x00, + 0x6f, 0x00, 0x66, 0x00, 0x74, 0x00, 0x2e, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6e, 0x00, 0x74, 0x00, + 0x61, 0x00, 0x69, 0x00, 0x6e, 0x00, 0x65, 0x00, 0x72, 0x00, 0x2e, 0x00, 0x44, 0x00, 0x61, 0x00, + 0x74, 0x00, 0x61, 0x00, 0x53, 0x00, 0x70, 0x00, 0x61, 0x00, 0x63, 0x00, 0x65, 0x00, 0x73, 0x00, + 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, + } + if bytes.Contains(b, identifier) { + return nil, errors.New("not support encrypted file currently") + } return nil, err } @@ -77,18 +111,8 @@ func OpenReader(r io.Reader) (*File, error) { if err != nil { return nil, err } - f := &File{ - checked: make(map[string]bool), - Comments: make(map[string]*xlsxComments), - DrawingRels: make(map[string]*xlsxWorkbookRels), - Drawings: make(map[string]*xlsxWsDr), - Sheet: make(map[string]*xlsxWorksheet), - SheetCount: sheetCount, - DecodeVMLDrawing: make(map[string]*decodeVmlDrawing), - VMLDrawing: make(map[string]*vmlDrawing), - WorkSheetRels: make(map[string]*xlsxWorkbookRels), - XLSX: file, - } + f := newFile() + f.SheetCount, f.XLSX = sheetCount, file f.CalcChain = f.calcChainReader() f.sheetMap = f.getSheetMap() f.Styles = f.stylesReader() @@ -96,6 +120,17 @@ func OpenReader(r io.Reader) (*File, error) { return f, nil } +// CharsetTranscoder Set user defined codepage transcoder function for open +// XLSX from non UTF-8 encoding. +func (f *File) CharsetTranscoder(fn charsetTranscoderFn) *File { f.CharsetReader = fn; return f } + +// Creates new XML decoder with charset reader. +func (f *File) xmlNewDecoder(rdr io.Reader) (ret *xml.Decoder) { + ret = xml.NewDecoder(rdr) + ret.CharsetReader = f.CharsetReader + return +} + // setDefaultTimeStyle provides a function to set default numbers format for // time.Time type cell value by given worksheet name, cell coordinates and // number format code. @@ -105,34 +140,50 @@ func (f *File) setDefaultTimeStyle(sheet, axis string, format int) error { return err } if s == 0 { - style, _ := f.NewStyle(`{"number_format": ` + strconv.Itoa(format) + `}`) - f.SetCellStyle(sheet, axis, axis, style) + style, _ := f.NewStyle(&Style{NumFmt: format}) + _ = f.SetCellStyle(sheet, axis, axis, style) } return err } // workSheetReader provides a function to get the pointer to the structure // after deserialization by given worksheet name. -func (f *File) workSheetReader(sheet string) (*xlsxWorksheet, error) { - name, ok := f.sheetMap[trimSheetName(sheet)] - if !ok { - return nil, fmt.Errorf("sheet %s is not exist", sheet) +func (f *File) workSheetReader(sheet string) (xlsx *xlsxWorksheet, err error) { + var ( + name string + ok bool + ) + + if name, ok = f.sheetMap[trimSheetName(sheet)]; !ok { + err = fmt.Errorf("sheet %s is not exist", sheet) + return } - if f.Sheet[name] == nil { - var xlsx xlsxWorksheet - _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML(name)), &xlsx) + if xlsx = f.Sheet[name]; f.Sheet[name] == nil { + if strings.HasPrefix(name, "xl/chartsheets") { + err = fmt.Errorf("sheet %s is chart sheet", sheet) + return + } + xlsx = new(xlsxWorksheet) + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(name)))). + Decode(xlsx); err != nil && err != io.EOF { + err = fmt.Errorf("xml decode error: %s", err) + return + } + err = nil if f.checked == nil { f.checked = make(map[string]bool) } - ok := f.checked[name] - if !ok { - checkSheet(&xlsx) - checkRow(&xlsx) + if ok = f.checked[name]; !ok { + checkSheet(xlsx) + if err = checkRow(xlsx); err != nil { + return + } f.checked[name] = true } - f.Sheet[name] = &xlsx + f.Sheet[name] = xlsx } - return f.Sheet[name], nil + + return } // checkSheet provides a function to fill each row element and make that is @@ -145,32 +196,50 @@ func checkSheet(xlsx *xlsxWorksheet) { row = lastRow } } - sheetData := xlsxSheetData{} - existsRows := map[int]int{} - for k := range xlsx.SheetData.Row { - existsRows[xlsx.SheetData.Row[k].R] = k + sheetData := xlsxSheetData{Row: make([]xlsxRow, row)} + for _, r := range xlsx.SheetData.Row { + sheetData.Row[r.R-1] = r } - for i := 0; i < row; i++ { - _, ok := existsRows[i+1] - if ok { - sheetData.Row = append(sheetData.Row, xlsx.SheetData.Row[existsRows[i+1]]) - } else { - sheetData.Row = append(sheetData.Row, xlsxRow{ - R: i + 1, - }) - } + for i := 1; i <= row; i++ { + sheetData.Row[i-1].R = i } xlsx.SheetData = sheetData } -// replaceWorkSheetsRelationshipsNameSpaceBytes provides a function to replace -// xl/worksheets/sheet%d.xml XML tags to self-closing for compatible Microsoft -// Office Excel 2007. -func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte { - var oldXmlns = []byte(`<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`) - var newXmlns = []byte(`<worksheet xr:uid="{00000000-0001-0000-0000-000000000000}" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac xr xr2 xr3" 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">`) - workbookMarshal = bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1) - return workbookMarshal +// addRels provides a function to add relationships by given XML path, +// relationship type, target and target mode. +func (f *File) addRels(relPath, relType, target, targetMode string) int { + rels := f.relsReader(relPath) + if rels == nil { + rels = &xlsxRelationships{} + } + var rID int + for _, rel := range rels.Relationships { + ID, _ := strconv.Atoi(strings.TrimPrefix(rel.ID, "rId")) + if ID > rID { + rID = ID + } + } + rID++ + var ID bytes.Buffer + ID.WriteString("rId") + ID.WriteString(strconv.Itoa(rID)) + rels.Relationships = append(rels.Relationships, xlsxRelationship{ + ID: ID.String(), + Type: relType, + Target: target, + TargetMode: targetMode, + }) + f.Relationships[relPath] = rels + return rID +} + +// replaceRelationshipsNameSpaceBytes provides a function to replace +// XML tags to self-closing for compatible Microsoft Office Excel 2007. +func replaceRelationshipsNameSpaceBytes(contentMarshal []byte) []byte { + var oldXmlns = stringToBytes(` xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`) + var newXmlns = []byte(templateNamespaceIDMap) + return bytesReplace(contentMarshal, oldXmlns, newXmlns, -1) } // UpdateLinkedValue fix linked values within a spreadsheet are not updating in @@ -199,7 +268,10 @@ func replaceWorkSheetsRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte // </row> // func (f *File) UpdateLinkedValue() error { - for _, name := range f.GetSheetMap() { + wb := f.workbookReader() + // recalculate formulas + wb.CalcPr = nil + for _, name := range f.GetSheetList() { xlsx, err := f.workSheetReader(name) if err != nil { return err @@ -216,48 +288,74 @@ func (f *File) UpdateLinkedValue() error { return nil } -// GetMergeCells provides a function to get all merged cells from a worksheet -// currently. -func (f *File) GetMergeCells(sheet string) ([]MergeCell, error) { - var mergeCells []MergeCell - xlsx, err := f.workSheetReader(sheet) - if err != nil { - return mergeCells, err +// AddVBAProject provides the method to add vbaProject.bin file which contains +// functions and/or macros. The file extension should be .xlsm. For example: +// +// if err := f.SetSheetPrOptions("Sheet1", excelize.CodeName("Sheet1")); err != nil { +// fmt.Println(err) +// } +// if err := f.AddVBAProject("vbaProject.bin"); err != nil { +// fmt.Println(err) +// } +// if err := f.SaveAs("macros.xlsm"); err != nil { +// fmt.Println(err) +// } +// +func (f *File) AddVBAProject(bin string) error { + var err error + // Check vbaProject.bin exists first. + if _, err = os.Stat(bin); os.IsNotExist(err) { + return err } - if xlsx.MergeCells != nil { - mergeCells = make([]MergeCell, 0, len(xlsx.MergeCells.Cells)) - - for i := range xlsx.MergeCells.Cells { - ref := xlsx.MergeCells.Cells[i].Ref - axis := strings.Split(ref, ":")[0] - val, _ := f.GetCellValue(sheet, axis) - mergeCells = append(mergeCells, []string{ref, val}) + if path.Ext(bin) != ".bin" { + return errors.New("unsupported VBA project extension") + } + f.setContentTypePartVBAProjectExtensions() + wb := f.relsReader("xl/_rels/workbook.xml.rels") + var rID int + var ok bool + for _, rel := range wb.Relationships { + if rel.Target == "vbaProject.bin" && rel.Type == SourceRelationshipVBAProject { + ok = true + continue + } + t, _ := strconv.Atoi(strings.TrimPrefix(rel.ID, "rId")) + if t > rID { + rID = t } } - - return mergeCells, err -} - -// MergeCell define a merged cell data. -// It consists of the following structure. -// example: []string{"D4:E10", "cell value"} -type MergeCell []string - -// GetCellValue returns merged cell value. -func (m *MergeCell) GetCellValue() string { - return (*m)[1] -} - -// GetStartAxis returns the merge start axis. -// example: "C2" -func (m *MergeCell) GetStartAxis() string { - axis := strings.Split((*m)[0], ":") - return axis[0] + rID++ + if !ok { + wb.Relationships = append(wb.Relationships, xlsxRelationship{ + ID: "rId" + strconv.Itoa(rID), + Target: "vbaProject.bin", + Type: SourceRelationshipVBAProject, + }) + } + file, _ := ioutil.ReadFile(bin) + f.XLSX["xl/vbaProject.bin"] = file + return err } -// GetEndAxis returns the merge end axis. -// example: "D4" -func (m *MergeCell) GetEndAxis() string { - axis := strings.Split((*m)[0], ":") - return axis[1] +// setContentTypePartVBAProjectExtensions provides a function to set the +// content type for relationship parts and the main document part. +func (f *File) setContentTypePartVBAProjectExtensions() { + var ok bool + content := f.contentTypesReader() + for _, v := range content.Defaults { + if v.Extension == "bin" { + ok = true + } + } + for idx, o := range content.Overrides { + if o.PartName == "/xl/workbook.xml" { + content.Overrides[idx].ContentType = ContentTypeMacro + } + } + if !ok { + content.Defaults = append(content.Defaults, xlsxDefault{ + Extension: "bin", + ContentType: ContentTypeVBA, + }) + } } |