You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by tom corcoran <os...@gmail.com> on 2008/05/29 18:42:57 UTC
Convert .xls to ascii?
I have about 10 different .xls file types. Each file has the column names in
row 1 and has multiple sheets with the same data for different locations. I
have hundreds of each file type. My solution need to be able to work
dynamically as new files come online.
I am looking at parsing the first line to create a mysql schema and then
importing the xls in java using mysql's load infile. I have used this to
load ascii files without an issue. It seems I can use
org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way I
can use this package to convert to an ascii file which I will easily be able
to load into MySQl? The delimiter used, be it comma or otherwise is
irrelevant as load infile can cope with an ascii delimiter.
Thanks for advice.
Tom.
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
In case anyone is interested I worked out this. The first lines of the
outputed CSV are now:
SheetName0=name1
SheetName1=name2
SheetName2=name3
etc.
I did this by adding to processRecord:
*case* BoundSheetRecord.*sid*:
BoundSheetRecord bsrec = (BoundSheetRecord) record;
sheetNumber++;
sheetName = bsrec.getSheetname();
*break*;
Tom.
On Tue, Jun 3, 2008 at 3:32 PM, tom corcoran <os...@gmail.com> wrote:
> Hi Andy,
>
> That was the trick! I took this file and added it so my packge, which was
> the only change I have made so far to it. I ould ot find it on any of the
> jars for 3.0.2, though there seemed to be suggesioons it should be in the
> scratchpad jar.
>
> It work great. I am now trying to figure out how to export the sheetname as
> well as this is vital for me.I'm not sure if I can tweak the code to do a
> setIncludeSheetNames...
>
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
Hi Andy,
That was the trick! I took this file and added it so my packge, which was
the only change I have made so far to it. I ould ot find it on any of the
jars for 3.0.2, though there seemed to be suggesioons it should be in the
scratchpad jar.
It work great. I am now trying to figure out how to export the sheetname as
well as this is vital for me.I'm not sure if I can tweak the code to do a
setIncludeSheetNames...
My working (filenames/exceptionhandling removed for brievity) code is as
follows:
InputStream inputStream = *new* FileInputStream(excelFile);
POIFSFileSystem fileSystem = *new* POIFSFileSystem(inputStream);
FileOutputStream outFile = *new* FileOutputStream(cvsFile);
PrintStream printStream = *new* PrintStream(outFile);
XLS2CSVmra xls2csv = *new* XLS2CSVmra(fileSystem, printStream, -1);
xls2csv.process();
Any ideas on how I might tweak XLS2CSVmra to export the sheet names as well
are appreciated.
Tom.
On Thu, May 29, 2008 at 7:32 PM, Andy Chien <hs...@utoronto.ca>
wrote:
> Ops
>
> I just figured it out now, that was the example. Code located in the
> following:
>
>
> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
>
>
> Cheers,
>
> Andy
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Convert .xls to ascii?
Posted by Andy Chien <hs...@utoronto.ca>.
Ops
I just figured it out now, that was the example. Code located in the
following:
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java
Cheers,
Andy
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
My bad. I needed to have poi-3.1-beta2-20080530.jar in my path and include:
*
import* org.apache.poi.hssf.extractor.ExcelExtractor
*import* org.apache.poi.hssf.usermodel.HSSFWorkbook
*import* org.apache.poi.poifs.filesystem.POIFSFileSystem
On Fri, May 30, 2008 at 5:39 PM, tom corcoran <os...@gmail.com> wrote:
> The text extraction code at that link says:
>
> InputStream inp = new FileInputStream("workbook.xls");
> HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
> ExcelExtractor extractor = new ExcelExtractor(wb);
> extractor.setFormulasNotResults(true);
> extractor.setIncludeSheetNames(false);
> String text = extractor.getText();
>
> I cannot find HSSFWorkbook in poi-3.1-beta2-20080530.jar. What am I
> missing?
>
> Also, Doug mentopned that the HSSFRow defines a CellIterator this will
> allow you to iterate through
> the row and get each element. can anyone point me to sample code.
>
> If I can get this worked out maybe we could post the code to a poi snippet
> directory or something. Why should everyone need to reinvent the wheeel
> every time to do a common task like. coverting a .xls to a flat file.
>
> Thanks, Tom.
>
> On Thu, May 29, 2008 at 7:28 PM, Andy Chien <hs...@utoronto.ca>
> wrote:
>
>> Hi Tom:
>>
>> Though I have not used this feature before, I am aware of the following
>> object. Perhaps it can help? I vaguely recall there were some example on the
>> apache website but I can not seem to find it anymore.
>>
>>
>> http://poi.apache.org/apidocs/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.html
>>
>> http://poi.apache.org/hssf/quick-guide.html#TextExtraction
>>
>> Hope this helps you.
>>
>>
>> Cheers,
>>
>> Andy
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
The text extraction code at that link says:
InputStream inp = new FileInputStream("workbook.xls");
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
ExcelExtractor extractor = new ExcelExtractor(wb);
extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
String text = extractor.getText();
I cannot find HSSFWorkbook in poi-3.1-beta2-20080530.jar. What am I missing?
Also, Doug mentopned that the HSSFRow defines a CellIterator this will allow
you to iterate through
the row and get each element. can anyone point me to sample code.
If I can get this worked out maybe we could post the code to a poi snippet
directory or something. Why should everyone need to reinvent the wheeel
every time to do a common task like. coverting a .xls to a flat file.
Thanks, Tom.
On Thu, May 29, 2008 at 7:28 PM, Andy Chien <hs...@utoronto.ca>
wrote:
> Hi Tom:
>
> Though I have not used this feature before, I am aware of the following
> object. Perhaps it can help? I vaguely recall there were some example on the
> apache website but I can not seem to find it anymore.
>
>
> http://poi.apache.org/apidocs/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.html
>
> http://poi.apache.org/hssf/quick-guide.html#TextExtraction
>
> Hope this helps you.
>
>
> Cheers,
>
> Andy
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Convert .xls to ascii?
Posted by Andy Chien <hs...@utoronto.ca>.
Hi Tom:
Though I have not used this feature before, I am aware of the following
object. Perhaps it can help? I vaguely recall there were some example on
the apache website but I can not seem to find it anymore.
http://poi.apache.org/apidocs/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.html
http://poi.apache.org/hssf/quick-guide.html#TextExtraction
Hope this helps you.
Cheers,
Andy
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Wrong result with formulas including $-sign using evaluator.evaluate(cell)?
Posted by Tim Wilkins <ti...@mediatel.co.uk>.
Have been looking for something like this, but the site appears to be
down :(
Peter Arrenbrecht wrote:
> On Fri, May 30, 2008 at 8:57 AM, Rudolf Schmidt <we...@gmx.de> wrote:
>
>> Hi,
>>
>> evaluator.evaluate(cell) gives me wrong results, if it evaluates
>> cellformulas, that have $-signs in it or refer to cells which have formulas
>> with $, e.g. "C$12 * a3". Removing the $ helps, but is not always possible.
>>
>> Any suggestions?
>>
>
> Shameless plug: http://formulacompiler.org/ (GPLed Excel to JVM byte
> code compiler)
> -parren
>
> ---------------------------------------------------------------------
> 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: Wrong result with formulas including $-sign using evaluator.evaluate(cell)?
Posted by Peter Arrenbrecht <pe...@gmail.com>.
On Fri, May 30, 2008 at 8:57 AM, Rudolf Schmidt <we...@gmx.de> wrote:
> Hi,
>
> evaluator.evaluate(cell) gives me wrong results, if it evaluates
> cellformulas, that have $-signs in it or refer to cells which have formulas
> with $, e.g. "C$12 * a3". Removing the $ helps, but is not always possible.
>
> Any suggestions?
Shameless plug: http://formulacompiler.org/ (GPLed Excel to JVM byte
code compiler)
-parren
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Wrong result with formulas including $-sign using evaluator.evaluate(cell)?
Posted by Rudolf Schmidt <we...@gmx.de>.
Hi,
evaluator.evaluate(cell) gives me wrong results, if it evaluates
cellformulas, that have $-signs in it or refer to cells which have formulas
with $, e.g. "C$12 * a3". Removing the $ helps, but is not always possible.
Any suggestions?
Snippet:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
evaluator.setCurrentRow(row);
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
return cellValue.getNumberValue();
Regards, Rudolf
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Convert .xls to ascii?
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 30 May 2008, tom corcoran wrote:
> I really wish POI was available as a jar file :-( I have spent the last
> 3 hours trying to build it without success.
It's normally worth a quick search of the archives. That would've pointed
you at the nightly builds:
http://encore.torchbox.com/poi-svn-build/
Which will give you the latest jar files, without needing to set up ant
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
Sorry I was running the dist, which failed. i see where the jar's have gone
to build\dist....perhaps the documentation could be extended to point that
out for boneheads like me.....
On Fri, May 30, 2008 at 5:22 PM, tom corcoran <os...@gmail.com> wrote:
> I really wish POI was available as a jar file :-( I have spent the last 3
> hours trying to build it without success. It keeps saying that JUnit is not
> available and to include the JAR file in my classpath. I have followed
> http://poi.apache.org/howtobuild.html a load of time now and many reboots
> later I get the same message :-(
>
> Tom.
>
> On Fri, May 30, 2008 at 4:54 AM, Doug Stevens <dm...@bendcable.com>
> wrote:
>
>> Tom,
>>
>> The HSSFRow defines a CellIterator this will allow you to iterate through
>> the row and get each element. You can then write the value of the cell out
>> to a new File, just create a comma delimeted string for the row.
>>
>> Are you using Excel 97 or ExcelML formatted sheets?
>>
>> Doug Stevens
>> (541) 788-3221
>>
>>
>>
>> -----Original Message-----
>> From: tom corcoran [mailto:osssoftware@gmail.com]
>> Sent: Thursday, May 29, 2008 9:43 AM
>> To: user@poi.apache.org
>> Subject: Convert .xls to ascii?
>>
>>
>> I have about 10 different .xls file types. Each file has the column names
>> in
>> row 1 and has multiple sheets with the same data for different locations.
>> I
>> have hundreds of each file type. My solution need to be able to work
>> dynamically as new files come online.
>>
>> I am looking at parsing the first line to create a mysql schema and then
>> importing the xls in java using mysql's load infile. I have used this to
>> load ascii files without an issue. It seems I can use
>> org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way
>> I
>> can use this package to convert to an ascii file which I will easily be
>> able
>> to load into MySQl? The delimiter used, be it comma or otherwise is
>> irrelevant as load infile can cope with an ascii delimiter.
>>
>> Thanks for advice.
>>
>> Tom.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
I really wish POI was available as a jar file :-( I have spent the last 3
hours trying to build it without success. It keeps saying that JUnit is not
available and to include the JAR file in my classpath. I have followed
http://poi.apache.org/howtobuild.html a load of time now and many reboots
later I get the same message :-(
Tom.
On Fri, May 30, 2008 at 4:54 AM, Doug Stevens <dm...@bendcable.com> wrote:
> Tom,
>
> The HSSFRow defines a CellIterator this will allow you to iterate through
> the row and get each element. You can then write the value of the cell out
> to a new File, just create a comma delimeted string for the row.
>
> Are you using Excel 97 or ExcelML formatted sheets?
>
> Doug Stevens
> (541) 788-3221
>
>
>
> -----Original Message-----
> From: tom corcoran [mailto:osssoftware@gmail.com]
> Sent: Thursday, May 29, 2008 9:43 AM
> To: user@poi.apache.org
> Subject: Convert .xls to ascii?
>
>
> I have about 10 different .xls file types. Each file has the column names
> in
> row 1 and has multiple sheets with the same data for different locations. I
> have hundreds of each file type. My solution need to be able to work
> dynamically as new files come online.
>
> I am looking at parsing the first line to create a mysql schema and then
> importing the xls in java using mysql's load infile. I have used this to
> load ascii files without an issue. It seems I can use
> org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way
> I
> can use this package to convert to an ascii file which I will easily be
> able
> to load into MySQl? The delimiter used, be it comma or otherwise is
> irrelevant as load infile can cope with an ascii delimiter.
>
> Thanks for advice.
>
> Tom.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Convert .xls to ascii?
Posted by Anthony Andrews <py...@yahoo.com>.
Tom
The most recent versions of the applications in Microsoft's Office suite have offered the option to save files in an 'open' XML based file format. That is the ExcelML that other posters have referred to.
tom corcoran <os...@gmail.com> wrote: Thanks Andy & Doug, will play with that today. I don't know what ExcelIML is
and could not find a reference on the web. I would imagine the spreadsheets
are 97/2003 but I have don't have control over their production.
On Fri, May 30, 2008 at 4:54 AM, Doug Stevens wrote:
> Tom,
>
> The HSSFRow defines a CellIterator this will allow you to iterate through
> the row and get each element. You can then write the value of the cell out
> to a new File, just create a comma delimeted string for the row.
>
> Are you using Excel 97 or ExcelML formatted sheets?
>
> Doug Stevens
> (541) 788-3221
>
>
>
> -----Original Message-----
> From: tom corcoran [mailto:osssoftware@gmail.com]
> Sent: Thursday, May 29, 2008 9:43 AM
> To: user@poi.apache.org
> Subject: Convert .xls to ascii?
>
>
> I have about 10 different .xls file types. Each file has the column names
> in
> row 1 and has multiple sheets with the same data for different locations. I
> have hundreds of each file type. My solution need to be able to work
> dynamically as new files come online.
>
> I am looking at parsing the first line to create a mysql schema and then
> importing the xls in java using mysql's load infile. I have used this to
> load ascii files without an issue. It seems I can use
> org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way
> I
> can use this package to convert to an ascii file which I will easily be
> able
> to load into MySQl? The delimiter used, be it comma or otherwise is
> irrelevant as load infile can cope with an ascii delimiter.
>
> Thanks for advice.
>
> Tom.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Convert .xls to ascii?
Posted by tom corcoran <os...@gmail.com>.
Thanks Andy & Doug, will play with that today. I don't know what ExcelIML is
and could not find a reference on the web. I would imagine the spreadsheets
are 97/2003 but I have don't have control over their production.
On Fri, May 30, 2008 at 4:54 AM, Doug Stevens <dm...@bendcable.com> wrote:
> Tom,
>
> The HSSFRow defines a CellIterator this will allow you to iterate through
> the row and get each element. You can then write the value of the cell out
> to a new File, just create a comma delimeted string for the row.
>
> Are you using Excel 97 or ExcelML formatted sheets?
>
> Doug Stevens
> (541) 788-3221
>
>
>
> -----Original Message-----
> From: tom corcoran [mailto:osssoftware@gmail.com]
> Sent: Thursday, May 29, 2008 9:43 AM
> To: user@poi.apache.org
> Subject: Convert .xls to ascii?
>
>
> I have about 10 different .xls file types. Each file has the column names
> in
> row 1 and has multiple sheets with the same data for different locations. I
> have hundreds of each file type. My solution need to be able to work
> dynamically as new files come online.
>
> I am looking at parsing the first line to create a mysql schema and then
> importing the xls in java using mysql's load infile. I have used this to
> load ascii files without an issue. It seems I can use
> org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way
> I
> can use this package to convert to an ascii file which I will easily be
> able
> to load into MySQl? The delimiter used, be it comma or otherwise is
> irrelevant as load infile can cope with an ascii delimiter.
>
> Thanks for advice.
>
> Tom.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
RE: Convert .xls to ascii?
Posted by Doug Stevens <dm...@bendcable.com>.
Tom,
The HSSFRow defines a CellIterator this will allow you to iterate through
the row and get each element. You can then write the value of the cell out
to a new File, just create a comma delimeted string for the row.
Are you using Excel 97 or ExcelML formatted sheets?
Doug Stevens
(541) 788-3221
-----Original Message-----
From: tom corcoran [mailto:osssoftware@gmail.com]
Sent: Thursday, May 29, 2008 9:43 AM
To: user@poi.apache.org
Subject: Convert .xls to ascii?
I have about 10 different .xls file types. Each file has the column names in
row 1 and has multiple sheets with the same data for different locations. I
have hundreds of each file type. My solution need to be able to work
dynamically as new files come online.
I am looking at parsing the first line to create a mysql schema and then
importing the xls in java using mysql's load infile. I have used this to
load ascii files without an issue. It seems I can use
org.apache.poi.hssf.eventusermodel to read the xls files. Is there any way I
can use this package to convert to an ascii file which I will easily be able
to load into MySQl? The delimiter used, be it comma or otherwise is
irrelevant as load infile can cope with an ascii delimiter.
Thanks for advice.
Tom.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org