Java中用poi技术将报表导出成excel表

在这里,用的技术是poi技术,框架是springBoot+Maven,有需要的参考下

(1)、首先,先引入maven所需的依赖包,我这里用的是3.16版本

[XML] 纯文本查看 复制代码
?

<font style="color:rgb(79, 79, 79)"><font face="&quot;"><font style="font-size:16px"><dependency>

        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>    
        <version>3.16</version>        

</dependency> </font></font></font>
(2)、引入创建excel表的工具类GradesXLS

[Java] 纯文本查看 复制代码
?

package com.xx.test.utils;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**

  • 导出XLS文件

*
*/
public class GradesXLS {

    private String path;

    public GradesXLS() {
            super();
    }
     
    //构造函数获取保存excel路径
    public GradesXLS(String path) {
            super();
            this.path = path;
    }
     
    public Boolean CreateExcel(List<Object[]> dataList, String[] column, String clientName, HttpServletResponse response) throws Exception {
            //创建一个工作簿
            HSSFWorkbook workBook = new HSSFWorkbook();
            //创建一个工作表,名为:第一页
            HSSFSheet sheet = workBook.createSheet(clientName);
            //设置单元格的宽度(0:表示第一行的第一个单元格,1:第一行的第二个单元格)
            sheet.setColumnWidth((short) 0, 3500);
            sheet.setColumnWidth((short) 1, 5000);
            sheet.setColumnWidth((short) 2, 5000);
            //创建一个单元格,从0开始
            HSSFRow row = sheet.createRow((short) 0);
            //构造一个数组设置第一行之后的单元格
            HSSFCell[] cell = new HSSFCell[column.length];
            for (int i = 0; i < column.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellValue(column);
            }
             
            //获得从数据库中查询出来的数据
            if (dataList != null && dataList.size() > 0) {
                    //循环list中的数据
                    for (int i = 0; i < dataList.size(); i++) {
                            Object[] objs = dataList.get(i);
                            HSSFRow dataRow = sheet.createRow(i + 1);
                            HSSFCell data[] = new HSSFCell[column.length];
                            for (int j = 0; j < column.length; j++) {
                                    data[j] = dataRow.createCell(j);
                                    String info = String.valueOf(objs[j]);
                                    System.out.println("GradesXLS:" + info);
                                    data[j].setCellValue((info == null) ? "" : info);
                            }
                     }
                     
                    try {

// String fileName = clientName + "-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";

                            //设置日期格式
                            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            // new Date()为获取当前系统时间,也可使用当前时间戳
                            String date = df.format(new Date());
                            String fileName = clientName + "-" + date + ".xls";//文件名,客户端名+日期+文件后缀名
                            response.setContentType("application/vnd.ms-excel");//设置内容格式
                            response.setCharacterEncoding("utf-8");//设置编码格式
                            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gbk"), "iso8859-1"));
                            //用IO流来写进去
                            OutputStream os = response.getOutputStream();
                            workBook.write(os);
                            os.flush();
                            return true;
                    } catch (FileNotFoundException e) {
                            // TODO: handle exception
                            e.printStackTrace();
                            return false;
                    } catch (IOException e) {
                            // TODO: handle exception
                            e.printStackTrace();
                            return false;
                    }
            } 
            return false;
    }
     

}

(3)controller类

[Java] 纯文本查看 复制代码
?

@Autowired

    private SubjectService subjectService;

    /**
     * 成绩单
     */
     
    @RequestMapping(value="/subjectList")
    @ResponseBody
    public Object getSubjectList()  {
             
            return subjectService.getSubjectList();
             
    }

(4)最重要的service层来了

[Java] 纯文本查看 复制代码
?

//成绩单

    @Autowired
    private SubjectDao subjectDao;

//方法
public Object subjectExcel(HttpServletResponse response) {

            String[] column = {"学号","语文","数学","英语","体育"};
            String clientName = "成绩单";
     
            //数据组装
            ArrayList<Object[]> dataList = new ArrayList<>();
            List<SubjectOut> subjectList = subjectDao.subjectExcel(SubjectOut.class);//查询数据库表,将结果放入SubjectOut类中返回
            //判断查出来的数据是否为空
            if (subjectList != null && subjectList.size() > 0) {
                    GradesXLS xls = new GradesXLS();//实例化刚才的工具类   //循环添加数据
                    for (SubjectOut subjectInfo : subjectList) {
                            Object[] obj = {subjectInfo.getId(),subjectInfo.getChinese(),subjectInfo.getMath(),subjectInfo.getSports()};
                            dataList.add(obj);
                    }
                    try {
                            return xls.CreateExcel(dataList,column,clientName,response);//将工具类创建出的excel返会下载
                    } catch (Exception e) {
                            e.printStackTrace();
                    }
            }
            return null;
    }

(5)dao层略

(6)数据表

[SQL] 纯文本查看 复制代码
?

CREATE TABLE tb_subject (
id int(11) NOT NULL AUTO_INCREMENT,
chinese varchar(255) DEFAULT NULL,
math varchar(255) DEFAULT NULL,
english varchar(255) DEFAULT NULL,
sports varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;


-- Records of tb_subject


INSERT INTO tb_subject VALUES ('1', '98', '100', '87', '90');
INSERT INTO tb_subject VALUES ('2', '88', '98', '86', '100');
INSERT INTO tb_subject VALUES ('3', '84', '85', '86', '87');
INSERT INTO tb_subject VALUES ('4', '91', '92', '93', '94');
INSERT INTO tb_subject VALUES ('5', '84', '87', '89', '97');
INSERT INTO tb_subject VALUES ('6', '91', '94', '95', '92');
INSERT INTO tb_subject VALUES ('7', '94', '92', '97', '91');
INSERT INTO tb_subject VALUES ('8', '75', '78', '79', '90');
INSERT INTO tb_subject VALUES ('9', '84', '72', '73', '91');
INSERT INTO tb_subject VALUES ('10', '85', '81', '72', '73');

评论

暂无评论