java操作excel方法

1. 依赖

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

2.方法
读取

 /**
     * 1 读取
     * 读取excel 中数据 转为 list<Map>
     * @param filePath 读取路径
     * @param titleIndex 标题行
     * @param start 开始行
     * @param end 结束行
     * @return
     */
    public List<Map> readExcelList(String filePath,int titleIndex,int start,int end)
    {
        List<Map> res=new ArrayList<>();
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File(filePath);
            //创建文件对象
            FileInputStream is = new FileInputStream(excelFile);
            //文件流
            Workbook workbook = WorkbookFactory.create(is);
            //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();

            //Sheet的数量
            //遍历每个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            //获取总行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            List<String> titleList=new ArrayList();
            //获取标题行
            Row row1 = sheet.getRow(titleIndex);
            int cellCount = row1.getPhysicalNumberOfCells();
            for(int j=0;j<cellCount;j++){
                Cell cell = row1.getCell(j);
                titleList.add(cell.getStringCellValue());
            }
            //遍历每一行
            for (int r = start; r < end; r++) {
                System.out.println("读取第几行"+r);
                Row row = sheet.getRow(r);
                Map map=new HashMap();
                for(int j=0;j<cellCount;j++){
                    Cell cell=row.getCell(j);
                    if(cell==null){
                        map.put(titleList.get(j),"");
                    }else{
                        map.put(titleList.get(j),cell.getStringCellValue());
                    }
                }
                res.add(map);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    /**
     * 2 读取
     * 读取excel 中的某列
     * @param filePath  读取路径
     * @param l 读取列下标
     * @return
     */
    public List<String> readExcelList(String filePath,int l)
    {
        List<String> res=new ArrayList<>();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File(filePath);
            //创建文件对象
            FileInputStream is = new FileInputStream(excelFile);
            //文件流
            Workbook workbook = WorkbookFactory.create(is);
            //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();

            //Sheet的数量
            //遍历每个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            //获取总行数
            //遍历每一行
            for (int r = 1; r < rowCount; r++) {
                Row row = sheet.getRow(r);
                int cellCount = row.getPhysicalNumberOfCells();
                //获取总列数
                //遍历每一个单元格
                if(cellCount>=l){
                    Cell cell = row.getCell(l);
                    String cellValue  = cell.getStringCellValue();
                    res.add(cellValue);
                }
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

写入/输出

    /** 1 写入
     * 每条数据key的并集为表头
     * 将List<Map> 输出到excel表格中
     * @param sheetName 创建的sheet名称
     * @param fileName 文件名
     * @param list 输入的List<Map>
     */
    public  void GeneralInput(String sheetName, String fileName , List<Map> list){

        //第一步，创建一个workbook对应一个excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //第二部，在workbook中创建一个sheet对应excel中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        HSSFRow row = sheet.createRow(0);
        //第四步，创建单元格，设置表头

        //获取表头字段
        Set<String> keys=new LinkedHashSet<>();
        if(list!=null&&list.size()>0){
            for(Map l:list){
                Map<String,Object> map=l;
                for (String key : map.keySet()) {
                    keys.add(key);
                }
            }
        }
        //创建表头
        int x=0;
        for (String key :keys) {
            sheet.setColumnWidth(x,5100);
            HSSFCell cell = row.createCell(x);
            cell.setCellValue(key);
            x++;
        }
        //根据表头获取想要的字段值
        for(int i=0;i<list.size();i++){
            //每条/行数据
            Map<String,Object> map=list.get(i);
            HSSFRow row1 = sheet.createRow(i + 1);
            //创建单元格设值
            int j=0;
            for(String title:keys){
                //每个表头数据
                for(String key : map.keySet()){
                    //每条数据的键值 和表头对比
                    if(title.equals(key)){
                        row1.createCell(j).setCellValue(String.valueOf(map.get(key)));
                        break;
                    }
                }
                j++;
            }
        }

        //将文件保存到指定的位置
        try {
            FileOutputStream fos = new FileOutputStream("D:\\test\\"+fileName+".xls");
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 2 写入 1万条左右数据
     * 将List<Map> 输出到excel表格中
     * 数据键值和表头一一对应
     * @param
     * @param fileName 输出的文件名
     * @param list 输出的list
     * @param sheetIndex 输出的sheet下标
     */
    public  void GeneralInput(String fileName , List<Map> list,int sheetIndex) throws IOException {

        FileInputStream fis = new FileInputStream(fileName);
        //第一步，创建一个workbook对应一个excel文件
//        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);

        //第二部，在workbook中创建一个sheet对应excel中的sheet
//        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        //获取最后一条数据行数
        int rowIndex=sheet.getLastRowNum();
        //第三部，在sheet表中添加表头第rowIndex行，老版本的poi对sheet的行列有限制
        //第四步，创建单元格，设置表头
        //表头字段
        List<String> keys=new ArrayList<>();
        //创建表头 根据已有的表头创建
        Row firstRow = sheet.getRow(0);
        //获取总列数
        int cellCount = firstRow.getPhysicalNumberOfCells();
        for(int x=0;x<cellCount;x++){
            //表头第几列
            Cell cellTitle = firstRow.getCell(x);
            String title=cellTitle.getStringCellValue();
            keys.add(title);
            System.out.println("表头"+x+"=="+title);
        }
        //根据表头获取想要的字段值
        for(int i=0;i<list.size();i++){
            //每条数据
            Map<String,Object> map=list.get(i);
//            HSSFRow row1 = sheet.createRow(i + 1+rowIndex);
            XSSFRow row1 = sheet.createRow(i + 1+rowIndex);
            //创建单元格设值
            int j=0;
            for(String title:keys){
                //每个表头数据
                for(String key : map.keySet()){
                    //每条数据的键值 和表头对比
                    if(title.equals(key)){
                        row1.createCell(j).setCellValue(String.valueOf(map.get(key)));
                        break;
                    }
                }
                j++;
            }
            System.out.println("复制到第几行"+i);
        }
        //将文件保存到指定的位置
        try {
            FileOutputStream fos = new FileOutputStream(fileName);
            workbook.write(fos);
            System.out.println("写入成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

删除

    /**
     * 删除
     * @param sheet 删除的sheet对象
     * @param rowIndex 删除行数
     * @throws IOException
     */
    public static void removeRow(XSSFSheet sheet,int rowIndex) throws IOException {

        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum){
            //将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
            System.out.println("删除第几行"+rowIndex);
        }
        if (rowIndex == lastRowNum) {
//            HSSFRow removingRow = sheet.getRow(rowIndex);
            XSSFRow removingRow = sheet.getRow(rowIndex);

            if (removingRow != null) {
                sheet.removeRow(removingRow);
                System.out.println("删除第几行"+rowIndex);
            }
        }

    }

映射

/** 映射
     * 3 张年报 1 综合治理
     * 纵向扩展报表数据拼接
     *  读取excel 中数据 按规则拼为json数据  适用于 单层对象
     * @param filePath 读取路径
     * @param sheetIndex 读取sheet下标
     * @param start 开始行
     * @param end 结束行
     * @param prefx 前缀
     * @return data={prefx1:'',prefx2:''...}
     */
    public   List<Map> readExcelList(String filePath, int sheetIndex, int start, int end,String prefx)
    {
        List<Map> res=new ArrayList<>();
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File(filePath);
            //创建文件对象
            FileInputStream is = new FileInputStream(excelFile);
            //文件流
            Workbook workbook = WorkbookFactory.create(is);
            //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();

            //Sheet的数量
            //遍历每个Sheet
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            //获取总行数
            int rowCount = sheet.getPhysicalNumberOfRows();
//            String titles[]={"pkId","adcdName","year","adcd","status","data"};
//            int dataNum=5;

            //映射字段数组
            String titles[]={"reportDate","projYear","ss","projCode","projName","ss1","adcd","pkId","status","data"};
            int dataNum=9;

            //获取标题行
            Row row1 = sheet.getRow(0);
            int cellCount = row1.getPhysicalNumberOfCells();
            //遍历每一行
            for (int r = start; r <end; r++) {
                System.out.println("第几行"+r);
                Row row = sheet.getRow(r);
                Map map=new HashMap();
                String data="{";
                for(int j=0;j<cellCount;j++){
                    Cell cell=row.getCell(j);
                    if(cell==null){
                        continue;
                    }else{
                        if(j<dataNum){
                            map.put(titles[j],cell.getStringCellValue());
                        }else if(j<cellCount-1){
                            data+=prefx+(j-8)+":'"+cell.getStringCellValue()+"',";
                        }else if(j==cellCount-1){
                            data+=prefx+(j-8)+":'"+cell.getStringCellValue()+"'}";
                            map.put(titles[9],data);
                        }
                    }
                }
                res.add(map);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }