Excel Formatting

[Toufeeq Tofie]

Hi

Is there anyone who can help me with how to do Excel formatting within the Progress coding:

1. Bold
2. Borders
3. Colors
4. Justification
5. Cell formatting (Number, Character, Date, Size, etc)
6. Inserting Lines

Thanks
Toufeeq

 

[sphipp]

You'll find a lot of examples here http://www.progresstalk.com/search.php?searchid=632326 or http://www.progresstalk.com/search.php?searchid=632335

 

[Casper]

If I don;t know how to do stuff in Excel:

1. I just record a macro, look at the VB code and then turn that to Progress (most times that means changing '.' for ':' ).
2. http://msdn.microsoft.com/en-us/library/aa272268(office.11).aspx

Regards,

Casper

 

[joey.jeremiah]

i dislike activex.

for one it doesn't scale very well in performance and resources.

not to mention platform dependent and requires additional software and licenses.


you can create html files, and open them in excel.

they scale very well, after all they're only text files. they're not platform dependent.

and theres also lots of 3rd party tools including free and open source tools. like html2pdf conversions.


for anything more then what you could do with html files look into openxml (office new, default formats based on xml and zip files).

 

[sunil]

For Bold,Font, Font Size and Syle Formatting

chWorkSheet:Range("A1:BB7"):Font:Bold = TRUE.
chWorkSheet:Range("A1:BB65536"):Font:Name = "Times New Roman".
chWorkSheet:Range("A8:BB65536"):Font:Size = 9.
chWorkSheet:Range("C8:BB65536"):Style = "Comma".
chWorkSheet:Range("C6:AX6"):NumberFormat = "[$-409]mmm-yy;@".

Note ("A1:BB7" is Column A to B and Row 1 to 7

Colour Formatting

chWorkSheet:Range("O1:Q65536"):Interior:ColorIndex = 42.

Note
If Cahnge 42 Color Index as a anothe number Color will chainge.

For Boader

PROCEDURE border:
DEFINE INPUT PARAMETER chWS AS COM-HANDLE.
DEF INPUT PARAM cRange AS CHARACT.
DEF INPUT PARAM Bod-Col-Ind AS INTEGER.
DEF INPUT PARAM Int-Col AS INTEG.
chWS:Range(cRange):Borders(5):LineStyle = -4142.
chWS:Range(cRange):Borders(6):LineStyle = -4142.
chWS:Range(cRange):Borders(7):LineStyle = 1.
chWS:Range(cRange):Borders(7):WEIGHT = 2.
chWS:Range(cRange):Borders(7):ColorIndex = Bod-Col-Ind.
chWS:Range(cRange):Borders(8):LineStyle = 1.
chWS:Range(cRange):Borders(8):WEIGHT = 2.
chWS:Range(cRange):Borders(8):ColorIndex = Bod-Col-Ind.
chWS:Range(cRange):Borders(9):LineStyle = 1.
chWS:Range(cRange):Borders(9):WEIGHT = 2.
chWS:Range(cRange):Borders(9):ColorIndex = Bod-Col-Ind.
chWS:Range(cRange):Borders(10):LineStyle = 1.
chWS:Range(cRange):Borders(10):WEIGHT = 2.
chWS:Range(cRange):Borders(10):ColorIndex = Bod-Col-Ind.
chWS:Range(cRange):Borders(11):LineStyle = 1.
chWS:Range(cRange):Borders(11):WEIGHT = 2.
chWS:Range(cRange):Borders(11):ColorIndex = Bod-Col-Ind.
/* chWS:Range(cRange):Borders(12):LineStyle = 1. */
/* chWS:Range(cRange):Borders(12):WEIGHT = 2. */
/* chWS:Range(cRange):Borders(12):ColorIndex = Bod-Col-Ind. */
/* */
chWS:Range(cRange):Interior:ColorIndex = Int-Col.
chWS:Range(cRange):Interiorattern = 1.

Note:
Call this Procedur where you wan to apply the Border. using Run Command

I will attached 2 programs with this reply.

Thanks,
Sunil

 

[Toufeeq Tofie]

Thank you all its greatly appreciated

 

[infomercialscam]

open excel Go to Format then Cells ( Ctrl + 1 ) on my keyboard.
Then Select date. there are quiet a few options to choose from under the Numbers Tab in the Format Cell box.

Next - Sort the worksheet.
You have to click the corner of the spreadsheet in order to highlight every cell in it. above 1 and beside A.
Then click Data and Sort. you can then sort.

I haven't really done much with excel and webpages, i normally use tables on webpages. So im not sure on that one. besides if its loosing boarders.. Maybe add a column or row shrink it down to near nothing to buffer the effect.

 

[Toufeeq Tofie]

There is one more thing, does can anyone tell me how to create a pivot table programatically on data within an existing excel document

 

[joey.jeremiah]

http://www.oehive.org/project/libxlsx

checkout the samples.zip

also works on unix/linux (actually even faster).


tell me if you need a hand.

hth

 

[joey.jeremiah]

did you try the samples by any chance ?

has anyone else did ?

thanks

 

[jdrouil]

Hi Joey, I would like to produce excel straight out of Progress, but don't want to use all of the complex object driven features in the newer versions of Progress. If I create a CSV using the export delimiter "," verb, what are my options to convert it to a simplified XLS?

Thanks,

Joe

Environment: Progress 9, QAD 9.1.

 

[joey.jeremiah]

look at the samples.zip

you could do it in one command.

and you don't have to know about any object. none.


the whole idea is that you do the visual design part in office (or open office).

and fill in the data in progress.


for example:

1. you create a file in excel with a list

you could also attach pivots, charts to that list, add auto filters etc.

add conditional formats, put even rows in one color and uneven in another color etc.

add summaries, formulas etc.


2. and replace the sample data you put in your list

with data from a temp-table or query ... and you're done.

all the styles, pivots, charts, formulas etc. get updated automagically.


3. there's also a second way to create files

for creating more sophistcated forms

using cut and paste commands and fields.

have a look at cut_paste.p in the samples.zip (it has a cute doggy in page header. look at the print preview)


all these programs run on unix/linux.

and there's no need for microsoft office or anything else.


hopefully next week it will also produce pdf files (among others).

and soon after that word files (with libdocx.p).

so you could create any word, excel, pdf etc. files anywhere.


so to cut it short look at the samples.zip

you'll see how simple it is

http://www.oehive.org/project/libxlsx

 

[Tony Beales]

Joey,

In your 'replace pivot' example, how would you handle an increased or reduced number of rows of data?

Thanks,
Tony

 

[tamhas]

You might also want to check out http://www.oehive.org/TempTableExpositor and http://www.peg.com/utilities/

 

[joey.jeremiah]

the temp-table expositor saves you some time, there's nothing really new here.

it lets you create lists in excel on *windows* using activex and several other text formats.


libxlsx let's you create rich, professional looking documents with styles, formulas, charts, pivots, images etc.

for all of the major formats, xlsx, xls, pdf, openoffice, lotus, html and many more.

including printing these graphical documents directly from unix/linux, as well as windows.

works on both windows and unix/linux, and compatible with progress v9 or higher.


libdocx for generating word 2007 files will be finished in the coming weeks.

we are also planning to release a 4gl query optimizer together with libxlsx and libdocx.

please download the library and try the samples.

 

[paulpeter]

Hmm,

If csv files are named .csv then the default open under Windoze activates excel without needing to do the text-to-column.

I don't think there is a Perl OLE.pm module available for OpenVMS, that woudl be too easy

XL97 and up can read a HTML table saved with an .xls extension.
This allows you to add some basic formatting which will be preserved
when the file is opened in XL. Just write out a text file with the table
from <Table> to </Table> and give it an .xls extension (if you want to
link to it)

I just tried it, going into excel first, and saving a trivial worksheet as html. Then rename to xls, Use notepad to change some entries to prove you can play with with, re-display with excel.

 

[jdrouil]

Thanks Paul. I am doing the csv thing now and it is "ok". Not looking for anything too fancy, just column widths that match the data, and the ability to specify cell or column formats. Will try your html idea and get back. Thanks again for responding.

 

[KnutHandsome]

Thanks Paul.... Thanks again for responding.

paulpeter is a clever spambot.

 

[joey.jeremiah]

i recently uploaded a new version of slibxlsx.p

http://www.oehive.org/project/libxlsx


have a look at the slibooxml/samples dir in the installation ZIP file.

hth