Images, comments and hyperlinks

Functions for the things a worksheet holds besides cell values.

Image functions

Guide: Images.

XLSX.addImage — Function
addImage(s::Worksheet, ref::AbstractString, image::Union{AbstractString, IOBuffer}; size=nothing, inCell=false, alt="")
addImage(s::Worksheet, row::Integer, col::Integer, image::Union{AbstractString, IOBuffer}; size=nothing, inCell=false, alt="")

Insert an image into a worksheet at the given cell reference. The image "floats" above the grid and does not affect cell contents or dimensions. In Excel, the image may be resized and repositioned by the user as normal. Supports file paths and IOBuffer sources.

If multiple, overlapping images are added, newer images overly older ones.

Arguments

s::Worksheet: the target worksheet.

ref::AbstractString: Either a valid cell reference (e.g. "A1") or a valid cell range (e.g. "B2:D4"). The image will be anchored to the top left of the reference and sized to fit within the reference bounds. If a cell range is given, the size keyword argument is ignored.

image::Union{AbstractString, IOBuffer}: Specifies the image to be inserted. Either:

  • a file path (String)
  • an IOBuffer containing raw image bytes. The buffer is left unchanged (its contents and position are not consumed).

Supported formats (auto-detected): PNG, JPEG, GIF.

Keyword Arguments

size: provide the desired size of the image as a tuple of integers: (width_px, height_px). If nothing (default), the image's native pixel size is used. Ignored if ref is a cell range. Actual size will snap to the nearest cell boundaries.

inCell: if true, place the picture in the cell ref, as Excel's Place in Cell does, instead of floating it over the grid. The picture becomes the cell's value and sizes itself to the cell; ref must be a single cell and size is not allowed. The same as s[ref] = CellImage(image; alt): see CellImage and getCellImage.

alt: a picture in a cell's alternative text, which a screen reader reads out.

Return Value

Returns a structured summary describing where and how the image was placed as a NamedTuple of String values:

(
    sheet      = sheet name,
    media_name = internal media file name,
    from       = Start cell (top left),
    to         = End cell (bottom right),
)

Examples

Insert from a file:

info = XLSX.addImage(sheet, "B2", "photo.jpg")

Insert from an IOBuffer:

buf = IOBuffer(read("logo.png"))
info = XLSX.addImage(sheet, "C5", buf)

Insert with explicit size:

info = XLSX.addImage(sheet, "A1", "icon.png"; size=(128, 128))

Place a picture in a cell:

info = XLSX.addImage(sheet, "B2", "logo.png"; inCell=true, alt="Company logo")
Note

When a specified size is used or when the image's native pixel size is used, the size of the image is always snapped to the nearest cell boundary. The calculation assumes a default cell size of 64 pixels width and 20 pixels height. If cells have been resized from these assumed dimensions, the image will be stretched or squeezed to fit the snapped cell range.

source
XLSX.getImages — Function
getImages(s::Worksheet) -> Vector{ImageInfo}
getImages(xf::XLSXFile) -> Vector{ImageInfo}

Return a list of all images in the specified worksheet.

source
XLSX.deleteImage — Function
deleteImage(s::Worksheet, ref) -> Int
deleteImage(s::Worksheet, image::ImageInfo) -> Int

Delete the images whose top-left corner is in cell ref ("B3", or a CellRef), and the picture in that cell if it holds one (see CellImage), or the image image returned by getImages. Returns how many were deleted: 0 if there were none. A cell whose picture is deleted is left empty, with its format.

The picture file goes too, unless another image in the workbook shows it. The cells under the image are not changed.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.deleteImage(sheet, "B3")
1

julia> foreach(img -> XLSX.deleteImage(sheet, img), XLSX.getImages(sheet))   # every image

See also addImage and getImages.

source
XLSX.CellImage — Type
CellImage

A picture in a cell, as Excel's Place in Cell puts one there: the cell's value is the picture, which sizes itself to the cell. Write one with sheet[ref] = CellImage(...) or addImage(...; inCell=true), and read one with getCellImage.

CellImage(image; alt="")

image is a file path or an IOBuffer holding a PNG, JPEG or GIF picture. alt is its alternative text, which a screen reader reads out.

Fields:

  • bytes::Vector{UInt8}: the picture's file
  • format::Symbol: :png, :jpeg or :gif
  • alt::String: its alternative text, or ""
  • url::Union{Nothing,String}: for a picture an IMAGE formula fetched from the web, its address. bytes then holds the copy Excel saved, if any.
source
XLSX.getCellImage — Function
getCellImage(ws::Worksheet, ref) -> Union{CellImage, Nothing}
getCellImage(xf::XLSXFile, "Sheet1!B2") -> Union{CellImage, Nothing}

The picture in cell ref, as a CellImage, or nothing if the cell holds none. The picture is one placed in the cell, by Excel's Place in Cell or by sheet[ref] = CellImage(...), or one an IMAGE formula shows, for which url gives its web address. Pictures floating over the cells are read with getImages.

A cell holding a picture reads as missing through getdata, readtable and the other functions that read values, since a picture isn't data.

Examples

julia> img = XLSX.getCellImage(sheet, "B2")
CellImage(:png, 5120 bytes, alt="Logo")

julia> write("logo.png", img.bytes);

See also getCellImages.

source
XLSX.getCellImages — Function
getCellImages(ws::Worksheet) -> Vector{NamedTuple}

Every picture in the sheet's cells, by row and then column, as (cell, image) NamedTuples of the cell's reference ("B2") and its CellImage.

See also getCellImage.

source

Text boxes and shapes

Guide: Text boxes and shapes.

XLSX.addTextBox — Function
addTextBox(ws::Worksheet, ref, text; kw...) -> NamedTuple

Add a text box, as Excel's Insert → Text Box does, floating over the cells like an image. ref is a cell, where its top-left corner goes, or a range it fills ("B2:E6"), as for addImage. text is a string, in which "\n" starts a new paragraph, or a RichTextString for runs in different fonts.

Keywords

  • size: the box's (width, height) in pixels, from a single cell. It snaps to whole cells, as an image does. The default is (192, 80).
  • font: a NamedTuple of the keywords of setFont for the whole text: bold, italic, under, strike, size (points), color, name and vertAlign. A rich text run's own settings take precedence.
  • fill: the box's colour, or :none. A text box is white by default.
  • line: the border's colour, or :none; lineWidth its width in points, and lineDash its dashes (:solid, :dash, :dot, :lgDash, :dashDot, :sysDash, :sysDot, ...). A text box has a thin grey border by default.
  • horizontal: :left, :center, :right or :justify.
  • vertical: :top, :middle or :bottom.
  • name: the name Excel shows for the box; by default TextBox n, as Excel names them.

Colours can be a hex value ("FF0000", "#FF0000" or 8-digit "FFFF0000", whose first two digits, transparency, Excel ignores), the name of a Colors.jl color as a String or Symbol, a Colors.jl Colorant, or a theme color ("accent1", :tx1 or a XLSX.SchemeColor). See Specifying colors.

Returns (sheet, name, from, to): the sheet, the box's name and the cells at its corners.

The file must be open for writing (mode="rw").

Examples

julia> XLSX.addTextBox(sheet, "B2:E6", "Quarterly results\nAll figures in thousands";
                       font=(size=14, bold=true), fill="FFF2CC", horizontal=:center, vertical=:middle)

julia> XLSX.addTextBox(sheet, "G2", XLSX.RichTextString(XLSX.RichTextRun("Note: ", [:bold => true]),
                                                         XLSX.RichTextRun("draft figures")))

See also addShape, getShapes and deleteShape.

source
XLSX.addShape — Function
addShape(ws::Worksheet, ref, kind::Symbol; text="", kw...) -> NamedTuple

Add a shape, as Excel's Insert → Shapes does, floating over the cells like an image: a rectangle, oval, arrow, callout and so on, in Excel's default style (the theme's first accent colour, with white text). kind is one of :rect, :roundRect, :ellipse, :triangle, :rtTriangle, :diamond, :parallelogram, :trapezoid, :pentagon, :hexagon, :octagon, :plus, :star5, :rightArrow, :leftArrow, :upArrow, :downArrow, :leftRightArrow, :chevron, :homePlate, :wedgeRectCallout, :wedgeRoundRectCallout, :wedgeEllipseCallout, :cloud, :heart, :lightningBolt, :sun, :moon, :smileyFace, :flowChartProcess, :flowChartDecision, :flowChartTerminator, :can, :cube.

text is the shape's text, as for addTextBox, whose keywords addShape takes too. By default the text is at the top left, as Excel puts it.

Examples

julia> XLSX.addShape(sheet, "H2:J5", :roundRect; text="Start here", horizontal=:center, vertical=:middle)

julia> XLSX.addShape(sheet, "H8", :rightArrow; fill="green", line=:none)

See also getShapes and deleteShape.

source
XLSX.getShapes — Function
getShapes(ws::Worksheet) -> Vector{NamedTuple}

The text boxes and shapes on a worksheet, each a NamedTuple with fields name, kind (:textbox, or the shape's kind, such as :rect or :ellipse), from and to (the cells at its corners) and text (its text, with a "\n" between paragraphs).

Images and charts aren't shapes: see getImages and getCharts.

Works on files opened for reading only.

source
XLSX.deleteShape — Function
deleteShape(ws::Worksheet, ref) -> Int

Delete the text boxes and shapes whose top-left corner is in cell ref ("B2", or a CellRef), or the one named ref, as getShapes gives the names. Returns how many were deleted. Images and charts are left alone.

The file must be open for writing (mode="rw").

source

Comment functions

Guide: Comments.

XLSX.addComment — Function
addComment(s::Worksheet, ref::AbstractString, text::AbstractString; author="", width=144, height=79, visible=false)
addComment(s::Worksheet, row::Integer, col::Integer, text::AbstractString; kw...)

Attach a comment (an Excel "Note") to a single cell. A cell with a comment shows a small red triangle in its top right corner, and the comment pops up when the mouse hovers over the cell.

If the cell already has a comment, it is replaced.

Comments can only be attached to a single cell. Giving a cell range is an error.

Arguments

s::Worksheet: the target worksheet.

ref: a single cell reference, e.g. "B3", or a CellRef.

text::AbstractString: the comment text. Use \n for line breaks.

Keyword Arguments

author::AbstractString: the comment's author. Defaults to "".

width, height: the size of the pop-up box, in pixels. The box is positioned assuming default cell sizes of 64 pixels wide and 20 pixels high.

visible::Bool: if true, the comment is always shown rather than only on hover.

Return Value

A NamedTuple of Strings: (sheet, ref, author, text).

Examples

XLSX.addComment(sheet, "B3", "Checked against the 2024 accounts"; author="Tim")
XLSX.addComment(sheet, 5, 2, "Line one\nLine two"; visible=true)
Note

Excel 365 also has threaded comments: see addThreadedComment. Every version of Excel shows the notes written here. A cell has either a note or a threaded comment, so a cell with a thread is refused.

See also getComment, getComments, deleteComment.

source
XLSX.getComment — Function
getComment(s::Worksheet, ref) -> Union{Nothing, NamedTuple}
getComment(s::Worksheet, row::Integer, col::Integer) -> Union{Nothing, NamedTuple}

Return the comment attached to the given cell as a NamedTuple of Strings, (sheet, ref, author, text), or nothing if the cell has no comment.

The text is returned as stored. Notes written by Excel itself usually begin with the author's name, e.g. "Tim:\n...", because Excel puts it in the note text.

For a threaded comment (Excel 365), this returns the legacy note that Excel stores alongside it; read the thread with getThreadedComment.

See also addComment, getComments.

source
XLSX.getComments — Function
getComments(s::Worksheet) -> Vector{NamedTuple}
getComments(xf::XLSXFile) -> Vector{NamedTuple}

Return every comment on a worksheet, or in every worksheet of a workbook, as a vector of NamedTuples of Strings, (sheet, ref, author, text).

See also addComment, getComment.

source
XLSX.deleteComment — Function
deleteComment(s::Worksheet, ref)
deleteComment(s::Worksheet, row::Integer, col::Integer)

Remove the comment attached to the given cell. Return true if a comment was removed and false if the cell had none.

See also addComment.

source
XLSX.addThreadedComment — Function
addThreadedComment(s::Worksheet, ref, text; author, date=now(UTC))
addThreadedComment(s::Worksheet, row::Integer, col::Integer, text; kw...)

Start a threaded comment (an Excel 365 comment, as opposed to a note) on a single cell. Excel 365 shows it in the cell's comment thread, where people can reply to it. Older versions of Excel show it as a note.

author is the name shown on the comment. Each name becomes one of the workbook's people, shared by every comment with the same name. date is when it was written, in UTC.

A cell has one thread: add to one with addThreadedReply. A cell can't have both a note and a threaded comment, so a cell with a note (addComment) is refused.

The file must be open for writing (mode="rw").

Return Value

The thread, as getThreadedComment returns it.

Examples

XLSX.addThreadedComment(sheet, "B3", "Is this figure final?"; author="Tim")
XLSX.addThreadedReply(sheet, "B3", "Yes, checked against the accounts."; author="Ann")
XLSX.setThreadedCommentResolved(sheet, "B3", true)

See also getThreadedComments, deleteThreadedComment.

source
XLSX.addThreadedReply — Function
addThreadedReply(s::Worksheet, ref, text; author, date=now(UTC))
addThreadedReply(s::Worksheet, row::Integer, col::Integer, text; kw...)

Add a reply to the end of the threaded comment on a cell (see addThreadedComment). The cell must already have a thread.

The file must be open for writing (mode="rw").

Return Value

The thread, as getThreadedComment returns it.

source
XLSX.setThreadedCommentResolved — Function
setThreadedCommentResolved(s::Worksheet, ref, resolved::Bool=true)
setThreadedCommentResolved(s::Worksheet, row::Integer, col::Integer, resolved::Bool=true)

Mark the threaded comment on a cell as resolved, or (with false) reopen it. Excel shows a resolved thread greyed out, and it can't be replied to there until it's reopened.

The file must be open for writing (mode="rw").

See also addThreadedComment.

source
XLSX.getThreadedComment — Function
getThreadedComment(s::Worksheet, ref) -> Union{Nothing, NamedTuple}
getThreadedComment(s::Worksheet, row::Integer, col::Integer) -> Union{Nothing, NamedTuple}

Return the threaded comment on the given cell, as getThreadedComments does, or nothing if the cell has none.

source
XLSX.getThreadedComments — Function
getThreadedComments(s::Worksheet) -> Vector{NamedTuple}
getThreadedComments(xf::XLSXFile) -> Vector{NamedTuple}

Return the threaded comments (Excel 365's comments, as opposed to notes) on a worksheet, or in every worksheet of a workbook: one NamedTuple per thread, (sheet, ref, author, text, date, resolved, replies). author is the person's display name, date a DateTime (or nothing if the file gives none), and resolved whether the thread is marked resolved. replies holds the replies, in order, each as (author, text, date).

The file doesn't need to be opened for writing. getComment returns, for a cell with a threaded comment, the note Excel stores alongside it for older versions of Excel.

See also getThreadedComment, addThreadedComment.

source
XLSX.deleteThreadedComment — Function
deleteThreadedComment(s::Worksheet, ref)
deleteThreadedComment(s::Worksheet, row::Integer, col::Integer)

Remove the threaded comment on a cell, with all its replies and the note Excel keeps for it. Return true if there was one and false if the cell had none.

The file must be open for writing (mode="rw").

See also addThreadedComment.

source

Guide: Hyperlinks.

XLSX.addHyperlink — Function
addHyperlink(s::Worksheet, ref; url=nothing, location=nothing, display=nothing, tooltip=nothing)
addHyperlink(s::Worksheet, row::Integer, col::Integer; kw...)

Make a cell, or a range of cells such as "A1:C3", a hyperlink. Give exactly one of url or location:

  • url: an address outside the workbook, such as a web page ("https://julialang.org"), an email address ("mailto:someone@example.com") or a file ("report.pdf", relative to the workbook's folder).
  • location: a place in this workbook, such as a cell ("Sheet2!A1", or "'My Sheet'!B4" for a sheet name with spaces) or a defined name.

If the cell is empty, its value is set to display (or to the url or location if display is not given), and it is given a blue, underlined font, as Excel does when you insert a link. A cell that already holds a value keeps its value and its formatting.

A range has one link, which works from any of its cells. As Excel does, the text goes in the range's top-left cell, if that cell is empty, and every empty cell in the range gets the blue, underlined font.

tooltip is the text Excel shows when the mouse is over the link. display is also stored with the link itself.

If the cell or range already has a hyperlink of its own, it is replaced. A hyperlink may not overlap another one: delete the other link first.

Return Value

A NamedTuple (sheet, ref, url, location, display, tooltip). Each field other than sheet and ref is a String or nothing.

Examples

XLSX.addHyperlink(sheet, "A1"; url="https://julialang.org", display="Julia")
XLSX.addHyperlink(sheet, "A2"; url="mailto:team@example.com", tooltip="Email the team")
XLSX.addHyperlink(sheet, 3, 1; location="Summary!B2", display="Go to summary")
XLSX.addHyperlink(sheet, "C1:E2"; url="https://example.com/report", display="Full report")

See also getHyperlink, getHyperlinks, deleteHyperlink.

source
XLSX.getHyperlink — Function
getHyperlink(s::Worksheet, ref) -> Union{Nothing, NamedTuple}
getHyperlink(s::Worksheet, row::Integer, col::Integer) -> Union{Nothing, NamedTuple}

Return the hyperlink on the given cell as a NamedTuple (sheet, ref, url, location, display, tooltip), or nothing if the cell has no hyperlink. A link on a range of cells, which Excel can write, is returned for any cell in the range, with the range as its ref.

url is set for a link outside the workbook and location for a link to a place in it. Each is nothing when not used, as are display and tooltip when the link doesn't have them.

The file doesn't need to be opened for writing.

See also addHyperlink, getHyperlinks.

source
XLSX.getHyperlinks — Function
getHyperlinks(s::Worksheet) -> Vector{NamedTuple}
getHyperlinks(xf::XLSXFile) -> Vector{NamedTuple}

Return every hyperlink on a worksheet, or on every worksheet of a workbook, as a vector of NamedTuples (sheet, ref, url, location, display, tooltip), in the order the file holds them.

The file doesn't need to be opened for writing.

See also addHyperlink, getHyperlink.

source
XLSX.deleteHyperlink — Function
deleteHyperlink(s::Worksheet, ref)
deleteHyperlink(s::Worksheet, row::Integer, col::Integer)

Remove the hyperlink on the given cell, or every hyperlink that overlaps the given range ("A1:C3"). Return true if a hyperlink was removed and false if there was none. A link on a range of cells is removed from its whole range.

The cell's value and formatting are left as they are.

See also addHyperlink.

source