Java中使用jxl.jar将数据导出为excel文件

小咪咪 2022-09-11 14:19 66阅读 0赞

Java对Excel文件的读写操作可由jxl.jar或poi.jar实现，这里使用**jxl.jar**完成对Excel文件的导出。

## 一、将Excel文件导出在本地 ##

步骤：  
  创建文件 -> 创建工作簿 -> 创建Sheet -> 加入数据 -> 写入文件。

示意图

![e22fa63ed50eba1f37789de14b69ce61.png][]

示例：由指定的数组生成Excel文件，导出在D盘根目录下，文件名为‘ExportExcelToLocal.xls’。  
实现程序

package cn.exporttoexceldemo;
    
    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    
    import java.io.File;
    
    public class ExportExcelToLocal {
        public static void main(String[] args) {
            String title[] = {"工号","姓名","部门"};
            String content[][] = {
                    {"001","乔峰","财务部"},
                    {"002","段誉","销售部"},
                    {"003","虚竹","采购部"},
                    {"004","张无忌","研发部"},
                    {"005","黄蓉","人力资源部"}
            };
    
            //1.创建一个文件
            File file = new File("D:\\ExportExcelToLocal.xls");
    
            try {
                //2.创建WritableWorkbook工作簿对象，并将其接入创建的文件
                WritableWorkbook writableWorkbook = Workbook.createWorkbook(file);
    
                //3.创建一个Sheet，第一个参数是sheet的名称，第二个参数是索引号
                //（可通过WritableFont、WritableCellFormat等方法对单元格的属性进行设置）
                WritableSheet writableSheet = writableWorkbook.createSheet("sheet1", 0);
    
                //4.在sheet中加入数据
                for (int i = 0; i < title.length; i++) { //标题行
                    writableSheet.addCell(new Label(i,0,title[i])); //new Label(列号，行号，内容)
                }
                for (int i = 0; i < content.length; i++) { //内容
                    for (int j = 0; j < content[0].length; j++){
                        writableSheet.addCell(new Label(j, i+1, content[i][j]));
                    }
                }
    
                //5.对WritableWorkbook对象执行写入操作
                writableWorkbook.write();
                writableWorkbook.close(); //关闭writableWorkbook
    
            }catch (Exception e){
                e.printStackTrace();
            }
    
        }
    }

生成的excel文件的内容

![1093671563daff947c381e6d590a72c2.png][]

## 二、将Excel文件导出在客户端，由浏览器下载 ##

实现生成并由浏览器下载Excel文件的功能有两种方式：1.先将Excel文件导出在服务器磁盘上，再由客户端下载；2.直接导出至输出流，由浏览器下载为Excel文件。这里演示直接将数据内容导出至输出流，完成由浏览器下载为Excel文件的功能。

步骤：定义输出流 ->定义工作簿-> 输出流接入网络响应 -> 工作簿接入输出流 -> 创建sheet -> 加入数据 -> 写入输出流。

示意图

![7a37ca3cf87c8b70da65bdfaee40ad9e.png][]

示例：将指定数组的数据导出至输出流，由浏览器下载为Excel文件，文件名为‘ExportExcelToClient.xls’。

实现程序

package cn.exporttoexceldemo;
    
    import jxl.Workbook;
    import jxl.write.Label;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    
    import javax.servlet.ServletException;
    import javax.servlet.ServletOutputStream;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    
    @WebServlet("/ExportExcelToClientServlet")
    public class ExportExcelToClientServlet extends HttpServlet {
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    
            String title[] = {"工号","姓名","部门"};
            String content[][] = {
                    {"001","乔峰","财务部"},
                    {"002","段誉","销售部"},
                    {"003","虚竹","采购部"},
                    {"004","张无忌","研发部"},
                    {"005","黄蓉","人力资源部"}
            };
            String fileName = "ExportExcelToClient.xls"; //定义文件名
    
            //设置编码
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            try {
                if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){ //IE浏览器
                    fileName = URLEncoder.encode(fileName,"UTF-8");
                }else {
                    fileName = new String(fileName.getBytes("UTF-8"),"ISO8859-1");
                }
                response.setHeader("Content-Disposition", "p_w_upload; filename="+fileName);
            }catch (UnsupportedEncodingException e){
                e.printStackTrace();
            }
    
            //1.定义一个输出流对象
            ServletOutputStream servletOutputStream = null;
    
            //2.定义一个WritableWorkbook工作簿对象
            WritableWorkbook writableWorkbook = null;
    
            try {
                //3.将输出流对象接入响应端response
                servletOutputStream = response.getOutputStream();
    
                //4.将工作簿对象接入输出流对象
                writableWorkbook = Workbook.createWorkbook(servletOutputStream);
    
                //5.创建一个Sheet，第一个参数是sheet的名称，第二个参数是索引号
                WritableSheet writableSheet = writableWorkbook.createSheet("sheet1",0);
                
                //6.在sheet中加入数据
                for (int i = 0; i < title.length; i++) { //标题行
                    writableSheet.addCell(new Label(i,0,title[i]));
                }
                for (int i = 0; i < content.length; i++) { //内容
                    for (int j = 0; j < content[0].length; j++){
                        writableSheet.addCell(new Label(j, i+1, content[i][j]));
                    }
                }
    
                //7.对工作簿对象执行写入操作
                writableWorkbook.write();
                servletOutputStream.flush();
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                try {
                    writableWorkbook.close();
                    servletOutputStream.close();
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
    
        }
    
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            this.doPost(request, response);
        }
    }

生成的excel文件[java教程][java]的内容

![26ff060d2c21d186c0b9f398e7a5dd9a.png][]

End.

[e22fa63ed50eba1f37789de14b69ce61.png]: /images/20220828/26c0478bba3946afbd0bce52b0ba46f6.png
[1093671563daff947c381e6d590a72c2.png]: /images/20220828/965f3a46ca104e61978eaddb203d4e31.png
[7a37ca3cf87c8b70da65bdfaee40ad9e.png]: /images/20220828/c0695d8b64d9431ab879f80e78ef34fc.png
[java]: https://www.xin3721.com/eschool/Javaxin3721/
[26ff060d2c21d186c0b9f398e7a5dd9a.png]: /images/20220828/c550b98681e6405bbe809f0367c7ddc0.png