poi 对excel的读写(跟着宝哥学java)

1 导入依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.0</version>
</dependency>

2 创建实体类

package com.zhiyou100.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.*;
import java.io.Serializable;

@Entity
@Table(name="table_student")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student  implements Serializable {
    @Id
    @Column(name="s_id")
    @GenericGenerator(name = "sg",strategy = "increment")
    @GeneratedValue(generator = "sg")
    private Integer id;

    @Column(name = "s_name",unique = true,nullable = false)
    private String name;

    @Column(name = "s_sex",nullable = false)
    private String sex;

    @Column(name = "s_score",nullable = false)
    private Float score;
    @Column(name = "s_dy",nullable = false)
    private Boolean dy;

}

3 创建poi工具类

package com.zhiyou100.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

import com.zhiyou100.entity.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.CellStyle;
import org.apache.poi.ss.usermodel.CellType;

public class PoiUtil<E>{
    public static void main(String[] args) throws Exception{
           List<Student> list=new ArrayList<>();
//           for (int i=0;i<=10;i++){
//               list.add(new Student(
//                       (int)(Math.random()*10000),
//                       UUID.randomUUID().toString().replace("-",""),
//                       Math.random()>0.5?"男":"女",
//                       (int)(Math.random()*1000)/10.0f,
//                       Math.random()>0.5));
//           }
//         new PoiUtil<Student>().createExcel(list,"d:\\");

           list=new PoiUtil<Student>().readExcel(new FileInputStream("d:\\Student.xls"),new Student());
          System.out.println(list);
    }
    //创建excel文件
    //public static void createExcel(List<Object>,File file)throws Exception{
    //使用反射把list中的对象写成表格的行
    //表格标题是对象的类名
    //表格列标题是属性名
    public   void createExcel(List<E> list,String path)throws Exception{
        Class cla=list.get(0).getClass();
        //1 创建工作薄:
        HSSFWorkbook book=new HSSFWorkbook();
        //2 创建sheet：表格对象
        HSSFSheet sheet=book.createSheet(cla.getSimpleName());
        //3 创建第一行：列标题
        HSSFRow row0=sheet.createRow(0);
        Field[] fields=cla.getDeclaredFields();//对象有多少个属性  表格就有多少列
        for (int i = 0; i < fields.length; i++) {
            Field field=fields[i];
            field.setAccessible(true);
            String fieldName=field.getName();
            row0.createCell(i).setCellValue(fieldName);
        }
        //4 每个对象对应写成一行
        for (int i = 0; i <list.size(); i++) {
            Object obj=list.get(i);
            //创建行
            HSSFRow rowi=sheet.createRow(i+1);
            for (int j = 0; j < fields.length; j++) {
                Field field=fields[j];
                Object fieldValue=field.get(obj);//获取属性的值
                HSSFCell cellij=rowi.createCell(j);
                if(field.getType()==Date.class){
                    CellStyle dateStyle=book.createCellStyle();
                    dateStyle.setDataFormat(book.createDataFormat().getFormat("yyyy-MM-dd"));
                    cellij.setCellStyle(dateStyle);
                }
                //cellij.setCellValue(fieldValue);
                setCellValue(field,fieldValue,cellij);
            }
        }
        book.write(new File(path,cla.getSimpleName()+".xls"));
        book.close();

    }
    //根据filed的类型把Object类型的值 转换为Field类型的值  然后给cell设置文本内容
    private static void setCellValue(Field field,Object value,HSSFCell cell){
        //需要的类型：boolean  string date double
        Class type=field.getType();
        if(type==int.class||type==Integer.class){
            cell.setCellValue((Integer)value);
        }else if(type==short.class||type==Short.class){
            cell.setCellValue((Short)value);
        }else if(type==long.class||type==Long.class){
            cell.setCellValue((Long)value);
        }else if(type==boolean.class||type==Boolean.class){
            cell.setCellValue((Boolean)value);
        }else if(type==float.class||type==Float.class){
            cell.setCellValue((Float)value);
        }else if(type==char.class||type==Character.class){
            cell.setCellValue((Character)value);
        }else if(type==String.class){
            cell.setCellValue((String)value);
        }else if(type==byte.class||type==Byte.class){
            cell.setCellValue((Byte)value);
        }else if(type==double.class||type==Double.class){
            cell.setCellValue((Double)value);
        }else if(type==Date.class){
            cell.setCellValue((Date)value);
        }else{
            throw new RuntimeException(type+"是不支持的类型！");
        }
    }


    //读取excel文件
    public   List<E> readExcel(InputStream in,E e)throws Exception{
        Class cla=e.getClass();
        //1 创建工作薄:读取源文件
        HSSFWorkbook book=new HSSFWorkbook(in);
        //2 获取第一个表格
        HSSFSheet sheet=book.getSheet(cla.getSimpleName());
        //3 获取第一行：列标题  对应的时类的属性名
        HSSFRow row1=sheet.getRow(0);
        //创建一个数组存储属性名
        List<String> fieldNames=new ArrayList<>();
        Iterator<Cell> it=row1.cellIterator();
        while(it.hasNext()){
            Cell cell=it.next();
            fieldNames.add(cell.getStringCellValue());//存储列明：属性名
        }
        //遍历其他行
        List<E> listObj=new ArrayList<>();
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            HSSFRow rowi=sheet.getRow(i);
            //每行对应一个className类型的对象
            E obj=(E)cla.newInstance();
            for (int j = 0; j <rowi.getLastCellNum(); j++) {
                Object fieldValue;
                HSSFCell cellij=rowi.getCell(j);
                //获取其值
                //System.out.println(cellij.getCellStyle()+":"+cellij.getCellType()+":::"+j);
                if(cellij.getCellType()==CellType.BOOLEAN){
                    fieldValue=cellij.getBooleanCellValue();
                }else if(cellij.getCellType()==CellType.NUMERIC){
                    fieldValue=cellij.getNumericCellValue();
                }else{
                    fieldValue=cellij.getStringCellValue();
                }
                //把值赋值给obj的属性
                Field fieldj=cla.getDeclaredField(fieldNames.get(j));
                fieldj.setAccessible(true);
                fieldValue=changeValue(fieldj,fieldValue);//
                fieldj.set(obj, fieldValue);
            }
            listObj.add(obj);
        }
        book.close();
        return listObj;
    }
    //把value转换为field对应的类型：
    private static Object changeValue(Field field,Object value){
        Class type=field.getType();
        if(type==int.class||type==Integer.class){
            return (int)((double)value);
        }
        if(type==short.class||type==Short.class){
            return (short)((double)value);
        }
        if(type==byte.class||type==Byte.class){
            return (byte)((double)value);
        }
        if(type==long.class||type==Long.class){
            return (long)((double)value);
        }
        if(type==float.class||type==Float.class){
            return (float)((double)value);
        }
        if(type==java.util.Date.class){
            return new Date((long)((double)value));
        }
        return value;
    }
}