package com.demo.excel; import com.demo.pojo.Student; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.util.ArrayList; import java.util.List; import static javax.xml.bind.JAXBIntrospector.getValue; /** * Created by xfma on 2017/1/19. */ public class ReadExcel { final static String excelFileName = "F:/Test.xls"; public static void main(String[] args) { try { createExcel(createStudent()); List<Student> list = readExcel(excelFileName); for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i).getName() + " " + list.get(i).getAge() + " " + list.get(i).getSchool() + " " + list.get(i).getAddress()); } } catch (Exception e) { e.getStackTrace(); } } /** * 读Excel * * @param excelFileName 文件名 * @return * @throws Exception */ public static List<Student> readExcel(String excelFileName) throws Exception { boolean isExcel2003 = true; if (!excelFileName.endsWith("xls")) { isExcel2003 = false; } File file = new File(excelFileName); FileInputStream fis = new FileInputStream(file); Workbook workbook = null; /*excel2003和2007不是用同一个对象读取的*/ if (isExcel2003) { workbook = new HSSFWorkbook(fis); } else { workbook = new XSSFWorkbook(fis); } Sheet sheet = workbook.getSheetAt(0);//得到第0个Sheet sheet.getLastRowNum(); List<Student> list = new ArrayList<Student>(); for (int r = 0; r < sheet.getLastRowNum(); r++) { Row row = sheet.getRow(r + 1);//越过标题,从第二行读 if (row != null) { Student student = new Student(); Cell name = row.getCell(0); Cell age = row.getCell(1); Cell address = row.getCell(2); Cell school = row.getCell(3); student.setName(getValue(name).toString()); float f = Float.parseFloat(getValue(age).toString()); student.setAge((int) f); student.setAddress(getValue(address).toString()); student.setSchool(getValue(school).toString()); list.add(student); } } return list; } /** * 生成Excel * * @param list 对象集合 * @throws Exception */ public static void createExcel(List<Student> list) throws Exception { //1.创建一个HSSFWorkbook对象,每个HSSFWorkbook对应一个新的Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); //2.在HSSFWorkbook中添加一个sheet,对应Excel中的一个sheet表 HSSFSheet sheet = workbook.createSheet("学生信息表"); String[] cells = new String[]{"姓名", "年龄", "地址", "学校"};//表头 int rowSize = list.size() + 1;//从第二条开始读,去掉标题 for (int r = 0; r < rowSize; r++) { HSSFRow row = sheet.createRow(r); for (int c = 0; c < cells.length; c++) { HSSFCell cell = row.createCell(c); if (r == 0) { cell.setCellValue(cells[c]);//创建表头 } else { /*往表内写数据*/ int index = r - 1;//从第一条数据往里面写 switch (c) { case 0: cell.setCellValue(list.get(index).getName()); continue; case 1: cell.setCellValue(list.get(index).getAge()); continue; case 2: cell.setCellValue(list.get(index).getAddress()); continue; case 3: cell.setCellValue(list.get(index).getSchool()); continue; } } } } FileOutputStream fileOut = new FileOutputStream(excelFileName); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } /** * 创建示例 * * @return */ public static List<Student> createStudent() { List<Student> list = new ArrayList<Student>(); Student s1 = new Student("小黑", 18, "上海浦东", "复旦大学"); Student s2 = new Student("小白", 19, "上海普陀", "同济大学"); Student s3 = new Student("小玉", 22, "上海黄埔", "上海交通大学"); Student s4 = new Student("小红", 20, "上海静安", "上海财经大学"); list.add(s1); list.add(s2); list.add(s3); list.add(s4); return list; } }
pojo:
package com.demo.pojo; import java.io.Serializable; /** * Created by xfma on 2017/1/19. */ public class Student implements Serializable{ private String name; private Integer age; private String address; private String school; public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getSchool() { return school; } public void setSchool(String school) { this.school = school; } public Student(String name, Integer age, String address, String school) { this.name = name; this.age = age; this.address = address; this.school = school; } public Student() { } }
pom.xml:
<!-- POI --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <!-- poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>