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() + "\t" + list.get(i).getAge() + "\t" + list.get(i).getSchool() + "\t" + 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>
05-28 02:52