import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
import org.springside.modules.web.Servlets;
//在页面加入按钮用于发送请求以及请求条件
//导出Excel表格
function hkExportExcel() {
$.messager.confirm('确认', '确认把该搜索结果导出Excel表格 ？', function(r) {
if (r) {
//获得帅选条件
var startTime = $('#startTime1').datetimebox('getValue');
var endTime = $('#endTime1').datetimebox('getValue');
var mphone = $("#mphone").val();
var billCode = $("#billCode").val();
var businessType = $("#select_value").val();
$.messager.progress({
title : '处理中',
msg : '请稍后',
});
$.messager.progress('close');
location.href = "${ctx}/export/financialExportExcel?startTime="
+ startTime + "&endTime=" + endTime + "&mphone="
+ mphone + "&orderNo=" + billCode + "&businessType="
+ businessType;
}
});
}
import com.kedang.fenxiao.entity.AdminInfo;
import com.kedang.fenxiao.entity.FXFoundsFlow;
import com.kedang.fenxiao.entity.FXOrderRecord;
import com.kedang.fenxiao.service.AdminInfoService;
import com.kedang.fenxiao.service.FXOrderRecordService;
import com.kedang.fenxiao.service.FinancialStatementsService;
import com.kedang.fenxiao.service.account.ShiroDbRealm.ShiroUser;
import com.kedang.fenxiao.util.DateUtil;
import com.kedang.fenxiao.util.DateUtils;
import com.kedang.fenxiao.util.ExcelView;
@Controller
@RequestMapping(value = "export")
public class ExportExcelController
{
@Autowired
private FinancialStatementsService financialStatementService;
@Autowired
private AdminInfoService adminInfoService;
@Autowired
private FXOrderRecordService fxOrderRecordService;
/**
* 导出订单页面
* @param order
* @param startTime
* @param endTime
* @param session
* @param request
* @param response
* @return
*/
@RequestMapping(value = "exportOrderExcel", method = RequestMethod.GET)
public ModelAndView exportExcel(@ModelAttribute("startTime") String startTime,
@ModelAttribute("endTime") String endTime, HttpServletRequest request, HttpServletResponse response)
{
ExcelView excelView = new ExcelView();
try
{
Map<String, Object> map = new HashMap<String, Object>();
map.put("title", "订单导出" + DateUtil.getDateFormatS(new Date()) + ".xls");
String condition = "查询条件:";
Map<String, Object> searchParams = Servlets.getParametersStartingWith(request, "search_");
String orderStatus = request.getParameter("orderStatus");
String mobile = request.getParameter("mphone");
String orderNo = request.getParameter("orderNo");
String provinceId = request.getParameter("provinceId");
if (mobile != null && !"".equals(mobile))
{
condition += " 手机号码:" + mobile;
}
// if(order.getFxEnterprise().getId()!=null&&!"".equals(order.getFxEnterprise().getId())){
// condition+=" 合作商ID:"+order.getFxEnterprise().getId();
// }
// if(order.getAccount_type()!=null&&!"".equals(order.getAccount_type())){
// condition+=" 店铺名称:"+order.getFxEnterprise().getId();
// }
if (orderStatus != null && orderStatus != "")
{
if ("0".equals(orderStatus))
{
condition += " 订单状态:" + "成功";
}
else if ("1".equals(orderStatus))
{
condition += " 订单状态:" + "失败";
}
else if ("2".equals(orderStatus))
{
condition += " 订单状态:" + "充值中";
}
else if ("3".equals(orderStatus))
{
condition += " 订单状态:" + "提交成功";
}
}
if (startTime != null && !"".equals(startTime))
{
condition += " 开始时间:" + startTime;
}
if (endTime != null && !"".equals(endTime))
{
condition += " 结束时间:" + endTime;
}
if (orderNo != null && !"".equals(orderNo))
{
condition += " 订单号码:" + orderNo;
}
if (provinceId != null && !"".equals(provinceId))
{
condition += " 省份:" + showProvince(provinceId);
}
//小标题
List<String> title = new ArrayList<String>(Arrays.asList("序号", "企业名称", "订单编号"));
List<FXOrderRecord> orders = new ArrayList<FXOrderRecord>();
searchParams.put("LIKE_downstreamOrderNo", orderNo);
searchParams.put("LIKE_mobile", mobile);
searchParams.put("EQ_downstreamStatus", orderStatus);
//得到当前用户所在企业
ShiroUser shiroUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
AdminInfo admin = adminInfoService.findOne(shiroUser.getId());
searchParams.put("EQ_fxEnterprise.id", admin.getFxEnterprise().getId());
//添加时间条件
searchParams.put("GTE_clientSubmitTime", DateUtils.getFormatDate(startTime, "yyyy-MM-dd HH:mm"));
searchParams.put("LTE_clientSubmitTime", DateUtils.getFormatDate(endTime, "yyyy-MM-dd HH:mm"));
searchParams.put("EQ_fxProduct.provinceId", provinceId); orders = fxOrderRecordService.findAllOrderRecord(searchParams);
// orders=fxOrderRecordService.findAllOrderRecord();
excelView
.buildExcelDocument(
map,
exportCzinstanceOrderList(title, orders, DateUtil.getDateFormatS(new Date()) + "查询订单导出",
condition), request, response);
}
catch (Exception e)
{
e.printStackTrace();
// Log.exceptionInfo("查询订单导出 异常信息:"+e.getCause().getMessage());
}
return new ModelAndView(excelView);
}
/**
* @Description: 订单查询页面的导出
* @param title
* @param rows
* @param sheetName
* @param condition
* @return
* @throws Exception
*/
public static HSSFWorkbook exportCzinstanceOrderList(List<String> title, List<FXOrderRecord> rows,
String sheetName, String condition) throws Exception
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置列宽
sheet.setColumnWidth(0, 8 * 256);
sheet.setColumnWidth(1, 30 * 256);
sheet.setColumnWidth(2, 18 * 256);
sheet.setColumnWidth(3, 35 * 256);
sheet.setColumnWidth(4, 8 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 14 * 256);
//大标题
HSSFCellStyle bigTitle = workbook.createCellStyle();
bigTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont bigFont = workbook.createFont();
bigFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
bigFont.setFontHeight((short) 300);
bigTitle.setFont(bigFont);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, title.size() - 1));
HSSFRow titleR = sheet.createRow(0);
HSSFCell cellT = titleR.createCell(0);
cellT.setCellValue(sheetName);
cellT.setCellStyle(bigTitle);
//加粗居中
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
HSSFCell cell = null;
//查询条件 加粗
HSSFCellStyle conditionStyle = workbook.createCellStyle();
HSSFFont conditionFont = workbook.createFont();
conditionFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
conditionStyle.setFont(font);
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, title.size() - 1));
HSSFRow conditionR = sheet.createRow(1);
cell = conditionR.createCell(0);
cell.setCellStyle(conditionStyle);
cell.setCellValue(condition);
int j = 0;
//小标题
HSSFRow titleRow = sheet.createRow(2);
for (String str : title)
{
cell = titleRow.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(titleStyle);
cell.setCellValue(str);
j++;
}
//水平居中
HSSFCellStyle center = workbook.createCellStyle();
center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int i = 0;
for (FXOrderRecord order : rows)
{
HSSFRow row = sheet.createRow(i + 3);
//序号
cell = row.createCell(0);
cell.setCellStyle(center);
cell.setCellValue(i + 1);
//企业名称
cell = row.createCell(1);
cell.setCellStyle(center);
cell.setCellValue(order.getFxEnterprise().getName());
//订单编号
cell = row.createCell(2);
cell.setCellStyle(center);
cell.setCellValue(order.getDownstreamOrderNo());
i++;
}
return workbook;
}
}
//创建一个ExcelView视图类
import java.io.OutputStream;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
public class ExcelView extends AbstractExcelView{
@Override
public void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
String fileName=model.get("title")+".xls";
fileName=new String(fileName.getBytes("gb2312"), "iso8859-1");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
}