POI的简单使用

POI的导入和导出

本文代码使用的POI版本

 <!--poi引入-->
            <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>

导出功能

• 单行表头的导出

导出的工具类：

 import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.xssf.usermodel.*;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import static org.apache.poi.ss.usermodel.BorderStyle.THIN;
    
    /**
     * @author suchao
     * @version 1.0
     * @name
     * @description
     * @date 2017/10/19
     */
    public class ExportExcelsingleModel {
        /**
         * 显示的导出表的标题
         */
        private String title;
    
        /**
         * 导出表的列名
         */
        private String[] columnName;
    
        /**
         * 需要导出的数据集合
         */
        private List<Object[]> dataList = new ArrayList<Object[]>();
    
        /**
         * 输入流对象
         */
        private HttpServletRequest request;
    
        /**
         * 输出流对象
         */
        private HttpServletResponse response;
    
        /**
         * @param title 
         * @param columnName
         * @param dataList
         * @param request
         * @param response
         * @description 构造方法，传入要导出的数据
         */
        public ExportExcelsingleModel(String title, String[] columnName, List<Object[]> dataList, HttpServletRequest request, HttpServletResponse response) {
            this.dataList = dataList;
            this.columnName = columnName;
            this.title = title;
            this.request = request;
            this.response = response;
        }
    
    
        /**
         * @param
         * @return
         * @author suchao
         * @date 2021/03/28
         * @description 导出数据到excel
         */
        public void exports() throws Exception {
    
            try {
                // 创建工作簿对象
                XSSFWorkbook workbook = new XSSFWorkbook();
                // 创建工作表
                XSSFSheet sheet = workbook.createSheet(title);                     
    
                // 产生表格标题行
                XSSFRow rowm = sheet.createRow(0);
                XSSFCell cellHeader = rowm.createCell(0);
    
                //设置标题和单元格样式
                //获取列头样式对象
                XSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
                //单元格样式对象
                XSSFCellStyle style = this.getStyle(workbook);                    
    
    
                // 定义所需列数
                int columnNum = columnName.length;
    
    
                // 将列头设置到sheet的单元格中
                for (int n = 0; n < columnNum; n++) {
                    //创建列头对应个数的单元格
                    XSSFCell cellRowName = rowm.createCell(n);                  
                    cellRowName.setCellType(CellType.STRING);
                    //设置列头单元格的数据类型
                    XSSFRichTextString text = new XSSFRichTextString(columnName[n]);
                    //设置列头单元格的值
                    cellRowName.setCellValue(text);
                    //设置列头单元格样式
                    cellRowName.setCellStyle(columnTopStyle);                          
                }
    
                //将查询出的数据设置到sheet对应的单元格中
                //遍历每个对象
                for (int i = 0; i < dataList.size(); i++) {
                    Object[] obj = dataList.get(i);
                    //创建所需的行数
                    XSSFRow row = sheet.createRow(i + 1);
                    for (int j = 0; j < obj.length; j++) {
                        XSSFCell cell = null;   
                        cell = row.createCell(j);
                        //设置单元格的数据类型
                        cell.setCellType(CellType.STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());
                        }
                        //设置单元格样式
                        cell.setCellStyle(style);
    
                    }
                }
    
    
                //让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        XSSFRow currentRow;
                        //当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            //取得当前的单元格
                            XSSFCell currentCell = currentRow.getCell(colNum);
                            //如果当前单元格类型为字符串
                            if (currentCell.getCellTypeEnum() == CellType.STRING) {
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth < length) {
                                    //将单元格里面值大小作为列宽度
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    //再根据不同列单独做下处理
                    if (colNum == 0) {
                        sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                    } else {
                        sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                    }
                }
    
                String strName = title + ".xlsx";
                if (workbook != null) {
                    try {
                        String filenames = java.net.URLEncoder.encode(strName, "UTF-8");
                        String filename = new String(filenames.getBytes(), "UTF-8");
                        String headStr = "attachment; filename=\"" + filename + "\"";
                        response.setContentType("APPLICATION/OCTET-STREAM");
                        response.setHeader("Content-Disposition", headStr);
                        OutputStream out1 = response.getOutputStream();
                        workbook.write(out1);
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    
        /**
         * @param
         * @return
         * @author suchao
         * @date 2021/03/28
         * @description 标题行的单元格样式
         */
        public XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
    
            // 设置字体
    
            XSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short) 11);
            //字体加粗
            font.setBold(true);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            //设置底边框;
            style.setBorderBottom(THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;
            style.setBorderLeft(THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框;
            style.setBorderRight(THIN);
            //设置右边框颜色;
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框;
            style.setBorderTop(THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    
        /**
         * @param
         * @return
         * @author suchao
         * @date 2021/03/28
         * @description 列数据信息单元格样式
         */
        public XSSFCellStyle getStyle(XSSFWorkbook workbook) {
            // 设置字体
            XSSFFont font = workbook.createFont();
            //设置字体大小
            //font.setFontHeightInPoints((short)10);
            //字体加粗
            //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            XSSFCellStyle style = workbook.createCellStyle();
            //设置底边框;
            style.setBorderBottom(THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;
            style.setBorderLeft(THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框;
            style.setBorderRight(THIN);
            //设置右边框颜色;
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框;
            style.setBorderTop(THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    }

Controller层代码：

    /**
         * @param
         * @return
         * @author suchao
         * @date @date 2021/03/28
         * @description  Get请求，可以通过浏览器直接访问下载
         */
    @GetMapping("/exportExcelTwoRowsHead") 
        public void getSheetModel(
                HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ParseException {
    
            List<Object[]> dataList = new ArrayList<>();
            String title = "学生信息Excel模板";
            String[] columnName = {"学校","班级","姓名","性别","学校简介","学校电话"};
            // 这是导出模板的示例数据，dataList也可以是数据库中查询出的数据，进行导出
            String[] objs = new String[]{"神奇高中","2班","天一清","男","这是一所魔幻的高中","xxxxxxx"};
            dataList.add(objs);
    
            //实例化工具类
            ExportExcelsingleModel ex = new ExportExcelsingleModel(title, columnName, dataList, request, response);
            try {
                //导出excel
                ex.exports();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

• 两行表头导出为excel

导出的工具类：

    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.*;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import static org.apache.poi.ss.usermodel.BorderStyle.THIN;
    
    /**
     * @author suchao
     * @version 1.0
     * @name
     * @description
     * @date 2021/03/28
     */
    public class ExportExcelTwo {
        /**
         * 显示的导出表的标题
         */
        private String title;
    
        /**
         * 导出表的列名
         */
        private List<Object[]> columnName;
    
        /**
         * 需要导出的数据集合
         */
        private List<Object[]> dataList = new ArrayList<>();
    
        /**
         * 输入流对象
         */
        private HttpServletRequest request;
    
        /**
         * 输出流对象
         */
        private HttpServletResponse response;
    
        /**
         * @param title
         * @param columnName
         * @param dataList
         * @param request
         * @param response
         * @description 构造方法，传入要导出的数据
         */
        public ExportExcelTwo(String title, List<Object[]> columnName, List<Object[]> dataList, HttpServletRequest request, HttpServletResponse response) {
            this.dataList = dataList;
            this.columnName = columnName;
            this.title = title;
            this.request = request;
            this.response = response;
        }
    
    
        /**
         * 用于判断是否有重复表头，用来获取重复的下标，以便于合并单元格
         *
         * @param columnName
         * @return
         * @author suchao
         */
        public List getArrayRepeatIndex(String[] columnName) {
            List indexList = new ArrayList();
            // 对表头进行遍历，从左往右逐个比较
            for (int i = 0; i < columnName.length; i++) {
                String s = columnName[i];
                String sta = "无";
                int in = -1;
                for (int j = 0; j < columnName.length; j++) {
                    // 将某一个和其他所有的列名进行比较
                    if (columnName[j].equals(s)) {
                        in = j;
                        // 如果和前面的列名相等，停止比较
                        if (j < i) {
                            break;
                        }
                        // 如果比较到了本身，跳过然后往后比较
                        if (j == i) {
                            continue;
                        }
                        sta = "有";
                    }
                }
                // 如果有重复（需要合并单元格），将开始和结束的下标放入List集合中
                if (sta.equals("有")) {
                    indexList.add(i);
                    indexList.add(in);
                }
    
            }
            return indexList;
        }
    
        /**
         * @param
         * @return
         * @author suchao
         * @date @date 2021/03/28
         * @description 导出数据到excel
         */
        public void exports() throws Exception {
    
            try {
                // 创建工作簿对象
                XSSFWorkbook workbook = new XSSFWorkbook();
                // 创建工作表
                XSSFSheet sheet = workbook.createSheet(title);
    
                //设置标题和单元格样式
                //获取列头样式对象
                XSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
                //单元格样式对象
                XSSFCellStyle style = this.getStyle(workbook);
    
    
                // 定义所需列数
                int columnNum = columnName.get(0).length;
    
                // 将所有表头列名都放入单元格，然后下一步进行合并（这样是为了导入时表头验证比较方便）
                for (int i = 0; i < columnName.size(); i++) {
                    XSSFRow rowm = sheet.createRow(i);
                    // 将列头设置到sheet的单元格中
                    for (int n = 0; n < columnNum; n++) {
                        //创建列头对应个数的单元格
                        XSSFCell cellRowName = rowm.createCell(n);
                        //设置列头单元格的数据类型
                        cellRowName.setCellType(CellType.STRING);
    
                        XSSFRichTextString text = new XSSFRichTextString((columnName.get(i))[n].toString());
                        //设置列头单元格的值
                        cellRowName.setCellValue(text);
                        //设置列头单元格样式
                        cellRowName.setCellStyle(columnTopStyle);
                    }
    
                }
                
                // 只获取第一行表头，因为正常的表头不会第二行表头，还左右单元格合并
                List repeatList = this.getArrayRepeatIndex((String[]) columnName.get(0));
                // 左右单元格合并
                for (int k = 0; k < repeatList.size(); k = k + 2) {
                    int leftIndex = (int) repeatList.get(k);
                    int rightIndex = (int) repeatList.get(k + 1);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, leftIndex, rightIndex));
                }
    
                // 上下单元格合并
                for (int i = 0; i < columnNum; i++) {
                    if ((columnName.get(0))[i].equals((columnName.get(1))[i])) {
                        sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
    
                    }
                }
    
                //将查询出的数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {
                    //遍历每个对象
                    Object[] obj = dataList.get(i);
                    //创建所需的行数
                    XSSFRow row = sheet.createRow(i + 2);
                    for (int j = 0; j < obj.length; j++) {
                        XSSFCell cell = null;
                        cell = row.createCell(j);
                        //设置单元格的数据类型
                        cell.setCellType(CellType.STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());
                        }
                        //设置单元格样式
                        cell.setCellStyle(style);
    
                    }
                }
    
    
                //让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                        XSSFRow currentRow;
                        //当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            //取得当前的单元格
                            XSSFCell currentCell = currentRow.getCell(colNum);
                            //如果当前单元格类型为字符串
                            if (currentCell.getCellTypeEnum() == CellType.STRING) {
                                int length = currentCell.getStringCellValue().getBytes().length;
                                if (columnWidth < length) {
                                    //将单元格里面值大小作为列宽度
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    //再根据不同列单独做下处理（如果没这个需求可以注释掉）
                    if (colNum == 0) {
                        sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                    } else {
                        sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                    }
                }
    
                // 设置导出时excel的名字
                String strName = title + ".xlsx";
                if (workbook != null) {
                    try {
                        String filenames = java.net.URLEncoder.encode(strName, "UTF-8");
                        String filename = new String(filenames.getBytes(), "UTF-8");
                        String headStr = "attachment; filename=\"" + filename + "\"";
                        response.setContentType("APPLICATION/OCTET-STREAM");
                        response.setHeader("Content-Disposition", headStr);
                        OutputStream out1 = response.getOutputStream();
                        workbook.write(out1);
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    
        /**
         * @param
         * @return
         * @author suchao
         * @date @date 2021/03/28
         * @description 标题行的单元格样式
         */
        public XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
    
            // 设置字体
    
            XSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short) 11);
            //字体加粗
            font.setBold(true);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            //设置底边框;
            style.setBorderBottom(THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;
            style.setBorderLeft(THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框;
            style.setBorderRight(THIN);
            //设置右边框颜色;
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框;
            style.setBorderTop(THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    
        /**
         * @param
         * @return
         * @author suchao
         * @date @date 2021/03/28
         * @description 列数据信息单元格样式
         */
        public XSSFCellStyle getStyle(XSSFWorkbook workbook) {
            // 设置字体
            XSSFFont font = workbook.createFont();
            //设置字体大小
            //font.setFontHeightInPoints((short)10);
            //字体加粗
            //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            XSSFCellStyle style = workbook.createCellStyle();
            //设置底边框;
            style.setBorderBottom(THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //设置左边框;
            style.setBorderLeft(THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //设置右边框;
            style.setBorderRight(THIN);
            //设置右边框颜色;
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //设置顶边框;
            style.setBorderTop(THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    }

Controller层代码：

    /**
         * @param
         * @return
         * @author suchao
         * @date 2021/03/28
         * @description  Get请求，可以通过浏览器直接访问下载
         */
    @GetMapping("/exportExcelTwoRowsHead") 
        public void getSheetModel(
                HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ParseException {
    
            List<Object[]> dataList = new ArrayList<>();
            String title = "学生信息Excel模板";
            String[] column1 = {"学校","学生信息","学生信息","学生信息","学校简介","学校电话"};
            String[] column2 = {"学校","班级","姓名","性别","学校简介","学校电话"};
            List<Object[]> columnName = new ArrayList<>();
            columnName.add(column1);
            columnName.add(column2);
    
            // 这是导出模板的示例数据，dataList也可以是数据库中查询出的数据，进行导出
            String[] objs = new String[]{"神奇高中","2班","天一清","男","这是一所魔幻的高中","xxxxxxx"};
            dataList.add(objs);
    
            //实例化工具类
            ExportExcelTwo ex = new ExportExcelTwo(title, columnName, dataList, request, response);
            try {
                //导出excel
                ex.exports();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

导入功能

Controller层代码

    @PostMapping("/excelImport")
        public Object importpipe(@RequestParam("file")
                                         MultipartFile file, @RequestParam("usertoken") String usertoken,HttpServletRequest request, HttpServletResponse response) throws Exception{
    
            Map resultMap = new HashMap();
            Map corroMap =  lineService.excelImport(file,usertoken,request,response);
            resultMap.put("result","");
            resultMap.put("model",corroMap);
            return resultMap;
    
        }

Service层代码

    /**
    * 导入之前确保所有单元格样式为文本格式
    */
    public Map excelImport(MultipartFile file, String usertoken, HttpServletRequest request, HttpServletResponse response) throws Exception {
           
         // 此方法在下面CellDeal类中
            CellDeal cellDeal = new CellDeal();
            List<CdPipelineBasicInfo> list1 = new ArrayList();
            
            //1.得到上传的表
            InputStream ins = file.getInputStream();
            Workbook workbook2 = new XSSFWorkbook(ins);
            //2、获取test工作表
            Sheet sheet2 = workbook2.getSheetAt(0);
            //获取表的总行数
            int num = sheet2.getLastRowNum();
          
            //总列数
            int successNumber = 0;
            int failureNumber = 0;
           
            // 遍历每一行
            for (int j = 1; j <= num; j++) {
                // 获取此行数据
                Row row1 = sheet2.getRow(j);
                           
                String parentOrgName = cellDeal.getCellValue(row1.getCell(0));
                String companyName = cellDeal.getCellValue(row1.getCell(1));
                String orgId = cdCorrInhFillingDeal.returnOrgIdByName(orgList, companyName);
                if (orgId.equals("")) {
                    continue;
                }           
                String code = cellDeal.getCellValue(row1.getCell(3));
                if (equipCode.equals("")) {
                    continue;
                }        
           // ......
            }
        }

获取单元格内容方法：

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    
    import java.util.HashMap;
    import java.util.Map;
    
    public class CellDeal {
        /**
         * @author suchao
         * @apiNote 用于对导入时获取的cell对象进行判空
         * @param cell
         * @return java.lang.String
         */
        public String getCellValue(Cell cell){
            String cellValue ="";
            if(cell!=null){
                cell.setCellType(CellType.STRING);
                cellValue =cell.getStringCellValue().trim();
            }
            return cellValue;
        }
    }