Excel Export - Funky Column Headers

[Former Member]

I have this code which creates an XML representation of context data. However when the Spreadsheet opens the headers are all funky.

in Debug mode values are set fine:

entriesName = "People" but shows up in Excel as "/People"

entryName = "PeopleElement"

headerName = Correct header name (i.e., Name, WorkPhone,Email) but shows up in Excel as "/PeopleElement/Name","/PeopleElement/WorkPhone","/PeopleElement/Email".

Here is the code can someone help me figure out where/why these extra characters are being added?

String sXML;

String attributeName, headerName;

String entriesName = dataNode.getNodeInfo().getName();

String entryName = entriesName + "Element";

// trim given header texts, so that XML element names adhere to the rule 'no spaces contained'.

trimHeaderTexts(columnInfos);

sXML="<?xml version='1.0' encoding='UTF-8' standalone='no'?>\n";

sXML=sXML"<"entriesName+">\n";

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

{

IWDNodeElement dataNodeElement = dataNode.getElementAt(i);

sXML=sXML"<"entryName+">\n";

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

{

attributeName = (String) iter.next();

headerName = (String) columnInfos.get(attributeName);

sXML=sXML+"<";

sXML=sXML+headerName;

sXML=sXML+">";

sXML=sXML+"<![CDATA[";

sXML=sXML+dataNodeElement.getAttributeValue(attributeName);

sXML=sXML+"]]>";

sXML=sXML+"</";

sXML=sXML+headerName;

sXML=sXML+">\n";

}

sXML=sXML"</"entryName+">\n";

}

sXML=sXML"</"entriesName+">\n";

return sXML;

Also I would like the columns to display in a different order than they do (they are displaying in the order of the context element but I would like them to display in a more logical order. I set up this mapping but it doesn't seem to change the order it just seems to set which elements from the selected node to display.

private Map getPersonColumnInfo()

{

Map columnInfoMap = new LinkedHashMap();

columnInfoMap.put(IPrivatePeopleFinderResultsView.IPeopleElement.NAME,"Name");

columnInfoMap.put(IPrivatePeopleFinderResultsView.IPeopleElement.WORK__PHONE, "Work Phone");

columnInfoMap.put(IPrivatePeopleFinderResultsView.IPeopleElement.EMAIL, "Email");

return columnInfoMap;

}

Edited by: TA on Aug 13, 2008 5:59 PM

[Former Member · Accepted solution]

Hi TA,

Kindly look at the following links for more clarification

- https://www.sdn.sap.com/irj/sdn/wiki?path=/x/0mq

-

The above link has ready to use code which can be used for download to excel , or you can use jexcel.api which is also a freeware and does not download the data as an xml string.

Regards

Pankaj Prasoon

[Former Member]

Ended up using the advance excel tutorial mixed with the binary cache tutorial for a good solution

[dhruv_shah3]

Hi TA,

Can you pls give that solution so the SDN Fraternity could use that.

I am also facing the same problem for Column Headers.

Regards,

Dhruv

[Former Member]

I used this tutorial: https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/edc2f3c2-0401-0010-8898-acd5b6a9.... But I replaced this function private String toExcel(IWDNode dataNode, Map columnInfos) with this:

private HSSFWorkbook CreateExcel(IWDNode dataNode, Map mHeaders)

{

IWDMessageManager msgManager = wdComponentAPI.getMessageManager();

FileOutputStream fileContacts = null;

HSSFWorkbook oBook = null;

try

{

//Create the File

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

oBook = new HSSFWorkbook();

oBook = PopulateExcel(oBook,dataNode,mHeaders);

oBook.setSheetName(0,"Contacts");

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);

}

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,"My Contacts");

//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 String[iColumns];

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

{

oCell = oRow.createCell(iHeaderCell);

aHeaders[iHeaderCell]=(String) iter.next();

oCell.setCellValue(new HSSFRichTextString(aHeaders[iHeaderCell]));

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(aHeaders[stCell]).toString();

if (sValue.length()>0)

{

oCell.setCellValue(new HSSFRichTextString(sValue));

}

else

{

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

}

//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("*Denotes this person is a Mylan Contractor"));

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("This List is property of Mylan and is not to be distributed"));

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 (aHeaders[iColumn].equals("Email"))

{

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

}

else

{

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

}

}

return oBook;

}

Then I removed this function: private String trimHeaderText(String headerText)

Hope this helps !!)

[Former Member]

Hi TA,

I am trying to create export to excel functionality using the code you mentioed above.

I downloaded jakarta-poi-1.5.1-final-20020615.jar from http://www.apache.org/dyn/closer.cgi/poi/ but some of the classes are missing like HSSFRichTextString and some of the methods like createRow,setActiveSheet are missing from HSSFWorkbook() class.

How do i rectify them.If you have the jar file send it to me balasubramanyam.duvvuri at gmail dot com

Thanks

Bala Duvvuri

[Former Member]

These are not missing from the jar they are missing from your code. You have to 'import' them if you select "ctl->shift->o NWDS will import them for you.

After you do that you should see the following (or you can paste these in yourself).

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.hssf.util.Region;

Edited by: TA on Oct 7, 2008 8:44 PM

[Former Member]

TA,

for importing all the classes i should add a jar file right.can you send me that jar file

Thanks

Bala Duvvuri

[Former Member]

Hi Bala,

I sent the required jar file to your gmail id.

Hope it will be helpful to u.

Regards,

Vijay.

[Former Member]

Mohamad,

thanks for your help.

i will come back if i need anything

Thanks

Bala Duvvuri

[Former Member]

Hi,

I am getting Lost document summary information when trying to open the excel sheet

Please help me in resolving this

Thanks

Bala Duvvuri

[Former Member]

Hi

Try this link

Regards

Vinod V

[Former Member]

Thanks Pankaj and Vinod these were both great suggestions - I actually used the tutorial suggested by Pankaj which was very similar to Vinod's suggestion.

The fact is that the code works fine - the excel spreadsheet is created and all the data is there it is just giving me these funky headers instead of just the plain column headers I would like to have. People will be confused by the header PeopleElement/Name instead of Name.

Also it is listing Email, Name then Phone which also makes no sense because in a list of People you should always see the Name first.

[Former Member]

Hi,

Have you tried to print the xml generated?

Could you please post the generated xml.

Regards

Ayyapparaj

[Former Member]

This is the XML string that the code generates:

"<?xml version='1.0' encoding='UTF-8' standalone='n'>

<People>

<PeopleElement>

<Name><![CDATA[Terri]]></Name>

<Email><![CDATA[Terri.hotmail.com]]></Email>

<Phone><![CDATA[4581]]></Phone>

</PeopleElement>

<PeopleElement>

<Name><![CDATA[Bob]]></Name>

<Email><![CDATA[Bob.hotmail.com]]></Email>

<Phone><![CDATA[3344]]></Phone>

</PeopleElement>

</People>"

I thought this was coming out in the wrong order but it is not - The XML string is generated correctly it is upon conversion to excel it seems to get messed up.

Edited by: TA on Aug 14, 2008 8:29 PM

Edited by: TA on Aug 20, 2008 10:50 PM

Edited by: TA on Aug 20, 2008 10:54 PM