SpringBoot·Excel上传下载

Excel导入导出-整合poi

1. 需要导出的字段名-充当Excel头部字段名列表
2. 拉取需要导出的数据列表
3. 根据确定的字段以及数据列表构造list<map>
4. 最终将构造好的数据写入excel的sheet中
5. 将最终的excel实例-workbook以流的形式写回浏览器response

方法一：

注意：数据类型需要转换成string类型在导出 如：日期类型需要转换成string类型再输出，同时需要在配置文件中开启驼峰命名，不然会报空指针（**因为数据库表字段里有下划线**）

application.properties文件中配置开启驼峰命名，不然查询数据库表时数据会为null值
mybatis.configuration.map-underscore-to-camel-case=true

<!-- 表格导出依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.xmlunit</groupId>
    <artifactId>xmlunit-core</artifactId>
</dependency>

表的实体类

public class Student {
    private Integer id;
    private String name;
    private String dopn;
    private Date createTime;
    private Date updateTime;
    
 省略get、set、toString方法……
}

mapper接口

@Mapper
public interface StudentMapp {
        @Select("select * from student")
        public List<Student> stud();
    }

service层

@Service
public class StuService {
    @Resource
    public StudentMapp studentMapp;
    public List<Student> stud() {
        return studentMapp.stud();
    }
}

控制层·将数据库数据导出excel

@Controller
@RequestMapping("/")
public class StudentExcelsud {
    @Resource
    public StudentMapp studentMapp;

    @RequestMapping(value = "/UserExcelDownloads", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建HSSFWorkbook对象,  excel的文档对象
        HSSFSheet sheet = workbook.createSheet("信息表"); //excel的表单

        List<Student> classmateList = studentMapp.stud();

        System.out.println("方法2");


        String fileName = "userinf"  + ".xls";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        String[] headers = { "id", "姓名", "号码", "创建时间","更新时间"};
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //在表中存放查询到的数据放入对应的列
        for (Student student : classmateList) {
			// 注意：日期类型需要转换成string类型再输出，同时需要在配置文件中开启驼峰命名，不然会报空指针
            DateFormat str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createTime1 = str.format( student.getCreateTime() );
            String updateTime1 = str.format( student.getUpdateTime() );

            System.out.println("createTime1："+createTime1 +"updateTime1："+ updateTime1);

            row1.createCell(0).setCellValue(student.getId());
            row1.createCell(1).setCellValue(student.getName());
            row1.createCell(2).setCellValue(student.getDopn());
            row1.createCell(3).setCellValue(createTime1);
            row1.createCell(4).setCellValue(updateTime1);
            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());

        System.out.println("方法233333333");
    }
}

方法二：

数据表导出excle

注意：

在application.properties文件中需要配置通用信息，文件名后缀不要忘记
poi.product.excel.sheet.name=“信息表”
poi.product.excel.file.name=“第一个文件.xls”
方法2 也是需要先对数据类型进行转换成Sring类型导出excel

<!--poi excel处理-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

@Controller
public class Excelexpord {

    /**
     * 查询数据库要导出的表
     */
    @Autowired
    public StudentMapper stutdentMapper;
    @Autowired
    public PoiService poiService ;
    @Autowired // 获取自定义信息
    private Environment env;


    @Autowired
    public Export export;
    // 要等出的数据查询
    @ResponseBody 
    @RequestMapping(value = "/biao1",method = RequestMethod.GET)
    public BaseResponse list(){
        BaseResponse response=new BaseResponse(StatusCode.Success);

            List<Student> products=stutdentMapper.selectAll();
            response.setData(products);
            System.out.println("打印结果："+products);
        return response;
    }

    /**
     * 导出Excel
     */
  @RequestMapping(value = "/export",method = RequestMethod.GET)
    public @ResponseBody String export(String name, HttpServletResponse response){
        final String[] headers=new String[]{"id","名字","部门号","创建时间","更新时间"};
        List<Student> student=stutdentMapper.selectAll();
        try {
            if (student!=null && student.size()>0){
                //TODO：将产品信息列表list->list-map
                List<Map<Integer, Object>> listMap=ExcelService.manageProductList(student);

                //TODO：将list-map塞入真正的excel对应的workbook
                // 直接导出
                // Workbook wb=poiService.fillExcelSheetData(listMap,headers,env.getProperty("poi.product.excel.sheet.name"));
                // 分sheet导出
                Workbook wb=poiService.manageSheet(listMap,headers,env.getProperty("poi.product.excel.sheet.name"));

                //TODO：将excel实例以流的形式写回浏览器
                export.downloadExcel(response,wb,env.getProperty("poi.product.excel.file.name"));
                return env.getProperty("poi.product.excel.file.name");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

}

将数据库中查询出的数据存放到list列表中

@Service
public class ExcelService {

    private static final Logger log= LoggerFactory.getLogger(ExcelService.class);
    /**
     * 处理 信息列表 转化为list-map->以用于后续塞入excel的sheet中
     * @param
     * @return
     */
    // manageProductList()此方法需传入数据库查询的结果返回的对象 List<Student> student=stutdentMapper.selectAll() ;

    public static List<Map<Integer, Object>> manageProductList(final List<Student> student){
        //TODO：excel表头--"id","名字","号码","创建时间","更新时间"

        List<Map<Integer, Object>> listMap=new LinkedList<Map<Integer, Object>>();

        Map<Integer,Object> rowMap;
        for (Student p:student){
            rowMap= new HashMap<>();
            rowMap.put(0,p.getId());
            rowMap.put(1,p.getName());
            rowMap.put(2,p.getDopn());
            rowMap.put(3,p.getCreateTime());
            rowMap.put(4,p.getUpdateTime());
            listMap.add(rowMap);
        }
        return listMap;
    }

}

将生成的数据填充到excel中

@Service
public class PoiService {

    private static final Logger log= LoggerFactory.getLogger(PoiService.class);

    private static final SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 分sheet导出
     * @param dataList
     * @param headers
     * @param sheetName
     * @return
     */
        //TODO：dataList.subList(0,99)
        //TODO：截取 0-99 总数100 调用 fillExcelSheetData
        //TODO：截取 100-199 总数 100  调用 fillExcelSheetData
    public Workbook manageSheet(List<Map<Integer, Object>> dataList,String[] headers, String sheetName){
        final Integer sheetSize=2; // 每个sheet几条数据
        //final Integer sheetSize=env.getProperty("poi.product.excel.sheet.size",Integer.class);
        int dataTotal=dataList.size();

        int sheetTotal = (dataTotal%sheetSize==0)? dataTotal/sheetSize : (dataTotal/sheetSize + 1);
        int start=0;
        int end=sheetSize;

        List<Map<Integer, Object>> subList;
        Workbook wb=new HSSFWorkbook();

        for (int i=0;i<sheetTotal;i++){
            subList=dataList.subList(start,end);
            wb=this.fillExcelSheetDataV2(subList,headers,sheetName+"_"+(i+1),wb);

            start += sheetSize;
            end += sheetSize;
            if (end>=dataTotal){
                end=dataTotal;
            }
        }
        return wb;
    }


    /**
     * 填充数据到excel的sheet中
     * @param dataList
     * @param headers
     * @param sheetName
     */
  // 分多个sheet导出
    public Workbook fillExcelSheetDataV2(List<Map<Integer, Object>> dataList, String[] headers, String sheetName,Workbook wb){
   // 直接导出 需要将上面的方法注释掉
   // public Workbook fillExcelSheetData(List<Map<Integer, Object>> dataList, String[] headers, String sheetName){
   //     Workbook wb=new HSSFWorkbook();
        Sheet sheet=wb.createSheet(sheetName);

        //TODO：创建sheet的第一行数据-即excel的表头
        Row headerRow=sheet.createRow(0);
        for(int i=0;i<headers.length;i++){
            headerRow.createCell(i).setCellValue(headers[i]);
        }

        //TODO：从第二行开始塞入真正的数据列表
        int rowIndex=1;
        Row row;
        Object obj;

        for(Map<Integer, Object> rowMap:dataList){
            try {
                row=sheet.createRow(rowIndex++);

                //TODO：遍历表头行-每个key -> 取到实际的value
                for(int i=0;i<headers.length;i++){
                    obj=rowMap.get(i);

                    if (obj==null) {
                        row.createCell(i).setCellValue("");
                    }else if (obj instanceof Date) {
                        String tempDate=simpleDateFormat.format((Date)obj);
                        row.createCell(i).setCellValue((tempDate==null)?"":tempDate);
                    }else {
                        row.createCell(i).setCellValue(String.valueOf(obj));
                    }
                }
            } catch (Exception e) {
                log.debug("excel sheet填充数据 发生异常： ",e.fillInStackTrace());
            }
        }

        return wb;
    }

}

将最终生成的excel以流的形式写出到浏览器

@Service
public class Export {

    /**
     * 下载Excel
     * @param response
     * @param wb
     * @param fileName
     * @throws Exception
     */
    public static void downloadExcel(HttpServletResponse response, Workbook wb, String fileName) throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso-8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

}

---

---

---