基本信息

---

下载

   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

---

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300

本次赞助数额为： 2 元　

支付宝支付

微信支付

   源码介绍

---

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 160
		if (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 32
		return 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;
	}

}