diff options
Diffstat (limited to 'cell.go')
| -rw-r--r-- | cell.go | 377 |
1 files changed, 284 insertions, 93 deletions
@@ -1,11 +1,11 @@ -// 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 @@ -47,7 +47,8 @@ func (f *File) GetCellValue(sheet, axis string) (string, error) { }) } -// SetCellValue provides a function to set value of a cell. The following +// SetCellValue provides a function to set value of a cell. The specified +// coordinates should not be in the first row of the table. The following // shows the supported data types: // // int @@ -85,7 +86,8 @@ func (f *File) SetCellValue(sheet, axis string, value interface{}) error { case []byte: err = f.SetCellStr(sheet, axis, string(v)) case time.Duration: - err = f.SetCellDefault(sheet, axis, strconv.FormatFloat(v.Seconds()/86400.0, 'f', -1, 32)) + _, d := setCellDuration(v) + err = f.SetCellDefault(sheet, axis, d) if err != nil { return err } @@ -97,7 +99,7 @@ func (f *File) SetCellValue(sheet, axis string, value interface{}) error { case nil: err = f.SetCellStr(sheet, axis, "") default: - err = f.SetCellStr(sheet, axis, fmt.Sprintf("%v", value)) + err = f.SetCellStr(sheet, axis, fmt.Sprint(value)) } return err } @@ -133,28 +135,50 @@ func (f *File) setCellIntFunc(sheet, axis string, value interface{}) error { // setCellTimeFunc provides a method to process time type of value for // SetCellValue. func (f *File) setCellTimeFunc(sheet, axis string, value time.Time) error { - excelTime, err := timeToExcelTime(value) + xlsx, err := f.workSheetReader(sheet) if err != nil { return err } - if excelTime > 0 { - err = f.SetCellDefault(sheet, axis, strconv.FormatFloat(excelTime, 'f', -1, 64)) - if err != nil { - return err - } + cellData, col, _, err := f.prepareCell(xlsx, sheet, axis) + if err != nil { + return err + } + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + + var isNum bool + cellData.T, cellData.V, isNum, err = setCellTime(value) + if err != nil { + return err + } + if isNum { err = f.setDefaultTimeStyle(sheet, axis, 22) if err != nil { return err } - } else { - err = f.SetCellStr(sheet, axis, value.Format(time.RFC3339Nano)) - if err != nil { - return err - } } return err } +func setCellTime(value time.Time) (t string, b string, isNum bool, err error) { + var excelTime float64 + excelTime, err = timeToExcelTime(value) + if err != nil { + return + } + isNum = excelTime > 0 + if isNum { + t, b = setCellDefault(strconv.FormatFloat(excelTime, 'f', -1, 64)) + } else { + t, b = setCellDefault(value.Format(time.RFC3339Nano)) + } + return +} + +func setCellDuration(value time.Duration) (t string, v string) { + v = strconv.FormatFloat(value.Seconds()/86400.0, 'f', -1, 32) + return +} + // 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) error { @@ -169,11 +193,15 @@ func (f *File) SetCellInt(sheet, axis string, value int) error { return err } cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) - cellData.T = "" - cellData.V = strconv.Itoa(value) + cellData.T, cellData.V = setCellInt(value) return err } +func setCellInt(value int) (t string, v string) { + v = strconv.Itoa(value) + return +} + // 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) error { @@ -188,13 +216,18 @@ func (f *File) SetCellBool(sheet, axis string, value bool) error { return err } cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) - cellData.T = "b" + cellData.T, cellData.V = setCellBool(value) + return err +} + +func setCellBool(value bool) (t string, v string) { + t = "b" if value { - cellData.V = "1" + v = "1" } else { - cellData.V = "0" + v = "0" } - return err + return } // SetCellFloat sets a floating point value into a cell. The prec parameter @@ -218,11 +251,15 @@ func (f *File) SetCellFloat(sheet, axis string, value float64, prec, bitSize int return err } cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) - cellData.T = "" - cellData.V = strconv.FormatFloat(value, 'f', prec, bitSize) + cellData.T, cellData.V = setCellFloat(value, prec, bitSize) return err } +func setCellFloat(value float64, prec, bitSize int) (t string, v string) { + v = strconv.FormatFloat(value, 'f', prec, bitSize) + return +} + // 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) error { @@ -236,21 +273,25 @@ func (f *File) SetCellStr(sheet, axis, value string) error { if err != nil { return err } + cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) + cellData.T, cellData.V, cellData.XMLSpace = setCellStr(value) + return err +} + +func setCellStr(value string) (t string, v string, ns xml.Attr) { if len(value) > 32767 { value = value[0:32767] } - // Leading space(s) character detection. - if len(value) > 0 && value[0] == 32 { - cellData.XMLSpace = xml.Attr{ + // Leading and ending space(s) character detection. + if len(value) > 0 && (value[0] == 32 || value[len(value)-1] == 32) { + ns = xml.Attr{ Name: xml.Name{Space: NameSpaceXML, Local: "space"}, Value: "preserve", } } - - cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) - cellData.T = "str" - cellData.V = value - return err + t = "str" + v = value + return } // SetCellDefault provides a function to set string type value of a cell as @@ -265,11 +306,15 @@ func (f *File) SetCellDefault(sheet, axis, value string) error { return err } cellData.S = f.prepareCellStyle(xlsx, col, cellData.S) - cellData.T = "" - cellData.V = value + cellData.T, cellData.V = setCellDefault(value) return err } +func setCellDefault(value string) (t string, v string) { + v = value + return +} + // 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, error) { @@ -284,9 +329,15 @@ func (f *File) GetCellFormula(sheet, axis string) (string, error) { }) } +// FormulaOpts can be passed to SetCellFormula to use other formula types. +type FormulaOpts struct { + Type *string // Formula type + Ref *string // Shared formula ref +} + // SetCellFormula provides a function to set cell formula by given string and // worksheet name. -func (f *File) SetCellFormula(sheet, axis, formula string) error { +func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts) error { rwMutex.Lock() defer rwMutex.Unlock() xlsx, err := f.workSheetReader(sheet) @@ -299,7 +350,7 @@ func (f *File) SetCellFormula(sheet, axis, formula string) error { } if formula == "" { cellData.F = nil - f.deleteCalcChain(f.GetSheetIndex(sheet), axis) + f.deleteCalcChain(f.getSheetID(sheet), axis) return err } @@ -308,6 +359,17 @@ func (f *File) SetCellFormula(sheet, axis, formula string) error { } else { cellData.F = &xlsxF{Content: formula} } + + for _, o := range opts { + if o.Type != nil { + cellData.F.T = *o.Type + } + + if o.Ref != nil { + cellData.F.Ref = *o.Ref + } + } + return err } @@ -391,7 +453,9 @@ func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error { linkData = xlsxHyperlink{ Ref: axis, } - rID := f.addSheetRelationships(sheet, SourceRelationshipHyperLink, link, linkType) + sheetPath := f.sheetMap[trimSheetName(sheet)] + sheetRels := "xl/worksheets/_rels/" + strings.TrimPrefix(sheetPath, "xl/worksheets/") + ".rels" + rID := f.addRels(sheetRels, SourceRelationshipHyperLink, link, linkType) linkData.RID = "rId" + strconv.Itoa(rID) case "Location": linkData = xlsxHyperlink{ @@ -406,64 +470,170 @@ func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error { return nil } -// MergeCell provides a function to merge cells by given coordinate area and -// sheet name. For example create a merged cell of D3:E9 on Sheet1: +// SetCellRichText provides a function to set cell with rich text by given +// worksheet. For example, set rich text on the A1 cell of the worksheet named +// Sheet1: // -// err := f.MergeCell("Sheet1", "D3", "E9") +// package main // -// 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) error { - hcol, hrow, err := CellNameToCoordinates(hcell) - if err != nil { - return err - } - - vcol, vrow, err := CellNameToCoordinates(vcell) +// import ( +// "fmt" +// +// "github.com/360EntSecGroup-Skylar/excelize" +// ) +// +// func main() { +// f := excelize.NewFile() +// if err := f.SetRowHeight("Sheet1", 1, 35); err != nil { +// fmt.Println(err) +// return +// } +// if err := f.SetColWidth("Sheet1", "A", "A", 44); err != nil { +// fmt.Println(err) +// return +// } +// if err := f.SetCellRichText("Sheet1", "A1", []excelize.RichTextRun{ +// { +// Text: "blod", +// Font: &excelize.Font{ +// Bold: true, +// Color: "2354e8", +// Family: "Times New Roman", +// }, +// }, +// { +// Text: " and ", +// Font: &excelize.Font{ +// Family: "Times New Roman", +// }, +// }, +// { +// Text: " italic", +// Font: &excelize.Font{ +// Bold: true, +// Color: "e83723", +// Italic: true, +// Family: "Times New Roman", +// }, +// }, +// { +// Text: "text with color and font-family,", +// Font: &excelize.Font{ +// Bold: true, +// Color: "2354e8", +// Family: "Times New Roman", +// }, +// }, +// { +// Text: "\r\nlarge text with ", +// Font: &excelize.Font{ +// Size: 14, +// Color: "ad23e8", +// }, +// }, +// { +// Text: "strike", +// Font: &excelize.Font{ +// Color: "e89923", +// Strike: true, +// }, +// }, +// { +// Text: " and ", +// Font: &excelize.Font{ +// Size: 14, +// Color: "ad23e8", +// }, +// }, +// { +// Text: "underline.", +// Font: &excelize.Font{ +// Color: "23e833", +// Underline: "single", +// }, +// }, +// }); err != nil { +// fmt.Println(err) +// return +// } +// style, err := f.NewStyle(&excelize.Style{ +// Alignment: &excelize.Alignment{ +// WrapText: true, +// }, +// }) +// if err != nil { +// fmt.Println(err) +// return +// } +// if err := f.SetCellStyle("Sheet1", "A1", "A1", style); err != nil { +// fmt.Println(err) +// return +// } +// if err := f.SaveAs("Book1.xlsx"); err != nil { +// fmt.Println(err) +// } +// } +// +func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error { + ws, err := f.workSheetReader(sheet) if err != nil { return err } - - if hcol == vcol && hrow == vrow { - return err - } - - // Correct the coordinate area, such correct C1:B3 to B1:C3. - if vcol < hcol { - hcol, vcol = vcol, hcol - } - - if vrow < hrow { - hrow, vrow = vrow, hrow - } - - hcell, _ = CoordinatesToCellName(hcol, hrow) - vcell, _ = CoordinatesToCellName(vcol, vrow) - - xlsx, err := f.workSheetReader(sheet) + cellData, col, _, err := f.prepareCell(ws, sheet, cell) if err != nil { return err } - if xlsx.MergeCells != nil { - ref := hcell + ":" + vcell - // Delete the merged cells of the overlapping area. - for _, cellData := range xlsx.MergeCells.Cells { - cc := strings.Split(cellData.Ref, ":") - if len(cc) != 2 { - return fmt.Errorf("invalid area %q", cellData.Ref) + cellData.S = f.prepareCellStyle(ws, col, cellData.S) + si := xlsxSI{} + sst := f.sharedStringsReader() + textRuns := []xlsxR{} + for _, textRun := range runs { + run := xlsxR{T: &xlsxT{Val: textRun.Text}} + if strings.ContainsAny(textRun.Text, "\r\n ") { + run.T.Space = "preserve" + } + fnt := textRun.Font + if fnt != nil { + rpr := xlsxRPr{} + if fnt.Bold { + rpr.B = " " + } + if fnt.Italic { + rpr.I = " " } - c1, _ := checkCellInArea(hcell, cellData.Ref) - c2, _ := checkCellInArea(vcell, cellData.Ref) - c3, _ := checkCellInArea(cc[0], ref) - c4, _ := checkCellInArea(cc[1], ref) - if !(!c1 && !c2 && !c3 && !c4) { - return nil + if fnt.Strike { + rpr.Strike = " " } + if fnt.Underline != "" { + rpr.U = &attrValString{Val: &fnt.Underline} + } + if fnt.Family != "" { + rpr.RFont = &attrValString{Val: &fnt.Family} + } + if fnt.Size > 0.0 { + rpr.Sz = &attrValFloat{Val: &fnt.Size} + } + if fnt.Color != "" { + rpr.Color = &xlsxColor{RGB: getPaletteColor(fnt.Color)} + } + run.RPr = &rpr + } + textRuns = append(textRuns, run) + } + si.R = textRuns + sst.SI = append(sst.SI, si) + sst.Count++ + sst.UniqueCount++ + cellData.T, cellData.V = "s", strconv.Itoa(len(sst.SI)-1) + f.addContentTypePart(0, "sharedStrings") + rels := f.relsReader("xl/_rels/workbook.xml.rels") + for _, rel := range rels.Relationships { + if rel.Target == "sharedStrings.xml" { + return err } - xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells, &xlsxMergeCell{Ref: ref}) - } else { - xlsx.MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: hcell + ":" + vcell}}} } + // Update xl/_rels/workbook.xml.rels + f.addRels("xl/_rels/workbook.xml.rels", SourceRelationshipSharedStrings, "sharedStrings.xml", "") return err } @@ -488,12 +658,14 @@ func (f *File) SetSheetRow(sheet, axis string, slice interface{}) error { 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 + // Error should never happens here. But keep checking to early detect regresions + // if it will be introduced in future. if err != nil { return err } - f.SetCellValue(sheet, cell, v.Index(i).Interface()) + if err := f.SetCellValue(sheet, cell, v.Index(i).Interface()); err != nil { + return err + } } return err } @@ -571,9 +743,9 @@ func (f *File) formattedValue(s int, v string) string { return v } styleSheet := f.stylesReader() - ok := builtInNumFmtFunc[styleSheet.CellXfs.Xf[s].NumFmtID] + ok := builtInNumFmtFunc[*styleSheet.CellXfs.Xf[s].NumFmtID] if ok != nil { - return ok(styleSheet.CellXfs.Xf[s].NumFmtID, v) + return ok(*styleSheet.CellXfs.Xf[s].NumFmtID, v) } return v } @@ -597,7 +769,7 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error axis = strings.ToUpper(axis) if xlsx.MergeCells != nil { for i := 0; i < len(xlsx.MergeCells.Cells); i++ { - ok, err := checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref) + ok, err := f.checkCellInArea(axis, xlsx.MergeCells.Cells[i].Ref) if err != nil { return axis, err } @@ -611,7 +783,7 @@ func (f *File) mergeCellsParser(xlsx *xlsxWorksheet, axis string) (string, error // checkCellInArea provides a function to determine if a given coordinate is // within an area. -func checkCellInArea(cell, area string) (bool, error) { +func (f *File) checkCellInArea(cell, area string) (bool, error) { col, row, err := CellNameToCoordinates(cell) if err != nil { return false, err @@ -621,11 +793,30 @@ func checkCellInArea(cell, area string) (bool, error) { if len(rng) != 2 { return false, err } + coordinates, err := f.areaRefToCoordinates(area) + if err != nil { + return false, err + } - firstCol, firstRow, _ := CellNameToCoordinates(rng[0]) - lastCol, lastRow, _ := CellNameToCoordinates(rng[1]) + return cellInRef([]int{col, row}, coordinates), err +} + +// cellInRef provides a function to determine if a given range is within an +// range. +func cellInRef(cell, ref []int) bool { + return cell[0] >= ref[0] && cell[0] <= ref[2] && cell[1] >= ref[1] && cell[1] <= ref[3] +} - return col >= firstCol && col <= lastCol && row >= firstRow && row <= lastRow, err +// isOverlap find if the given two rectangles overlap or not. +func isOverlap(rect1, rect2 []int) bool { + return cellInRef([]int{rect1[0], rect1[1]}, rect2) || + cellInRef([]int{rect1[2], rect1[1]}, rect2) || + cellInRef([]int{rect1[0], rect1[3]}, rect2) || + cellInRef([]int{rect1[2], rect1[3]}, rect2) || + cellInRef([]int{rect2[0], rect2[1]}, rect1) || + cellInRef([]int{rect2[2], rect2[1]}, rect1) || + cellInRef([]int{rect2[0], rect2[3]}, rect1) || + cellInRef([]int{rect2[2], rect2[3]}, rect1) } // getSharedForumula find a cell contains the same formula as another cell, |