Go + Excel 学习 Excelize rows.go

Copyright 2016 - 2019 The excelize Authors.
版权所有2016-2019优秀作者。

All rights reserved.
保留所有权利。

Use of this source code is governed by a BSD-style license that can be found in the LICENSE file.
此源代码的使用由可在许可文件中找到的BSD样式许可管理。

Package excelize providing a set of functions that allow you to write to and read from XLSX files.
包提供了一组函数，允许您对xlsx文件进行写入和读取。

Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later.
支持读取和写入由Microsoft Excel™2007及更高版本生成的XLSX文件。

Support save file without losing original charts of XLSX.
支持保存文件而不丢失xlsx的原始图表。

This library needs Go version 1.10 or later.
此库需要go版本1.10或更高版本。

package excelize

import (
	"encoding/xml"
	"fmt"
	"math"
	"strconv"
)

type Rows struct

Rows defines an iterator to a sheet
行定义工作表的迭代器

type Rows struct {
	err    error
	f      *File
	rows   []xlsxRow
	curRow int
}

type ErrSheetNotExist struct

ErrSheetNotExist defines an error of sheet is not exist
errsheetnotexist定义工作表不存在的错误

type ErrSheetNotExist struct {
	SheetName string
}

func (err ErrSheetNotExist) Error() string

func (err ErrSheetNotExist) Error() string {
	return fmt.Sprintf("Sheet %s is not exist", string(err.SheetName))
}

func (rows *Rows) Next() bool

Next will return true if find the next row element.
如果找到下一行元素，next将返回true。

func (rows *Rows) Next() bool {
	return rows.curRow < len(rows.rows)
}

func (rows *Rows) Error() error

Error will return the error when the find next row element
查找下一行元素时，错误将返回Error

func (rows *Rows) Error() error {
	return rows.err
}

func convertRowHeightToPixels(height float64) float64

convertRowHeightToPixels provides a function to convert the height of a cell from user’s units to pixels.
convertRowHeightToPixels 提供一个函数，用于将单元格的高度从用户的单位转换为像素。

If the height hasn’t been set by the user we use the default value. If the row is hidden it has a value of zero.
如果用户没有设置高度，我们使用默认值。如果行被隐藏，则其值为零。

func convertRowHeightToPixels(height float64) float64 {
	var pixels float64
	if height == 0 {
		return pixels
	}
	pixels = math.Ceil(4.0 / 3.0 * height)
	return pixels
}

func (f *File) Rows(sheet string) (*Rows, error)

Rows return a rows iterator.
行返回行迭代器。

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 {
		data := f.readXML(name)
		f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpaceBytes(namespaceStrictToTransitional(data)))
	}
	return &Rows{
		f:    f,
		rows: xlsx.SheetData.Row,
	}, nil
}

func (f *File) GetRowHeight(sheet string, row int) (float64, error)

GetRowHeight provides a function to get row height by given worksheet name and row index.
GetRowHeight提供了一个函数，可以根据给定的工作表名称和行索引获取行高。

func (f *File) GetRowHeight(sheet string, row int) (float64, error) {
	if row < 1 {
		return defaultRowHeightPixels, newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return defaultRowHeightPixels, err
	}
	if row > len(xlsx.SheetData.Row) {
		return defaultRowHeightPixels, nil // it will be better to use 0, but we take care with BC
	}
	for _, v := range xlsx.SheetData.Row {
		if v.R == row && v.Ht != 0 {
			return v.Ht, nil
		}
	}
	// Optimisation for when the row heights haven't changed.
	return defaultRowHeightPixels, nil
}

func (f *File) getRowHeight(sheet string, row int) int

getRowHeight provides a function to get row height in pixels by given sheet name and row index.
getRowHeight提供了一个函数，可以通过给定的工作表名称和行索引以像素为单位获取行高。

func (f *File) getRowHeight(sheet string, row int) int {
	xlsx, _ := f.workSheetReader(sheet)
	for _, v := range xlsx.SheetData.Row {
		if v.R == row+1 && v.Ht != 0 {
			return int(convertRowHeightToPixels(v.Ht))
		}
	}
	// Optimisation for when the row heights haven't changed.当行高没有变化时进行优化。
	return int(defaultRowHeightPixels)
}

func (f *File) SetRowHeight(sheet string, row int, height float64) error

SetRowHeight provides a function to set the height of a single row.
set row height提供了一个函数来设置单行的高度。

func (f *File) SetRowHeight(sheet string, row int, height float64) error {
	if row < 1 {
		return newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}

	prepareSheetXML(xlsx, 0, row)

	rowIdx := row - 1
	xlsx.SheetData.Row[rowIdx].Ht = height
	xlsx.SheetData.Row[rowIdx].CustomHeight = true
	return nil
}

func (f *File) GetRows(sheet string) ([][]string, error)

GetRows return all the rows in a sheet by given worksheet name (case sensitive).
getrows按给定的工作表名称返回工作表中的所有行（区分大小写）。

返回一个二维字符串数组和一个错误信息。

func (f *File) GetRows(sheet string) ([][]string, error) {
	rows, err := f.Rows(sheet)
	if err != nil {
		return nil, err
	}
	results := make([][]string, 0, 64)
	for rows.Next() {
		if rows.Error() != nil {
			break
		}
		row, err := rows.Columns()
		if err != nil {
			break
		}
		results = append(results, row)
	}
	return results, nil
}

func (rows *Rows) Columns() ([]string, error)

Columns return the current row’s column values
列返回当前行的列值

func (rows *Rows) Columns() ([]string, error) {
	curRow := rows.rows[rows.curRow]
	rows.curRow++

	columns := make([]string, len(curRow.C))
	d := rows.f.sharedStringsReader()
	for _, colCell := range curRow.C {
		col, _, err := CellNameToCoordinates(colCell.R)
		if err != nil {
			return columns, err
		}
		val, _ := colCell.getValueFrom(rows.f, d)
		columns[col-1] = val
	}
	return columns, nil
}

func (f *File) InsertRow(sheet string, row int) error

InsertRow provides a function to insert a new row after given Excel row number starting from 1.
InsertRow提供一个函数，用于在给定的Excel行号（从1开始）之后插入新行。

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.
excelize当前仅部分更新这些引用。

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

DuplicateRow inserts a copy of specified row (by its Excel row number) below.
duplicateRow在下面插入指定行的副本（按其excel行号）。

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.
excelize当前仅部分更新这些引用。

func (f *File) DuplicateRow(sheet string, row int) error {
	return f.DuplicateRowTo(sheet, row, row+1)
}

func (f *File) RemoveRow(sheet string, row int) error

RemoveRow provides a function to remove single row by given worksheet name and Excel row number.
RemoveRow提供了一个按给定工作表名称和Excel行号删除单行的函数。

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.
excelize当前仅部分更新这些引用。

func (f *File) RemoveRow(sheet string, row int) error {
	if row < 1 {
		return newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	if row > len(xlsx.SheetData.Row) {
		return f.adjustHelper(sheet, rows, row, -1)
	}
	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 nil
}

func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error)

getValueFrom return a value from a column/row cell, this function is inteded to be used with for range on rows an argument with the xlsx opened file.
getValueFrom从列/行单元格返回一个值，此函数将与xlsx打开文件的参数一起用于行上的范围。

func (xlsx *xlsxC) getValueFrom(f *File, d *xlsxSST) (string, error) {
	switch xlsx.T {
	case "s":
		xlsxSI := 0
		xlsxSI, _ = strconv.Atoi(xlsx.V)
		return f.formattedValue(xlsx.S, d.SI[xlsxSI].String()), nil
	case "str":
		return f.formattedValue(xlsx.S, xlsx.V), nil
	case "inlineStr":
		return f.formattedValue(xlsx.S, xlsx.IS.String()), nil
	default:
		return f.formattedValue(xlsx.S, xlsx.V), nil
	}
}

func (f *File) SetRowVisible(sheet string, row int, visible bool) error

SetRowVisible provides a function to set visible of a single row by given worksheet name and Excel row number.
SetRowVisible 提供了一个函数，用于根据给定的工作表名称和excel行号设置单行的可见。

func (f *File) SetRowVisible(sheet string, row int, visible bool) error {
	if row < 1 {
		return newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	prepareSheetXML(xlsx, 0, row)
	xlsx.SheetData.Row[row-1].Hidden = !visible
	return nil
}

func checkRow(xlsx *xlsxWorksheet) error

checkRow provides a function to check and fill each column element for all rows and make that is continuous in a worksheet of XML.
checkRow提供了一个函数，用于检查和填充所有行的每个列元素，并使其在XML工作表中连续。

Noteice: this method could be very slow for large spreadsheets (more than 3000 rows one sheet).
注：对于大型电子表格（一张超过3000行），这种方法可能非常慢。

func checkRow(xlsx *xlsxWorksheet) error {
	for rowIdx := range xlsx.SheetData.Row {
		rowData := &xlsx.SheetData.Row[rowIdx]

		colCount := len(rowData.C)
		if colCount == 0 {
			continue
		}
		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 = xlsx.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
				}
				xlsx.SheetData.Row[rowIdx].C[colNum-1] = *colData
			}
		}
	}
	return nil
}

func (f *File) GetRowOutlineLevel(sheet string, row int) (uint8, error)

GetRowOutlineLevel provides a function to get outline level number of a single row by given worksheet name and Excel row number.
getRowOutlineLevel提供了一个函数，用于根据给定的工作表名称和Excel行号获取单行的大纲级别编号。

func (f *File) GetRowOutlineLevel(sheet string, row int) (uint8, error) {
	if row < 1 {
		return 0, newInvalidRowNumberError(row)
	}
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return 0, err
	}
	if row > len(xlsx.SheetData.Row) {
		return 0, nil
	}
	return xlsx.SheetData.Row[row-1].OutlineLevel, nil
}

func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error

SetRowOutlineLevel provides a function to set outline level number of a single row by given worksheet name and Excel row number.
SetRowOutlineLevel 提供了一个函数，用于根据给定的工作表名称和Excel行号设置单行的大纲级别号。

func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error {
	if row < 1 {
		return newInvalidRowNumberError(row)
	}
	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	prepareSheetXML(xlsx, 0, row)
	xlsx.SheetData.Row[row-1].OutlineLevel = level
	return nil
}

func (f *File) GetRowVisible(sheet string, row int) (bool, error)

GetRowVisible provides a function to get visible of a single row by given worksheet name and Excel row number.
GetRowVisible 提供了一个函数，可以通过给定的工作表名称和excel行号获取是否该行显示。

func (f *File) GetRowVisible(sheet string, row int) (bool, error) {
	if row < 1 {
		return false, newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return false, err
	}
	if row > len(xlsx.SheetData.Row) {
		return false, nil
	}
	return !xlsx.SheetData.Row[row-1].Hidden, nil
}

func (f *File) sharedStringsReader() *xlsxSST

sharedStringsReader provides a function to get the pointer to the structure after deserialization of xl/sharedStrings.xml.
sharedStringsReader提供了一个函数，用于在对xl/sharedStrings.xml进行反序列化之后获取指向结构的指针。

func (f *File) sharedStringsReader() *xlsxSST {
	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)
		f.SharedStrings = &sharedStrings
	}
	return f.SharedStrings
}

func (f *File) DuplicateRowTo(sheet string, row, row2 int) error

DuplicateRowTo inserts a copy of specified row by it Excel number to specified row position moving down exists rows after target position.
DuplicateRowTo 按excel数字将指定行的副本插入到指定行位置在目标位置后下移已存在的行。

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.
excelize当前仅部分更新这些引用。

func (f *File) DuplicateRowTo(sheet string, row, row2 int) error {
	if row < 1 {
		return newInvalidRowNumberError(row)
	}

	xlsx, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	if row > len(xlsx.SheetData.Row) || row2 < 1 || row == row2 {
		return nil
	}

	var ok bool
	var rowCopy xlsxRow

	for i, r := range xlsx.SheetData.Row {
		if r.R == row {
			rowCopy = xlsx.SheetData.Row[i]
			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 xlsx.SheetData.Row {
		if r.R == row2 {
			idx2 = i
			break
		}
	}
	if idx2 == -1 && len(xlsx.SheetData.Row) >= row2 {
		return nil
	}

	rowCopy.C = append(make([]xlsxC, 0, len(rowCopy.C)), rowCopy.C...)
	f.ajustSingleRowDimensions(&rowCopy, row2)

	if idx2 != -1 {
		xlsx.SheetData.Row[idx2] = rowCopy
	} else {
		xlsx.SheetData.Row = append(xlsx.SheetData.Row, rowCopy)
	}
	return nil
}