cancel
Showing results for 
Search instead for 
Did you mean: 

Excel download issuel

Former Member
0 Kudos

Hello experts,

we are facing a strange issue in downloading excel file from portal following are the details

"when more data is coming from back end (ABAP RFC) excel download is taking too much of time and finally after download completes it is throwing "excel found unreadable content do you want to open yes/no" pop up and if we open a blank sheet is coming up."

ours is an custom webdynpro java application which has been built using adaptive RFC model for reading data from ABAP

below are the resolutions i tried

1. WDResourceFactory.createCachedResource(ExcelCSVFile, File_Name,WDWebResourceType.XLS, false);

2. WDWebResource.getWebResource(file, type);

3. Even i set the below parameters to extend the cached resource validity even though download is not working

     sap.resourcecache.pollingtime, sap.resourcecache.deletiontime, sapresourcecache.persistencetime

Please help me in resolving this.

Thanks,

Sanjay.

Accepted Solutions (0)

Answers (1)

Answers (1)

junwu
Active Contributor
0 Kudos

how much is your "more data"?

share u code, then we can analyze

Former Member
0 Kudos

  String File_Name= "delivery_schedules" + ".xls";

  IWDCachedWebResource cachedExcelResource = null;

  try

  {

  File Excel_File = new File(File_Name);

  WritableWorkbook Excel_Workbook =Workbook.createWorkbook(Excel_File);

  WritableFont Header_Font = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE,WritableFont.BOLD,false,UnderlineStyle.SINGLE,Colour.BLUE);

  WritableCellFormat Header_Format = new WritableCellFormat(Header_Font);

  WritableFont Item_Font = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);

  WritableCellFormat Item_Format = new WritableCellFormat(Item_Font);

  WritableCellFormat Item_Format2 = new WritableCellFormat(NumberFormats.FLOAT);

  WritableSheet Excel_Sheet = Excel_Workbook.createSheet("Delivery Schedules", 0);

  Label Excel_Label;

  Number Excel_Number;

  Excel_Label = new Label(0, 0, "mateRial",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  //Code Added By Ram

  Excel_Label = new Label(1, 0, "zGS",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  //Code Added By Ram

  Excel_Label = new Label(2, 0, "descriPtion",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(3, 0, "sAPONo.",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(4, 0, "releaseNumber",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(5, 0, "deliveryDate",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(6, 0, "deliveryTime",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  // Modification done with Respect to Ticket 8000000793

  // begin

  Excel_Label = new Label(7, 0, "collectionDate",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  //end

  Excel_Label = new Label(8, 0, "scheduledQty.",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(9, 0, "transitQty",Header_Format);

  Excel_Sheet.addCell(Excel_Label);

  DateFormat Date_Formater = new SimpleDateFormat("yyyyMMdd");

 

  for (int i=0;i<wdContext.nodeDS_E_Result().size();i++)

  {

  IPrivateSearchView.IDS_E_ResultElement ResultElement= wdContext.nodeDS_E_Result().getDS_E_ResultElementAt(i);

  Excel_Label = new Label(0,i+1,ResultElement.getMatnr(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  // Code Added By Ram

  Excel_Label = new Label(1,i+1,ResultElement.getRevlv(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  // Code Added By Ram

  Excel_Label = new Label(2,i+1,ResultElement.getMaktx(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(3,i+1,ResultElement.getEbeln(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(4,i+1,ResultElement.getAbruf(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(5,i+1,Date_Formater.format(ResultElement.getEindt()),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  Excel_Label = new Label(6,i+1,ResultElement.getRecpt_Time(),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  // Modification done with Respect to Ticket 8000000793

  // begin

  if (ResultElement.getColl_Date()!=null)

  {

  Excel_Label = new Label(7,i+1,Date_Formater.format(ResultElement.getColl_Date()),Item_Format);

  Excel_Sheet.addCell(Excel_Label);

  }

  // end

  Excel_Number = new Number(8,i+1,ResultElement.getMenge().doubleValue(),Item_Format2);

  Excel_Sheet.addCell(Excel_Number);

  Excel_Number = new Number(9,i+1,ResultElement.getTrans_Qty().doubleValue(),Item_Format2);

  Excel_Sheet.addCell(Excel_Number);

  }

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

  Excel_Workbook.write();

  FileInputStream ExcelCSVFile = new FileInputStream(Excel_File);

  cachedExcelResource = getCachedWebResource(ExcelCSVFile,File_Name,WDWebResourceType.getWebResourceType("xls","application/ms-excel")); //WDResourceFactory.createCachedResource(ExcelCSVFile, File_Name,WDWebResourceType.XLS, false);

  wdContext.nodeOthers().currentOthersElement().setURLExcel(cachedExcelResource.getUrl(WDFileDownloadBehaviour.ALLOW_SAVE.ordinal()));

  Excel_Workbook.close();

// popupConfDialog = wdComponentAPI.getWindowManager().createConfirmationWindow(wdContext.nodeOthers().currentOthersElement().getURLExcel(),evtHndlrCancel,"Ok");

// popupConfDialog.setTitle("Delivery Schedules");

// popupConfDialog.setIcon(wdContext.nodeIconSource().currentIconSourceElement().getIconInfo());

// popupConfDialog.setWindowPosition(WDWindowPos.CENTER);

// popupConfDialog.show();

  IWDWindow Excel_Window = wdComponentAPI.getWindowManager().createNonModalExternalWindow(wdContext.nodeOthers().currentOthersElement().getURLExcel()," document");

  Excel_Window.removeWindowFeature(WDWindowFeature.TOOL_BAR);

  Excel_Window.removeWindowFeature(WDWindowFeature.ADDRESS_BAR);

  Excel_Window.removeWindowFeature(WDWindowFeature.STATUS_BAR);

  Excel_Window.show();

  }

  catch (Exception Ex)

  {

  this.wdComponentAPI.getMessageManager().reportException(Ex.toString(),true);

  }

private IWDCachedWebResource getCachedWebResource(InputStream file,String name,WDWebResourceType type)
{
IWDCachedWebResource cachedWebResource = null;
if (file != null)
{
cachedWebResource = WDWebResource.getWebResource(file, type);
cachedWebResource.setResourceName(name);
}
return cachedWebResource;
}
Former Member
0 Kudos

Data would be around 2000 line items (records)

junwu
Active Contributor
0 Kudos

don't use file api

ByteArrayOutputStream baoStream = new ByteArrayOutputStream();

Former Member
0 Kudos

Hello,

I have done the following changes this time i could see below error message while opening excel

Error: The file you are trying to open "xxxx.xls" is in a different format than specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening file"

and after opening file its a blank excel sheet.

Changes done:

ByteArrayOutputStream baoStrm = new ByteArrayOutputStream();
//File Excel_File = new File(File_Name);
//WritableWorkbook Excel_Workbook =Workbook.createWorkbook(Excel_File);

WritableWorkbook Excel_Workbook = Workbook.createWorkbook(baoStrm);

//FileInputStream ExcelCSVFile = new FileInputStream(Excel_File);
ByteArrayInputStream baiS = new ByteArrayInputStream(baoStrm.toByteArray());
cachedExcelResource = getCachedWebResource(baiS,File_Name,WDWebResourceType.getWebResourceType("XLS","application/ms-excel"));
//cachedExcelResource = WDResourceFactory.createCachedResource(baiS, File_Name,WDWebResourceType.XLS, false);
junwu
Active Contributor
0 Kudos

why ByteArrayInputStream baiS = new ByteArrayInputStream(baoStrm.toByteArray());?

Former Member
0 Kudos

Hello Sanjay,

I have used the same code to generate the excel file in my project.

But the generated excel file is coming blank.

Can you please help me in this issue.

..................Code starting...................

String fileName = "output" + ".xls";

IWDCachedWebResource cachedExcelResource = null;

try

{

  File f = new File("output.xls");

  WritableWorkbook workbook = Workbook.createWorkbook(f);

  WritableFont red = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.SINGLE,Colour.RED);

    WritableCellFormat redFormat = new WritableCellFormat(red);

  WritableFont blue = new WritableFont(WritableFont.ARIAL,

  WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);

  WritableCellFormat blueFormat = new WritableCellFormat(blue);

  WritableSheet sheet = workbook.createSheet("First Sheet", 0);

                         

  jxl.write.Label label = new jxl.write.Label(0, 0, "Ele1",redFormat);

  sheet.addCell(label);

  label = new jxl.write.Label(1,0,"Ele2",redFormat);

  sheet.addCell(label);

             

  label = new jxl.write.Label(2,0,"Ele3",redFormat);

  sheet.addCell(label);

  WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER);

                

        for(int x=0; x<wdContext.nodeTable().size(); x++)

  {

   IWDNodeElement element2 = wdContext.nodeTable().getElementAt(x);

   Object type = element2.getAttributeValue("clm1");

   wdContext.currentContextElement().setTest001(type + "");

   jxl.write.Label number1 = new jxl.write.Label(0, x+1, "abcd");

   sheet.addCell(number1); // For adding the element to excel sheet

    }

                                                

  for(int x=0; x<wdContext.nodeTable().size(); x++)

  {

   IWDNodeElement element2 = wdContext.nodeTable().getElementAt(x);

   Object type = element2.getAttributeValue("clm2");

   wdContext.currentContextElement().setTest001(type + "");

   jxl.write.Label number1 = new jxl.write.Label(1, x+1, "abcd");

   sheet.addCell(number1); // For adding the element to excel sheet

  }

                              

  for(int x=0; x<wdContext.nodeTable().size(); x++)

  {

   IWDNodeElement element2 = wdContext.nodeTable().getElementAt(x);

   Object type = element2.getAttributeValue("clm3");

   wdContext.currentContextElement().setTest001(type + "");

   jxl.write.Label number1 = new jxl.write.Label(2, x+1, "abcd");

   sheet.addCell(number1); // For adding the element to excel sheet

  }

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

  workbook.write();

  FileInputStream excelCSVFile = new FileInputStream(f);

  cachedExcelResource = getCachedWebResource( excelCSVFile,  fileName,  WDWebResourceType.getWebResourceType("xls","application/ms-excel"));

  wdContext.currentContextElement().setExcel_URL(cachedExcelResource.getURL());

  IWDWindow window = wdComponentAPI.getWindowManager().createNonModalExternalWindow(cachedExcelResource.getUrl (WDFileDownloadBehaviour.OPEN_INPLACE.ordinal()),"Browser-description");

  window.removeWindowFeature(WDWindowFeature.ADDRESS_BAR);

  window.removeWindowFeature(WDWindowFeature.MENU_BAR);

  window.removeWindowFeature(WDWindowFeature.STATUS_BAR);

  window.removeWindowFeature(WDWindowFeature.TOOL_BAR);

  window.setWindowSize(780, 430);

  window.setWindowPosition(20, 140);

  window.show();

  }

  catch (RuntimeException e)

  {

  e.printStackTrace();

  wdComponentAPI.getMessageManager().reportSuccess("runtimeException in view onActionexcel_creation method" +e.getLocalizedMessage());

  }

  catch (Exception ex)

  {

  ex.printStackTrace();

  wdComponentAPI.getMessageManager().reportSuccess("Exception in view onActionexcel_creation method" +ex.getLocalizedMessage());

  }

..............end of code.................

Thanks

Sarita