java 数据导入到exc ,并下载
package com.lizi.admin.controller.platform.excel;
import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelUtil { // 创建HSSFWorkbook工作薄对象 public static HSSFWorkbook export_text(List<Map<String, Object>> list, String data) { try { // 创建工作薄对象 HSSFWorkbook wb = new HSSFWorkbook(); // 创建标题行样式 HSSFCellStyle headStyle = headStyle(wb); // 创建内容行样式 HSSFCellStyle contentStyle = contentStyle(wb); // 创建表 HSSFSheet sheet_1 = wb.createSheet(data + "对账信息"); // 设置表的默认列宽 sheet_1.setDefaultColumnWidth(30); // 创建标题行 HSSFRow headRow = sheet_1.createRow(0); HSSFCell head_cell_1 = headRow.createCell(0); // 创建标题行第一列 head_cell_1.setCellValue("对账时间"); // 第一列内容 head_cell_1.setCellStyle(headStyle); // 将标题行样式添加 HSSFCell head_cell_2 = headRow.createCell(1); head_cell_2.setCellValue("商家总流水"); head_cell_2.setCellStyle(headStyle); HSSFCell head_cell_3 = headRow.createCell(2); head_cell_3.setCellValue("商家总流水笔数"); head_cell_3.setCellStyle(headStyle); HSSFCell head_cell_4 = headRow.createCell(3); head_cell_4.setCellValue("实际收单总流水"); head_cell_4.setCellStyle(headStyle); HSSFCell head_cell_5 = headRow.createCell(4); head_cell_5.setCellValue("实际收单总流水笔数"); head_cell_5.setCellStyle(headStyle); HSSFCell head_cell_6 = headRow.createCell(5); head_cell_6.setCellValue("差异金额"); head_cell_6.setCellStyle(headStyle); HSSFCell head_cell_7 = headRow.createCell(6); head_cell_7.setCellValue("差异笔数"); head_cell_7.setCellStyle(headStyle); HSSFCell head_cell_8 = headRow.createCell(7); head_cell_8.setCellValue("调账金额"); head_cell_8.setCellStyle(headStyle); HSSFCell head_cell_9 = headRow.createCell(8); head_cell_9.setCellValue("调账笔数"); head_cell_9.setCellStyle(headStyle); HSSFCell head_cell_10 = headRow.createCell(9); head_cell_10.setCellValue("对账单状态"); head_cell_10.setCellStyle(headStyle); // 为内容行添加数据和样式 for (int i = 1; i <= list.size(); i++) { HSSFRow contentRow = sheet_1.createRow(i); Map<String, Object> map = list.get(i - 1); String accountsTime = map.get("accountsTime").toString(); String sysMoney = map.get("sysMoney").toString(); String sysNum = map.get("sysNum").toString(); String passagewayMoney = map.get("passagewayMoney").toString(); String passagewayNum = map.get("passagewayNum").toString(); String differenceMoney = map.get("differenceMoney").toString(); String differenceNumber = map.get("differenceNumber").toString(); String adjustmentMoney = map.get("adjustmentMoney").toString(); String adjustmentNumber = map.get("adjustmentNumber").toString(); String state = ""; if (map.get("state") != null) { if (map.get("state").toString().equals("0")) { state = "正常"; } if (map.get("state").toString().equals("1")) { state = "未对账"; } if (map.get("state").toString().equals("2")) { state = "已对账"; } } HSSFCell content_cell_1 = contentRow.createCell(0); content_cell_1.setCellValue(accountsTime); content_cell_1.setCellStyle(contentStyle); HSSFCell content_cell_2 = contentRow.createCell(1); content_cell_2.setCellValue(sysMoney); content_cell_2.setCellStyle(contentStyle); HSSFCell content_cell_3 = contentRow.createCell(2); content_cell_3.setCellValue(sysNum); content_cell_3.setCellStyle(contentStyle); HSSFCell content_cell_4 = contentRow.createCell(3); content_cell_4.setCellValue(passagewayMoney); content_cell_4.setCellStyle(contentStyle); HSSFCell content_cell_5 = contentRow.createCell(4); content_cell_5.setCellValue(passagewayNum); content_cell_5.setCellStyle(contentStyle); HSSFCell content_cell_6 = contentRow.createCell(5); content_cell_6.setCellValue(differenceMoney); content_cell_6.setCellStyle(contentStyle); HSSFCell content_cell_7 = contentRow.createCell(6); content_cell_7.setCellValue(differenceNumber); content_cell_7.setCellStyle(contentStyle); HSSFCell content_cell_8 = contentRow.createCell(7); content_cell_8.setCellValue(adjustmentMoney); content_cell_8.setCellStyle(contentStyle); HSSFCell content_cell_9 = contentRow.createCell(8); content_cell_9.setCellValue(adjustmentNumber); content_cell_9.setCellStyle(contentStyle); HSSFCell content_cell_10 = contentRow.createCell(9); content_cell_10.setCellValue(state); content_cell_10.setCellStyle(contentStyle); } return wb; } catch (Exception e) { e.getStackTrace(); } return null; } // 创建HSSFWorkbook工作薄对象 public static HSSFWorkbook export_day(List<Map<String, Object>> list, String data) { try { // 创建工作薄对象 HSSFWorkbook wb = new HSSFWorkbook(); // 创建标题行样式 HSSFCellStyle headStyle = headStyle(wb); // 创建内容行样式 HSSFCellStyle contentStyle = contentStyle(wb); // 创建表 HSSFSheet sheet_1 = wb.createSheet(data + "对账信息"); // 设置表的默认列宽 sheet_1.setDefaultColumnWidth(30); // 创建标题行 HSSFRow headRow = sheet_1.createRow(0); HSSFCell head_cell_1 = headRow.createCell(0); // 创建标题行第一列 head_cell_1.setCellValue("对账时间"); // 第一列内容 head_cell_1.setCellStyle(headStyle); // 将标题行样式添加 HSSFCell head_cell_2 = headRow.createCell(1); head_cell_2.setCellValue("商户名称"); head_cell_2.setCellStyle(headStyle); HSSFCell head_cell_3 = headRow.createCell(2); head_cell_3.setCellValue("系统订单号"); head_cell_3.setCellStyle(headStyle); HSSFCell head_cell_4 = headRow.createCell(3); head_cell_4.setCellValue("系统订单金额"); head_cell_4.setCellStyle(headStyle); HSSFCell head_cell_5 = headRow.createCell(4); head_cell_5.setCellValue("通道订单号"); head_cell_5.setCellStyle(headStyle); HSSFCell head_cell_6 = headRow.createCell(5); head_cell_6.setCellValue("通道订单金额"); head_cell_6.setCellStyle(headStyle); HSSFCell head_cell_7 = headRow.createCell(6); head_cell_7.setCellValue("订单状态"); head_cell_7.setCellStyle(headStyle); HSSFCell head_cell_8 = headRow.createCell(7); head_cell_8.setCellValue("订单类型"); head_cell_8.setCellStyle(headStyle); HSSFCell head_cell_9 = headRow.createCell(8); head_cell_9.setCellValue("交易时间"); head_cell_9.setCellStyle(headStyle); // 为内容行添加数据和样式 for (int i = 1; i <= list.size(); i++) { HSSFRow contentRow = sheet_1.createRow(i); Map<String, Object> map = list.get(i - 1); String accountsTime = map.get("accountsTime").toString(); String shopName = map.get("shopName").toString(); String sysNo = ""; if (map.get("sysNo") != null) { sysNo = map.get("sysNo").toString(); } String sysMoney = ""; if (map.get("sysMoney") != null) { sysMoney = map.get("sysMoney").toString(); } String passagewayNo = ""; if (map.get("passagewayNo") != null) { passagewayNo = map.get("passagewayNo").toString(); } String passagewayMoney = ""; if (map.get("passagewayMoney") != null) { passagewayMoney = map.get("passagewayMoney").toString(); } String orderType = ""; if (map.get("orderType") != null) { int type = Integer.valueOf(map.get("orderType").toString()).intValue(); switch (type) {// 0正常1订单缺失2通道缺失3金额不正确 case 0: orderType = "正常"; break; case 1: orderType = "订单缺失"; break; case 2: orderType = "通道缺失"; break; default: orderType = "金额不正确"; break; } } String orderState = ""; if (map.get("orderState") != null) { int state = Integer.valueOf(map.get("orderState").toString()).intValue(); switch (state) {// 0正常1未对账2已对帐3已调账 case 0: orderState = "正常"; break; case 1: orderState = "未对账"; break; case 2: orderState = "已对帐"; break; default: orderState = "已调账"; break; } } String transactionTime = map.get("transactionTime").toString(); HSSFCell content_cell_1 = contentRow.createCell(0); content_cell_1.setCellValue(accountsTime); content_cell_1.setCellStyle(contentStyle); HSSFCell content_cell_2 = contentRow.createCell(1); content_cell_2.setCellValue(shopName); content_cell_2.setCellStyle(contentStyle); HSSFCell content_cell_3 = contentRow.createCell(2); content_cell_3.setCellValue(sysNo); content_cell_3.setCellStyle(contentStyle); HSSFCell content_cell_4 = contentRow.createCell(3); content_cell_4.setCellValue(sysMoney); content_cell_4.setCellStyle(contentStyle); HSSFCell content_cell_5 = contentRow.createCell(4); content_cell_5.setCellValue(passagewayNo); content_cell_5.setCellStyle(contentStyle); HSSFCell content_cell_6 = contentRow.createCell(5); content_cell_6.setCellValue(passagewayMoney); content_cell_6.setCellStyle(contentStyle); HSSFCell content_cell_7 = contentRow.createCell(6); content_cell_7.setCellValue(orderType); content_cell_7.setCellStyle(contentStyle); HSSFCell content_cell_8 = contentRow.createCell(7); content_cell_8.setCellValue(orderState); content_cell_8.setCellStyle(contentStyle); HSSFCell content_cell_9 = contentRow.createCell(8); content_cell_9.setCellValue(transactionTime); content_cell_9.setCellStyle(contentStyle); } return wb; } catch (Exception e) { e.getStackTrace(); } return null; } /** * 创建标题行样式 * * @param wb * @return */ public static HSSFCellStyle headStyle(HSSFWorkbook wb) { HSSFCellStyle headStyle = wb.createCellStyle(); // 创建样式对象 HSSFFont headFont = wb.createFont(); // 创建字体 headFont.setFontName("微软雅黑"); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headFont.setColor(HSSFFont.COLOR_RED); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headStyle.setFont(headFont); return headStyle; } /** * 创建内容行样式 * * @param wb * @return */ public static HSSFCellStyle contentStyle(HSSFWorkbook wb) { HSSFCellStyle contentStyle = wb.createCellStyle(); HSSFFont contentFont = wb.createFont(); contentFont.setFontName("微软雅黑"); contentFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); contentFont.setColor(HSSFFont.COLOR_NORMAL); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); return contentStyle; } }
/**
* 导出年月的对账单
* @param model
* @param request
* @param response
* @param session
* @param payType
* @param date
* @param type
* @return
*/
@ResponseBody
@RequestMapping(value = "/FINANCIALCENTER/exportByYM")
public ModelAndView exportByYM(Model model, HttpServletRequest request,HttpServletResponse response, HttpSession session,
@RequestParam(value = "payType", required = true) String payType,
@RequestParam(value = "date", required = true) String date,
@RequestParam(value = "type", required = true) String type) {
try {
if(StringUtils.isNotBlank(payType)){
if(payType.equals("1")){
payType="7";
}
if(payType.equals("2")){
payType="6";
}
if(payType.equals("3")){
payType="4";
}
}
Criteria criteria = new Criteria();
criteria.getCondition().put("state", "");
criteria.getCondition().put("type", payType);
SessionUser user = getSessionUser(session);
TUSER tUser = this.tUSERService.queryById(user.getUserId());
Integer agentId=0;
if(tUser.getTYPE().equals(SystemConfig.USER_TYPE_AGENT_T)){
agentId=tUser.getAGENTID();
}
criteria.getCondition().put("agentId",agentId);
List<Map<String,Object>> maps=null;
if(type.equals("1")){//年
criteria.getCondition().put("year", date);
maps=this.adjustService.getOrderByYearAndType(criteria);
}
if(type.equals("2")){//月
criteria.getCondition().put("date", date);
maps=this.adjustService.getOrderByMonthAndType(criteria);
}
if(type.equals("3")){//日
criteria.getCondition().put("day", date);
maps=this.adjustService.getOrderByDayAndType(criteria);
}
if(maps!=null&&maps.size()>0){
//调用方法创建HSSFWorkbook工作簿对象
HSSFWorkbook wb = null;
if(type.equals("3")){//日
wb = ExcelUtil.export_day(maps,date);
}else{
wb = ExcelUtil.export_text(maps,date);
}
try {
//定义导出文件的名称，看不懂的同学可以先行了解一下文件下载
Random r = new Random();
int n2 = r.nextInt(1000);
String name=date+"_"+n2+".xls";
String fileName = new String(name.getBytes("UTF-8"),"ISO-8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename="+fileName);
OutputStream os = response.getOutputStream();
//将工作薄写入到输出流中
wb.write(os);
os.close();
} catch (Exception e) {
e.getStackTrace();
}
}else{
model.addAttribute(ErrorMsg.KEY_CODE, ErrorMsg.CALL_FAIL);
model.addAttribute(ErrorMsg.KEY_MESSAGE, date+"该日期没有交易数据");
return new ModelAndView(new MappingJacksonJsonView());
}
} catch (Exception e) {
e.printStackTrace();
model.addAttribute(ErrorMsg.KEY_CODE, ErrorMsg.CALL_FAIL);
model.addAttribute(ErrorMsg.KEY_MESSAGE, "查詢失败！");
}
return new ModelAndView(new MappingJacksonJsonView());
}
/**
* 导出excel
*/
exports.exportExcel = function(me){
var year = $cmt_year_details.param.year;
var payType = $cmt_year_details.payType;
var type = 1;
confirmDialog("导出提示", "确定导出Excel么？", {
okAction : function() {
$("#"+$cmt_year_details.page_id).loading('show');
location.href=$cmt_year_details.config.exportByYM+'&date='+year+'&payType='+payType+'&type='+type;
$("#"+$cmt_year_details.page_id).loading('hide');
}
});
}
