diff options
Diffstat (limited to 'cell.go')
| -rw-r--r-- | cell.go | 706 |
1 files changed, 302 insertions, 404 deletions
@@ -11,6 +11,7 @@ package excelize import ( "encoding/xml" + "errors" "fmt" "reflect" "strconv" @@ -29,18 +30,18 @@ const ( STCellFormulaTypeShared = "shared" ) -// mergeCellsParser provides a function to check merged cells in worksheet by -// given axis. -func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { - axis = strings.ToUpper(axis) - if xlsx.MergeCells != nil { - for i := 0; i < len(xlsx.MergeCells.Cells); i++ { - if checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref) { - axis = strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0] - } +// GetCellValue provides a function to get formatted value from cell by given +// worksheet name and axis in XLSX file. If it is possible to apply a format +// to the cell value, it will do so, if not then an error will be returned, +// along with the raw value of the cell. +func (f *File) GetCellValue(sheet, axis string) string { + return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool) { + val, err := c.getValueFrom(f, f.sharedStringsReader()) + if err != nil { + panic(err) // Fail fast to avoid future side effects! } - } - return axis + return val, true + }) } // SetCellValue provides a function to set value of a cell. The following @@ -68,256 +69,169 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { // Note that default date format is m/d/yy h:mm of time.Time type value. You can // set numbers format by SetCellStyle() method. func (f *File) SetCellValue(sheet, axis string, value interface{}) { - switch t := value.(type) { - case float32: - f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(float32)), 'f', -1, 32)) - case float64: - f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(float64)), 'f', -1, 64)) - case string: - f.SetCellStr(sheet, axis, t) - case []byte: - f.SetCellStr(sheet, axis, string(t)) - case time.Duration: - f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(value.(time.Duration).Seconds()/86400), 'f', -1, 32)) - f.setDefaultTimeStyle(sheet, axis, 21) - case time.Time: - f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(timeToExcelTime(timeToUTCTime(value.(time.Time)))), 'f', -1, 64)) - f.setDefaultTimeStyle(sheet, axis, 22) - case nil: - f.SetCellStr(sheet, axis, "") - case bool: - f.SetCellBool(sheet, axis, bool(value.(bool))) - default: - f.setCellIntValue(sheet, axis, value) - } -} - -// setCellIntValue provides a function to set int value of a cell. -func (f *File) setCellIntValue(sheet, axis string, value interface{}) { - switch value.(type) { + switch v := value.(type) { case int: - f.SetCellInt(sheet, axis, value.(int)) + f.SetCellInt(sheet, axis, v) case int8: - f.SetCellInt(sheet, axis, int(value.(int8))) + f.SetCellInt(sheet, axis, int(v)) case int16: - f.SetCellInt(sheet, axis, int(value.(int16))) + f.SetCellInt(sheet, axis, int(v)) case int32: - f.SetCellInt(sheet, axis, int(value.(int32))) + f.SetCellInt(sheet, axis, int(v)) case int64: - f.SetCellInt(sheet, axis, int(value.(int64))) + f.SetCellInt(sheet, axis, int(v)) case uint: - f.SetCellInt(sheet, axis, int(value.(uint))) + f.SetCellInt(sheet, axis, int(v)) case uint8: - f.SetCellInt(sheet, axis, int(value.(uint8))) + f.SetCellInt(sheet, axis, int(v)) case uint16: - f.SetCellInt(sheet, axis, int(value.(uint16))) + f.SetCellInt(sheet, axis, int(v)) case uint32: - f.SetCellInt(sheet, axis, int(value.(uint32))) + f.SetCellInt(sheet, axis, int(v)) case uint64: - f.SetCellInt(sheet, axis, int(value.(uint64))) + f.SetCellInt(sheet, axis, int(v)) + case float32: + f.SetCellDefault(sheet, axis, strconv.FormatFloat(float64(v), 'f', -1, 32)) + case float64: + f.SetCellDefault(sheet, axis, strconv.FormatFloat(v, 'f', -1, 64)) + case string: + f.SetCellStr(sheet, axis, v) + case []byte: + f.SetCellStr(sheet, axis, string(v)) + case time.Duration: + f.SetCellDefault(sheet, axis, strconv.FormatFloat(v.Seconds()/86400.0, 'f', -1, 32)) + f.setDefaultTimeStyle(sheet, axis, 21) + case time.Time: + vv := timeToExcelTime(v) + if vv > 0 { + f.SetCellDefault(sheet, axis, strconv.FormatFloat(timeToExcelTime(v), 'f', -1, 64)) + f.setDefaultTimeStyle(sheet, axis, 22) + } else { + f.SetCellStr(sheet, axis, v.Format(time.RFC3339Nano)) + } + case bool: + f.SetCellBool(sheet, axis, v) + case nil: + f.SetCellStr(sheet, axis, "") default: f.SetCellStr(sheet, axis, fmt.Sprintf("%v", value)) } } -// SetCellBool provides a function to set bool type value of a cell by given +// SetCellInt provides a function to set int type value of a cell by given // worksheet name, cell coordinates and cell value. -func (f *File) SetCellBool(sheet, axis string, value bool) { +func (f *File) SetCellInt(sheet, axis string, value int) { xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return - } - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) + cellData, col, _ := f.prepareCell(xlsx, sheet, axis) + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + cellData.T = "" + cellData.V = strconv.Itoa(value) +} - xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) - xlsx.SheetData.Row[xAxis].C[yAxis].T = "b" +// SetCellBool provides a function to set bool type value of a cell by given +// worksheet name, cell name and cell value. +func (f *File) SetCellBool(sheet, axis string, value bool) { + xlsx := f.workSheetReader(sheet) + cellData, col, _ := f.prepareCell(xlsx, sheet, axis) + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + cellData.T = "b" if value { - xlsx.SheetData.Row[xAxis].C[yAxis].V = "1" + cellData.V = "1" } else { - xlsx.SheetData.Row[xAxis].C[yAxis].V = "0" + cellData.V = "0" } } -// GetCellValue provides a function to get formatted value from cell by given -// worksheet name and axis in XLSX file. If it is possible to apply a format -// to the cell value, it will do so, if not then an error will be returned, -// along with the raw value of the cell. -func (f *File) GetCellValue(sheet, axis string) string { +// SetCellStr provides a function to set string type value of a cell. Total +// number of characters that a cell can contain 32767 characters. +func (f *File) SetCellStr(sheet, axis, value string) { xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return "" - } - xAxis := row - 1 - rows := len(xlsx.SheetData.Row) - if rows > 1 { - lastRow := xlsx.SheetData.Row[rows-1].R - if lastRow >= rows { - rows = lastRow - } - } - if rows < xAxis { - return "" - } - for k := range xlsx.SheetData.Row { - if xlsx.SheetData.Row[k].R == row { - for i := range xlsx.SheetData.Row[k].C { - if axis == xlsx.SheetData.Row[k].C[i].R { - val, _ := xlsx.SheetData.Row[k].C[i].getValueFrom(f, f.sharedStringsReader()) - return val - } - } + cellData, col, _ := f.prepareCell(xlsx, sheet, axis) + + // Leading space(s) character detection. + if len(value) > 0 && value[0] == 32 { + cellData.XMLSpace = xml.Attr{ + Name: xml.Name{Space: NameSpaceXML, Local: "space"}, + Value: "preserve", } } - return "" -} -// formattedValue provides a function to returns a value after formatted. If -// it is possible to apply a format to the cell value, it will do so, if not -// then an error will be returned, along with the raw value of the cell. -func (f *File) formattedValue(s int, v string) string { - if s == 0 { - return v - } - styleSheet := f.stylesReader() - ok := builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID] - if ok != nil { - return ok(styleSheet.CellXfs.Xf[s].NumFmtID, v) - } - return v + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + cellData.T = "str" + cellData.V = value } -// GetCellStyle provides a function to get cell style index by given worksheet -// name and cell coordinates. -func (f *File) GetCellStyle(sheet, axis string) int { +// SetCellDefault provides a function to set string type value of a cell as +// default format without escaping the cell. +func (f *File) SetCellDefault(sheet, axis, value string) { xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return 0 - } - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) - - return f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) + cellData, col, _ := f.prepareCell(xlsx, sheet, axis) + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + cellData.T = "" + cellData.V = value } // GetCellFormula provides a function to get formula from cell by given // worksheet name and axis in XLSX file. func (f *File) GetCellFormula(sheet, axis string) string { - xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return "" - } - xAxis := row - 1 - rows := len(xlsx.SheetData.Row) - if rows > 1 { - lastRow := xlsx.SheetData.Row[rows-1].R - if lastRow >= rows { - rows = lastRow - } - } - if rows < xAxis { - return "" - } - for k := range xlsx.SheetData.Row { - if xlsx.SheetData.Row[k].R == row { - for i := range xlsx.SheetData.Row[k].C { - if axis == xlsx.SheetData.Row[k].C[i].R { - if xlsx.SheetData.Row[k].C[i].F == nil { - continue - } - if xlsx.SheetData.Row[k].C[i].F.T == STCellFormulaTypeShared { - return getSharedForumula(xlsx, xlsx.SheetData.Row[k].C[i].F.Si) - } - return xlsx.SheetData.Row[k].C[i].F.Content - } - } + return f.getCellStringFunc(sheet, axis, func(x *xlsxWorksheet, c *xlsxC) (string, bool) { + if c.F == nil { + return "", false } - } - return "" -} - -// getSharedForumula find a cell contains the same formula as another cell, -// the "shared" value can be used for the t attribute and the si attribute can -// be used to refer to the cell containing the formula. Two formulas are -// considered to be the same when their respective representations in -// R1C1-reference notation, are the same. -// -// Note that this function not validate ref tag to check the cell if or not in -// allow area, and always return origin shared formula. -func getSharedForumula(xlsx *xlsxWorksheet, si string) string { - for k := range xlsx.SheetData.Row { - for i := range xlsx.SheetData.Row[k].C { - if xlsx.SheetData.Row[k].C[i].F == nil { - continue - } - if xlsx.SheetData.Row[k].C[i].F.T != STCellFormulaTypeShared { - continue - } - if xlsx.SheetData.Row[k].C[i].F.Si != si { - continue - } - if xlsx.SheetData.Row[k].C[i].F.Ref != "" { - return xlsx.SheetData.Row[k].C[i].F.Content - } + if c.F.T == STCellFormulaTypeShared { + return getSharedForumula(x, c.F.Si), true } - } - return "" + return c.F.Content, true + }) } // SetCellFormula provides a function to set cell formula by given string and // worksheet name. func (f *File) SetCellFormula(sheet, axis, formula string) { xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return - } - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) + cellData, _, _ := f.prepareCell(xlsx, sheet, axis) if formula == "" { - xlsx.SheetData.Row[xAxis].C[yAxis].F = nil + cellData.F = nil f.deleteCalcChain(axis) return } - if xlsx.SheetData.Row[xAxis].C[yAxis].F != nil { - xlsx.SheetData.Row[xAxis].C[yAxis].F.Content = formula + + if cellData.F != nil { + cellData.F.Content = formula } else { - f := xlsxF{ - Content: formula, + cellData.F = &xlsxF{Content: formula} + } +} + +// GetCellHyperLink provides a function to get cell hyperlink by given +// worksheet name and axis. Boolean type value link will be ture if the cell +// has a hyperlink and the target is the address of the hyperlink. Otherwise, +// the value of link will be false and the value of the target will be a blank +// string. For example get hyperlink of Sheet1!H6: +// +// link, target := xlsx.GetCellHyperLink("Sheet1", "H6") +// +func (f *File) GetCellHyperLink(sheet, axis string) (bool, string) { + // Check for correct cell name + if _, _, err := SplitCellName(axis); err != nil { + panic(err) // Fail fast to avoid possible future side effects + } + + xlsx := f.workSheetReader(sheet) + axis = f.mergeCellsParser(xlsx, axis) + + if xlsx.Hyperlinks != nil { + for _, link := range xlsx.Hyperlinks.Hyperlink { + if link.Ref == axis { + if link.RID != "" { + return true, f.getSheetRelationshipsTargetByID(sheet, link.RID) + } + return true, link.Location + } } - xlsx.SheetData.Row[xAxis].C[yAxis].F = &f } + return false, "" } // SetCellHyperLink provides a function to set cell hyperlink by given @@ -335,53 +249,36 @@ func (f *File) SetCellFormula(sheet, axis, formula string) { // xlsx.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location") // func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) { - xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - linkTypes := map[string]xlsxHyperlink{ - "External": {}, - "Location": {Location: link}, + // Check for correct cell name + if _, _, err := SplitCellName(axis); err != nil { + panic(err) // Fail fast to avoid possible future side effects } - hyperlink, ok := linkTypes[linkType] - if !ok || axis == "" { - return - } - hyperlink.Ref = axis - if linkType == "External" { - rID := f.addSheetRelationships(sheet, SourceRelationshipHyperLink, link, linkType) - hyperlink.RID = "rId" + strconv.Itoa(rID) - } - if xlsx.Hyperlinks == nil { - xlsx.Hyperlinks = &xlsxHyperlinks{} - } - xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, hyperlink) -} -// GetCellHyperLink provides a function to get cell hyperlink by given -// worksheet name and axis. Boolean type value link will be ture if the cell -// has a hyperlink and the target is the address of the hyperlink. Otherwise, -// the value of link will be false and the value of the target will be a blank -// string. For example get hyperlink of Sheet1!H6: -// -// link, target := xlsx.GetCellHyperLink("Sheet1", "H6") -// -func (f *File) GetCellHyperLink(sheet, axis string) (bool, string) { - var link bool - var target string xlsx := f.workSheetReader(sheet) axis = f.mergeCellsParser(xlsx, axis) - if xlsx.Hyperlinks == nil || axis == "" { - return link, target - } - for h := range xlsx.Hyperlinks.Hyperlink { - if xlsx.Hyperlinks.Hyperlink[h].Ref == axis { - link = true - target = xlsx.Hyperlinks.Hyperlink[h].Location - if xlsx.Hyperlinks.Hyperlink[h].RID != "" { - target = f.getSheetRelationshipsTargetByID(sheet, xlsx.Hyperlinks.Hyperlink[h].RID) - } + + var linkData xlsxHyperlink + + switch linkType { + case "External": + linkData = xlsxHyperlink{ + Ref: axis, } + rID := f.addSheetRelationships(sheet, SourceRelationshipHyperLink, link, linkType) + linkData.RID = "rId" + strconv.Itoa(rID) + case "Location": + linkData = xlsxHyperlink{ + Ref: axis, + Location: link, + } + default: + panic(fmt.Errorf("invalid link type %q", linkType)) + } + + if xlsx.Hyperlinks == nil { + xlsx.Hyperlinks = new(xlsxHyperlinks) } - return link, target + xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink, linkData) } // MergeCell provides a function to merge cells by given coordinate area and @@ -392,213 +289,214 @@ func (f *File) GetCellHyperLink(sheet, axis string) (bool, string) { // If you create a merged cell that overlaps with another existing merged cell, // those merged cells that already exist will be removed. func (f *File) MergeCell(sheet, hcell, vcell string) { - if hcell == vcell { - return + hcol, hrow, err := CellNameToCoordinates(hcell) + if err != nil { + panic(err) } - hcell = strings.ToUpper(hcell) - vcell = strings.ToUpper(vcell) - - // Coordinate conversion, convert C1:B3 to 2,0,1,2. - hcol := string(strings.Map(letterOnlyMapF, hcell)) - hrow, _ := strconv.Atoi(strings.Map(intOnlyMapF, hcell)) - hyAxis := hrow - 1 - hxAxis := TitleToNumber(hcol) + vcol, vrow, err := CellNameToCoordinates(vcell) + if err != nil { + panic(err) + } - vcol := string(strings.Map(letterOnlyMapF, vcell)) - vrow, _ := strconv.Atoi(strings.Map(intOnlyMapF, vcell)) - vyAxis := vrow - 1 - vxAxis := TitleToNumber(vcol) + if hcol == vcol && hrow == vrow { + return + } - if vxAxis < hxAxis { - hcell, vcell = vcell, hcell - vxAxis, hxAxis = hxAxis, vxAxis + if vcol < hcol { + hcol, vcol = vcol, hcol } - if vyAxis < hyAxis { - hcell, vcell = vcell, hcell - vyAxis, hyAxis = hyAxis, vyAxis + if vrow < hrow { + hrow, vrow = vrow, hrow } + hcell, _ = CoordinatesToCellName(hcol, hrow) + vcell, _ = CoordinatesToCellName(vcol, vrow) + xlsx := f.workSheetReader(sheet) if xlsx.MergeCells != nil { - mergeCell := xlsxMergeCell{} - // Correct the coordinate area, such correct C1:B3 to B1:C3. - mergeCell.Ref = ToAlphaString(hxAxis) + strconv.Itoa(hyAxis+1) + ":" + ToAlphaString(vxAxis) + strconv.Itoa(vyAxis+1) + ref := hcell + ":" + vcell + cells := make([]*xlsxMergeCell, 0, len(xlsx.MergeCells.Cells)) + // Delete the merged cells of the overlapping area. - for i := 0; i < len(xlsx.MergeCells.Cells); i++ { - if checkCellInArea(hcell, xlsx.MergeCells.Cells[i].Ref) || checkCellInArea(strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0], mergeCell.Ref) { - xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...) - } else if checkCellInArea(vcell, xlsx.MergeCells.Cells[i].Ref) || checkCellInArea(strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[1], mergeCell.Ref) { - xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...) + for _, cellData := range xlsx.MergeCells.Cells { + cc := strings.Split(cellData.Ref, ":") + if len(cc) != 2 { + panic(fmt.Errorf("invalid area %q", cellData.Ref)) + } + + if !checkCellInArea(hcell, cellData.Ref) && !checkCellInArea(vcell, cellData.Ref) && + !checkCellInArea(cc[0], ref) && !checkCellInArea(cc[1], ref) { + cells = append(cells, cellData) } } - xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &mergeCell) + cells = append(xlsx.MergeCells.Cells, &xlsxMergeCell{Ref: ref}) + xlsx.MergeCells.Cells = cells } else { - mergeCell := xlsxMergeCell{} - // Correct the coordinate area, such correct C1:B3 to B1:C3. - mergeCell.Ref = ToAlphaString(hxAxis) + strconv.Itoa(hyAxis+1) + ":" + ToAlphaString(vxAxis) + strconv.Itoa(vyAxis+1) - mergeCells := xlsxMergeCells{} - mergeCells.Cells = append(mergeCells.Cells, &mergeCell) - xlsx.MergeCells = &mergeCells + xlsx.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: hcell + ":" + vcell}}} } } -// SetCellInt provides a function to set int type value of a cell by given -// worksheet name, cell coordinates and cell value. -func (f *File) SetCellInt(sheet, axis string, value int) { - xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) +// SetSheetRow writes an array to row by given worksheet name, starting +// coordinate and a pointer to array type 'slice'. For example, writes an +// array to row 6 start with the cell B6 on Sheet1: +// +// xlsx.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2}) +// +func (f *File) SetSheetRow(sheet, axis string, slice interface{}) { + col, row, err := CellNameToCoordinates(axis) if err != nil { - return + panic(err) // Fail fast to avoid future side effects! } - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) - xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) - xlsx.SheetData.Row[xAxis].C[yAxis].T = "" - xlsx.SheetData.Row[xAxis].C[yAxis].V = strconv.Itoa(value) -} + // Make sure 'slice' is a Ptr to Slice + v := reflect.ValueOf(slice) + if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice { + panic(errors.New("pointer to slice expected")) // Fail fast to avoid future side effects! + } + v = v.Elem() -// prepareCellStyle provides a function to prepare style index of cell in -// worksheet by given column index and style index. -func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int { - if xlsx.Cols != nil && style == 0 { - for _, v := range xlsx.Cols.Col { - if v.Min <= col && col <= v.Max { - style = v.Style - } + for i := 0; i < v.Len(); i++ { + cell, err := CoordinatesToCellName(col+i, row) + // Error should never happens here. But keep ckecking to early detect regresions + // if it will be introduced in furure + if err != nil { + panic(err) // Fail fast to avoid future side effects! } + f.SetCellValue(sheet, cell, v.Index(i).Interface()) } - return style } -// SetCellStr provides a function to set string type value of a cell. Total -// number of characters that a cell can contain 32767 characters. -func (f *File) SetCellStr(sheet, axis, value string) { - xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - if len(value) > 32767 { - value = value[0:32767] - } - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) +// getCellInfo does common preparation for all SetCell* methods. +func (f *File) prepareCell(xlsx *xlsxWorksheet, sheet, cell string) (*xlsxC, int, int) { + cell = f.mergeCellsParser(xlsx, cell) + + col, row, err := CellNameToCoordinates(cell) if err != nil { - return + panic(err) // Fail fast and prevent future side effects } - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) + prepareSheetXML(xlsx, col, row) - // Leading space(s) character detection. - if len(value) > 0 { - if value[0] == 32 { - xlsx.SheetData.Row[xAxis].C[yAxis].XMLSpace = xml.Attr{ - Name: xml.Name{Space: NameSpaceXML, Local: "space"}, - Value: "preserve", - } - } - } - xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) - xlsx.SheetData.Row[xAxis].C[yAxis].T = "str" - xlsx.SheetData.Row[xAxis].C[yAxis].V = value + return &xlsx.SheetData.Row[row-1].C[col-1], col, row } -// SetCellDefault provides a function to set string type value of a cell as -// default format without escaping the cell. -func (f *File) SetCellDefault(sheet, axis, value string) { +// getCellStringFunc does common value extraction workflow for all GetCell* methods. +// Passed function implements specific part of required logic. +func (f *File) getCellStringFunc(sheet, axis string, fn func(x *xlsxWorksheet, c *xlsxC) (string, bool)) string { xlsx := f.workSheetReader(sheet) axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) + + _, row, err := CellNameToCoordinates(axis) if err != nil { - return + panic(err) // Fail fast to avoid future side effects! } - xAxis := row - 1 - yAxis := TitleToNumber(col) - rows := xAxis + 1 - cell := yAxis + 1 + lastRowNum := 0 + if l := len(xlsx.SheetData.Row); l > 0 { + lastRowNum = xlsx.SheetData.Row[l-1].R + } - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) + // keep in mind: row starts from 1 + if row > lastRowNum { + return "" + } - xlsx.SheetData.Row[xAxis].C[yAxis].S = f.prepareCellStyle(xlsx, cell, xlsx.SheetData.Row[xAxis].C[yAxis].S) - xlsx.SheetData.Row[xAxis].C[yAxis].T = "" - xlsx.SheetData.Row[xAxis].C[yAxis].V = value + for rowIdx := range xlsx.SheetData.Row { + rowData := &xlsx.SheetData.Row[rowIdx] + if rowData.R != row { + continue + } + for colIdx := range rowData.C { + colData := &rowData.C[colIdx] + if axis != colData.R { + continue + } + if val, ok := fn(xlsx, colData); ok { + return val + } + } + } + return "" } -// SetSheetRow writes an array to row by given worksheet name, starting -// coordinate and a pointer to array type 'slice'. For example, writes an -// array to row 6 start with the cell B6 on Sheet1: -// -// xlsx.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2}) -// -func (f *File) SetSheetRow(sheet, axis string, slice interface{}) { - xlsx := f.workSheetReader(sheet) - axis = f.mergeCellsParser(xlsx, axis) - col := string(strings.Map(letterOnlyMapF, axis)) - row, err := strconv.Atoi(strings.Map(intOnlyMapF, axis)) - if err != nil { - return - } - // Make sure 'slice' is a Ptr to Slice - v := reflect.ValueOf(slice) - if v.Kind() != reflect.Ptr { - return +// formattedValue provides a function to returns a value after formatted. If +// it is possible to apply a format to the cell value, it will do so, if not +// then an error will be returned, along with the raw value of the cell. +func (f *File) formattedValue(s int, v string) string { + if s == 0 { + return v } - v = v.Elem() - if v.Kind() != reflect.Slice { - return + styleSheet := f.stylesReader() + ok := builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID] + if ok != nil { + return ok(styleSheet.CellXfs.Xf[s].NumFmtID, v) } + return v +} - xAxis := row - 1 - yAxis := TitleToNumber(col) - - rows := xAxis + 1 - cell := yAxis + 1 - - completeRow(xlsx, rows, cell) - completeCol(xlsx, rows, cell) +// prepareCellStyle provides a function to prepare style index of cell in +// worksheet by given column index and style index. +func (f *File) prepareCellStyle(xlsx *xlsxWorksheet, col, style int) int { + if xlsx.Cols != nil && style == 0 { + for _, c := range xlsx.Cols.Col { + if c.Min <= col && col <= c.Max { + style = c.Style + } + } + } + return style +} - idx := 0 - for i := cell - 1; i < v.Len()+cell-1; i++ { - c := ToAlphaString(i) + strconv.Itoa(row) - f.SetCellValue(sheet, c, v.Index(idx).Interface()) - idx++ +// mergeCellsParser provides a function to check merged cells in worksheet by +// given axis. +func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) string { + axis = strings.ToUpper(axis) + if xlsx.MergeCells != nil { + for i := 0; i < len(xlsx.MergeCells.Cells); i++ { + if checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref) { + axis = strings.Split(xlsx.MergeCells.Cells[i].Ref, ":")[0] + } + } } + return axis } // checkCellInArea provides a function to determine if a given coordinate is // within an area. func checkCellInArea(cell, area string) bool { - cell = strings.ToUpper(cell) - area = strings.ToUpper(area) + col, row, err := CellNameToCoordinates(cell) + if err != nil { + panic(err) + } - ref := strings.Split(area, ":") - if len(ref) < 2 { + rng := strings.Split(area, ":") + if len(rng) != 2 { return false } - from := ref[0] - to := ref[1] + firstCol, firtsRow, _ := CellNameToCoordinates(rng[0]) + lastCol, lastRow, _ := CellNameToCoordinates(rng[1]) - col, row := getCellColRow(cell) - fromCol, fromRow := getCellColRow(from) - toCol, toRow := getCellColRow(to) + return col >= firstCol && col <= lastCol && row >= firtsRow && row <= lastRow +} - return axisLowerOrEqualThan(fromCol, col) && axisLowerOrEqualThan(col, toCol) && axisLowerOrEqualThan(fromRow, row) && axisLowerOrEqualThan(row, toRow) +// getSharedForumula find a cell contains the same formula as another cell, +// the "shared" value can be used for the t attribute and the si attribute can +// be used to refer to the cell containing the formula. Two formulas are +// considered to be the same when their respective representations in +// R1C1-reference notation, are the same. +// +// Note that this function not validate ref tag to check the cell if or not in +// allow area, and always return origin shared formula. +func getSharedForumula(xlsx *xlsxWorksheet, si string) string { + for _, r := range xlsx.SheetData.Row { + for _, c := range r.C { + if c.F != nil && c.F.Ref != "" && c.F.T == STCellFormulaTypeShared && c.F.Si == si { + return c.F.Content + } + } + } + return "" } |