Re: Java API for Excel

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Dominik Stadler
Hi,

Can you share an anonymized sample-csv-file? Apache POI likely does not
have support for this itself, but if it is a text-format, it should be
fairly easy to iterate over the rows/cells and produce the text-format
yourself if we can figure out how Excel formats this CSV in some special
way to be able to later detect the format again.

BTW, please post such questions to the user-list, not to developers
directly, this prevents others from participating in the documentation.

Dominik.

On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
wrote:

> Hi,
> My name is Siva Sripada and I am a physician living in Michigan. I am
> writing a program to upload a list of patients (first name, last name,
> birthdate) to a website that will provide me information on a patient’s
> previous prescription history over the last 1 year.
> I am using the Apache API for Java for Excel and the program works
> great!!! but….The website requires that the file be in *Excel’s* CSV format
> only. So for example the website will not take a text file with
> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
> simply renamed with a .cvs file. Currently I am opening the .xlsx file
> saved with my program thru Excel and re-saving it with Excel’s CSV format
> - which works but does not leave me satisfied.
>
> Excel’s CSV files have the unique(?) property of being able to be opened
> by both Excel and any text editor (don’t know if that means anything to
> you). Plus these files when opened in a text editor have comma delimiters
> plus a carriage return at the end of each line.
>
> My question is :
> Is there a way to change the formatting of the .xlsx file thru The Apache
> API so that it saves it as a “true” Excel CVS file?
>
> Any help would be appreciated.
> Thank you,
> Siva Sripada
>
Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Javen O'Neal-2
I'm guessing that an Excel CSV file is a regular CSV file that is written
in a specific dialect (quoted strings, newline character, delimiter, etc).

You could try transforming your CSV file into an Excel dialect if you knew
what this dialect was (I'm assuming the receiving website has a spec and a
file validator, either of which could be used to figure out why vanilla CSV
files are rejected).

You could try using Python's csv module to write your data in the Excel
dialect, which might be easier than a Java project.

You could also work with the website authors for a better way to exchange
data. If you need some teeth with your request, mention how their system
might have issues demonstrating compliance with HIPAA.


On Apr 21, 2017 11:04 AM, "Dominik Stadler" <[hidden email]> wrote:

Hi,

Can you share an anonymized sample-csv-file? Apache POI likely does not
have support for this itself, but if it is a text-format, it should be
fairly easy to iterate over the rows/cells and produce the text-format
yourself if we can figure out how Excel formats this CSV in some special
way to be able to later detect the format again.

BTW, please post such questions to the user-list, not to developers
directly, this prevents others from participating in the documentation.

Dominik.

On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
wrote:

> Hi,
> My name is Siva Sripada and I am a physician living in Michigan. I am
> writing a program to upload a list of patients (first name, last name,
> birthdate) to a website that will provide me information on a patient’s
> previous prescription history over the last 1 year.
> I am using the Apache API for Java for Excel and the program works
> great!!! but….The website requires that the file be in *Excel’s* CSV
format

> only. So for example the website will not take a text file with
> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
> simply renamed with a .cvs file. Currently I am opening the .xlsx file
> saved with my program thru Excel and re-saving it with Excel’s CSV format
> - which works but does not leave me satisfied.
>
> Excel’s CSV files have the unique(?) property of being able to be opened
> by both Excel and any text editor (don’t know if that means anything to
> you). Plus these files when opened in a text editor have comma delimiters
> plus a carriage return at the end of each line.
>
> My question is :
> Is there a way to change the formatting of the .xlsx file thru The Apache
> API so that it saves it as a “true” Excel CVS file?
>
> Any help would be appreciated.
> Thank you,
> Siva Sripada
>
Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Nick Burch-8
Apache Commons CSV <https://commons.apache.org/proper/commons-csv/>
talks the Excel CSV variant (amongst others - it's configurable)

Nick

On 21/04/17 22:48, Javen O'Neal wrote:

> I'm guessing that an Excel CSV file is a regular CSV file that is written
> in a specific dialect (quoted strings, newline character, delimiter, etc).
>
> You could try transforming your CSV file into an Excel dialect if you knew
> what this dialect was (I'm assuming the receiving website has a spec and a
> file validator, either of which could be used to figure out why vanilla CSV
> files are rejected).
>
> You could try using Python's csv module to write your data in the Excel
> dialect, which might be easier than a Java project.
>
> You could also work with the website authors for a better way to exchange
> data. If you need some teeth with your request, mention how their system
> might have issues demonstrating compliance with HIPAA.
>
>
> On Apr 21, 2017 11:04 AM, "Dominik Stadler" <[hidden email]> wrote:
>
> Hi,
>
> Can you share an anonymized sample-csv-file? Apache POI likely does not
> have support for this itself, but if it is a text-format, it should be
> fairly easy to iterate over the rows/cells and produce the text-format
> yourself if we can figure out how Excel formats this CSV in some special
> way to be able to later detect the format again.
>
> BTW, please post such questions to the user-list, not to developers
> directly, this prevents others from participating in the documentation.
>
> Dominik.
>
> On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
> wrote:
>
>> Hi,
>> My name is Siva Sripada and I am a physician living in Michigan. I am
>> writing a program to upload a list of patients (first name, last name,
>> birthdate) to a website that will provide me information on a patient’s
>> previous prescription history over the last 1 year.
>> I am using the Apache API for Java for Excel and the program works
>> great!!! but….The website requires that the file be in *Excel’s* CSV
> format
>> only. So for example the website will not take a text file with
>> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
>> simply renamed with a .cvs file. Currently I am opening the .xlsx file
>> saved with my program thru Excel and re-saving it with Excel’s CSV format
>> - which works but does not leave me satisfied.
>>
>> Excel’s CSV files have the unique(?) property of being able to be opened
>> by both Excel and any text editor (don’t know if that means anything to
>> you). Plus these files when opened in a text editor have comma delimiters
>> plus a carriage return at the end of each line.
>>
>> My question is :
>> Is there a way to change the formatting of the .xlsx file thru The Apache
>> API so that it saves it as a “true” Excel CVS file?
>>
>> Any help would be appreciated.
>> Thank you,
>> Siva Sripada
>>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Javen O'Neal-2
In reply to this post by Javen O'Neal-2
http://stackoverflow.com/a/8125708/2683399

You could use Excel to save to an Excel CSV and try to imitate the output
with whatever application you write.

On Apr 21, 2017 14:48, "Javen O'Neal" <[hidden email]> wrote:

> I'm guessing that an Excel CSV file is a regular CSV file that is written
> in a specific dialect (quoted strings, newline character, delimiter, etc).
>
> You could try transforming your CSV file into an Excel dialect if you knew
> what this dialect was (I'm assuming the receiving website has a spec and a
> file validator, either of which could be used to figure out why vanilla CSV
> files are rejected).
>
> You could try using Python's csv module to write your data in the Excel
> dialect, which might be easier than a Java project.
>
> You could also work with the website authors for a better way to exchange
> data. If you need some teeth with your request, mention how their system
> might have issues demonstrating compliance with HIPAA.
>
>
> On Apr 21, 2017 11:04 AM, "Dominik Stadler" <[hidden email]>
> wrote:
>
> Hi,
>
> Can you share an anonymized sample-csv-file? Apache POI likely does not
> have support for this itself, but if it is a text-format, it should be
> fairly easy to iterate over the rows/cells and produce the text-format
> yourself if we can figure out how Excel formats this CSV in some special
> way to be able to later detect the format again.
>
> BTW, please post such questions to the user-list, not to developers
> directly, this prevents others from participating in the documentation.
>
> Dominik.
>
> On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
> wrote:
>
> > Hi,
> > My name is Siva Sripada and I am a physician living in Michigan. I am
> > writing a program to upload a list of patients (first name, last name,
> > birthdate) to a website that will provide me information on a patient’s
> > previous prescription history over the last 1 year.
> > I am using the Apache API for Java for Excel and the program works
> > great!!! but….The website requires that the file be in *Excel’s* CSV
> format
> > only. So for example the website will not take a text file with
> > comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
> > simply renamed with a .cvs file. Currently I am opening the .xlsx file
> > saved with my program thru Excel and re-saving it with Excel’s CSV format
> > - which works but does not leave me satisfied.
> >
> > Excel’s CSV files have the unique(?) property of being able to be opened
> > by both Excel and any text editor (don’t know if that means anything to
> > you). Plus these files when opened in a text editor have comma delimiters
> > plus a carriage return at the end of each line.
> >
> > My question is :
> > Is there a way to change the formatting of the .xlsx file thru The Apache
> > API so that it saves it as a “true” Excel CVS file?
> >
> > Any help would be appreciated.
> > Thank you,
> > Siva Sripada
> >
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

David Law-2
In reply to this post by Dominik Stadler
Hi,

one thing was not quite clear about the original question:
do you actually need the Excel at all?
or is the sole purpose of the exercise just to generate a CSV-File for
your upload?

And even if you do need the Excel, it would seem easier just to change the
Programme to write the CSV as a second file without having to use POI
for that bit.

Surely, the Excel CSV-Format is just a question of using other
delimiters between fields?
(and maybe quoting/escaping any strings containing delimiters, which is
absolutely trivial)

All the best,
DaveLaw


On 21/04/2017 20:04, Dominik Stadler wrote:

> Hi,
>
> Can you share an anonymized sample-csv-file? Apache POI likely does not
> have support for this itself, but if it is a text-format, it should be
> fairly easy to iterate over the rows/cells and produce the text-format
> yourself if we can figure out how Excel formats this CSV in some special
> way to be able to later detect the format again.
>
> BTW, please post such questions to the user-list, not to developers
> directly, this prevents others from participating in the documentation.
>
> Dominik.
>
> On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
> wrote:
>
>> Hi,
>> My name is Siva Sripada and I am a physician living in Michigan. I am
>> writing a program to upload a list of patients (first name, last name,
>> birthdate) to a website that will provide me information on a patient’s
>> previous prescription history over the last 1 year.
>> I am using the Apache API for Java for Excel and the program works
>> great!!! but….The website requires that the file be in *Excel’s* CSV format
>> only. So for example the website will not take a text file with
>> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
>> simply renamed with a .cvs file. Currently I am opening the .xlsx file
>> saved with my program thru Excel and re-saving it with Excel’s CSV format
>> - which works but does not leave me satisfied.
>>
>> Excel’s CSV files have the unique(?) property of being able to be opened
>> by both Excel and any text editor (don’t know if that means anything to
>> you). Plus these files when opened in a text editor have comma delimiters
>> plus a carriage return at the end of each line.
>>
>> My question is :
>> Is there a way to change the formatting of the .xlsx file thru The Apache
>> API so that it saves it as a “true” Excel CVS file?
>>
>> Any help would be appreciated.
>> Thank you,
>> Siva Sripada
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Siva Sripada
Hello
In fact this is the route I've taken. I'm using the OpenCSV API. Thanks for your reply and help tho!

Siva

Sent from my iPhone

> On Apr 23, 2017, at 11:04 AM, David Law <[hidden email]> wrote:
>
> Hi,
>
> one thing was not quite clear about the original question:
> do you actually need the Excel at all?
> or is the sole purpose of the exercise just to generate a CSV-File for your upload?
>
> And even if you do need the Excel, it would seem easier just to change the
> Programme to write the CSV as a second file without having to use POI for that bit.
>
> Surely, the Excel CSV-Format is just a question of using other delimiters between fields?
> (and maybe quoting/escaping any strings containing delimiters, which is absolutely trivial)
>
> All the best,
> DaveLaw
>
>
>> On 21/04/2017 20:04, Dominik Stadler wrote:
>> Hi,
>>
>> Can you share an anonymized sample-csv-file? Apache POI likely does not
>> have support for this itself, but if it is a text-format, it should be
>> fairly easy to iterate over the rows/cells and produce the text-format
>> yourself if we can figure out how Excel formats this CSV in some special
>> way to be able to later detect the format again.
>>
>> BTW, please post such questions to the user-list, not to developers
>> directly, this prevents others from participating in the documentation.
>>
>> Dominik.
>>
>> On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
>> wrote:
>>
>>> Hi,
>>> My name is Siva Sripada and I am a physician living in Michigan. I am
>>> writing a program to upload a list of patients (first name, last name,
>>> birthdate) to a website that will provide me information on a patient’s
>>> previous prescription history over the last 1 year.
>>> I am using the Apache API for Java for Excel and the program works
>>> great!!! but….The website requires that the file be in *Excel’s* CSV format
>>> only. So for example the website will not take a text file with
>>> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
>>> simply renamed with a .cvs file. Currently I am opening the .xlsx file
>>> saved with my program thru Excel and re-saving it with Excel’s CSV format
>>> - which works but does not leave me satisfied.
>>>
>>> Excel’s CSV files have the unique(?) property of being able to be opened
>>> by both Excel and any text editor (don’t know if that means anything to
>>> you). Plus these files when opened in a text editor have comma delimiters
>>> plus a carriage return at the end of each line.
>>>
>>> My question is :
>>> Is there a way to change the formatting of the .xlsx file thru The Apache
>>> API so that it saves it as a “true” Excel CVS file?
>>>
>>> Any help would be appreciated.
>>> Thank you,
>>> Siva Sripada
>>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Java API for Excel

Mohamed Ennahdi El Idrissi
Hi Siva,
If you are fetching patients from a database, you may use this API: Automatic Report Generator
 
|  
|   |  
Automatic Report Generator
 Download Automatic Report Generator for free. Generate reports from Java applications directly. Generally, what ...  |  |

  |

 

Merci,
Mohamed Ennahdi El Idrissi

mohalgorithmsorbit.blogspot.com

      From: Siva Sripada <[hidden email]>
 To: David Law <[hidden email]>
Cc: POI Users List <[hidden email]>
 Sent: Monday, April 24, 2017 9:46 AM
 Subject: Re: Java API for Excel
   
Hello
In fact this is the route I've taken. I'm using the OpenCSV API. Thanks for your reply and help tho!

Siva

Sent from my iPhone

> On Apr 23, 2017, at 11:04 AM, David Law <[hidden email]> wrote:
>
> Hi,
>
> one thing was not quite clear about the original question:
> do you actually need the Excel at all?
> or is the sole purpose of the exercise just to generate a CSV-File for your upload?
>
> And even if you do need the Excel, it would seem easier just to change the
> Programme to write the CSV as a second file without having to use POI for that bit.
>
> Surely, the Excel CSV-Format is just a question of using other delimiters between fields?
> (and maybe quoting/escaping any strings containing delimiters, which is absolutely trivial)
>
> All the best,
> DaveLaw
>
>
>> On 21/04/2017 20:04, Dominik Stadler wrote:
>> Hi,
>>
>> Can you share an anonymized sample-csv-file? Apache POI likely does not
>> have support for this itself, but if it is a text-format, it should be
>> fairly easy to iterate over the rows/cells and produce the text-format
>> yourself if we can figure out how Excel formats this CSV in some special
>> way to be able to later detect the format again.
>>
>> BTW, please post such questions to the user-list, not to developers
>> directly, this prevents others from participating in the documentation.
>>
>> Dominik.
>>
>> On Fri, Apr 21, 2017 at 7:45 PM, Siva Sripada <[hidden email]>
>> wrote:
>>
>>> Hi,
>>> My name is Siva Sripada and I am a physician living in Michigan. I am
>>> writing a program to upload a list of patients (first name, last name,
>>> birthdate) to a website that will provide me information on a patient’s
>>> previous prescription history over the last 1 year.
>>> I am using the Apache API for Java for Excel and the program works
>>> great!!! but….The website requires that the file be in *Excel’s* CSV format
>>> only. So for example the website will not take a text file with
>>> comma delimiters saved as a CSV file. Nor will it accept a .xlsx that was
>>> simply renamed with a .cvs file. Currently I am opening the .xlsx file
>>> saved with my program thru Excel and re-saving it with Excel’s CSV format
>>> - which works but does not leave me satisfied.
>>>
>>> Excel’s CSV files have the unique(?) property of being able to be opened
>>> by both Excel and any text editor (don’t know if that means anything to
>>> you). Plus these files when opened in a text editor have comma delimiters
>>> plus a carriage return at the end of each line.
>>>
>>> My question is :
>>> Is there a way to change the formatting of the .xlsx file thru The Apache
>>> API so that it saves it as a “true” Excel CVS file?
>>>
>>> Any help would be appreciated.
>>> Thank you,
>>> Siva Sripada
>>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]