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');
}
});
}