实例介绍
【实例简介】
【实例截图】
【实例截图】
【核心代码】
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;
}
}
好例子网口号：伸出你的我的手 — 分享！
小贴士
感谢您为本站写下的评论，您的评论对其它用户来说具有重要的参考价值，所以请认真填写。
- 类似“顶”、“沙发”之类没有营养的文字，对勤劳贡献的楼主来说是令人沮丧的反馈信息。
- 相信您也不想看到一排文字/表情墙，所以请不要反馈意义不大的重复字符，也请尽量不要纯表情的回复。
- 提问之前请再仔细看一遍楼主的说明，或许是您遗漏了。
- 请勿到处挖坑绊人、招贴广告。既占空间让人厌烦，又没人会搭理，于人于己都无利。
关于好例子网
本站旨在为广大IT学习爱好者提供一个非营利性互相学习交流分享平台。本站所有资源都可以被免费获取学习研究。本站资源来自网友分享，对搜索内容的合法性不具有预见性、识别性、控制性，仅供学习研究，请务必在下载后24小时内给予删除，不得用于其他任何用途，否则后果自负。基于互联网的特殊性，平台无法对用户传输的作品、信息、内容的权属或合法性、安全性、合规性、真实性、科学性、完整权、有效性等进行实质审查；无论平台是否已进行审查，用户均应自行承担因其传输的作品、信息、内容而可能或已经产生的侵权或权属纠纷等法律责任。本站所有资源不代表本站的观点或立场，基于网友分享，根据中国法律《信息网络传播权保护条例》第二十二与二十三条之规定，若资源存在侵权或相关问题请联系本站客服人员，点此联系我们。关于更多版权及免责申明参见 版权及免责申明
网友评论
我要评论