diff options
Diffstat (limited to 'sheet.go')
| -rw-r--r-- | sheet.go | 977 |
1 files changed, 761 insertions, 216 deletions
@@ -1,11 +1,11 @@ -// 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. +// charts of XLSX. This library needs Go version 1.10 or later. package excelize @@ -14,9 +14,13 @@ import ( "encoding/json" "encoding/xml" "errors" + "fmt" + "io" "io/ioutil" + "log" "os" "path" + "reflect" "regexp" "strconv" "strings" @@ -30,7 +34,7 @@ import ( // the number of sheets in the workbook (file) after appending the new sheet. func (f *File) NewSheet(name string) int { // Check if the worksheet already exists - if f.GetSheetIndex(name) != 0 { + if f.GetSheetIndex(name) != -1 { return f.SheetCount } f.DeleteSheet(name) @@ -46,24 +50,29 @@ func (f *File) NewSheet(name string) int { // Update docProps/app.xml f.setAppXML() // Update [Content_Types].xml - f.setContentTypes(sheetID) + f.setContentTypes("/xl/worksheets/sheet"+strconv.Itoa(sheetID)+".xml", ContentTypeSpreadSheetMLWorksheet) // Create new sheet /xl/worksheets/sheet%d.xml f.setSheet(sheetID, name) // Update xl/_rels/workbook.xml.rels - rID := f.addXlsxWorkbookRels(sheetID) + rID := f.addRels("xl/_rels/workbook.xml.rels", SourceRelationshipWorkSheet, fmt.Sprintf("worksheets/sheet%d.xml", sheetID), "") // Update xl/workbook.xml f.setWorkbook(name, sheetID, rID) - return sheetID + return f.GetSheetIndex(name) } // contentTypesReader provides a function to get the pointer to the // [Content_Types].xml structure after deserialization. func (f *File) contentTypesReader() *xlsxTypes { + var err error + if f.ContentTypes == nil { - var content xlsxTypes - _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML("[Content_Types].xml")), &content) - f.ContentTypes = &content + f.ContentTypes = new(xlsxTypes) + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("[Content_Types].xml")))). + Decode(f.ContentTypes); err != nil && err != io.EOF { + log.Printf("xml decode error: %s", err) + } } + return f.ContentTypes } @@ -79,11 +88,16 @@ func (f *File) contentTypesWriter() { // workbookReader provides a function to get the pointer to the xl/workbook.xml // structure after deserialization. func (f *File) workbookReader() *xlsxWorkbook { + var err error + if f.WorkBook == nil { - var content xlsxWorkbook - _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML("xl/workbook.xml")), &content) - f.WorkBook = &content + f.WorkBook = new(xlsxWorkbook) + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML("xl/workbook.xml")))). + Decode(f.WorkBook); err != nil && err != io.EOF { + log.Printf("xml decode error: %s", err) + } } + return f.WorkBook } @@ -92,7 +106,7 @@ func (f *File) workbookReader() *xlsxWorkbook { func (f *File) workBookWriter() { if f.WorkBook != nil { output, _ := xml.Marshal(f.WorkBook) - f.saveFileList("xl/workbook.xml", replaceRelationshipsNameSpaceBytes(output)) + f.saveFileList("xl/workbook.xml", replaceRelationshipsBytes(replaceRelationshipsNameSpaceBytes(output))) } } @@ -105,21 +119,29 @@ func (f *File) workSheetWriter() { f.Sheet[p].SheetData.Row[k].C = trimCell(v.C) } output, _ := xml.Marshal(sheet) - f.saveFileList(p, replaceWorkSheetsRelationshipsNameSpaceBytes(output)) + f.saveFileList(p, replaceRelationshipsBytes(replaceRelationshipsNameSpaceBytes(output))) ok := f.checked[p] if ok { + delete(f.Sheet, p) f.checked[p] = false } } } } -// trimCell provides a function to trim blank cells which created by completeCol. +// trimCell provides a function to trim blank cells which created by fillColumns. func trimCell(column []xlsxC) []xlsxC { + rowFull := true + for i := range column { + rowFull = column[i].hasValue() && rowFull + } + if rowFull { + return column + } col := make([]xlsxC, len(column)) i := 0 for _, c := range column { - if c.S != 0 || c.V != "" || c.F != nil || c.T != "" { + if c.hasValue() { col[i] = c i++ } @@ -129,21 +151,22 @@ func trimCell(column []xlsxC) []xlsxC { // setContentTypes provides a function to read and update property of contents // type of XLSX. -func (f *File) setContentTypes(index int) { +func (f *File) setContentTypes(partName, contentType string) { content := f.contentTypesReader() content.Overrides = append(content.Overrides, xlsxOverride{ - PartName: "/xl/worksheets/sheet" + strconv.Itoa(index) + ".xml", - ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", + PartName: partName, + ContentType: contentType, }) } // setSheet provides a function to update sheet property by given index. func (f *File) setSheet(index int, name string) { - var xlsx xlsxWorksheet - xlsx.Dimension.Ref = "A1" - xlsx.SheetViews.SheetView = append(xlsx.SheetViews.SheetView, xlsxSheetView{ - WorkbookViewID: 0, - }) + xlsx := xlsxWorksheet{ + Dimension: &xlsxDimension{Ref: "A1"}, + SheetViews: &xlsxSheetViews{ + SheetView: []xlsxSheetView{{WorkbookViewID: 0}}, + }, + } path := "xl/worksheets/sheet" + strconv.Itoa(index) + ".xml" f.sheetMap[trimSheetName(name)] = path f.Sheet[path] = &xlsx @@ -160,50 +183,18 @@ func (f *File) setWorkbook(name string, sheetID, rid int) { }) } -// workbookRelsReader provides a function to read and unmarshal workbook -// relationships of XLSX file. -func (f *File) workbookRelsReader() *xlsxWorkbookRels { - if f.WorkBookRels == nil { - var content xlsxWorkbookRels - _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML("xl/_rels/workbook.xml.rels")), &content) - f.WorkBookRels = &content - } - return f.WorkBookRels -} - -// workBookRelsWriter provides a function to save xl/_rels/workbook.xml.rels after +// relsWriter provides a function to save relationships after // serialize structure. -func (f *File) workBookRelsWriter() { - if f.WorkBookRels != nil { - output, _ := xml.Marshal(f.WorkBookRels) - f.saveFileList("xl/_rels/workbook.xml.rels", output) - } -} - -// addXlsxWorkbookRels update workbook relationships property of XLSX. -func (f *File) addXlsxWorkbookRels(sheet int) int { - content := f.workbookRelsReader() - rID := 0 - for _, v := range content.Relationships { - t, _ := strconv.Atoi(strings.TrimPrefix(v.ID, "rId")) - if t > rID { - rID = t +func (f *File) relsWriter() { + for path, rel := range f.Relationships { + if rel != nil { + output, _ := xml.Marshal(rel) + if strings.HasPrefix(path, "xl/worksheets/sheet/rels/sheet") { + output = replaceRelationshipsNameSpaceBytes(output) + } + f.saveFileList(path, replaceRelationshipsBytes(output)) } } - rID++ - ID := bytes.Buffer{} - ID.WriteString("rId") - ID.WriteString(strconv.Itoa(rID)) - target := bytes.Buffer{} - target.WriteString("worksheets/sheet") - target.WriteString(strconv.Itoa(sheet)) - target.WriteString(".xml") - content.Relationships = append(content.Relationships, xlsxWorkbookRelation{ - ID: ID.String(), - Target: target.String(), - Type: SourceRelationshipWorkSheet, - }) - return rID } // setAppXML update docProps/app.xml file of XML. @@ -211,30 +202,29 @@ func (f *File) setAppXML() { f.saveFileList("docProps/app.xml", []byte(templateDocpropsApp)) } -// replaceRelationshipsNameSpaceBytes; Some tools that read XLSX files have -// very strict requirements about the structure of the input XML. In -// particular both Numbers on the Mac and SAS dislike inline XML namespace -// declarations, or namespace prefixes that don't match the ones that Excel -// itself uses. This is a problem because the Go XML library doesn't multiple -// namespace declarations in a single element of a document. This function is -// a horrible hack to fix that after the XML marshalling is completed. -func replaceRelationshipsNameSpaceBytes(workbookMarshal []byte) []byte { - oldXmlns := []byte(`<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`) - newXmlns := []byte(`<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">`) - return bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1) +// replaceRelationshipsBytes; Some tools that read XLSX files have very strict +// requirements about the structure of the input XML. This function is a +// horrible hack to fix that after the XML marshalling is completed. +func replaceRelationshipsBytes(content []byte) []byte { + oldXmlns := stringToBytes(`xmlns:relationships="http://schemas.openxmlformats.org/officeDocument/2006/relationships" relationships`) + newXmlns := stringToBytes("r") + return bytesReplace(content, oldXmlns, newXmlns, -1) } -// SetActiveSheet provides function to set default active worksheet of XLSX by -// given index. Note that active index is different from the index returned by -// function GetSheetMap(). It should be greater than 0 and less than total -// worksheet numbers. +// SetActiveSheet provides a function to set the default active sheet of the +// workbook by a given index. Note that the active index is different from the +// ID returned by function GetSheetMap(). It should be greater or equal to 0 +// and less than the total worksheet numbers. func (f *File) SetActiveSheet(index int) { - if index < 1 { - index = 1 + if index < 0 { + index = 0 } wb := f.workbookReader() - for activeTab, sheet := range wb.Sheets.Sheet { - if sheet.SheetID == index { + for activeTab := range wb.Sheets.Sheet { + if activeTab == index { + if wb.BookViews == nil { + wb.BookViews = &xlsxBookViews{} + } if len(wb.BookViews.WorkBookView) > 0 { wb.BookViews.WorkBookView[0].ActiveTab = activeTab } else { @@ -244,8 +234,17 @@ func (f *File) SetActiveSheet(index int) { } } } - for idx, name := range f.GetSheetMap() { - xlsx, _ := f.workSheetReader(name) + for idx, name := range f.GetSheetList() { + xlsx, err := f.workSheetReader(name) + if err != nil { + // Chartsheet or dialogsheet + return + } + if xlsx.SheetViews == nil { + xlsx.SheetViews = &xlsxSheetViews{ + SheetView: []xlsxSheetView{{WorkbookViewID: 0}}, + } + } if len(xlsx.SheetViews.SheetView) > 0 { xlsx.SheetViews.SheetView[0].TabSelected = false } @@ -263,20 +262,39 @@ func (f *File) SetActiveSheet(index int) { // GetActiveSheetIndex provides a function to get active sheet index of the // XLSX. If not found the active sheet will be return integer 0. -func (f *File) GetActiveSheetIndex() int { - for idx, name := range f.GetSheetMap() { - xlsx, _ := f.workSheetReader(name) - for _, sheetView := range xlsx.SheetViews.SheetView { - if sheetView.TabSelected { - return idx +func (f *File) GetActiveSheetIndex() (index int) { + var sheetID = f.getActiveSheetID() + wb := f.workbookReader() + if wb != nil { + for idx, sheet := range wb.Sheets.Sheet { + if sheet.SheetID == sheetID { + index = idx + } + } + } + return +} + +// getActiveSheetID provides a function to get active sheet index of the +// XLSX. If not found the active sheet will be return integer 0. +func (f *File) getActiveSheetID() int { + wb := f.workbookReader() + if wb != nil { + if wb.BookViews != nil && len(wb.BookViews.WorkBookView) > 0 { + activeTab := wb.BookViews.WorkBookView[0].ActiveTab + if len(wb.Sheets.Sheet) > activeTab && wb.Sheets.Sheet[activeTab].SheetID != 0 { + return wb.Sheets.Sheet[activeTab].SheetID } } + if len(wb.Sheets.Sheet) >= 1 { + return wb.Sheets.Sheet[0].SheetID + } } return 0 } -// SetSheetName provides a function to set the worksheet name be given old and -// new worksheet name. Maximum 31 characters are allowed in sheet title and +// SetSheetName provides a function to set the worksheet name by given old and +// new worksheet names. Maximum 31 characters are allowed in sheet title and // this function only changes the name of the sheet and will not update the // sheet name in the formula or reference associated with the cell. So there // may be problem formula error or reference missing. @@ -293,48 +311,64 @@ func (f *File) SetSheetName(oldName, newName string) { } } -// GetSheetName provides a function to get worksheet name of XLSX by given -// worksheet index. If given sheet index is invalid, will return an empty +// getSheetNameByID provides a function to get worksheet name of XLSX by given +// worksheet ID. If given sheet ID is invalid, will return an empty // string. -func (f *File) GetSheetName(index int) string { - content := f.workbookReader() - rels := f.workbookRelsReader() - for _, rel := range rels.Relationships { - rID, _ := strconv.Atoi(strings.TrimSuffix(strings.TrimPrefix(rel.Target, "worksheets/sheet"), ".xml")) - if rID == index { - for _, v := range content.Sheets.Sheet { - if v.ID == rel.ID { - return v.Name - } - } +func (f *File) getSheetNameByID(ID int) string { + wb := f.workbookReader() + if wb == nil || ID < 1 { + return "" + } + for _, sheet := range wb.Sheets.Sheet { + if ID == sheet.SheetID { + return sheet.Name } } return "" } -// GetSheetIndex provides a function to get worksheet index of XLSX by given sheet -// name. If given worksheet name is invalid, will return an integer type value -// 0. +// GetSheetName provides a function to get the sheet name of the workbook by +// the given sheet index. If the given sheet index is invalid, it will return +// an empty string. +func (f *File) GetSheetName(index int) (name string) { + for idx, sheet := range f.GetSheetList() { + if idx == index { + name = sheet + } + } + return +} + +// getSheetID provides a function to get worksheet ID of XLSX by given +// sheet name. If given worksheet name is invalid, will return an integer type +// value -1. +func (f *File) getSheetID(name string) int { + var ID = -1 + for sheetID, sheet := range f.GetSheetMap() { + if sheet == trimSheetName(name) { + ID = sheetID + } + } + return ID +} + +// GetSheetIndex provides a function to get a sheet index of the workbook by +// the given sheet name. If the given sheet name is invalid, it will return an +// integer type value 0. func (f *File) GetSheetIndex(name string) int { - content := f.workbookReader() - rels := f.workbookRelsReader() - for _, v := range content.Sheets.Sheet { - if v.Name == name { - for _, rel := range rels.Relationships { - if v.ID == rel.ID { - rID, _ := strconv.Atoi(strings.TrimSuffix(strings.TrimPrefix(rel.Target, "worksheets/sheet"), ".xml")) - return rID - } - } + var idx = -1 + for index, sheet := range f.GetSheetList() { + if sheet == trimSheetName(name) { + idx = index } } - return 0 + return idx } -// GetSheetMap provides a function to get worksheet name and index map of XLSX. -// For example: +// GetSheetMap provides a function to get worksheets, chart sheets, dialog +// sheets ID and name map of the workbook. For example: // -// f, err := excelize.OpenFile("./Book1.xlsx") +// f, err := excelize.OpenFile("Book1.xlsx") // if err != nil { // return // } @@ -343,27 +377,45 @@ func (f *File) GetSheetIndex(name string) int { // } // func (f *File) GetSheetMap() map[int]string { - content := f.workbookReader() - rels := f.workbookRelsReader() + wb := f.workbookReader() sheetMap := map[int]string{} - for _, v := range content.Sheets.Sheet { - for _, rel := range rels.Relationships { - relStr := strings.SplitN(rel.Target, "worksheets/sheet", 2) - if rel.ID == v.ID && len(relStr) == 2 { - rID, _ := strconv.Atoi(strings.TrimSuffix(relStr[1], ".xml")) - sheetMap[rID] = v.Name - } + if wb != nil { + for _, sheet := range wb.Sheets.Sheet { + sheetMap[sheet.SheetID] = sheet.Name } } return sheetMap } -// getSheetMap provides a function to get worksheet name and XML file path map of -// XLSX. +// GetSheetList provides a function to get worksheets, chart sheets, and +// dialog sheets name list of the workbook. +func (f *File) GetSheetList() (list []string) { + wb := f.workbookReader() + if wb != nil { + for _, sheet := range wb.Sheets.Sheet { + list = append(list, sheet.Name) + } + } + return +} + +// getSheetMap provides a function to get worksheet name and XML file path map +// of XLSX. func (f *File) getSheetMap() map[string]string { - maps := make(map[string]string) - for idx, name := range f.GetSheetMap() { - maps[name] = "xl/worksheets/sheet" + strconv.Itoa(idx) + ".xml" + content := f.workbookReader() + rels := f.relsReader("xl/_rels/workbook.xml.rels") + maps := map[string]string{} + for _, v := range content.Sheets.Sheet { + for _, rel := range rels.Relationships { + if rel.ID == v.ID { + // Construct a target XML as xl/worksheets/sheet%d by split path, compatible with different types of relative paths in workbook.xml.rels, for example: worksheets/sheet%d.xml and /xl/worksheets/sheet%d.xml + pathInfo := strings.Split(rel.Target, "/") + pathInfoLen := len(pathInfo) + if pathInfoLen > 1 { + maps[v.Name] = fmt.Sprintf("xl/%s", strings.Join(pathInfo[pathInfoLen-2:], "/")) + } + } + } } return maps } @@ -382,7 +434,8 @@ func (f *File) SetSheetBackground(sheet, picture string) error { } file, _ := ioutil.ReadFile(picture) name := f.addMedia(file, ext) - rID := f.addSheetRelationships(sheet, SourceRelationshipImage, strings.Replace(name, "xl", "..", 1), "") + sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(f.sheetMap[trimSheetName(sheet)], "xl/worksheets/") + ".rels" + rID := f.addRels(sheetRels, SourceRelationshipImage, strings.Replace(name, "xl", "..", 1), "") f.addSheetPicture(sheet, rID) f.setContentTypePartImageExtensions() return err @@ -394,22 +447,45 @@ func (f *File) SetSheetBackground(sheet, picture string) error { // value of the deleted worksheet, it will cause a file error when you open it. // This function will be invalid when only the one worksheet is left. func (f *File) DeleteSheet(name string) { - content := f.workbookReader() - for k, v := range content.Sheets.Sheet { - if v.Name == trimSheetName(name) && len(content.Sheets.Sheet) > 1 { - content.Sheets.Sheet = append(content.Sheets.Sheet[:k], content.Sheets.Sheet[k+1:]...) - sheet := "xl/worksheets/sheet" + strconv.Itoa(v.SheetID) + ".xml" - rels := "xl/worksheets/_rels/sheet" + strconv.Itoa(v.SheetID) + ".xml.rels" - target := f.deleteSheetFromWorkbookRels(v.ID) + if f.SheetCount == 1 || f.GetSheetIndex(name) == -1 { + return + } + sheetName := trimSheetName(name) + wb := f.workbookReader() + wbRels := f.relsReader("xl/_rels/workbook.xml.rels") + for idx, sheet := range wb.Sheets.Sheet { + if sheet.Name == sheetName { + wb.Sheets.Sheet = append(wb.Sheets.Sheet[:idx], wb.Sheets.Sheet[idx+1:]...) + var sheetXML, rels string + if wbRels != nil { + for _, rel := range wbRels.Relationships { + if rel.ID == sheet.ID { + sheetXML = fmt.Sprintf("xl/%s", rel.Target) + pathInfo := strings.Split(rel.Target, "/") + if len(pathInfo) == 2 { + rels = fmt.Sprintf("xl/%s/_rels/%s.rels", pathInfo[0], pathInfo[1]) + } + } + } + } + target := f.deleteSheetFromWorkbookRels(sheet.ID) f.deleteSheetFromContentTypes(target) - f.deleteCalcChain(v.SheetID, "") // Delete CalcChain - delete(f.sheetMap, name) - delete(f.XLSX, sheet) + f.deleteCalcChain(sheet.SheetID, "") // Delete CalcChain + delete(f.sheetMap, sheetName) + delete(f.XLSX, sheetXML) delete(f.XLSX, rels) - delete(f.Sheet, sheet) + delete(f.Relationships, rels) + delete(f.Sheet, sheetXML) f.SheetCount-- } } + if wb.BookViews != nil { + for idx, bookView := range wb.BookViews.WorkBookView { + if bookView.ActiveTab >= f.SheetCount { + wb.BookViews.WorkBookView[idx].ActiveTab-- + } + } + } f.SetActiveSheet(len(f.GetSheetMap())) } @@ -417,7 +493,7 @@ func (f *File) DeleteSheet(name string) { // relationships by given relationships ID in the file // xl/_rels/workbook.xml.rels. func (f *File) deleteSheetFromWorkbookRels(rID string) string { - content := f.workbookRelsReader() + content := f.relsReader("xl/_rels/workbook.xml.rels") for k, v := range content.Relationships { if v.ID == rID { content.Relationships = append(content.Relationships[:k], content.Relationships[k+1:]...) @@ -448,7 +524,7 @@ func (f *File) deleteSheetFromContentTypes(target string) { // return err // func (f *File) CopySheet(from, to int) error { - if from < 1 || to < 1 || from == to || f.GetSheetName(from) == "" || f.GetSheetName(to) == "" { + if from < 0 || to < 0 || from == to || f.GetSheetName(from) == "" || f.GetSheetName(to) == "" { return errors.New("invalid worksheet index") } return f.copySheet(from, to) @@ -457,12 +533,14 @@ func (f *File) CopySheet(from, to int) error { // copySheet provides a function to duplicate a worksheet by gave source and // target worksheet name. func (f *File) copySheet(from, to int) error { - sheet, err := f.workSheetReader("sheet" + strconv.Itoa(from)) + fromSheet := f.GetSheetName(from) + sheet, err := f.workSheetReader(fromSheet) if err != nil { return err } worksheet := deepcopy.Copy(sheet).(*xlsxWorksheet) - path := "xl/worksheets/sheet" + strconv.Itoa(to) + ".xml" + toSheetID := strconv.Itoa(f.getSheetID(f.GetSheetName(to))) + path := "xl/worksheets/sheet" + toSheetID + ".xml" if len(worksheet.SheetViews.SheetView) > 0 { worksheet.SheetViews.SheetView[0].TabSelected = false } @@ -470,8 +548,8 @@ func (f *File) copySheet(from, to int) error { worksheet.TableParts = nil worksheet.PageSetUp = nil f.Sheet[path] = worksheet - toRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(to) + ".xml.rels" - fromRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(from) + ".xml.rels" + toRels := "xl/worksheets/_rels/sheet" + toSheetID + ".xml.rels" + fromRels := "xl/worksheets/_rels/sheet" + strconv.Itoa(f.getSheetID(fromSheet)) + ".xml.rels" _, ok := f.XLSX[fromRels] if ok { f.XLSX[toRels] = f.XLSX[fromRels] @@ -482,7 +560,7 @@ func (f *File) copySheet(from, to int) error { // SetSheetVisible provides a function to set worksheet visible by given worksheet // name. A workbook must contain at least one visible worksheet. If the given // worksheet has been activated, this setting will be invalidated. Sheet state -// values as defined by http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.sheetstatevalues.aspx +// values as defined by https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.sheetstatevalues // // visible // hidden @@ -510,7 +588,7 @@ func (f *File) SetSheetVisible(name string, visible bool) error { } } for k, v := range content.Sheets.Sheet { - xlsx, err := f.workSheetReader(f.GetSheetMap()[k]) + xlsx, err := f.workSheetReader(v.Name) if err != nil { return err } @@ -687,69 +765,242 @@ func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error) { var ( regSearch bool result []string - inElement string - r xlsxRow ) for _, r := range reg { regSearch = r } - - xlsx, err := f.workSheetReader(sheet) - if err != nil { - return result, err - } - name, ok := f.sheetMap[trimSheetName(sheet)] if !ok { - return result, nil + return result, ErrSheetNotExist{sheet} } - if xlsx != nil { + if f.Sheet[name] != nil { + // flush data output, _ := xml.Marshal(f.Sheet[name]) - f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(output)) + f.saveFileList(name, replaceRelationshipsNameSpaceBytes(output)) } - xml.NewDecoder(bytes.NewReader(f.readXML(name))) - d := f.sharedStringsReader() + return f.searchSheet(name, value, regSearch) +} + +// searchSheet provides a function to get coordinates by given worksheet name, +// cell value, and regular expression. +func (f *File) searchSheet(name, value string, regSearch bool) (result []string, err error) { + var ( + cellName, inElement string + cellCol, row int + d *xlsxSST + ) - decoder := xml.NewDecoder(bytes.NewReader(f.readXML(name))) + d = f.sharedStringsReader() + decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) for { - token, _ := decoder.Token() - if token == nil { + var token xml.Token + token, err = decoder.Token() + if err != nil || token == nil { + if err == io.EOF { + err = nil + } break } switch startElement := token.(type) { case xml.StartElement: inElement = startElement.Name.Local if inElement == "row" { - r = xlsxRow{} - _ = decoder.DecodeElement(&r, &startElement) - for _, colCell := range r.C { - val, _ := colCell.getValueFrom(f, d) - if regSearch { - regex := regexp.MustCompile(value) - if !regex.MatchString(val) { - continue - } - } else { - if val != value { - continue - } - } - - cellCol, _, err := CellNameToCoordinates(colCell.R) - if err != nil { - return result, err + row, err = attrValToInt("r", startElement.Attr) + if err != nil { + return + } + } + if inElement == "c" { + colCell := xlsxC{} + _ = decoder.DecodeElement(&colCell, &startElement) + val, _ := colCell.getValueFrom(f, d) + if regSearch { + regex := regexp.MustCompile(value) + if !regex.MatchString(val) { + continue } - cellName, err := CoordinatesToCellName(cellCol, r.R) - if err != nil { - return result, err + } else { + if val != value { + continue } - result = append(result, cellName) } + cellCol, _, err = CellNameToCoordinates(colCell.R) + if err != nil { + return result, err + } + cellName, err = CoordinatesToCellName(cellCol, row) + if err != nil { + return result, err + } + result = append(result, cellName) } default: } } - return result, nil + return +} + +// attrValToInt provides a function to convert the local names to an integer +// by given XML attributes and specified names. +func attrValToInt(name string, attrs []xml.Attr) (val int, err error) { + for _, attr := range attrs { + if attr.Name.Local == name { + val, err = strconv.Atoi(attr.Value) + if err != nil { + return + } + } + } + return +} + +// SetHeaderFooter provides a function to set headers and footers by given +// worksheet name and the control characters. +// +// Headers and footers are specified using the following settings fields: +// +// Fields | Description +// ------------------+----------------------------------------------------------- +// AlignWithMargins | Align header footer margins with page margins +// DifferentFirst | Different first-page header and footer indicator +// DifferentOddEven | Different odd and even page headers and footers indicator +// ScaleWithDoc | Scale header and footer with document scaling +// OddFooter | Odd Page Footer +// OddHeader | Odd Header +// EvenFooter | Even Page Footer +// EvenHeader | Even Page Header +// FirstFooter | First Page Footer +// FirstHeader | First Page Header +// +// The following formatting codes can be used in 6 string type fields: +// OddHeader, OddFooter, EvenHeader, EvenFooter, FirstFooter, FirstHeader +// +// Formatting Code | Description +// ------------------------+------------------------------------------------------------------------- +// && | The character "&" +// | +// &font-size | Size of the text font, where font-size is a decimal font size in points +// | +// &"font name,font type" | A text font-name string, font name, and a text font-type string, +// | font type +// | +// &"-,Regular" | Regular text format. Toggles bold and italic modes to off +// | +// &A | Current worksheet's tab name +// | +// &B or &"-,Bold" | Bold text format, from off to on, or vice versa. The default mode is off +// | +// &D | Current date +// | +// &C | Center section +// | +// &E | Double-underline text format +// | +// &F | Current workbook's file name +// | +// &G | Drawing object as background +// | +// &H | Shadow text format +// | +// &I or &"-,Italic" | Italic text format +// | +// &K | Text font color +// | +// | An RGB Color is specified as RRGGBB +// | +// | A Theme Color is specified as TTSNNN where TT is the theme color Id, +// | S is either "+" or "-" of the tint/shade value, and NNN is the +// | tint/shade value +// | +// &L | Left section +// | +// &N | Total number of pages +// | +// &O | Outline text format +// | +// &P[[+|-]n] | Without the optional suffix, the current page number in decimal +// | +// &R | Right section +// | +// &S | Strikethrough text format +// | +// &T | Current time +// | +// &U | Single-underline text format. If double-underline mode is on, the next +// | occurrence in a section specifier toggles double-underline mode to off; +// | otherwise, it toggles single-underline mode, from off to on, or vice +// | versa. The default mode is off +// | +// &X | Superscript text format +// | +// &Y | Subscript text format +// | +// &Z | Current workbook's file path +// +// For example: +// +// err := f.SetHeaderFooter("Sheet1", &excelize.FormatHeaderFooter{ +// DifferentFirst: true, +// DifferentOddEven: true, +// OddHeader: "&R&P", +// OddFooter: "&C&F", +// EvenHeader: "&L&P", +// EvenFooter: "&L&D&R&T", +// FirstHeader: `&CCenter &"-,Bold"Bold&"-,Regular"HeaderU+000A&D`, +// }) +// +// This example shows: +// +// - The first page has its own header and footer +// +// - Odd and even-numbered pages have different headers and footers +// +// - Current page number in the right section of odd-page headers +// +// - Current workbook's file name in the center section of odd-page footers +// +// - Current page number in the left section of even-page headers +// +// - Current date in the left section and the current time in the right section +// of even-page footers +// +// - The text "Center Bold Header" on the first line of the center section of +// the first page, and the date on the second line of the center section of +// that same page +// +// - No footer on the first page +// +func (f *File) SetHeaderFooter(sheet string, settings *FormatHeaderFooter) error { + xlsx, err := f.workSheetReader(sheet) + if err != nil { + return err + } + if settings == nil { + xlsx.HeaderFooter = nil + return err + } + + v := reflect.ValueOf(*settings) + // Check 6 string type fields: OddHeader, OddFooter, EvenHeader, EvenFooter, + // FirstFooter, FirstHeader + for i := 4; i < v.NumField()-1; i++ { + if v.Field(i).Len() >= 255 { + return fmt.Errorf("field %s must be less than 255 characters", v.Type().Field(i).Name) + } + } + xlsx.HeaderFooter = &xlsxHeaderFooter{ + AlignWithMargins: settings.AlignWithMargins, + DifferentFirst: settings.DifferentFirst, + DifferentOddEven: settings.DifferentOddEven, + ScaleWithDoc: settings.ScaleWithDoc, + OddHeader: settings.OddHeader, + OddFooter: settings.OddFooter, + EvenHeader: settings.EvenHeader, + EvenFooter: settings.EvenFooter, + FirstFooter: settings.FirstFooter, + FirstHeader: settings.FirstHeader, + } + return err } // ProtectSheet provides a function to prevent other users from accidentally @@ -846,6 +1097,10 @@ type ( PageLayoutOrientation string // PageLayoutPaperSize defines the paper size of the worksheet PageLayoutPaperSize int + // FitToHeight specified number of vertical pages to fit on + FitToHeight int + // FitToWidth specified number of horizontal pages to fit on + FitToWidth int ) const ( @@ -885,11 +1140,43 @@ func (p *PageLayoutPaperSize) getPageLayout(ps *xlsxPageSetUp) { *p = PageLayoutPaperSize(ps.PaperSize) } +// setPageLayout provides a method to set the fit to height for the worksheet. +func (p FitToHeight) setPageLayout(ps *xlsxPageSetUp) { + if int(p) > 0 { + ps.FitToHeight = int(p) + } +} + +// getPageLayout provides a method to get the fit to height for the worksheet. +func (p *FitToHeight) getPageLayout(ps *xlsxPageSetUp) { + if ps == nil || ps.FitToHeight == 0 { + *p = 1 + return + } + *p = FitToHeight(ps.FitToHeight) +} + +// setPageLayout provides a method to set the fit to width for the worksheet. +func (p FitToWidth) setPageLayout(ps *xlsxPageSetUp) { + if int(p) > 0 { + ps.FitToWidth = int(p) + } +} + +// getPageLayout provides a method to get the fit to width for the worksheet. +func (p *FitToWidth) getPageLayout(ps *xlsxPageSetUp) { + if ps == nil || ps.FitToWidth == 0 { + *p = 1 + return + } + *p = FitToWidth(ps.FitToWidth) +} + // SetPageLayout provides a function to sets worksheet page layout. // // Available options: // PageLayoutOrientation(string) -// PageLayoutPaperSize(int) +// PageLayoutPaperSize(int) // // The following shows the paper size sorted by excelize index number: // @@ -1034,6 +1321,8 @@ func (f *File) SetPageLayout(sheet string, opts ...PageLayoutOption) error { // Available options: // PageLayoutOrientation(string) // PageLayoutPaperSize(int) +// FitToHeight(int) +// FitToWidth(int) func (f *File) GetPageLayout(sheet string, opts ...PageLayoutOptionPtr) error { s, err := f.workSheetReader(sheet) if err != nil { @@ -1047,39 +1336,295 @@ func (f *File) GetPageLayout(sheet string, opts ...PageLayoutOptionPtr) error { return err } -// workSheetRelsReader provides a function to get the pointer to the structure -// after deserialization of xl/worksheets/_rels/sheet%d.xml.rels. -func (f *File) workSheetRelsReader(path string) *xlsxWorkbookRels { - if f.WorkSheetRels[path] == nil { - _, ok := f.XLSX[path] - if ok { - c := xlsxWorkbookRels{} - _ = xml.Unmarshal(namespaceStrictToTransitional(f.readXML(path)), &c) - f.WorkSheetRels[path] = &c +// SetDefinedName provides a function to set the defined names of the workbook +// or worksheet. If not specified scope, the default scope is workbook. +// For example: +// +// f.SetDefinedName(&excelize.DefinedName{ +// Name: "Amount", +// RefersTo: "Sheet1!$A$2:$D$5", +// Comment: "defined name comment", +// Scope: "Sheet2", +// }) +// +func (f *File) SetDefinedName(definedName *DefinedName) error { + wb := f.workbookReader() + d := xlsxDefinedName{ + Name: definedName.Name, + Comment: definedName.Comment, + Data: definedName.RefersTo, + } + if definedName.Scope != "" { + if sheetID := f.getSheetID(definedName.Scope); sheetID != 0 { + sheetID-- + d.LocalSheetID = &sheetID + } + } + if wb.DefinedNames != nil { + for _, dn := range wb.DefinedNames.DefinedName { + var scope string + if dn.LocalSheetID != nil { + scope = f.getSheetNameByID(*dn.LocalSheetID + 1) + } + if scope == definedName.Scope && dn.Name == definedName.Name { + return errors.New("the same name already exists on the scope") + } + } + wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName, d) + return nil + } + wb.DefinedNames = &xlsxDefinedNames{ + DefinedName: []xlsxDefinedName{d}, + } + return nil +} + +// DeleteDefinedName provides a function to delete the defined names of the +// workbook or worksheet. If not specified scope, the default scope is +// workbook. For example: +// +// f.DeleteDefinedName(&excelize.DefinedName{ +// Name: "Amount", +// Scope: "Sheet2", +// }) +// +func (f *File) DeleteDefinedName(definedName *DefinedName) error { + wb := f.workbookReader() + if wb.DefinedNames != nil { + for idx, dn := range wb.DefinedNames.DefinedName { + var scope string + if dn.LocalSheetID != nil { + scope = f.getSheetNameByID(*dn.LocalSheetID + 1) + } + if scope == definedName.Scope && dn.Name == definedName.Name { + wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName[:idx], wb.DefinedNames.DefinedName[idx+1:]...) + return nil + } + } + } + return errors.New("no defined name on the scope") +} + +// GetDefinedName provides a function to get the defined names of the workbook +// or worksheet. +func (f *File) GetDefinedName() []DefinedName { + var definedNames []DefinedName + wb := f.workbookReader() + if wb.DefinedNames != nil { + for _, dn := range wb.DefinedNames.DefinedName { + definedName := DefinedName{ + Name: dn.Name, + Comment: dn.Comment, + RefersTo: dn.Data, + Scope: "Workbook", + } + if dn.LocalSheetID != nil { + definedName.Scope = f.getSheetNameByID(*dn.LocalSheetID + 1) + } + definedNames = append(definedNames, definedName) + } + } + return definedNames +} + +// GroupSheets provides a function to group worksheets by given worksheets +// name. Group worksheets must contain an active worksheet. +func (f *File) GroupSheets(sheets []string) error { + // check an active worksheet in group worksheets + var inActiveSheet bool + activeSheet := f.GetActiveSheetIndex() + sheetMap := f.GetSheetList() + for idx, sheetName := range sheetMap { + for _, s := range sheets { + if s == sheetName && idx == activeSheet { + inActiveSheet = true + } + } + } + if !inActiveSheet { + return errors.New("group worksheet must contain an active worksheet") + } + // check worksheet exists + ws := []*xlsxWorksheet{} + for _, sheet := range sheets { + xlsx, err := f.workSheetReader(sheet) + if err != nil { + return err + } + ws = append(ws, xlsx) + } + for _, s := range ws { + sheetViews := s.SheetViews.SheetView + if len(sheetViews) > 0 { + for idx := range sheetViews { + s.SheetViews.SheetView[idx].TabSelected = true + } + continue } } - return f.WorkSheetRels[path] + return nil } -// workSheetRelsWriter provides a function to save -// xl/worksheets/_rels/sheet%d.xml.rels after serialize structure. -func (f *File) workSheetRelsWriter() { - for p, r := range f.WorkSheetRels { - if r != nil { - v, _ := xml.Marshal(r) - f.saveFileList(p, v) +// UngroupSheets provides a function to ungroup worksheets. +func (f *File) UngroupSheets() error { + activeSheet := f.GetActiveSheetIndex() + for index, sheet := range f.GetSheetList() { + if activeSheet == index { + continue + } + ws, _ := f.workSheetReader(sheet) + sheetViews := ws.SheetViews.SheetView + if len(sheetViews) > 0 { + for idx := range sheetViews { + ws.SheetViews.SheetView[idx].TabSelected = false + } } } + return nil +} + +// InsertPageBreak create a page break to determine where the printed page +// ends and where begins the next one by given worksheet name and axis, so the +// content before the page break will be printed on one page and after the +// page break on another. +func (f *File) InsertPageBreak(sheet, cell string) (err error) { + var ws *xlsxWorksheet + var row, col int + var rowBrk, colBrk = -1, -1 + if ws, err = f.workSheetReader(sheet); err != nil { + return + } + if col, row, err = CellNameToCoordinates(cell); err != nil { + return + } + col-- + row-- + if col == row && col == 0 { + return + } + if ws.RowBreaks == nil { + ws.RowBreaks = &xlsxBreaks{} + } + if ws.ColBreaks == nil { + ws.ColBreaks = &xlsxBreaks{} + } + + for idx, brk := range ws.RowBreaks.Brk { + if brk.ID == row { + rowBrk = idx + } + } + for idx, brk := range ws.ColBreaks.Brk { + if brk.ID == col { + colBrk = idx + } + } + + if row != 0 && rowBrk == -1 { + ws.RowBreaks.Brk = append(ws.RowBreaks.Brk, &xlsxBrk{ + ID: row, + Max: 16383, + Man: true, + }) + ws.RowBreaks.ManualBreakCount++ + } + if col != 0 && colBrk == -1 { + ws.ColBreaks.Brk = append(ws.ColBreaks.Brk, &xlsxBrk{ + ID: col, + Max: 1048575, + Man: true, + }) + ws.ColBreaks.ManualBreakCount++ + } + ws.RowBreaks.Count = len(ws.RowBreaks.Brk) + ws.ColBreaks.Count = len(ws.ColBreaks.Brk) + return +} + +// RemovePageBreak remove a page break by given worksheet name and axis. +func (f *File) RemovePageBreak(sheet, cell string) (err error) { + var ws *xlsxWorksheet + var row, col int + if ws, err = f.workSheetReader(sheet); err != nil { + return + } + if col, row, err = CellNameToCoordinates(cell); err != nil { + return + } + col-- + row-- + if col == row && col == 0 { + return + } + removeBrk := func(ID int, brks []*xlsxBrk) []*xlsxBrk { + for i, brk := range brks { + if brk.ID == ID { + brks = append(brks[:i], brks[i+1:]...) + } + } + return brks + } + if ws.RowBreaks == nil || ws.ColBreaks == nil { + return + } + rowBrks := len(ws.RowBreaks.Brk) + colBrks := len(ws.ColBreaks.Brk) + if rowBrks > 0 && rowBrks == colBrks { + ws.RowBreaks.Brk = removeBrk(row, ws.RowBreaks.Brk) + ws.ColBreaks.Brk = removeBrk(col, ws.ColBreaks.Brk) + ws.RowBreaks.Count = len(ws.RowBreaks.Brk) + ws.ColBreaks.Count = len(ws.ColBreaks.Brk) + ws.RowBreaks.ManualBreakCount-- + ws.ColBreaks.ManualBreakCount-- + return + } + if rowBrks > 0 && rowBrks > colBrks { + ws.RowBreaks.Brk = removeBrk(row, ws.RowBreaks.Brk) + ws.RowBreaks.Count = len(ws.RowBreaks.Brk) + ws.RowBreaks.ManualBreakCount-- + return + } + if colBrks > 0 && colBrks > rowBrks { + ws.ColBreaks.Brk = removeBrk(col, ws.ColBreaks.Brk) + ws.ColBreaks.Count = len(ws.ColBreaks.Brk) + ws.ColBreaks.ManualBreakCount-- + } + return +} + +// relsReader provides a function to get the pointer to the structure +// after deserialization of xl/worksheets/_rels/sheet%d.xml.rels. +func (f *File) relsReader(path string) *xlsxRelationships { + var err error + + if f.Relationships[path] == nil { + _, ok := f.XLSX[path] + if ok { + c := xlsxRelationships{} + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(f.readXML(path)))). + Decode(&c); err != nil && err != io.EOF { + log.Printf("xml decode error: %s", err) + } + f.Relationships[path] = &c + } + } + + return f.Relationships[path] } // fillSheetData ensures there are enough rows, and columns in the chosen // row to accept data. Missing rows are backfilled and given their row number +// Uses the last populated row as a hint for the size of the next row to add func prepareSheetXML(xlsx *xlsxWorksheet, col int, row int) { rowCount := len(xlsx.SheetData.Row) + sizeHint := 0 + if rowCount > 0 { + sizeHint = len(xlsx.SheetData.Row[rowCount-1].C) + } if rowCount < row { // append missing rows for rowIdx := rowCount; rowIdx < row; rowIdx++ { - xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{R: rowIdx + 1}) + xlsx.SheetData.Row = append(xlsx.SheetData.Row, xlsxRow{R: rowIdx + 1, C: make([]xlsxC, 0, sizeHint)}) } } rowData := &xlsx.SheetData.Row[row-1] |