Excel导出
导入到的jar包:
<dependency>
<groupId>org.apache.poi
</groupId>
<artifactId>poi-ooxml-schemas
</artifactId>
<version>3.16
</version>
</dependency>
<dependency>
<groupId>org.apache.poi
</groupId>
<artifactId>poi-examples
</artifactId>
<version>3.16
</version>
</dependency>
<dependency>
<groupId>org.apache.poi
</groupId>
<artifactId>poi-ooxml
</artifactId>
<version>3.16
</version>
</dependency>
<dependency>
<groupId>org.apache.poi
</groupId>
<artifactId>poi-scratchpad
</artifactId>
<version>3.16
</version>
</dependency>
//从分页对象里面出参数
Map<String
,
Object> params = couponDetailsTemporaryVOPage.getCondition() ==
null
?
new
HashMap<>()
: couponDetailsTemporaryVOPage.getCondition()
;
List<CouponDetailsTemporaryVO> couponList =
this
.
baseMapper
.findCouponDetailsTemporaryPage(couponDetailsTemporaryVOPage
,
params)
;
public static final
String[]
voucherColumnNames
= {
"批次"
,
"兑换码"
,
"抵用券名称"
,
"金额"
,
"生效日期"
,
"失效日期"
,
"创建原因"
}
;
//主要是get方法
public static final
String[]
voucherMethodNames
= {
"
get
BatchId"
,
"
get
CouponCode"
,
"
get
CouponDetailsName"
,
"
get
CutAmount"
,
"
get
BeginTime"
,
"
get
EndTime"
,
"
get
Reason"
}
;
//导出的功能
ExcelEntity<CouponDetailsTemporaryVO> excelEntity =
new
ExcelEntity<>(
"兑换码报表"
,
ExcelExporter.
voucherColumnNames
,
ExcelExporter.
voucherMethodNames
,
couponList)
;
HSSFWorkbook excel =
null;
try
{
excel = ExcelExporter.
export2Excel
(excelEntity)
;
ExcelExporter.
saveWorkBook
(excel
,
response)
;
}
catch
(Exception e) {
e.printStackTrace()
;
}
需要的两个工具类
package com.jiahui.qwc.hcrm.core.utils
;
import java.util.List
;
/**
* 代表要打印的Excel表格,用于存放要导出为Excel的相关数据
*
*
@param
<
T
>
代表要打印的数据实体,如User等
*/
public class ExcelEntity<
T> {
private String
sheetName =
"Sheet1"
;
//默认生成的sheet名称
private String
header=
""
;
//题头
private String
footer=
""
;
//脚注
//底下是必须具备的属性
private String
fileName
;
private String[]
columnNames
;
//列名
private String[]
methodNames
;
//与列名对应的方法名
private List<
T>
entities
;
//数据实体
public
ExcelEntity(String fileName
, String[] columnNames
, String[] methodNames
, List<
T> entities) {
this(
"sheet1"
,
""
,
""
,fileName
,columnNames
,methodNames
,entities)
;
}
public
ExcelEntity(String sheetName
, String header
, String footer
, String fileName
, String[] columnNames
,
String[] methodNames
, List<
T> entities) {
this.
sheetName = sheetName
;
this.
header = header
;
this.
footer = footer
;
this.
fileName = fileName
;
this.
columnNames = columnNames
;
this.
methodNames = methodNames
;
this.
entities = entities
;
}
public String
getHeader() {
return
header
;
}
public void
setHeader(String header) {
this.
header = header
;
}
public String
getSheetName() {
return
sheetName
;
}
public void
setSheetName(String sheetName) {
this.
sheetName = sheetName
;
}
public List<
T>
getEntities() {
return
entities
;
}
/**
*
*
@param
entities
用于导出Excel的实体集合
*/
public void
setEntities(List<
T> entities) {
this.
entities = entities
;
}
public String
getFooter() {
return
footer
;
}
public void
setFooter(String footer) {
this.
footer = footer
;
}
public String[]
getColumnNames() {
return
columnNames
;
}
public void
setColumnNames(String[] columnNames) {
this.
columnNames = columnNames
;
}
public String
getFileName() {
return
fileName
;
}
public void
setFileName(String fileName) {
this.
fileName = fileName
;
}
public String[]
getMethodNames() {
return
methodNames
;
}
public void
setMethodNames(String[] methodNames) {
this.
methodNames = methodNames
;
}
}
需要的两个工具类
package
com.jiahui.qwc.hcrm.core.utils
;
import
org.apache.poi.hssf.usermodel.HSSFSheet
;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook
;
import
org.apache.poi.ss.usermodel.*
;
import
javax.servlet.http.HttpServletResponse
;
import
java.io.ByteArrayOutputStream
;
import
java.lang.reflect.Method
;
import
java.text.SimpleDateFormat
;
import
java.util.Date
;
import
java.util.List
;
/**
* 一个通用的将List
<T>
中数据导出为Excel文档的工具类
*/
public class
ExcelExporter {
/**
* 收入报表名
*/
public static final
String
EXPORT_INCOME_NAME
=
"收入报表"
;
/**
* 收入报表表头数组
*/
public static final
String[]
INCOME_HEADERS
={
"系统流水号"
,
"第三方支付流水号"
,
"会员注册号码"
,
"购买方式"
,
"支付方式"
,
"是否是企业用户"
,
"购买途径"
,
"是否是套餐"
,
"销售/消耗"
,
"服务利润中心代码"
,
"服务利润中心名称"
,
"类别No"
,
"大类"
,
"小类"
,
"支付时间"
,
"实际签到时间"
,
"原价"
,
"收入金额"
,
"收入金额(不含税)"
,
"税额"
,
"账单金额"
,
"折扣金额"
,
"账单净额"
,
"公司支付金额"
,
"自付金额"
}
;
/**
*收入报表方法名数组
*/
public static final
String[]
INCOME_METHOD_NAMES
= {
"getPayId"
,
"getPayNo"
,
"getMobile"
,
"getWayName"
,
"getPayWay"
,
"getIsCompany"
,
"getBuyWayName"
,
"getIsPackage"
,
"getSale"
,
"getProfitCenterNo"
,
"getProfitCenterName"
,
"getGoodsTypeId"
,
"getCategoryName"
,
"getCategoryItemName"
,
"getPayTime"
,
"getSignTime"
,
"getPriceAmount"
,
"getIncome"
,
"getIncomeNotTaxAmount"
,
"getTax"
,
"getBillAmount"
,
"getDiscountAmount"
,
"getBillNoTaxAmount"
,
"getCompanyAmount"
,
"getPersonalAmount"
}
;
/**
* 抵用券/优惠券的表头数组
*/
public static final
String[]
voucherColumnNames
= {
"批次"
,
"兑换码"
,
"抵用券名称"
,
"金额"
,
"生效日期"
,
"失效日期"
,
"创建原因"
}
;
public static final
String[]
voucherMethodNames
= {
"getBatchId"
,
"getCouponCode"
,
"getCouponDetailsName"
,
"getCutAmount"
,
"getBeginTime"
,
"getEndTime"
,
"getReason"
}
;
/**
* 根据ExcelEntity等参数生成Workbook
*
@param
entity
*
@return
*
@throws
Exception
*/
public static
<
T
> HSSFWorkbook
export2Excel
(ExcelEntity<
T
> entity)
throws
Exception{
HSSFWorkbook workbook =
export2Excel
(entity.getHeader()
,
entity.getFooter()
,
entity.getSheetName()
,
entity.getColumnNames()
,
entity.getMethodNames()
,
entity.getEntities())
;
return
workbook
;
}
/**
* 根据给定参数导出Excel文档
*
*
@param
headerTitle
* 题头
*
@param
footerTitle
脚注
*
@param
sheetName
*
@param
columnNames
* 表头名称
*
@param
methodNames
*
@param
entities
*
@return
*
@throws
Exception
*/
public static
<
T
> HSSFWorkbook
export2Excel
(String headerTitle
,
String footerTitle
,
String sheetName
,
String[] columnNames
,
String[] methodNames
,
List<
T
> entities)
throws
Exception {
if
(methodNames.
length
!= columnNames.
length
)
throw new
IllegalArgumentException(
"methodNames.length should be equal to columnNames.length:"
+ columnNames.
length
+
" "
+ methodNames.
length
)
;
HSSFWorkbook wb =
new
HSSFWorkbook()
;
HSSFSheet sheet = wb.createSheet(sheetName)
;
//设置题头
Header header = sheet.getHeader()
;
header.setCenter(headerTitle)
;
//设置脚注
Footer footer = sheet.getFooter()
;
footer.setCenter(footerTitle)
;
int
[] columnWidths =
new int
[columnNames.
length
]
;
// 创建表头
createTableHeader
(sheet
,
0
,
headerTitle
,
columnNames
,
columnWidths)
;
// 填充表内容
createTableContent
(sheet
,
1
,
methodNames
,
columnWidths
,
entities)
;
return
wb
;
}
/**
* 创建表头
*
*
@param
sheet
*
@param
index
* 表头开始的行数
*
@param
headerTitle
* 题头
*
@param
columnNames
*
@param
columnWidths
*/
private static void
createTableHeader
(Sheet sheet
, int
index
,
String headerTitle
,
String[] columnNames
,
int
[] columnWidths) {
Row headerRow = sheet.createRow(index)
;
/* 格式设置 */
// 设置字体
Font font = sheet.getWorkbook().createFont()
;
font.setBoldweight(Font.
BOLDWEIGHT_BOLD
)
;
// 粗体显示
// 设置背景色
CellStyle style = sheet.getWorkbook().createCellStyle()
;
style.setFillForegroundColor(IndexedColors.
PALE_BLUE
.getIndex())
;
style.setFillPattern(CellStyle.
SOLID_FOREGROUND
)
;
style.setFont(font)
;
for
(
int
i =
0
;
i < columnNames.
length
;
i++) {
Cell headerCell = headerRow.createCell(i)
;
headerCell.setCellStyle(style)
;
headerCell.setCellValue(columnNames[i])
;
}
for
(
int
i =
0
;
i < columnNames.
length
;
i++) {
columnWidths[i] = (columnNames[i].getBytes().
length
+
2
) *
256
;
sheet.setColumnWidth(i
,
columnWidths[i])
;
}
}
/**
* 创建表格内容
*
*
@param
sheet
*
@param
rowIndexBegin
* 表内容开始的行数
*
@param
methodNames
* T对象的方法名
*
@param
columnWidths
*
@param
entities
*
@throws
Exception
*/
private static
<
T
>
void
createTableContent
(Sheet sheet
, int
rowIndexBegin
,
String[] methodNames
, int
[] columnWidths
,
List<
T
> entities)
throws
Exception {
Class<?
extends
Object> clazz =
null;
if
(entities.size() >
0
)
clazz = entities.get(
0
).getClass()
;
String content =
null;
for
(
T
t : entities) {
Row row = sheet.createRow(rowIndexBegin++)
;
for
(
int
i =
0
;
i < methodNames.
length
;
i++) {
Cell cell = row.createCell(i)
;
Method method = clazz.getMethod(methodNames[i]
, null
)
;
Object object = method.invoke(t
, null
)
;
object = object ==
null
?
""
: object
;
if
(object.getClass().equals(Date.
class
)) {
// 对日期格式进行特殊处理
content =
formatToString
((Date)object
,
"yyyy-MM-dd HH:mm:ss"
)
;
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// content = sdf.format((Date) object);
cell.setCellValue(content)
;
}
else
{
content = object.toString()
;
cell.setCellValue(content)
;
}
int
columnWidth = (content.getBytes().
length
+
2
) *
256
;
if
(columnWidth > columnWidths[i]) {
// 如果实际内容宽度大于对应的表头宽度,则设置为实际内容宽度
columnWidths[i] = columnWidth
;
sheet.setColumnWidth(i
,
columnWidths[i])
;
}
}
}
}
// public static <T> void testPOI(String[] columnNames, String[] methodNames, List<T> entities) throws Exception {
// String sheetName = "Test";
// String title = "标题栏";
// String dstFile = "d:/temp/test.xlsx";
// Workbook newWorkBook2007 = new XSSFWorkbook();
// Sheet sheet = newWorkBook2007.createSheet(sheetName);
// int[] columnWidths = new int[columnNames.length];
// // 创建表头
// createTableHeader(sheet, 0, title, columnNames, columnWidths);
// // 填充表内容
// createTableContent(sheet, 1, methodNames, columnWidths, entities);
// // 保存为文件
// saveWorkBook2007(newWorkBook2007, dstFile,res);
// System.out.println("end");
//
// }
/**
* 将workbook2007存为文件
*
*/
public static void
saveWorkBook
(HSSFWorkbook workBook
,
HttpServletResponse response)
throws
Exception{
ByteArrayOutputStream baos=
new
ByteArrayOutputStream()
;
workBook.write(baos)
;
byte
[] byteList = baos.toByteArray()
;
response.reset()
;
// 设置response的Header
response.addHeader(
"Content-Disposition"
,
String.
format
(
"attachment;filename=%s.xls"
,
UUIDUtil.
generateUUID
()))
;
response.setContentType(
"application/vnd.ms-excel;charset=utf-8"
)
;
baos.flush()
;
baos.close()
;
response.getOutputStream().write(byteList)
;
}
/**
* 将workbook2007存为文件
* 导出制定文件名的工作薄
*
@param
workBook
*
@param
response
*
@param
workBookName
*
@throws
Exception
*/
public static void
saveWorkBookName
(HSSFWorkbook workBook
,
HttpServletResponse response
,
String workBookName)
throws
Exception{
ByteArrayOutputStream baos=
new
ByteArrayOutputStream()
;
workBook.write(baos)
;
byte
[] byteList = baos.toByteArray()
;
response.reset()
;
// 设置response的Header
response.addHeader(
"Content-Disposition"
,
String.
format
(
"attachment;filename=%s.xls"
,
java.net.URLEncoder
.
encode
(workBookName
,
"UTF-8"
)))
;
response.setContentType(
"application/vnd.ms-excel;charset=utf-8"
)
;
baos.flush()
;
baos.close()
;
response.getOutputStream().write(byteList)
;
}
/**
* 测试方法
*
@param
args
*
@throws
Exception
*/
// public static void main(String[] args) throws Exception {
// // 准备数据
// List<Wind> winds = new ArrayList<>();// Wind有三个方法:getLocation、getSpeed、getTimestamp
// for (int i = 0; i < 10; i++) {
// Wind wind = new Wind();
// wind.setLocation(i);
// wind.setSpeed(i * 10);
// wind.setTimestamp(new Date());
// winds.add(wind);
// }
// String[] columnNames = { "地点", "速度", "时间" };
// String[] methodNames = { "getLocation", "getSpeed", "getTimestamp" };
String fileName = "d:/temp/excel1.xlsx";
// String fileName = "d:/excel1.xlsx";
// // 生成ExcelEntity实体,包含4个必备参数
// ExcelEntity<Wind> excelEntity = new ExcelEntity<>(fileName, columnNames, methodNames, winds);
// //excelEntity.setHeader("题头");
// //excelEntity.setFooter("脚注");
// Workbook excel = ExcelExporter.export2Excel(excelEntity);
// ExcelExporter.export2Excel("题头","脚注", "sheet1", columnNames, methodNames, winds);//也可以这样调用,无需新建ExcelEntity对象
// //将Workbook存为文件
// ByteArrayOutputStream baos=new ByteArrayOutputStream();
//
//
// ExcelExporter.saveWorkBook2007(excel, excelEntity.getFileName());
//
// System.out.println("导出完成!");
//
// }
/**
* 时间对象转换成字符串
<br />
* Author:zhanglei
<br />
* CreateTime:2015年11月17日16:17:07
*
*
@param
time
Date对象
*
@param
format
格式化字符串,如:yyyy-MM-dd HH:mm:ss.SSS
* */
public static
String
formatToString
(Date time
,
String format){
SimpleDateFormat dateFormat =
new
SimpleDateFormat(format)
;
return
dateFormat.format(time)
;
}
}