Poi简单使用

Poi

文档读写

先导入jar包
下载地址：https://www.apache.org/dyn/closer.lua/poi/release/bin/poi-bin-4.0.0-20180907.tar.gz

写一个poi工具类读写excle文件

//使用泛型使其可以读写任意对象的信息，将每一个对象读成ｅｘｃｅｌ的一行
//使用此工具类需要保证传入对象的对象类型为
package com.zhiyou100.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

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;

import com.zhiyou100.entity.Car;

public class PoiUtil<E>{
	public static void main(String[] args) throws Exception{
        //测试一下
		PoiUtil<Car>  pu=new PoiUtil<>();
		List<Car> list=new ArrayList<>();
		//写excel文件，把每一个对象写成excel中的一行
		list.add(new Car("car_011", "大众", "豫A1111", new Date(), 2, "黑", 30001f, "11.jpg", 1));
		list.add(new Car("car_012", "本田", "豫A1114", new Date(), 1, "白", 30002f, "12.jpg", 1));
		list.add(new Car("car_013", "丰田", "豫A1112", new Date(), 3, "红", 30003f, "13.jpg", 1));
		list.add(new Car("car_014", "日产", "豫A1113", new Date(), 4, "黑", 30004f, "14.jpg", 1));
		pu.createExcel(list, "D:\\poi");
		//读excel文件，把每一行读成一个对象
		list=pu.readExcel(new FileInputStream(new File("D:\\poi\\Car.xls")), new Car());
		System.out.println(list);  
	}
	//创建excel文件
	//使用反射把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;
	}
}

写个页面验证一下

• 实体类Car

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Car  implements Serializable{
    private String carId;
    private String carLogo;
    private String carNumber;
    
    @DateTimeFormat(pattern="yyyy-MM-dd")
    private Date carTime;
    
    private Integer carZuo;
    private String carColor;
    private Float carPrice;
    private String carPhoto;
    private Integer carStatus;
}

• 实现

@RequestMapping("/car")
@Controller
public class CarController {
	
	@Autowired
	private CarService carService;
	@RequestMapping("/excel/createFile.action")
	public ResponseEntity<byte[]> excelCreateFileMethod(HttpServletRequest req)throws Exception{
		//读取数据库
		List<Car> list=carService.getAll();
		//创建excel
		PoiUtil<Car> poi=new PoiUtil<>();
		String path=req.getServletContext().getRealPath("/");
		poi.createExcel(list, path);
		
		File file=new File(path,"Car.xls");
		
		//把excel以附件的形式响应给客户端
		//获取文件的类型
        String type=req.getServletContext().getMimeType(file.getName());

        byte[] arr=FileUtils.readFileToByteArray(file);

        //创建heander对象  响应头
        HttpHeaders headers=new HttpHeaders();
        headers.set("Content-Type", type);
        headers.set("Content-Disposition", "attachment;filename="+URLEncoder.encode(file.getName(), "utf-8"));
        //获取源文件
        //context.get
        ResponseEntity<byte[]>  entity=new ResponseEntity<byte[]>(arr,headers,HttpStatus.OK);
        return entity;	
	}
    //读取传入的excel文件，封装为对象，存入数据库
	@RequestMapping("/excel/readFile.action")
	public String excelReadFileMethod(@RequestParam("excel")MultipartFile excel,HttpServletRequest req)throws Exception{
		PoiUtil<Car> poi=new PoiUtil<>();
		List<Car> list=poi.readExcel(excel.getInputStream(), new Car());
		for (Car car : list) {
			carService.addOne(car);
		}
		req.setAttribute("message", "读取excel成功！");
		return "car_manager";
	}
} 

• 页面

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>    

<div class="div_a">
    <a href="<c:url value='/car/excel/createFile.action'/>">导出excel</a>     |
    <form action="<c:url value='/car/excel/readFile.action'/>" method="post" enctype="multipart/form-data" style="display: inline;">
        <input type="file" name="excel"/>
        <input type="submit" value="读取excel"/>
    </form>
</div>

• service和dao层 略

结果展示

• 读数据库数据到excel