Problem with export to excel

[Former Member]

Hi,

I was able to add external jar successfully and able to generate an excel sheet using the following code

public void exportToExcel2003( com.sap.tc.webdynpro.progmodel.api.IWDNode dataNode, java.util.Map columnInfos )

{

//@@begin exportToExcel2003()

byte[] excelXMLFile;

IWDCachedWebResource cachedExcelResource = null;

//String fileName = dataNode.getNodeInfo().getName() + ".xls";

String fileName = "SalaryData.xls";

try {

// create Excel 2003 XML data as a byte array for the given context node,

// attributes and headers

excelXMLFile = CreateExcel(dataNode, columnInfos).getBytes();

// POIFSFileSystem fs = new POIFSFileSystem(

// new FileInputStream(

// fileName));

// HSSFWorkbook wb = new HSSFWorkbook(fs);

// // get a reference to the worksheet

// HSSFSheet sheet = wb.getSheetAt(0);

// InputStream is = new FileInputStream(CreateExcel(dataNode, columnInfos).getBytes());

// HSSFWorkbook.

// create a cached Web Dynpro XLS resource for the given byte array

// and filename

cachedExcelResource = getCachedWebResource(

excelXMLFile, fileName, WDWebResourceType.XLS);

// Store URL and file name of cached Excel resource in context.

if (cachedExcelResource != null) {

wdContext.currentContextElement().setExcelFileURL(

cachedExcelResource.getURL());

//wdComponentAPI.getMessageManager().reportSuccess("inside"+cachedExcelResource.getURL());

wdContext.currentContextElement().setExcelFileName(

cachedExcelResource.getResourceName());

// wdContext.currentContextElement().setExcelFileName(

// "SalaryData.xls");

//wdComponentAPI.getMessageManager().reportSuccess("inside"+cachedExcelResource.getResourceName());

// Open popup window with a link to the cached Excel file Web resource.

openExcel();

} else {

wdComponentAPI.getMessageManager().reportException(

"Failed to create Excel file from table!", true);

}

}

// catch (UnsupportedEncodingException e) {

// wdComponentAPI.getMessageManager().reportException(

// e.getLocalizedMessage(), true);

// }

catch (WDURLException e) {

wdComponentAPI.getMessageManager().reportException(

e.getLocalizedMessage(), true);

}

//@@end

}

private HSSFWorkbook CreateExcel(IWDNode dataNode, Map mHeaders)

{

IWDMessageManager msgManager = wdComponentAPI.getMessageManager();

FileOutputStream fileContacts = null;

HSSFWorkbook oBook = null;

try

{

// Create the File

fileContacts = new FileOutputStream("SalaryData.xls");

oBook = new HSSFWorkbook();

oBook = PopulateExcel(oBook,dataNode,mHeaders);

oBook.setSheetName(0,"SalaryData");

try

{

//oBook.setActiveSheet(0);

oBook.write(fileContacts);

fileContacts.close();

}

catch (IOException ex)

{

msgManager.reportException(ex.getLocalizedMessage(), true);

}

}

catch (FileNotFoundException e)

{

msgManager.reportException(e.getLocalizedMessage(), true);

}

catch(Exception e){

}

return oBook;

}

private HSSFWorkbook PopulateExcel(HSSFWorkbook oBook,IWDNode dataNode, Map mHeaders)

{

IWDMessageManager msgManager = wdComponentAPI.getMessageManager();

// Create a new sheet

HSSFSheet oSheet = oBook.createSheet();

oBook.setSheetName(0,"Salary Data");

// Declare a row object reference and Counting Variable

HSSFRow oRow = null;

int iRow = 0;

// Declare a cell object reference

HSSFCell oCell = null;

// Create create cell styles

HSSFCellStyle csHeaders = oBook.createCellStyle();

HSSFCellStyle csNormal = oBook.createCellStyle();

HSSFCellStyle csLastRow = oBook.createCellStyle();

HSSFCellStyle csLegend = oBook.createCellStyle();

HSSFCellStyle csDisclaimer = oBook.createCellStyle();

HSSFDataFormat df = oBook.createDataFormat();

// Create create fonts objects

HSSFFont fHeaders = oBook.createFont();

HSSFFont fNormal = oBook.createFont();

HSSFFont fFine = oBook.createFont();

// Set Header Font properties

fHeaders.setFontHeightInPoints((short) 12);

fHeaders.setColor( (short)HSSFColor.WHITE.index);

fHeaders.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// Set Normal Font properties

fNormal.setFontHeightInPoints((short) 10);

fNormal.setColor( (short)HSSFFont.COLOR_NORMAL );

fNormal.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

// Set Fine Print Font properties

fFine.setFontHeightInPoints((short) 8);

fFine.setColor( (short)HSSFFont.COLOR_NORMAL );

fFine.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

fFine.setItalic(true);

// Assign Styles to Header Cells

csHeaders.setFont(fHeaders);

csHeaders.setBorderBottom(csHeaders.BORDER_THICK);

csHeaders.setBorderTop(csHeaders.BORDER_THICK);

csHeaders.setBorderRight(csHeaders.BORDER_THIN);

csHeaders.setBorderLeft(csHeaders.BORDER_THIN);

csHeaders.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);

csHeaders.setFillForegroundColor((short)HSSFColor.LIGHT_BLUE.index);

csHeaders.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// Assign Styles to Normal Cells

csNormal.setFont(fNormal);

csNormal.setBorderBottom(csNormal.BORDER_THIN);

csNormal.setBorderTop(csNormal.BORDER_THIN);

csNormal.setBorderRight(csNormal.BORDER_THIN);

csNormal.setBorderLeft(csNormal.BORDER_THIN);

// Assign Style for Last Data Row

csLastRow.setBorderBottom(csLastRow.BORDER_THICK);

csLastRow.setBorderTop(csLastRow.BORDER_THIN);

csLastRow.setBorderRight(csLastRow.BORDER_THIN);

csLastRow.setBorderLeft(csLastRow.BORDER_THIN);

// Assign Style to Legend and Disclaimer Cells

csLegend.setFont(fFine);

csLegend.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

csDisclaimer.setFont(fFine);

csDisclaimer.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// Create a Header Row

oRow = oSheet.createRow(iRow);

// Set and Populate the Header Cells

short iHeaderCell = 0;

int iColumns = mHeaders.size();

String[] aHeaders = new StringiColumns;

for (Iterator iter = mHeaders.keySet().iterator(); iter.hasNext();)

{

oCell = oRow.createCell(iHeaderCell);

aHeadersiHeaderCell=(String) iter.next();

oCell.setCellValue(new HSSFRichTextString(aHeadersiHeaderCell));

//oCell.setCellValue(aHeadersiHeaderCell);

oCell.setCellStyle(csHeaders);

iHeaderCell++;

}

// Set and Populate the Data Cells

int iElement = 0; //Start counting node Elements at 0

String sValue="";

for (iRow = 1; iRow <= dataNode.size(); iRow++)

{

// Create a row for the element

oRow = oSheet.createRow(iRow);

// Create cells needed to display the attributes

IWDNodeElement ePerson = dataNode.getElementAt(iElement);

for (short stCell = 0; stCell < iColumns; stCell++)

{

oCell = oRow.createCell(stCell);

oCell.setCellType(1);

// Set Value to print

sValue = ePerson.getAttributeValue(aHeadersstCell).toString();

if (sValue.length()>0)

{

oCell.setCellValue(new HSSFRichTextString(sValue));

//oCell.setCellValue(sValue);

}

else

{

oCell.setCellValue(new HSSFRichTextString(" "));

//oCell.setCellValue(" ");

}

// Create borders

if (iRow<dataNode.size())

{

oCell.setCellStyle(csNormal);

}

else

{

oCell.setCellStyle(csLastRow);

}

}

iElement++;

}

// Create a Row to Display the Legend

oRow = oSheet.createRow(iRow);

oCell = oRow.createCell((short)0);

oCell.setCellValue(new HSSFRichTextString("Salary Data"));

//oCell.setCellValue("Salary Data");

oCell.setCellStyle(csLegend);

oSheet.addMergedRegion(new Region(iRow,(short)0,iRow,(short)(iColumns-1)));

// Advance two rows and display the Disclaimer

iRow = iRow+2;

oRow = oSheet.createRow(iRow);

oCell = oRow.createCell((short)0);

oCell.setCellValue(new HSSFRichTextString("Salary Data"));

//oCell.setCellValue("Salary Data");

oCell.setCellStyle(csDisclaimer);

oSheet.addMergedRegion(new Region(iRow,(short)0,iRow,(short)(iColumns-1)));

for (short iColumn = 0; iColumn < iColumns; iColumn++)

{

// Size the Columns to fit the contents

if (aHeadersiColumn.equals("Email"))

{

oSheet.setColumnWidth(iColumn,(short)10000);

}

else

{

oSheet.setColumnWidth(iColumn,(short)7000);

}

}

return oBook;

}

but problem is when i open the excel sheet it says Lost document summary information

Please let me know how to resolve this issue

Thanks

Bala Duvvuri

[Former Member]

Hi,

Go through this link

/docs/DOC-8061#58

Thanks&Regards

Padma N

[Former Member]

Hi All,

i was able to resolve this issue using

Thanks

Bala Duvvuri

[Former Member]

Hi All,

can somebody send me the solution

Thanks

Bala Duvvuri

[Former Member]

I get the same error and do not know that there is a solution for this. We are training users to ignore the message as it does not affect the outcome. The spreadsheet generates and saves regardless of this message.

[Former Member]

I found an answer

"Method getBytes - get the bytes of just the HSSF portions of the XLS file. Use this to construct a POI POIFSFileSystem yourself."

Meaning you're getting ONLY the "Workbook" steam in the XLS file. You'd need to use POIFS to create a whole XLS file.

To do what you're attempting, construct an output stream, pass it to HSSFWorkbook.write(OutputStream), read the output stream into a byte array and construct an input stream around that.

from this link http://osdir.com/ml/jakarta.poi.user/2002-08/msg00101.html but i dont understand how to code for those sentences

Thanks

Bala Duvvuri