JAVA读取写入excle表兼容版

Maven依赖：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
       <dependency>
            <groupId>org.apache.poi</groupId>
           <artifactId>poi-ooxml</artifactId>
           <version>4.1.0</version>
     </dependency>

模拟的实体类：

package com.test.word;

import java.io.Serializable;
/**
 * 模拟的实体类
 * @author xieh
 *
 */
public class Test  implements Serializable{
    private  int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Test [id=" + id + ", name=" + name + "]";
    }
}

读取和写入的excle工具类：

package com.test.word;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excle工具类
 * 
 * @author xieh 2019/11/24 使用的jar包是：poi-4.1.0.jar 和 poi-ooxml-4.1.0.jar
 *
 */
public class ExcelUtil {
    
    /**
     * 读取excle内容：注意此方法不兼容.xlsx文件
     * @throws Exception
     */
    @SuppressWarnings("resource")
    public static List<Map<String, String>> excelRead() throws Exception {
        //用流的方式先读取到你想要的excel的文件
        //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
        FileInputStream fis = new FileInputStream(new File("D:/a.xls"));
        // 解析excel
        POIFSFileSystem pSystem = new POIFSFileSystem(fis);
        // 获取整个excel
        HSSFWorkbook hb = new HSSFWorkbook(pSystem);
        System.out.println(hb.getNumCellStyles());
        // 获取第一个表单sheet
        HSSFSheet sheet = hb.getSheetAt(0);
        // 获取第一行
        int firstrow = sheet.getFirstRowNum();
        // 获取最后一行
        int lastrow = sheet.getLastRowNum();
        // 存取最后结果
        List<Map<String, String>> result = new ArrayList<Map<String, String>>();
        // 循环行数依次获取列数
        for (int i = firstrow; i < lastrow + 1; i++) {
            // 获取哪一行i
            Row row = sheet.getRow(i);
            // 存放每行的键值对结果
            Map<String, String> tempMap = new HashMap<String, String>();
            if (row != null) {
                // 获取这一行的第一列
                int firstcell = row.getFirstCellNum();
                // 获取这一行的最后一列
                int lastcell = row.getLastCellNum();
                // 创建一个集合，用处将每一行的每一列数据都存入集合中
                //List<String> list = new ArrayList<String>();
                for (int j = firstcell; j < lastcell; j++) {
                    // 获取第j列
                    Cell cell = row.getCell(j);
                    if (cell != null && !("").equals(cell.toString())) {
                        //System.out.print(cell + "	");
                        //list.add(cell.toString());
                        tempMap.put("c" + j, cell.toString().trim());
                    }
                }
                // 存放每行的结果
                result.add(tempMap);
            }// row if end
            
        }// for end
        fis.close();
        System.out.println(result.toString());
        return result;
    }

    
    
    
    /**
     * 此方法兼容.xls 和 .xlsx格式，建议使用
     * @param path
     * @return excle表中的内容
     * @throws Exception
     */
    @SuppressWarnings("resource")
    public static List<Map<String, String>> excelXRead(String path) throws Exception {
        //用流的方式先读取到你想要的excel的文件
        //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
         File excel = new File(path);
         String[] split = excel.getName().split("\.");  //.是特殊字符，需要转义
         Workbook wb = null;
         FileInputStream  fis = null;
         
         //根据文件后缀（xls/xlsx）进行判断
         if ( "xls".equals(split[1])){
             fis = new FileInputStream(excel);   //文件流对象 
             wb = new HSSFWorkbook(fis);
             
         }else if ("xlsx".equals(split[1])){
             wb = new XSSFWorkbook(excel);
             
         }else {
             System.out.println("文件类型错误!");
             return new ArrayList<>();
             
         }    
         //开始解析
         Sheet sheet = wb.getSheetAt(0);     //读取sheet 0
        // 获取第一行
        int firstrow = sheet.getFirstRowNum() + 1; //第一行是列名，所以不读
        // 获取最后一行
        int lastrow = sheet.getLastRowNum();
        // 存取最后结果
        List<Map<String, String>> result = new ArrayList<Map<String, String>>();
        // 循环行数依次获取列数
        for (int i = firstrow; i < lastrow + 1; i++) {
            // 获取哪一行i
            Row row = sheet.getRow(i);
            // 存放每行的键值对结果
            Map<String, String> tempMap = new HashMap<String, String>();
            if (row != null) {
                // 获取这一行的第一列
                int firstcell = row.getFirstCellNum();
                // 获取这一行的最后一列
                int lastcell = row.getLastCellNum();
                // 创建一个集合，用处将每一行的每一列数据都存入集合中
                //List<String> list = new ArrayList<String>();
                for (int j = firstcell; j < lastcell; j++) {
                    // 获取第j列
                    Cell cell = row.getCell(j);
                    if (cell != null && !("").equals(cell.toString())) {
                        //System.out.print(cell + "	");
                        //list.add(cell.toString());
                        tempMap.put("c" + j, cell.toString().trim());
                    }
                }
                // 存放每行的结果
                result.add(tempMap);
            }// row if end
            
        }// for end
        
        if(null != fis){
            fis.close();
        }
        System.out.println(result.toString());
        return result;
    }
    
    
    /**
     * 创建和写入excle内容
     * @param exportList 实体类list
     * @param file 生成的文件路径
     * @throws Exception
     */
    public static void writeXls(List<Test> exportList, File file) throws Exception {
         
        String[] options = { "ID", "内容", "名字" };
        XSSFWorkbook book = new XSSFWorkbook();
 
        CreationHelper createHelper = book.getCreationHelper();
 
        XSSFCellStyle style = book.createCellStyle();
        XSSFCellStyle dateStyle = book.createCellStyle();
        XSSFDataFormat format = book.createDataFormat();
        style.setWrapText(true);
        dateStyle.setWrapText(true);
 
        XSSFSheet sheet = book.createSheet("sheet");
 
        sheet.setColumnWidth(3, 13000);
        sheet.setDefaultColumnWidth(20);
 
        XSSFRow firstRow = sheet.createRow(0);
        XSSFCell[] firstCells = new XSSFCell[3];
 
        CellStyle styleBlue = book.createCellStyle(); // 样式对象
        // 设置单元格的背景颜色为淡蓝色
        styleBlue.setWrapText(true);// 指定当单元格内容显示不下时自动换行
 
        Font font = book.createFont();
        //font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 280);
        style.setFont(font);
        dateStyle.setFont(font);
        dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
        styleBlue.setFont(font);
 
        for (int j = 0; j < options.length; j++) {
            firstCells[j] = firstRow.createCell(j);
            firstCells[j].setCellStyle(styleBlue);
            firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
        }
        getExport(sheet, style, createHelper, exportList, dateStyle);
        if (file.exists()) {
            file.delete();
        }
        file.createNewFile();
 
        OutputStream os = new FileOutputStream(file);
        book.write(os);
        os.close();
    }

    
    
    
    
    
    /**
     * excle实体数据写入
     * @param sheet
     * @param style
     * @param createHelper
     * @param exportList
     * @param dateStyle
     */
    private static void getExport(XSSFSheet sheet, XSSFCellStyle style, CreationHelper createHelper, List<Test> exportList,
            XSSFCellStyle dateStyle) {
        // 遍历实例类的list集合
        for (int i = 0; i < exportList.size(); i++) {
            // 创建行
            XSSFRow row = sheet.createRow(i + 1);
            
            // 实体类
            Test export = exportList.get(i);
            
            // 第一列
            XSSFCell hotelId = row.createCell(0);
            hotelId.setCellStyle(style);
            
            // 第二列
            XSSFCell hotelName = row.createCell(1);
            hotelName.setCellStyle(dateStyle);
            
            // 第三列
            XSSFCell chargeCount = row.createCell(2);
            chargeCount.setCellStyle(style);
            
            // 设置值
            hotelId.setCellValue(export.getId());
            hotelName.setCellValue("测试");
            chargeCount.setCellValue(export.getName());
 
            // ta.append("写入excel开始,行数是" + (i + 1) + "
");
        }// for end
 
    }

    
    
    
    
    /**
     * 测试
     * 
     * @param args
     */
    public static void main(String[] args) {
        try {
            ExcelUtil.excelRead();
            ExcelUtil.excelXRead("D:/柱子.xlsx");
            ExcelUtil.excelXRead("D:/a.xls");
            List<Test> show = new ArrayList<Test>();
            Test test1 = new Test();
            test1.setId(1);
            test1.setName("xieh");
            
            Test test2 = new Test();
            test2.setId(2);
            test2.setName("xieh");
            show.add(test1);
            show.add(test2);
            ExcelUtil.writeXls(show, new File("D:/bbb.xls"));
            ExcelUtil.writeXls(show, new File("D:/aaa.xlsx"));
            
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

效果：