导出数据到Excel并下载- 准备
1、导入依赖
<!--Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
2、Excell实体类
package cn.ucmed.privacy.agreement.dataobject.entity;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import java.util.List;
public class ExcelExportEntity<T> {
private Class<T> cls;
private List<T> list;
private String[] keys;
private String[] columnNames;
private String sheetName;
private String[] extraParam;
/**
* 重载toString方法
*
* @return
* @see Object#toString()
*/
@Override
public String toString() {
return ToStringBuilder.reflectionToString(this, ToStringStyle.SHORT_PREFIX_STYLE);
}
public Class<T> getCls() {
return cls;
}
public void setCls(Class<T> cls) {
this.cls = cls;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public String[] getKeys() {
return keys;
}
public void setKeys(String[] keys) {
this.keys = keys;
}
public String[] getColumnNames() {
return columnNames;
}
public void setColumnNames(String[] columnNames) {
this.columnNames = columnNames;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public String[] getExtraParam() {
return extraParam;
}
public void setExtraParam(String[] extraParam) {
this.extraParam = extraParam;
}
}
3、Excel工具类
package cn.ucmed.privacy.agreement.util;
import cn.ucmed.privacy.agreement.dataobject.entity.ExcelExportEntity;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
public class ExcelHelper {
public final static ExcelHelper INSTANCE = new ExcelHelper();
private static final org.apache.log4j.Logger logger = org.apache.log4j.Logger
.getLogger(ExcelHelper.class);
public byte[] listToExcelWithSheets(List<ExcelExportEntity> exportList) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
for (int n = 0; n < exportList.size(); n++) {
Sheet sheet = wb.createSheet(exportList.get(n).getSheetName());
// 创建两种单元格格式
CellStyle topStyle = wb.createCellStyle();
Font boldFont = wb.createFont();
boldFont.setBold(true);
topStyle.setFont(boldFont);
CellStyle dateStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
CellStyle stringStyle = wb.createCellStyle();
DataFormat stringFormat = wb.createDataFormat();
stringStyle.setDataFormat(stringFormat.getFormat("@"));
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < exportList.get(n).getKeys().length; i++) {
sheet.setColumnWidth(i, 3500);
try {
String methodName = "get" + exportList.get(n).getKeys()[i].substring(0, 1).toUpperCase() + exportList.get(n).getKeys()[i].substring(1);
@SuppressWarnings("unchecked")
Method m = exportList.get(n).getCls().getMethod(methodName);
switch (m.getReturnType().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
//...
break;
case "java.lang.boolean":
//...
break;
case "java.util.date":
sheet.setDefaultColumnStyle(i, dateStyle);
break;
case "java.lang.string":
default:
sheet.setDefaultColumnStyle(i, stringStyle);
break;
}
} catch (Exception e) {
logger.debug(e);
} finally {
try {
wb.close();
} catch (IOException e) {
logger.debug(e);
}
}
}
// 创建第一行
Row row = sheet.createRow(0);
//设置列名
for (int i = 0; i < exportList.get(n).getColumnNames().length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(exportList.get(n).getColumnNames()[i]);
cell.setCellStyle(topStyle);
}
//设置每行每列的值
for (int i = 0; i < exportList.get(n).getList().size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + 1);
// 在row行上创建一个方格
for (int j = 0; j < exportList.get(n).getKeys().length; j++) {
Cell cell = row1.createCell(j);
Object val = null;
Field[] fields = exportList.get(n).getList().get(i).getClass().getDeclaredFields();
for (Field f : fields) {
String name = f.getName();
if (name.equals(exportList.get(n).getKeys()[j])) {
try {
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method m = exportList.get(n).getList().get(i).getClass().getMethod(methodName);
val = m.invoke(exportList.get(n).getList().get(i));
} catch (Exception e) {
logger.debug(e);
}
}
}
if (val != null) {
switch (val.getClass().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
cell.setCellValue((int) val);
break;
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
cell.setCellValue((double) val);
break;
case "java.lang.boolean":
cell.setCellValue((Boolean) val);
break;
case "java.util.date":
cell.setCellValue((Date) val);
cell.setCellStyle(dateStyle);
break;
case "java.lang.string":
default:
cell.setCellValue(val.toString());
break;
}
}
}
}
}
try {
wb.write(os);
} catch (Exception e) {
logger.debug(e);
}
byte[] content = os.toByteArray();
return content;
}
public byte[] listToExcelWithSheets2(List<ExcelExportEntity> exportList) throws Exception {
ByteArrayOutputStream os = new ByteArrayOutputStream();
String[] extraParam = null;
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
for (int n = 0; n < exportList.size(); n++) {
extraParam = exportList.get(n).getExtraParam();
Sheet sheet = wb.createSheet(exportList.get(n).getSheetName());
// 创建两种单元格格式
CellStyle dateStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
CellStyle stringStyle = wb.createCellStyle();
DataFormat stringFormat = wb.createDataFormat();
stringStyle.setDataFormat(stringFormat.getFormat("@"));
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < exportList.get(n).getKeys().length; i++) {
switch (i) {
case 0:
sheet.setColumnWidth(i, 4000);
break;
case 1:
sheet.setColumnWidth(i, 2000);
break;
case 2:
sheet.setColumnWidth(i, 3000);
break;
case 3:
sheet.setColumnWidth(i, 4000);
break;
case 4:
sheet.setColumnWidth(i, 2500);
break;
case 5:
sheet.setColumnWidth(i, 2000);
break;
case 6:
sheet.setColumnWidth(i, 2000);
break;
case 7:
sheet.setColumnWidth(i, 8000);
break;
case 8:
sheet.setColumnWidth(i, 4000);
break;
case 9:
sheet.setColumnWidth(i, 3000);
break;
case 10:
sheet.setColumnWidth(i, 6000);
break;
case 11:
sheet.setColumnWidth(i, 8000);
break;
case 12:
sheet.setColumnWidth(i, 4000);
break;
default:
break;
}
try {
String methodName = "get" + exportList.get(n).getKeys()[i].substring(0, 1).toUpperCase() + exportList.get(n).getKeys()[i].substring(1);
@SuppressWarnings("unchecked")
Method m = exportList.get(n).getCls().getMethod(methodName);
switch (m.getReturnType().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
//...
break;
case "java.lang.boolean":
//...
break;
case "java.util.date":
sheet.setDefaultColumnStyle(i, dateStyle);
break;
case "java.lang.string":
default:
sheet.setDefaultColumnStyle(i, stringStyle);
break;
}
} catch (Exception e) {
logger.debug(e);
}
}
// 创建第一行 设置标题
Row firstRow = sheet.createRow(0);
Cell titleCell = firstRow.createCell(0);
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 12);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗字体
font.setFontHeightInPoints((short) 16);//字体大小
font.setCharSet(HSSFFont.DEFAULT_CHARSET);
//创建单元格样式
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
style.setFont(font);//将字体加入到样式中。
CellStyle style1 = wb.createCellStyle();
style1.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
CellStyle style2 = wb.createCellStyle();
style2.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
titleCell.setCellStyle(style);
titleCell.setCellValue(extraParam[0]);
sheet.addMergedRegion(cellRangeAddress);
//创建第二行
Row secondRow = sheet.createRow(1);
secondRow.setHeight((short) 800);
setValue(wb, secondRow.createCell(0), extraParam[1]);
setValue(wb, secondRow.createCell(2), extraParam[2]);
setValue(wb, secondRow.createCell(5), extraParam[3]);
setValue(wb, secondRow.createCell(8), extraParam[4]);
setValue(wb, secondRow.createCell(9), extraParam[5]);
//创建第三行 设置列名
Row thirdRow = sheet.createRow(2);
thirdRow.setHeight((short) 800);
Font font1 = wb.createFont();
font1.setFontHeightInPoints((short) 11);//字体大小
font1.setBold(true);
style1.setFont(font1);
for (int i = 0; i < exportList.get(n).getColumnNames().length; i++) {
Cell cell = thirdRow.createCell(i);
cell.setCellStyle(style1);
cell.setCellValue(exportList.get(n).getColumnNames()[i]);
}
//设置每行每列的值
for (int i = 0; i < exportList.get(n).getList().size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + 3);
row1.setHeight((short) 800);
Font font2 = wb.createFont();
font2.setFontHeightInPoints((short) 11);//字体大小
style2.setFont(font2);
style2.setWrapText(true);//自动换行
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 在row行上创建一个方格
for (int j = 0; j < exportList.get(n).getKeys().length; j++) {
Cell cell = row1.createCell(j);
cell.setCellStyle(style2);
Object val = null;
Field[] fields = exportList.get(n).getList().get(i).getClass().getDeclaredFields();
for (Field f : fields) {
String name = f.getName();
if (name.equals(exportList.get(n).getKeys()[j])) {
try {
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method m = exportList.get(n).getList().get(i).getClass().getMethod(methodName);
val = m.invoke(exportList.get(n).getList().get(i));
} catch (Exception e) {
logger.debug(e);
}
}
}
if (val != null) {
switch (val.getClass().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
cell.setCellValue((int) val);
break;
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
cell.setCellValue((double) val);
break;
case "java.lang.boolean":
cell.setCellValue((Boolean) val);
break;
case "java.util.date":
cell.setCellValue((Date) val);
cell.setCellStyle(dateStyle);
break;
case "java.lang.string":
default:
cell.setCellValue(val.toString());
break;
}
}
}
}
//设置最后两行
Row lastRow1 = sheet.createRow(exportList.get(n).getList().size() + 3);
Row lastRow2 = sheet.createRow(exportList.get(n).getList().size() + 4);
lastRow1.setHeight((short) 800);
lastRow2.setHeight((short) 800);
setValue(wb, lastRow1.createCell(0), extraParam[5]);
setValue(wb, lastRow1.createCell(4), extraParam[6]);
setValue(wb, lastRow1.createCell(9), extraParam[7]);
setValue(wb, lastRow2.createCell(0), extraParam[8]);
setValue(wb, lastRow2.createCell(4), extraParam[9]);
setValue(wb, lastRow2.createCell(9), extraParam[10]);
}
try {
wb.write(os);
} catch (Exception e) {
logger.debug(e);
}
byte[] content = os.toByteArray();
return content;
}
public <T> byte[] listToExcel(Class<T> cls, List<T> list, String[] keys, String[] columnNames) {
List<ExcelExportEntity> exportList = new ArrayList<>();
ExcelExportEntity eee = new ExcelExportEntity();
eee.setCls(cls);
eee.setList(list);
eee.setKeys(keys);
eee.setColumnNames(columnNames);
eee.setSheetName("Sheet1");
exportList.add(eee);
return listToExcelWithSheets(exportList);
}
public <T> byte[] listToExcel2(Class<T> cls, List<T> list, String[] keys, String[] columnNames, String[] extraParam) throws Exception {
List<ExcelExportEntity> exportList = new ArrayList<>();
ExcelExportEntity eee = new ExcelExportEntity();
eee.setCls(cls);
eee.setList(list);
eee.setKeys(keys);
eee.setColumnNames(columnNames);
eee.setSheetName("Sheet1");
eee.setExtraParam(extraParam);
exportList.add(eee);
return listToExcelWithSheets2(exportList);
}
public <T> byte[] listToExcelWithExtraRow(Class<T> cls, List<T> list, String[] keys, String[] columnNames, String[] extraParam) {
List<ExcelExportEntity> exportList = new ArrayList<>();
ExcelExportEntity eee = new ExcelExportEntity();
eee.setCls(cls);
eee.setList(list);
eee.setKeys(keys);
eee.setColumnNames(columnNames);
eee.setSheetName("Sheet1");
eee.setExtraParam(extraParam);
exportList.add(eee);
return listToExcelWithSheets3(exportList);
}
public <T> byte[] listToExcelWithExtraRowTotal(Class<T> cls, List<T> list, String[] keys, String[] columnNames, String[] extraParam) {
List<ExcelExportEntity> exportList = new ArrayList<>();
ExcelExportEntity eee = new ExcelExportEntity();
eee.setCls(cls);
eee.setList(list);
eee.setKeys(keys);
eee.setColumnNames(columnNames);
eee.setSheetName("Sheet1");
eee.setExtraParam(extraParam);
exportList.add(eee);
return listToExcelWithSheets4(exportList);
}
private byte[] listToExcelWithSheets3(List<ExcelExportEntity> exportList) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
String[] extraParam = null;
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
for (int n = 0; n < exportList.size(); n++) {
extraParam = exportList.get(n).getExtraParam();
Sheet sheet = wb.createSheet(exportList.get(n).getSheetName());
// 创建两种单元格格式
CellStyle topStyle = wb.createCellStyle();
Font boldFont = wb.createFont();
boldFont.setBold(true);
topStyle.setFont(boldFont);
topStyle.setAlignment(CellStyle.ALIGN_CENTER);
CellStyle dateStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
CellStyle stringStyle = wb.createCellStyle();
DataFormat stringFormat = wb.createDataFormat();
stringStyle.setDataFormat(stringFormat.getFormat("@"));
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < exportList.get(n).getKeys().length; i++) {
sheet.setColumnWidth(i, 3500);
try {
String methodName = "get" + exportList.get(n).getKeys()[i].substring(0, 1).toUpperCase() + exportList.get(n).getKeys()[i].substring(1);
@SuppressWarnings("unchecked")
Method m = exportList.get(n).getCls().getMethod(methodName);
switch (m.getReturnType().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
//...
break;
case "java.lang.boolean":
//...
break;
case "java.util.date":
sheet.setDefaultColumnStyle(i, dateStyle);
break;
case "java.lang.string":
default:
sheet.setDefaultColumnStyle(i, stringStyle);
break;
}
} catch (Exception e) {
logger.debug(e);
} finally {
try {
wb.close();
} catch (IOException e) {
logger.debug(e);
}
}
}
// 创建第一行
Row row = sheet.createRow(0);
//设置列名
for (int i = 0; i < exportList.get(n).getColumnNames().length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(exportList.get(n).getColumnNames()[i]);
cell.setCellStyle(topStyle);
}
//设置每行每列的值
for (int i = 0; i < exportList.get(n).getList().size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + 1);
// 在row行上创建一个方格
for (int j = 0; j < exportList.get(n).getKeys().length; j++) {
Cell cell = row1.createCell(j);
Object val = null;
Field[] fields = exportList.get(n).getList().get(i).getClass().getDeclaredFields();
for (Field f : fields) {
String name = f.getName();
if (name.equals(exportList.get(n).getKeys()[j])) {
try {
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method m = exportList.get(n).getList().get(i).getClass().getMethod(methodName);
val = m.invoke(exportList.get(n).getList().get(i));
} catch (Exception e) {
logger.debug(e);
}
}
}
if (val != null) {
switch (val.getClass().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
cell.setCellValue((int) val);
break;
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
cell.setCellValue((double) val);
break;
case "java.lang.boolean":
cell.setCellValue((Boolean) val);
break;
case "java.util.date":
cell.setCellValue((Date) val);
cell.setCellStyle(dateStyle);
break;
case "java.lang.string":
default:
cell.setCellValue(val.toString());
break;
}
}
}
}
Row lastRow1 = sheet.createRow(exportList.get(n).getList().size() + 1);
setValue3(wb, lastRow1.createCell(0), extraParam[0]);
setValue2(wb, lastRow1.createCell(1), extraParam[1]);
setValue2(wb, lastRow1.createCell(2), extraParam[2]);
setValue2(wb, lastRow1.createCell(3), extraParam[3]);
}
try {
wb.write(os);
} catch (Exception e) {
logger.debug(e);
}
byte[] content = os.toByteArray();
return content;
}
private byte[] listToExcelWithSheets4(List<ExcelExportEntity> exportList) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
String[] extraParam = null;
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
for (int n = 0; n < exportList.size(); n++) {
extraParam = exportList.get(n).getExtraParam();
Sheet sheet = wb.createSheet(exportList.get(n).getSheetName());
// 创建两种单元格格式
CellStyle topStyle = wb.createCellStyle();
Font boldFont = wb.createFont();
boldFont.setBold(true);
topStyle.setFont(boldFont);
topStyle.setAlignment(CellStyle.ALIGN_CENTER);
CellStyle dateStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
CellStyle stringStyle = wb.createCellStyle();
DataFormat stringFormat = wb.createDataFormat();
stringStyle.setDataFormat(stringFormat.getFormat("@"));
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for (int i = 0; i < exportList.get(n).getKeys().length; i++) {
sheet.setColumnWidth(i, Math.max(3500, exportList.get(n).getColumnNames()[i].length() * 512 + 1000));
try {
String methodName = "get" + exportList.get(n).getKeys()[i].substring(0, 1).toUpperCase() + exportList.get(n).getKeys()[i].substring(1);
@SuppressWarnings("unchecked")
Method m = exportList.get(n).getCls().getMethod(methodName);
switch (m.getReturnType().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
//...
break;
case "java.lang.boolean":
//...
break;
case "java.util.date":
sheet.setDefaultColumnStyle(i, dateStyle);
break;
case "java.lang.string":
default:
sheet.setDefaultColumnStyle(i, stringStyle);
break;
}
} catch (Exception e) {
logger.debug(e);
} finally {
try {
wb.close();
} catch (IOException e) {
logger.debug(e);
}
}
}
// 创建第一行
Row row = sheet.createRow(0);
//设置列名
for (int i = 0; i < exportList.get(n).getColumnNames().length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(exportList.get(n).getColumnNames()[i]);
cell.setCellStyle(topStyle);
}
//总计数
double[][] total = new double[2][exportList.get(n).getKeys().length];
if (exportList.get(n).getList().size() > 0) {
Field[] fs = exportList.get(n).getList().get(0).getClass().getDeclaredFields();
for (int k = 0; k < exportList.get(n).getKeys().length; k++) {
String s = exportList.get(n).getKeys()[k];
Field field = Arrays.stream(fs).filter((e) -> e.getName().equals(s)).findAny().orElse(null);
if (field != null) {
if ("int".equals(field.getType().getName())) {
total[0][k] = 1;
}
if ("double".equals(field.getType().getName())) {
total[0][k] = 2;
}
}
}
}
//设置每行每列的值
for (int i = 0; i < exportList.get(n).getList().size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow(i + 1);
// 在row行上创建一个方格
for (int j = 0; j < exportList.get(n).getKeys().length; j++) {
Cell cell = row1.createCell(j);
Object val = null;
Field[] fields = exportList.get(n).getList().get(i).getClass().getDeclaredFields();
for (Field f : fields) {
String name = f.getName();
if (name.equals(exportList.get(n).getKeys()[j])) {
try {
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method m = exportList.get(n).getList().get(i).getClass().getMethod(methodName);
val = m.invoke(exportList.get(n).getList().get(i));
} catch (Exception e) {
logger.debug(e);
}
}
}
if (total[0][j] == 1.0) {
total[1][j] += (int)val;
}
if (total[0][j] == 2.0) {
total[1][j] += (double)val;
}
if (val != null) {
switch (val.getClass().getName().toLowerCase()) {
case "int":
case "java.lang.integer":
cell.setCellValue((int) val);
break;
case "long":
case "java.lang.long":
case "double":
case "java.lang.double":
case "java.math.bigdecimal":
cell.setCellValue((double) val);
break;
case "java.lang.boolean":
cell.setCellValue((Boolean) val);
break;
case "java.util.date":
cell.setCellValue((Date) val);
cell.setCellStyle(dateStyle);
break;
case "java.lang.string":
default:
cell.setCellValue(val.toString());
break;
}
}
}
}
Row totalRow = sheet.createRow(exportList.get(n).getList().size() + 1);
DecimalFormat df = new DecimalFormat("0.00");
for (int i = 0; i < total[1].length; i++) {
if (total[0][i] == 1.0) {
setValue4(wb, totalRow.createCell(i), String.valueOf((int)(total[1][i])));
}
if (total[0][i] == 2.0) {
setValue4(wb, totalRow.createCell(i), df.format(total[1][i]));
}
}
Row lastRow1 = sheet.createRow(exportList.get(n).getList().size() + 2);
setValue3(wb, lastRow1.createCell(0), extraParam[0]);
setValue2(wb, lastRow1.createCell(1), extraParam[1]);
setValue2(wb, lastRow1.createCell(2), extraParam[2]);
setValue2(wb, lastRow1.createCell(3), extraParam[3]);
}
try {
wb.write(os);
} catch (Exception e) {
logger.debug(e);
}
byte[] content = os.toByteArray();
return content;
}
public <T> List<T> excelToList(Class<T> cls, InputStream s, String tableName, String[] keys, String[] columnNames) throws Exception {
List<T> list = new ArrayList();
Workbook wb = new HSSFWorkbook(s);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
if (sheet.getSheetName().equals(tableName) || ((tableName.isEmpty()) && i == 0)) {
Row firstRow = sheet.getRow(0);
int colCount = firstRow.getLastCellNum();
//判断列是否都存在
List<String> excelColunms = new ArrayList();
for (int k = 0; k < colCount; k++) {
String colName = firstRow.getCell(k).getStringCellValue();
excelColunms.add(colName);
}
List<String> noCols = new ArrayList();
for (String cn : columnNames) {
if (!excelColunms.contains(cn)) {
noCols.add(cn);
}
}
if (noCols.size() > 0) {
throw new Exception("Excel缺少如下列：" + noCols);
}
int rowCount = sheet.getLastRowNum();
for (int j = 1; j < rowCount + 1; j++) {
T t = cls.newInstance();
Row row = sheet.getRow(j);
for (int k = 0; k < colCount; k++) {
String colName = firstRow.getCell(k).getStringCellValue();
for (int l = 0; l < columnNames.length; l++) {
if (colName.equals(columnNames[l])) {
if (row.getCell(k) != null) {
Field[] fields = t.getClass().getDeclaredFields();
for (Field f : fields) {
String name = f.getName();
if (name.equals(keys[l])) {
String methodName = "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method m = t.getClass().getMethod(methodName, f.getType());
String typeName = f.getType().getName().toLowerCase();
switch (typeName) {
case "int":
case "java.lang.integer":
try {
m.invoke(t, (int) Math.round(row.getCell(k).getNumericCellValue()));
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为数值类型");
}
break;
case "long":
case "java.lang.long":
try {
m.invoke(t, row.getCell(k).getStringCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为数值类型");
}
break;
case "double":
case "java.lang.double":
try {
m.invoke(t, row.getCell(k).getNumericCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为数值类型");
}
break;
case "java.math.bigdecimal":
try {
m.invoke(t, new BigDecimal(row.getCell(k).getNumericCellValue()));
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为货币类型");
}
break;
case "java.lang.boolean":
try {
m.invoke(t, row.getCell(k).getBooleanCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为布尔类型");
}
break;
case "java.util.date":
try {
m.invoke(t, row.getCell(k).getDateCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为日期类型");
}
break;
case "java.lang.string":
try {
m.invoke(t, row.getCell(k).getStringCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效，应为文本类型");
}
break;
default:
try {
m.invoke(t, row.getCell(k).getStringCellValue());
} catch (Exception e) {
throw new Exception("第" + (j + 1) + "行，第" + (k < 26 ? (char) ('A' + k) : "A" + (char) (('A' + k - 26))) + "列，类型无效");
}
break;
}
}
}
}
}
}
}
list.add(t);
}
}
}
return list;
}
public void setValue(Workbook wb, Cell cell, String value) {
CellStyle style3 = wb.createCellStyle();
style3.setAlignment(CellStyle.ALIGN_CENTER);//水平居中
style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
Font font3 = wb.createFont();
font3.setFontHeightInPoints((short) 10);//字体大小
style3.setFont(font3);
cell.setCellStyle(style3);
cell.setCellValue(value);
}
public void setValue2(Workbook wb, Cell cell, String value) {
CellStyle style3 = wb.createCellStyle();
style3.setAlignment(CellStyle.ALIGN_RIGHT);
style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
Font font3 = wb.createFont();
font3.setFontHeightInPoints((short) 10);//字体大小
style3.setFont(font3);
cell.setCellStyle(style3);
cell.setCellValue(value);
}
public void setValue3(Workbook wb, Cell cell, String value) {
CellStyle style3 = wb.createCellStyle();
style3.setAlignment(CellStyle.ALIGN_LEFT);
style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
Font font3 = wb.createFont();
font3.setFontHeightInPoints((short) 10);//字体大小
style3.setFont(font3);
cell.setCellStyle(style3);
cell.setCellValue(value);
}
public void setValue4(Workbook wb, Cell cell, String value) {
CellStyle style4 = wb.createCellStyle();
style4.setAlignment(CellStyle.ALIGN_RIGHT);
style4.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//c垂直居中
Font font4 = wb.createFont();
font4.setBold(true);
font4.setFontHeightInPoints((short) 10);//字体大小
style4.setFont(font4);
cell.setCellStyle(style4);
cell.setCellValue(value);
}
}
4、导出Excel例子
@ApiOperation(value = "导出注销列表")
@RequestMapping(value = "/export", method = RequestMethod.GET)
public void export(
@ApiParam("处理状态status 0未处理 1已处理") @RequestParam(value = "status", required = false) String status,
@ApiParam("appName(app名称)") @RequestParam(value = "appName", required = false) String appName,
@ApiParam("userName(用户姓名)") @RequestParam(value = "userName", required = false) String userName,
@ApiParam("phone(手机号)") @RequestParam(value = "phone", required = false) String phone,
@ApiParam("startTime") @RequestParam(value = "startTime", required = false) String startTime,
@ApiParam("endTime") @RequestParam(value = "endTime", required = false) String endTime,
HttpServletResponse response
) {
List<CancellationRecordDTO> CancellationRecordList = cancellationRecordService.getCancellationRecordsList(status, appName, userName, phone, startTime, endTime);
List<CancellationRecordExportDTO> cancellationRecordExportDTOList = new ArrayList<>();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
for (CancellationRecordDTO cancellationRecordDTO : CancellationRecordList) {
CancellationRecordExportDTO cancellationRecordExportDTO = new CancellationRecordExportDTO();
cancellationRecordExportDTO.setAppName(cancellationRecordDTO.getAppName());
cancellationRecordExportDTO.setUserName(cancellationRecordDTO.getUserName());
cancellationRecordExportDTO.setPhone(cancellationRecordDTO.getPhone());
cancellationRecordExportDTO.setCreateTime(df.format(cancellationRecordDTO.getCreateTime()));
cancellationRecordExportDTO.setStatus("0".equals(cancellationRecordDTO.getStatus())?"未处理":"已处理");
cancellationRecordExportDTO.setRemark(cancellationRecordDTO.getRemark());
cancellationRecordExportDTOList.add(cancellationRecordExportDTO);
}
try (ServletOutputStream out = response.getOutputStream()) {
String[] keys = new String[]{"appName", "userName", "phone", "createTime", "status", "remark"};
String[] columnNames = new String[]{"APP名称", "用户姓名", "用户手机号", "申请时间", "处理状态", "备注"};
byte[] content = ExcelHelper.INSTANCE.listToExcelWithExtraRow(CancellationRecordExportDTO.class, cancellationRecordExportDTOList, keys, columnNames, new String[]{"", "", "", ""});
String fileName = "注销申请列表";
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("gbk");
response.addHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes("gbk"), "iso-8859-1"));
out.write(content);
out.flush();
} catch (IOException e) {
log.error(e.getMessage());
}
}
* 这里将时间转为字符串存入Excel
恭喜注册成功，快来登录吧！