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屋！