转自:https://www.jb51.net/article/95526.htm
介绍
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html,API文档http://poi.apache.org/apidocs/index.html
实现
已经在代码中加入了完整的注释。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelOperate { public static void main(String[] args) { // 创建Excel表格 createExcel(getStudent()); // 读取Excel表格 List<Student> list = readExcel(); System.out.println(list.toString()); } /** * 初始化数据 * * @return 数据 */ private static List<Student> getStudent() { List<Student> list = new ArrayList<Student>(); Student student1 = new Student( "小明" , 8 , "二年级" ); Student student2 = new Student( "小光" , 9 , "三年级" ); Student student3 = new Student( "小花" , 10 , "四年级" ); list.add(student1); list.add(student2); list.add(student3); return list; } /** * 创建Excel * * @param list * 数据 */ private static void createExcel(List<Student> list) { // 创建一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet = workbook.createSheet( "学生表一" ); // 添加表头行 HSSFRow hssfRow = sheet.createRow( 0 ); // 设置单元格格式居中 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 添加表头内容 HSSFCell headCell = hssfRow.createCell( 0 ); headCell.setCellValue( "姓名" ); headCell.setCellStyle(cellStyle); headCell = hssfRow.createCell( 1 ); headCell.setCellValue( "年龄" ); headCell.setCellStyle(cellStyle); headCell = hssfRow.createCell( 2 ); headCell.setCellValue( "年级" ); headCell.setCellStyle(cellStyle); // 添加数据内容 for ( int i = 0 ; i < list.size(); i++) { hssfRow = sheet.createRow(( int ) i + 1 ); Student student = list.get(i); // 创建单元格,并设置值 HSSFCell cell = hssfRow.createCell( 0 ); cell.setCellValue(student.getName()); cell.setCellStyle(cellStyle); cell = hssfRow.createCell( 1 ); cell.setCellValue(student.getAge()); cell.setCellStyle(cellStyle); cell = hssfRow.createCell( 2 ); cell.setCellValue(student.getGrade()); cell.setCellStyle(cellStyle); } // 保存Excel文件 try { OutputStream outputStream = new FileOutputStream( "D:/students.xls" ); workbook.write(outputStream); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 读取Excel * * @return 数据集合 */ private static List<Student> readExcel() { List<Student> list = new ArrayList<Student>(); HSSFWorkbook workbook = null ; try { // 读取Excel文件 InputStream inputStream = new FileInputStream( "D:/students.xls" ); workbook = new HSSFWorkbook(inputStream); inputStream.close(); } catch (Exception e) { e.printStackTrace(); } // 循环工作表 for ( int numSheet = 0 ; numSheet < workbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = workbook.getSheetAt(numSheet); if (hssfSheet == null ) { continue ; } // 循环行 for ( int rowNum = 1 ; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null ) { continue ; } // 将单元格中的内容存入集合 Student student = new Student(); HSSFCell cell = hssfRow.getCell( 0 ); if (cell == null ) { continue ; } student.setName(cell.getStringCellValue()); cell = hssfRow.getCell( 1 ); if (cell == null ) { continue ; } student.setAge(( int ) cell.getNumericCellValue()); cell = hssfRow.getCell( 2 ); if (cell == null ) { continue ; } student.setGrade(cell.getStringCellValue()); list.add(student); } } return list; } } |
附上Student类的代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | public class Student { private String name; private int age; private String grade; public Student() { } public Student(String name, int age, String grade) { super (); this .name = name; this .age = age; this .grade = grade; } 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 String getGrade() { return grade; } public void setGrade(String grade) { this .grade = grade; } @Override public String toString() { return "Student [name=" + name + ", age=" + age + ", grade=" + grade + "]" ; } } |
测试结果
导出的Excel表格
students
打印读取的Excel数据
1 | [Student [name=小明, age= 8 , grade=二年级], Student [name=小光, age= 9 , grade=三年级], Stu |