How to create a excel spread sheet on client side

Question

I am creating a web app where I want to show a excel icon in my results. When user clicks on icon, it should open up a spread sheet on clients machine with some data sent by server (clients will have excel installed).

I wrote some code to create excel on my local machine from the webservice.

public class App 
{
  public static void main( String[] args )
{
   try {
    URL oracle = new URL("http://someService.com");
     URLConnection yc =null;
        yc = oracle.openConnection();
            //Get the workbook instance for XLS file 

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Sample sheet");
             BufferedReader in = new BufferedReader(
                     new InputStreamReader(
                     yc.getInputStream()));
             String inputLine;
             int rowNum =0;
                while ((inputLine = in.readLine()) != null) {
                    Row row = sheet.createRow(rowNum++);
                    String[] coloumns = inputLine.split("\t");
                    int cellNum =0;
                    for(String coloumn: coloumns){
                        coloumn.
                           Cell cell = row.createCell(cellNum++);
                           cell.setCellValue(coloumn);
                    }
                    System.out.println(inputLine);
        } 
                in.close();  
                FileOutputStream out = 
                        new FileOutputStream(new File("C:\\libraries\\new.xls"));
                workbook.write(out);
                out.close();
                System.out.println("Excel written successfully..");

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }




   }
   }

This works fine. All it does is it reads data coming from web service and creates a spreadsheet in my machine at C:\libraries\new.xls.

Now if I am on web app I want to open a spread sheet on clients machine. I don't have to save the sheet. Just open with the data.

How can I open spread sheet on client machine with this data from web service?

EDIT

Here is my new server code:

@RequestMapping(value = "/Excel")
    public void getFile(HttpServletResponse response){
        OutputStream out =null;
        try {
            out = response.getOutputStream();
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        response.setContentType("application/x-ms-excel");
         try {
                URL oracle = new URL("http://someService.com");
                 URLConnection yc =null;
                    yc = oracle.openConnection();
                        //Get the workbook instance for XLS file 
                    IOUtils.copy(yc.getInputStream(),out);
                    out.flush();
                            System.out.println("Excel written successfully..");

                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

}

So now i ran the web app and nothing happened? Should i do something on front end to invoke this stream.

You need to have this inside of a servlet or similar, not a main method. What app server/ framework are you using? You should start there. — Andres Olarte, Feb 27 '13 at 19:39
The above code is just some code to verify if apache poi can do the job. It does. Now i want to migrate this to my web app. But not able to figure out. how can i invoke excel on clients machine. — javaMan, Feb 27 '13 at 19:43
You cannot invoke excel on a client's machine. You can stream the excel file, and the user can decide to open it, save it, or cancel (Or the user's computer can be configured to always open). Have you created your action to stream the result to client? — Andres Olarte, Feb 27 '13 at 19:45
I dont know how i can stream the result. Can you show some code on how to do that? — javaMan, Feb 27 '13 at 19:48
You can do it more simply using this :: http://stackoverflow.com/a/16479713/1490962 Work on that example... — Bhushankumar Lilapara, May 30 '13 at 10:35

score 2 · Answer 1 · answered Feb 27 '13 at 19:51

2

You can do something like this inside a Spring MVC controller to send the file to the client's computer:

@RequestMapping(value = "/myexcelreport")
public void getFile( 
  HttpServletResponse response) {
  OutputStream out=response.getOutputStream();
  response.setContentType("application/x-ms-excel");
  ... Same code a before, just use out instead of a FileOutputStream , and don't close out.
  out.flush();

}

answered Feb 27 '13 at 19:51

Andres Olarte

4,380
3
24
45

so, now can i directly send the input stream i receive from web service. I am little confused. Can you explain what do u mean by "use out instead of FileOutputStream – javaMan Feb 27 '13 at 20:03
Your code looks correct at a first glance. What happens when a client hits the URL with their browser? Do you get exceptions on the server log? Errors on the client? – Andres Olarte Feb 27 '13 at 21:44
No errors. I didnt understand why this will work. Because i copied inputstream to output stream. But what is it doing afterwards. Should i do something to tell front end to do? I mean how is this stream going to front end. – javaMan Feb 27 '13 at 22:05
This is basically taking the data from your webservice at http://someService.com and sending the data to your client's browser. You need to open this with a browser, hitting http://{MYHOST}/Excel. How are you trying to access it? Or how are you expecting this would work? – Andres Olarte Feb 28 '13 at 13:56

How to create a excel spread sheet on client side

1 Answers1