From bd5b033b02f3fed70b90ec64b13c3291ba2186ff Mon Sep 17 00:00:00 2001 From: Ri Xu Date: Tue, 24 Jan 2017 18:29:02 +0800 Subject: Support set work sheet background image. --- col.go | 2 +- excelize_test.go | 27 +++++++++++++++++++++++++++ file.go | 2 +- lib.go | 10 ++++++---- picture.go | 38 +++++++++++++++++++++++++++++++------- sheet.go | 32 ++++++++++++++++++++++++++++++-- test/images/background.jpg | Bin 0 -> 2376 bytes 7 files changed, 96 insertions(+), 15 deletions(-) create mode 100644 test/images/background.jpg diff --git a/col.go b/col.go index 60adab4..4d8f7a3 100644 --- a/col.go +++ b/col.go @@ -68,7 +68,7 @@ func (f *File) SetColWidth(sheet, startcol, endcol string, width float64) { // | | | (x2,y2)| // +-----+------------+------------+ // -// Example of an object that covers some of the area from cell A1 to B2. +// Example of an object that covers some of the area from cell A1 to B2. // // Based on the width and height of the object we need to calculate 8 vars: // diff --git a/excelize_test.go b/excelize_test.go index 8da4a99..0ac4b9d 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -208,3 +208,30 @@ func TestSetCellFormula(t *testing.T) { t.Log(err) } } + +func TestSetSheetBackground(t *testing.T) { + xlsx, err := OpenFile("./test/Workbook1.xlsx") + if err != nil { + t.Log(err) + } + err = xlsx.SetSheetBackground("sheet2", "./test/images/background.png") + if err != nil { + t.Log(err) + } + err = xlsx.SetSheetBackground("sheet2", "./test/Workbook1.xlsx") + if err != nil { + t.Log(err) + } + err = xlsx.SetSheetBackground("sheet2", "./test/images/background.jpg") + if err != nil { + t.Log(err) + } + err = xlsx.SetSheetBackground("sheet2", "./test/images/background.jpg") + if err != nil { + t.Log(err) + } + err = xlsx.Save() + if err != nil { + t.Log(err) + } +} diff --git a/file.go b/file.go index e43d05f..2a044f0 100644 --- a/file.go +++ b/file.go @@ -27,7 +27,7 @@ func CreateFile() *File { } } -// Save provides function override the xlsx file with origin path. +// Save provides function to override the xlsx file with origin path. func (f *File) Save() error { buf := new(bytes.Buffer) w := zip.NewWriter(buf) diff --git a/lib.go b/lib.go index f57f160..77c6e23 100644 --- a/lib.go +++ b/lib.go @@ -38,7 +38,7 @@ func ReadZipReader(r *zip.Reader) (map[string]string, int, error) { return fileList, worksheets, nil } -// Read XML content as string. +// readXML provides function to read XML content as string. func (f *File) readXML(name string) string { if content, ok := f.XLSX[name]; ok { return content @@ -46,7 +46,8 @@ func (f *File) readXML(name string) string { return "" } -// Update given file content in file list of XLSX. +// saveFileList provides function to update given file content in file list of +// XLSX. func (f *File) saveFileList(name string, content string) { f.XLSX[name] = XMLHeader + content } @@ -63,7 +64,8 @@ func readFile(file *zip.File) string { return string(buff.Bytes()) } -// Convert integer to Excel sheet column title. +// toAlphaString provides function to convert integer to Excel sheet column +// title. func toAlphaString(value int) string { if value < 0 { return "" @@ -77,7 +79,7 @@ func toAlphaString(value int) string { return ans } -// Convert Excel sheet column title to int. +// titleToNumber provides function to convert Excel sheet column title to int. func titleToNumber(s string) int { weight := 0.0 sum := 0 diff --git a/picture.go b/picture.go index 9a91938..712dddf 100644 --- a/picture.go +++ b/picture.go @@ -135,7 +135,23 @@ func (f *File) addSheetDrawing(sheet string, rID int) { if err != nil { fmt.Println(err) } - f.saveFileList(name, string(output)) + f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpace(string(output))) +} + +// addSheetPicture provides function to add picture element to +// xl/worksheets/sheet%d.xml by given sheet name and relationship index. +func (f *File) addSheetPicture(sheet string, rID int) { + var xlsx xlsxWorksheet + name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml" + xml.Unmarshal([]byte(f.readXML(name)), &xlsx) + xlsx.Picture = &xlsxPicture{ + RID: "rId" + strconv.Itoa(rID), + } + output, err := xml.Marshal(xlsx) + if err != nil { + fmt.Println(err) + } + f.saveFileList(name, replaceWorkSheetsRelationshipsNameSpace(string(output))) } // countDrawings provides function to get drawing files count storage in the @@ -270,13 +286,10 @@ func (f *File) addMedia(file string, ext string) { f.XLSX[media] = string(dat) } -// addDrawingContentTypePart provides function to add image part relationships -// in http://purl.oclc.org/ooxml/officeDocument/relationships/image and -// appropriate content type. -func (f *File) addDrawingContentTypePart(index int) { +func (f *File) setContentTypePartImageExtensions() { var imageTypes = map[string]bool{"jpeg": false, "png": false, "gif": false} var content xlsxTypes - xml.Unmarshal([]byte(f.readXML(`[Content_Types].xml`)), &content) + xml.Unmarshal([]byte(f.readXML("[Content_Types].xml")), &content) for _, v := range content.Defaults { _, ok := imageTypes[v.Extension] if ok { @@ -291,6 +304,17 @@ func (f *File) addDrawingContentTypePart(index int) { }) } } + output, _ := xml.Marshal(content) + f.saveFileList("[Content_Types].xml", string(output)) +} + +// addDrawingContentTypePart provides function to add image part relationships +// in http://purl.oclc.org/ooxml/officeDocument/relationships/image and +// appropriate content type. +func (f *File) addDrawingContentTypePart(index int) { + f.setContentTypePartImageExtensions() + var content xlsxTypes + xml.Unmarshal([]byte(f.readXML("[Content_Types].xml")), &content) for _, v := range content.Overrides { if v.PartName == "/xl/drawings/drawing"+strconv.Itoa(index)+".xml" { output, _ := xml.Marshal(content) @@ -303,7 +327,7 @@ func (f *File) addDrawingContentTypePart(index int) { ContentType: "application/vnd.openxmlformats-officedocument.drawing+xml", }) output, _ := xml.Marshal(content) - f.saveFileList(`[Content_Types].xml`, string(output)) + f.saveFileList("[Content_Types].xml", string(output)) } // getSheetRelationshipsTargetByID provides function to get Target attribute diff --git a/sheet.go b/sheet.go index b5b410c..156d738 100644 --- a/sheet.go +++ b/sheet.go @@ -3,7 +3,10 @@ package excelize import ( "bytes" "encoding/xml" + "errors" "fmt" + "os" + "path" "strconv" "strings" ) @@ -193,8 +196,8 @@ func (f *File) GetActiveSheetIndex() int { xml.Unmarshal([]byte(f.readXML(buffer.String())), &xlsx) for _, sheetView := range xlsx.SheetViews.SheetView { if sheetView.TabSelected { - id, _ := strconv.Atoi(strings.TrimPrefix(v.ID, "rId")) - return id + ID, _ := strconv.Atoi(strings.TrimPrefix(v.ID, "rId")) + return ID } } buffer.Reset() @@ -258,3 +261,28 @@ func (f *File) GetSheetMap() map[int]string { } return sheetMap } + +// SetSheetBackground provides function to set background picture by given sheet +// index. +func (f *File) SetSheetBackground(sheet, picture string) error { + var supportTypes = map[string]string{".gif": ".gif", ".jpg": ".jpeg", ".jpeg": ".jpeg", ".png": ".png"} + var err error + // Check picture exists first. + if _, err = os.Stat(picture); os.IsNotExist(err) { + return err + } + ext, ok := supportTypes[path.Ext(picture)] + if !ok { + return errors.New("Unsupported image extension") + } + // Read sheet data. + var xlsx xlsxWorksheet + name := "xl/worksheets/" + strings.ToLower(sheet) + ".xml" + xml.Unmarshal([]byte(f.readXML(name)), &xlsx) + pictureID := f.countMedia() + 1 + rID := f.addSheetRelationships(sheet, SourceRelationshipImage, "../media/image"+strconv.Itoa(pictureID)+ext, "") + f.addSheetPicture(sheet, rID) + f.addMedia(picture, ext) + f.setContentTypePartImageExtensions() + return err +} diff --git a/test/images/background.jpg b/test/images/background.jpg new file mode 100644 index 0000000..9da8df0 Binary files /dev/null and b/test/images/background.jpg differ -- cgit v1.2.1