ApplinX tables to Excel

JoeA · December 19, 2007, 7:28pm

On a jsp page, I have a link to send the data to excel. I utilized the example in Sabrafoods and, of course, enlarged the scope by accumulating single occurrence fields and specialized headers. Now, all of this works fine, but there is one anomoly that I cannot seem to eliminate. The bottom row of the Applinx table that is displayed in the final Excel file is a blank row that has the “Merge & Center” function activated. I cannot see where this is implemented, but it prevents any summarization in any column. When you delete this row, all summarizations now work correctly. This answer, naturally, is unacceptable to our users and they want it eliminated before the excel file is displayed. Do you have any quick answers or suggestions on this matter? As always, I appreciate your efforts in assisting me. I have attached some sample files for you.

Thanks,

Joe Ayres
Blk2_excel.zip (5.69 KB)

Asaf_Shak · December 19, 2007, 9:16pm

Hi Joe,
I’m trying to understand what exactly the problem is.
What do you meant by “merge & center” function?
Meanwhile can you comment the part that export the page to excel (“getResponse().addHeader…”) and then send me the html you will get? Also send me an example of the excel file so we can understand better what is the happened.
Thanks,
Asaf

JoeA · December 19, 2007, 9:40pm

Hello Asaf,

I have attached the examples you requested. The “Merge & Center” function in Excel is on the toolbar. In my samples, line 50 of the spreadsheet is the row I am referring to. It is blank, but when open in excel, the “Merge & Center” button in the toolbar is highlighted, indicating that this is active for this row. I have never used it so I am unsure what it is for in this instance. I also don’t know where it gets set in this process. Let me know if I can clarify it any further.

Thanks,

Joe

P.S. Actually, I was looking at this further and I see that the “Merge & Center” function is on for the entire spreadsheet, not just the blank row at the bottom.
Blk2_Excel_Test_files.zip (15.4 KB)

Asaf_Shak · December 20, 2007, 8:06pm

Hi Joe,
We looked at the files you sent us and this is our conclusions:

Here it seems that the “Merge & center” function is not on the entire spreadsheet, but only on the cells that are composed of more then one cell. This is the default behavior for this kind of cells ( in HTML)
We took your code and try the same on our SabraFood application but we didn

JoeA · December 21, 2007, 7:09pm

Sure, here they are in a zipped file. Now, I have identified that the blank line appearing below the table is the following code in the Blk2_Excel_Test_htm file:

If you could also help me to understand why this is generated, maybe we can then resolve this problem. If it was not there, then our user problem of performing Sum on columns would be allieviated.

Thanks,

Joe
Excel.zip (254 KB)

Asaf_Shak · December 23, 2007, 5:40pm

Hi Joe,
I got your zip file, but it seems that the GCT is corrupted.
Did you managed to work with it? When working with the GXZ you sent me and the GCT on APX_521_005 I got the first screen ‘NetPass1’ and the second screen ‘CICS2’ but afterwards all the screen were empty. The GCT is important since I want to debug the code here, and I need the relevant screens & table to be available in the GCT.
Waiting for you replay,
Asaf

JoeA · December 24, 2007, 9:31pm

Hi Asaf,

I have attached a new file for you. Let me know if it works.

Joe
Dec24_excel.zip (20.9 KB)

Asaf_Shak · December 25, 2007, 5:44pm

Hi,
In 19.12 you sent me a zip file containing file called

JoeA · December 26, 2007, 6:48pm

Hi Asaf,

I did follow your instructions: (
Dec26_Excel.zip (20.9 KB)

JoeA · December 27, 2007, 12:29am

Asaf,

I made some corrections to a Service and created the attached GCT. Maybe this will work for you.

Joe
Dec21g_excel.zip (18.3 KB)

Asaf_Shak · December 27, 2007, 1:57pm

Hi Joe,
It seems that I misled you.
Please generate a new HTML file using these instructions:
Please comment this part in your java file (blk2_excel.java):

getResponse().reset();
getResponse().setContentType(“application/vnd.ms-excel”);
getResponse().addHeader(“Content-Disposition”,“attachment; filename="” + gx_table.getName() + “.xls"”);

Now, after pressing the excel link you will get HTML page. Please send me this page and the view source of this page.
The GCT is still not working well, we are looking into this issue also, but we if you can I want you to record the file in this way:
Go to the application properties (in the admin) → host tab → trace file section and check the

JoeA · December 27, 2007, 6:21pm

Hi Asaf,

Here are the files you asked for. I believe they are correct, but let me know if they need tweaking.

Thanks,

Joe
excel_problem.zip (48.9 KB)

Asaf_Shak · December 27, 2007, 8:19pm

Hi,
Thanks for the files, All of them are working great and are exactly what I wanted.
We saw the cause for the blank line (in the HTML).
We will work to fix it and I will update you as soon as possible (but only after this weekend)
Thanks for the information and the quick replays.
Regards,
Asaf

Asaf_Shak · December 30, 2007, 3:18pm

Hi again Joe,
The cause of the blank line is hidden field which is generated because of the XSL file.
Attached here is new XSL file, try to use it to solve your problem.
Let me know if the new file worked for you.
Happy New Year,
Asaf
hosttable.xsl (1.22 KB)

JoeA · December 31, 2007, 10:27pm

Hi Asaf,

Happy Holidays to you and all at Sabratec (Software AG). The hostTable.xsl file was exactly what we needed. It works perfectly. Thanks for your assistance, and as always, I greatly appreciate your time and effort.

Regards,

Joe

Asaf_Shak · January 1, 2008, 2:19pm

Thank you very much,
Happy holidays to you too.
Asaf and all SoftwareAG Israel (Sabratec)