说明:数据库使用mysql,导入的字段类型只能是有varchar,Date 类型
火狐导出文件时做特殊设置,不能用url编码
ExcelUtil.java:
package com.lw.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FilenameUtils;
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.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
/**
* Excel组件
*
* @author david
* @version 1.0
* @since 1.0
*/
public class ExcelUtil {
/**
* Excel 2003
*/
private final static String XLS = "xls";
/**
* Excel 2007
*/
private final static String XLSX = "xlsx";
/***
* 导入Excel数据
*
* <li>1、读取excel数据</li>
* <li>2、校验数据的合法性(日期,金额,字符长度(和数据库结构比较))</li>
* <li>3、合法数据绑定到bean对象中(反射)</li>
* <li>4、得到数据层面校验通过的bean对象集合,</li>
*
* @param file
* 导入数据文件
* @param entityClass
* bean对象类型bean.class
* @param sheetIndex
* sheet索引
* @param columnArray
* 字段列数组 (需要导入的字段数组)
* @param checkColumn
* 需要校验格式的字段列Map
* @throws IOException
* @throws NoSuchFieldException
* @throws SecurityException
* @throws InstantiationException
* @throws SQLException
* @throws IllegalAccessException
* @throws ParseException
*/
public static <T> List<T> excelToList(File file, Integer sheetIndex, Class<T> entityClass, String[] columnArray,
Map<String, ColumnCheckTypeEnum> checkColumn) throws IOException, SecurityException, NoSuchFieldException,
InstantiationException, SQLException, IllegalAccessException, ParseException {
List<T> list = new ArrayList<T>();
Workbook workbook = null;
if (XLS.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) {
workbook = new HSSFWorkbook(new FileInputStream(file));
} else if (XLSX.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) {
workbook = new XSSFWorkbook(new FileInputStream(file));
} else {
throw new IOException("导入excel出错,不支持文件类型!");
}
if (sheetIndex == null) {
sheetIndex = 0;
}
if ((sheetIndex + 1) > workbook.getNumberOfSheets()) {
throw new IndexOutOfBoundsException("导入excel出错,指定sheet索引越界!");
}
// sheet中要导出的列
if (columnArray == null || columnArray.length < 1) {
throw new NullPointerException("导入excel出错,导入列设置错误!");
}
// 拿到sheet
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName = sheet.getSheetName(); // sheetName 使用表名称
// 解析公式结果
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
// 每个sheet中的数据
List<Map<String, Object>> dataList = readSheet(sheet, evaluator, columnArray);
// 数据库表对应的字段列信息
List<ColumnData> columnDatas = null;
try {
columnDatas = DBDataUtil.getMySqlColumnDatas(sheetName, "dbName");
} catch (SQLException e) {
throw new SQLException("导入excel出错,获取表信息错误!");
}
// 不在数据库中的列
List<String> outColumnDatas = new ArrayList<String>();
List<String> columnList = new ArrayList<String>();
for (int i = 0; i < columnDatas.size(); i++) {
columnList.add(columnDatas.get(i).getColumnLowName());
}
for (int i = 0; i < columnArray.length; i++) {
if (!columnList.contains(columnArray[i])) {
outColumnDatas.add(columnArray[i]);
}
}
// 遍历数据库表对应的字段列信息
for (int j = 0; j < dataList.size(); j++) {
// 拿到每一行的数据
Map<String, Object> rowData = dataList.get(j);
T o = null;
try {
o = (T) entityClass.newInstance();
} catch (IllegalAccessException e) {
throw new IllegalAccessException("导入excel出错,错误信息:" + e.getMessage());
}
if (checkColumn == null) {
checkColumn = new HashMap<String, ColumnCheckTypeEnum>();
}
// 1、循环列(数据库中包含)设置,依次设置每一列
for (int k = 0; k < columnDatas.size(); k++) {
ColumnData cd = columnDatas.get(k);
Object value = rowData.get(cd.getColumnLowName());
String errMsg = "导入excel出错,错误位置>>:第【" + (j + 1 + 1) + "】行,第【"
+ (getIndexOfArrayItem(cd.getColumnLowName(), columnArray) + 1 + 1) + "】列。错误信息:";
// 1、先根据设置,校验自定义校验的列
if (checkColumn.containsKey(cd.getColumnLowName()) == false || ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL
.equals(checkColumn.get(cd.getColumnLowName())) == false) {
if (value == null) {
continue;
}
}
ColumnCheckTypeEnum checkType = checkColumn.get(cd.getColumnLowName());
customColumnCheck(value, errMsg, checkType);
// 2、自定义校验完成后,进行常规校验
// 通过属性名称获取属性,把值设置到属性里面
Field field = entityClass.getDeclaredField(cd.getColumnLowName());
field.setAccessible(true); // 设置属性可访问, private
if ("java.lang.String".equalsIgnoreCase(cd.getJavaType())) {
if (value.toString().length() > Integer.parseInt(cd.getDataMaxLength())) {
throw new SQLException(errMsg + "当前数据长度不能超过【" + cd.getDataMaxLength() + "】,当前文本长度【" + value.toString().length() + "】");
}
try {
field.set(o, value.toString());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
} else if ("java.util.Date".equalsIgnoreCase(cd.getJavaType())) {
try {
field.set(o, (Date) value);
} catch (ClassCastException e) {
throw new ClassCastException(errMsg + "数据格式错误,无法将【" + value + "】转换为java.util.Date类型日期格式。" + e.getMessage());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
} else if ("java.lang.Integer".equalsI