poi导出excel
Spring+SpringMVC+Maven+MyBatis+Mysql此环境下的POI导入
此文档讲解Excel导出的功能
1.1 poi导出Excel
浏览器效果展示
前端代码
前端html代码：
<div class="layui-inline">
<div class="layui-input-block" style="margin-left: 10px;">
<button type="button" permission="49" id="exportExcel" class="layui-btn layui-btn-small">Excel导出</button>
</div>
</div>
Js代码：
//导出商城订单
<script>
$('#exportExcel').on('click', function() {
confirm("确认导出吗？")?window.location.href ="/exportExcel/export":"";//此处填上你请求Excel对应的url
});
</script>
依赖包注入(pom.xml)
<!-- poi Excel导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!-- 上传文件 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.2</version>
</dependency>
Controller控制层
Package com.controller;
import cn.medbridge.ss.model.UserBk;
import cn.medbridge.ss.service.ExportExcelService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
@RestController
@RequestMapping("/exportExcel")
public class ExportExcelController {
/**
* 日志监控
*/
private static Logger Logger = LogManager.getLogger(ExportExcelController.class);
@Autowired
private ExportExcelService exportExcelService;
/**
* 商城已付款订单批量导出为处理中
* @param response
*/
@RequestMapping("/export")
@ResponseBody
public void export(HttpServletResponse response, HttpSession session) {
Logger.info("商城订单导出");
UserBk user = (UserBk)session.getAttribute("users");
try {
String fileName = "待兑换积分券订单";
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls" );
ServletOutputStream out = response.getOutputStream();
String[] titles = { "序号","用户名称", "联系电话" ,"兑换订单编号","兑换日期","兑换积分值","打款金额","户名","卡号","开户信息","银行流水单号"};
exportExcelService.export(titles, out,user.getuName());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Service业务逻辑层
Service层
import java.util.List;
import javax.servlet.ServletOutputStream;
import cn.medbridge.ss.model.ShoppingOrderExport;
import cn.medbridge.ss.model.UserBk;
/**
* 导出
* @author WYL
*
*/
public interface ExportExcelService {
/**
* 导出
* @param titles
* @param out
* @param lis
*/
public void export(String[] titles, ServletOutputStream out, String operator);
/**
* 获得商城订单列表
* @return
*/
List<ShoppingOrderExport> getExportShoppingOrderList();
}
Service实现类
import cn.medbridge.ss.dao.ShoppingOrderMapper;
import cn.medbridge.ss.model.ShoppingOrderExport;
import cn.medbridge.ss.service.ExportExcelService;
import cn.medbridge.ss.service.ScoreHistoryService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.ServletOutputStream;
import java.util.List;
/**
* 导出
* @author WYL
*
*/
@Service
public class ExportExcelServiceImpl implements ExportExcelService {
@Autowired
private ShoppingOrderMapper shoppingOrderMapper;
@Autowired
private ScoreHistoryService scoreHistoryService;
/**
* 导出
*
* @param titles
* @param out
*/
public void export(String[] titles, ServletOutputStream out, String operator) {
try {
// 第一步，创建一个workbook，对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
// 居中样式
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置表头
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow hssfRow = hssfSheet.createRow(0);
//设置列
HSSFCell hssfCell = null;
for (int i = 0; i < titles.length; i++) {
hssfCell = hssfRow.createCell(i);// 在行上新建列
hssfCell.setCellValue(titles[i]);// 设置列
hssfCell.setCellStyle(hssfCellStyle);// 设置列的样式
}
List<ShoppingOrderExport> list = shoppingOrderMapper.getExportShoppingOrderList();
// 第五步，写入实体数据
if(list.size()>0) {
for(int i = 0 ;i < list.size();i++) {
hssfRow = hssfSheet.createRow(i + 1);
ShoppingOrderExport soe = list.get(i);
hssfRow.createCell(0).setCellValue(i+1);
if(soe.getUserName() != null)
hssfRow.createCell(1).setCellValue(soe.getUserName());
if(soe.getPhone() != null)
hssfRow.createCell(2).setCellValue(soe.getPhone());
if(soe.getSoCode() != null)
hssfRow.createCell(3).setCellValue(soe.getSoCode());
if(soe.getCreateTime() != null)
hssfRow.createCell(4).setCellValue(soe.getCreateTime());
if(soe.getSoNum() != null)
hssfRow.createCell(5).setCellValue(soe.getSoNum());
if(soe.getSoPrice() != null)
hssfRow.createCell(6).setCellValue(soe.getSoPrice());
if(soe.getAccountName() != null)
hssfRow.createCell(7).setCellValue(soe.getAccountName());
if(soe.getAccountCode() != null)
hssfRow.createCell(8).setCellValue(soe.getAccountCode());
if(soe.getAccountBank() != null)
hssfRow.createCell(9).setCellValue(soe.getAccountBank());
if(soe.getSenderCode() != null)
hssfRow.createCell(10).setCellValue(soe.getSenderCode() );
//更新状态
int updateStatus = shoppingOrderMapper.updateShoppingOrderSoStatus(soe.getSoCode(),operator);
if(updateStatus > 0 ){
//导入成功 推送已发货消息
scoreHistoryService.goodsDeliver(soe.getSoCode(),4);
}else{
System.out.println("更新失败");
}
}
}
try {
/**
* 导出到服务器
FileOutputStream fileOutputStream = new FileOutputStream("D://导入失败数据表.xls");// 指定路径与名字和格式
workbook.write(fileOutputStream);// 将数据写出去
fileOutputStream.close();// 关闭输出流
*/
//导出到客户端
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("导出信息失败");
// throw new Exception("导出信息失败！");
}
}
/**
* 获得导出的商城订单
*/
@Override
public List<ShoppingOrderExport> getExportShoppingOrderList() {
return shoppingOrderMapper.getExportShoppingOrderList();
}
}
Mapper持久层
此方法为要导出的数据
//获得商城订单List
List<ShoppingOrderExport> getExportShoppingOrderList();
此为更新状态的mapper
//更新已付款的订单为处理中
int updateShoppingOrderSoStatus(@Param("soCode")String soCode,@Param("operator")String operator);
xml配置层
<!-- excel导出 语句-->
<select id="getExportShoppingOrderList" resultMap="BaseResultExportMap" >
SELECT
sso.user_name,
case sso.u_type when 1 then
(select sa.phone from ss_agency sa where sa.a_id = sso.user_id)
else
(select ss.phone from ss_servant ss where ss.s_id = sso.user_id)
end sa_phone,
sso.so_code,
sso.create_time,
sso.so_num * sso.so_price so_num,
null so_price ,
sso.user_name accountName,
scw.card_num accountCode,
scw.bank_name accountBank,
null senderCode
FROM ss_shopping_order sso ,ss_goods sg,ss_cash_way scw
WHERE sso.g_id = sg.g_id
and sso.user_id = scw.user_id
and sso.u_type = scw.u_type
-- and scw.locked = 1
and sso.so_status = 2
and sg.g_class = 2
order by sso.create_time desc
</select>
<!-- 更新已付款的订单为处理中 -->
<update id="updateShoppingOrderSoStatus">
UPDATE ss_shopping_order sso
set sso.so_status = 4,sso.operator= #{operator}
WHERE sso.so_code = #{soCode}
AND sso.so_status = 2
</update>
导出结果
浙公网安备 33010602011771号