分别使用POI和JXL导出数据到Excel

1.使用POI

引入jar包

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!-- poi HSSF is our port of the Microsoft Excel 97(-2007) file format (BIFF8) to pure Java. -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<!-- poi-ooxml XSSF is our port of the Microsoft Excel XML (2007+) file format (OOXML) to pure Java -->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.17</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.17</version>
      </dependency>

　　

ExcelExport.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author Sue
 * @create 2019-04-29 14:38
 **/
@Controller
public class ExcelExport {
 
    @RequestMapping(value = "/execute", method = RequestMethod.GET)
    public void execute(HttpServletRequest request, HttpServletResponse response) throws IOException {
 
        //待导出的数据
        List<ReportInfo> reportInfoList = new ArrayList<>();
        reportInfoList.add(new ReportInfo("上海卡部", "this week 01", "next week 01"));
        reportInfoList.add(new ReportInfo("广州卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("厦门卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("宁波卡部", "this week 05", "next week 05"));
        reportInfoList.add(new ReportInfo("武汉卡部", "this week 05", "next week 05"));
 
        // 导出.xlsx文件使用这个对象
        // XSSFWorkbook work = null;
 
        HSSFWorkbook workbook = null;
        try {
            //创建一个空白的workbook
            workbook = new HSSFWorkbook();
            //建立新的sheet对象（excel的表单）
            HSSFSheet sheet = workbook.createSheet("周报信息表");
            //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(0);
            //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            HSSFCell cell = row1.createCell(0);
 
            //创建样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置单元格样式
            cell.setCellStyle(cellStyle);
            //设置单元格内容
            cell.setCellValue("周报信息一览表");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
            //在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(1);
            //创建单元格并设置单元格内容
            row2.createCell(0).setCellValue("业务条线");
            row2.createCell(1).setCellValue("本周工作成果");
            row2.createCell(2).setCellValue("下周工作计划");
            //在sheet里创建第三行
            HSSFCellStyle cellStyle1 = workbook.createCellStyle();
            //自动换行
            cellStyle1.setWrapText(true);
            for (int i = 0; i < reportInfoList.size(); i++) {
                HSSFRow row = sheet.createRow(2 + i);
 
                HSSFCell cell0 = row.createCell(0);
                cell0.setCellStyle(cellStyle1);
                cell0.setCellValue(reportInfoList.get(i).getName());
 
                HSSFCell cell1 = row.createCell(1);
                cell1.setCellStyle(cellStyle1);
                cell1.setCellValue(reportInfoList.get(i).getThisWeek());
 
                HSSFCell cell2 = row.createCell(2);
                cell2.setCellStyle(cellStyle1);
                cell2.setCellValue(reportInfoList.get(i).getNextWeek());
            }
            //输出Excel文件
            response.reset();
            response.setContentType("application/octet-stream;charset=utf-8");
            String fileName = "周报信息导出表" + ".xls";
            OutputStream os = response.getOutputStream();
            response.reset();//清空输出流
            String finalFileName = URLEncoder.encode(fileName, "UTF8");
            //这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
            response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
            response.setContentType("application/vnd.ms-excel");
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            throw new IOException();
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
    }
}

ReportInfo.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/**
 * @author Sue
 * @create 2019-04-29 14:47
 **/
public class ReportInfo {
    private String name;
    private String thisWeek;
    private String nextWeek;
 
    public ReportInfo(String name, String thisWeek, String nextWeek) {
        this.name = name;
        this.thisWeek = thisWeek;
        this.nextWeek = nextWeek;
    }
 
    public ReportInfo() {
 
    }
 
    public String getName() {
 
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getThisWeek() {
        return thisWeek;
    }
 
    public void setThisWeek(String thisWeek) {
        this.thisWeek = thisWeek;
    }
 
    public String getNextWeek() {
        return nextWeek;
    }
 
    public void setNextWeek(String nextWeek) {
        this.nextWeek = nextWeek;
    }
}

访问IP测试

2.使用JXL

引入jar包

?
1
2
3
4
5
6
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

TestExcel.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.JxlWriteException;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
 
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
 
/**
 * @author Sue
 * @create 2019-04-29 10:39
 **/
@RestController
public class TestExcel {
 
    /**
     * 测试 导出xls文件的表头
     */
    public static final String[] TestToXls = {"编号", "设计人员工号", "设计人员姓名", "开发人员工号", "开发人员姓名", "测试人员工号", "测试人员姓名"};
 
    @GetMapping("/test")
    public void toExcel(HttpServletResponse response, HttpServletRequest request) throws Exception {
 
        List<Test> listTest = new ArrayList<Test>();
        Test test = new Test("1", "01", "001", "0001", "00001", "000001", "0000001");
        listTest.add(test);
 
        //这里为导出文件存放的路径
        String filePath = "D:\\sheet\\";
        //加入一个uuid随机数是因为
        //每次导出的时候，如果文件存在了，会将其覆盖掉，这里是保存所有的文件
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
 
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy年MM月dd HH时mm分ss秒");
        // 给要导出的文件起名为 "测试导出数据表_时间.xls"
        String filePath2 = filePath + "数据表" + "-" + fmt.format(new Date()) + ".xls";
        WritableWorkbook wb = null;
        try {
            File file2 = new File(filePath2);
            if (!file2.exists()) {//不存在，创建
                file2.createNewFile();
            }
            wb = Workbook.createWorkbook(file2);//创建xls表格文件
 
            // 表头显示
            WritableCellFormat wcf = new WritableCellFormat();
            wcf.setAlignment(Alignment.CENTRE);// 水平居中
            wcf.setWrap(true);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcf.setFont(new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD));// 表头字体 加粗 13号
            wcf.setBackground(jxl.format.Colour.PERIWINKLE);
            // 内容显示
            WritableCellFormat wcf2 = new WritableCellFormat();
            wcf2.setWrap(true);//设置单元格可以换行
            wcf2.setAlignment(Alignment.CENTRE);//水平居中
            wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中
            wcf2.setFont(new WritableFont(WritableFont.TIMES, 11));// 内容字体 11号
 
            //导出的xls的第一页，第二页就是0换成1，“sheet1”，也可以修改为自己想要的显示的内容
            WritableSheet ws = wb.createSheet("sheet1", 0);
            //WritableSheet ws2 = wb.createSheet("sheet2", 1);//第2个sheet页
            ws.addCell(new Label(0, 0, "导出结果"));//代表着表格中第一列的第一行显示查询结果几个字
 
            // 导出时生成表头
            for (int i = 0; i < TestToXls.length; i++) {
                //i,代表的第几列，1，代表第2行，第三个参数为要显示的内容，第四个参数，为内容格式设置（按照wcf的格式显示）
                ws.addCell(new Label(i, 1, TestToXls[i], wcf));//在sheet1中循环加入表头
            }
 
            int k = 2;//从第三行开始写入数据
 
            for (int i = 0; i < listTest.size(); i++) {
                ws.addCell(new Label(0, k, listTest.get(i).getIdd(), wcf2));
                ws.addCell(new Label(1, k, listTest.get(i).getDesignId(), wcf2));
                ws.addCell(new Label(2, k, listTest.get(i).getDesignName(), wcf2));
                ws.addCell(new Label(3, k, listTest.get(i).getDevelopId(), wcf2));
                ws.addCell(new Label(4, k, listTest.get(i).getDevelopName(), wcf2));
                ws.addCell(new Label(5, k, listTest.get(i).getTestId(), wcf2));
                ws.addCell(new Label(6, k, listTest.get(i).getTestName(), wcf2));
                //ws.mergeCells(4, 5, 5, 5);//合并两列，按参数顺序，意思是第4列的第五行，跟第五列的第五行合并为一个单元格
                k++;
            }
            wb.write();//写入，到这里已经生成完成，可以在相应目录下找到刚才生成的文件
        } catch (IOException e) {
            e.printStackTrace();
        } catch (JxlWriteException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //这个是我们项目中，是把刚才生成的文件，响应到前台，进行下载、保存，可省略。
        downLoadFile(filePath2, response);
    }
 
    public void downLoadFile(String filePath, HttpServletResponse response) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy年MM月dd HH时mm分ss秒");
 
        FileInputStream in = null;
        ServletOutputStream out = null;
        BufferedOutputStream toOut = null;
        String fileName = "导出数据表" + "-" + fmt.format(new Date()) + ".xls";
        try {
            in = new FileInputStream(new File(filePath));
            byte[] buffer = new byte[in.available()];
            while (in.read(buffer) != -1) {
                //                HttpServletResponse response = this.getContext().getResponse();//从application中得到response
                response.reset();// 清空
                // 设置响应的文件的头文件格式
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso-8859-1"));
//                response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
                response.addHeader("Content-type", "application-download");
                // 获取响应的对象流
                out = response.getOutputStream();
                toOut = new BufferedOutputStream(out);
                toOut.write(buffer);
                toOut.flush();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
                if (out != null) {
                    out.close();
                }
                if (toOut != null) {
                    toOut.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
 
}

Test.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
/**
 * @author Sue
 * @create 2019-04-29 10:41
 **/
public class Test {
    private String idd;//编号
    private String designId;//设计人员工号
    private String designName;//设计人员姓名
    private String developId;//开发人员工号
    private String developName;//开发人员姓名
    private String testId;//测试人员工号
    private String testName;//测试人员姓名
 
    public Test(String idd, String designId, String designName, String developId, String developName, String testId, String testName) {
        this.idd = idd;
        this.designId = designId;
        this.designName = designName;
        this.developId = developId;
        this.developName = developName;
        this.testId = testId;
        this.testName = testName;
    }
 
    public Test() {
 
    }
 
    public String getIdd() {
        return idd;
    }
 
    public void setIdd(String idd) {
        this.idd = idd;
    }
 
    public String getDesignId() {
        return designId;
    }
 
    public void setDesignId(String designId) {
        this.designId = designId;
    }
 
    public String getDesignName() {
        return designName;
    }
 
    public void setDesignName(String designName) {
        this.designName = designName;
    }
 
    public String getDevelopId() {
        return developId;
    }
 
    public void setDevelopId(String developId) {
        this.developId = developId;
    }
 
    public String getDevelopName() {
        return developName;
    }
 
    public void setDevelopName(String developName) {
        this.developName = developName;
    }
 
    public String getTestId() {
        return testId;
    }
 
    public void setTestId(String testId) {
        this.testId = testId;
    }
 
    public String getTestName() {
        return testName;
    }
 
    public void setTestName(String testName) {
        this.testName = testName;
    }
}

 

访问IP测试即可

 

posted @   少说点话  阅读(478)  评论(0)  编辑  收藏  举报
编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
网站运行：7年51天17时24分35秒
点击右上角即可分享
微信分享提示

目录导航