diff options
| -rw-r--r-- | comment.go | 209 | ||||
| -rw-r--r-- | excelize_test.go | 13 | ||||
| -rw-r--r-- | picture.go | 43 | ||||
| -rw-r--r-- | sheet.go | 7 | ||||
| -rw-r--r-- | vmlDrawing.go | 135 | ||||
| -rw-r--r-- | xmlComments.go | 55 | ||||
| -rw-r--r-- | xmlDrawing.go | 2 | ||||
| -rw-r--r-- | xmlSharedStrings.go | 16 | ||||
| -rw-r--r-- | xmlStyles.go | 4 |
9 files changed, 477 insertions, 7 deletions
diff --git a/comment.go b/comment.go new file mode 100644 index 0000000..4919ae1 --- /dev/null +++ b/comment.go @@ -0,0 +1,209 @@ +package excelize + +import ( + "encoding/json" + "encoding/xml" + "strconv" + "strings" +) + +// parseFormatCommentsSet provides function to parse the format settings of the +// comment with default value. +func parseFormatCommentsSet(formatSet string) *formatComment { + format := formatComment{ + Author: "Author:", + Text: " ", + } + json.Unmarshal([]byte(formatSet), &format) + return &format +} + +// AddComment provides the method to add comment in a sheet by given worksheet +// index, cell and format set (such as author and text). For example, add a +// comment in Sheet1!$A$30: +// +// xlsx.AddComment("Sheet1", "A30", `{"author":"Excelize","text":"This is a comment."}`) +// +func (f *File) AddComment(sheet, cell, format string) { + formatSet := parseFormatCommentsSet(format) + // Read sheet data. + xlsx := f.workSheetReader(sheet) + commentID := f.countComments() + 1 + drawingVML := "xl/drawings/vmlDrawing" + strconv.Itoa(commentID) + ".vml" + sheetRelationshipsComments := "../comments" + strconv.Itoa(commentID) + ".xml" + sheetRelationshipsDrawingVML := "../drawings/vmlDrawing" + strconv.Itoa(commentID) + ".vml" + if xlsx.LegacyDrawing != nil { + // The worksheet already has a comments relationships, use the relationships drawing ../drawings/vmlDrawing%d.vml. + sheetRelationshipsDrawingVML = f.getSheetRelationshipsTargetByID(sheet, xlsx.LegacyDrawing.RID) + commentID, _ = strconv.Atoi(strings.TrimSuffix(strings.TrimPrefix(sheetRelationshipsDrawingVML, "../drawings/vmlDrawing"), ".vml")) + drawingVML = strings.Replace(sheetRelationshipsDrawingVML, "..", "xl", -1) + } else { + // Add first comment for given sheet. + rID := f.addSheetRelationships(sheet, SourceRelationshipDrawingVML, sheetRelationshipsDrawingVML, "") + f.addSheetRelationships(sheet, SourceRelationshipComments, sheetRelationshipsComments, "") + f.addSheetLegacyDrawing(sheet, rID) + } + commentsXML := "xl/comments" + strconv.Itoa(commentID) + ".xml" + f.addComment(commentsXML, cell, formatSet) + f.addDrawingVML(commentID, drawingVML, cell) + f.addCommentsContentTypePart(commentID) +} + +// addDrawingVML provides function to create comment as +// xl/drawings/vmlDrawing%d.vml by given commit ID and cell. +func (f *File) addDrawingVML(commentID int, drawingVML, cell string) { + col := string(strings.Map(letterOnlyMapF, cell)) + row, _ := strconv.Atoi(strings.Map(intOnlyMapF, cell)) + xAxis := row - 1 + yAxis := titleToNumber(col) + vml := vmlDrawing{ + XMLNSv: "urn:schemas-microsoft-com:vml", + XMLNSo: "urn:schemas-microsoft-com:office:office", + XMLNSx: "urn:schemas-microsoft-com:office:excel", + XMLNSmv: "http://macVmlSchemaUri", + Shapelayout: &xlsxShapelayout{ + Ext: "edit", + IDmap: &xlsxIDmap{ + Ext: "edit", + Data: commentID, + }, + }, + Shapetype: &xlsxShapetype{ + ID: "_x0000_t202", + Coordsize: "21600,21600", + Spt: 202, + Path: "m0,0l0,21600,21600,21600,21600,0xe", + Stroke: &xlsxStroke{ + Joinstyle: "miter", + }, + VPath: &vPath{ + Gradientshapeok: "t", + Connecttype: "rect", + }, + }, + } + sp := encodeShape{ + Fill: &vFill{ + Color2: "#fbfe82", + Angle: -180, + Type: "gradient", + Fill: &oFill{ + Ext: "view", + Type: "gradientUnscaled", + }, + }, + Shadow: &vShadow{ + On: "t", + Color: "black", + Obscured: "t", + }, + Path: &vPath{ + Connecttype: "none", + }, + Textbox: &vTextbox{ + Style: "mso-direction-alt:auto", + Div: &xlsxDiv{ + Style: "text-align:left", + }, + }, + ClientData: &xClientData{ + ObjectType: "Note", + Anchor: "3, 15, 8, 6, 4, 54, 13, 2", + AutoFill: "False", + Row: xAxis, + Column: yAxis, + }, + } + s, _ := xml.Marshal(sp) + shape := xlsxShape{ + ID: "_x0000_s1025", + Type: "#_x0000_t202", + Style: "position:absolute;73.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden", + Fillcolor: "#fbf6d6", + Strokecolor: "#edeaa1", + Val: string(s[13 : len(s)-14]), + } + c, ok := f.XLSX[drawingVML] + if ok { + d := decodeVmlDrawing{} + xml.Unmarshal([]byte(c), &d) + for _, v := range d.Shape { + s := xlsxShape{ + ID: "_x0000_s1025", + Type: "#_x0000_t202", + Style: "position:absolute;73.5pt;width:108pt;height:59.25pt;z-index:1;visibility:hidden", + Fillcolor: "#fbf6d6", + Strokecolor: "#edeaa1", + Val: v.Val, + } + vml.Shape = append(vml.Shape, s) + } + } + vml.Shape = append(vml.Shape, shape) + v, _ := xml.Marshal(vml) + f.XLSX[drawingVML] = string(v) +} + +// addComment provides function to create chart as xl/comments%d.xml by given +// cell and format sets. +func (f *File) addComment(commentsXML, cell string, formatSet *formatComment) { + comments := xlsxComments{ + Authors: []xlsxAuthor{ + xlsxAuthor{ + Author: formatSet.Author, + }, + }, + } + cmt := xlsxComment{ + Ref: cell, + AuthorID: 0, + Text: xlsxText{ + R: []xlsxR{ + xlsxR{ + RPr: &xlsxRPr{ + B: " ", + Sz: &attrValInt{Val: 9}, + Color: &xlsxColor{ + Indexed: 81, + }, + RFont: &attrValString{Val: "Calibri"}, + Family: &attrValInt{Val: 2}, + }, + T: formatSet.Author + ": ", + }, + xlsxR{ + RPr: &xlsxRPr{ + Sz: &attrValInt{Val: 9}, + Color: &xlsxColor{ + Indexed: 81, + }, + RFont: &attrValString{Val: "Calibri"}, + Family: &attrValInt{Val: 2}, + }, + T: formatSet.Text, + }, + }, + }, + } + c, ok := f.XLSX[commentsXML] + if ok { + d := xlsxComments{} + xml.Unmarshal([]byte(c), &d) + comments.CommentList.Comment = append(comments.CommentList.Comment, d.CommentList.Comment...) + } + comments.CommentList.Comment = append(comments.CommentList.Comment, cmt) + v, _ := xml.Marshal(comments) + f.saveFileList(commentsXML, string(v)) +} + +// countComments provides function to get comments files count storage in the +// folder xl. +func (f *File) countComments() int { + count := 0 + for k := range f.XLSX { + if strings.Contains(k, "xl/comments") { + count++ + } + } + return count +} diff --git a/excelize_test.go b/excelize_test.go index d16937b..38c299d 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -524,6 +524,19 @@ func TestAddShape(t *testing.T) { } } +func TestAddComments(t *testing.T) { + xlsx, err := OpenFile("./test/Workbook_2.xlsx") + if err != nil { + t.Log(err) + } + xlsx.AddComment("Sheet1", "A30", `{"author":"Excelize","text":"This is first comment."}`) + xlsx.AddComment("Sheet2", "B7", `{"author":"Excelize","text":"This is second comment."}`) + err = xlsx.Save() + if err != nil { + t.Log(err) + } +} + func TestAddChart(t *testing.T) { xlsx, err := OpenFile("./test/Workbook1.xlsx") if err != nil { @@ -139,6 +139,15 @@ func (f *File) addSheetRelationships(sheet, relType, target, targetMode string) return rID } +// addSheetLegacyDrawing provides function to add legacy drawing element to +// xl/worksheets/sheet%d.xml by given sheet name and relationship index. +func (f *File) addSheetLegacyDrawing(sheet string, rID int) { + xlsx := f.workSheetReader(sheet) + xlsx.LegacyDrawing = &xlsxLegacyDrawing{ + RID: "rId" + strconv.Itoa(rID), + } +} + // addSheetDrawing provides function to add drawing element to // xl/worksheets/sheet%d.xml by given sheet name and relationship index. func (f *File) addSheetDrawing(sheet string, rID int) { @@ -308,6 +317,24 @@ func (f *File) setContentTypePartImageExtensions() { } } +// setContentTypePartVMLExtensions provides function to set the content type +// for relationship parts and the Main Document part. +func (f *File) setContentTypePartVMLExtensions() { + vml := false + content := f.contentTypesReader() + for _, v := range content.Defaults { + if v.Extension == "vml" { + vml = true + } + } + if !vml { + content.Defaults = append(content.Defaults, xlsxDefault{ + Extension: "vml", + ContentType: "application/vnd.openxmlformats-officedocument.vmlDrawing", + }) + } +} + // addDrawingContentTypePart provides function to add image part relationships // in the file [Content_Types].xml by given drawing index. func (f *File) addDrawingContentTypePart(index int) { @@ -324,6 +351,22 @@ func (f *File) addDrawingContentTypePart(index int) { }) } +// addCommentsContentTypePart provides function to add comments part +// relationships in the file [Content_Types].xml by given comment index. +func (f *File) addCommentsContentTypePart(index int) { + f.setContentTypePartVMLExtensions() + content := f.contentTypesReader() + for _, v := range content.Overrides { + if v.PartName == "/xl/comments"+strconv.Itoa(index)+".xml" { + return + } + } + content.Overrides = append(content.Overrides, xlsxOverride{ + PartName: "/xl/comments" + strconv.Itoa(index) + ".xml", + ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml", + }) +} + // getSheetRelationshipsTargetByID provides function to get Target attribute // value in xl/worksheets/_rels/sheet%d.xml.rels by given sheet name and // relationship index. @@ -418,8 +418,7 @@ func (f *File) CopySheet(from, to int) error { // target worksheet index. func (f *File) copySheet(from, to int) { sheet := f.workSheetReader("sheet" + strconv.Itoa(from)) - var worksheet xlsxWorksheet - worksheet = *sheet + worksheet := *sheet path := "xl/worksheets/sheet" + strconv.Itoa(to) + ".xml" if len(worksheet.SheetViews.SheetView) > 0 { worksheet.SheetViews.SheetView[0].TabSelected = false @@ -444,7 +443,7 @@ func (f *File) HideSheet(name string) { content := f.workbookReader() count := 0 for _, v := range content.Sheets.Sheet { - if v.State != "hidden" { + if v.State != sheetStateHidden { count++ } } @@ -456,7 +455,7 @@ func (f *File) HideSheet(name string) { tabSelected = xlsx.SheetViews.SheetView[0].TabSelected } if v.Name == name && count > 1 && !tabSelected { - content.Sheets.Sheet[k].State = "hidden" + content.Sheets.Sheet[k].State = sheetStateHidden } } } diff --git a/vmlDrawing.go b/vmlDrawing.go new file mode 100644 index 0000000..307186a --- /dev/null +++ b/vmlDrawing.go @@ -0,0 +1,135 @@ +package excelize + +import "encoding/xml" + +// vmlDrawing directly maps the root element in the file +// xl/drawings/vmlDrawing%d.vml. +type vmlDrawing struct { + XMLName xml.Name `xml:"xml"` + XMLNSv string `xml:"xmlns:v,attr"` + XMLNSo string `xml:"xmlns:o,attr"` + XMLNSx string `xml:"xmlns:x,attr"` + XMLNSmv string `xml:"xmlns:mv,attr"` + Shapelayout *xlsxShapelayout `xml:"o:shapelayout"` + Shapetype *xlsxShapetype `xml:"v:shapetype"` + Shape []xlsxShape `xml:"v:shape"` +} + +// xlsxShapelayout directly maps the shapelayout element. This element contains +// child elements that store information used in the editing and layout of +// shapes. +type xlsxShapelayout struct { + Ext string `xml:"v:ext,attr"` + IDmap *xlsxIDmap `xml:"o:idmap"` +} + +// xlsxIDmap directly maps the idmap element. +type xlsxIDmap struct { + Ext string `xml:"v:ext,attr"` + Data int `xml:"data,attr"` +} + +// xlsxShape directly maps the shape element. +type xlsxShape struct { + XMLName xml.Name `xml:"v:shape"` + ID string `xml:"id,attr"` + Type string `xml:"type,attr"` + Style string `xml:"style,attr"` + Fillcolor string `xml:"fillcolor,attr"` + Insetmode string `xml:"urn:schemas-microsoft-com:office:office insetmode,attr,omitempty"` + Strokecolor string `xml:"strokecolor,attr,omitempty"` + Val string `xml:",innerxml"` +} + +// xlsxShapetype directly maps the shapetype element. +type xlsxShapetype struct { + ID string `xml:"id,attr"` + Coordsize string `xml:"coordsize,attr"` + Spt int `xml:"o:spt,attr"` + Path string `xml:"path,attr"` + Stroke *xlsxStroke `xml:"v:stroke"` + VPath *vPath `xml:"v:path"` +} + +// xlsxStroke directly maps the stroke element. +type xlsxStroke struct { + Joinstyle string `xml:"joinstyle,attr"` +} + +// vPath directly maps the v:path element. +type vPath struct { + Gradientshapeok string `xml:"gradientshapeok,attr,omitempty"` + Connecttype string `xml:"o:connecttype,attr"` +} + +// vFill directly maps the v:fill element. This element must be defined within a +// Shape element. +type vFill struct { + Angle int `xml:"angle,attr,omitempty"` + Color2 string `xml:"color2,attr"` + Type string `xml:"type,attr,omitempty"` + Fill *oFill `xml:"o:fill"` +} + +// oFill directly maps the o:fill element. +type oFill struct { + Ext string `xml:"v:ext,attr"` + Type string `xml:"type,attr,omitempty"` +} + +// vShadow directly maps the v:shadow element. This element must be defined +// within a Shape element. In addition, the On attribute must be set to True. +type vShadow struct { + On string `xml:"on,attr"` + Color string `xml:"color,attr,omitempty"` + Obscured string `xml:"obscured,attr"` +} + +// vTextbox directly maps the v:textbox element. This element must be defined +// within a Shape element. +type vTextbox struct { + Style string `xml:"style,attr"` + Div *xlsxDiv `xml:"div"` +} + +// xlsxDiv directly maps the div element. +type xlsxDiv struct { + Style string `xml:"style,attr"` +} + +// xClientData (Attached Object Data) directly maps the x:ClientData element. +// This element specifies data associated with objects attached to a +// spreadsheet. While this element might contain any of the child elements +// below, only certain combinations are meaningful. The ObjectType attribute +// determines the kind of object the element represents and which subset of +// child elements is appropriate. Relevant groups are identified for each child +// element. +type xClientData struct { + ObjectType string `xml:"ObjectType,attr"` + MoveWithCells string `xml:"x:MoveWithCells,omitempty"` + SizeWithCells string `xml:"x:SizeWithCells,omitempty"` + Anchor string `xml:"x:Anchor"` + AutoFill string `xml:"x:AutoFill"` + Row int `xml:"x:Row"` + Column int `xml:"x:Column"` +} + +// decodeVmlDrawing defines the structure used to parse the file +// xl/drawings/vmlDrawing%d.vml. +type decodeVmlDrawing struct { + Shape []decodeShape `xml:"urn:schemas-microsoft-com:vml shape"` +} + +// decodeShape defines the structure used to parse the particular shape element. +type decodeShape struct { + Val string `xml:",innerxml"` +} + +// encodeShape defines the structure used to re-serialization shape element. +type encodeShape struct { + Fill *vFill `xml:"v:fill"` + Shadow *vShadow `xml:"v:shadow"` + Path *vPath `xml:"v:path"` + Textbox *vTextbox `xml:"v:textbox"` + ClientData *xClientData `xml:"x:ClientData"` +} diff --git a/xmlComments.go b/xmlComments.go new file mode 100644 index 0000000..fadc9b3 --- /dev/null +++ b/xmlComments.go @@ -0,0 +1,55 @@ +package excelize + +import "encoding/xml" + +// xlsxComments directly maps the comments element from the namespace +// http://schemas.openxmlformats.org/spreadsheetml/2006/main. A comment is a +// rich text note that is attached to and associated with a cell, separate from +// other cell content. Comment content is stored separate from the cell, and is +// displayed in a drawing object (like a text box) that is separate from, but +// associated with, a cell. Comments are used as reminders, such as noting how a +// complex formula works, or to provide feedback to other users. Comments can +// also be used to explain assumptions made in a formula or to call out +// something special about the cell. +type xlsxComments struct { + XMLName xml.Name `xml:"http://schemas.openxmlformats.org/spreadsheetml/2006/main comments"` + Authors []xlsxAuthor `xml:"authors"` + CommentList xlsxCommentList `xml:"commentList"` +} + +// xlsxAuthor directly maps the author element. This element holds a string +// representing the name of a single author of comments. Every comment shall +// have an author. The maximum length of the author string is an implementation +// detail, but a good guideline is 255 chars. +type xlsxAuthor struct { + Author string `xml:"author"` +} + +// xlsxCommentList (List of Comments) directly maps the xlsxCommentList element. +// This element is a container that holds a list of comments for the sheet. +type xlsxCommentList struct { + Comment []xlsxComment `xml:"comment"` +} + +// xlsxComment directly maps the comment element. This element represents a +// single user entered comment. Each comment shall have an author and can +// optionally contain richly formatted text. +type xlsxComment struct { + Ref string `xml:"ref,attr"` + AuthorID int `xml:"authorId,attr"` + Text xlsxText `xml:"text"` +} + +// xlsxText directly maps the text element. This element contains rich text +// which represents the text of a comment. The maximum length for this text is a +// spreadsheet application implementation detail. A recommended guideline is +// 32767 chars. +type xlsxText struct { + R []xlsxR `xml:"r"` +} + +// formatComment directly maps the format settings of the comment. +type formatComment struct { + Author string `json:"author"` + Text string `json:"text"` +} diff --git a/xmlDrawing.go b/xmlDrawing.go index 0ec2bc4..71b9cf9 100644 --- a/xmlDrawing.go +++ b/xmlDrawing.go @@ -6,9 +6,11 @@ import "encoding/xml" const ( SourceRelationship = "http://schemas.openxmlformats.org/officeDocument/2006/relationships" SourceRelationshipChart = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart" + SourceRelationshipComments = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" SourceRelationshipImage = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" SourceRelationshipTable = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" SourceRelationshipDrawingML = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" + SourceRelationshipDrawingVML = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" SourceRelationshipHyperLink = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" SourceRelationshipWorkSheet = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" SourceRelationshipChart201506 = "http://schemas.microsoft.com/office/drawing/2015/06/chart" diff --git a/xmlSharedStrings.go b/xmlSharedStrings.go index 3b9d78a..878c08d 100644 --- a/xmlSharedStrings.go +++ b/xmlSharedStrings.go @@ -28,5 +28,19 @@ type xlsxSI struct { // http://schemas.openxmlformats.org/spreadsheetml/2006/main - currently I have // not checked this for completeness - it does as much as I need. type xlsxR struct { - T string `xml:"t"` + RPr *xlsxRPr `xml:"rPr"` + T string `xml:"t"` +} + +// xlsxRPr (Run Properties) specifies a set of run properties which shall be +// applied to the contents of the parent run after all style formatting has been +// applied to the text. These properties are defined as direct formatting, since +// they are directly applied to the run and supersede any formatting from +// styles. +type xlsxRPr struct { + B string `xml:"b,omitempty"` + Sz *attrValInt `xml:"sz"` + Color *xlsxColor `xml:"color"` + RFont *attrValString `xml:"rFont"` + Family *attrValInt `xml:"family"` } diff --git a/xmlStyles.go b/xmlStyles.go index 682c5d3..aa0c9c8 100644 --- a/xmlStyles.go +++ b/xmlStyles.go @@ -52,8 +52,8 @@ type xlsxLine struct { type xlsxColor struct { Auto bool `xml:"auto,attr,omitempty"` RGB string `xml:"rgb,attr,omitempty"` - Indexed *int `xml:"indexed,attr,omitempty"` - Theme *int `xml:"theme,attr,omitempty"` + Indexed int `xml:"indexed,attr,omitempty"` + Theme int `xml:"theme,attr,omitempty"` Tint float64 `xml:"tint,attr,omitempty"` } |