Java之POI的excel导入导出

    　　<dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.17</version>        </dependency>

 public static void main(String[] args) throws Exception {        //读取文件        File file = new File("d:\test.xls");        InputStream inputStream = new FileInputStream(file);        //使用POI的流处理数据        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);        //声明2003版excel的文件读取方式        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);        //获取第一个sheet页        HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);        //获取数据总行数        int rows = sheetAt.getPhysicalNumberOfRows();        //每行数据处理        for (int i = 0; i < rows; i++) {            //获取一行数据            HSSFRow row = sheetAt.getRow(i);            if (i == 0) {                //这个主要用于标题                System.out.println(row.getCell(0));                continue;            }            //获取一行数据            Iterator<Cell> cellIterator = row.cellIterator();            List<Cell> cells = IteratorUtils.toList(cellIterator);            System.out.println(cells);        }    }

　　　　<dependency>            <groupId>commons-beanutils</groupId>            <artifactId>commons-beanutils</artifactId>            <version>1.9.3</version>        </dependency>

import java.util.List;import java.util.Map;public class ExcelParams {    //sheet页数目    private Integer sheetNum = 0;    //实体名称    private String entityName;    //保存实体名称行数    private Integer clazzNum = 0;    //字段行数    private Integer columnNum = 1;    //开始数据读取的行数    private Integer dataNum = 3;    //开始读取列    private Integer readColNum = 0;    //读取的excel数据    private List<Map<String,  >> excelList;    //最终数据处理    private List<Map<String,  >> excelHandleList;    public Integer getSheetNum() {        return sheetNum;    }    public void setSheetNum(Integer sheetNum) {        this.sheetNum = sheetNum;    }    public String getEntityName() {        return entityName;    }    public void setEntityName(String entityName) {        this.entityName = entityName;    }    public Integer getClazzNum() {        return clazzNum;    }    public void setClazzNum(Integer clazzNum) {        this.clazzNum = clazzNum;    }    public Integer getColumnNum() {        return columnNum;    }    public void setColumnNum(Integer columnNum) {        this.columnNum = columnNum;    }    public Integer getDataNum() {        return dataNum;    }    public void setDataNum(Integer dataNum) {        this.dataNum = dataNum;    }    public Integer getReadColNum() {        return readColNum;    }    public void setReadColNum(Integer readColNum) {        this.readColNum = readColNum;    }    public List<Map<String,  >> getExcelList() {        return excelList;    }    public void setExcelList(List<Map<String,  >> excelList) {        this.excelList = excelList;    }    public List<Map<String,  >> getExcelHandleList() {        return excelHandleList;    }    public void setExcelHandleList(List<Map<String,  >> excelHandleList) {        this.excelHandleList = excelHandleList;    }}

import com.troy.excel.domain.ExcelParams;public interface ExcelHandle {    ExcelParams excelDataHanle(ExcelParams excelParams);}

　　/**     * excel导入功能     * @param file     * @param excelParams     * @param excelHandle     * @return     * @throws Exception     */    public static ExcelParams excelImport(File file, ExcelParams excelParams, ExcelHandle excelHandle) throws Exception {        //获取文件流        InputStream inputStream = new FileInputStream(file);        //通过poi的方式进行读取        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);        //声明工作簿        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);        //进入sheet页        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(excelParams.getSheetNum());        //读取实体名称数据        HSSFRow entityRow = hssfSheet.getRow(excelParams.getClazzNum());        HSSFCell entityName = entityRow.getCell(excelParams.getReadColNum());        excelParams.setEntityName(entityName.getStringCellValue());        //读取数据保存到list中        List<Map<String, >> excelList = new ArrayList<>();        //获取字段数据        HSSFRow colunmRow = hssfSheet.getRow(excelParams.getColumnNum());        List<Cell> colums = IteratorUtils.toList(colunmRow.cellIterator());        //读取excel数据        for (int i = excelParams.getDataNum(); i < hssfSheet.getPhysicalNumberOfRows(); i++) {            //获取某一行的数据            HSSFRow excelRow = hssfSheet.getRow(i);            Map<String,  > map = new HashMap<>();            for (int j = 0; j < excelRow.getPhysicalNumberOfCells(); j++) {                if (colums != null && colums.size() > j) {                    HSSFCell rowCell = excelRow.getCell(j);                    //设置类型的目的方便数据装换                    rowCell.setCellType(CellType.STRING);                    map.put(colums.get(j).getStringCellValue(), rowCell.getStringCellValue());                }            }            excelList.add(map);        }        //放入数据放入下一步处理        excelParams.setExcelList(excelList);        //ExcelHandle接口用来做进一步数据处理，要求必须重写        excelHandle.excelDataHanle(excelParams);        return excelParams;    }

　　/**     * 保存excel数据     * @param excelParams     * @return     */    public static void saveExcelList(ExcelParams excelParams, EntityManager entityManager) throws Exception {        //1、获取保存的对象        Class<?> clazz = Class.forName(excelParams.getEntityName());        //2、保存数据        for (Map<String,  > map:excelParams.getExcelHandleList()) {            //对考数据                = clazz.newInstance();            BeanUtils.populate( , map);            //保存数据            entityManager.persist( );        }    }

public static void main(String[] args) throws Exception {        //读取文件        File file = new File("d:\test.xls");        //声明参数        ExcelParams excelParams = new ExcelParams();        excelParams.setSheetNum(0);        excelParams.setClazzNum(0);        excelParams.setColumnNum(1);        excelParams.setDataNum(3);        excelParams.setReadColNum(0);        //导如数据        ExcelParams excelData = ExcelUtil.excelImport(file, excelParams, (ep) -> {            //重写具体的实现方法            ep.setExcelHandleList(excelParams.getExcelList());            return ep;        });        //保存实体，这里需要加入到事物中，我这里没有具体测试        ExcelUtil.saveExcelList(excelData, null);        System.out.println(excelParams.getExcelList());    }

　　/**     * excel数据导出     * @param       * @param datas     * @param out     * @param <T>     * @throws Exception     */    public static <T> void excelExport(String  , List<T> datas, OutputStream out) throws Exception {        //声明一个工作簿        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();        //设置一个sheet名词        HSSFSheet hssfSheet = hssfWorkbook.createSheet( );        //数据处理，通过out写出        for (int i = 0; i < datas.size(); i++) {            //创建行数，主要是创建在第几行            HSSFRow hssfRow = hssfSheet.createRow(i);            //获取T的字段数            Field[] fields = datas.get(i).getClass().getDeclaredFields();            for (int j = 0; j < fields.length; j++) {                //获取字段名称                String fieldName = fields[j].getName();                //获取get方法                String methodName = "get"+ fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);                Method method = datas.get(i).getClass().getMethod(methodName);                //执行get方法获取对应数据                  text = method.invoke(datas.get(i));                //加入到对应单元格                HSSFCell hssfCell = hssfRow.createCell(j);                if (text != null) {                    hssfCell.setCellValue(text.toString());                }            }        }        //写入到输出流中        hssfWorkbook.write(out);    }

　　 public static void main(String[] args) throws Exception {        OutputStream outputStream = new FileOutputStream("d:\1.xls");        String   = "用户数据";        List<User> users = new ArrayList<>();        for (int i = 1; i <= 10; i++) {            User user = new User();            user.setId(i);            user.setName("name"+i);            users.add(user);        }        ExcelUtil.excelExport( , users, outputStream);    }