diff options
| -rw-r--r-- | cell.go | 166 | ||||
| -rw-r--r-- | cell_test.go | 81 | ||||
| -rw-r--r-- | comment.go | 8 | ||||
| -rw-r--r-- | file.go | 1 | ||||
| -rw-r--r-- | picture.go | 30 | ||||
| -rw-r--r-- | styles.go | 14 | ||||
| -rw-r--r-- | styles_test.go | 2 | ||||
| -rw-r--r-- | xmlDrawing.go | 2 | ||||
| -rw-r--r-- | xmlSharedStrings.go | 59 |
9 files changed, 331 insertions, 32 deletions
@@ -457,6 +457,172 @@ func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) error { return nil } +// SetCellRichText provides a function to set cell with rich text by given +// worksheet. For example: +// +// package main +// +// 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 + } + cellData, col, _, err := f.prepareCell(ws, sheet, cell) + if err != nil { + return err + } + 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 = " " + } + 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 + } + } + // Update xl/_rels/workbook.xml.rels + f.addRels("xl/_rels/workbook.xml.rels", SourceRelationshipSharedStrings, "sharedStrings.xml", "") + return err +} + // 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: diff --git a/cell_test.go b/cell_test.go index 1efbc5a..f46b4b9 100644 --- a/cell_test.go +++ b/cell_test.go @@ -141,3 +141,84 @@ func TestOverflowNumericCell(t *testing.T) { // GOARCH=amd64 - all ok; GOARCH=386 - actual: "-2147483648" assert.Equal(t, "8595602512225", val, "A1 should be 8595602512225") } + +func TestSetCellRichText(t *testing.T) { + f := NewFile() + assert.NoError(t, f.SetRowHeight("Sheet1", 1, 35)) + assert.NoError(t, f.SetColWidth("Sheet1", "A", "A", 44)) + richTextRun := []RichTextRun{ + { + Text: "blod", + Font: &Font{ + Bold: true, + Color: "2354e8", + Family: "Times New Roman", + }, + }, + { + Text: " and ", + Font: &Font{ + Family: "Times New Roman", + }, + }, + { + Text: "italic ", + Font: &Font{ + Bold: true, + Color: "e83723", + Italic: true, + Family: "Times New Roman", + }, + }, + { + Text: "text with color and font-family,", + Font: &Font{ + Bold: true, + Color: "2354e8", + Family: "Times New Roman", + }, + }, + { + Text: "\r\nlarge text with ", + Font: &Font{ + Size: 14, + Color: "ad23e8", + }, + }, + { + Text: "strike", + Font: &Font{ + Color: "e89923", + Strike: true, + }, + }, + { + Text: " and ", + Font: &Font{ + Size: 14, + Color: "ad23e8", + }, + }, + { + Text: "underline.", + Font: &Font{ + Color: "23e833", + Underline: "single", + }, + }, + } + assert.NoError(t, f.SetCellRichText("Sheet1", "A1", richTextRun)) + assert.NoError(t, f.SetCellRichText("Sheet1", "A2", richTextRun)) + style, err := f.NewStyle(&Style{ + Alignment: &Alignment{ + WrapText: true, + }, + }) + assert.NoError(t, err) + assert.NoError(t, f.SetCellStyle("Sheet1", "A1", "A1", style)) + assert.NoError(t, f.SaveAs(filepath.Join("test", "TestSetCellRichText.xlsx"))) + // Test set cell rich text on not exists worksheet + assert.EqualError(t, f.SetCellRichText("SheetN", "A1", richTextRun), "sheet SheetN is not exist") + // Test set cell rich text with illegal cell coordinates + assert.EqualError(t, f.SetCellRichText("Sheet1", "A", richTextRun), `cannot convert cell "A" to coordinates: invalid cell name "A"`) +} @@ -50,7 +50,9 @@ func (f *File) GetComments() (comments map[string][]Comment) { sheetComment.Text += *comment.Text.T } for _, text := range comment.Text.R { - sheetComment.Text += text.T + if text.T != nil { + sheetComment.Text += text.T.Val + } } sheetComments = append(sheetComments, sheetComment) } @@ -263,7 +265,7 @@ func (f *File) addComment(commentsXML, cell string, formatSet *formatComment) { RFont: &attrValString{Val: stringPtr(defaultFont)}, Family: &attrValInt{Val: intPtr(2)}, }, - T: a, + T: &xlsxT{Val: a}, }, { RPr: &xlsxRPr{ @@ -274,7 +276,7 @@ func (f *File) addComment(commentsXML, cell string, formatSet *formatComment) { RFont: &attrValString{Val: stringPtr(defaultFont)}, Family: &attrValInt{Val: intPtr(2)}, }, - T: t, + T: &xlsxT{Val: t}, }, }, }, @@ -97,6 +97,7 @@ func (f *File) WriteToBuffer() (*bytes.Buffer, error) { f.workBookWriter() f.workSheetWriter() f.relsWriter() + f.sharedStringsWriter() f.styleSheetWriter() for path, content := range f.XLSX { @@ -367,22 +367,24 @@ func (f *File) addContentTypePart(index int, contentType string) { "drawings": f.setContentTypePartImageExtensions, } partNames := map[string]string{ - "chart": "/xl/charts/chart" + strconv.Itoa(index) + ".xml", - "chartsheet": "/xl/chartsheets/sheet" + strconv.Itoa(index) + ".xml", - "comments": "/xl/comments" + strconv.Itoa(index) + ".xml", - "drawings": "/xl/drawings/drawing" + strconv.Itoa(index) + ".xml", - "table": "/xl/tables/table" + strconv.Itoa(index) + ".xml", - "pivotTable": "/xl/pivotTables/pivotTable" + strconv.Itoa(index) + ".xml", - "pivotCache": "/xl/pivotCache/pivotCacheDefinition" + strconv.Itoa(index) + ".xml", + "chart": "/xl/charts/chart" + strconv.Itoa(index) + ".xml", + "chartsheet": "/xl/chartsheets/sheet" + strconv.Itoa(index) + ".xml", + "comments": "/xl/comments" + strconv.Itoa(index) + ".xml", + "drawings": "/xl/drawings/drawing" + strconv.Itoa(index) + ".xml", + "table": "/xl/tables/table" + strconv.Itoa(index) + ".xml", + "pivotTable": "/xl/pivotTables/pivotTable" + strconv.Itoa(index) + ".xml", + "pivotCache": "/xl/pivotCache/pivotCacheDefinition" + strconv.Itoa(index) + ".xml", + "sharedStrings": "/xl/sharedStrings.xml", } contentTypes := map[string]string{ - "chart": ContentTypeDrawingML, - "chartsheet": ContentTypeSpreadSheetMLChartsheet, - "comments": ContentTypeSpreadSheetMLComments, - "drawings": ContentTypeDrawing, - "table": ContentTypeSpreadSheetMLTable, - "pivotTable": ContentTypeSpreadSheetMLPivotTable, - "pivotCache": ContentTypeSpreadSheetMLPivotCacheDefinition, + "chart": ContentTypeDrawingML, + "chartsheet": ContentTypeSpreadSheetMLChartsheet, + "comments": ContentTypeSpreadSheetMLComments, + "drawings": ContentTypeDrawing, + "table": ContentTypeSpreadSheetMLTable, + "pivotTable": ContentTypeSpreadSheetMLPivotTable, + "pivotCache": ContentTypeSpreadSheetMLPivotCacheDefinition, + "sharedStrings": ContentTypeSpreadSheetMLSharedStrings, } s, ok := setContentType[contentType] if ok { @@ -13,6 +13,7 @@ import ( "bytes" "encoding/json" "encoding/xml" + "errors" "fmt" "io" "log" @@ -1022,6 +1023,15 @@ func (f *File) styleSheetWriter() { } } +// sharedStringsWriter provides a function to save xl/sharedStrings.xml after +// serialize structure. +func (f *File) sharedStringsWriter() { + if f.SharedStrings != nil { + output, _ := xml.Marshal(f.SharedStrings) + f.saveFileList("xl/sharedStrings.xml", replaceRelationshipsNameSpaceBytes(output)) + } +} + // parseFormatStyleSet provides a function to parse the format settings of the // cells and conditional formats. func parseFormatStyleSet(style string) (*Style, error) { @@ -1033,7 +1043,7 @@ func parseFormatStyleSet(style string) (*Style, error) { } // NewStyle provides a function to create the style for cells by given JSON or -// structure. Note that the color field uses RGB color code. +// structure pointer. Note that the color field uses RGB color code. // // The following shows the border styles sorted by excelize index number: // @@ -1906,6 +1916,8 @@ func (f *File) NewStyle(style interface{}) (int, error) { } case *Style: fs = v + default: + return cellXfsID, errors.New("invalid parameter type") } s := f.stylesReader() numFmtID := setNumFmt(s, fs) diff --git a/styles_test.go b/styles_test.go index 5a9a771..5681c95 100644 --- a/styles_test.go +++ b/styles_test.go @@ -193,6 +193,8 @@ func TestNewStyle(t *testing.T) { assert.Equal(t, 2, styles.CellXfs.Count, "Should have 2 styles") _, err = f.NewStyle(&Style{}) assert.NoError(t, err) + _, err = f.NewStyle(Style{}) + assert.EqualError(t, err, "invalid parameter type") } func TestGetDefaultFont(t *testing.T) { diff --git a/xmlDrawing.go b/xmlDrawing.go index 142121d..191631b 100644 --- a/xmlDrawing.go +++ b/xmlDrawing.go @@ -25,6 +25,7 @@ const ( SourceRelationshipChartsheet = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet" SourceRelationshipPivotTable = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable" SourceRelationshipPivotCache = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition" + SourceRelationshipSharedStrings = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" SourceRelationshipVBAProject = "http://schemas.microsoft.com/office/2006/relationships/vbaProject" SourceRelationshipChart201506 = "http://schemas.microsoft.com/office/drawing/2015/06/chart" SourceRelationshipChart20070802 = "http://schemas.microsoft.com/office/drawing/2007/8/2/chart" @@ -55,6 +56,7 @@ const ( ContentTypeSpreadSheetMLComments = "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml" ContentTypeSpreadSheetMLPivotCacheDefinition = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml" ContentTypeSpreadSheetMLPivotTable = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml" + ContentTypeSpreadSheetMLSharedStrings = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" ContentTypeSpreadSheetMLTable = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml" ContentTypeSpreadSheetMLWorksheet = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" ContentTypeVBA = "application/vnd.ms-office.vbaProject" diff --git a/xmlSharedStrings.go b/xmlSharedStrings.go index 61e5727..a6525df 100644 --- a/xmlSharedStrings.go +++ b/xmlSharedStrings.go @@ -28,31 +28,46 @@ type xlsxSST struct { SI []xlsxSI `xml:"si"` } -// xlsxSI directly maps the si element from the namespace -// http://schemas.openxmlformats.org/spreadsheetml/2006/main - currently I have -// not checked this for completeness - it does as much as I need. +// xlsxSI (String Item) is the representation of an individual string in the +// Shared String table. If the string is just a simple string with formatting +// applied at the cell level, then the String Item (si) should contain a +// single text element used to express the string. However, if the string in +// the cell is more complex - i.e., has formatting applied at the character +// level - then the string item shall consist of multiple rich text runs which +// collectively are used to express the string. type xlsxSI struct { - T string `xml:"t"` + T string `xml:"t,omitempty"` R []xlsxR `xml:"r"` } +// String extracts characters from a string item. func (x xlsxSI) String() string { if len(x.R) > 0 { var rows strings.Builder for _, s := range x.R { - rows.WriteString(s.T) + if s.T != nil { + rows.WriteString(s.T.Val) + } } return rows.String() } return x.T } -// xlsxR directly maps the r element from the namespace -// http://schemas.openxmlformats.org/spreadsheetml/2006/main - currently I have -// not checked this for completeness - it does as much as I need. +// xlsxR represents a run of rich text. A rich text run is a region of text +// that share a common set of properties, such as formatting properties. The +// properties are defined in the rPr element, and the text displayed to the +// user is defined in the Text (t) element. type xlsxR struct { RPr *xlsxRPr `xml:"rPr"` - T string `xml:"t"` + T *xlsxT `xml:"t"` +} + +// xlsxT directly maps the t element in the run properties. +type xlsxT struct { + XMLName xml.Name `xml:"t"` + Space string `xml:"xml:space,attr,omitempty"` + Val string `xml:",innerxml"` } // xlsxRPr (Run Properties) specifies a set of run properties which shall be @@ -61,9 +76,25 @@ type xlsxR struct { // they are directly applied to the run and supersede any formatting from // styles. type xlsxRPr struct { - B string `xml:"b,omitempty"` - Sz *attrValFloat `xml:"sz"` - Color *xlsxColor `xml:"color"` - RFont *attrValString `xml:"rFont"` - Family *attrValInt `xml:"family"` + RFont *attrValString `xml:"rFont"` + Charset *attrValInt `xml:"charset"` + Family *attrValInt `xml:"family"` + B string `xml:"b,omitempty"` + I string `xml:"i,omitempty"` + Strike string `xml:"strike,omitempty"` + Outline string `xml:"outline,omitempty"` + Shadow string `xml:"shadow,omitempty"` + Condense string `xml:"condense,omitempty"` + Extend string `xml:"extend,omitempty"` + Color *xlsxColor `xml:"color"` + Sz *attrValFloat `xml:"sz"` + U *attrValString `xml:"u"` + VertAlign *attrValString `xml:"vertAlign"` + Scheme *attrValString `xml:"scheme"` +} + +// RichTextRun directly maps the settings of the rich text run. +type RichTextRun struct { + Font *Font + Text string } |