From 02b81b7efe58549f88c93c1264ef618ee842c0fc Mon Sep 17 00:00:00 2001 From: Ri Xu Date: Tue, 28 Mar 2017 21:18:06 +0800 Subject: - Get an images in a cell supported, new function `GetPicture` added; - go test updated --- excelize_test.go | 26 +++++++++ picture.go | 82 +++++++++++++++++++++++++- sheet.go | 3 +- xmlDecodeDrawing.go | 162 ++++++++++++++++++++++++++++++++++++++++++++++++++++ xmlDrawing.go | 2 + 5 files changed, 271 insertions(+), 4 deletions(-) diff --git a/excelize_test.go b/excelize_test.go index 0ce1e54..0d83c86 100644 --- a/excelize_test.go +++ b/excelize_test.go @@ -4,6 +4,7 @@ import ( _ "image/gif" _ "image/jpeg" _ "image/png" + "io/ioutil" "strconv" "testing" ) @@ -376,3 +377,28 @@ func TestSetDeleteSheet(t *testing.T) { t.Log(err) } } + +func TestGetPicture(t *testing.T) { + xlsx, err := OpenFile("./test/Workbook_2.xlsx") + if err != nil { + t.Log(err) + } + file, raw := xlsx.GetPicture("Sheet1", "F21") + if file == "" { + err = ioutil.WriteFile(file, raw, 0644) + if err != nil { + t.Log(err) + } + } + // Try to get picture from a worksheet that doesn't contain any images. + file, raw = xlsx.GetPicture("Sheet3", "I9") + if file != "" { + err = ioutil.WriteFile(file, raw, 0644) + if err != nil { + t.Log(err) + } + } + // Try to get picture from a cell that doesn't contain an image. + file, raw = xlsx.GetPicture("Sheet2", "A2") + t.Log(file, len(raw)) +} diff --git a/picture.go b/picture.go index ab24f32..8bc4a1d 100644 --- a/picture.go +++ b/picture.go @@ -72,13 +72,12 @@ func parseFormatPictureSet(formatSet string) *formatPicture { // } // func (f *File) AddPicture(sheet, cell, picture, format 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)] + ext, ok := supportImageTypes[path.Ext(picture)] if !ok { return errors.New("Unsupported image extension") } @@ -360,3 +359,82 @@ func (f *File) getSheetRelationshipsTargetByID(sheet string, rID string) string } return "" } + +// GetPicture provides function to get picture base name and raw content embed +// in XLSX by given worksheet and cell name. This function returns the file name +// in XLSX and file contents as []byte data types. For example: +// +// xlsx, err := excelize.OpenFile("/tmp/Workbook.xlsx") +// if err != nil { +// fmt.Println(err) +// os.Exit(1) +// } +// file, raw := xlsx.GetPicture("Sheet1", "A2") +// if file == "" { +// os.Exit(1) +// } +// err := ioutil.WriteFile(file, raw, 0644) +// if err != nil { +// fmt.Println(err) +// os.Exit(1) +// } +// +func (f *File) GetPicture(sheet, cell string) (string, []byte) { + xlsx := f.workSheetReader(sheet) + if xlsx.Drawing == nil { + return "", []byte{} + } + target := f.getSheetRelationshipsTargetByID(sheet, xlsx.Drawing.RID) + drawingXML := strings.Replace(target, "..", "xl", -1) + + _, ok := f.XLSX[drawingXML] + if !ok { + return "", []byte{} + } + decodeWsDr := decodeWsDr{} + xml.Unmarshal([]byte(f.readXML(drawingXML)), &decodeWsDr) + + cell = strings.ToUpper(cell) + fromCol := string(strings.Map(letterOnlyMapF, cell)) + fromRow, _ := strconv.Atoi(strings.Map(intOnlyMapF, cell)) + row := fromRow - 1 + col := titleToNumber(fromCol) + + drawingRelationships := strings.Replace(strings.Replace(target, "../drawings", "xl/drawings/_rels", -1), ".xml", ".xml.rels", -1) + + for _, anchor := range decodeWsDr.TwoCellAnchor { + decodeTwoCellAnchor := decodeTwoCellAnchor{} + xml.Unmarshal([]byte(""+anchor.Content+""), &decodeTwoCellAnchor) + if decodeTwoCellAnchor.From == nil || decodeTwoCellAnchor.Pic == nil { + continue + } + if decodeTwoCellAnchor.From.Col == col && decodeTwoCellAnchor.From.Row == row { + xlsxWorkbookRelation := f.getDrawingRelationships(drawingRelationships, decodeTwoCellAnchor.Pic.BlipFill.Blip.Embed) + _, ok := supportImageTypes[filepath.Ext(xlsxWorkbookRelation.Target)] + if !ok { + continue + } + + return filepath.Base(xlsxWorkbookRelation.Target), []byte(f.XLSX[strings.Replace(xlsxWorkbookRelation.Target, "..", "xl", -1)]) + } + } + return "", []byte{} +} + +// getDrawingRelationships provides function to get drawing relationships from +// xl/drawings/_rels/drawing%s.xml.rels by given file name and relationship ID. +func (f *File) getDrawingRelationships(rels, rID string) *xlsxWorkbookRelation { + _, ok := f.XLSX[rels] + if !ok { + return nil + } + var drawingRels xlsxWorkbookRels + xml.Unmarshal([]byte(f.readXML(rels)), &drawingRels) + for _, v := range drawingRels.Relationships { + if v.ID != rID { + continue + } + return &v + } + return nil +} diff --git a/sheet.go b/sheet.go index 56a0ba9..aa653f8 100644 --- a/sheet.go +++ b/sheet.go @@ -257,13 +257,12 @@ func (f *File) GetSheetMap() map[int]string { // 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)] + ext, ok := supportImageTypes[path.Ext(picture)] if !ok { return errors.New("Unsupported image extension") } diff --git a/xmlDecodeDrawing.go b/xmlDecodeDrawing.go index 3d01719..bb4cd24 100644 --- a/xmlDecodeDrawing.go +++ b/xmlDecodeDrawing.go @@ -22,3 +22,165 @@ type decodeWsDr struct { TwoCellAnchor []*decodeCellAnchor `xml:"twoCellAnchor,omitempty"` XMLName xml.Name `xml:"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing wsDr,omitempty"` } + +// decodeTwoCellAnchor directly maps the oneCellAnchor (One Cell Anchor Shape +// Size) and twoCellAnchor (Two Cell Anchor Shape Size). This element specifies +// a two cell anchor placeholder for a group, a shape, or a drawing element. It +// moves with cells and its extents are in EMU units. +type decodeTwoCellAnchor struct { + From *decodeFrom `xml:"from"` + To *decodeTo `xml:"to"` + Pic *decodePic `xml:"pic,omitempty"` + ClientData *decodeClientData `xml:"clientData"` +} + +// decodeCNvPr directly maps the cNvPr (Non-Visual Drawing Properties). This +// element specifies non-visual canvas properties. This allows for additional +// information that does not affect the appearance of the picture to be stored. +type decodeCNvPr struct { + ID int `xml:"id,attr"` + Name string `xml:"name,attr"` + Descr string `xml:"descr,attr"` + Title string `xml:"title,attr,omitempty"` +} + +// decodePicLocks directly maps the picLocks (Picture Locks). This element +// specifies all locking properties for a graphic frame. These properties inform +// the generating application about specific properties that have been +// previously locked and thus should not be changed. +type decodePicLocks struct { + NoAdjustHandles bool `xml:"noAdjustHandles,attr,omitempty"` + NoChangeArrowheads bool `xml:"noChangeArrowheads,attr,omitempty"` + NoChangeAspect bool `xml:"noChangeAspect,attr"` + NoChangeShapeType bool `xml:"noChangeShapeType,attr,omitempty"` + NoCrop bool `xml:"noCrop,attr,omitempty"` + NoEditPoints bool `xml:"noEditPoints,attr,omitempty"` + NoGrp bool `xml:"noGrp,attr,omitempty"` + NoMove bool `xml:"noMove,attr,omitempty"` + NoResize bool `xml:"noResize,attr,omitempty"` + NoRot bool `xml:"noRot,attr,omitempty"` + NoSelect bool `xml:"noSelect,attr,omitempty"` +} + +// decodeBlip directly maps the blip element in the namespace +// http://purl.oclc.org/ooxml/officeDoc ument/relationships - This element +// specifies the existence of an image (binary large image or picture) and +// contains a reference to the image data. +type decodeBlip struct { + Embed string `xml:"embed,attr"` + Cstate string `xml:"cstate,attr,omitempty"` + R string `xml:"r,attr"` +} + +// decodeStretch directly maps the stretch element. This element specifies that +// a BLIP should be stretched to fill the target rectangle. The other option is +// a tile where a BLIP is tiled to fill the available area. +type decodeStretch struct { + FillRect string `xml:"fillRect"` +} + +// decodeOff directly maps the colOff and rowOff element. This element is used +// to specify the column offset within a cell. +type decodeOff struct { + X int `xml:"x,attr"` + Y int `xml:"y,attr"` +} + +// decodeExt directly maps the ext element. +type decodeExt struct { + Cx int `xml:"cx,attr"` + Cy int `xml:"cy,attr"` +} + +// decodePrstGeom directly maps the prstGeom (Preset geometry). This element +// specifies when a preset geometric shape should be used instead of a custom +// geometric shape. The generating application should be able to render all +// preset geometries enumerated in the ST_ShapeType list. +type decodePrstGeom struct { + Prst string `xml:"prst,attr"` +} + +// decodeXfrm directly maps the xfrm (2D Transform for Graphic Frame). This +// element specifies the transform to be applied to the corresponding graphic +// frame. This transformation is applied to the graphic frame just as it would +// be for a shape or group shape. +type decodeXfrm struct { + Off decodeOff `xml:"off"` + Ext decodeExt `xml:"ext"` +} + +// decodeCNvPicPr directly maps the cNvPicPr (Non-Visual Picture Drawing +// Properties). This element specifies the non-visual properties for the picture +// canvas. These properties are to be used by the generating application to +// determine how certain properties are to be changed for the picture object in +// question. +type decodeCNvPicPr struct { + PicLocks decodePicLocks `xml:"picLocks"` +} + +// directly maps the nvPicPr (Non-Visual Properties for a Picture). This element +// specifies all non-visual properties for a picture. This element is a +// container for the non-visual identification properties, shape properties and +// application properties that are to be associated with a picture. This allows +// for additional information that does not affect the appearance of the picture +// to be stored. +type decodeNvPicPr struct { + CNvPr decodeCNvPr `xml:"cNvPr"` + CNvPicPr decodeCNvPicPr `xml:"cNvPicPr"` +} + +// decodeBlipFill directly maps the blipFill (Picture Fill). This element +// specifies the kind of picture fill that the picture object has. Because a +// picture has a picture fill already by default, it is possible to have two +// fills specified for a picture object. +type decodeBlipFill struct { + Blip decodeBlip `xml:"blip"` + Stretch decodeStretch `xml:"stretch"` +} + +// decodeSpPr directly maps the spPr (Shape Properties). This element specifies +// the visual shape properties that can be applied to a picture. These are the +// same properties that are allowed to describe the visual properties of a shape +// but are used here to describe the visual appearance of a picture within a +// document. +type decodeSpPr struct { + Xfrm decodeXfrm `xml:"a:xfrm"` + PrstGeom decodePrstGeom `xml:"a:prstGeom"` +} + +// decodePic elements encompass the definition of pictures within the DrawingML +// framework. While pictures are in many ways very similar to shapes they have +// specific properties that are unique in order to optimize for picture- +// specific scenarios. +type decodePic struct { + NvPicPr decodeNvPicPr `xml:"nvPicPr"` + BlipFill decodeBlipFill `xml:"blipFill"` + SpPr decodeSpPr `xml:"spPr"` +} + +// decodeFrom specifies the starting anchor. +type decodeFrom struct { + Col int `xml:"col"` + ColOff int `xml:"colOff"` + Row int `xml:"row"` + RowOff int `xml:"rowOff"` +} + +// decodeTo directly specifies the ending anchor. +type decodeTo struct { + Col int `xml:"col"` + ColOff int `xml:"colOff"` + Row int `xml:"row"` + RowOff int `xml:"rowOff"` +} + +// decodeClientData directly maps the clientData element. An empty element which +// specifies (via attributes) certain properties related to printing and +// selection of the drawing object. The fLocksWithSheet attribute (either true +// or false) determines whether to disable selection when the sheet is +// protected, and fPrintsWithSheet attribute (either true or false) determines +// whether the object is printed when the sheet is printed. +type decodeClientData struct { + FLocksWithSheet bool `xml:"fLocksWithSheet,attr"` + FPrintsWithSheet bool `xml:"fPrintsWithSheet,attr"` +} diff --git a/xmlDrawing.go b/xmlDrawing.go index 94fcc97..4b87d9d 100644 --- a/xmlDrawing.go +++ b/xmlDrawing.go @@ -12,6 +12,8 @@ const ( NameSpaceXML = "http://www.w3.org/XML/1998/namespace" ) +var supportImageTypes = map[string]string{".gif": ".gif", ".jpg": ".jpeg", ".jpeg": ".jpeg", ".png": ".png"} + // xlsxCNvPr directly maps the cNvPr (Non-Visual Drawing Properties). This // element specifies non-visual canvas properties. This allows for additional // information that does not affect the appearance of the picture to be stored. -- cgit v1.2.1