diff options
Diffstat (limited to 'rows.go')
| -rw-r--r-- | rows.go | 348 |
1 files changed, 203 insertions, 145 deletions
@@ -1,19 +1,21 @@ -// 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 import ( "bytes" "encoding/xml" + "errors" "fmt" "io" + "log" "math" "strconv" ) @@ -21,8 +23,16 @@ import ( // GetRows return all the rows in a sheet by given worksheet name (case // sensitive). For example: // -// rows, err := f.GetRows("Sheet1") -// for _, row := range rows { +// rows, err := f.Rows("Sheet1") +// if err != nil { +// fmt.Println(err) +// return +// } +// for rows.Next() { +// row, err := rows.Columns() +// if err != nil { +// fmt.Println(err) +// } // for _, colCell := range row { // fmt.Print(colCell, "\t") // } @@ -30,95 +40,38 @@ import ( // } // func (f *File) GetRows(sheet string) ([][]string, error) { - name, ok := f.sheetMap[trimSheetName(sheet)] - if !ok { - return nil, nil - } - - xlsx, err := f.workSheetReader(sheet) + rows, err := f.Rows(sheet) if err != nil { return nil, err } - if xlsx != nil { - output, _ := xml.Marshal(f.Sheet[name]) - f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(output)) - } - - xml.NewDecoder(bytes.NewReader(f.readXML(name))) - d := f.sharedStringsReader() - var ( - inElement string - rowData xlsxRow - ) - - rowCount, colCount, err := f.getTotalRowsCols(name) - if err != nil { - return nil, nil - } - rows := make([][]string, rowCount) - for i := range rows { - rows[i] = make([]string, colCount) - } - - var row int - decoder := xml.NewDecoder(bytes.NewReader(f.readXML(name))) - for { - token, _ := decoder.Token() - if token == nil { + results := make([][]string, 0, 64) + for rows.Next() { + if rows.Error() != nil { break } - switch startElement := token.(type) { - case xml.StartElement: - inElement = startElement.Name.Local - if inElement == "row" { - rowData = xlsxRow{} - _ = decoder.DecodeElement(&rowData, &startElement) - cr := rowData.R - 1 - for _, colCell := range rowData.C { - col, _, err := CellNameToCoordinates(colCell.R) - if err != nil { - return nil, err - } - val, _ := colCell.getValueFrom(f, d) - rows[cr][col-1] = val - if val != "" { - row = rowData.R - } - } - } - default: + row, err := rows.Columns() + if err != nil { + break } + results = append(results, row) } - return rows[:row], nil + return results, nil } // Rows defines an iterator to a sheet type Rows struct { - decoder *xml.Decoder - token xml.Token - err error - f *File + err error + curRow, totalRow, stashRow int + sheet string + rows []xlsxRow + f *File + decoder *xml.Decoder } // Next will return true if find the next row element. func (rows *Rows) Next() bool { - for { - rows.token, rows.err = rows.decoder.Token() - if rows.err == io.EOF { - rows.err = nil - } - if rows.token == nil { - return false - } - - switch startElement := rows.token.(type) { - case xml.StartElement: - inElement := startElement.Name.Local - if inElement == "row" { - return true - } - } - } + rows.curRow++ + return rows.curRow <= rows.totalRow } // Error will return the error when the find next row element @@ -128,23 +81,62 @@ func (rows *Rows) Error() error { // Columns return the current row's column values func (rows *Rows) Columns() ([]string, error) { - if rows.token == nil { - return []string{}, nil + var ( + err error + inElement string + row, cellCol int + columns []string + ) + + if rows.stashRow >= rows.curRow { + return columns, err } - startElement := rows.token.(xml.StartElement) - r := xlsxRow{} - _ = rows.decoder.DecodeElement(&r, &startElement) + d := rows.f.sharedStringsReader() - columns := make([]string, len(r.C)) - for _, colCell := range r.C { - col, _, err := CellNameToCoordinates(colCell.R) - if err != nil { - return columns, err + for { + token, _ := rows.decoder.Token() + if token == nil { + break + } + switch startElement := token.(type) { + case xml.StartElement: + inElement = startElement.Name.Local + if inElement == "row" { + for _, attr := range startElement.Attr { + if attr.Name.Local == "r" { + row, err = strconv.Atoi(attr.Value) + if err != nil { + return columns, err + } + if row > rows.curRow { + rows.stashRow = row - 1 + return columns, err + } + } + } + } + if inElement == "c" { + colCell := xlsxC{} + _ = rows.decoder.DecodeElement(&colCell, &startElement) + cellCol, _, err = CellNameToCoordinates(colCell.R) + if err != nil { + return columns, err + } + blank := cellCol - len(columns) + for i := 1; i < blank; i++ { + columns = append(columns, "") + } + val, _ := colCell.getValueFrom(rows.f, d) + columns = append(columns, val) + } + case xml.EndElement: + inElement = startElement.Name.Local + if inElement == "row" { + return columns, err + } } - val, _ := colCell.getValueFrom(rows.f, d) - columns[col-1] = val } - return columns, nil + return columns, err } // ErrSheetNotExist defines an error of sheet is not exist @@ -153,14 +145,22 @@ type ErrSheetNotExist struct { } func (err ErrSheetNotExist) Error() string { - return fmt.Sprintf("Sheet %s is not exist", string(err.SheetName)) + return fmt.Sprintf("sheet %s is not exist", string(err.SheetName)) } -// Rows return a rows iterator. For example: +// Rows returns a rows iterator, used for streaming reading data for a +// worksheet with a large data. For example: // // rows, err := f.Rows("Sheet1") +// if err != nil { +// fmt.Println(err) +// return +// } // for rows.Next() { -// row, err := rows.Columns() +// row, err := rows.Columns() +// if err != nil { +// fmt.Println(err) +// } // for _, colCell := range row { // fmt.Print(colCell, "\t") // } @@ -168,31 +168,22 @@ func (err ErrSheetNotExist) Error() string { // } // func (f *File) Rows(sheet string) (*Rows, error) { - xlsx, err := f.workSheetReader(sheet) - if err != nil { - return nil, err - } name, ok := f.sheetMap[trimSheetName(sheet)] if !ok { return nil, 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)) } - return &Rows{ - f: f, - decoder: xml.NewDecoder(bytes.NewReader(f.readXML(name))), - }, nil -} - -// getTotalRowsCols provides a function to get total columns and rows in a -// worksheet. -func (f *File) getTotalRowsCols(name string) (int, int, error) { - decoder := xml.NewDecoder(bytes.NewReader(f.readXML(name))) - var inElement string - var r xlsxRow - var tr, tc int + var ( + err error + inElement string + row int + rows Rows + ) + decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) for { token, _ := decoder.Token() if token == nil { @@ -202,23 +193,23 @@ func (f *File) getTotalRowsCols(name string) (int, int, error) { case xml.StartElement: inElement = startElement.Name.Local if inElement == "row" { - r = xlsxRow{} - _ = decoder.DecodeElement(&r, &startElement) - tr = r.R - for _, colCell := range r.C { - col, _, err := CellNameToCoordinates(colCell.R) - if err != nil { - return tr, tc, err - } - if col > tc { - tc = col + for _, attr := range startElement.Attr { + if attr.Name.Local == "r" { + row, err = strconv.Atoi(attr.Value) + if err != nil { + return &rows, err + } } } + rows.totalRow = row } default: } } - return tr, tc, nil + rows.f = f + rows.sheet = name + rows.decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name))) + return &rows, nil } // SetRowHeight provides a function to set the height of a single row. For @@ -248,7 +239,8 @@ func (f *File) SetRowHeight(sheet string, row int, height float64) error { // name and row index. func (f *File) getRowHeight(sheet string, row int) int { xlsx, _ := f.workSheetReader(sheet) - for _, v := range xlsx.SheetData.Row { + for i := range xlsx.SheetData.Row { + v := &xlsx.SheetData.Row[i] if v.R == row+1 && v.Ht != 0 { return int(convertRowHeightToPixels(v.Ht)) } @@ -286,15 +278,21 @@ func (f *File) GetRowHeight(sheet string, row int) (float64, error) { // sharedStringsReader provides a function to get the pointer to the structure // after deserialization of xl/sharedStrings.xml. func (f *File) sharedStringsReader() *xlsxSST { + var err error + if f.SharedStrings == nil { var sharedStrings xlsxSST ss := f.readXML("xl/sharedStrings.xml") if len(ss) == 0 { ss = f.readXML("xl/SharedStrings.xml") } - _ = xml.Unmarshal(namespaceStrictToTransitional(ss), &sharedStrings) + if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(ss))). + Decode(&sharedStrings); err != nil && err != io.EOF { + log.Printf("xml decode error: %s", err) + } f.SharedStrings = &sharedStrings } + return f.SharedStrings } @@ -304,20 +302,21 @@ func (f *File) sharedStringsReader() *xlsxSST { func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) { switch xlsx.T { case "s": - xlsxSI := 0 - xlsxSI, _ = strconv.Atoi(xlsx.V) - if len(d.SI[xlsxSI].R) > 0 { - value := "" - for _, v := range d.SI[xlsxSI].R { - value += v.T + if xlsx.V != "" { + xlsxSI := 0 + xlsxSI, _ = strconv.Atoi(xlsx.V) + if len(d.SI) > xlsxSI { + return f.formattedValue(xlsx.S, d.SI[xlsxSI].String()), nil } - return value, nil } - return f.formattedValue(xlsx.S, d.SI[xlsxSI].T), nil + return f.formattedValue(xlsx.S, xlsx.V), nil case "str": return f.formattedValue(xlsx.S, xlsx.V), nil case "inlineStr": - return f.formattedValue(xlsx.S, xlsx.IS.T), nil + if xlsx.IS != nil { + return f.formattedValue(xlsx.S, xlsx.IS.String()), nil + } + return f.formattedValue(xlsx.S, xlsx.V), nil default: return f.formattedValue(xlsx.S, xlsx.V), nil } @@ -364,8 +363,8 @@ func (f *File) GetRowVisible(sheet string, row int) (bool, error) { } // SetRowOutlineLevel provides a function to set outline level number of a -// single row by given worksheet name and Excel row number. For example, -// outline row 2 in Sheet1 to level 1: +// single row by given worksheet name and Excel row number. The value of +// parameter 'level' is 1-7. For example, outline row 2 in Sheet1 to level 1: // // err := f.SetRowOutlineLevel("Sheet1", 2, 1) // @@ -373,6 +372,9 @@ func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error { if row < 1 { return newInvalidRowNumberError(row) } + if level > 7 || level < 1 { + return errors.New("invalid outline level") + } xlsx, err := f.workSheetReader(sheet) if err != nil { return err @@ -421,16 +423,18 @@ func (f *File) RemoveRow(sheet string, row int) error { return err } if row > len(xlsx.SheetData.Row) { - return nil - } - for rowIdx := range xlsx.SheetData.Row { - if xlsx.SheetData.Row[rowIdx].R == row { - xlsx.SheetData.Row = append(xlsx.SheetData.Row[:rowIdx], - xlsx.SheetData.Row[rowIdx+1:]...)[:len(xlsx.SheetData.Row)-1] - return f.adjustHelper(sheet, rows, row, -1) + return f.adjustHelper(sheet, rows, row, -1) + } + keep := 0 + for rowIdx := 0; rowIdx < len(xlsx.SheetData.Row); rowIdx++ { + v := &xlsx.SheetData.Row[rowIdx] + if v.R != row { + xlsx.SheetData.Row[keep] = *v + keep++ } } - return nil + xlsx.SheetData.Row = xlsx.SheetData.Row[:keep] + return f.adjustHelper(sheet, rows, row, -1) } // InsertRow provides a function to insert a new row after given Excel row @@ -439,6 +443,10 @@ func (f *File) RemoveRow(sheet string, row int) error { // // err := f.InsertRow("Sheet1", 3) // +// Use this method with caution, which will affect changes in references such +// as formulas, charts, and so on. If there is any referenced value of the +// worksheet, it will cause a file error when you open it. The excelize only +// partially updates these references currently. func (f *File) InsertRow(sheet string, row int) error { if row < 1 { return newInvalidRowNumberError(row) @@ -517,6 +525,40 @@ func (f *File) DuplicateRowTo(sheet string, row, row2 int) error { } else { xlsx.SheetData.Row = append(xlsx.SheetData.Row, rowCopy) } + return f.duplicateMergeCells(sheet, xlsx, row, row2) +} + +// duplicateMergeCells merge cells in the destination row if there are single +// row merged cells in the copied row. +func (f *File) duplicateMergeCells(sheet string, xlsx *xlsxWorksheet, row, row2 int) error { + if xlsx.MergeCells == nil { + return nil + } + if row > row2 { + row++ + } + for _, rng := range xlsx.MergeCells.Cells { + coordinates, err := f.areaRefToCoordinates(rng.Ref) + if err != nil { + return err + } + if coordinates[1] < row2 && row2 < coordinates[3] { + return nil + } + } + for i := 0; i < len(xlsx.MergeCells.Cells); i++ { + areaData := xlsx.MergeCells.Cells[i] + coordinates, _ := f.areaRefToCoordinates(areaData.Ref) + x1, y1, x2, y2 := coordinates[0], coordinates[1], coordinates[2], coordinates[3] + if y1 == y2 && y1 == row { + from, _ := CoordinatesToCellName(x1, row2) + to, _ := CoordinatesToCellName(x2, row2) + if err := f.MergeCell(sheet, from, to); err != nil { + return err + } + i++ + } + } return nil } @@ -552,6 +594,22 @@ func checkRow(xlsx *xlsxWorksheet) error { if colCount == 0 { continue } + // check and fill the cell without r attribute in a row element + rCount := 0 + for idx, cell := range rowData.C { + rCount++ + if cell.R != "" { + lastR, _, err := CellNameToCoordinates(cell.R) + if err != nil { + return err + } + if lastR > rCount { + rCount = lastR + } + continue + } + rowData.C[idx].R, _ = CoordinatesToCellName(rCount, rowIdx+1) + } lastCol, _, err := CellNameToCoordinates(rowData.C[colCount-1].R) if err != nil { return err |