采用的poi版本依赖如下:
<properties>
<poi.version>3.13</poi.version>
</properties>
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- csv -->
<dependency>
<groupId>net.sourceforge.javacsv</groupId>
<artifactId>javacsv</artifactId>
<version>2.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.8</version>
</dependency>
Controller代码:
//数据导出
@GetMapping("sys/exportOrderInfo/{dateSection}")
@RequiresPermissions("sys:orderInfo:excel")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response,@PathVariable String dateSection) throws Exception {
Session session = super.getSession();
SysUser user = (SysUser)session.getAttribute(ComConst.SESSTION_KEY_USER);
int orgId=0;
try {
orgId = user.getOrgId();
} catch (Exception e) {
orgId=0;
e.printStackTrace();
}
//获取数据
//获取数据
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat nowformat = new SimpleDateFormat("yyyyMMddHHmmss");
Calendar c = Calendar.getInstance();
String startDate;
String nowDate = nowformat.format(c.getTime());
if(dateSectionCnstants.ALL.equals(dateSection)){
startDate = "";
}else if(dateSectionCnstants.ONEMONTH.equals(dateSection)){
c.add(Calendar.MONTH, -1); //得到前一个月
startDate = format.format(c.getTime());
System.out.println(startDate);
}else if(dateSectionCnstants.THREEMONTH.equals(dateSection)){
c.add(Calendar.MONTH, -3); //得到前3个月
startDate = format.format(c.getTime());
System.out.println(startDate);
}else if(dateSectionCnstants.HALFYEAR.equals(dateSection)){
c.add(Calendar.MONTH, -6); //得到前半年
startDate = format.format(c.getTime());
System.out.println(startDate);
}else if(dateSectionCnstants.ONEYEAR.endsWith(dateSection)){
c.add(Calendar.YEAR, -1); //年份减1
startDate =format.format(c.getTime());
System.out.println(startDate);
}else{
startDate = "";
}
List<OrderInfo> list = orderInfoService.excelOrderInfo(startDate);
//excel标题
String[] title = {"订单编号","购买人手机号","购买人证件号","订单总价","购买人姓名","商品名称","游玩时间","单价","购买数量","已检票数","总退票数","下单时间"};
//excel文件名
String fileName = "订单信息表"+nowDate+".xls";
//sheet名
String sheetName = "订单信息表";
String[][] content = null;
if(list != null){
content = new String[list.size()][title.length];
for (int i = 0; i < list.size(); i++) {
OrderInfo r = list.get(i);
content[i][0] = r.getOrderCode();
content[i][1] = r.getLinkMobile();
content[i][2] = r.getCertificateNo();
if(r.getOrderPrice() != null){
content[i][3] = r.getOrderPrice().toString();
}else{
content[i][3] = "";
}
content[i][4] = r.getLinkName();
content[i][5] = r.getGoodsName();
if(r.getOccDate() != null){
String occDate = DateUtil.getDateFormat(r.getOccDate(),"yyyyMMdd");
content[i][6] = occDate;
}else{
content[i][6] = "";
}
if(r.getPrice() != null){
content[i][7] = r.getPrice().toString();
}else{
content[i][7] = "";
}
if(r.getQuantity() != null){
content[i][8] = r.getQuantity().toString();
}else{
content[i][8] = "";
}
if(r.getAlreadyCheckNum() != null){
content[i][9] = r.getAlreadyCheckNum().toString();
}else{
content[i][9] = "";
}
//总退票数
content[i][10] = r.getReturnNum();
//下单时间
if(r.getCreateDate() != null){
String createDate = DateUtil.getDateFormat(r.getCreateDate(),"yyyyMMdd HH:mm:ss");
content[i][11] = createDate;
}else{
content[i][11] = "";
}
}
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}