Java Poi 创建与读取Excel

创建实体Java Bean–Student

public class Student {
private int id;
private String name;
private int age;
private Date birth;

public Student() {  }

public Student(int id, String name, int age, Date birth) {
    super();
    this.id = id;
    this.name = name;
    this.age = age;
    this.birth = birth;
}

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

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

public Date getBirth() {
    return birth;
}

public void setBirth(Date birth) {
    this.birth = birth;
}

}
创建Excel表

public class CreateXLS {
private static String path = “E:/Student.xls”;
private static List mList;
/*工作簿/
private static HSSFWorkbook workbook;

static {
    mList = new ArrayList();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
    try {
        Student user1 = new Student(1, "张三", 16,
                dateFormat.parse("1997-03-12"));
        Student user2 = new Student(2, "李四", 17,
                dateFormat.parse("1996-08-12"));
        Student user3 = new Student(3, "王五", 26,
                dateFormat.parse("1985-11-12"));
        mList.add(user1);
        mList.add(user2);
        mList.add(user3);
    } catch (ParseException e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    workbook = new HSSFWorkbook();
    // 第二步， 在webbook中添加一个sheet，对应Excel文件中的sheet
    HSSFSheet sheet = workbook.createSheet("学生表一");
    // 第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制short
    HSSFRow row = sheet.createRow(0);
    // 第四步，创建单元格，并设置值表头，设置表头居中
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
    // 创建第一行
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("学号");
    cell.setCellStyle(style);
    
    cell = row.createCell(1);
    cell.setCellValue("姓名");
    cell.setCellStyle(style);
    
    cell = row.createCell(2);
    cell.setCellValue("年龄");        
    cell.setCellStyle(style);
    
    cell = row.createCell(3);
    cell.setCellValue("生日");
    cell.setCellStyle(style);
    // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
    for (int i = 0; i < mList.size(); i++) {
        row = sheet.createRow(i + 1);
        Student student = mList.get(i);
        // 第四步，创建单元格，并设置值
        row.createCell((short) 0).setCellValue((double) student.getId());
        row.createCell((short) 1).setCellValue(student.getName());
        row.createCell((short) 2).setCellValue((double) student.getAge());
        cell = row.createCell((short) 3);
        cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(student.getBirth()));
    }
    
    try{
        FileOutputStream fout = new FileOutputStream(path);
        workbook.write(fout);
        fout.close();
    } catch (Exception e){
        e.printStackTrace();
    }
}

}
读取Excel表

public class ReadXLS {
private static String path = “E:/Student.xls”;

public static void main(String[] args) throws IOException {
    List>> list = readExcelWithTitle(path);
    System.out.println(list.toString());
}

/**
 * [[{姓名=张三, 生日=1997-03-12, 学号=1.0, 年龄=16.0}, 
 *   {姓名=李四, 生日=1996-08-12, 学号=2.0, 年龄=17.0}, 
 *   {姓名=王五, 生日=1985-11-12, 学号=3.0, 年龄=26.0}]]
 */
public static List>> readExcelWithTitle(String path) throws IOException {
    String fileType = path.substring(path.lastIndexOf(".")+1, path.length());
    InputStream inputStream = null;
    Workbook workbook = null;
    try {
        inputStream = new FileInputStream(path);
        if (fileType.equals("xls")){
            workbook = new HSSFWorkbook(inputStream);
        }
        // 对应excel文件
        List>> result = new ArrayList>>();
        // 遍历sheet页
        int sheetSize = workbook.getNumberOfSheets();
        
        for (int i = 0; i < sheetSize; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 对应sheet页
            List> sheetList = new ArrayList>();
            // 对应所有标题
            List titles = new ArrayList();
            // 遍历行
            int rowSize = sheet.getLastRowNum() + 1;
            for (int j = 0; j < rowSize; j++) {
                Row row = sheet.getRow(j);
                if (null == row) { // 略过空行
                    continue;
                }
                
                int cellSize = row.getLastCellNum();// 行中有多少个单元格，也就是有多少列
                if (0 == j) {
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        titles.add(cell.toString());
                    }
                } else { // 其他数据行
                    Map rowMap = new HashMap();
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        String key = titles.get(k);
                        String value = null;
                        if (null != cell) {
                            value = cell.toString();
                        }
                        rowMap.put(key, value);
                    }
                    sheetList.add(rowMap);
                }
            }
            result.add(sheetList);              
        }
        return result;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (null != workbook) {
            workbook.close();
        }
        if (null != inputStream) {
            inputStream.close();
        }
    }       
    return null;
}

}
深圳网站建设www.sz886.com