SAP Webdynpro Java UItable export to excel

[Former Member]

Hi all,

I  have delopment a old method for export UItable to excel 2003 (.xls) , the method was development following:

Exporting Context Data into Excel Using the Web Dynpro Binary Cache

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/edc2f3c2-0401-0010-8898-acd5b6a94...

I have two problem:

- The first is when I export big tables, the process is very slow and sometime the portal not work correctly, other time I can export my excel but is very very slow...

Also is very strange because when I export my UItable and select save the file have a size about 70 MB (also is very slow the process to save)  but when I go to the folder in show the properties file it have a sice about 1 or 2 MB.

- The another problem is if the user have another version of excel (more new) .xlsx, show a warning message,  we will upgrade these components:

SAP_JTECHF 7.01 SP9 (1000.7.01.9.0.20110512080700)  - now version

SAP_JTECHS 7.01 SP9 (1000.7.01.9.0.20110512080700)  - now version

But we are not sure if with the upgrade we can solve the problem with extensions .xlsx.

Please can you help me with this issue?

Thanks in advance

Regards

Mónica

[junwu]

this upgrade won't change anything.

use some excel lib to do the job, like jxl, apach poi...

[Former Member]

Thanks Govardan and Jun.

The upgrade has been a SAP recommendation because we opened note to SAP about this issue....

I understand that have two consecutive for loops for uploading the data can affect the speed, but I don't understand the problem with the file size ,

Why the files have a size so big when they are loading and after the size file is smaller?

Regards

[govardan_raj]

Hi Monica ,

when the UI Table is huge since it has many records and in the doucment two consecutive for loops are being used for uploading the data so you are facing the time issue .

you can use external jar file jxl.jar and use below the code for uploadign the data to excel

//--------------------------------------------------------------------------------------------------------------------------------

   try

   {

  //Download Excel file

  boolean isRecordFound = false;

  ByteArrayOutputStream out = new ByteArrayOutputStream();

  WritableWorkbook workbook =   Workbook.createWorkbook(out);

  WritableFont black = new WritableFont(WritableFont.createFont(****RptConstant.TREBUCHET_MS_FONT_STYLE),WritableFont.DEFAULT_POINT_SIZE,WritableFont.BOLD,false,UnderlineStyle.SINGLE,Colour.BLACK);

  WritableCellFormat blackFormat = new WritableCellFormat(black);

  WritableFont blue = new WritableFont(WritableFont.createFont(****RptConstant.TREBUCHET_MS_FONT_STYLE),WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);

  WritableSheet sheet = workbook.createSheet("Customer Report", 0);

  WritableCellFormat amountFormat = new WritableCellFormat(NumberFormats.FLOAT);

  amountFormat.setFont(blue);

  WritableCellFormat textFormat = new WritableCellFormat(NumberFormats.TEXT);

  textFormat.setFont(blue);

  WritableCellFormat wcf = new WritableCellFormat(); 

  wcf.setAlignment(Alignment.RIGHT);

  Label label;

  Number number;

  String[] ****ReportHeader =  columnHeaderForDownloadedExlandPDF.split(",");

  int ****ReportHeaderLength = ****ReportHeader.length;

  for(int i=0; i<****ReportHeaderLength; i++)

  {

  label = new Label(i,0,****ReportHeader[i].toString(),blackFormat);

  sheet.addCell(label);

  }

  int rowCount = 0;

  int colcount = 0;

  while(TTTTTReportResultSet.next())

  {

  isRecordFound = true;

  rowCount++;

  colcount = 0;

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(9),textFormat);

  sheet.addCell(label);

  colcount++;

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(12),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(17),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(1),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(2),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(18),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(19),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(7),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(14),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  // Did on 06.05.2014 as TTTTT Amount was changed to string

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(6),wcf);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(8),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(20),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(3),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(4),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(31),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(5),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(15),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(16),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(10),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(11),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(13),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(21),textFormat);

  sheet.addCell(label);

  colcount++;

      }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(22),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(23),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(24),textFormat);

  sheet.addCell(label);

  colcount++;

  }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(25),textFormat);

  sheet.addCell(label);

  colcount++;

      }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(26),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(27),textFormat);

  sheet.addCell(label);

  colcount++;

      }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(28),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(29),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  {

  label = new Label(colcount,rowCount,TTTTTReportResultSet.getString(30),textFormat);

  sheet.addCell(label);

  colcount++;

     }

  }

  workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

  workbook.write();

  workbook.close();

  if(isRecordFound)

  {

  String fileName = null;

  Date date = new Date(System.currentTimeMillis());

  SimpleDateFormat dateFormat = new SimpleDateFormat(****RptConstant.DATE_FROMAT);

  String date1 = dateFormat.format(date);

  fileName = corp_code+"_TTTTT_Report_"+date1;

  IWDResource Resource= WDResourceFactory.createResource(new ByteArrayInputStream(out.toByteArray()),fileName,WDWebResourceType.XLS,true);

  out.close();

  wdComponentAPI.getWindowManager().createNonModalExternalWindow(Resource.getUrl(WDFileDownloadBehaviour.ALLOW_SAVE.ordinal()), Resource.getResourceName()).show();

  }

  else

  {

  wdComponentAPI.getMessageManager().reportException("No Data Found",true);

  wdThis.getMessageWindow();

  }

  }catch(Exception e)

  {

  wdThis.handleException(e,****UtilConstant.ERR_DOWNLOADING_TO_EXCEL);

  }

you can go through this code and have any doubts i would clarify , and for using this you should have external jar file jxl.jar .

make sure you download suitable version of jxl.jar , for 7.01 it is different