15 _ "golang.org/x/image/bmp"
16 _ "golang.org/x/image/tiff"
18 "github.com/stretchr/testify/assert"
21 func BenchmarkAddPictureFromBytes(b *testing.B) {
23 imgFile, err := os.ReadFile(filepath.Join("test", "images", "excel.png"))
25 b.Error("unable to load image for benchmark")
28 for i := 1; i <= b.N; i++ {
29 if err := f.AddPictureFromBytes("Sheet1", fmt.Sprint("A", i), &Picture{Extension: ".png", File: imgFile, Format: &GraphicOptions{AltText: "Excel"}}); err != nil {
35 func TestAddPicture(t *testing.T) {
36 f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
37 assert.NoError(t, err)
39 // Test add picture to worksheet with offset and location hyperlink
40 assert.NoError(t, f.AddPicture("Sheet2", "I9", filepath.Join("test", "images", "excel.jpg"),
41 &GraphicOptions{OffsetX: 140, OffsetY: 120, Hyperlink: "#Sheet2!D8", HyperlinkType: "Location"}))
42 // Test add picture to worksheet with offset, external hyperlink and positioning
43 assert.NoError(t, f.AddPicture("Sheet1", "F21", filepath.Join("test", "images", "excel.jpg"),
44 &GraphicOptions{OffsetX: 10, OffsetY: 10, Hyperlink: "https://github.com/xuri/excelize", HyperlinkType: "External", Positioning: "oneCell"}))
46 file, err := os.ReadFile(filepath.Join("test", "images", "excel.png"))
47 assert.NoError(t, err)
49 // Test add picture to worksheet with autofit
50 assert.NoError(t, f.AddPicture("Sheet1", "A30", filepath.Join("test", "images", "excel.jpg"), &GraphicOptions{AutoFit: true}))
51 assert.NoError(t, f.AddPicture("Sheet1", "B30", filepath.Join("test", "images", "excel.jpg"), &GraphicOptions{OffsetX: 10, OffsetY: 10, AutoFit: true}))
52 _, err = f.NewSheet("AddPicture")
53 assert.NoError(t, err)
54 assert.NoError(t, f.SetRowHeight("AddPicture", 10, 30))
55 assert.NoError(t, f.MergeCell("AddPicture", "B3", "D9"))
56 assert.NoError(t, f.MergeCell("AddPicture", "B1", "D1"))
57 assert.NoError(t, f.AddPicture("AddPicture", "C6", filepath.Join("test", "images", "excel.jpg"), &GraphicOptions{AutoFit: true}))
58 assert.NoError(t, f.AddPicture("AddPicture", "A1", filepath.Join("test", "images", "excel.jpg"), &GraphicOptions{AutoFit: true}))
60 // Test add picture to worksheet from bytes
61 assert.NoError(t, f.AddPictureFromBytes("Sheet1", "Q1", &Picture{Extension: ".png", File: file, Format: &GraphicOptions{AltText: "Excel Logo"}}))
62 // Test add picture to worksheet from bytes with illegal cell reference
63 assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "A", &Picture{Extension: ".png", File: file, Format: &GraphicOptions{AltText: "Excel Logo"}}), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
65 for _, preset := range [][]string{{"Q8", "gif"}, {"Q15", "jpg"}, {"Q22", "tif"}, {"Q28", "bmp"}} {
66 assert.NoError(t, f.AddPicture("Sheet1", preset[0], filepath.Join("test", "images", fmt.Sprintf("excel.%s", preset[1])), nil))
69 // Test write file to given path
70 assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddPicture1.xlsx")))
71 assert.NoError(t, f.Close())
73 // Test get pictures after inserting a new picture from a workbook which contains existing pictures
74 f, err = OpenFile(filepath.Join("test", "TestAddPicture1.xlsx"))
75 assert.NoError(t, err)
76 assert.NoError(t, f.AddPicture("Sheet1", "A30", filepath.Join("test", "images", "excel.jpg"), nil))
77 pics, err := f.GetPictures("Sheet1", "A30")
78 assert.NoError(t, err)
79 assert.Len(t, pics, 2)
81 // Test get picture cells
82 cells, err := f.GetPictureCells("Sheet1")
83 assert.NoError(t, err)
84 assert.Equal(t, []string{"F21", "A30", "B30", "Q1", "Q8", "Q15", "Q22", "Q28"}, cells)
85 assert.NoError(t, f.Close())
87 f, err = OpenFile(filepath.Join("test", "TestAddPicture1.xlsx"))
88 assert.NoError(t, err)
89 path := "xl/drawings/drawing1.xml"
90 f.Drawings.Delete(path)
91 cells, err = f.GetPictureCells("Sheet1")
92 assert.NoError(t, err)
93 assert.Equal(t, []string{"F21", "A30", "B30", "Q1", "Q8", "Q15", "Q22", "Q28"}, cells)
94 // Test get picture cells with unsupported charset
95 f.Drawings.Delete(path)
96 f.Pkg.Store(path, MacintoshCyrillicCharset)
97 _, err = f.GetPictureCells("Sheet1")
98 assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
99 assert.NoError(t, f.Close())
101 f, err = OpenFile(filepath.Join("test", "TestAddPicture1.xlsx"))
102 assert.NoError(t, err)
103 // Test get picture cells with unsupported charset
104 f.Pkg.Store(path, MacintoshCyrillicCharset)
105 _, err = f.GetPictureCells("Sheet1")
106 assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
107 assert.NoError(t, f.Close())
109 // Test add picture with unsupported charset content types
112 f.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
113 assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "Q1", &Picture{Extension: ".png", File: file, Format: &GraphicOptions{AltText: "Excel Logo"}}), "XML syntax error on line 1: invalid UTF-8")
115 // Test add picture with invalid sheet name
116 assert.EqualError(t, f.AddPicture("Sheet:1", "A1", filepath.Join("test", "images", "excel.jpg"), nil), ErrSheetNameInvalid.Error())
119 func TestAddPictureErrors(t *testing.T) {
120 f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
121 assert.NoError(t, err)
123 // Test add picture to worksheet with invalid file path
124 assert.Error(t, f.AddPicture("Sheet1", "G21", filepath.Join("test", "not_exists_dir", "not_exists.icon"), nil))
126 // Test add picture to worksheet with unsupported file type
127 assert.EqualError(t, f.AddPicture("Sheet1", "G21", filepath.Join("test", "Book1.xlsx"), nil), ErrImgExt.Error())
129 assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "G21", &Picture{Extension: "jpg", File: make([]byte, 1), Format: &GraphicOptions{AltText: "Excel Logo"}}), ErrImgExt.Error())
131 // Test add picture to worksheet with invalid file data
132 assert.EqualError(t, f.AddPictureFromBytes("Sheet1", "G21", &Picture{Extension: ".jpg", File: make([]byte, 1), Format: &GraphicOptions{AltText: "Excel Logo"}}), image.ErrFormat.Error())
134 // Test add picture with custom image decoder and encoder
135 decode := func(r io.Reader) (image.Image, error) { return nil, nil }
136 decodeConfig := func(r io.Reader) (image.Config, error) { return image.Config{Height: 100, Width: 90}, nil }
137 for cell, ext := range map[string]string{"Q1": "emf", "Q7": "wmf", "Q13": "emz", "Q19": "wmz"} {
138 image.RegisterFormat(ext, "", decode, decodeConfig)
139 assert.NoError(t, f.AddPicture("Sheet1", cell, filepath.Join("test", "images", fmt.Sprintf("excel.%s", ext)), nil))
141 assert.NoError(t, f.AddPicture("Sheet1", "Q25", "excelize.svg", &GraphicOptions{ScaleX: 2.8}))
142 assert.NoError(t, f.SaveAs(filepath.Join("test", "TestAddPicture2.xlsx")))
143 assert.NoError(t, f.Close())
146 func TestGetPicture(t *testing.T) {
148 assert.NoError(t, f.AddPicture("Sheet1", "A1", filepath.Join("test", "images", "excel.png"), nil))
149 pics, err := f.GetPictures("Sheet1", "A1")
150 assert.NoError(t, err)
151 assert.Len(t, pics[0].File, 13233)
152 assert.Empty(t, pics[0].Format.AltText)
154 f, err = prepareTestBook1()
155 if !assert.NoError(t, err) {
159 pics, err = f.GetPictures("Sheet1", "F21")
160 assert.NoError(t, err)
161 if !assert.NotEmpty(t, filepath.Join("test", fmt.Sprintf("image1%s", pics[0].Extension))) || !assert.NotEmpty(t, pics[0].File) ||
162 !assert.NoError(t, os.WriteFile(filepath.Join("test", fmt.Sprintf("image1%s", pics[0].Extension)), pics[0].File, 0o644)) {
166 // Try to get picture from a worksheet with illegal cell reference
167 _, err = f.GetPictures("Sheet1", "A")
168 assert.EqualError(t, err, newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
170 // Try to get picture from a worksheet that doesn't contain any images
171 pics, err = f.GetPictures("Sheet3", "I9")
172 assert.EqualError(t, err, "sheet Sheet3 does not exist")
173 assert.Len(t, pics, 0)
175 // Try to get picture from a cell that doesn't contain an image
176 pics, err = f.GetPictures("Sheet2", "A2")
177 assert.NoError(t, err)
178 assert.Len(t, pics, 0)
180 // Test get picture with invalid sheet name
181 _, err = f.GetPictures("Sheet:1", "A2")
182 assert.EqualError(t, err, ErrSheetNameInvalid.Error())
184 f.getDrawingRelationships("xl/worksheets/_rels/sheet1.xml.rels", "rId8")
185 f.getDrawingRelationships("", "")
186 f.getSheetRelationshipsTargetByID("", "")
187 f.deleteSheetRelationships("", "")
189 // Try to get picture from a local storage file.
190 assert.NoError(t, f.SaveAs(filepath.Join("test", "TestGetPicture.xlsx")))
192 f, err = OpenFile(filepath.Join("test", "TestGetPicture.xlsx"))
193 assert.NoError(t, err)
195 pics, err = f.GetPictures("Sheet1", "F21")
196 assert.NoError(t, err)
197 if !assert.NotEmpty(t, filepath.Join("test", fmt.Sprintf("image1%s", pics[0].Extension))) || !assert.NotEmpty(t, pics[0].File) ||
198 !assert.NoError(t, os.WriteFile(filepath.Join("test", fmt.Sprintf("image1%s", pics[0].Extension)), pics[0].File, 0o644)) {
202 // Try to get picture from a local storage file that doesn't contain an image
203 pics, err = f.GetPictures("Sheet1", "F22")
204 assert.NoError(t, err)
205 assert.Len(t, pics, 0)
206 assert.NoError(t, f.Close())
208 // Try to get picture with one cell anchor
209 f, err = OpenFile(filepath.Join("test", "TestGetPicture.xlsx"))
210 assert.NoError(t, err)
211 f.Pkg.Store("xl/drawings/drawing2.xml", []byte(`<xdr:wsDr xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><xdr:oneCellAnchor><xdr:from><xdr:col>10</xdr:col><xdr:row>15</xdr:row></xdr:from><xdr:to><xdr:col>13</xdr:col><xdr:row>22</xdr:row></xdr:to><xdr:pic><xdr:nvPicPr><xdr:cNvPr id="2"></xdr:cNvPr></xdr:nvPicPr><xdr:blipFill><a:blip r:embed="rId1"></a:blip></xdr:blipFill></xdr:pic></xdr:oneCellAnchor></xdr:wsDr>`))
212 pics, err = f.GetPictures("Sheet2", "K16")
213 assert.NoError(t, err)
214 assert.Len(t, pics, 1)
215 // Try to get picture cells with one cell anchor
216 cells, err := f.GetPictureCells("Sheet2")
217 assert.NoError(t, err)
218 assert.Equal(t, []string{"K16"}, cells)
220 // Test get picture from none drawing worksheet
222 pics, err = f.GetPictures("Sheet1", "F22")
223 assert.NoError(t, err)
224 assert.Len(t, pics, 0)
225 f, err = prepareTestBook1()
226 assert.NoError(t, err)
228 // Test get pictures with unsupported charset
229 path := "xl/drawings/drawing1.xml"
230 f.Drawings.Delete(path)
231 f.Pkg.Store(path, MacintoshCyrillicCharset)
232 _, err = f.getPicture(20, 5, path, "xl/drawings/_rels/drawing2.xml.rels")
233 assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
234 f.Drawings.Delete(path)
235 _, err = f.getPicture(20, 5, path, "xl/drawings/_rels/drawing2.xml.rels")
236 assert.EqualError(t, err, "XML syntax error on line 1: invalid UTF-8")
239 func TestAddDrawingPicture(t *testing.T) {
240 // Test addDrawingPicture with illegal cell reference
242 opts := &GraphicOptions{PrintObject: boolPtr(true), Locked: boolPtr(false)}
243 assert.EqualError(t, f.addDrawingPicture("sheet1", "", "A", "", 0, 0, image.Config{}, opts), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
244 // Test addDrawingPicture with invalid positioning types
245 assert.Equal(t, f.addDrawingPicture("sheet1", "", "A1", "", 0, 0, image.Config{}, &GraphicOptions{Positioning: "x"}), ErrParameterInvalid)
247 path := "xl/drawings/drawing1.xml"
248 f.Pkg.Store(path, MacintoshCyrillicCharset)
249 assert.EqualError(t, f.addDrawingPicture("sheet1", path, "A1", "", 0, 0, image.Config{}, opts), "XML syntax error on line 1: invalid UTF-8")
252 func TestAddPictureFromBytes(t *testing.T) {
254 imgFile, err := os.ReadFile("logo.png")
255 assert.NoError(t, err, "Unable to load logo for test")
257 assert.NoError(t, f.AddPictureFromBytes("Sheet1", fmt.Sprint("A", 1), &Picture{Extension: ".png", File: imgFile, Format: &GraphicOptions{AltText: "logo"}}))
258 assert.NoError(t, f.AddPictureFromBytes("Sheet1", fmt.Sprint("A", 50), &Picture{Extension: ".png", File: imgFile, Format: &GraphicOptions{AltText: "logo"}}))
260 f.Pkg.Range(func(fileName, v interface{}) bool {
261 if strings.Contains(fileName.(string), "media/image") {
266 assert.Equal(t, 1, imageCount, "Duplicate image should only be stored once.")
267 assert.EqualError(t, f.AddPictureFromBytes("SheetN", fmt.Sprint("A", 1), &Picture{Extension: ".png", File: imgFile, Format: &GraphicOptions{AltText: "logo"}}), "sheet SheetN does not exist")
268 // Test add picture from bytes with invalid sheet name
269 assert.EqualError(t, f.AddPictureFromBytes("Sheet:1", fmt.Sprint("A", 1), &Picture{Extension: ".png", File: imgFile, Format: &GraphicOptions{AltText: "logo"}}), ErrSheetNameInvalid.Error())
272 func TestDeletePicture(t *testing.T) {
273 f, err := OpenFile(filepath.Join("test", "Book1.xlsx"))
274 assert.NoError(t, err)
275 // Test delete picture on a worksheet which does not contains any pictures
276 assert.NoError(t, f.DeletePicture("Sheet1", "A1"))
277 // Add same pictures on different worksheets
278 assert.NoError(t, f.AddPicture("Sheet1", "F20", filepath.Join("test", "images", "excel.jpg"), nil))
279 assert.NoError(t, f.AddPicture("Sheet1", "I20", filepath.Join("test", "images", "excel.jpg"), nil))
280 assert.NoError(t, f.AddPicture("Sheet2", "F1", filepath.Join("test", "images", "excel.jpg"), nil))
281 // Test delete picture on a worksheet, the images should be preserved
282 assert.NoError(t, f.DeletePicture("Sheet1", "F20"))
283 assert.NoError(t, f.SaveAs(filepath.Join("test", "TestDeletePicture.xlsx")))
284 assert.NoError(t, f.Close())
286 f, err = OpenFile(filepath.Join("test", "TestDeletePicture.xlsx"))
287 assert.NoError(t, err)
288 // Test delete same picture on different worksheet, the images should be removed
289 assert.NoError(t, f.DeletePicture("Sheet1", "F10"))
290 assert.NoError(t, f.DeletePicture("Sheet2", "F1"))
291 assert.NoError(t, f.DeletePicture("Sheet1", "I20"))
292 assert.NoError(t, f.SaveAs(filepath.Join("test", "TestDeletePicture2.xlsx")))
294 // Test delete picture on not exists worksheet
295 assert.EqualError(t, f.DeletePicture("SheetN", "A1"), "sheet SheetN does not exist")
296 // Test delete picture with invalid sheet name
297 assert.Equal(t, ErrSheetNameInvalid, f.DeletePicture("Sheet:1", "A1"))
298 // Test delete picture with invalid coordinates
299 assert.Equal(t, newCellNameToCoordinatesError("", newInvalidCellNameError("")), f.DeletePicture("Sheet1", ""))
300 assert.NoError(t, f.Close())
301 // Test delete picture on no chart worksheet
302 assert.NoError(t, NewFile().DeletePicture("Sheet1", "A1"))
304 f, err = OpenFile(filepath.Join("test", "TestDeletePicture.xlsx"))
305 assert.NoError(t, err)
306 // Test delete picture with unsupported charset drawing
307 f.Pkg.Store("xl/drawings/drawing1.xml", MacintoshCyrillicCharset)
308 assert.EqualError(t, f.DeletePicture("Sheet1", "F10"), "XML syntax error on line 1: invalid UTF-8")
309 assert.NoError(t, f.Close())
311 f, err = OpenFile(filepath.Join("test", "TestDeletePicture.xlsx"))
312 assert.NoError(t, err)
313 // Test delete picture with unsupported charset drawing relationships
314 f.Relationships.Delete("xl/drawings/_rels/drawing1.xml.rels")
315 f.Pkg.Store("xl/drawings/_rels/drawing1.xml.rels", MacintoshCyrillicCharset)
316 assert.NoError(t, f.DeletePicture("Sheet2", "F1"))
317 assert.NoError(t, f.Close())
320 assert.NoError(t, err)
321 assert.NoError(t, f.AddPicture("Sheet1", "A1", filepath.Join("test", "images", "excel.jpg"), nil))
322 assert.NoError(t, f.AddPicture("Sheet1", "G1", filepath.Join("test", "images", "excel.jpg"), nil))
323 drawing, ok := f.Drawings.Load("xl/drawings/drawing1.xml")
325 // Made two picture reference the same drawing relationship ID
326 drawing.(*xlsxWsDr).TwoCellAnchor[1].Pic.BlipFill.Blip.Embed = "rId1"
327 assert.NoError(t, f.DeletePicture("Sheet1", "A1"))
328 assert.NoError(t, f.Close())
331 func TestDrawingResize(t *testing.T) {
333 // Test calculate drawing resize on not exists worksheet
334 _, _, _, _, err := f.drawingResize("SheetN", "A1", 1, 1, nil)
335 assert.EqualError(t, err, "sheet SheetN does not exist")
336 // Test calculate drawing resize with invalid coordinates
337 _, _, _, _, err = f.drawingResize("Sheet1", "", 1, 1, nil)
338 assert.EqualError(t, err, newCellNameToCoordinatesError("", newInvalidCellNameError("")).Error())
339 ws, ok := f.Sheet.Load("xl/worksheets/sheet1.xml")
341 ws.(*xlsxWorksheet).MergeCells = &xlsxMergeCells{Cells: []*xlsxMergeCell{{Ref: "A:A"}}}
342 assert.EqualError(t, f.AddPicture("Sheet1", "A1", filepath.Join("test", "images", "excel.jpg"), &GraphicOptions{AutoFit: true}), newCellNameToCoordinatesError("A", newInvalidCellNameError("A")).Error())
345 func TestSetContentTypePartRelsExtensions(t *testing.T) {
347 f.ContentTypes = &xlsxTypes{}
348 assert.NoError(t, f.setContentTypePartRelsExtensions())
350 // Test set content type part relationships extensions with unsupported charset content types
352 f.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
353 assert.EqualError(t, f.setContentTypePartRelsExtensions(), "XML syntax error on line 1: invalid UTF-8")
356 func TestSetContentTypePartImageExtensions(t *testing.T) {
358 // Test set content type part image extensions with unsupported charset content types
360 f.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
361 assert.EqualError(t, f.setContentTypePartImageExtensions(), "XML syntax error on line 1: invalid UTF-8")
364 func TestSetContentTypePartVMLExtensions(t *testing.T) {
366 // Test set content type part VML extensions with unsupported charset content types
368 f.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
369 assert.EqualError(t, f.setContentTypePartVMLExtensions(), "XML syntax error on line 1: invalid UTF-8")
372 func TestAddContentTypePart(t *testing.T) {
374 // Test add content type part with unsupported charset content types
376 f.Pkg.Store(defaultXMLPathContentTypes, MacintoshCyrillicCharset)
377 assert.EqualError(t, f.addContentTypePart(0, "unknown"), "XML syntax error on line 1: invalid UTF-8")
380 func TestGetPictureCells(t *testing.T) {
382 // Test get picture cells on a worksheet which not contains any pictures
383 cells, err := f.GetPictureCells("Sheet1")
384 assert.NoError(t, err)
385 assert.Empty(t, cells)
386 // Test get picture cells on not exists worksheet
387 _, err = f.GetPictureCells("SheetN")
388 assert.EqualError(t, err, "sheet SheetN does not exist")
391 func TestExtractDecodeCellAnchor(t *testing.T) {
393 cond := func(a *decodeFrom) bool { return true }
394 cb := func(a *decodeCellAnchor, r *xlsxRelationship) {}
395 f.extractDecodeCellAnchor(&xdrCellAnchor{GraphicFrame: string(MacintoshCyrillicCharset)}, "", cond, cb)