0)poi简单介绍
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF － 提供读写Microsoft Excel格式档案的功能。 XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。 HWPF － 提供读写Microsoft Word格式档案的功能。 HSLF － 提供读写Microsoft PowerPoint格式档案的功能。 HDGF － 提供读写Microsoft Visio格式档案的功能。
1）导入依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.httpcomponents</groupId><artifactId>httpclient</artifactId><version>4.5.6</version></dependency>
2）写个ExcelUtil 工具类
package com.example.tool;import java.beans.IntrospectionException;import java.beans.PropertyDescriptor;import java.io.InputStream;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Map;import com.example.bean.ExcelBean;import org.apache.http.client.utils.DateUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;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.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** @ClassName ExcelUtil* @Description TODO* @Author 胡泽* @Date /8/31 9:31* @Version 1.0*/public class ExcelUtil {private final static String excelL = ".xls"; // - 版本的excelprivate final static String excelU = ".xlsx"; // + 版本的excel/*** Excel导入*/public static List<List<Object>> getUserListByExcel(InputStream in, String fileName) throws Exception {List<List<Object>> list = null;// 创建Excel工作薄Workbook work = getWorkbook(in, fileName);if (null == work) {throw new Exception("创建Excel工作薄为空！");}Sheet sheet = null;Row row = null;Cell cell = null;list = new ArrayList<List<Object>>();// 遍历Excel中所有的sheetfor (int i = 0; i < work.getNumberOfSheets(); i++) {sheet = work.getSheetAt(i);if (sheet == null) {continue;}// 遍历当前sheet中的所有行// 包含头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {// 读取一行row = sheet.getRow(j);// 去掉空行和表头if (row == null || row.getFirstCellNum() == j) {continue;}// 遍历所有的列List<Object> li = new ArrayList<Object>();for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {cell = row.getCell(y);li.add(getCellValue(cell));}list.add(li);}}return list;}/*** 描述：根据文件后缀，自适应上传文件的版本*/public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {Workbook wb = null;String fileType = fileName.substring(fileName.lastIndexOf("."));if (excelL.equals(fileType)) {wb = new HSSFWorkbook(inStr); // -} else if (excelU.equals(fileType)) {wb = new XSSFWorkbook(inStr); // +} else {throw new Exception("解析的文件格式有误！");}return wb;}/*** 描述：对表格中数值进行格式化*/public static Object getCellValue(Cell cell) {Object value = null;DecimalFormat df = new DecimalFormat("0"); // 格式化字符类型的数字SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss"); // 日期格式化DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字if (cell.getCellType() == CellType.STRING) {value = cell.getRichStringCellValue().getString();} else if (cell.getCellType() == CellType.NUMERIC) {if ("General".equals(cell.getCellStyle().getDataFormatString())) {value = df.format(cell.getNumericCellValue());} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {value = sdf.format(cell.getDateCellValue());} else {value = df2.format(cell.getNumericCellValue());}} else if (cell.getCellType() == CellType.BOOLEAN) {value = cell.getBooleanCellValue();} else if (cell.getCellType() == CellType.BLANK) {value = "";}return value;}/*** 导入Excel表结束 导出Excel表开始** @param sheetName* 工作簿名称* @param clazz* 数据源model类型* @param objs* excel标题列以及对应model字段名* @param map* 标题列行数以及cell字体样式*/public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<ExcelBean>> map,String sheetName) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException,ClassNotFoundException, IntrospectionException {// 创建新的Excel工作簿XSSFWorkbook workbook = new XSSFWorkbook();// 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称XSSFSheet sheet = workbook.createSheet(sheetName);// 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;createTableHeader(sheet, map); //创建标题（头）createTableRows(sheet, map, objs, clazz); // 创建内容return workbook;}/*** @param sheet* 工作簿* @param map* 每行每个单元格对应的列头信息*/public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {XSSFRow row = sheet.createRow(entry.getKey());List<ExcelBean> excels = entry.getValue();for (int x = 0; x < excels.size(); x++) {XSSFCell cell = row.createCell(x);cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容}}}public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz)throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException{int rowindex = map.size();int maxKey = 0;List<ExcelBean> ems = new ArrayList<>();for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {if (entry.getKey() > maxKey) {maxKey = entry.getKey();}}ems = map.get(maxKey);for (Object obj : objs) {XSSFRow row = sheet.createRow(rowindex);for (int i = 0; i < ems.size(); i++) {ExcelBean em = (ExcelBean) ems.get(i);// 获得get方法PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);Method getMethod = pd.getReadMethod();Object rtn = getMethod.invoke(obj);String value = "";// 如果是日期类型进行转换if (rtn != null) {if (rtn instanceof Date) {value = DateUtils.formatDate((Date) rtn, "yyyy-MM-dd");} else {value = rtn.toString();}}XSSFCell cell = row.createCell(i);cell.setCellValue(value);cell.setCellType(CellType.STRING);}rowindex++;}}}
3）实体类
package com.example.bean;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import java.io.Serializable;/*** @ClassName ExcelBean* @Description TODO* @Author 胡泽* @Date /8/31 9:33* @Version 1.0*/public class ExcelBean implements Serializable {private String headTextName; // 列头（标题）名private String propertyName; // 对应字段名private Integer cols; // 合并单元格数private XSSFCellStyle cellStyle;public ExcelBean() {}public ExcelBean(String headTextName, String propertyName) {this.headTextName = headTextName;this.propertyName = propertyName;}public ExcelBean(String headTextName, String propertyName, Integer cols) {super();this.headTextName = headTextName;this.propertyName = propertyName;this.cols = cols;}public String getHeadTextName() {return headTextName;}public void setHeadTextName(String headTextName) {this.headTextName = headTextName;}public String getPropertyName() {return propertyName;}public void setPropertyName(String propertyName) {this.propertyName = propertyName;}public Integer getCols() {return cols;}public void setCols(Integer cols) {this.cols = cols;}public XSSFCellStyle getCellStyle() {return cellStyle;}public void setCellStyle(XSSFCellStyle cellStyle) {this.cellStyle = cellStyle;}}
4）jsp页面
<%--Created by IntelliJ IDEA.User: adminDate: /8/30Time: 9:02To change this template use File | Settings | File Templates.--%><%@ page contentType="text/html;charset=UTF-8" language="java" %><%@ taglib prefix="c" uri ="/jsp/jstl/core"%><html><head><title>Title</title></head><body><%--<form action="list" method="post" enctype="multipart/form-data" ><input type="file" name="myfile" /><input type="submit" value="导入"/></form>--%><form action="getExcelData" method="post" enctype="multipart/form-data" ><input type="file" name="myfile" /><input type="submit" value="导入"/></form><hr/><form action="export" method="post"><input type="submit" value="导出"></form></body></html>
5）controller 类
@RequestMapping(value = "/getExcelData", method = RequestMethod.POST)public String getExcelData(MultipartFile myfile, Model model) {try {List<List<Object>> lists = ExcelUtil.getUserListByExcel(myfile.getInputStream(), myfile.getOriginalFilename());//List<List<Object>>--->List<User>List<UserInfo> users = new ArrayList<>();//for (int i = 0; i < lists.size(); i++) {UserInfo user = new UserInfo();List<Object> ob = lists.get(i);//List<Object>user.setId(Integer.parseInt(ob.get(0).toString()));//"1"user.setUsername(ob.get(1).toString());//ob.get(4).toString();//Object-->String("-08-30 12:12:12")--->DateSimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");System.out.println("1111111111111111111"+ob.get(4).toString());Date time = sdf.parse(ob.get(4).toString());user.setCreatedate(time);users.add(user);}//插入数据userInfoService.insert(users);} catch (Exception e) {e.printStackTrace();}UserInfoExample userExample = new UserInfoExample();List<UserInfo> allusers = userInfoService.selectByExample(userExample);model.addAttribute("all", allusers);return "display";}
6)效果图
哈哈哈哈哈哈哈哈！！！！！
