<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
public class ExportExcelUtil {
/** 显示的导出表的标题*/
private String title;
/** 导出表的列名*/
private String[] rowName ;
/** 数据集合*/
private List<Object[]> dataList = new ArrayList<>();
/** 构造方法,传入要导出的数据*/
public ExportExcelUtil(String title, String[] rowName, List<Object[]> dataList){
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
}
/**
* 导出数据
* @param response
*/
public void export( HttpServletResponse response){
try {
// 创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet(title);
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//设置高度
rowm.setHeight((short) (25 * 35));
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
//获取列头样式对象
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
//获取子标题样式对象
HSSFCellStyle subStyle = this.getSubStyle(workbook);
//单元格样式对象
HSSFCellStyle style = this.getStyle(workbook);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 创建第二行
HSSFRow subRow = sheet.createRow(1);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, (rowName.length - 1)));
HSSFCell subCell = subRow.createCell(0);
subCell.setCellStyle(subStyle);
subCell.setCellValue("下载时间: " + DateUtil.Date2String(new Date()));
// 定义所需列数
int columnNum = rowName.length;
// 在索引3的位置创建行(最顶端的行开始的第三行)
HSSFRow rowRowName = sheet.createRow(2);
rowRowName.setHeight((short) (25 * 25)); //设置高度
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
//创建列头对应个数的单元格
HSSFCell cellRowName = rowRowName.createCell(n);
//设置列头单元格的数据类型
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
//设置列头单元格的值
cellRowName.setCellValue(text);
//设置列头单元格样式
cellRowName.setCellStyle(columnTopStyle);
}
//将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
//遍历每个对象
Object[] obj = dataList.get(i);
//创建所需的行数
HSSFRow row = sheet.createRow(i + 3);
//设置高度
row.setHeight((short) (25 * 20));
for (int j = 0; j < obj.length; j++) {
//设置单元格的数据类型
HSSFCell cell = null;
if (j == 0) {
cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i + 1);
} else {
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
cell.setCellValue(obj[j].toString());
}
}
//应用单元格样式
cell.setCellStyle(style);
}
}
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length + 2;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 64);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
if (workbook != null) {
try {
String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = response.getOutputStream();
workbook.write(out);
/* FileOutputStream out = new FileOutputStream("d:\\" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
.toString() + ".xls");*/
workbook.write(out);
out.close();
} catch (IOException e) {
throw new BizException(BizResultCodeEnum.SYSTEM_BUSY, "生成图表异常");
}
}
} catch(Exception e){
throw new BizException(BizResultCodeEnum.SYSTEM_ERROR, "系统异常");
}
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置边框
//style.setBorderBottom(BorderStyle.THIN);//设置底边框;
//下边框
style.setBorderLeft(BorderStyle.DASH_DOT_DOT); // 左边框
style.setBorderTop(BorderStyle.THIN);//设置顶边框;
style.setBorderRight(BorderStyle.THIN);//右边
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置居中
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置单元格背景颜色
style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
public HSSFCellStyle getSubStyle(HSSFWorkbook workbook) {
//----------------二级标题格样式----------------------------------
HSSFCellStyle subStyle = workbook.createCellStyle(); //表格样式
subStyle.setAlignment(HorizontalAlignment.RIGHT);
subStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font ztFont2 = workbook.createFont();
// 设置字体为斜体字
ztFont2.setItalic(false);
// 将字体设置为“红色”
ztFont2.setColor(Font.COLOR_NORMAL);
// 将字体大小设置为18px
ztFont2.setFontHeightInPoints((short) 11);
// 不加粗
ztFont2.setBold(false);
// 字体应用到当前单元格上
ztFont2.setFontName("宋体");
/*// 添加(Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线)
ztFont.setUnderline(Font.U_DOUBLE);
// 是否添加删除线
ztFont.setStrikeout(true);*/
subStyle.setFont(ztFont2);
//设置单元格背景颜色
subStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
subStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return subStyle;
}
/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置边框
style.setBorderBottom(BorderStyle.THIN);//设置底边框;
//下边框
style.setBorderLeft(BorderStyle.DASH_DOT_DOT); // 左边框
style.setBorderTop(BorderStyle.THIN);//设置顶边框;
style.setBorderRight(BorderStyle.THIN);//右边
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置居中
style.setAlignment(HorizontalAlignment.CENTER);//水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//创建一个DataFormat对象
HSSFDataFormat format = workbook.createDataFormat();
//控制单元格格式,@就是指文本型
style.setDataFormat(format.getFormat("@"));
return style;
}
}
工具类使用方法
String title = "商户黑名单";
String[] rowName = {"序号","创建时间","修改时间","商户号","类别","备注"};
List<Object[]> dataList = new ArrayList<>();
Object[] objs = null;
List<PreauthforbidMerchantVo> vos = preauthforbidMerchantService.queryBatchMerchant("2018-09-18",
"2018-09-25");
for (int i =0;i<vos.size();i++) {
PreauthforbidMerchantVo vo = vos.get(i);
objs = new Object[rowName.length];
objs[0] = i;//序号
objs[1] = vo.getGmtCreateString() == null ? "-" : vo.getGmtCreateString();
objs[2] = vo.getGmtModifiedString() == null ? "-" : vo.getGmtModifiedString();
objs[3] = vo.getMerchantId() == null ? "-" : vo.getMerchantId();
objs[4] = vo.getValid() == 1 ? "黑名单" : "白名单";
objs[5] = vo.getRemark() == null ? "-" : vo.getRemark();
dataList.add(objs);
}
ExportExcel exportExcel = new ExportExcel(title,rowName,dataList);
exportExcel.export();