Thread: Setting Excel column width

Setting Excel column width

12/02/14 12:28

Jannie Louw

Replies: 10

Is there any way to set the column width of an Excel spreadsheet when using
ExcelDocument. At the moment this has to be done manually every time the
spreadsheet is generated.
I have tried to use &ExcelDocument.AutoFit = 1, but this seems to have no
effect. I would prefer to set every column's width programmatically

Replies

Jannie Louw

12/02/14 13:58

Hi Leandro I did not appear to work. I have tried putting the Autofit command before populating the cells & after, but no result How else can I populate an Excel spreadsheet without using &ExcelDocument Regards & Thanks Jannie Louw

Jannie Louw

12/02/14 14:27

Hi Leandro I did work when I deleted all the old files manually, but then I also had to turn on Wrap manually again Thanks again

Sergio Prusky

12/02/14 15:17

Jannie, Try using the Template property. Before you open the Excel, assign it a template. You can set the width and other properties of columns and cells in the template. For the template you may use an xls or an xlt. We use the following lines before creating any Excel, changing the name of the Excel file. In this example, StyleInventoy.xlt defines formatting (column width, heading, even macros if necessary). &DocNbr = PGetNextNbr.Udp('REPORTS','Y', 1) &FileName = trim(&SdtContext.CmpPathReports) + 'StyleInventory-' + trim(&DocNbr.ToString()) + '.xls' &ExcelFile.Template = trim(&SdtContext.CmpPathReports) + 'templates\StyleInventory.xlt' &ExcelFile.Open(&FileName) On Wed, Feb 12, 2014 at 6:54 AM, Jannie Louw >

Sergio Prusky

13/02/14 15:20

Jannie, You will have to find out why it is hanging up. What platform are you using? Try to find an error log that will help you define what the error is. With Java for example, if the applications doesn't have access to the file, it crashes. If it is a Web application, try placing the template file in a location that can be accessed from the application, to make sure that the user that is running the program has access to the file. We always use xls and xlt, we don't use xlsx or xltx, although these should work too. For all versions of Excel we use these older formats, because I don't know what the libraries that GX uses to read and write Excel files support. Best, Sergio On Wed, Feb 12, 2014 at 5:27 AM, Jannie Louw >

Jannie Louw

14/02/14 10:14

Hi Sergio
I am using Evolution 2 u4 with C# running under window 8 with Excel 2013 on
a web environment.
It hangs when I try to ".xls" Excel files instead of ".xlsx (even with no
.template statement). If I do not include the .template statement the excel
.xlsx file is created correctly, but obviously unformatted.
As soon as I include the .template statement with .xlsx excel files, I get
the error message "Invalid template", even if it is included in the same
directory where the excel file is create. Therefore the system must have
access to the folder. I did check the security settings of the folders.
I will log an issue.
I have 
Thanks for all your support
 
Jannie,
 
You will have to find out why it is hanging up.  What platform are you
using?  Try to find an error log that will help you define what the error
is.  With Java for example, if the applications doesn't have access to the
file, it crashes.
 
If it is a Web application, try placing the template file in a location that
can be accessed from the application, to make sure that the user that is
running the program has access to the file. We always use xls and xlt, we
don't use xlsx or xltx, although these should work too. For all versions of
Excel we use these older formats, because I don't know what the libraries
that GX uses to read and write Excel files support.
 
Best,
Sergio
 
On Wed, Feb 12, 2014 at 5:27 AM, Jannie Louw <jl26@mweb.co.za
<https://app.getsignals.com/link?url=mailto%3Ajl26%40mweb.co.za&ukey=agxzfnN
pZ25hbHNjcnhyGAsSC1VzZXJQcm9maWxlGICAgID7i9kKDA&k=04441729-e52b-42d8-b954-26
8619ffbe03> >

Hi Sergio I am using Evolution 2 u4 with C# running under window 8 with Excel 2013 on a web environment. It hangs when I try to ".xls" Excel files instead of ".xlsx (even with no .template statement). If I do not include the .template statement the excel .xlsx file is created correctly, but obviously unformatted. As soon as I include the .template statement with .xlsx excel files, I get the error message "Invalid template", even if it is included in the same directory where the excel file is create. Therefore the system must have access to the folder. I did check the security settings of the folders. I will log an issue. I have Thanks for all your support Jannie, You will have to find out why it is hanging up. What platform are you using? Try to find an error log that will help you define what the error is. With Java for example, if the applications doesn't have access to the file, it crashes. If it is a Web application, try placing the template file in a location that can be accessed from the application, to make sure that the user that is running the program has access to the file. We always use xls and xlt, we don't use xlsx or xltx, although these should work too. For all versions of Excel we use these older formats, because I don't know what the libraries that GX uses to read and write Excel files support. Best, Sergio On Wed, Feb 12, 2014 at 5:27 AM, Jannie Louw >

ggallotti7825

14/02/14 11:08

Hi Jannie, The reason why application hangs is because GeneXus didn't find "Jakarta Files" that are required when working with ExcelDocument. You may read this SAC: https://www2.genexusnet.com/gxpintranet/hgxpp001.aspx?1,14,132,O,S,0,,19780 All in all you will need the Jakartas dlls: http://sourceforge.net/project/showfiles.php?group_id=151482&package_id=171498 And enable "32 bit application" in you IIS. The good news is that GeneXus X Evolution 2 U4 has native support for xlsx Excel files. For working with templates in xlsx you will need to create your template with xlsx extension instead of .template. This behavior is documented here: https://www2.genexusnet.com/gxpintranet/hgxpp001.aspx?1,14,132,O,S,0,,34662 Hope it helps, Gonzalo. On Fri, Feb 14, 2014 at 9:13 AM, Jannie Louw

Blanche Tucker

14/02/14 12:30

How do I remove myself from this mailing list? I have forgotten. Thanks!

Jannie Louw

17/02/14 06:59

Hi Gonzalo Unfortunately I do not have access to https://www2.genexusnet.com/gxpintranet/hgxpp001.aspx?1,14,132,O,S,0,,19780 or https://www2.genexusnet.com/gxpintranet/hgxpp001.aspx?1,14,132,O,S,0,,34662. I assume this is because I only have access to the Extranet not the Intranet. I downloaded the Jakarte dll's and copied them to c:\Models\KnowledgeBase\CSharpModel\web\bin\ directory and tries the .xls with the .xlt template, but the system still hangs. This is with Evolution 2 u4. I then tries the .xlsx option with .xlsx as the template (which sounds strange as .xlsx is not a template) or even opening an existing .xlsx file with the correct format. None of these options worked. If I put the .xlsx file in the .\Custom Office Template" I still get the error message that the Template does not exist. Any other suggestions?

Jannie Louw

17/02/14 07:20

Hi Gonzalo I eventually got it working for with the .xlsx file where I open an existing .xlsx file with the correct format, clearing the file and the writing to the file I still would appreciate if you can email the https://www2.genexusnet.com/gxpintranet/hgxpp001.aspx?1,14,132,O,S,0,,34662 article Thanks

ggallotti7825

17/02/14 10:05

http://www2.gxtechnical.com/portal/hgxpp001.aspx?15,4,61,O,E,0,,19780;E; http://www2.gxtechnical.com/portal/hgxpp001.aspx?15,4,61,O,E,0,,34662;E; On Mon, Feb 17, 2014 at 6:19 AM, Jannie Louw

Back to gxenglish-l