How to update an Excel Sheet through a WebService

[Former Member]

Dear All,

Please help me with this query.

Kindly could you guide me on updating an Excel Sheet which is saved on my local server through a WebService which is deployed on the WebApp Server. The Server is located locally too. I have developed the WebService on the NWDS.

I have tried using POI and ODBC for updating the sheet. But it appears to me that the server is unable to fetch the excel sheet.

Please could you provide your valuable suggestions on this.

Regards,

Suyukti B N

[Former Member]

Jens,

Glad to see your fast reply.

I have tried the POI approach as well. I have a different issue all together with the POI. With POI I am able to only read the excel but not update it.

Will explain in detail for this case with POI:

1) The Excel sheet has been uploaded on KM repository on my local portal. I refer to this sheet while reading and updating.

The read and update happens with the Java Perspective as a Java Method only. However, once I deploy it as webservice, I am able to only read and not update the excel.

2) Please suggest if you have some sample code to update the excel sheet from a webservice?

Thank You.

Regards,

Suyukti B N

[Former Member]

Hi Suyukti,

what exceptions do you get, when you try to update your excel???

examples, as followed:

javadocs poi: http://poi.apache.org/apidocs/

example: http://poi.apache.org/hssf/quick-guide.html

simple example to set a value to a cell:

public void fillCell (HSSFWorkbook workbook,int sheet, int row, int col, String value)

{

HSSFSheet currentSheet;

HSSFRow currentRow;

HSSFCell currentCell;

currentSheet = workbook.getSheetAt(sheet);

if (currentSheet.getRow(row)!=null)

currentRow = currentSheet.getRow(row);

else

currentRow = currentSheet.createRow(row);

if (currentRow.getCell((short)col)!=null)

currentCell = currentRow.getCell((short)col);

else

currentCell = currentRow.createCell((short)col);

if (value != null)

currentCell.setCellValue(value);

else

currentCell.setCellValue("");

}

[Former Member]

Hi,

Thanks for your reply. Below are more details regarding my question. I am trying to achieve the requirement with :

Java WebDynpro (Front End) <--> WebService (Middle Tier) <->ODBC <---> Microsoft Excel (Back End)

1) I am Using the Java perspective to create the service method. The method has to read and update the excel sheet. I have created a ODBC DataSource on my local machine. I also have the SAP WebApp Server on my local machine. Below is the method I am using for "reading" the excel :

****************************

public String read(){

Connection connection = null;

String columnValue = "";

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:DS");

Statement st = con.createStatement();

ResultSet rs = st.executeQuery("Select * from [Sheet1$]");

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 1; i <= numberOfColumns; i++) {

if (i > 1) System.out.print(", "); columnValue = rs.getString(i);

}

System.out.println("value is" + columnValue);

}

st.close();

con.close();

} catch (Exception ex) {

System.err.print("Exception: ");

System.err.println(ex.getMessage());

return "false" + ex.toString();

}

return "true";

}

****************************

This code works perfectly fine and provides me the values that are present on the Excel Sheet.

2) I now switch over from the Java Perspective to the WebService Perspective on my NetWeaver Developer Studio. I create a new WebService for the above Java method.

3) I then create an Ear file and deploy on the Server.

4) Now when I click on Test WebService, the values that are returned from the Excel are "null". It shows return String as False and an SQL Exception. java.sql.SQLException: General error.

Hence I concluded that after deploying the Service on the Server, the Excel Sheet is not being fetched or read.

Please could you provide your thoughts on this.

Thank You Very Much,

Warm Regards,

Suyukti B N

[Former Member]

hi Suyukti,

why don't you use java frameworks like jexcelapi or poi instead of your obdc datasource aproach?

example for using poi: /people/perumal.kanthan/blog/2005/03/21/reading-excel-data-from-java-using-hssf-api

regards, jens

[Former Member]

Hi Suyukti,

what means "But it appears to me that the server is unable to fetch the excel sheet"?

any exceptions?

can u provide more information about your "server fetch" behaviour.

regards, jens