diff options
Diffstat (limited to 'sheet.go')
| -rw-r--r-- | sheet.go | 169 |
1 files changed, 107 insertions, 62 deletions
@@ -129,12 +129,19 @@ func (f *File) workSheetWriter() { } } -// 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++ } @@ -154,11 +161,12 @@ func (f *File) setContentTypes(index int) { // 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 @@ -212,7 +220,7 @@ func replaceRelationshipsBytes(content []byte) []byte { // 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 xr:uid="{00000000-0001-0000-0000-000000000000}" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x14ac xr xr2 xr3 xr6 xr10 x15" 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">`) + newXmlns := []byte(`<workbook` + templateNamespaceIDMap) return bytes.Replace(workbookMarshal, oldXmlns, newXmlns, -1) } @@ -227,6 +235,9 @@ func (f *File) SetActiveSheet(index int) { wb := f.workbookReader() for activeTab, sheet := range wb.Sheets.Sheet { if sheet.SheetID == index { + if wb.BookViews == nil { + wb.BookViews = &xlsxBookViews{} + } if len(wb.BookViews.WorkBookView) > 0 { wb.BookViews.WorkBookView[0].ActiveTab = activeTab } else { @@ -258,16 +269,13 @@ func (f *File) SetActiveSheet(index int) { func (f *File) GetActiveSheetIndex() int { wb := f.workbookReader() if wb != nil { - view := wb.BookViews.WorkBookView - sheets := wb.Sheets.Sheet - var activeTab int - if len(view) > 0 { - activeTab = view[0].ActiveTab - if len(sheets) > activeTab && sheets[activeTab].SheetID != 0 { - return sheets[activeTab].SheetID + 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 { + if len(wb.Sheets.Sheet) >= 1 { return wb.Sheets.Sheet[0].SheetID } } @@ -418,6 +426,13 @@ func (f *File) DeleteSheet(name string) { 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())) } @@ -691,30 +706,23 @@ func (f *File) GetSheetVisible(name string) bool { // // result, err := f.SearchSheet("Sheet1", "[0-9]", true) // -func (f *File) SearchSheet(sheet, value string, reg ...bool) (result []string, err error) { +func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error) { var ( - xlsx *xlsxWorksheet - regSearch, r, ok bool - name string - output []byte + regSearch bool + result []string ) - - for _, r = range reg { + for _, r := range reg { regSearch = r } - if xlsx, err = f.workSheetReader(sheet); err != nil { - return - } - if name, ok = f.sheetMap[trimSheetName(sheet)]; !ok { - return + name, ok := f.sheetMap[trimSheetName(sheet)] + if !ok { + return result, ErrSheetNotExist{sheet} } - if xlsx != nil { - if output, err = xml.Marshal(f.Sheet[name]); err != nil { - return - } + if f.Sheet[name] != nil { + // flush data + output, _ := xml.Marshal(f.Sheet[name]) f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(output)) } - return f.searchSheet(name, value, regSearch) } @@ -722,17 +730,16 @@ func (f *File) SearchSheet(sheet, value string, reg ...bool) (result []string, e // cell value, and regular expression. func (f *File) searchSheet(name, value string, regSearch bool) (result []string, err error) { var ( - d *xlsxSST - decoder *xml.Decoder - inElement string - r xlsxRow - token xml.Token + cellName, inElement string + cellCol, row int + d *xlsxSST ) d = f.sharedStringsReader() - decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) + decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) for { - if token, err = decoder.Token(); err != nil || token == nil { + token, err := decoder.Token() + if err != nil || token == nil { if err == io.EOF { err = nil } @@ -742,31 +749,38 @@ func (f *File) searchSheet(name, value string, regSearch bool) (result []string, 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 + for _, attr := range startElement.Attr { + if attr.Name.Local == "r" { + row, err = strconv.Atoi(attr.Value) + if err != nil { + return result, err } } - - cellCol, _, err := CellNameToCoordinates(colCell.R) - if err != nil { - return result, err + } + } + 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: } @@ -1288,7 +1302,7 @@ func (f *File) SetDefinedName(definedName *DefinedName) error { scope = f.GetSheetName(*dn.LocalSheetID + 1) } if scope == definedName.Scope && dn.Name == definedName.Name { - return errors.New("the same name already exists on scope") + return errors.New("the same name already exists on the scope") } } wb.DefinedNames.DefinedName = append(wb.DefinedNames.DefinedName, d) @@ -1300,6 +1314,32 @@ func (f *File) SetDefinedName(definedName *DefinedName) error { 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.GetSheetName(*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 { @@ -1401,12 +1441,17 @@ func (f *File) relsReader(path string) *xlsxRelationships { // 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] |