example chart (picture and not chart, finally)
stla opened this issue · 24 comments
Hello @qrilka
I have tried to do a chart. I'm rather lost, so my attempt is a bit random.
I took your file test/DrawingTests.hs and I added the code below. The module compiles without issue, but there's no chart in the created xlsx. Could you please say me what I'm doing wrong? I hope my attempt makes sense, but it's possibly totally silly.
testFileInfo :: FileInfo
testFileInfo = FileInfo
{
_fiFilename = "mytest.xml",
_fiContentType = "image/png",
_fiContents = renderChartSpace testScatterChartSpace
}
drawingObject :: DrawingObject FileInfo ChartSpace
drawingObject = picture id testFileInfo
where id = DrawingElementId {unDrawingElementId = 0}
testAnchor :: Anchor FileInfo ChartSpace
testAnchor = simpleAnchorXY (6, 6) size drawingObject
where size = positiveSize2D 1000 1000
mytestDrawing :: Drawing
mytestDrawing = Drawing {_xdrAnchors = [testAnchor]}
worksheet :: Worksheet
worksheet =
set wsDrawing (Just mytestDrawing) def
& cellValueAt (1,1) ?~ CellDouble 42.0
& cellValueAt (1,2) ?~ CellDouble 42.0
& cellValueAt (1,3) ?~ CellDouble 42.0
& cellValueAt (1,4) ?~ CellDouble 42.0
& cellValueAt (2,1) ?~ CellDouble 42.0
& cellValueAt (2,2) ?~ CellDouble 42.0
& cellValueAt (2,3) ?~ CellDouble 42.0
& cellValueAt (2,4) ?~ CellDouble 42.0
& cellValueAt (3,1) ?~ CellDouble 42.0
write :: IO()
write = do
ct <- getPOSIXTime
let xlsx = def & atSheet "Sheet1" ?~ worksheet
L.writeFile "chart.xlsx" (fromXlsx ct xlsx)
Now I'm afraid this is totally wrong. The xml file goes to the media folder, not to the charts folder...
Hi @stla - could you describe a bit what do you want to achieve as an end result?
Your code adds a picture but you talk about some chart - those are quite different drawing objects.
At the moment there's only that DrawingTests module unfortunately though I was thinking about adding examples directory with basic code snippets for partucular features. And in DrawingTests you could check functions testXXXChartSpace - i.e. you need to select chart type and add some series to it.
Please ask additional questions if you have any.
Hello @qrilka
I wanted to add a chart, not a picture. I will check the module you mention.
Now I understand that I add a picture, because the file goes to the media folder.
Actually I am also interested in adding a picture, but I don't know how to do as well. I've done an attempt. In my code above, I have replaced renderChartSpace testScatterChartSpace with the bytestring encoding of a picture. When I uncompress the xlsx file, I find the picture in the media folder as expected. But it does not appear in the sheet.
@stla could you share that resulting xlsx file? Did you keep that mytest.xml file name? I'm not 100% sure that Excel accepts arbitrary file names here.
BTW to create a picture there's a helper named... picture :)
I use picture. The file name is image1.png now.
The file: https://www.dropbox.com/s/z7o0g67fazhps2t/chart.xlsx?dl=0
@qrilka
I see the picture in the sheet now. In drawing1.xml, I have replaced
<xdr:spPr><a:noFill/>
<a:ln><a:noFill/></a:ln>
</xdr:spPr>
with
<xdr:spPr>
<a:xfrm><a:off x="0" y="762000"/><a:ext cx="4895850" cy="4743450"/></a:xfrm>
<a:prstGeom prst="rect"><a:avLst/></a:prstGeom>
</xdr:spPr>
<xdr:spPr>
<a:prstGeom prst="rect"><a:avLst/></a:prstGeom>
</xdr:spPr>
is enough.
@stla I think the problem was around
<ext cx="1000" cy="1000"/>
In your file as units of those values are EMUs and 1000 EMUs is 0.00278 cm which is way beyond visible.
And it makes me wonder why preset geometry overrides those extensions :(
and for sizes there is cmSize2D helper, though it only accepts integer values :-\
Yes, I also had to increase these values. But this is not enough, I got just a transparent box.
The transparent box:
It looks like 2 cell anchor works OK but not 1 cell anchor (at least in Excel Online).
At the same time Google Sheets and LibreOffice work OK in both cases.
Looks like another undocumented detail of Excel implementation and it's not clear what to do about it...
Weird. Below is my full code, if you're interested. I get the picture by setting the "geometry".
{-# LANGUAGE OverloadedStrings #-}
module Lib
where
import Control.Lens
import qualified Data.ByteString.Lazy as L
import Data.Time.Clock.POSIX (getPOSIXTime)
import Codec.Xlsx
testFileInfo :: FileInfo
testFileInfo = FileInfo
{
_fiFilename = "image1.png",
_fiContentType = "image/png",
_fiContents = "\137PNG\r\n\SUB\n\NUL\NUL\NUL\rIHDR\NUL\NUL\STXS\NUL\NUL\SOH\229\b\ETX\NUL\NUL\NUL\230\228\137/\NUL\NUL\NUL\ACKPLTE\NUL\NUL\NUL\255\255\255\165\217\159\221\NUL\NUL\NUL\tpHYs\NUL\NUL\SO\195\NUL\NUL\SO\195\SOH\199o\168d\NUL\NUL\EOT\245IDATx\156\237\215\&1\n\196\&0\DLE\EOT\193\245\255?}\169\ETX)Y\SUB\fGU\160X\f\GSH\243@k\190\190\NUL\DELGS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4EMS\212\&4\181\&7c\189\DC3\171\172\205c\190#\163l\205\235\228\205&[\154\186\177\201\150\166nl\178\230=ua\148=\255\190\&3\171P\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\211\DC45MQ\251\SOH\n\231gl\ETXsW\232\NUL\NUL\NUL\NULIEND\174B`\130"
}
drawingObject :: DrawingObject FileInfo ChartSpace
drawingObject = picture id testFileInfo
where id = DrawingElementId {unDrawingElementId = 1}
testAnchor :: Anchor FileInfo ChartSpace
testAnchor = simpleAnchorXY (6, 8) size drawingObject
where size = positiveSize2D 2000000 2000000
defaultShapeProperties :: ShapeProperties
defaultShapeProperties =
ShapeProperties {
_spXfrm = Nothing,
_spGeometry = Just PresetGeometry,
_spFill = Nothing,
_spOutline = Nothing
}
testAnchor2 :: Anchor FileInfo ChartSpace
testAnchor2 =
set anchObject pic testAnchor
where pic = set picShapeProperties defaultShapeProperties (_anchObject testAnchor)
testDrawing :: Drawing
testDrawing = Drawing {_xdrAnchors = [testAnchor2]}
worksheet :: Worksheet
worksheet =
set wsDrawing (Just testDrawing) def
& cellValueAt (1,1) ?~ CellDouble 42.0
write :: IO()
write = do
ct <- getPOSIXTime
let xlsx = def & atSheet "Sheet1" ?~ worksheet
L.writeFile "picture.xlsx" (fromXlsx ct xlsx)
@stla you do something special here - with this example I get We couldn't save your file as Google Sheets at this time. from Google Sheets and OneDrive was not showing up the file when I was uploading it but F5 resolved the problem. So it looks like there's something not quite well documented here :(
Many weird things. I've also noticed that the location of the anchor is different between Excel and LibreOffice.
<oneCellAnchor>
<from>
<col>2</col>
<colOff>3600000</colOff>
<row>3</row>
<rowOff>0</rowOff>
</from><ext cx="3000000" cy="3000000"/>
<pic>
<nvPicPr><cNvPr id="1" name="image1.png"/><cNvPicPr/></nvPicPr>
<blipFill><a:blip r:embed="rId1"/><a:stretch/></blipFill>
<spPr><a:prstGeom prst="rect"/></spPr>
</pic><clientData/></oneCellAnchor>
Excel:
LibreOffice:
Anyway that's great. I generate such sheets for my job. My boss was impressed today ^^
And sorry for a quite late reply
Hi @qrilka
I'm going to try the latest commit now.
I have renamed the title of this thread. It will look less messy like this and we can close it.
I've tried through my library jsonxlsx.
json2xlsx -d {\"A\":[1,2],\"B\":[3,\"x\"]} -H -i a.png -t 2 -l 4 -x 400 -y 400 -o testpicture.xlsx
This looks perfect !
Excel:
LibreOffice:
This even fixes another bug. Previously in Excel it was not possible to have the top-left corner in column A. Before this commit the above command gave:
@qrilka
Did you change something regarding the encoding? Previously with my library jsonxlsx I had no issues with UTF8 characters, but now they are not correctly rendered in the XLSX file.
Previously with my library jsonxlsx I had no issues with UTF8 characters, but now they are not correctly rendered in the XLSX file.
No sorry I'm not sure. In fact I call jsonxlsx from R, maybe this issue is from my R code actually.
That's fine, I fixed my encoding issue with the help of Data.ByteString.Lazy.UTF8.toString. This had nothing to do with the update of xlsx.