about excel export for WD!

[Former Member]

i 've seen the thread <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/edc2f3c2-0401-0010-8898-acd5b6a94353"><b>web dynpro Binary Cache</b></a>.

<b>i have to got some changes for requirement:</b>

<b>1.></b>i don't wanna to open a new URL page and a download dialog windows(open/save/cancel option buttion) when i click excel URL file.i hope it only to pop the download dialog windows;

<b>2.></b> after i download the excel file to my local computer.i don't wanna to select some options(1.As a XML list;2.As a read-only workbook) to open it.i hope i can open it directly by click the *.xls file.

<b>3.></b>i don't know how to change the column name for excel file. Liking some blank-letter maybe appear in the column name. e.g.: <b>First Name</b> not <b>FirstName</b>.

looking forward to u reply.thanks in advance!

[Former Member · Accepted solution]

HI,

Use the following threads,

/message/758567#758567 [original link is broken]

Regards

Saravanan K

[Former Member · Accepted solution]

sorry,Saravanan K

There is no any helps to me from these links, in fact i can got excel export function by web dynpro Binary Cache way.but a little problems as above is confusing me.

[Former Member · Accepted solution]

Point 2 and Point 3 are solvable in the following way.

Refer the Web Dynpro Binary Cache and there are minute changes that needs to be done.

Implement the following methods:

exportToCSV

-

---

  public void exportToCSV( com.sap.tc.webdynpro.progmodel.api.IWDNode dataNode, java.util.Map columnInfos )
  {
    //@@begin exportToCSV()
	byte[] excelCSVFile;
	IWDCachedWebResource cachedExcelResource = null;
	String fileName = dataNode.getNodeInfo().getName() + ".csv";
  	
	try
	{
		excelCSVFile = toExcelCSV(dataNode, columnInfos).getBytes("UTF-8");
		cachedExcelResource = getCachedWebResource(excelCSVFile, fileName, WDWebResourceType.getWebResourceType("csv", "application/ms-excel")); 
  		
		if(cachedExcelResource != null)
		{
			wdContext.currentContextElement().setExcelFileURL(cachedExcelResource.getURL());
			wdContext.currentContextElement().setExcelFileName(cachedExcelResource.getResourceName());
			openExcelLinkPopup();
		}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
  }

To Excel CSV

-

---

  public String toExcelCSV( com.sap.tc.webdynpro.progmodel.api.IWDNode dataNode, java.util.Map columnInfos )
  {
	StringBuffer x = new StringBuffer();
    
	String attributeName, headerName;
    
	//trimHeaderTexts(columnInfos);

	for(Iterator iter = columnInfos.keySet().iterator(); iter.hasNext();)
	{
		attributeName = (String) iter.next();
		headerName = (String) columnInfos.get(attributeName);
		x.append(headerName)
		 .append(",");
	}
	x.append("n");

	for(int i = 0; i< dataNode.size(); ++i)
	{
		IWDNodeElement dataNodeElement = dataNode.getElementAt(i);
		
		for(Iterator iter = columnInfos.keySet().iterator(); iter.hasNext();)
		{
			attributeName = (String) iter.next();
			x
			 .append(dataNodeElement.getAttributeValue(attributeName))
			 .append(",");
		}
		x.append("n");
	}
    
	wdComponentAPI.getMessageManager().reportSuccess(x.toString());
    
	return x.toString();
  }

Regards,

Subramanian V.

[Former Member · Accepted solution]

hi,Subramanian Venkateswaran

as done as u said,the point 2 and 3 can be solved!

i'v read this post

<a href="/people/sap.user72/blog/2006/05/04/enhancing-tables-in-webdynpro-java-150-custom-built-table-utilities an Excel File</b></a>

but i cann't execute it correctly,code as following:

<b>ArrayList al = new ArrayList();

al.add(IPrivateTableCompBasketView.IProductsElement.ARTICLE);

al.add(IPrivateTableCompBasketView.IProductsElement.CURRENCY);

Map mp=wdContext.currentContextElement().getTableUtils().DownloadToExcel(al); //debug step by step,here report exception:<i>java.lang.NullPointerException</i>

String url=(String)mp.get("url");

wdContext.currentContextElement().setLinkUrl(url);</b>

[Former Member · Accepted solution]

For the record, the weblog you are referring to will not eliminate your points 2 and 3. It is just a different flavour of Web Dynpro Binary Cache tutorial.

Secondly, your problem at hand, as the error states, you are referring to an object whose value is null. Most likely your attribute TableUtils has not been initialized.

Regards,

Subramanian V.

[Former Member · Accepted solution]

hi,Subramanian Venkateswaran

can u tell me how to set the excel columns font and color in your code of toExcelCSV()?

[Former Member · Accepted solution]

This is not possible with toExcelCSV().

You can use <a href="http://sourceforge.net/projects/jexcelapi">JExcelAPI</a>

to do this.

Regards,

Subramanian V.

[Former Member · Accepted solution]

Refer this weblog <a href="/people/subramanian.venkateswaran2/blog/2006/08/16/exporting-table-data-to-ms-excel-sheetenhanced-web-dynpro-binary-cache">Exporting table data to MS-Excel Sheet(enhanced Web Dynpro Binary Cache)</a>

Regards,

Subramanian V.

Message was edited by: Subramanian Venkateswaran

[Rodrigo-Giner · Accepted solution]

Hi Subramanian.

I used the ready to use proyect of Export Excel with Binary Cache.

If I replace my code with yours.

When I open the Products.cvs I get this.

Article,Quantity,Price in EURO,Total Per Article In Euro,Color,

jacket,0,34.60,0.00,blue,

skirt,0,24.95,0.00,red,

t-shirt,0,29.90,0.00,orange,

trousers,0,64.90,0.00,black,

top,0,44.90,0.00,black,

dress,0,78.90,0.00,colored,

blouse,0,35.50,0.00,white,

jeans,0,89.90,0.00,blue,

pullover,0,69.00,0.00,red,

sweatshirt,0,61.60,0.00,green,

polo shirt,0,14.65,0.00,yellow,

short,0,44.90,0.00,dark blue,

blouse,0,49.90,0.00,white,

skirt,0,55.00,0.00,white,

pullover,0,127.00,0.00,white,

dress,0,178.90,0.00,black,

top,0,54.00,0.00,red,

trousers,0,79.00,0.00,black,

t-shirt,0,45.60,0.00,red,

jacket,0,55.80,0.00,white,

pullover,0,130.90,0.00,white,

jacket,0,200.90,0.00,blue,

skirt,0,89.90,0.00,brown,

alpaca pullover,0,230.00,0.00,brown,

lambswool pullover,0,130.00,0.00,yellow,

I dont care the style, I only need that the Order of the columns be just like the Table in the view. I read somewhere that I have to use ArrayList instead of Map, but if I change in all the program Map for ArrayList When I wanna open the file say that I cannot open/read the file.

How can I solve this ?

[Former Member · Accepted solution]

Hi Rodrigo,

Use TreeMap instead of hashmap. it should solve ur problem.

regards

Shyam.

[BeGanz]

Hallo Wingoal,

Look at the NW 7.0 version of my Excel Export tutorial on SDN: <a href="https://wiki.sdn.sap.com/wiki/x/0mQ">Exporting Table Data Using On-Demand Streams - SAP NetWeaver 7.0</a>:

Use <b>LinkedHashMap</b> instead of <i>HashMap</i>:

  private Map getProductColumnInfos() {
    Map columnInfosMap = new LinkedHashMap();
    columnInfosMap.put(IPrivateTableCompBasketView.IProductsElement.QUANTITY, "Quantity");
    columnInfosMap.put(IPrivateTableCompBasketView.IProductsElement.ARTICLE, "Article");
    columnInfosMap.put(IPrivateTableCompBasketView.IProductsElement.COLOR, "Color");
    columnInfosMap.put(IPrivateTableCompBasketView.IProductsElement.PRICE, "Price in EURO");
    columnInfosMap.put(
      IPrivateTableCompBasketView.IProductsElement.TOTAL__PER__ARTICLE,
      "Total Per Article In Euro");
    return columnInfosMap;
  }

This keeps the order of key-displaytext-pairs passed by the client (table component) to the service (excel export component) stable.

What's still not solved there is the "Keep spacial characters in column header texts" problem.

Regards, Bertram