1.在pom.xml文件中导入依赖
<dependencies>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>28.1-jre</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8-beta4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8-beta4</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
</dependencies>
2.编写表格属性实体类
package com.util;
import java.util.List;
/**
* @Describution:表格属性类
* @Author: Yang Yong
* @Date: 2020-12-31 15:05
* @Version: 1.0
**/
public class ExcelSheetPO {
/**
* sheet的名称
*/
private String sheetName;
/**
* 表格标题
*/
private String title;
/**
* 头部标题集合
*/
private String[] headers;
/**
* 数据集合
*/
private List<List<Object>> dataList;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(String[] headers) {
this.headers = headers;
}
public List<List<Object>> getDataList() {
return dataList;
}
public void setDataList(List<List<Object>> dataList) {
this.dataList = dataList;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}
3.定义表格中行的属性实体类
package com.util;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;
import java.util.Objects;
/**
* @Describution: Excel表中的数据属性
* @Author: Yang Yong
* @Date: 2020-12-31 15:10
* @Version: 1.0
**/
public class ColumnDef {
public final static String COLUMN_NAME_INDEX0 = "@0index__";
public final static String COLUMN_NAME_INDEX1 = "@1index__";
/**
* 列标题
*/
private String columnHeader;
/**
* 属性名称
*/
private String propertyName;
/**
* 显示为富文本
*/
private boolean richText;
/**
* 数据格式化
*/
private String strFormat;
/**
* 当值为null时的显示内容
*/
private Object nullValue;
public ColumnDef(String columnHeader, String propertyName) {
this.propertyName = propertyName;
this.columnHeader = columnHeader;
}
public ColumnDef(String columnHeader, String propertyName, String dataFormat) {
this.propertyName = propertyName;
this.columnHeader = columnHeader;
this.strFormat = dataFormat;
}
/**
* 输出用于显示的数据
* 默认原样输出
*
* @param objValue
* @return
*/
public final Object toDisplayString(Object objValue) {
if (objValue == null) {
return getNullValue();
}
return toDisplayStringInner(objValue);
}
/**
* 具体实现输出用于显示的数据
*
* @param objValue
* @return
*/
protected Object toDisplayStringInner(Object objValue) {
if (StringUtils.isEmpty(strFormat)) {
return objValue;
}
if (objValue instanceof List) {
List list = (List) objValue;
Object[] results = new String[list.size()];
for (int i = 0; i < list.size(); i++) {
Object objItem = list.get(i);
results[i] = toDisplayStringInner(objItem);
}
return results;
}
if (objValue instanceof Date) {
return DateFormatUtils.format((Date) objValue, strFormat);
}
if (strFormat.startsWith("com.jd.common.enums.")) {
Class<?> enumClass;
try {
enumClass = Class.forName(strFormat);
} catch (ClassNotFoundException e) {
return getNullValue();
}
Method parseMethod;
try {
parseMethod = enumClass.getMethod("parse", objValue.getClass());
} catch (NoSuchMethodException e) {
return getNullValue();
}
Object codeNameEnum;
try {
codeNameEnum = parseMethod.invoke(null, objValue);
} catch (IllegalAccessException e) {
return getNullValue();
} catch (InvocationTargetException e) {
return getNullValue();
}
if (codeNameEnum == null) {
return getNullValue();
}
Method nameMethod;
try {
nameMethod = enumClass.getMethod("getName");
return nameMethod.invoke(codeNameEnum).toString();
} catch (NoSuchMethodException e) {
return codeNameEnum.toString();
} catch (InvocationTargetException e) {
return codeNameEnum.toString();
} catch (IllegalAccessException e) {
return codeNameEnum.toString();
}
}
return objValue;
}
public String getColumnHeader() {
return columnHeader;
}
public void setColumnHeader(String columnHeader) {
this.columnHeader = columnHeader;
}
public String getPropertyName() {
return propertyName;
}
public void setPropertyName(String propertyName) {
this.propertyName = propertyName;
}
public final Object getPropertyValue(Object dataItem, int rowIndex, int columnIndex) {
if (dataItem == null) {
return null;
}
if (Objects.equals(propertyName, COLUMN_NAME_INDEX0)) {
return rowIndex;
}
if (Objects.equals(propertyName, COLUMN_NAME_INDEX1)) {
return rowIndex + 1;
}
return getPropertyValue(dataItem, propertyName.split("\\."));
}
private Object getPropertyValue(Object dataItem, String[] propertyNames) {
Object result = dataItem;
for (String name : propertyNames) {
String methodName = "getByID" + upCaps(name.charAt(0)) + name.substring(1);
try {
result = result.getClass().getMethod(methodName).invoke(result);
} catch (IllegalAccessException e) {
return null;
} catch (InvocationTargetException e) {
return null;
} catch (NoSuchMethodException e) {
return null;
}
if (result == null) {
return null;
}
}
return result;
}
private char upCaps(char c) {
if (c >= 'a' && c <= 'z') {
return (char) (c - 32);
}
return c;
}
public boolean isRichText() {
return richText;
}
public void setRichText(boolean richText) {
this.richText = richText;
}
public Object getNullValue() {
return nullValue;
}
public void setNullValue(Object nullValue) {
this.nullValue = nullValue;
}
public String getStrFormat() {
return strFormat;
}
public void setStrFormat(String strFormat) {
this.strFormat = strFormat;
}
}
4.从本地上传Excel文件到Allication Code中
package com.util;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @Describution:
* @Author: Yang Yong
* @Date: 2020-12-31 14:59
* @Version: 1.0
**/
public class ImportExcel {
/**
* 标题样式
*/
private final static String STYLE_HEADER = "header";
/**
* 表头样式
*/
private final static String STYLE_TITLE = "title";
/**
* 数据样式
*/
private final static String STYLE_DATA = "data";
/**
* 存储样式
*/
private static final HashMap<String, CellStyle> cellStyleMap = new HashMap<>();
private static final String FORMAT_XSSF = "xlsx";
/**
* 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static List<ExcelSheetPO> readExcel(InputStream file)
throws FileNotFoundException, IOException {
Workbook wb = null;
wb = new HSSFWorkbook(file);
// 开始读取数据
List<ExcelSheetPO> sheetPOs = new ArrayList<>();
// 解析sheet
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<Object>> dataList = new ArrayList<>();
ExcelSheetPO sheetPO = new ExcelSheetPO();
sheetPO.setSheetName(sheet.getSheetName());
sheetPO.setDataList(dataList);
int readRowCount = 0;
readRowCount = sheet.getPhysicalNumberOfRows();
// 解析sheet 的行
int readColumnCount = 0;
for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
if (row.getFirstCellNum() < 0) {
continue;
}
if(j==0){
readColumnCount = (int) row.getLastCellNum();
}
List<Object> rowValue = new LinkedList<Object>();
// 解析sheet 的列
for (int k = 0; k < readColumnCount; k++) {
Cell cell = row.getCell(k);
rowValue.add(getCellValue(wb, cell));
}
dataList.add(rowValue);
}
sheetPOs.add(sheetPO);
}
return sheetPOs;
}
public static List<Map<String,Object>> getData(InputStream file) throws IOException {
List<ExcelSheetPO> excelSheetPOS = ExcelUtils.readExcel(file);
ArrayList<Map<String, Object>> list = Lists.newArrayList();
ArrayList<Object> arrayList = Lists.newArrayList();
for (ExcelSheetPO excelSheetPO : excelSheetPOS) {
List<List<Object>> dataList = excelSheetPO.getDataList();
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> map = Maps.newHashMap();
if (i == 0) {
List<Object> objects = dataList.get(0);
objects.forEach(o -> {
arrayList.add(o);
});
} else {
for (int j = 0; j < arrayList.size(); j++) {
map.put((String) arrayList.get(j), dataList.get(i).get(j));
}
list.add(map);
}
}
}
return list;
}
private static Object getCellValue(Workbook wb, Cell cell) {
Object columnValue = null;
if (cell != null) {
DecimalFormat df = new DecimalFormat("0");// 格式化 number
// String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd ");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
columnValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
columnValue = dataFormatter.formatCellValue(cell);
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
columnValue = dataFormatter.formatCellValue(cell);
} else if (cell.getCellStyle().getDataFormat()==14){
columnValue = sf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}else {
columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BOOLEAN:
columnValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
columnValue = "";
break;
case Cell.CELL_TYPE_FORMULA:
// 格式单元格
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
columnValue = cellValue.getNumberValue();
break;
default:
columnValue = cell.toString();
}
}
return columnValue;
}
private static CellStyle getStyle(String type, Workbook wb) {
if (cellStyleMap.containsKey(type)) {
return cellStyleMap.get(type);
}
// 生成一个样式
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);
if (STYLE_HEADER == type) {
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 16);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
} else if (STYLE_TITLE == type) {
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 18);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
} else if (STYLE_DATA == type) {
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 12);
style.setFont(font);
}
cellStyleMap.put(type, style);
return style;
}
private static <T> void generateExcelSheet(Workbook workbook,List<Map> data, List<String> columns, String sheetTitle){
Sheet sheet = workbook.createSheet(sheetTitle);
{
//产生表格标题行
Row headerRow = sheet.createRow(0);
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Cell cell = headerRow.createCell(columnIndex);
HSSFRichTextString text = new HSSFRichTextString(columns.get(columnIndex));
cell.setCellValue(text);
}
}
//遍历产生数据行
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
Map dataItem = data.get(rowIndex);
Row row = sheet.createRow(1 + rowIndex);
//遍历产生单元格
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Cell cell = row.createCell(columnIndex);
String columnDef = columns.get(columnIndex);
//Object objValue = columnDef.toDisplayString(columnDef.getPropertyValue(dataItem, rowIndex, columnIndex));
Object objValue =dataItem.get(columnDef);
if (objValue == null) {
continue;
}
if (objValue instanceof Byte) {
cell.setCellValue(((Byte) objValue).doubleValue());
} else if (objValue instanceof Short) {
cell.setCellValue(((Short) objValue).doubleValue());
} else if (objValue instanceof Integer) {
cell.setCellValue(((Integer) objValue).doubleValue());
} else if (objValue instanceof Long) {
cell.setCellValue(((Long) objValue).doubleValue());
} else if (objValue instanceof Float) {
cell.setCellValue(((Float) objValue).doubleValue());
} else if (objValue instanceof Double) {
cell.setCellValue((double) objValue);
} else if (objValue instanceof Boolean) {
cell.setCellValue((boolean) objValue);
} else if (objValue instanceof Date) {
if (StringUtils.isEmpty(columnDef)) {
cell.getCellStyle().setDataFormat((short) 0x16);
cell.setCellValue((Date) objValue);
} else {
short builtinFormat = HSSFDataFormat.getBuiltinFormat(columnDef);
if (builtinFormat >= 0) {
cell.getCellStyle().setDataFormat(builtinFormat);
cell.setCellValue((Date) objValue);
} else {
DateFormatUtils.format((Date) objValue, columnDef);
}
}
} else if (objValue instanceof Calendar) {
cell.setCellValue((Calendar) objValue);
} else if (objValue instanceof Character) {
cell.setCellValue(objValue.toString());
} else if (objValue instanceof String) {
cell.setCellValue(objValue.toString());
}
cell.setCellValue(objValue.toString());
}
}
}
/**
* 生成多Sheet的Excel文件
* @param dataMap 数据Map（key为sheet下标）
* @param columnsMap 列集合Map（key为sheet下标）
* @param fileFormat 文件格式
* @param sheetTitleList Sheet页标题集合（顺序）
*/
public static <T> void generateExcelFileBySheets(Map<Integer,List<T>> dataMap, Map<Integer,List<String>> columnsMap,
OutputStream stream, String fileFormat, List<String> sheetTitleList) throws IOException {
Workbook workbook = Objects.equals(fileFormat.toLowerCase(), FORMAT_XSSF) ? new XSSFWorkbook() : new HSSFWorkbook();
if(!CollectionUtils.isEmpty(sheetTitleList) && !CollectionUtils.isEmpty(dataMap) && !CollectionUtils.isEmpty(columnsMap)){
//遍历产生Sheet页
for(int i = 0; i < sheetTitleList.size(); i++) {
String sheetTitle = sheetTitleList.get(i);
List<String> columns = columnsMap.get(i);
List<Map> data = (List<Map>) dataMap.get(i);
generateExcelSheet(workbook, data, columns, sheetTitle);
}
}
workbook.write(stream);
}
}
5.将数据库中的数据导出到本地
package com.util;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
/**
* @Describution:将数据库中的数据以Excel文件的方式导出到本地
* @Author: Yang Yong
* @Date: 2020-12-31 14:59
* @Version: 1.0
**/
public class ExportExcel<T> {
private static final String FORMAT_XSSF = "xlsx";
/**
* 生成Excel文件
*
* @param <T>
* @param data
* @param columns
* @param title
* @throws IOException
*/
public static <T> void generateExcelFile(List<T> data, List<ColumnDef> columns, OutputStream stream, String format, String title) throws IOException {
Workbook workbook = generateExcelFile(data, columns, format, title);
workbook.write(stream);
}
/**
* 生成Excel文件
*
* @param <T>
* @param data
* @param columns
* @return
*/
public static <T> Workbook generateExcelFile(List<T> data, List<ColumnDef> columns, String fileFormat, String sheetTitle) {
Workbook workbook = Objects.equals(fileFormat.toLowerCase(), FORMAT_XSSF) ? new XSSFWorkbook() : new HSSFWorkbook();
generateExcelSheet(workbook,data,columns,sheetTitle);
return workbook;
}
/**
* 生成Excel文件的Sheet页面 （拆分自generateExcelFile，便于复用）
* @param workbook Excel文件
* @param data 数据
* @param columns 列集合
* @param sheetTitle sheet页标题
*/
private static <T> void generateExcelSheet(Workbook workbook,List<T> data, List<ColumnDef> columns, String sheetTitle){
Sheet sheet = workbook.createSheet(sheetTitle);
{
//产生表格标题行
Row headerRow = sheet.createRow(0);
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Cell cell = headerRow.createCell(columnIndex);
HSSFRichTextString text = new HSSFRichTextString(columns.get(columnIndex).getColumnHeader());
cell.setCellValue(text);
}
}
//遍历产生数据行
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
T dataItem = data.get(rowIndex);
Row row = sheet.createRow(1 + rowIndex);
//遍历产生单元格
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Cell cell = row.createCell(columnIndex);
ColumnDef columnDef = columns.get(columnIndex);
Object objValue = columnDef.toDisplayString(columnDef.getPropertyValue(dataItem, rowIndex, columnIndex));
if (objValue == null) {
continue;
}
if (objValue instanceof Byte) {
cell.setCellValue(((Byte) objValue).doubleValue());
} else if (objValue instanceof Short) {
cell.setCellValue(((Short) objValue).doubleValue());
} else if (objValue instanceof Integer) {
cell.setCellValue(((Integer) objValue).doubleValue());
} else if (objValue instanceof Long) {
cell.setCellValue(((Long) objValue).doubleValue());
} else if (objValue instanceof Float) {
cell.setCellValue(((Float) objValue).doubleValue());
} else if (objValue instanceof Double) {
cell.setCellValue((double) objValue);
} else if (objValue instanceof Boolean) {
cell.setCellValue((boolean) objValue);
} else if (objValue instanceof Date) {
String dataFormat = columnDef.getStrFormat();
if (StringUtils.isEmpty(dataFormat)) {
cell.getCellStyle().setDataFormat((short) 0x16);
cell.setCellValue((Date) objValue);
} else {
short builtinFormat = HSSFDataFormat.getBuiltinFormat(dataFormat);
if (builtinFormat >= 0) {
cell.getCellStyle().setDataFormat(builtinFormat);
cell.setCellValue((Date) objValue);
} else {
DateFormatUtils.format((Date) objValue, dataFormat);
}
}
} else if (objValue instanceof Calendar) {
cell.setCellValue((Calendar) objValue);
} else if (objValue instanceof Character) {
cell.setCellValue(objValue.toString());
} else if (objValue instanceof String) {
if (columnDef.isRichText()) {
cell.setCellValue(new HSSFRichTextString((String) objValue));
} else {
cell.setCellValue((String) objValue);
}
} else {
if (columnDef.isRichText()) {
cell.setCellValue(new HSSFRichTextString(objValue.toString()));
} else {
cell.setCellValue(objValue.toString());
}
}
}
}
}
/**
* 生成多Sheet的Excel文件
* @param dataMap 数据Map（key为sheet下标）
* @param columnsMap 列集合Map（key为sheet下标）
* @param fileFormat 文件格式
* @param sheetTitleList Sheet页标题集合（顺序）
*/
public static <T> void generateExcelFileBySheets(Map<Integer,List<T>> dataMap, Map<Integer,List<ColumnDef>> columnsMap,
OutputStream stream, String fileFormat, List<String> sheetTitleList) throws IOException {
Workbook workbook = Objects.equals(fileFormat.toLowerCase(), FORMAT_XSSF) ? new XSSFWorkbook() : new HSSFWorkbook();
if(!CollectionUtils.isEmpty(sheetTitleList) && !CollectionUtils.isEmpty(dataMap) && !CollectionUtils.isEmpty(columnsMap)){
//遍历产生Sheet页
for(int i = 0; i < sheetTitleList.size(); i++) {
String sheetTitle = sheetTitleList.get(i);
List<ColumnDef> columns = columnsMap.get(i);
List<T> data = dataMap.get(i);
generateExcelSheet(workbook, data, columns, sheetTitle);
}
}
workbook.write(stream);
}
/**
* 将excel转换为Obj的过程中，将为空的数据转换成了‘null’字符串，将其再转换回null值
* @param originObj
*/
public static void convertNullValue(Object originObj) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
Field[] fields = originObj.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field indexField = fields[i];
indexField.setAccessible(true);
String fieldName = indexField.getName();
String fieldNameUpper = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
if ("class java.lang.String".equals(indexField.getType().toString())) {
String getter = "getByID" + fieldNameUpper;
Method getMethod = null;
getMethod = originObj.getClass().getMethod(getter);
Object value = getMethod.invoke(originObj);
if (value != null && value.equals("null")) {
indexField.set(originObj,null);
}
}
}
}
}
说明：由于每个项目的业务详情不一样，需要导入导出的字段需要在逻辑处理层自行定义，这里就不展开详细讲解了
1478
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
