Time format
Last Post 06 Feb 2013 02:32 PM by Thomas Sullivan. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Al Slezak
Advanced Member
Advanced Member
Posts:563
Avatar

--
28 Jan 2013 12:54 PM

Hi,

I am using the different formats in from the Java tool so that you can write rpg reports that go directly to Excel reports.  I want to create a cell style for that of all the different fime formats. 

// Create a cell style for dates.  Dates in Excel    
   //  are numbers that are formatted in a particular   
   //  way.                                             
                                                        
   Dates = HSSFWorkbook_createCellStyle(book);          
                                                        
   DataFmt = HSSFWorkbook_createDataFormat(book);       
   TempStr = new_String('m/d/yy');                      
   DateFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
   HSSFCellStyle_setDataFormat(Dates: DateFmt);         

Can you do the same thing as they do with the dates to create a new format for time but I also want to create a *USA format does anyone know how to do that.

TimeUSA = HSSFWorkbook_createCellStyle(book);

DataFmt = HSSFWorkbook_createDataFormat(book;

TempStr = new_String('00:00:00);

TimeFmt = HSSFDATAFormat_getFormat(DataFmt: TempStr);

HssfCellStyle_setDataFormat(TimeUSA : TimeFmt);

But how to you account for the am-pm part?

Al

Barbara Morris
Senior Member
Senior Member
Posts:5203

--
28 Jan 2013 03:34 PM
I know next to nothing about Excel, but I was curious, so ... I found a thread in an Excel forum that suggests format "h.mm AM/PM", so maybe this would work?

TempString = new_String('hh:mm AM/PM')
Lynne Noll
Senior Member
Senior Member
Posts:6567

--
29 Jan 2013 01:50 PM
I'm don't know about Excel, but SQL server has trouble when I try to replicate time fields from DB2. There are just dates and timestamps (date -time fields). A standalone time field gives trouble.
Al Slezak
Advanced Member
Advanced Member
Posts:563
Avatar

--
30 Jan 2013 09:47 AM

Hi All,

I tried that and there seems to be a problem.  I can create the style but for the date there is a program that it uses HSSF_DATE2XLS and HSSF_XLS2DATE and the HSSF_DATE procedure is already created.  What would I have to do to create this for the Time or is there a utility I need to add so I can format the time with this utility for creating the Excel program directly from the RPG.  Has anyone used this and tried to create these new styles?  I am sure there is some expert that can help.

Al

chris hayden
New Member
New Member
Posts:56

--
30 Jan 2013 02:00 PM

why not use the open source tool called SQL2XLS?

create your sql statement on the fly and the results are placed in an excel spreadsheet

Thanks,

Chris Hayden

 

 

 

Al Slezak
Advanced Member
Advanced Member
Posts:563
Avatar

--
30 Jan 2013 03:08 PM

Chris,

 

Because it does not allow you to do all the formatting in a standard program.  I need to do this because our users are low level and have to have the data in a formated excel document that they can work with.  I want to be able to do timestamps and times but I need to upgrade I think because I was looking at the Scott Klemet site for the tool and there seems to be an update for timestamps.  I will have to get that installed then maybe I can do the time conversion.   Doesn't anybody else use this it is a great tool?

Al

chris hayden
New Member
New Member
Posts:56

--
30 Jan 2013 07:57 PM
we modified the source for SQL2XLS to handle date formats, etc
Tommy Holden
Senior Member
Senior Member
Posts:2833
Avatar

--
31 Jan 2013 05:13 AM
i personally use Scott's HSSF/POI service programs exclusively for creating spreadsheets on the fly.
Al Slezak
Advanced Member
Advanced Member
Posts:563
Avatar

--
06 Feb 2013 01:11 PM

Tommy,

That is what I am trying to use but I want to know if I need to upgrade something so that I can write out a time in the *USA format  i.e. 2:00 p.m..  How can I set that up in the HSSF/ POD.  Do I need to upgrade to the latest version to get that to work or not, if not how do you define it?

Al

Thomas Sullivan
New Member
New Member
Posts:44

--
06 Feb 2013 02:32 PM
In Excel, times are stored in decimal format as the fraction of 24 hours - i.e 1:00 am is .04167 (1/24th of 24).  This means that if you can mathematically convert a time into a fraction of 24 hours, you can use a TEXT formula to format the time:

= Application.Text(.04167,"[h]:mm")

will display "1:00".

but I don't know how to use the TEXT formatting formula to show AM/PM.
You are not authorized to post a reply.

Acceptable Use Policy