POI简单实例实现Excel导入导出

POI实例

创建一张excel首先要知道excel由什么组成，比如sheet页就是一个工作表格，例如一行，一个单元格，单元格格式，单元格内容格式…这些都对应着poi里面的一个类。

Excel表格对应的POI生成：

• 创建Excel:
  HSSFWorkbook wb = new HSSFWorkbook();
• 生成sheet页
  HSSFSheet sheet = wb.createSheet(“POI表格”);
• 一行
  HSSFRow row = sheet.createRow(0);
• 一个单元格
  HSSFCell cell = row.createCell(0)；
• 更多设置见 博客.或自行查看API

1.POI导出

// 本文以Junit举例，具体用法自行学习
package POI;
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.junit.Test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
public class POIUsers {
    @Test
    public void usersPOI(){
        //1.创建工作薄
        Workbook workbook = new HSSFWorkbook();
        //2.创建sheet
        Sheet sheet = workbook.createSheet("usersPOI");
        
        //样式设置
        CellStyle cellStyle = work.createCellStyle();
        Font font = work.createFont();
        font.setColor(Font.COLOR_RED);
        font.setBold(true);
        cellStyle.setFont(font);
        
        //准备标题数据
        String[] title = {"姓名","年龄","工资","出生日期"};
        Date birth = new Date();
        //准备数据区
        List<Users> userList = Arrays.asList(new Users("Java",18,1000,birth),
                new Users("Python",18,1000,birth),
                new Users("Linux",16,10000,birth),
                new Users("Angularjs",16,10000,birth),
                new Users("Hadoop",38,5000,birth));
        /**
         * 创建标题行
         */
        Row row = sheet.createRow(0);
        //创建单元格
        for (int i = 0; i < title.length; i++) {
            Cell cell= row.createCell(i);
            //单元格赋值
            cell.setCellValue(title[i]);
        }

        /**
         *  数据区
         */
        Class userClass = Users.class;
        // 获得所有的属性字段
        Field[] fields = userClass.getDeclaredFields();
        for (int i = 0; i < userList.size(); i++) {
            // 创建第二行
            Row row2 = sheet.createRow(i + 1);
            for (int j = 0; j < title.length; j++) {
                /**
                 * Class 类对象 包含类的所有信息 与 类的对象 类的实例对象 3种方式
                 * 1.Class.forName("类的权限定名"); 2.实例名.getClass(); 3.类名.Class;
                 */
                // 第二行 第i个单元格
                Cell cell = row2.createCell(j);
                Field field = fields[j];

                // 打破封装
                field.setAccessible(true);

                // 获得field的值
                Object obj = field.get(userList.get(i));
                System.out.println(obj.toString());

                if (obj instanceof Date) {
                    Date date = (Date) obj;
                    String format = new SimpleDateFormat("yyyy-MM-dd")
                            .format(date);
                    cell.setCellValue(format);
                } else {
                    cell.setCellValue(obj.toString());
                }
            }
        }
        //6.输出
        File file = new File("E:\\POITest\\helloUser.xls");
        try {
            workbook.write(new FileOutputStream(file));
            System.out.println("导出成功!");
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

Users实体类：

package POI;

import java.util.Date;

/**
 * 用户实体
 */
public class Users {
	private String name;
	private Integer age;
	private double salary;
	private Date birth;
	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 double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	
	
	public Users(String name, Integer age, double salary, Date birth) {
		super();
		this.name = name;
		this.age = age;
		this.salary = salary;
		this.birth = birth;
	}
	public Users() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "User [name=" + name + ", age=" + age + ", salary=" + salary
				+ ", birth=" + birth + "]";
	}
}

2.POI导入

package POI;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class POIImport {
    /**
     * 导入
     */
    @Test
    public void ImportPOI() throws IOException, ParseException {
        FileInputStream inputStream = new FileInputStream(new File("E:\\POIImport\\HelloPOI.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        //获得sheet
        HSSFSheet sheet = workbook.getSheet("importPOI");
        //获得总行数
        int rowNum = sheet.getLastRowNum();
        List<User> users = new ArrayList<User>();
        for (int i = 1; i <= rowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            User user = new User();
            user.setId(row.getCell(0).getStringCellValue());
            user.setName(row.getCell(1).getStringCellValue());
            user.setAge(Integer.parseInt(row.getCell(2).getStringCellValue()));
            user.setBirth(new SimpleDateFormat("yyyy-MM-dd").parse(row.getCell(3).getStringCellValue()));
            users.add(user);
        }
        System.out.println(users);
    }
}