123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724 |
- package excelize
- import (
- "bytes"
- "encoding/xml"
- "fmt"
- "io"
- "log"
- "math"
- "strconv"
- "github.com/mohae/deepcopy"
- )
- func (f *File) GetRows(sheet string) ([][]string, error) {
- rows, err := f.Rows(sheet)
- if err != nil {
- return nil, err
- }
- results, cur, max := make([][]string, 0, 64), 0, 0
- for rows.Next() {
- cur++
- row, err := rows.Columns()
- if err != nil {
- break
- }
- results = append(results, row)
- if len(row) > 0 {
- max = cur
- }
- }
- return results[:max], nil
- }
- type Rows struct {
- err error
- curRow, totalRow, stashRow int
- sheet string
- f *File
- decoder *xml.Decoder
- }
- func (rows *Rows) Next() bool {
- rows.curRow++
- return rows.curRow <= rows.totalRow
- }
- func (rows *Rows) Error() error {
- return rows.err
- }
- func (rows *Rows) Columns() ([]string, error) {
- var rowIterator rowXMLIterator
- if rows.stashRow >= rows.curRow {
- return rowIterator.columns, rowIterator.err
- }
- rowIterator.rows = rows
- rowIterator.d = rows.f.sharedStringsReader()
- for {
- token, _ := rows.decoder.Token()
- if token == nil {
- break
- }
- switch xmlElement := token.(type) {
- case xml.StartElement:
- rowIterator.inElement = xmlElement.Name.Local
- if rowIterator.inElement == "row" {
- rowIterator.row++
- if rowIterator.attrR, rowIterator.err = attrValToInt("r", xmlElement.Attr); rowIterator.attrR != 0 {
- rowIterator.row = rowIterator.attrR
- }
- if rowIterator.row > rowIterator.rows.curRow {
- rowIterator.rows.stashRow = rowIterator.row - 1
- return rowIterator.columns, rowIterator.err
- }
- }
- rowXMLHandler(&rowIterator, &xmlElement)
- if rowIterator.err != nil {
- return rowIterator.columns, rowIterator.err
- }
- case xml.EndElement:
- rowIterator.inElement = xmlElement.Name.Local
- if rowIterator.row == 0 {
- rowIterator.row = rowIterator.rows.curRow
- }
- if rowIterator.inElement == "row" && rowIterator.row+1 < rowIterator.rows.curRow {
- return rowIterator.columns, rowIterator.err
- }
- if rowIterator.inElement == "sheetData" {
- return rowIterator.columns, rowIterator.err
- }
- }
- }
- return rowIterator.columns, rowIterator.err
- }
- func appendSpace(l int, s []string) []string {
- for i := 1; i < l; i++ {
- s = append(s, "")
- }
- return s
- }
- type ErrSheetNotExist struct {
- SheetName string
- }
- func (err ErrSheetNotExist) Error() string {
- return fmt.Sprintf("sheet %s is not exist", string(err.SheetName))
- }
- type rowXMLIterator struct {
- err error
- inElement string
- attrR, cellCol, row int
- columns []string
- rows *Rows
- d *xlsxSST
- }
- func rowXMLHandler(rowIterator *rowXMLIterator, xmlElement *xml.StartElement) {
- rowIterator.err = nil
- if rowIterator.inElement == "c" {
- rowIterator.cellCol++
- colCell := xlsxC{}
- _ = rowIterator.rows.decoder.DecodeElement(&colCell, xmlElement)
- if colCell.R != "" {
- if rowIterator.cellCol, _, rowIterator.err = CellNameToCoordinates(colCell.R); rowIterator.err != nil {
- return
- }
- }
- blank := rowIterator.cellCol - len(rowIterator.columns)
- val, _ := colCell.getValueFrom(rowIterator.rows.f, rowIterator.d)
- if val != "" {
- rowIterator.columns = append(appendSpace(blank, rowIterator.columns), val)
- }
- }
- }
- func (f *File) Rows(sheet string) (*Rows, error) {
- name, ok := f.sheetMap[trimSheetName(sheet)]
- if !ok {
- return nil, ErrSheetNotExist{sheet}
- }
- if f.Sheet[name] != nil {
-
- output, _ := xml.Marshal(f.Sheet[name])
- f.saveFileList(name, f.replaceNameSpaceBytes(name, output))
- }
- var (
- err error
- inElement string
- row int
- rows Rows
- )
- decoder := f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
- for {
- token, _ := decoder.Token()
- if token == nil {
- break
- }
- switch xmlElement := token.(type) {
- case xml.StartElement:
- inElement = xmlElement.Name.Local
- if inElement == "row" {
- row++
- for _, attr := range xmlElement.Attr {
- if attr.Name.Local == "r" {
- row, err = strconv.Atoi(attr.Value)
- if err != nil {
- return &rows, err
- }
- }
- }
- rows.totalRow = row
- }
- case xml.EndElement:
- if xmlElement.Name.Local == "sheetData" {
- rows.f = f
- rows.sheet = name
- rows.decoder = f.xmlNewDecoder(bytes.NewReader(f.readXML(name)))
- return &rows, nil
- }
- default:
- }
- }
- return &rows, nil
- }
- func (f *File) SetRowHeight(sheet string, row int, height float64) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- if height > MaxRowHeight {
- return ErrMaxRowHeight
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- prepareSheetXML(ws, 0, row)
- rowIdx := row - 1
- ws.SheetData.Row[rowIdx].Ht = height
- ws.SheetData.Row[rowIdx].CustomHeight = true
- return nil
- }
- func (f *File) getRowHeight(sheet string, row int) int {
- ws, _ := f.workSheetReader(sheet)
- for i := range ws.SheetData.Row {
- v := &ws.SheetData.Row[i]
- if v.R == row+1 && v.Ht != 0 {
- return int(convertRowHeightToPixels(v.Ht))
- }
- }
-
- return int(defaultRowHeightPixels)
- }
- func (f *File) GetRowHeight(sheet string, row int) (float64, error) {
- if row < 1 {
- return defaultRowHeightPixels, newInvalidRowNumberError(row)
- }
- var ht = defaultRowHeight
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return ht, err
- }
- if ws.SheetFormatPr != nil && ws.SheetFormatPr.CustomHeight {
- ht = ws.SheetFormatPr.DefaultRowHeight
- }
- if row > len(ws.SheetData.Row) {
- return ht, nil
- }
- for _, v := range ws.SheetData.Row {
- if v.R == row && v.Ht != 0 {
- return v.Ht, nil
- }
- }
-
- return ht, nil
- }
- func (f *File) sharedStringsReader() *xlsxSST {
- var err error
- f.Lock()
- defer f.Unlock()
- relPath := f.getWorkbookRelsPath()
- if f.SharedStrings == nil {
- var sharedStrings xlsxSST
- ss := f.readXML("xl/sharedStrings.xml")
- if err = f.xmlNewDecoder(bytes.NewReader(namespaceStrictToTransitional(ss))).
- Decode(&sharedStrings); err != nil && err != io.EOF {
- log.Printf("xml decode error: %s", err)
- }
- if sharedStrings.UniqueCount == 0 {
- sharedStrings.UniqueCount = sharedStrings.Count
- }
- f.SharedStrings = &sharedStrings
- for i := range sharedStrings.SI {
- if sharedStrings.SI[i].T != nil {
- f.sharedStringsMap[sharedStrings.SI[i].T.Val] = i
- }
- }
- f.addContentTypePart(0, "sharedStrings")
- rels := f.relsReader(relPath)
- for _, rel := range rels.Relationships {
- if rel.Target == "/xl/sharedStrings.xml" {
- return f.SharedStrings
- }
- }
-
- f.addRels(relPath, SourceRelationshipSharedStrings, "/xl/sharedStrings.xml", "")
- }
- return f.SharedStrings
- }
- func (c *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) {
- f.Lock()
- defer f.Unlock()
- switch c.T {
- case "s":
- if c.V != "" {
- xlsxSI := 0
- xlsxSI, _ = strconv.Atoi(c.V)
- if len(d.SI) > xlsxSI {
- return f.formattedValue(c.S, d.SI[xlsxSI].String()), nil
- }
- }
- return f.formattedValue(c.S, c.V), nil
- case "str":
- return f.formattedValue(c.S, c.V), nil
- case "inlineStr":
- if c.IS != nil {
- return f.formattedValue(c.S, c.IS.String()), nil
- }
- return f.formattedValue(c.S, c.V), nil
- default:
- isNum, precision := isNumeric(c.V)
- if isNum && precision > 15 {
- val, _ := roundPrecision(c.V)
- if val != c.V {
- return f.formattedValue(c.S, val), nil
- }
- }
- return f.formattedValue(c.S, c.V), nil
- }
- }
- func roundPrecision(value string) (result string, err error) {
- var num float64
- if num, err = strconv.ParseFloat(value, 64); err != nil {
- return
- }
- result = fmt.Sprintf("%g", math.Round(num*numericPrecision)/numericPrecision)
- return
- }
- func (f *File) SetRowVisible(sheet string, row int, visible bool) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- prepareSheetXML(ws, 0, row)
- ws.SheetData.Row[row-1].Hidden = !visible
- return nil
- }
- func (f *File) GetRowVisible(sheet string, row int) (bool, error) {
- if row < 1 {
- return false, newInvalidRowNumberError(row)
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return false, err
- }
- if row > len(ws.SheetData.Row) {
- return false, nil
- }
- return !ws.SheetData.Row[row-1].Hidden, nil
- }
- func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- if level > 7 || level < 1 {
- return ErrOutlineLevel
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- prepareSheetXML(ws, 0, row)
- ws.SheetData.Row[row-1].OutlineLevel = level
- return nil
- }
- func (f *File) GetRowOutlineLevel(sheet string, row int) (uint8, error) {
- if row < 1 {
- return 0, newInvalidRowNumberError(row)
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return 0, err
- }
- if row > len(ws.SheetData.Row) {
- return 0, nil
- }
- return ws.SheetData.Row[row-1].OutlineLevel, nil
- }
- func (f *File) RemoveRow(sheet string, row int) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- if row > len(ws.SheetData.Row) {
- return f.adjustHelper(sheet, rows, row, -1)
- }
- keep := 0
- for rowIdx := 0; rowIdx < len(ws.SheetData.Row); rowIdx++ {
- v := &ws.SheetData.Row[rowIdx]
- if v.R != row {
- ws.SheetData.Row[keep] = *v
- keep++
- }
- }
- ws.SheetData.Row = ws.SheetData.Row[:keep]
- return f.adjustHelper(sheet, rows, row, -1)
- }
- func (f *File) InsertRow(sheet string, row int) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- return f.adjustHelper(sheet, rows, row, 1)
- }
- func (f *File) DuplicateRow(sheet string, row int) error {
- return f.DuplicateRowTo(sheet, row, row+1)
- }
- func (f *File) DuplicateRowTo(sheet string, row, row2 int) error {
- if row < 1 {
- return newInvalidRowNumberError(row)
- }
- ws, err := f.workSheetReader(sheet)
- if err != nil {
- return err
- }
- if row > len(ws.SheetData.Row) || row2 < 1 || row == row2 {
- return nil
- }
- var ok bool
- var rowCopy xlsxRow
- for i, r := range ws.SheetData.Row {
- if r.R == row {
- rowCopy = deepcopy.Copy(ws.SheetData.Row[i]).(xlsxRow)
- ok = true
- break
- }
- }
- if !ok {
- return nil
- }
- if err := f.adjustHelper(sheet, rows, row2, 1); err != nil {
- return err
- }
- idx2 := -1
- for i, r := range ws.SheetData.Row {
- if r.R == row2 {
- idx2 = i
- break
- }
- }
- if idx2 == -1 && len(ws.SheetData.Row) >= row2 {
- return nil
- }
- rowCopy.C = append(make([]xlsxC, 0, len(rowCopy.C)), rowCopy.C...)
- f.ajustSingleRowDimensions(&rowCopy, row2)
- if idx2 != -1 {
- ws.SheetData.Row[idx2] = rowCopy
- } else {
- ws.SheetData.Row = append(ws.SheetData.Row, rowCopy)
- }
- return f.duplicateMergeCells(sheet, ws, row, row2)
- }
- func (f *File) duplicateMergeCells(sheet string, ws *xlsxWorksheet, row, row2 int) error {
- if ws.MergeCells == nil {
- return nil
- }
- if row > row2 {
- row++
- }
- for _, rng := range ws.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(ws.MergeCells.Cells); i++ {
- areaData := ws.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
- }
- }
- }
- return nil
- }
- func checkRow(ws *xlsxWorksheet) error {
- for rowIdx := range ws.SheetData.Row {
- rowData := &ws.SheetData.Row[rowIdx]
- colCount := len(rowData.C)
- if colCount == 0 {
- continue
- }
-
- 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
- }
- if colCount < lastCol {
- oldList := rowData.C
- newlist := make([]xlsxC, 0, lastCol)
- rowData.C = ws.SheetData.Row[rowIdx].C[:0]
- for colIdx := 0; colIdx < lastCol; colIdx++ {
- cellName, err := CoordinatesToCellName(colIdx+1, rowIdx+1)
- if err != nil {
- return err
- }
- newlist = append(newlist, xlsxC{R: cellName})
- }
- rowData.C = newlist
- for colIdx := range oldList {
- colData := &oldList[colIdx]
- colNum, _, err := CellNameToCoordinates(colData.R)
- if err != nil {
- return err
- }
- ws.SheetData.Row[rowIdx].C[colNum-1] = *colData
- }
- }
- }
- return nil
- }
- func convertRowHeightToPixels(height float64) float64 {
- var pixels float64
- if height == 0 {
- return pixels
- }
- pixels = math.Ceil(4.0 / 3.0 * height)
- return pixels
- }
|