Java中使用POI导入导出工具类

客官°小女子只卖身不卖艺 2021-12-08 15:57 252阅读 0赞

依赖：

<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>

数据：

class Person {
        private String name;
        private String sex;
        private Integer age;
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
    
        @Override
        public String toString() {
            return "Person{" +
                    "name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    '}';
        }
    }

@RequestMapping("export")
        public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws NoSuchMethodException, IOException, InvocationTargetException, IllegalAccessException {
            String filename = "人员信息";
    
            String[] columNames = {"name", "sex", "age"};
            Person p1 = new Person();
            p1.setName("张三");
            p1.setSex("男");
            p1.setAge(20);
            Person p2 = new Person();
            p2.setName("李四");
            p2.setSex("女");
            p2.setAge(22);
    
            List<Person> dataList = new ArrayList<>();
            dataList.add(p1);
            dataList.add(p2);
    
            exportExcel(request, response, filename, columNames, dataList);
        }

**导出工具类：**

/**
         * 
         * @param request 请求
         * @param response 响应
         * @param filename 文件名
         * @param headers 标题头
         * @param headerFields 标题字段
         * @param rowDatas 数据集合
         * @throws IOException
         * @throws NoSuchMethodException
         * @throws SecurityException
         * @throws IllegalAccessException
         * @throws IllegalArgumentException
         * @throws InvocationTargetException
         */
        public static void exportExcel(HttpServletRequest request, HttpServletResponse response, String filename,String[] headers, String[] headerFields, Collection<?> rowDatas) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
            response.setHeader("Connection", "close");
            response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
            filename = filename + System.currentTimeMillis() + ".xls";
            filename = encodeFileName(request, filename);
            response.setHeader("Content-Disposition", "attachment;filename=" + filename);
    
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("sheet1");
            sheet.setDefaultRowHeightInPoints(13);//设置缺省列高
            sheet.setDefaultColumnWidth(15);//设置缺省列宽
    
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFCellStyle.ALIGN_CENTER 3.15poi 版本问题
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式  HorizontalAlignment.CENTER 3.17poi
    
            HSSFFont font = wb.createFont();
            font.setColor(Font.COLOR_NORMAL); // 将字体设置为“正常”
            font.setBold(true);//粗体显示
            font.setFontName("微软雅黑");  // 将“微软雅黑”字体应用到当前单元格上
            font.setFontHeightInPoints((short) 10); // 将字体大小设置为10px
    
            style.setFont(font);
    
    
            //设置单元格表头
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
            }
    
            // 第五步，写入实体数据 这些数据从数据库得到
            Iterator<?> it = rowDatas.iterator();
            int  i = 0;
            while (it.hasNext()) {
                row = sheet.createRow(i + 1);
                Object object =  it.next();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j < headerFields .length; j++) {
                    Method method = object.getClass().getDeclaredMethod("get" + headerFields [j].substring(0, 1).toUpperCase() + headerFields [j].substring(1));
                    Object value = method.invoke(object);
                    row.createCell(j).setCellValue(value == null ? "" : value.toString());
                }
    
            }
    
            // 第六步，导出文件
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        }
    
    
        /**
         *
         * @Title: encodeFileName
         * @Description: 导出文件转换文件名称编码
         * @param @param fileNames
         * @param @param request
         * @param @return    设定文件
         * @return String    返回类型
         * @throws
         */
        public static String encodeFileName(HttpServletRequest request,String fileNames) {
            String codedFilename = null;
            try {
                String agent = request.getHeader("USER-AGENT");
                if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
                        && -1 != agent.indexOf("Trident") || null != agent && -1 != agent.indexOf("Edge")) {// ie浏览器及Edge浏览器
                    String name = java.net.URLEncoder.encode(fileNames, "UTF-8");
                    codedFilename = name;
                } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,Chrome等浏览器
                    codedFilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return codedFilename ;
        }

**导入工具类：**

import org.apache.poi.ss.usermodel.*;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.util.ArrayList;
    import java.util.List;
    
    
    public class ImportExcelUtil {
        /**
         * 读取excel文件当中的数据，将其每行数据转换成javabean，将javabean存放到List集合中并返回。
         * @param c List集合中存储的每一个元素的类型
         * @param file excel文件路径
         * @param headerFields 需要获取值的字段，传入参数列需要和你的Excel字段列对应
         * @return dataList
         * @throws Exception
         */
        public static List getDataListFromExcel(Class c, File file,String[] headerFields) throws Exception {
            List dataList = new ArrayList();
            FileInputStream inStream = new FileInputStream(file);
            Workbook workBook = WorkbookFactory.create(inStream);
            Sheet sheet = workBook.getSheetAt(0);
    //        Row firstRow = sheet.getRow(0);
            //如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回 n-1
            int rowNum = sheet.getLastRowNum() + 1;
            //如果不是第一行开始遍历单元格
            for (int i = 1; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                Object o = c.newInstance();
                //getLastCellNum  如果row中一列数据都没有则返回-1，只有第一列有数据则返回1，最后有数据的列是第n列则返回 n
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    //第一行，第几个单元格,如果列对应的就是标题头，使用这种方式可以，如果不是，则需要传入列的字段
    //                Cell columnCell = firstRow.getCell(j);
    //                String column = getCellValue(columnCell);
    //                Field field = c.getDeclaredField(column);
                    Field field = c.getDeclaredField(headerFields[j]);
                    //第i行，第j个单元格
                    Cell cell = row.getCell(j);
                    String str = getCellValue(cell);
                    Method setMethod = c.getDeclaredMethod(
                            "set" + headerFields[j].substring(0, 1).toUpperCase() + headerFields[j].substring(1), field.getType());
                   //判断是否是字符类型
                    String reg = "^[0-9]+(.[0-9]+)?$";
                    if(!str.matches(reg)){
                        setMethod.invoke(o, str);
                    }else {
                        Double cellValue = Double.parseDouble(str);
                        setMethod.invoke(o, cellValue);
                    }
                }
                dataList.add(o);
            }
            return dataList;
        }
    
        private static String getCellValue(Cell cell) {
            String cellValue = "";
            DataFormatter formatter = new DataFormatter();
            if (cell != null) {
                switch (cell.getCellTypeEnum()) {
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            cellValue = formatter.formatCellValue(cell);
                        } else {
                            double value = cell.getNumericCellValue();
                            int intValue = (int) value;
                            cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                        }
                        break;
                    case STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    case BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case FORMULA: {
                        try {
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        } catch (IllegalStateException e) {
                            cellValue = String.valueOf(cell.getRichStringCellValue());
                        }
                    }
                    break;
                    case BLANK:
                        cellValue = "";
                        break;
                    case ERROR:
                        cellValue = "";
                        break;
                    default:
                        cellValue = cell.toString().trim();
                        break;
                }
            }
            return cellValue.trim();
        }
    
    
        public static void main(String[] args) throws Exception {
            String[] headerFields={"name","sex","age"};
            File file=new File("C:\\Users\\Administrator\\Downloads\\人员信息1563273376269.xls");
            List dataListFromExcel = getDataListFromExcel(Person.class, file, headerFields);
            System.out.println(dataListFromExcel);
    
    
        }
    
    }