JAVA:将数据(从数据库)导出到 excel 并将其发送到客户端 [英] JAVA : Exporting Data (from database) to excel and sending it to client side

问题描述

正如标题所暗示的，我需要将一些数据(我从数据库中获得的)放入一个excel表中，然后将其发送到客户端，以便用户可以保存、打开或取消操作.

As the title suggest, I need to put some data (which I have got from database) into an excel sheet, and then send it to client side so that user can save , open or cancel the action.

我看过一些关于此的文章，最接近的是:如何让用户下载我的文件?(Java、MVC、Excel、POI).参考史蒂文斯提供的链接，我尝试了以下代码:

I have seen some articles regarding this, the closest one being : How can I get the user to download my file? (Java, MVC, Excel, POI). Referring to links provided by Stevens I tried out the following code :

public String execute(){
    setContentDisposition("attachment; filename=\"" + ename + "\"");
    try{
        ServletContext servletContext = ServletActionContext.getServletContext();
        String filePath = servletContext.getRealPath("/WEB-INF/template/excel/mytemplate.xls");
        File file = new File(filePath);
        Workbook wb = WorkbookFactory.create(new FileInputStream(file));
        Sheet sheet = wb.getSheetAt(0);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        InputStream excelStream;
        excelStream = new ByteArrayInputStream(baos.toByteArray());
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    return SUCCESS;
}

这里首先没有定义 WorkbookFactory.其次，我无法正确理解代码是如何工作的.

Here firstly WorkbookFactory is not defined. Secondly, I could not understand properly how the code is working.

我还找到了这个链接:http://www.roseindia.net/answers/viewqa/Java-Beginners/14930-How-to-export-data-from-database-to-excel-sheet-by-using-java--in-standalone-project.html.但是这里excel文件保存在服务器上.我希望文件不应该保存在服务器端，它应该直接到客户端

I also found this link : http://www.roseindia.net/answers/viewqa/Java-Beginners/14930-How-to-export-data-from-database-to-excel-sheet-by-using-java--in-standalone-project.html. But here the excel file gets saved on the server. I want that the file should not be saved on the server side, it should directly go to client side

(如果有帮助)我正在使用:struts 2 框架，休眠

(If it helps) I am using : struts 2 framework, hibernate

我愿意使用其他东西，例如 POI API、jQuery 或任何其他好东西.

I am open to using other things like POI API, jQuery or any other good stuff.

由于某种原因，我无法使用 displayTag.

I can not use displayTag for some reason.

Javascript 将是我最后的手段(尽管我已经用它实现了)，因为它需要更改浏览器的一些默认安全设置(如果可以避免这种情况，我也愿意使用 javascript).

Javascript would be my last resort (although I have implemented with it) because it requires changing some default security settings of the browser (If this can be avoided I am open to javascript as well).

请告知我现在应该如何处理.

Please advise how should I go about this now.

谢谢！！

    <result-types>
        <result-type name="jsp" class="org.apache.struts2.views.jsp"/>
        <result-type name="tiles" class="org.apache.struts2.views.tiles.TilesResult"/>
        <result-type name="stream" class="org.apache.struts2.dispatcher.StreamResult"/>
    </result-types>
    <action name="myActionName" class="package.myActionClass">
         <result type="stream">
            <param name="contentType">"application/vnd.ms-excel"</param>
            <param name="inputName">excelStream</param>
            <param name="contentDisposition">contentDisposition</param>
            <param name="bufferSize">1024</param>
         </result>
    </action>

执行动作时出错:

java.lang.reflect.InvocationTargetException

java.lang.IncompatibleClassChangeError: Class org.apache.poi.hssf.usermodel.HSSFWorkbook does not implement the requested interface org.apache.poi.ss.usermodel.Workbook

推荐答案

好的.所以最后我克服了所有障碍，并找到了一种方法来做到这一点.

Okay. So finally I am through with all the roadblocks and have figured out a way to do this.

我意识到我面临的问题不在于创建 excel 文件，问题在于将其发送到客户端，而且没有在服务器上创建文件或临时文件.

I realized that the problem I was facing was not in creating the excel file, the problem was sending it to client side, and that too without creating a file or temporary file on the server.

这里是如何去做(我从我的原始代码中删除了细节，以便您可以轻松理解它).

So here is how to go about it (I have ripped off details from my original code so that you can easily understand it).

在操作文件中，您首先必须创建一个 HSSFWorkbook 对象，将数据放在其上，然后不将其保存到服务器上的磁盘，使用输入流将其发送到客户端.

In the action file you first have to create a HSSFWorkbook object, put data on it and then without saving it to disk on server, send it to client using inputstream.

动作文件代码:

public String execute(){

    setContentDisposition("attachment; filename=\"" + ename + ".xls\"");

    try{
        HSSFWorkbook hwb=new HSSFWorkbook();
        HSSFSheet sheet =  hwb.createSheet("new sheet");

        //////You can repeat this part using for or while to create multiple rows//////
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setValue("col0");
            row.createCell(1).setValue("col1");
            row.createCell(2).setValue("col2");
            row.createCell(3).setValue("col3");
            .
            .
            .
        ///////////////////////////////////////////////////////////////////////////////

        ///////////////////////////////////////////////////////////////////////////////
        //////Now you are ready with the HSSFworkbook object to be sent to client//////
        ///////////////////////////////////////////////////////////////////////////////

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        hwb.write(baos);
        excelStream = new ByteArrayInputStream(baos.toByteArray());

        ///////////////////////////////////////////////////////////////////////////////
        ////Here HSSFWorkbook object is sent directly to client w/o saving on server///
        ///////////////////////////////////////////////////////////////////////////////
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    return SUCCESS;
}

现在在 struts-config 文件中只写(注意 excelStream & contentDisposition 已经在动作本身中设置，这里的结果类型是 org.apache.struts2.dispatcher.StreamResult):

Now in the struts-config file just write (note that excelStream & contentDisposition has been set in the action itself also the result-type here is org.apache.struts2.dispatcher.StreamResult):

    <action name="actionName" class="actionClass">
        <result type="stream">
            <param name="contentType">"application/vnd.ms-excel"</param>
            <param name="inputName">excelStream</param>
            <param name="contentDisposition">contentDisposition</param>
            <param name="bufferSize">1024</param>
        </result>
    </action>

就是这样.现在，当执行操作时，将提示用户保存或打开文件.

Thats it. Now when the action is executed, the user will be prompted to save or open the file.

:)

这篇关于JAVA:将数据(从数据库)导出到 excel 并将其发送到客户端的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！