You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Ken Hausam <ka...@yahoo.com.INVALID> on 2016/01/09 23:27:40 UTC
Change chart type programmatically?
I am using XSSFChart and associated classes to create a line chart using Apache POI. Works great! Thanks. My question is, is there an easy way to change the chart type programmatically from a line chart to a stacked bar chart? I looked quickly at the CTChart class and associated CT classes and noticed that the various chart types had their own class. This makes me think that it's not as easy as just flipping a chart type attribute somewhere, but figured it couldn't hurt to ask.
Assuming that the answer to question above is "it can't be done easily otherwise we would have already included it in the API", does anyone have any creative ideas on ways to change the chart type? I am familiar with Microsoft's VSTO Add-In API and know that it could be done with this, but would like to keep my solution cross platform.
Thanks for reading,Ken
Re: Change chart type programmatically?
Posted by Ken Hausam <ka...@yahoo.com.INVALID>.
It may not be my chart changes that caused the "file format is not valid" message. I tried renaming an .xlsx to .zip, expanding, then compressing, then renaming .zip to .xlsx and I got the same "file format is not valid" message. No edits to any files. I will try some different zip tools until I guess this simple test working.
Ken
On Thursday, January 14, 2016 10:40 PM, Ken Hausam <ka...@yahoo.com> wrote:
Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of XML files.
After changing chart type, only one file changed: the chart1.xml file location in the xl/charts/ directory. I've attached the before (line.xml) and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't seem trivial either. I couldn't list the actual diff since the XML is all on one line which makes the diff hard to view as a text diff.
I already tried a very simple change: changing <c:lineChart> to <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping val="stacked"/> . No luck. Excel complained with "file format is not valid" when I tried to open it.
Don't know if it makes a difference, but I was using Mac Excel 2011 to change the chart type.
Thanks,Ken
On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org> wrote:
On Sat, 9 Jan 2016, Ken Hausam wrote:
> I am using XSSFChart and associated classes to create a line chart using
> Apache POI. Works great! Thanks. My question is, is there an easy way to
> change the chart type programmatically from a line chart to a stacked
> bar chart? I looked quickly at the CTChart class and associated CT
> classes and noticed that the various chart types had their own class.
> This makes me think that it's not as easy as just flipping a chart type
> attribute somewhere, but figured it couldn't hurt to ask.
I haven't looked at the chart stuff recently, so I can't answer off the
top of my head. What I'd suggest you do is firstly create a simple file in
Excel, with one sheet, with a few data points, and one style of chart.
Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
files (rename to .zip and unpack). Now, compare the xml, especially for
sheets and charts, and see what differs. Post a summary of that, and we'll
help if we can!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Dustin Spicuzza <du...@virtualroadside.com>.
Seems useful. I had put a script for unzipping the contents of OOXML
files and expanding the XML so a human could read it somewhere in the
tree... could combine the two.
Dustin
On 1/15/16 11:10 AM, Javen O'Neal wrote:
> I wrote some python code that accepts two OOXML files, unzips them
> (zipfile), compares the zip structures, then compares file contents for
> matching files (difflib), then outputs the diff.
>
> Would this be helpful to the POI project, and where would I commit it?
> On Jan 15, 2016 3:58 AM, "Dominik Stadler" <do...@gmx.at> wrote:
>
>> FYI, we also have a helper application OOXMLPrettyPrint as part of the POI
>> source code which will take a .xlsx/.docx/.pptx/... file and produce a
>> pretty-printed one (which as Dave stated might not have exactly the same
>> content any more!). If you run this on both files you can then better
>> compare these. Best if you combine this with a compare tool that can be
>> made aware that these files are actually ZIPs and allows to do in-ZIP
>> comparisons...
>>
>> Dominik.
>>
>> On Fri, Jan 15, 2016 at 9:27 AM, David Law <da...@apconsult.de> wrote:
>>
>>> Please note: this may be ok for a comparison, but...
>>> XML DOES NOT NORMALLY CONTAIN NEWLINES
>>> They result in spurious Text nodes.
>>> (which most products simply ignore because they are so common)
>>>
>>> Javen O'Neals suggestion to convert to canonical is a much better idea.
>>>
>>> Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
>>> <html
>>> ><body bgcolor="#ffff00"
>>> ><h1>Hello world!</h1
>>> ><p
>>> ><a href="https://poi.apache.org/">XML does not (normally)
>>> contain newlines</a
>>> ></p
>>> ><img src="Logo.png" alt="Smiley face" border="1"
>>> /></body
>>>> </html>
>>> P.S. the above is valid XML but NOT Valid XHTML :-)
>>>
>>> All the best,
>>> DaveLaw
>>>
>>>
>>> On 15/01/2016 04:50, Javen O'Neal wrote:
>>>
>>>> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
>>>> text editor with HTML/XML pretty printing.
>>>> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid>
>> wrote:
>>>> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
>>>>> XML files.
>>>>>
>>>>> After changing chart type, only one file changed: the chart1.xml file
>>>>> location in the xl/charts/ directory. I've attached the before
>> (line.xml)
>>>>> and after (stackedbar.xml) files. The diff isn't enormous, but it
>> doesn't
>>>>> seem trivial either. I couldn't list the actual diff since the XML is
>> all
>>>>> on one line which makes the diff hard to view as a text diff.
>>>>>
>>>>> I already tried a very simple change: changing <c:lineChart> to
>>>>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
>>>>> val="stacked"/> . No luck. Excel complained with "file format is not
>>>>> valid"
>>>>> when I tried to open it.
>>>>>
>>>>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
>>>>> change the chart type.
>>>>>
>>>>> Thanks,
>>>>> Ken
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <
>> apache@gagravarr.org
>>>>> wrote:
>>>>>
>>>>>
>>>>> On Sat, 9 Jan 2016, Ken Hausam wrote:
>>>>>
>>>>> I am using XSSFChart and associated classes to create a line chart
>> using
>>>>>> Apache POI. Works great! Thanks. My question is, is there an easy way
>> to
>>>>>> change the chart type programmatically from a line chart to a stacked
>>>>>> bar chart? I looked quickly at the CTChart class and associated CT
>>>>>> classes and noticed that the various chart types had their own class.
>>>>>> This makes me think that it's not as easy as just flipping a chart
>> type
>>>>>> attribute somewhere, but figured it couldn't hurt to ask.
>>>>>>
>>>>> I haven't looked at the chart stuff recently, so I can't answer off the
>>>>> top of my head. What I'd suggest you do is firstly create a simple file
>>>>> in
>>>>> Excel, with one sheet, with a few data points, and one style of chart.
>>>>> Save that. Next, change the type, and save-as that. Next, unzip both
>>>>> .xlsx
>>>>> files (rename to .zip and unpack). Now, compare the xml, especially for
>>>>> sheets and charts, and see what differs. Post a summary of that, and
>>>>> we'll
>>>>> help if we can!
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Ken Hausam <ka...@yahoo.com.INVALID>.
I came up with some code that works for my purposes. See below. The two replace lines are the significant code. The rest is plumbing. Thanks for all your help.
ZipInputStream zis = new ZipInputStream(new FileInputStream(args[0]));
ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(args[0] + ".new.xlsx"));
try {
ZipEntry entry;
while((entry = zis.getNextEntry())!=null) {
zos.putNextEntry(new ZipEntry(entry.getName()));
if(entry.getName().equals("xl/charts/chart1.xml")) {
StringWriter swriter = new StringWriter();
IOUtils.copy(zis, swriter);
String chartXml = swriter.toString();
chartXml = chartXml.replace("<c:lineChart>", "<c:barChart><c:barDir val=\"col\"/><c:grouping val=\"stacked\"/>");
chartXml = chartXml.replace("</c:lineChart>", "<c:gapWidth val=\"150\"/><c:overlap val=\"100\"/></c:barChart>");
StringReader sreader = new StringReader(chartXml);
IOUtils.copy(sreader, zos);
}
else {
IOUtils.copy(zis, zos);
}
zos.closeEntry();
}
}
finally {
zis.close();
zos.close();
}
On Friday, January 15, 2016 2:54 PM, Dominik Stadler <do...@gmx.at> wrote:
Hi,
Sounds nice!
Java-Dev-code usually resides in src/java/o.a.p.dev, but as your stuff it
is written in Python, it would probably make sense to put it under
"contrib/python" for now to not add more confusion to the list of
directories under /src directly.
Dominik.
On Fri, Jan 15, 2016 at 5:10 PM, Javen O'Neal <ja...@gmail.com> wrote:
> I wrote some python code that accepts two OOXML files, unzips them
> (zipfile), compares the zip structures, then compares file contents for
> matching files (difflib), then outputs the diff.
>
> Would this be helpful to the POI project, and where would I commit it?
> On Jan 15, 2016 3:58 AM, "Dominik Stadler" <do...@gmx.at> wrote:
>
> > FYI, we also have a helper application OOXMLPrettyPrint as part of the
> POI
> > source code which will take a .xlsx/.docx/.pptx/... file and produce a
> > pretty-printed one (which as Dave stated might not have exactly the same
> > content any more!). If you run this on both files you can then better
> > compare these. Best if you combine this with a compare tool that can be
> > made aware that these files are actually ZIPs and allows to do in-ZIP
> > comparisons...
> >
> > Dominik.
> >
> > On Fri, Jan 15, 2016 at 9:27 AM, David Law <da...@apconsult.de>
> wrote:
> >
> > > Please note: this may be ok for a comparison, but...
> > > XML DOES NOT NORMALLY CONTAIN NEWLINES
> > > They result in spurious Text nodes.
> > > (which most products simply ignore because they are so common)
> > >
> > > Javen O'Neals suggestion to convert to canonical is a much better idea.
> > >
> > > Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
> > > <html
> > > ><body bgcolor="#ffff00"
> > > ><h1>Hello world!</h1
> > > ><p
> > > ><a href="https://poi.apache.org/">XML does not (normally)
> > > contain newlines</a
> > > ></p
> > > ><img src="Logo.png" alt="Smiley face" border="1"
> > > /></body
> > > ></html>
> > >
> > > P.S. the above is valid XML but NOT Valid XHTML :-)
> > >
> > > All the best,
> > > DaveLaw
> > >
> > >
> > > On 15/01/2016 04:50, Javen O'Neal wrote:
> > >
> > >> Find-replace all ">" with ">\n", then run the diff. Alternatively,
> use a
> > >> text editor with HTML/XML pretty printing.
> > >> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid>
> > wrote:
> > >>
> > >> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips
> of
> > >>> XML files.
> > >>>
> > >>> After changing chart type, only one file changed: the chart1.xml file
> > >>> location in the xl/charts/ directory. I've attached the before
> > (line.xml)
> > >>> and after (stackedbar.xml) files. The diff isn't enormous, but it
> > doesn't
> > >>> seem trivial either. I couldn't list the actual diff since the XML is
> > all
> > >>> on one line which makes the diff hard to view as a text diff.
> > >>>
> > >>> I already tried a very simple change: changing <c:lineChart> to
> > >>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
> > >>> val="stacked"/> . No luck. Excel complained with "file format is not
> > >>> valid"
> > >>> when I tried to open it.
> > >>>
> > >>> Don't know if it makes a difference, but I was using Mac Excel 2011
> to
> > >>> change the chart type.
> > >>>
> > >>> Thanks,
> > >>> Ken
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <
> > apache@gagravarr.org
> > >>> >
> > >>> wrote:
> > >>>
> > >>>
> > >>> On Sat, 9 Jan 2016, Ken Hausam wrote:
> > >>>
> > >>> I am using XSSFChart and associated classes to create a line chart
> > using
> > >>>> Apache POI. Works great! Thanks. My question is, is there an easy
> way
> > to
> > >>>> change the chart type programmatically from a line chart to a
> stacked
> > >>>> bar chart? I looked quickly at the CTChart class and associated CT
> > >>>> classes and noticed that the various chart types had their own
> class.
> > >>>> This makes me think that it's not as easy as just flipping a chart
> > type
> > >>>> attribute somewhere, but figured it couldn't hurt to ask.
> > >>>>
> > >>>
> > >>> I haven't looked at the chart stuff recently, so I can't answer off
> the
> > >>> top of my head. What I'd suggest you do is firstly create a simple
> file
> > >>> in
> > >>> Excel, with one sheet, with a few data points, and one style of
> chart.
> > >>> Save that. Next, change the type, and save-as that. Next, unzip both
> > >>> .xlsx
> > >>> files (rename to .zip and unpack). Now, compare the xml, especially
> for
> > >>> sheets and charts, and see what differs. Post a summary of that, and
> > >>> we'll
> > >>> help if we can!
> > >>>
> > >>> Nick
> > >>>
> > >>> ---------------------------------------------------------------------
> > >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > >>> For additional commands, e-mail: user-help@poi.apache.org
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> ---------------------------------------------------------------------
> > >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > >>> For additional commands, e-mail: user-help@poi.apache.org
> > >>>
> > >>>
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > > For additional commands, e-mail: user-help@poi.apache.org
> > >
> > >
> >
>
Re: Change chart type programmatically?
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Sounds nice!
Java-Dev-code usually resides in src/java/o.a.p.dev, but as your stuff it
is written in Python, it would probably make sense to put it under
"contrib/python" for now to not add more confusion to the list of
directories under /src directly.
Dominik.
On Fri, Jan 15, 2016 at 5:10 PM, Javen O'Neal <ja...@gmail.com> wrote:
> I wrote some python code that accepts two OOXML files, unzips them
> (zipfile), compares the zip structures, then compares file contents for
> matching files (difflib), then outputs the diff.
>
> Would this be helpful to the POI project, and where would I commit it?
> On Jan 15, 2016 3:58 AM, "Dominik Stadler" <do...@gmx.at> wrote:
>
> > FYI, we also have a helper application OOXMLPrettyPrint as part of the
> POI
> > source code which will take a .xlsx/.docx/.pptx/... file and produce a
> > pretty-printed one (which as Dave stated might not have exactly the same
> > content any more!). If you run this on both files you can then better
> > compare these. Best if you combine this with a compare tool that can be
> > made aware that these files are actually ZIPs and allows to do in-ZIP
> > comparisons...
> >
> > Dominik.
> >
> > On Fri, Jan 15, 2016 at 9:27 AM, David Law <da...@apconsult.de>
> wrote:
> >
> > > Please note: this may be ok for a comparison, but...
> > > XML DOES NOT NORMALLY CONTAIN NEWLINES
> > > They result in spurious Text nodes.
> > > (which most products simply ignore because they are so common)
> > >
> > > Javen O'Neals suggestion to convert to canonical is a much better idea.
> > >
> > > Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
> > > <html
> > > ><body bgcolor="#ffff00"
> > > ><h1>Hello world!</h1
> > > ><p
> > > ><a href="https://poi.apache.org/">XML does not (normally)
> > > contain newlines</a
> > > ></p
> > > ><img src="Logo.png" alt="Smiley face" border="1"
> > > /></body
> > > ></html>
> > >
> > > P.S. the above is valid XML but NOT Valid XHTML :-)
> > >
> > > All the best,
> > > DaveLaw
> > >
> > >
> > > On 15/01/2016 04:50, Javen O'Neal wrote:
> > >
> > >> Find-replace all ">" with ">\n", then run the diff. Alternatively,
> use a
> > >> text editor with HTML/XML pretty printing.
> > >> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid>
> > wrote:
> > >>
> > >> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips
> of
> > >>> XML files.
> > >>>
> > >>> After changing chart type, only one file changed: the chart1.xml file
> > >>> location in the xl/charts/ directory. I've attached the before
> > (line.xml)
> > >>> and after (stackedbar.xml) files. The diff isn't enormous, but it
> > doesn't
> > >>> seem trivial either. I couldn't list the actual diff since the XML is
> > all
> > >>> on one line which makes the diff hard to view as a text diff.
> > >>>
> > >>> I already tried a very simple change: changing <c:lineChart> to
> > >>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
> > >>> val="stacked"/> . No luck. Excel complained with "file format is not
> > >>> valid"
> > >>> when I tried to open it.
> > >>>
> > >>> Don't know if it makes a difference, but I was using Mac Excel 2011
> to
> > >>> change the chart type.
> > >>>
> > >>> Thanks,
> > >>> Ken
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <
> > apache@gagravarr.org
> > >>> >
> > >>> wrote:
> > >>>
> > >>>
> > >>> On Sat, 9 Jan 2016, Ken Hausam wrote:
> > >>>
> > >>> I am using XSSFChart and associated classes to create a line chart
> > using
> > >>>> Apache POI. Works great! Thanks. My question is, is there an easy
> way
> > to
> > >>>> change the chart type programmatically from a line chart to a
> stacked
> > >>>> bar chart? I looked quickly at the CTChart class and associated CT
> > >>>> classes and noticed that the various chart types had their own
> class.
> > >>>> This makes me think that it's not as easy as just flipping a chart
> > type
> > >>>> attribute somewhere, but figured it couldn't hurt to ask.
> > >>>>
> > >>>
> > >>> I haven't looked at the chart stuff recently, so I can't answer off
> the
> > >>> top of my head. What I'd suggest you do is firstly create a simple
> file
> > >>> in
> > >>> Excel, with one sheet, with a few data points, and one style of
> chart.
> > >>> Save that. Next, change the type, and save-as that. Next, unzip both
> > >>> .xlsx
> > >>> files (rename to .zip and unpack). Now, compare the xml, especially
> for
> > >>> sheets and charts, and see what differs. Post a summary of that, and
> > >>> we'll
> > >>> help if we can!
> > >>>
> > >>> Nick
> > >>>
> > >>> ---------------------------------------------------------------------
> > >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > >>> For additional commands, e-mail: user-help@poi.apache.org
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>
> > >>> ---------------------------------------------------------------------
> > >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > >>> For additional commands, e-mail: user-help@poi.apache.org
> > >>>
> > >>>
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > > For additional commands, e-mail: user-help@poi.apache.org
> > >
> > >
> >
>
Re: Change chart type programmatically?
Posted by Javen O'Neal <ja...@gmail.com>.
I wrote some python code that accepts two OOXML files, unzips them
(zipfile), compares the zip structures, then compares file contents for
matching files (difflib), then outputs the diff.
Would this be helpful to the POI project, and where would I commit it?
On Jan 15, 2016 3:58 AM, "Dominik Stadler" <do...@gmx.at> wrote:
> FYI, we also have a helper application OOXMLPrettyPrint as part of the POI
> source code which will take a .xlsx/.docx/.pptx/... file and produce a
> pretty-printed one (which as Dave stated might not have exactly the same
> content any more!). If you run this on both files you can then better
> compare these. Best if you combine this with a compare tool that can be
> made aware that these files are actually ZIPs and allows to do in-ZIP
> comparisons...
>
> Dominik.
>
> On Fri, Jan 15, 2016 at 9:27 AM, David Law <da...@apconsult.de> wrote:
>
> > Please note: this may be ok for a comparison, but...
> > XML DOES NOT NORMALLY CONTAIN NEWLINES
> > They result in spurious Text nodes.
> > (which most products simply ignore because they are so common)
> >
> > Javen O'Neals suggestion to convert to canonical is a much better idea.
> >
> > Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
> > <html
> > ><body bgcolor="#ffff00"
> > ><h1>Hello world!</h1
> > ><p
> > ><a href="https://poi.apache.org/">XML does not (normally)
> > contain newlines</a
> > ></p
> > ><img src="Logo.png" alt="Smiley face" border="1"
> > /></body
> > ></html>
> >
> > P.S. the above is valid XML but NOT Valid XHTML :-)
> >
> > All the best,
> > DaveLaw
> >
> >
> > On 15/01/2016 04:50, Javen O'Neal wrote:
> >
> >> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
> >> text editor with HTML/XML pretty printing.
> >> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid>
> wrote:
> >>
> >> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
> >>> XML files.
> >>>
> >>> After changing chart type, only one file changed: the chart1.xml file
> >>> location in the xl/charts/ directory. I've attached the before
> (line.xml)
> >>> and after (stackedbar.xml) files. The diff isn't enormous, but it
> doesn't
> >>> seem trivial either. I couldn't list the actual diff since the XML is
> all
> >>> on one line which makes the diff hard to view as a text diff.
> >>>
> >>> I already tried a very simple change: changing <c:lineChart> to
> >>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
> >>> val="stacked"/> . No luck. Excel complained with "file format is not
> >>> valid"
> >>> when I tried to open it.
> >>>
> >>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
> >>> change the chart type.
> >>>
> >>> Thanks,
> >>> Ken
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <
> apache@gagravarr.org
> >>> >
> >>> wrote:
> >>>
> >>>
> >>> On Sat, 9 Jan 2016, Ken Hausam wrote:
> >>>
> >>> I am using XSSFChart and associated classes to create a line chart
> using
> >>>> Apache POI. Works great! Thanks. My question is, is there an easy way
> to
> >>>> change the chart type programmatically from a line chart to a stacked
> >>>> bar chart? I looked quickly at the CTChart class and associated CT
> >>>> classes and noticed that the various chart types had their own class.
> >>>> This makes me think that it's not as easy as just flipping a chart
> type
> >>>> attribute somewhere, but figured it couldn't hurt to ask.
> >>>>
> >>>
> >>> I haven't looked at the chart stuff recently, so I can't answer off the
> >>> top of my head. What I'd suggest you do is firstly create a simple file
> >>> in
> >>> Excel, with one sheet, with a few data points, and one style of chart.
> >>> Save that. Next, change the type, and save-as that. Next, unzip both
> >>> .xlsx
> >>> files (rename to .zip and unpack). Now, compare the xml, especially for
> >>> sheets and charts, and see what differs. Post a summary of that, and
> >>> we'll
> >>> help if we can!
> >>>
> >>> Nick
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> >>> For additional commands, e-mail: user-help@poi.apache.org
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> >>> For additional commands, e-mail: user-help@poi.apache.org
> >>>
> >>>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
>
Re: Change chart type programmatically?
Posted by Dominik Stadler <do...@gmx.at>.
FYI, we also have a helper application OOXMLPrettyPrint as part of the POI
source code which will take a .xlsx/.docx/.pptx/... file and produce a
pretty-printed one (which as Dave stated might not have exactly the same
content any more!). If you run this on both files you can then better
compare these. Best if you combine this with a compare tool that can be
made aware that these files are actually ZIPs and allows to do in-ZIP
comparisons...
Dominik.
On Fri, Jan 15, 2016 at 9:27 AM, David Law <da...@apconsult.de> wrote:
> Please note: this may be ok for a comparison, but...
> XML DOES NOT NORMALLY CONTAIN NEWLINES
> They result in spurious Text nodes.
> (which most products simply ignore because they are so common)
>
> Javen O'Neals suggestion to convert to canonical is a much better idea.
>
> Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
> <html
> ><body bgcolor="#ffff00"
> ><h1>Hello world!</h1
> ><p
> ><a href="https://poi.apache.org/">XML does not (normally)
> contain newlines</a
> ></p
> ><img src="Logo.png" alt="Smiley face" border="1"
> /></body
> ></html>
>
> P.S. the above is valid XML but NOT Valid XHTML :-)
>
> All the best,
> DaveLaw
>
>
> On 15/01/2016 04:50, Javen O'Neal wrote:
>
>> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
>> text editor with HTML/XML pretty printing.
>> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid> wrote:
>>
>> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
>>> XML files.
>>>
>>> After changing chart type, only one file changed: the chart1.xml file
>>> location in the xl/charts/ directory. I've attached the before (line.xml)
>>> and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't
>>> seem trivial either. I couldn't list the actual diff since the XML is all
>>> on one line which makes the diff hard to view as a text diff.
>>>
>>> I already tried a very simple change: changing <c:lineChart> to
>>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
>>> val="stacked"/> . No luck. Excel complained with "file format is not
>>> valid"
>>> when I tried to open it.
>>>
>>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
>>> change the chart type.
>>>
>>> Thanks,
>>> Ken
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <apache@gagravarr.org
>>> >
>>> wrote:
>>>
>>>
>>> On Sat, 9 Jan 2016, Ken Hausam wrote:
>>>
>>> I am using XSSFChart and associated classes to create a line chart using
>>>> Apache POI. Works great! Thanks. My question is, is there an easy way to
>>>> change the chart type programmatically from a line chart to a stacked
>>>> bar chart? I looked quickly at the CTChart class and associated CT
>>>> classes and noticed that the various chart types had their own class.
>>>> This makes me think that it's not as easy as just flipping a chart type
>>>> attribute somewhere, but figured it couldn't hurt to ask.
>>>>
>>>
>>> I haven't looked at the chart stuff recently, so I can't answer off the
>>> top of my head. What I'd suggest you do is firstly create a simple file
>>> in
>>> Excel, with one sheet, with a few data points, and one style of chart.
>>> Save that. Next, change the type, and save-as that. Next, unzip both
>>> .xlsx
>>> files (rename to .zip and unpack). Now, compare the xml, especially for
>>> sheets and charts, and see what differs. Post a summary of that, and
>>> we'll
>>> help if we can!
>>>
>>> Nick
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Change chart type programmatically?
Posted by David Law <da...@apconsult.de>.
Please note: this may be ok for a comparison, but...
XML DOES NOT NORMALLY CONTAIN NEWLINES
They result in spurious Text nodes.
(which most products simply ignore because they are so common)
Javen O'Neals suggestion to convert to canonical is a much better idea.
Workaround: a newline BEFORE the tag-closing '>' (and '/>' ):
<html
><body bgcolor="#ffff00"
><h1>Hello world!</h1
><p
><a href="https://poi.apache.org/">XML does not (normally)
contain newlines</a
></p
><img src="Logo.png" alt="Smiley face" border="1"
/></body
></html>
P.S. the above is valid XML but NOT Valid XHTML :-)
All the best,
DaveLaw
On 15/01/2016 04:50, Javen O'Neal wrote:
> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
> text editor with HTML/XML pretty printing.
> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid> wrote:
>
>> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
>> XML files.
>>
>> After changing chart type, only one file changed: the chart1.xml file
>> location in the xl/charts/ directory. I've attached the before (line.xml)
>> and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't
>> seem trivial either. I couldn't list the actual diff since the XML is all
>> on one line which makes the diff hard to view as a text diff.
>>
>> I already tried a very simple change: changing <c:lineChart> to
>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
>> val="stacked"/> . No luck. Excel complained with "file format is not valid"
>> when I tried to open it.
>>
>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
>> change the chart type.
>>
>> Thanks,
>> Ken
>>
>>
>>
>>
>>
>>
>>
>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org>
>> wrote:
>>
>>
>> On Sat, 9 Jan 2016, Ken Hausam wrote:
>>
>>> I am using XSSFChart and associated classes to create a line chart using
>>> Apache POI. Works great! Thanks. My question is, is there an easy way to
>>> change the chart type programmatically from a line chart to a stacked
>>> bar chart? I looked quickly at the CTChart class and associated CT
>>> classes and noticed that the various chart types had their own class.
>>> This makes me think that it's not as easy as just flipping a chart type
>>> attribute somewhere, but figured it couldn't hurt to ask.
>>
>> I haven't looked at the chart stuff recently, so I can't answer off the
>> top of my head. What I'd suggest you do is firstly create a simple file in
>> Excel, with one sheet, with a few data points, and one style of chart.
>> Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
>> files (rename to .zip and unpack). Now, compare the xml, especially for
>> sheets and charts, and see what differs. Post a summary of that, and we'll
>> help if we can!
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Javen O'Neal <ja...@gmail.com>.
If you get more into diffing these files, convert to canonical XML
with xmllint and then use a regular line-based text diff tool[1]
$ xmllint --c14n one.xml > 1.xml
$ xmllint --c14n two.xml > 2.xml
$ diff -u 1.xml 2.xml
[1] http://superuser.com/a/81036/303069
On Thu, Jan 14, 2016 at 10:30 PM, Javen O'Neal <ja...@gmail.com> wrote:
> Apparently changing chart types is a lot more complicated than
> replacing <c:lineChart> with <c:barChart> and <c:grouping
> val="standard"/> with <c:grouping val="stacked"/>.
> Excel might be adding, removing, and modifying more tags than
> absolutely necessary when changing the chart types (for example, if
> <c:invertIfNegative val="0"/> is not a required tag, it wouldn't need
> to be added). The schema might tell us if some tags are optional, and
> therefore allow us to ignore most of this diff.
>
> OpenOffice, LibreOffice, and other applications might not generate
> quite as large a diff as Excel did here. Don't invest too much time
> reading the diff below without seeing if a different application can
> give us a smaller set of changes to look for. If other applications
> give the same length diff, then having POI support changing chart
> types might not be a one-weekend project :)
>
> line.xml http://pastebin.com/B8bDCByq
> stackedbar.xml http://pastebin.com/ykkGMANE
> short diff http://pastebin.com/dxh7DPwR
> full diff http://pastebin.com/diff/ykkGMANE
>
> Formatted using http://www.xmlformatter.net/
>
> On Thu, Jan 14, 2016 at 7:50 PM, Javen O'Neal <ja...@gmail.com> wrote:
>> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
>> text editor with HTML/XML pretty printing.
>>
>> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid> wrote:
>>>
>>> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
>>> XML files.
>>>
>>> After changing chart type, only one file changed: the chart1.xml file
>>> location in the xl/charts/ directory. I've attached the before (line.xml)
>>> and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't
>>> seem trivial either. I couldn't list the actual diff since the XML is all on
>>> one line which makes the diff hard to view as a text diff.
>>>
>>> I already tried a very simple change: changing <c:lineChart> to
>>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
>>> val="stacked"/> . No luck. Excel complained with "file format is not valid"
>>> when I tried to open it.
>>>
>>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
>>> change the chart type.
>>>
>>> Thanks,
>>> Ken
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org>
>>> wrote:
>>>
>>>
>>> On Sat, 9 Jan 2016, Ken Hausam wrote:
>>>
>>> > I am using XSSFChart and associated classes to create a line chart using
>>> > Apache POI. Works great! Thanks. My question is, is there an easy way to
>>> > change the chart type programmatically from a line chart to a stacked
>>> > bar chart? I looked quickly at the CTChart class and associated CT
>>> > classes and noticed that the various chart types had their own class.
>>> > This makes me think that it's not as easy as just flipping a chart type
>>> > attribute somewhere, but figured it couldn't hurt to ask.
>>>
>>>
>>> I haven't looked at the chart stuff recently, so I can't answer off the
>>> top of my head. What I'd suggest you do is firstly create a simple file in
>>> Excel, with one sheet, with a few data points, and one style of chart.
>>> Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
>>> files (rename to .zip and unpack). Now, compare the xml, especially for
>>> sheets and charts, and see what differs. Post a summary of that, and we'll
>>> help if we can!
>>>
>>> Nick
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Javen O'Neal <ja...@gmail.com>.
Apparently changing chart types is a lot more complicated than
replacing <c:lineChart> with <c:barChart> and <c:grouping
val="standard"/> with <c:grouping val="stacked"/>.
Excel might be adding, removing, and modifying more tags than
absolutely necessary when changing the chart types (for example, if
<c:invertIfNegative val="0"/> is not a required tag, it wouldn't need
to be added). The schema might tell us if some tags are optional, and
therefore allow us to ignore most of this diff.
OpenOffice, LibreOffice, and other applications might not generate
quite as large a diff as Excel did here. Don't invest too much time
reading the diff below without seeing if a different application can
give us a smaller set of changes to look for. If other applications
give the same length diff, then having POI support changing chart
types might not be a one-weekend project :)
line.xml http://pastebin.com/B8bDCByq
stackedbar.xml http://pastebin.com/ykkGMANE
short diff http://pastebin.com/dxh7DPwR
full diff http://pastebin.com/diff/ykkGMANE
Formatted using http://www.xmlformatter.net/
On Thu, Jan 14, 2016 at 7:50 PM, Javen O'Neal <ja...@gmail.com> wrote:
> Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
> text editor with HTML/XML pretty printing.
>
> On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid> wrote:
>>
>> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
>> XML files.
>>
>> After changing chart type, only one file changed: the chart1.xml file
>> location in the xl/charts/ directory. I've attached the before (line.xml)
>> and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't
>> seem trivial either. I couldn't list the actual diff since the XML is all on
>> one line which makes the diff hard to view as a text diff.
>>
>> I already tried a very simple change: changing <c:lineChart> to
>> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
>> val="stacked"/> . No luck. Excel complained with "file format is not valid"
>> when I tried to open it.
>>
>> Don't know if it makes a difference, but I was using Mac Excel 2011 to
>> change the chart type.
>>
>> Thanks,
>> Ken
>>
>>
>>
>>
>>
>>
>>
>> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org>
>> wrote:
>>
>>
>> On Sat, 9 Jan 2016, Ken Hausam wrote:
>>
>> > I am using XSSFChart and associated classes to create a line chart using
>> > Apache POI. Works great! Thanks. My question is, is there an easy way to
>> > change the chart type programmatically from a line chart to a stacked
>> > bar chart? I looked quickly at the CTChart class and associated CT
>> > classes and noticed that the various chart types had their own class.
>> > This makes me think that it's not as easy as just flipping a chart type
>> > attribute somewhere, but figured it couldn't hurt to ask.
>>
>>
>> I haven't looked at the chart stuff recently, so I can't answer off the
>> top of my head. What I'd suggest you do is firstly create a simple file in
>> Excel, with one sheet, with a few data points, and one style of chart.
>> Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
>> files (rename to .zip and unpack). Now, compare the xml, especially for
>> sheets and charts, and see what differs. Post a summary of that, and we'll
>> help if we can!
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Javen O'Neal <ja...@gmail.com>.
Find-replace all ">" with ">\n", then run the diff. Alternatively, use a
text editor with HTML/XML pretty printing.
On Jan 14, 2016 19:40, "Ken Hausam" <ka...@yahoo.com.invalid> wrote:
> Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of
> XML files.
>
> After changing chart type, only one file changed: the chart1.xml file
> location in the xl/charts/ directory. I've attached the before (line.xml)
> and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't
> seem trivial either. I couldn't list the actual diff since the XML is all
> on one line which makes the diff hard to view as a text diff.
>
> I already tried a very simple change: changing <c:lineChart> to
> <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping
> val="stacked"/> . No luck. Excel complained with "file format is not valid"
> when I tried to open it.
>
> Don't know if it makes a difference, but I was using Mac Excel 2011 to
> change the chart type.
>
> Thanks,
> Ken
>
>
>
>
>
>
>
> On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org>
> wrote:
>
>
> On Sat, 9 Jan 2016, Ken Hausam wrote:
>
> > I am using XSSFChart and associated classes to create a line chart using
> > Apache POI. Works great! Thanks. My question is, is there an easy way to
> > change the chart type programmatically from a line chart to a stacked
> > bar chart? I looked quickly at the CTChart class and associated CT
> > classes and noticed that the various chart types had their own class.
> > This makes me think that it's not as easy as just flipping a chart type
> > attribute somewhere, but figured it couldn't hurt to ask.
>
>
> I haven't looked at the chart stuff recently, so I can't answer off the
> top of my head. What I'd suggest you do is firstly create a simple file in
> Excel, with one sheet, with a few data points, and one style of chart.
> Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
> files (rename to .zip and unpack). Now, compare the xml, especially for
> sheets and charts, and see what differs. Post a summary of that, and we'll
> help if we can!
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Change chart type programmatically?
Posted by Ken Hausam <ka...@yahoo.com.INVALID>.
Thanks Nick. I'm a novice here. Didn't even know that .xlsx were zips of XML files.
After changing chart type, only one file changed: the chart1.xml file location in the xl/charts/ directory. I've attached the before (line.xml) and after (stackedbar.xml) files. The diff isn't enormous, but it doesn't seem trivial either. I couldn't list the actual diff since the XML is all on one line which makes the diff hard to view as a text diff.
I already tried a very simple change: changing <c:lineChart> to <c:barChart> and changing <c:grouping val="standard"/> to <c:grouping val="stacked"/> . No luck. Excel complained with "file format is not valid" when I tried to open it.
Don't know if it makes a difference, but I was using Mac Excel 2011 to change the chart type.
Thanks,Ken
On Wednesday, January 13, 2016 2:47 AM, Nick Burch <ap...@gagravarr.org> wrote:
On Sat, 9 Jan 2016, Ken Hausam wrote:
> I am using XSSFChart and associated classes to create a line chart using
> Apache POI. Works great! Thanks. My question is, is there an easy way to
> change the chart type programmatically from a line chart to a stacked
> bar chart? I looked quickly at the CTChart class and associated CT
> classes and noticed that the various chart types had their own class.
> This makes me think that it's not as easy as just flipping a chart type
> attribute somewhere, but figured it couldn't hurt to ask.
I haven't looked at the chart stuff recently, so I can't answer off the
top of my head. What I'd suggest you do is firstly create a simple file in
Excel, with one sheet, with a few data points, and one style of chart.
Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
files (rename to .zip and unpack). Now, compare the xml, especially for
sheets and charts, and see what differs. Post a summary of that, and we'll
help if we can!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Change chart type programmatically?
Posted by Nick Burch <ap...@gagravarr.org>.
On Sat, 9 Jan 2016, Ken Hausam wrote:
> I am using XSSFChart and associated classes to create a line chart using
> Apache POI. Works great! Thanks. My question is, is there an easy way to
> change the chart type programmatically from a line chart to a stacked
> bar chart? I looked quickly at the CTChart class and associated CT
> classes and noticed that the various chart types had their own class.
> This makes me think that it's not as easy as just flipping a chart type
> attribute somewhere, but figured it couldn't hurt to ask.
I haven't looked at the chart stuff recently, so I can't answer off the
top of my head. What I'd suggest you do is firstly create a simple file in
Excel, with one sheet, with a few data points, and one style of chart.
Save that. Next, change the type, and save-as that. Next, unzip both .xlsx
files (rename to .zip and unpack). Now, compare the xml, especially for
sheets and charts, and see what differs. Post a summary of that, and we'll
help if we can!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org