ExcelUtil导入导出
范建华
说明：数据库使用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".equalsIgnoreCase(cd.getJavaType())) {
try {
if(value!=null && !"".equals(value.toString())) {
field.set(o, Integer.parseInt(value.toString()));
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "数据格式错误，无法将【" + value + "】转换为java.lang.Integer类型。" + e.getMessage());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
} else if ("java.math.BigDecimal".equalsIgnoreCase(cd.getJavaType())) {
try {
if(value!=null && !"".equals(value.toString())) {
field.set(o, new BigDecimal(value.toString()));
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "数据格式错误，无法将【" + value + "】转换为java.math.BigDecimal类型。" + e.getMessage());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
}
}
// 2、循环列（数据库中不包含）设置，依次设置每一列
for (int k = 0; k < outColumnDatas.size(); k++) {
String columnLowName = outColumnDatas.get(k);
Object value = rowData.get(outColumnDatas.get(k));
String errMsg = "导入excel出错，错误位置>>：sheet【" + sheetName + "】中，第【" + (j + 1 + 1) + "】行，第【"
+ (getIndexOfArrayItem(columnLowName, columnArray) + 1 + 1) + "】列。错误信息：";
// 1、先根据设置，校验自定义校验的列
if (checkColumn.containsKey(columnLowName) == false
|| ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL.equals(checkColumn.get(columnLowName)) == false) {
if (value == null) {
continue;
}
}
customColumnCheck(value, errMsg, checkColumn.get(columnLowName));
// 通过属性名称获取属性，把值设置到属性里面
Field field = entityClass.getDeclaredField(columnLowName);
field.setAccessible(true); // 设置属性可访问， private
String type = field.getType().getName();
if ("java.lang.String".equalsIgnoreCase(type)) {
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(type)) {
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".equalsIgnoreCase(type)) {
try {
if(value!=null && !"".equals(value.toString())) {
field.set(o, Integer.parseInt(value.toString()));
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "数据格式错误，无法将【" + value + "】转换为java.lang.Integer类型。" + e.getMessage());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
} else if ("java.math.BigDecimal".equalsIgnoreCase(type)) {
try {
if(value!=null && !"".equals(value.toString())) {
field.set(o, new BigDecimal(value.toString()));
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "数据格式错误，无法将【" + value + "】转换为java.math.BigDecimal类型。" + e.getMessage());
} catch (IllegalArgumentException e) {
throw new IllegalArgumentException(errMsg + e.getMessage());
} catch (IllegalAccessException e) {
throw new IllegalAccessException(errMsg + e.getMessage());
}
}
}
list.add(o);
}
return list;
}
/**
* @param value
* @param errMsg
* @param checkType
*/
private static void customColumnCheck(Object value, String errMsg, ColumnCheckTypeEnum checkType) {
// 非空校验
if (ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL.equals(checkType)) {
if (value == null) {
throw new InputMismatchException(errMsg + "此列为非空列，请检查excel数据是否为空！");
}
}
// 整数类型校验
else if (ColumnCheckTypeEnum.CHECK_TYPE_LONG.equals(checkType)) {
try {
Long.parseLong(value.toString());
} catch (NumberFormatException e) {
throw new NumberFormatException(
errMsg + "此列为整数列，单元格应设置为文本类型，请检查excel数据是否整数数字或单元格是否为文本类型！" + e.getMessage());
}
}
// 价格类型校验，取2位小数
else if (ColumnCheckTypeEnum.CHECK_TYPE_PRICE.equals(checkType)) {
try {
String price = value.toString();
new BigDecimal(price);
if (price.indexOf(".") > -1 && price.split("\\.")[1].length() > 2) {
throw new InputMismatchException(errMsg + "此列为金额数值列，小数点后不超过2位小数，请检查excel数据是否合格！");
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "此列为金额数值列，请检查excel数据是否合格！");
}
}
// 价格类型校验，取4位小数
else if (ColumnCheckTypeEnum.CHECK_TYPE_PRICE_1.equals(checkType)) {
try {
String price = value.toString();
new BigDecimal(price);
if (price.indexOf(".") > -1 && price.split("\\.")[1].length() > 4) {
throw new InputMismatchException(errMsg + "此列为金额数值列，小数点后不超过4位小数，请检查excel数据是否合格！");
}
} catch (NumberFormatException e) {
throw new NumberFormatException(errMsg + "此列为金额数值列，请检查excel数据是否合格！");
}
}
}
/***
* 读取单个sheet
* <p>
* 导入Excel数据使用私有方法
* </p>
*
* @param sheet
* 单个sheet
* @param evaluator
* 解析公式结果
* @param columnArray
* 字段列数组
*/
private static List<Map<String, Object>> readSheet(Sheet sheet, FormulaEvaluator evaluator, String... columnArray) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
// 从第二行开始读取数据
for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
Row row = sheet.getRow(rowIndex);
short firstCellNum = row.getFirstCellNum();
// short maxColIx = row.getLastCellNum();
Map<String, Object> rowMap = new HashMap<String, Object>();
// 读取列的时候，按照设置好的字段列的数量长度循环读取
for (short colIndex = firstCellNum; colIndex < columnArray.length; colIndex++) {
Cell cell = row.getCell(new Integer(colIndex) + 1); // 从第二列开始导入，
// 第一列序号列不做导入
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
rowMap.put(columnArray[colIndex], cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
// 这里的日期类型会被转换为数字类型，需要判别后区分处理
if (HSSFDateUtil.isCellDateFormatted(cell)) {
rowMap.put(columnArray[colIndex], cell.getDateCellValue());
} else {
rowMap.put(columnArray[colIndex], cellValue.getNumberValue());
}
break;
case Cell.CELL_TYPE_STRING:
rowMap.put(columnArray[colIndex], cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
list.add(rowMap);
}
return list;
}
private static int getIndexOfArrayItem(String arrItem, String[] array) {
int index = -1;
for (int i = 0; i < array.length; i++) {
if (array[i].equals(arrItem)) {
index = i;
break;
}
}
return index;
}
/***
* 导出Excel数据
*
* @param fileName
* 导出文件名
* @param sheetName
* sheet名称
* @param list
* 要导出的数据
* @param dataFormatMap
* 可以设置某个列的数据格式 ，key：列名；value：格式值
* @param request
* @param response
* @throws IOException
*/
public static void exportDatas(String fileName, String sheetName, List<Map<String, Object>> list,
Map<String, CellDataFormatEnum> dataFormatMap, HttpServletRequest request, HttpServletResponse response)
throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);
if (CollectionUtils.isEmpty(list)) {
// 输出空文件
outputFile(fileName, request, response, wb);
return;
}
int rowNum = 0;
// 设置单元格样式(标题样式)
HSSFCellStyle styleTitle = wb.createCellStyle();
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 对齐方式
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleTitle.setFont(font);
// 设置单元格样式(数据值样式)
HSSFCellStyle styleContent = wb.createCellStyle();
styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle styleContent1 = wb.createCellStyle();
styleContent1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 标题行
HSSFRow rowTitle = sheet.createRow(rowNum++);
int i = 0;
HSSFCell cellH0 = rowTitle.createCell(i++);
cellH0.setCellValue("序号");
cellH0.setCellStyle(styleTitle);
for (Map.Entry<String, Object> tmp : list.get(0).entrySet()) {
HSSFCell cellH = rowTitle.createCell(i++);
cellH.setCellValue(tmp.getKey());
cellH.setCellStyle(styleTitle);
}
int cellOrder = 1;
for (Map<String, Object> map : list) {
HSSFRow row = sheet.createRow(rowNum++);
int j = 0;
// 序号
HSSFCell cellC0 = row.createCell(j++);
cellC0.setCellValue(cellOrder++);
cellC0.setCellStyle(styleContent);
for (Map.Entry<String, Object> entry : map.entrySet()) {
HSSFCell cellC = row.createCell(j++);
sheet.setColumnWidth(cellC.getColumnIndex(), 256 * (11 + 10));
cellC.setCellValue(entry.getValue() + "");
// 动态设置单元格的格式 枚举值对应到HSSFDataFormat的值
CellDataFormatEnum df = null;
if (dataFormatMap != null) {
df = dataFormatMap.get(entry.getKey());
}
if (df != null) {
if(CellDataFormatEnum.DATA_FORMAT_DOUBLE.getShortEnumCode().equals(df.getShortEnumCode())) {
if(entry.getValue()==null || "".equals(entry.getValue())) {
cellC.setCellValue("");
} else {
if(isDouble(entry.getValue()+"")) {
styleContent1.setDataFormat(df.getShortEnumCode());
cellC.setCellValue(Double.valueOf(entry.getValue()+""));
} else {
cellC.setCellValue(entry.getValue()+"");
}
}
} else {
cellC.setCellValue(entry.getValue() + "");
}
cellC.setCellStyle(styleContent1);
} else {
cellC.setCellStyle(styleContent);
}
}
}
// 输出文件
outputFile(fileName, request, response, wb);
}
/***
* 导出Excel数据（多个sheet）
*
* @param fileName
* 导出文件名
* @param sheetNameMap
* key:sheetCode, value:sheet名称
* @param map
* 要导出的数据 Map<String, List<Map<String,
* Object>>>，key：结果集的编码（表名称），value:结果集（key：字段，value：字段的值）
* @param dataFormatMap
* 设置多个sheet中某个列的数据格式，Map<String, Map<String,
* CellDataFormatEnum>>，key:结果集的编码（表名称），value:(sheet中列的格式map，key：
* 列名；value：格式值)
* @param request
* @param response
* @throws IOException
*/
public static void exportDatas(String fileName, Map<String, String> sheetNameMap,
Map<String, List<Map<String, Object>>> map, Map<String, Map<String, CellDataFormatEnum>> dataFormatMap,
HttpServletRequest request, HttpServletResponse response) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
if (CollectionUtils.isEmpty(map)) {
// 输出空文件
outputFile(fileName, request, response, wb);
return;
}
for (Map.Entry<String, List<Map<String, Object>>> dataMap : map.entrySet()) {
String resultCode = dataMap.getKey(); // 结果集的编码（表名称）
HSSFSheet sheet = wb.createSheet(sheetNameMap.get(resultCode));
List<Map<String, Object>> list = dataMap.getValue(); // 数据list，字段已map的形式存储
if(CollectionUtils.isEmpty(list)) {
continue;
}
int rowNum = 0;
// 设置单元格样式(标题样式)
HSSFCellStyle styleTitle = wb.createCellStyle();
styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTitle.setWrapText(true);// 数据自动换行
// 对齐方式
styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
styleTitle.setFont(font);
// 设置单元格样式(数据值样式)
HSSFCellStyle styleContent = wb.createCellStyle();
styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleContent.setWrapText(true);// 数据自动换行
HSSFCellStyle styleContent1 = wb.createCellStyle();
styleContent1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent1.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 标题行
HSSFRow rowTitle = sheet.createRow(rowNum++);
int i = 0;
HSSFCell cellH0 = rowTitle.createCell(i++);
cellH0.setCellValue("序号");
cellH0.setCellStyle(styleTitle);
boolean isOnlyTitle = false;
for (Map.Entry<String, Object> tmp : list.get(0).entrySet()) {
HSSFCell cellH = rowTitle.createCell(i++);
// 不是空数据的情况
if (!"isOnlyTitle".equals(tmp.getKey())) {
cellH.setCellValue(tmp.getKey());
cellH.setCellStyle(styleTitle);
} else {
isOnlyTitle = true;
}
}
// 空数据时，只输出标题信息
if (isOnlyTitle) {
continue;
}
int cellOrder = 1;
for (Map<String, Object> entityMap : list) {
HSSFRow row = sheet.createRow(rowNum++);
int j = 0;
// 序号
HSSFCell cellC0 = row.createCell(j++);
cellC0.setCellValue(cellOrder++);
cellC0.setCellStyle(styleContent);
for (Map.Entry<String, Object> entry : entityMap.entrySet()) {
HSSFCell cellC = row.createCell(j++);
sheet.setColumnWidth(cellC.getColumnIndex(), 256 * (11 + 10));
// 动态设置单元格的格式 枚举值对应到HSSFDataFormat的值
CellDataFormatEnum df = null;
if (dataFormatMap != null) {
Map<String, CellDataFormatEnum> map2 = dataFormatMap.get(resultCode);
if(map2!=null) {
df = map2.get(entry.getKey());
}
}
if (df != null) {
if(CellDataFormatEnum.DATA_FORMAT_DOUBLE.getShortEnumCode().equals(df.getShortEnumCode())) {
if(entry.getValue()==null || "".equals(entry.getValue())) {
cellC.setCellValue("");
} else {
if(isDouble(entry.getValue()+"")) {
styleContent1.setDataFormat(df.getShortEnumCode());
cellC.setCellValue(Double.valueOf(entry.getValue()+""));
} else {
cellC.setCellValue(entry.getValue()+"");
}
}
} else {
cellC.setCellValue(entry.getValue() + "");
}
cellC.setCellStyle(styleContent1);
} else {
cellC.setCellStyle(styleContent);
cellC.setCellValue(entry.getValue() + "");
}
}
}
}
// 输出文件
outputFile(fileName, request, response, wb);
}
/**
* 输出文件
*
* @param fileName
* @param request
* @param response
* @param wb
* @throws UnsupportedEncodingException
* @throws IOException
*/
private static void outputFile(String fileName, HttpServletRequest request, HttpServletResponse response,
HSSFWorkbook wb) throws UnsupportedEncodingException, IOException {
String agent = request.getHeader("User-Agent");
// 火狐浏览器导出文件不会url解码
if (agent != null && !"".equals(agent) && agent.toLowerCase().indexOf("firefox") > -1) {
response.setHeader("Content-disposition",
"attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xls\"");
} else {
response.setHeader("Content-disposition",
"attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
/**
*
* 解析excel Map<sheet名称,List<Map<单元格对应的标题,单元格类容>>>
*
* @param filePath
* @param listSheetNames
* @return
* @throws IOException
*/
public static Map<String, List<Map<String, String>>> getExcelData(String filePath, List<String> listSheetNames)
throws IOException {
Workbook wookbook = null;
Map<String, String> mapExcel;
Map<Integer, String> mapColumnName = new HashMap<Integer, String>();
Map<String, List<Map<String, String>>> mapListDatabaseNameAndData = new HashMap<String, List<Map<String, String>>>();
try {
try {
wookbook = new HSSFWorkbook(new FileInputStream(filePath));
} catch (Exception e) {
wookbook = new XSSFWorkbook(new FileInputStream(filePath));
}
// 目前就一个sheet 循环读取是为了以后扩展用
for (int i = 0; i < wookbook.getNumberOfSheets(); i++) {
listSheetNames.add(wookbook.getSheetName(i));
}
Sheet sheet;
for (int k = 0; k < listSheetNames.size(); k++) {
sheet = wookbook.getSheet(listSheetNames.get(k));
List<Map<String, String>> listExcelData = new ArrayList<Map<String, String>>();
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 遍历行
for (int i = 0; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
boolean boolNullLine = true; // 验证是否整行是空行，有时候，会出现空行，单是无数据，可以拷贝出来得到全是竖线！
// 获取到Excel文件中的所有的列
int cells = row.getLastCellNum();
mapExcel = new HashMap<String, String>();
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
Cell cell = row.getCell(j);
if (cell != null) {
if (i == 0) {
mapColumnName.put(j, cell.getStringCellValue().toUpperCase());
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
boolNullLine = false;
DecimalFormat df = new DecimalFormat("#.########");
String strCell = df.format(cell.getNumericCellValue());
mapExcel.put(mapColumnName.get(j), strCell);
break;
case HSSFCell.CELL_TYPE_STRING:
boolNullLine = false;
mapExcel.put(mapColumnName.get(j), cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
case HSSFCell.CELL_TYPE_BLANK:
case HSSFCell.CELL_TYPE_BOOLEAN:
case HSSFCell.CELL_TYPE_ERROR:
default:
// 有些行是空行，
mapExcel.put(mapColumnName.get(j), null);
break;
}
} else {
mapExcel.put(mapColumnName.get(j), null);
}
}
// 如果这行不为空的，add
if (!boolNullLine) {
listExcelData.add(mapExcel);
}
}
}
mapListDatabaseNameAndData.put(listSheetNames.get(k), listExcelData);
}
} catch (FileNotFoundException e) {
} finally {
if (wookbook != null) {
// wookbook.close();
}
}
return mapListDatabaseNameAndData;
}
/**
* <pre>
* 导入excel数据列校验类型
* </pre>
*/
public enum ColumnCheckTypeEnum {
CHECK_TYPE_NOTNULL("1", "非空数据校验"),
CHECK_TYPE_PRICE("2", "价格类型校验,取2位小数"),
CHECK_TYPE_PRICE_1("3", "价格类型校验,取4位小数"),
CHECK_TYPE_LONG("4", "整数类型校验");
private String code; // 枚举代码
private String description; // 枚举标签
private ColumnCheckTypeEnum(String code, String description) {
this.code = code;
this.description = description;
}
public String getEnumCode() {
return code;
}
public String getEnumLabel() {
return description;
}
public static ColumnCheckTypeEnum getEnumType(String enumCode) {
ColumnCheckTypeEnum enumType = null;
EnumSet<ColumnCheckTypeEnum> enumSet = EnumSet.allOf(ColumnCheckTypeEnum.class);
for (ColumnCheckTypeEnum enumItem : enumSet) {
if(enumItem.getEnumCode().equals(enumCode)) {
enumType = enumItem;
break;
}
}
return enumType;
}
}
/**
* <pre>
* 导入excel数据列校验类型
* </pre>
*/
public enum CellDataFormatEnum {
DATA_FORMAT_GENERAL("" + HSSFDataFormat.getBuiltinFormat("General"), "常规"),
DATA_FORMAT_DOUBLE("" + HSSFDataFormat.getBuiltinFormat("0.00"), "2位小数浮点型数值"),
DATA_FORMAT_PRICE("" + HSSFDataFormat.getBuiltinFormat("0.0000"), "4位小数浮点型数值"),
DATA_FORMAT_INT("" + HSSFDataFormat.getBuiltinFormat("0"), "整数型数值"),
DATA_FORMAT_DATE("" + HSSFDataFormat.getBuiltinFormat("yyyy/MM/dd"), "date日期类型"),
DATA_FORMAT_TIMESTAMP("" + HSSFDataFormat.getBuiltinFormat("yyyy/MM/dd HH:mm:ss"), "timestamp日期类型")
;
private String code; // 枚举代码
private String description; // 枚举标签
private CellDataFormatEnum(String code, String description) {
this.code = code;
this.description = description;
}
public String getEnumCode() {
return code;
}
public String getEnumLabel() {
return description;
}
public Short getShortEnumCode() {
return Short.parseShort(code);
}
public static CellDataFormatEnum getEnumType(String enumCode) {
CellDataFormatEnum enumType = null;
EnumSet<CellDataFormatEnum> enumSet = EnumSet.allOf(CellDataFormatEnum.class);
for (CellDataFormatEnum enumItem : enumSet) {
if(enumItem.getEnumCode().equals(enumCode)) {
enumType = enumItem;
break;
}
}
return enumType;
}
}
private static boolean isDouble(String str) {
try {
new Double(str);
} catch (Exception e) {
return false;
}
return true;
}
}DBDataUtil.java：
package com.lw.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DBDataUtil {
private static String url = "jdbc:mysql://172.0.0.1:3306/dbName?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
private static String username = "root";
private static String password = "root";
private static String driverClassName = "com.mysql.jdbc.Driver";
/**
* 查询表的字段，封装成List
*
* @param tableName
* 表名
* @param schemaName
* 数据库名
* @return
* @throws SQLException
*/
public static List<ColumnData> getMySqlColumnDatas(String tableName, String schemaName) throws SQLException {
String SQLColumns = " select COLUMN_NAME,DATA_TYPE,column_comment,numeric_scale,numeric_precision,character_maximum_length,ordinal_position from information_schema.COLUMNS where table_name = '"
+ tableName + "'" + " and TABLE_SCHEMA='" + schemaName + "' " + " ORDER by ordinal_position";
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(SQLColumns);
List<ColumnData> columnList = new ArrayList<ColumnData>();
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String name = rs.getString(1);
String lowName = getColumnBeanName(name); // 将XXX_XXX转换成xxxXxx的格式
String type = rs.getString(2).toUpperCase(); // mysql 区分大小写
String javaType = getType(rs.getString(2), rs.getString(4), rs.getString(5)); // mysql
// 区分大小写
String comment = rs.getString(3);
String dataScale = rs.getString(4);
String dataPrecision = rs.getString(5);
String dataMaxLength = rs.getString(6);
ColumnData cd = new ColumnData(name, lowName, type, javaType, comment, dataScale, dataPrecision,
dataMaxLength);
columnList.add(cd);
}
rs.close();
ps.close();
con.close();
return columnList;
}
public static Connection getConnection() throws SQLException {
try {
Class.forName(driverClassName);
} catch (Exception e) {
e.printStackTrace();
}
return DriverManager.getConnection(url, username, password);
}
private static String getColumnBeanName(String column) {
String[] split = column.split("_");
StringBuffer columnVal = new StringBuffer();
if (split.length > 1) {
for (int i = 0; i < split.length; i++) {
String colVal = "";
if (i == 0) {
colVal = split[i].toLowerCase();
columnVal.append(colVal);
} else {
colVal = split[i].substring(0, 1).toUpperCase()
+ split[i].substring(1, split[i].length()).toLowerCase();
columnVal.append(colVal);
}
}
columnVal.toString();
} else {
String colVal = column.toLowerCase();
columnVal.append(colVal);
}
return columnVal.toString();
}
/***
* 获取java类型
*
* @param type
* 数据库数据类型
* @param dataScale
* 小数位数
* @param dataPrecision
* 数据精度
* @return
*/
private static String getType(String type, String dataScale, String dataPrecision) {
type = type.toLowerCase();
if ("char".equalsIgnoreCase(type) || "varchar".equalsIgnoreCase(type) || "varchar2".equalsIgnoreCase(type)) {
return "java.lang.String";
} else if ("NUMBER".equalsIgnoreCase(type) || "numeric".equalsIgnoreCase(type)) {//
if ((dataScale != null && !dataScale.equals("") && !dataScale.equals("0"))) {
if (dataPrecision != null && dataPrecision.equals("38")) {
return "java.math.BigDecimal";
} else {
return "java.lang.Double";
}
} else {
if (dataPrecision != null && dataPrecision.equals("38")) {
return "java.math.BigDecimal";
} else {
return "java.lang.Long";
}
}
} else if ("decimal".equalsIgnoreCase(type)) {
return "java.math.BigDecimal";
} else if ("DATE".equalsIgnoreCase(type)) {
return "java.util.Date";
} else if ("DATETIME".equalsIgnoreCase(type)) {
return "java.util.Date";
} else if ("BLOB".equalsIgnoreCase(type)) {
return "java.sql.Blob";
} else if ("CLOB".equalsIgnoreCase(type)) {
return "java.sql.Clob";
} else if ("int".equalsIgnoreCase(type)) {
return "java.lang.Integer";
} else if ("TINYINT".equalsIgnoreCase(type)) {
return "java.lang.Boolean";
} else if ("double".equalsIgnoreCase(type)) {
return "java.math.BigDecimal";
} else if ("datetime".equalsIgnoreCase(type)) {
return "java.util.Date";
}
return null;
}
}ColumnData.java
package com.lw.util;
/**
* 表字段类
* @author david
*/
public class ColumnData {
private String columnName;
private String dataType;
private String comments;
private String columnLowName;
private String dataScale;
private String dataPrecision;
private String javaType; // java数据类型
private String dataMaxLength; // 最大长度
public ColumnData() {}
public ColumnData(String columnName,
String columnLowName,
String dataType,
String javaType,
String comments,
String dataScale,
String dataPrecision,
String dataMaxLength) {
this.columnName = columnName;
this.columnLowName = columnLowName;
this.dataType = dataType;
this.javaType = javaType;
this.comments = comments;
this.dataScale = dataScale;
this.dataPrecision = dataPrecision;
this.dataMaxLength = dataMaxLength;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getDataType() {
return dataType;
}
public void setDataType(String dataType) {
this.dataType = dataType;
}
public String getComments() {
return comments;
}
public void setComments(String comments) {
this.comments = comments;
}
public String getColumnLowName() {
return columnLowName;
}
public void setColumnLowName(String columnLowName) {
this.columnLowName = columnLowName;
}
public String getDataScale() {
return dataScale;
}
public void setDataScale(String dataScale) {
this.dataScale = dataScale;
}
public String getDataPrecision() {
return dataPrecision;
}
public void setDataPrecision(String dataPrecision) {
this.dataPrecision = dataPrecision;
}
public String getJavaType() {
return javaType;
}
public void setJavaType(String javaType) {
this.javaType = javaType;
}
public String getDataMaxLength() {
return dataMaxLength;
}
public void setDataMaxLength(String dataMaxLength) {
this.dataMaxLength = dataMaxLength;
}
}
类似资料：