POI实现excel的导入导出-[未采用反射实现的版本]

秒速五厘米 2022-06-01 01:44 170阅读 0赞

最近在做项目中遇到了使用POI导入导出excel的需求,所以在这里总结下，方便自己和朋友学习:  
1.经典博客:  
1.[JavaWEB–POI之EXCEL操作、优化、封装详解系列（一）–概述与原理][JavaWEB_POI_EXCEL]  
2.[公共POI导出Excel方法–java][POI_Excel_java]  
3.[Apache POI 实现对 Excel 文件读写][Apache POI _ Excel]  
4.[EasyPoi教程V1.0][EasyPoi_V1.0]

2.原理:  
原理如下图  
![这里写图片描述][SouthEast]

3.代码:  
POIController.java

package com.spring.boot.gp4zj.controller;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.spring.boot.gp4zj.service.POIService;
    
    import cn.lz.cloud.common.service.ClazzNote;
    import cn.lz.cloud.common.service.ServiceNote;
    
    /** * 各种poi导入导出的请求控制模块; * * @author:JunZhou * @Company:LongZheng * @Email:1769676159@qq.com * @2018年1月16日@下午4:28:49 */
    @RestController
    @RequestMapping("/poi")
    @ClazzNote(desc = "excel文件", resource = "input", modName = "poi")
    public class POIController { 
        @Autowired
        private POIService poiService;
    
        // 获取考勤明细的excel文件;
        @RequestMapping("/getAttenceDetailExcel")
        @ServiceNote(desc = "获取考勤明细的excel", auth = ServiceNote.AUTH.CHECK)
        public void getAttenceDetailExcel(HttpServletResponse response) {
            poiService.getAttenceDetailExcel(response);
    
        }
    
        // 读取考勤明细的excel文件;
        @RequestMapping("/readAttenceDetailData")
        @ServiceNote(desc = "获取考勤明细的excel", auth = ServiceNote.AUTH.CHECK)
        public void readAttenceDetailData() {
    
    
        }
    
    }

POIServiceImpl.java

package com.spring.boot.gp4zj.service.impl;
    
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.spring.boot.gp4zj.model.AttenceDetail;
    import com.spring.boot.gp4zj.model.SimulationVO;
    import com.spring.boot.gp4zj.service.AttenceDetailService;
    import com.spring.boot.gp4zj.service.POIService;
    import com.spring.boot.gp4zj.util.POIUtils;
    
    import cn.lz.cloud.common.service.ResList;
    
    /** * 获取excel的业务逻辑模块; * @author:JunZhou * @Company:LongZheng * @Email:1769676159@qq.com * @2018年1月16日@下午4:47:09 */
    @Service
    public class POIServiceImpl implements POIService {
    
        @Autowired
        private AttenceDetailService attenceDetailService;
        public void getAttenceDetailExcel(HttpServletResponse response) {
            // TODO Auto-generated method stub
            ResList<AttenceDetail>  list = attenceDetailService.selectAll(SimulationVO.getADSimulationVo());
            List<AttenceDetail> excelData = list.getList();
            HSSFWorkbook workbook = POIUtils.createExcel(excelData);
            OutputStream out = null;
            if (workbook != null) {
                try {
                    String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                    String headStr = "attachment; filename=\"" + fileName + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    out = response.getOutputStream();
                    workbook.write(out);
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        out.flush();
                        out.close();
                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
    
        }
    
    }

POIUtils.java

package com.spring.boot.gp4zj.util;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    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 com.spring.boot.gp4zj.model.AttenceDetail;
    
    /** * Excel操作相关的工具类; * * @author:JunZhou * @Company:LongZheng * @Email:1769676159@qq.com * @2018年1月16日@下午3:19:25 */
    public class POIUtils {
        public static void main(String[] args) {
            List<AttenceDetail> list = readExcel();
            /*for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i).toString()); }*/
            System.out.println(list.get(0).toString());
        }
        /** * 创建Excel * * @param list * 数据 */
        public static HSSFWorkbook createExcel(List<AttenceDetail> list) {
            // 创建一个Excel文件
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建一个工作表
            HSSFSheet sheet = workbook.createSheet("考勤明细表");
            // 添加表头行
            HSSFRow hssfRow = sheet.createRow(0);
            // 设置单元格格式居中
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 添加表头内容
            // -------第一行第0-2列---------
            HSSFCell headCell = hssfRow.createCell(0);
            headCell.setCellValue("考勤日期");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(1);
            headCell.setCellValue("考勤名称");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(2);
            headCell.setCellValue("员工编码");
            headCell.setCellStyle(cellStyle);
    
            // -----------------------
            headCell = hssfRow.createCell(3);
            headCell.setCellValue("出勤时长");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(4);
            headCell.setCellValue("加班时长");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(5);
            headCell.setCellValue("合计出勤时长");
            headCell.setCellStyle(cellStyle);
    
            // -----------------------
            headCell = hssfRow.createCell(6);
            headCell.setCellValue("报工单位");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(7);
            headCell.setCellValue("结算单位");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(8);
            headCell.setCellValue("创建人");
            headCell.setCellStyle(cellStyle);
    
            // -----------------------
            headCell = hssfRow.createCell(9);
            headCell.setCellValue("出勤人天");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(10);
            headCell.setCellValue("加班人天");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(11);
            headCell.setCellValue("合计人天");
            headCell.setCellStyle(cellStyle);
    
            // -----------------------
            headCell = hssfRow.createCell(12);
            headCell.setCellValue("是否删除汇总表");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(13);
            headCell.setCellValue("是否确认");
            headCell.setCellStyle(cellStyle);
    
            headCell = hssfRow.createCell(14);
            headCell.setCellValue("创建时间");
            headCell.setCellStyle(cellStyle);
    
            // -----------------------
            headCell = hssfRow.createCell(15);
            headCell.setCellValue("修改时间");
            headCell.setCellStyle(cellStyle);
    
            // 添加数据内容
            for (int i = 0; i < list.size(); i++) { 
                hssfRow = sheet.createRow((int) i + 1);
                AttenceDetail attenceDetail = list.get(i);
                // 创建单元格，并设置值
    
                HSSFCell cell = hssfRow.createCell(0);
                cell.setCellValue(attenceDetail.getAttencedTime());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(1);
                cell.setCellValue(attenceDetail.getAttencerName());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(2);
                cell.setCellValue(attenceDetail.getAttencerCode());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(3);
                cell.setCellValue(attenceDetail.getWorkTimeLen());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(4);
                cell.setCellValue(attenceDetail.getExtraTimeLen());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(5);
                cell.setCellValue(attenceDetail.getTotalTimeLen());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(6);
                cell.setCellValue(attenceDetail.getApplyCompany());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(7);
                cell.setCellValue(attenceDetail.getPayCompany());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(8);
                cell.setCellValue(attenceDetail.getCreator());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(9);
                cell.setCellValue(attenceDetail.getWorkDayNum());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(10);
                cell.setCellValue(attenceDetail.getExtraDayNum());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(11);
                cell.setCellValue(attenceDetail.getTotalDayNum());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(12);
                cell.setCellValue(attenceDetail.getIsDelGather());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(13);
                cell.setCellValue(attenceDetail.getIsConfirmed());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(14);
                cell.setCellValue(attenceDetail.getCreateTime());
                cell.setCellStyle(cellStyle);
    
                cell = hssfRow.createCell(15);
                cell.setCellValue(attenceDetail.getEditTime());
                cell.setCellStyle(cellStyle);
    
            }
    
            /*// 保存Excel文件 try { OutputStream outputStream = new FileOutputStream("D:/students.xls"); workbook.write(outputStream); outputStream.close(); } catch (Exception e) { e.printStackTrace(); }*/
            return workbook;
        }
    
        /** * 接收上传的excel并读取其中的信息; */
        public static List<AttenceDetail> readExcel(){
    
            List<AttenceDetail> list = new ArrayList<AttenceDetail>();
            HSSFWorkbook workbook = null;
            try {
                // 读取Excel文件
                InputStream inputStream = new FileInputStream("D:/Excel-095968073.xls");
                workbook = new HSSFWorkbook(inputStream);
                inputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
    
            // 循环工作表
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) { 
                HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                // 循环行
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
    
                    // 将单元格中的内容存入集合
                    AttenceDetail attenceDetail = new AttenceDetail();
    
                    HSSFCell cell = hssfRow.getCell(0);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setAttencedTime(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(1);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setAttencerName(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(2);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setAttencerCode(cell.getStringCellValue());
                    //-------------------------------
                    cell = hssfRow.getCell(3);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setWorkTimeLen(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(4);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setExtraTimeLen(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(5);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setTotalTimeLen(cell.getStringCellValue());
    
                  //-------------------------------
                    cell = hssfRow.getCell(6);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setApplyCompany(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(7);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setPayCompany(cell.getStringCellValue());
    
                  //-------------------------------
                    cell = hssfRow.getCell(8);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setCreator(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(9);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setWorkDayNum(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(10);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setExtraDayNum(cell.getStringCellValue());
    
                    //-------------------------------
                    cell = hssfRow.getCell(11);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setTotalDayNum(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(12);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setIsDelGather(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(13);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setIsConfirmed(cell.getStringCellValue());
    
                    cell = hssfRow.getCell(14);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setCreateTime(cell.getStringCellValue());
                  //-------------------------------
                    cell = hssfRow.getCell(15);
                    if (cell == null) {
                        continue;
                    }
                    attenceDetail.setEditTime(cell.getStringCellValue());
                    list.add(attenceDetail);
                }
            }
            return list;
        }
    }

这部份的代码由于没有采用反射实现，效率较高，但是代码冗余和灵活性不高，所以接下来将采用反射实现.。

[JavaWEB_POI_EXCEL]: http://blog.csdn.net/Jack__Frost/article/details/77498797
[POI_Excel_java]: http://blog.csdn.net/wangchangpen62/article/details/44410967
[Apache POI _ Excel]: https://www.cnblogs.com/java-class/p/5255024.html
[EasyPoi_V1.0]: http://easypoi.mydoc.io/
[SouthEast]: /images/20220601/42ef233e6a0d4a758b78370fcfc01fc2.png