apache POI 操作excel<导入导出>
1、首先导入maven依赖
<!-- POI核心依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<!-- 为POI支持Office Open XML -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
2、MicroUtil.java工具类
package com.yinz.tool.j2ee; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Set; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; 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.ss.util.WorkbookUtil; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.yinz.tool.ReflectUtil; /** * * @description: 执行Microsoft文件 相关操作《导出/导入excel表格等》 * 需要用到POI相关jar包 * @author yinz * 2016-11-9 */ public class MicroUtil { public static void main(String[] args) throws Exception { String excelPath = "C:\\Users\\yinz\\Desktop\\场景2\\场景2-样例数据_拜访记录.xls"; Map<Integer, String> indexMapAtt = new HashMap<Integer, String>(); indexMapAtt.put(0, "mbActivityInfoId"); indexMapAtt.put(1, "activity"); indexMapAtt.put(2, "activityName"); indexMapAtt.put(3, "sendDate"); indexMapAtt.put(4, "state"); indexMapAtt.put(5, "isDelete"); indexMapAtt.put(6, "customerId"); indexMapAtt.put(7, "customerGender"); indexMapAtt.put(8, "customerBirthDay"); indexMapAtt.put(9, "empNo"); indexMapAtt.put(10, "noteType"); indexMapAtt.put(11, "contactDate"); indexMapAtt.put(12, "remark"); List<ContactRecord> result = readExcel(excelPath, null, 1, indexMapAtt, ContactRecord.class); System.out.println(result); } /** * 导出excel表格到输出流<可用于web界面导出excel表格> * @param response ： 输出流 * @param result ： 需要导出的数据java对象列表 * @param name ： 文件名 * @param excelHead ： 表格列名 * @param params ： 列对应的对象属性名 * @param isAddIndex ： 是否增加序列号 */ public static void exportExcel(HttpServletResponse response, List<?> result, String name, String[] excelHead, Object[] params, boolean isAddIndex) { //创建excel表格 HSSFWorkbook wb = new HSSFWorkbook(); //长度不能超过31个字符，并且不能包含部分特殊字符，使用如下方法，可将特殊字符替换为空格，长度超过31的进行 name = WorkbookUtil.createSafeSheetName(name); HSSFSheet sheet = wb.createSheet(name); String[] head = null; //如果增加序号，第一列设为序号，重新生成head数组 if (isAddIndex) { head = new String[excelHead.length + 1]; head[0] = "序列号"; for (int i = 0; i < excelHead.length; i++) { head[i+1] = excelHead[i]; } } else { head = excelHead; } for(int i=0;i<head.length;i++){ sheet.setColumnWidth(i, 6000); } //Rows are 0 based. HSSFRow row = sheet.createRow((int) 0); HSSFCellStyle style = wb.createCellStyle(); HSSFCellStyle style2 = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFDataFormat format = wb.createDataFormat(); style2.setDataFormat(format.getFormat("@")); for(int i=0;i<head.length;i++){ HSSFCell cell = row.createCell((short) i); cell.setCellValue(head[i]); cell.setCellStyle(style); cell.setCellType(HSSFCell.CELL_TYPE_STRING); sheet.setDefaultColumnStyle(i, style2); } int count = 1; if (result != null) { for (Object obj : result){ int i = 0; row = sheet.createRow(count); //先增加序号 if (isAddIndex) { row.createCell((short) 0).setCellValue(count); i++; } for (Object attrName:params) { HSSFCell cell = row.createCell((short) i); cell.setCellStyle(style2); Object value = ReflectUtil.getObjAttributeValue(obj, (String)attrName); cell.setCellValue(value == null ? "" : value.toString()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); i++; } count++; } } try { String filename = name+".xls";//设置文件名 response.reset(); response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(filename, "UTF-8")); response.setContentType("application/vnd.ms-excel"); OutputStream fout = response.getOutputStream(); //将excel写到输出流 wb.write(fout); fout.flush(); fout.close(); response.getOutputStream().flush(); response.getOutputStream().close(); } catch (Exception e) { e.printStackTrace(); } } //读取excel表格数据 /** * 读取excel表格内容到java对象 * @param inStream excel文件输入流 * @param sheetPage 要读取的sheet页(0 based)，若为null,则读取第1页 * @param startRow 从第几行开始读取 * @param indexMapAtt excel表单cell与java对象对应关系 * @param clazz 要生成的java类型 * @return * @throws Exception */ public static <T> List<T> readExcel(InputStream inStream, Integer[] sheetPage, Integer startRow, Map<Integer, String> indexMapAtt, Class<T> clazz) throws Exception { startRow = startRow == null ? 0 : startRow; List<T> result = new ArrayList<T>(); Workbook wb = new HSSFWorkbook(inStream);; Sheet sheet = null; if(sheetPage == null || sheetPage.length <= 0) { sheet = wb.getSheetAt(0); readSheet(sheet, result, startRow, indexMapAtt, clazz); } else { for(Integer sheetIndex : sheetPage) { sheet = wb.getSheetAt(sheetIndex); readSheet(sheet, result, startRow, indexMapAtt, clazz); } } return result; } /** * * @param excelPath excel表格路径 * @param sheetPage 要读取的excel中的sheet所有 、0开始 * @param startRow 开始读取的数据的行标 ，0开始 * @param indexMapAtt 列索引与对象属性对应map * @param clazz 每行记录所对应的java对象 * @return * @throws Exception */ @SuppressWarnings("resource") public static <T> List<T> readExcel(String excelPath, Integer[] sheetPage, Integer startRow, Map<Integer, String> indexMapAtt, Class<T> clazz) throws Exception { startRow = startRow == null ? 0 : startRow; List<T> result = new ArrayList<T>(); InputStream inStream = new FileInputStream(excelPath); Workbook wb = null; if(excelPath.matches("^.+\\.(?i)(xls)$")) { //excel2003 wb = new HSSFWorkbook(inStream); } else if(excelPath.matches("^.+\\.(?i)(xlsx)$")) { //excel2007 wb = new XSSFWorkbook(inStream); } else { return null; } Sheet sheet = null; if(sheetPage == null || sheetPage.length <= 0) { sheet = wb.getSheetAt(0); readSheet(sheet, result, startRow, indexMapAtt, clazz); } else { for(Integer sheetIndex : sheetPage) { sheet = wb.getSheetAt(sheetIndex); readSheet(sheet, result, startRow, indexMapAtt, clazz); } } return result; } //读取excel中的sheet数据、并为java对象集合赋值 private static <T> void readSheet(Sheet sheet, List<T> result, Integer startRow, Map<Integer, String> indexMapAtt, Class<T> resultType) throws Exception { //获取总行数 int totalRows = sheet.getPhysicalNumberOfRows(); T t; for(int i = startRow; i < totalRows; i++) { Row row = sheet.getRow(i); if(row == null) { continue; } t = resultType.newInstance(); Set<Integer> cellIndexSet = indexMapAtt.keySet(); for(Integer index : cellIndexSet) { Cell cell = row.getCell(index); if(cell == null) { continue; } cell.setCellType(HSSFCell.CELL_TYPE_STRING); ReflectUtil.setObjAttributeValue(t, indexMapAtt.get(index), cell.getStringCellValue() == null ? "" : cell.getStringCellValue()); } result.add(t); } } }
反射工具类：ReflectUtil.java
package com.yinz.tool; import java.lang.reflect.Field; import java.util.HashMap; import java.util.Map; /** * * @description: 反射工具类 * @author yinz * 2016-11-9 */ public class ReflectUtil { /** * 将对象属性和值以map存储，默认包含属性值为null的属性 * @param obj * @return */ public static Map<String, Object> obj2map(Object obj) { return obj2map(obj, true); } /** * 将对象属性和值以map存储 * @param obj * @param isContainNullValue ： 是否包含属性中为空的属性 * @return */ public static Map<String, Object> obj2map(Object obj, boolean isContainNullValue) { Map<String, Object> map = new HashMap<String, Object>(); Field[] fList = obj.getClass().getDeclaredFields(); try { for(Field f : fList) { f.setAccessible(true); if(isContainNullValue) { map.put(f.getName(), f.get(obj)); } else { if(f.get(obj) != null && f.get(obj) != "") { map.put(f.getName(), f.get(obj)); } } } } catch (Exception e) { return null; } return map; } /** * 获取对象指定属性值 * @param obj * @param attName * @return */ public static Object getObjAttributeValue(Object obj, String attName) { return getObjAttributeValue(obj, attName, ""); } /** * 获取对象指定属性的值 * @param obj : 查询对象 * @param attName ： 要获取的属性名 * @param defVal ： 默认值 * @return */ public static Object getObjAttributeValue(Object obj, String attName, String defVal) { Object value = null; Field field = null; try { field = obj.getClass().getDeclaredField(attName); field.setAccessible(true); value = field.get(obj); } catch (Exception e) { return defVal; } if(value == null || value.toString().trim().length() <= 0) { value = defVal; } return value; } /** * 为对象属性赋值 * @param obj 要赋值的对象 * @param attName 对象属性名 * @param value 属性值 */ public static void setObjAttributeValue(Object obj, String attName, String value) { Field field = null; try { field = obj.getClass().getDeclaredField(attName); field.setAccessible(true); field.set(obj, value); } catch (Exception e) { } } }
实体类：ContactRecord.java
package com.tianwen.nlp.pojo; public class ContactRecord { private Integer id; private String mbActivityInfoId; private String activityType; private String activityName; private String sendDate; private String state; private String isDelete; private String customerId; private String customerGender; private String customerBirthday; private String empNo; private String noteType; private String contactDate; private String remark; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getMbActivityInfoId() { return mbActivityInfoId; } public void setMbActivityInfoId(String mbActivityInfoId) { this.mbActivityInfoId = mbActivityInfoId == null ? null : mbActivityInfoId.trim(); } public String getActivityType() { return activityType; } public void setActivityType(String activityType) { this.activityType = activityType == null ? null : activityType.trim(); } public String getActivityName() { return activityName; } public void setActivityName(String activityName) { this.activityName = activityName == null ? null : activityName.trim(); } public String getSendDate() { return sendDate; } public void setSendDate(String sendDate) { this.sendDate = sendDate == null ? null : sendDate.trim(); } public String getState() { return state; } public void setState(String state) { this.state = state == null ? null : state.trim(); } public String getIsDelete() { return isDelete; } public void setIsDelete(String isDelete) { this.isDelete = isDelete == null ? null : isDelete.trim(); } public String getCustomerId() { return customerId; } public void setCustomerId(String customerId) { this.customerId = customerId == null ? null : customerId.trim(); } public String getCustomerGender() { return customerGender; } public void setCustomerGender(String customerGender) { this.customerGender = customerGender == null ? null : customerGender.trim(); } public String getCustomerBirthday() { return customerBirthday; } public void setCustomerBirthday(String customerBirthday) { this.customerBirthday = customerBirthday == null ? null : customerBirthday.trim(); } public String getEmpNo() { return empNo; } public void setEmpNo(String empNo) { this.empNo = empNo == null ? null : empNo.trim(); } public String getNoteType() { return noteType; } public void setNoteType(String noteType) { this.noteType = noteType == null ? null : noteType.trim(); } public String getContactDate() { return contactDate; } public void setContactDate(String contactDate) { this.contactDate = contactDate == null ? null : contactDate.trim(); } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark == null ? null : remark.trim(); } }
此处用与上传的excel文件：https://files.cnblogs.com/files/yinz/场景2-样例数据_拜访记录.rar
