LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 10-16-2022, 01:33 PM   #1
hyattdj
LQ Newbie
 
Registered: Oct 2022
Posts: 1

Rep: Reputation: 0
while converting CSV to XLS in Perl, want to do some formatting


Been a long time since I did serious Perl programming.
We have successfully converted CSV to xls but have a couple of bugs we want to correct before presenting to the customer.

We want to select column width. As the "dates" in the header column show up as XXXX and the users want to see the date.

Also, we are putting in a variable (date) into a cell. But the value does not look like any date format I know. But when we go into excel and define that cell as a date. The date is correct (looks too few digits to be linux start date).

The documentation I have looked at seems to be very narrow. Is there any documentation on how to do extensive formatting of XLS files from the command line/program which modules or what in the modules can do this formatting from a CSV file to an XLS file.
We would also like to use a broader range of colors and other formatting.
Thanks in advance
 
Old 10-17-2022, 09:17 AM   #2
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,412

Rep: Reputation: 416Reputation: 416Reputation: 416Reputation: 416Reputation: 416
What library are you using?

I did it a long time ago with Perl. I can't remember what I used, but it was something like this:

https://metacpan.org/pod/Excel::Writer::XLSX

It has lots of documentation and formats. Dates are a bit special.

https://metacpan.org/pod/Excel::Writer::XLSX#write_date_time(-$row,-$col,-$date_string,-$format-)
 
Old 10-17-2022, 09:31 AM   #3
boughtonp
Senior Member
 
Registered: Feb 2007
Location: UK
Distribution: Debian
Posts: 2,983

Rep: Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120Reputation: 2120

If you want formatted tabular data, easiest is to generate HTML tables.

If the client insists on Microsoft Excel, still use HTML, but with "mso-number-format" styling and saved as .xls extension.

Here's an example: https://dev.to/michrodz/styling-excel-cells-with-mso-number-format-css-attribute-updated-j6i

 
1 members found this post helpful.
Old 10-17-2022, 10:31 AM   #4
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 25,407

Rep: Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574Reputation: 7574
Quote:
Originally Posted by hyattdj View Post
Been a long time since I did serious Perl programming. We have successfully converted CSV to xls but have a couple of bugs we want to correct before presenting to the customer.

We want to select column width. As the "dates" in the header column show up as XXXX and the users want to see the date.

Also, we are putting in a variable (date) into a cell. But the value does not look like any date format I know. But when we go into excel and define that cell as a date. The date is correct (looks too few digits to be linux start date).

The documentation I have looked at seems to be very narrow. Is there any documentation on how to do extensive formatting of XLS files from the command line/program which modules or what in the modules can do this formatting from a CSV file to an XLS file. We would also like to use a broader range of colors and other formatting.
Not a lot to go on...you don't provide any samples of the input data, or any samples of your code that tell us what you're currently using or how. The Excel::Writer::XLSX
library, as Guttorm suggested:
https://metacpan.org/pod/Excel::Writer::XLSX

...has extensive documentation. Dates in Excel are numbers, followed by formatting.
https://metacpan.org/pod/Excel::Writ...-plus-a-format
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting CSV to XLS (using console)? Xeratul Linux - General 7 01-26-2017 11:18 AM
How to print lines in csv file if 1 csv column field = "text". There are 10 column (;) in csv file nexuslinux Linux - Newbie 9 04-23-2016 12:35 AM
Reading one EXCEL(.xls) file and writing to other EXCEL(.xls) file vijay mishra General 6 04-23-2012 08:53 PM
Perl convert csv to xls hawk__0 Programming 3 09-16-2009 10:13 AM
Parsing XLS or CSV in Perl - what and what not me_the_apprentice Programming 13 02-28-2006 04:44 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 05:30 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration