Java操作Excel文件

创建一个Excel文件

public static void creatExcelFile(String filepath){
        Workbook wb = new XSSFWorkbook();
        try {
            FileOutputStream fileOut = new FileOutputStream(filepath);
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            System.out.println("文件创建成功！");
        }
    }

判断Excel文件后缀名

因为老版本的excel文件后缀名是.xls,新版本的是.xlsx,获取WorkBook对象的调用的类就不同，所以在获取对象的时候要进行判断
Java代码：

 	/**
     * 判断Excel的版本,获取Workbook
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(String filepath) throws IOException{
        File file = new File(filepath);
        FileInputStream in = new FileInputStream(file);
        System.out.println(file.getName());
        if(file.getName().endsWith(".xls")){     
            HSSFWorkbook workBook =new HSSFWorkbook(in);
            return workBook;

        }else if(file.getName().endsWith(".xlsx")){    
            XSSFWorkbook workbook = new XSSFWorkbook(in);
            return workbook;
        }else {
            System.out.println("文件格式错误！");
        }
        return null;
    }

数据写入

    public static void writeExcel(){
        //数据准备
        Map<String,String> map = new HashMap<>();
        map.put("Name","ycy");
        map.put("Addr","xiaojie");
        map.put("Phone","123456");

        Map<String,String> map1 = new HashMap<>();
        map1.put("Name","ljx");
        map1.put("Addr","guangdong");
        map1.put("Phone","654321");

        List<Map> dataList = new ArrayList<>();
        dataList.add(map);
        dataList.add(map1);

        String finalXlsxPath = "D:\\Test\\demo.xlsx";
        OutputStream out = null;
        
        try {
            // 读取Excel文档
            Workbook workBook = getWorkbook(finalXlsxPath);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);
            
            /**
             * 往Excel中写新数据
             */
            for (int j = 0; j < dataList.size(); j++) {
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + 1);
                // 得到要插入的每一条记录
                Map dataMap = dataList.get(j);
                String name = dataMap.get("BankName").toString();
                String address = dataMap.get("Addr").toString();
                String phone = dataMap.get("Phone").toString();

                    //获取单元格
                    Cell first = row.createCell(0);
                    first.setCellValue(name);

                    Cell second = row.createCell(1);
                    second.setCellValue(address);

                    Cell third = row.createCell(2);
                    third.setCellValue(phone);

            }
            
            // 创建文件输出流，准备输出电子表格
            out =  new FileOutputStream("D:\\Test\\2.xlsx");
            workBook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(out != null){
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        System.out.println("数据导出成功");

根据模板文件写入数据

	public void  excelFileAnalysis1(){
        String   excelPath="C:\\Users\\41089\\Desktop\\新建文件夹\\北配电器.xlsx";
        FileInputStream c= null;
        try {
            c = new FileInputStream(excelPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        ExcelHeaderTableEntity excelHeaderTable=new ExcelHeaderTableEntity();//所有解析数据存入该对象
        try {
            File excel = new File(excelPath);
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在
                Workbook wb = getWorkbook(excelPath);
                //开始解析
                Sheet sheet = wb.getSheetAt(0);     //读取sheet 0
                int firstRowIndex = sheet.getFirstRowNum();   //第一行是列名
                int lastRowIndex = sheet.getLastRowNum();
                for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
                    ExcelGeneralTableEntity  excelGeneralTable= new ExcelGeneralTableEntity();
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
                            	//判断每个单元格，这里只列出了两个条件，
                            	//可以根据实际情况更改判断条件
                                if(cell.toString().equals("ycy"))
                                    cell.setCellValue("123");
                                if(cell.toString().equals("ljx"))
                                    cell.setCellValue("456");
                            }
                        }
                    }
                }
               FileOutputStream b=new FileOutputStream("D:\\Test\\d.xlsx");
                wb.write(b);
                b.close();
            } else {
                System.out.println("找不到指定的文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }