excel导入导出(基于org.apache.poi)

package com.cnmts.common.util;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Constructor;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.lang.reflect.Type;import java.util.ArrayList;import java.util.Date;import java.util.List;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.poifs.filesystem.NotOLE2FileException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;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;public class ExcelUtil<T> {private File file;private InputStream inputStream;private int ignoreRow = 1;// 忽略的行数private Class<T> entityClass = null;private String[] fields;// 要写入的字段private String[] header;private String sheetName = "sheet1";private String par = "yyyy-MM-dd";private static final String HSSF = "application/vnd.ms-excel";private static final String XSSF = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";/** * * @param className * 传入类的包名 类名 * @param fileToBeRead * 要读取的文件 * * @param fields * 要写入的目标类的哪些字段，请按照excel的列的顺序 * @throws ClassNotFoundException * @throws FileNotFoundException */public ExcelUtil(Class<T> clazz, String filePath, String... fields) throws ClassNotFoundException, FileNotFoundException {init(clazz, new File(filePath), fields);}public ExcelUtil(Class<T> clazz, File file, String... fields) throws ClassNotFoundException, FileNotFoundException {init(clazz, file, fields);}public ExcelUtil(Class<T> clazz, String... fields) throws FileNotFoundException {this.entityClass = clazz;this.fields = fields;}private void init(Class<T> clazz, File file, String... fields) throws FileNotFoundException {this.entityClass = clazz;this.file = file;this.inputStream = new FileInputStream(this.file);this.fields = fields;}/** * 设置工作薄名称 * * @param sheetName */public void setSheetName(String sheetName) {this.sheetName = sheetName;}/** * 设置头部 * * @param header */public void setHeader(String[] header) {this.header = header;}public List<T> convertToList() {List<T> list = null;try {list = toList2003();} catch (Exception e2) {e2.printStackTrace();try {list = toList2007();} catch (Exception e1) {e1.printStackTrace();}}return list;}public List<T> convertToList(String fileType) throws FileNotFoundException, NoSuchMethodException, SecurityException, InstantiationException, IllegalAccessException, IllegalArgumentException,InvocationTargetException, IOException, NotOLE2FileException {List<T> list = null;try {if (fileType != null && fileType.equals(HSSF)) {list = toList2003();} else if (fileType != null && fileType.equals(XSSF)) {list = toList2007();}} catch (Exception e) {e.printStackTrace();} finally {if (inputStream != null) {inputStream.close();}}return list;}/** * 设置忽略的行数，默认忽略第一行 * * @param ignoreRow * 要忽略的行数，默认忽略第一行 */public void setIgnoreRow(int ignoreRow) {this.ignoreRow = ignoreRow;}public List<T> toList2003() throws FileNotFoundException, IOException, NoSuchMethodException, SecurityException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {// 创建对工作表的引用。HSSFWorkbook workbook = new HSSFWorkbook(this.inputStream);HSSFSheet sheet = workbook.getSheetAt(0);// 在Excel文档中，第一张工作表的缺省索引是0，// 用于存储目标对象，excel每一行都是一个实体对象List<T> list = new ArrayList<T>();// 创建泛类的实例对象Constructor<T> constructor = entityClass.getConstructor();// Field[] objField = entityClass.getFields();Method[] methods = entityClass.getMethods();// 读取左上端单元HSSFRow row = null;for (int i = ignoreRow; sheet.getRow(i) != null; i ) {// 指针指向第i行row = sheet.getRow(i);T instance = constructor.newInstance();for (int j = 0; j < fields.length; j ) {String field = fields[j];for (Method method : methods) {String methodName = method.getName();String prefix = methodName.substring(0, 3);String suffix = methodName.substring(3);if (prefix.equals("set") && suffix.equalsIgnoreCase(field)) {HSSFCell cell = null;cell = row.getCell(j);if (cell == null) {continue;}// 单元格类型int cellType = cell.getCellType();if (cellType == Cell.CELL_TYPE_NUMERIC) {double value = row.getCell(j).getNumericCellValue();if (DateUtil.isCellDateFormatted(cell)) {// 日期Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);method.invoke(instance, date);continue;}setValue(method, instance, value);} else if (cellType == Cell.CELL_TYPE_STRING) {String value = row.getCell(j).getStringCellValue();setValue(method, instance, value);} else if (cellType == Cell.CELL_TYPE_FORMULA) {// 公式型try {double value = cell.getNumericCellValue();setValue(method, instance, value);} catch (IllegalStateException e) {String value = String.valueOf(cell.getRichStringCellValue());setValue(method, instance, value);}}}}}list.add(instance);}workbook.close();return list;}public List<T> toList2007() throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, FileNotFoundException, IOException, NoSuchMethodException, SecurityException, InstantiationException {// 创建对工作表的引用。XSSFWorkbook workbook = new XSSFWorkbook(this.inputStream);XSSFSheet sheet = workbook.getSheetAt(0);// 在Excel文档中，第一张工作表的缺省索引是0，// 用于存储目标对象，excel每一行都是一个实体对象List<T> list = new ArrayList<T>();// 创建泛类的实例对象Constructor<T> constructor = entityClass.getConstructor();// Field[] objField = entityClass.getFields();Method[] methods = entityClass.getMethods();// 读取左上端单元XSSFRow row = null;for (int i = ignoreRow; sheet.getRow(i) != null; i ) {// 指针指向第i行row = sheet.getRow(i);T instance = constructor.newInstance();for (int j = 0; j < fields.length; j ) {String field = fields[j];for (Method method : methods) {String methodName = method.getName();String prefix = methodName.substring(0, 3);String suffix = methodName.substring(3);if (prefix.equals("set") && suffix.equalsIgnoreCase(field)) {XSSFCell cell = null;cell = row.getCell(j);if (cell == null) {continue;}int cellType = cell.getCellType();if (cellType == Cell.CELL_TYPE_NUMERIC) {double value = row.getCell(j).getNumericCellValue();if (DateUtil.isCellDateFormatted(cell)) {// 日期Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);method.invoke(instance, date);continue;}setValue(method, instance, value);} else if (cellType == Cell.CELL_TYPE_STRING) {String value = row.getCell(j).getStringCellValue();setValue(method, instance, value);} else if (cellType == Cell.CELL_TYPE_FORMULA) {// 公式型try {double value = cell.getNumericCellValue();setValue(method, instance, value);} catch (IllegalStateException e) {String value = String.valueOf(cell.getRichStringCellValue());setValue(method, instance, value);}}}}}list.add(instance);}workbook.close();return list;}/** * * 去掉字符串右边的空格 * * @param str * 要处理的字符串 * * @return 处理后的字符串 */private static String rlTrim(String str) {// 去除ascii 160if (str.startsWith(" ")) {int startIndex = str.indexOf(" ");str = str.substring(startIndex 1, str.length());}if (str.endsWith(" ")) {int endIndex = str.lastIndexOf(" ");str = str.substring(0, endIndex);}// 去除ascii 32return str.trim();}/** * 注入值 * * @param method * @param instance * @param value * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException */private void setValue(Method method, T instance, double value) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {Class<?>[] parameterTypes = method.getParameterTypes();Class<?> class1 = parameterTypes[0];if (class1.equals(String.class)) {method.invoke(instance, rlTrim(value ""));} else if (class1.equals(int.class)) {method.invoke(instance, (int) value);} else if (class1.equals(Integer.class)) {method.invoke(instance, (int) value);} else if (class1.equals(double.class)) {method.invoke(instance, value);} else {method.invoke(instance, value);}}/** * 注入值 * * @param method * @param instance * @param value * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException */private void setValue(Method method, T instance, String value) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {Class<?>[] parameterTypes = method.getParameterTypes();Class<?> class1 = parameterTypes[0];if (class1.equals(String.class)) {method.invoke(instance, rlTrim(value));} else if (class1.equals(int.class)) {method.invoke(instance, Integer.parseInt(value));} else if (class1.equals(Integer.class)) {method.invoke(instance, Integer.parseInt(value));} else if (class1.equals(double.class)) {method.invoke(instance, Double.parseDouble(value));} else {method.invoke(instance, rlTrim(value));}}private List<T> convertSheetToList(Sheet sheet, Integer ignoreRow, String... fields) throws FileNotFoundException, IOException, NoSuchMethodException, SecurityException, InstantiationException,IllegalAccessException, IllegalArgumentException, InvocationTargetException, ClassNotFoundException {// 用于存储目标对象，excel每一行都是一个实体对象List<T> list = new ArrayList<T>();// 创建泛类的实例对象Constructor constructor = entityClass.getConstructor(null);Field[] objField = entityClass.getFields();Method[] methods = entityClass.getMethods();// 读取左上端单元Row row = null;for (int i = ignoreRow; sheet.getRow(i) != null; i ) {// 指针指向第i行row = sheet.getRow(i);T instance = (T) constructor.newInstance(null);for (int j = 0; j < fields.length; j ) {String field = fields[j];for (Method method : methods) {String methodName = method.getName();String prefix = methodName.substring(0, 3);String suffix = methodName.substring(3);if (prefix.equals("set") && suffix.equalsIgnoreCase(field)) {Cell cell = null;cell = row.getCell(j);if (cell == null) {continue;}// 单元格类型int cellType = cell.getCellType();if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {double value = row.getCell(j).getNumericCellValue();Type[] types = method.getGenericParameterTypes();for (Type type : types) {String parameterName = ((Class) type).getSimpleName();if (parameterName.equals("String")) {method.invoke(instance, rlTrim((int) value ""));} else if (parameterName.equals("int")) {method.invoke(instance, (int) value);} else if (parameterName.equals("Integer")) {method.invoke(instance, (int) value);} else if (parameterName.equalsIgnoreCase("double")) {method.invoke(instance, value);} else {method.invoke(instance, value);}}} else if (cellType == HSSFCell.CELL_TYPE_STRING) {String value = row.getCell(j).getStringCellValue();Type[] types = method.getGenericParameterTypes();for (Type type : types) {String parameterName = ((Class) type).getSimpleName();if (parameterName.equals("String")) {method.invoke(instance, rlTrim(value));} else if (parameterName.equals("int")) {method.invoke(instance, Integer.parseInt(value));} else if (parameterName.equals("Integer")) {method.invoke(instance, Integer.parseInt(value));} else if (parameterName.equalsIgnoreCase("double")) {method.invoke(instance, Double.parseDouble(value));} else {method.invoke(instance, rlTrim(value));}}} else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {// 公式型String value = "";try {value = String.valueOf(cell.getNumericCellValue());} catch (IllegalStateException e) {value = String.valueOf(cell.getRichStringCellValue());}Type[] types = method.getGenericParameterTypes();for (Type type : types) {String parameterName = ((Class) type).getSimpleName();if (parameterName.equals("String")) {method.invoke(instance, rlTrim(value));} else if (parameterName.equals("int")) {method.invoke(instance, Integer.parseInt(value));} else if (parameterName.equals("Integer")) {method.invoke(instance, Integer.parseInt(value));} else {method.invoke(instance, rlTrim(value));}}}}}}list.add(instance);}return list;}/** * List 转Excel * * @return * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalAccessException * @throws IllegalArgumentException */public ByteArrayOutputStream convertList2Excel(List<T> list) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet(sheetName);// 创建表头int index = 0;if (header != null) {HSSFRow row = sheet.createRow(index);for (int i = 0; i < header.length; i ) {HSSFCell cell = row.createCell(i);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(header[i]);}index ;}for (int i = 0; i < list.size(); i ) {HSSFRow row = sheet.createRow(i index);T instance = list.get(i);for (int j = 0; j < fields.length; j ) {String field = fields[j];Field entityField = entityClass.getDeclaredField(field);if (!entityField.isAccessible()) {entityField.setAccessible(true);}Cell cell = row.createCell(j);Object object = entityField.get(instance);if (object instanceof String) {cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue((String) object);}if (object instanceof Double) {cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);Double num = (double) object;if (num != 0) {cell.setCellValue(num);} else {cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue("");}}if (object instanceof Float) {cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);cell.setCellValue((float) object);}if (object instanceof Integer) {cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);cell.setCellValue((int) object);}if (object instanceof Date) {cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue((Date) object);CreationHelper createHelper = workbook.getCreationHelper();CellStyle cellStyle = workbook.createCellStyle();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(par));cell.setCellStyle(cellStyle);}}}ByteArrayOutputStream outputStream = new ByteArrayOutputStream();try {workbook.write(outputStream);workbook.close();} catch (IOException e) {e.printStackTrace();}return outputStream;}public void setPar(String par) {this.par = par;}}