正文
java中excel导入\导出工具类
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1.导入工具 package com.linrain.jcs.test; import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet; import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*; /**
* @Description: 导入excel工具类
* @CreateDate: 2019/1/24/0024$ 19:39$
* @Version: 1.0
*/
public class ImportExcelUtil { /**
* @param in ：承载着Excel的输入流
* @param ：要导入的工作表序号
* @param entityClass ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
* @param fieldMap ：Excel中的中文列头和类的英文属性的对应关系Map
* @param uniqueFields ：指定业务主键组合（即复合主键），这些列的组合不能重复
* @return ：List
* @throws ExcelException
* @MethodName : excelToList
* @Description : 将Excel转化为List
*/
public static <T> List<T> excelToList(InputStream in, String sheetName, Class<T> entityClass,
LinkedHashMap<String, String> fieldMap, String[] uniqueFields) throws ExcelException { //定义要返回的list
List<T> resultList = new ArrayList<T>(); try { //根据Excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(in);
//获取工作表
Sheet sheet = wb.getSheet(sheetName); //获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) { int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null || "".equals(currentCell.getContents().toString())) {
nullCols++;
}
} if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
} //如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
} Cell[] firstRow = sheet.getRow(0); String[] excelFieldNames = new String[firstRow.length]; //获取Excel中的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
} //判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
} //如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
} //将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
} //判断是否有重复行
//1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
} //2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
for (int j = 0; j < uniqueFields.length; j++) {
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet.findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1].getRow(),
true);
if (sameCell != null) {
nullCols++;
}
} if (nullCols == uniqueFields.length) {
throw new ExcelException("Excel中有重复行，请检查");
}
} //将sheet转换为list
for (int i = 1; i < realRows; i++) {
//新建要转换的对象
T entity = entityClass.newInstance(); //给对象中的字段赋值
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
//获取中文字段名
String cnNormalName = entry.getKey();
//获取英文字段名
String enNormalName = entry.getValue();
//根据中文字段名获取列号
int col = colMap.get(cnNormalName); //获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents().toString().trim(); //给对象赋值
setFieldValueByName(enNormalName, content, entity);
} resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
//如果是ExcelException，则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e; //否则将其它异常包装成ExcelException再抛出
} else {
e.printStackTrace();
throw new ExcelException("导入Excel失败");
}
}
return resultList;
} /**
* 根据字段名给对象的字段赋值
*
* @param fieldName 字段名
* @param fieldValue 字段值
* @param o 对象
* @throws Exception 异常
*/
public static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception { Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
// 获取字段类型
Class<?> fieldType = field.getType(); // 根据字段类型给字段赋值
if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
if (!fieldValue.toString().isEmpty()) {
if (fieldValue.toString().length() > 10) { field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
} else {
field.set(o, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue.toString()));
}
}
} else {
field.set(o, fieldValue);
}
} else {
throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
} /**
* @param sourceSheet
* @param eSheet 错误列表
* @param errorMap 错误原因
* @throws Exception
* @MethodName : addErrorRow
* @Description : 添加一行错误列表
*/
private void addErrorRow(Sheet sourceSheet, WritableSheet eSheet, LinkedHashMap<Integer, String> errorMap) throws Exception { // 复制错误的数据到错误列表
for (Map.Entry<Integer, String> entry : errorMap.entrySet()) {
int errorNo = entry.getKey();
String reason = entry.getValue();
int rows = eSheet.getRows();
for (int i = 0; i < sourceSheet.getColumns(); i++) {
System.out.println("错误列表当前列号" + i);
eSheet.addCell(new Label(i, rows, sourceSheet.getCell(i, errorNo).getContents()));
} // 添加错误原因和所在行号
eSheet.addCell(new Label(sourceSheet.getColumns(), rows, reason));
eSheet.addCell(new Label(sourceSheet.getColumns() + 1, rows, String.valueOf(errorNo + 1))); } } /**
* 设置工作表自动列宽和首行加粗
*
* @param ws 要设置格式的工作表
* @param extraWith 额外的宽度
*/
public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
// 获取本列的最宽单元格的宽度
for (int i = 0; i < ws.getColumns(); i++) {
int colWith = 0;
for (int j = 0; j < ws.getRows(); j++) {
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content.length();
if (colWith < cellWith) {
colWith = cellWith;
}
}
// 设置单元格的宽度为最宽宽度+额外宽度
ws.setColumnView(i, colWith + extraWith);
} } /**
* 根据字段名获取字段
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields(); // 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
} // 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
} // 如果本类和父类都没有，则返回空
return null;
} /**
* 根据实体拿到该实体的所有属性
*
* @param clazz 实体
* @return 返回属性的list集合
*/
public static List<String> getSuperClassFieldByClass(Class<?> clazz) { List<String> list = new ArrayList<String>(); // 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass(); Field[] superFields = superClazz.getDeclaredFields();
for (Field field : superFields) {
list.add(field.getName());
} // 如果父类没有，则返回空
return list;
} /**
* @param clazz ：对象对应的类
* @param equalFields ：复合业务主键对应的map
* @return 查询到的对象
* @MethodName : getObjByFields
* @Description :根据复合业务主键查询对象
*/
private <T> T getObjByFields(Class<T> clazz, LinkedHashMap<Object, Object> equalFields) { List<T> list = null;
if (equalFields.size() != 0) {
// list=commonBean.findResultListByEqual(clazz, equalFields);
} return list == null || list.size() == 0 ? null : list.get(0);
} /**
* @param normalFieldMap 普通字段Map
* @param referFieldMap 引用字段Map
* @return 组合后的Map
* @MethodName : combineFields
* @Description : 组合普通和引用中英文字段Map
*/
private LinkedHashMap<String, String> combineFields(LinkedHashMap<String, String> normalFieldMap, LinkedHashMap<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> referFieldMap) { LinkedHashMap<String, String> combineMap = new LinkedHashMap<String, String>(); // 如果存在普通字段，则添加之
if (normalFieldMap != null && normalFieldMap.size() != 0) {
combineMap.putAll(normalFieldMap);
} // 如果存在引用字段，则添加之
if (referFieldMap != null && referFieldMap.size() != 0) { // 组建引用中英文字段Map
LinkedHashMap<String, String> simpleReferFieldMap = new LinkedHashMap<String, String>();
for (Map.Entry<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> entry : referFieldMap.entrySet()) {
LinkedHashMap<String, Class<?>> keyMap = entry.getKey();
LinkedHashMap<String, String> valueMap = entry.getValue(); // 获取引用中文字段名
String referField = "";
for (Map.Entry<String, Class<?>> keyEntry : keyMap.entrySet()) {
referField = keyEntry.getKey();
break;
} for (Map.Entry<String, String> valueEntry : valueMap.entrySet()) {
String enField = valueEntry.getValue();
String cnField = valueEntry.getKey();
// 拼接英文引用字段
String fullEnField = referField + "." + enField; // 放入simpleReferFieldMap
simpleReferFieldMap.put(cnField, fullEnField);
} } // 放入combineMap
combineMap.putAll(simpleReferFieldMap);
} return combineMap; } }
2.导出工具 package com.linrain.jcs.test; import org.apache.commons.lang.StringUtils;
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 javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map; /**
* @Description: 导出excel工具类
* @CreateDate: 2019/1/24/ 17:18$
* @Version: 1.0
*/
public class ExportExcelUtil {
/**
* 常用普通文件下载
* @param response
* @param fileName
* @param sheetName
* @param data
*/
public static void down(HttpServletResponse response, String fileName, String sheetName, List<Map<String, Object>> data) {
// 生成提示信息，
response.setContentType("application/vnd.ms-excel");
if (StringUtils.isBlank(fileName)) {
fileName = Long.toString(System.currentTimeMillis());
}
OutputStream fOut = null;
try {
// 进行转码，使其支持中文文件名
fOut = response.getOutputStream();
String codedFileName = new String(fileName.getBytes("gbk"), "iso-8859-1");
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
HSSFWorkbook workbook = new HSSFWorkbook();
// 产生工作表对象
HSSFSheet sheet = workbook.createSheet(sheetName); for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow((int) i);
Map<String, Object> map = data.get(i); //遍历map中的值
int j = 0;
for (Object value : map.values()) {
HSSFCell cell = row.createCell((int) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (value == null) {
value = "";
}
cell.setCellValue(value.toString());
j++;
}
}
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fOut.flush();
fOut.close();
} catch (IOException e) {
}
}
}
}
package com.linrain.jcs.test; import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet; import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*; /**
* @Description: 导入excel工具类
* @CreateDate: 2019/1/24/0024$ 19:39$
* @Version: 1.0
*/
public class ImportExcelUtil { /**
* @param in ：承载着Excel的输入流
* @param ：要导入的工作表序号
* @param entityClass ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
* @param fieldMap ：Excel中的中文列头和类的英文属性的对应关系Map
* @param uniqueFields ：指定业务主键组合（即复合主键），这些列的组合不能重复
* @return ：List
* @throws ExcelException
* @MethodName : excelToList
* @Description : 将Excel转化为List
*/
public static <T> List<T> excelToList(InputStream in, String sheetName, Class<T> entityClass,
LinkedHashMap<String, String> fieldMap, String[] uniqueFields) throws ExcelException { //定义要返回的list
List<T> resultList = new ArrayList<T>(); try { //根据Excel数据源创建WorkBook
Workbook wb = Workbook.getWorkbook(in);
//获取工作表
Sheet sheet = wb.getSheet(sheetName); //获取工作表的有效行数
int realRows = 0;
for (int i = 0; i < sheet.getRows(); i++) { int nullCols = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell currentCell = sheet.getCell(j, i);
if (currentCell == null || "".equals(currentCell.getContents().toString())) {
nullCols++;
}
} if (nullCols == sheet.getColumns()) {
break;
} else {
realRows++;
}
} //如果Excel中没有数据则提示错误
if (realRows <= 1) {
throw new ExcelException("Excel文件中没有任何数据");
} Cell[] firstRow = sheet.getRow(0); String[] excelFieldNames = new String[firstRow.length]; //获取Excel中的列名
for (int i = 0; i < firstRow.length; i++) {
excelFieldNames[i] = firstRow[i].getContents().toString().trim();
} //判断需要的字段在Excel中是否都存在
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
} //如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
} //将列名和列号放入Map中,这样通过列名就可以拿到列号
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
for (int i = 0; i < excelFieldNames.length; i++) {
colMap.put(excelFieldNames[i], firstRow[i].getColumn());
} //判断是否有重复行
//1.获取uniqueFields指定的列
Cell[][] uniqueCells = new Cell[uniqueFields.length][];
for (int i = 0; i < uniqueFields.length; i++) {
int col = colMap.get(uniqueFields[i]);
uniqueCells[i] = sheet.getColumn(col);
} //2.从指定列中寻找重复行
for (int i = 1; i < realRows; i++) {
int nullCols = 0;
for (int j = 0; j < uniqueFields.length; j++) {
String currentContent = uniqueCells[j][i].getContents();
Cell sameCell = sheet.findCell(currentContent,
uniqueCells[j][i].getColumn(),
uniqueCells[j][i].getRow() + 1,
uniqueCells[j][i].getColumn(),
uniqueCells[j][realRows - 1].getRow(),
true);
if (sameCell != null) {
nullCols++;
}
} if (nullCols == uniqueFields.length) {
throw new ExcelException("Excel中有重复行，请检查");
}
} //将sheet转换为list
for (int i = 1; i < realRows; i++) {
//新建要转换的对象
T entity = entityClass.newInstance(); //给对象中的字段赋值
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
//获取中文字段名
String cnNormalName = entry.getKey();
//获取英文字段名
String enNormalName = entry.getValue();
//根据中文字段名获取列号
int col = colMap.get(cnNormalName); //获取当前单元格中的内容
String content = sheet.getCell(col, i).getContents().toString().trim(); //给对象赋值
setFieldValueByName(enNormalName, content, entity);
} resultList.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
//如果是ExcelException，则直接抛出
if (e instanceof ExcelException) {
throw (ExcelException) e; //否则将其它异常包装成ExcelException再抛出
} else {
e.printStackTrace();
throw new ExcelException("导入Excel失败");
}
}
return resultList;
} /**
* 根据字段名给对象的字段赋值
*
* @param fieldName 字段名
* @param fieldValue 字段值
* @param o 对象
* @throws Exception 异常
*/
public static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception { Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
// 获取字段类型
Class<?> fieldType = field.getType(); // 根据字段类型给字段赋值
if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
field.set(o, Integer.parseInt(fieldValue.toString()));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
field.set(o, Long.valueOf(fieldValue.toString()));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
if (!fieldValue.toString().isEmpty()) {
if (fieldValue.toString().length() > 10) { field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
} else {
field.set(o, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue.toString()));
}
}
} else {
field.set(o, fieldValue);
}
} else {
throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
} /**
* @param sourceSheet
* @param eSheet 错误列表
* @param errorMap 错误原因
* @throws Exception
* @MethodName : addErrorRow
* @Description : 添加一行错误列表
*/
private void addErrorRow(Sheet sourceSheet, WritableSheet eSheet, LinkedHashMap<Integer, String> errorMap) throws Exception { // 复制错误的数据到错误列表
for (Map.Entry<Integer, String> entry : errorMap.entrySet()) {
int errorNo = entry.getKey();
String reason = entry.getValue();
int rows = eSheet.getRows();
for (int i = 0; i < sourceSheet.getColumns(); i++) {
System.out.println("错误列表当前列号" + i);
eSheet.addCell(new Label(i, rows, sourceSheet.getCell(i, errorNo).getContents()));
} // 添加错误原因和所在行号
eSheet.addCell(new Label(sourceSheet.getColumns(), rows, reason));
eSheet.addCell(new Label(sourceSheet.getColumns() + 1, rows, String.valueOf(errorNo + 1))); } } /**
* 设置工作表自动列宽和首行加粗
*
* @param ws 要设置格式的工作表
* @param extraWith 额外的宽度
*/
public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
// 获取本列的最宽单元格的宽度
for (int i = 0; i < ws.getColumns(); i++) {
int colWith = 0;
for (int j = 0; j < ws.getRows(); j++) {
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content.length();
if (colWith < cellWith) {
colWith = cellWith;
}
}
// 设置单元格的宽度为最宽宽度+额外宽度
ws.setColumnView(i, colWith + extraWith);
} } /**
* 根据字段名获取字段
*
* @param fieldName 字段名
* @param clazz 包含该字段的类
* @return 字段
*/
public static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields(); // 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
} // 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
} // 如果本类和父类都没有，则返回空
return null;
} /**
* 根据实体拿到该实体的所有属性
*
* @param clazz 实体
* @return 返回属性的list集合
*/
public static List<String> getSuperClassFieldByClass(Class<?> clazz) { List<String> list = new ArrayList<String>(); // 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass(); Field[] superFields = superClazz.getDeclaredFields();
for (Field field : superFields) {
list.add(field.getName());
} // 如果父类没有，则返回空
return list;
} /**
* @param clazz ：对象对应的类
* @param equalFields ：复合业务主键对应的map
* @return 查询到的对象
* @MethodName : getObjByFields
* @Description :根据复合业务主键查询对象
*/
private <T> T getObjByFields(Class<T> clazz, LinkedHashMap<Object, Object> equalFields) { List<T> list = null;
if (equalFields.size() != 0) {
// list=commonBean.findResultListByEqual(clazz, equalFields);
} return list == null || list.size() == 0 ? null : list.get(0);
} /**
* @param normalFieldMap 普通字段Map
* @param referFieldMap 引用字段Map
* @return 组合后的Map
* @MethodName : combineFields
* @Description : 组合普通和引用中英文字段Map
*/
private LinkedHashMap<String, String> combineFields(LinkedHashMap<String, String> normalFieldMap, LinkedHashMap<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> referFieldMap) { LinkedHashMap<String, String> combineMap = new LinkedHashMap<String, String>(); // 如果存在普通字段，则添加之
if (normalFieldMap != null && normalFieldMap.size() != 0) {
combineMap.putAll(normalFieldMap);
} // 如果存在引用字段，则添加之
if (referFieldMap != null && referFieldMap.size() != 0) { // 组建引用中英文字段Map
LinkedHashMap<String, String> simpleReferFieldMap = new LinkedHashMap<String, String>();
for (Map.Entry<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> entry : referFieldMap.entrySet()) {
LinkedHashMap<String, Class<?>> keyMap = entry.getKey();
LinkedHashMap<String, String> valueMap = entry.getValue(); // 获取引用中文字段名
String referField = "";
for (Map.Entry<String, Class<?>> keyEntry : keyMap.entrySet()) {
referField = keyEntry.getKey();
break;
} for (Map.Entry<String, String> valueEntry : valueMap.entrySet()) {
String enField = valueEntry.getValue();
String cnField = valueEntry.getKey();
// 拼接英文引用字段
String fullEnField = referField + "." + enField; // 放入simpleReferFieldMap
simpleReferFieldMap.put(cnField, fullEnField);
} } // 放入combineMap
combineMap.putAll(simpleReferFieldMap);
} return combineMap; } }
package com.linrain.jcs.test; import org.apache.commons.lang.StringUtils;
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 javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map; /**
* @Description: 导出excel工具类
* @CreateDate: 2019/1/24/ 17:18$
* @Version: 1.0
*/
public class ExportExcelUtil {
/**
* 常用普通文件下载
* @param response
* @param fileName
* @param sheetName
* @param data
*/
public static void down(HttpServletResponse response, String fileName, String sheetName, List<Map<String, Object>> data) {
// 生成提示信息，
response.setContentType("application/vnd.ms-excel");
if (StringUtils.isBlank(fileName)) {
fileName = Long.toString(System.currentTimeMillis());
}
OutputStream fOut = null;
try {
// 进行转码，使其支持中文文件名
fOut = response.getOutputStream();
String codedFileName = new String(fileName.getBytes("gbk"), "iso-8859-1");
response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
HSSFWorkbook workbook = new HSSFWorkbook();
// 产生工作表对象
HSSFSheet sheet = workbook.createSheet(sheetName); for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow((int) i);
Map<String, Object> map = data.get(i); //遍历map中的值
int j = 0;
for (Object value : map.values()) {
HSSFCell cell = row.createCell((int) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (value == null) {
value = "";
}
cell.setCellValue(value.toString());
j++;
}
}
workbook.write(fOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fOut.flush();
fOut.close();
} catch (IOException e) {
}
}
}
}
