HSSFWorkbook导出excel

package com.hx.gpsplatform.common.utils;


import java.util.HashMap;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExportExcelUtils {
// 翻译的表头容器
private static Map<String,String[]> headMap = new HashMap<String,String[]>();
// value 名称容器
private static Map<String,String[]> valueMap = new HashMap<String,String[]>();
//
private static String[] getHeaderMap(String excelType){
if(headMap == null){
headMap = new HashMap<String,String[]>();
}
if(headMap.size() == 0){
headMap.put(ExportExcelUtils.TITLE_STOCK_IMEI, new String[]{"IMEI","设备型号","所属分组","在线状态","最后上传时间","库存状态","激活时间","在库时间","在库时长(天)"});
headMap.put(ExportExcelUtils.TITLE_STOCK_SIM, new String[]{"客户名","车牌","ICCID","SIM卡号码","所属分组","SIM卡供应商","SIM卡状态","流量","库存状态","激活时间","在库时间","在库时长(天)"});
headMap.put(ExportExcelUtils.TITLE_SIM, new String[]{ "SIM ID","ICCID","供应商ID(如：4-国脉,5-麦谷)","库存状态(1-使用中,2-在库,3-流失,4-坏卡)"});
headMap.put(ExportExcelUtils.TITLE_IMEI, new String[]{ "IMEI ID", "供应商id(如：1-移动,2-联通,3-电信)", "类型(1-无线,0-有线)", "型号", "描述", "库存状态(1-使用中,2-在库,3-流失,4-坏机)","安装日期","服务日期"});
headMap.put(ExportExcelUtils.TITLE_RELEVANCE, new String[]{ "客户名", "车牌", "所属用户", "IMEI", "设备类型", "激活日期", "SIM卡号", "流量", "卡激活时间", "在线状态", "贷款时间", "贷款周期","最后接收数据时间" });
headMap.put(ExportExcelUtils.TITLE_ONLINE, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式","车辆状态", "速度", "定位时间", "接收状态", "在线时长", "当前位置","电量" });
headMap.put(ExportExcelUtils.TITLE_OFFLINE, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式", "车辆状态", "速度", "定位时间", "接收状态", "离线时长", "离线位置","电量" });
headMap.put(ExportExcelUtils.TITLE_TRAVEL, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型", "定位方式", "车辆状态", "速度", "定位时间", "接收时间", "行驶时长", "当前位置","电量" });
headMap.put(ExportExcelUtils.TITLE_FLAMOUT, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","熄火时间", "熄火时长", "当前位置","在线状态" });
headMap.put(ExportExcelUtils.TITLE_POWER, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","报警类型", "报警次数","当前状态","当前统计时间" });
headMap.put(ExportExcelUtils.TITLE_ALARMDETAIL, new String[]{ "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","报警类型", "报警时间","处理情况"});
headMap.put(ExportExcelUtils.TITLE_MILEAGEDAYORMON, new String[]{ "车牌", "日期","里程"});
headMap.put(ExportExcelUtils.TITLE_MILEAGEHANDORMON, new String[]{ "车牌", "开始日期","结束日期","里程"});
headMap.put(ExportExcelUtils.TITLE_IMPORTAREA, new String[]{ "客户姓名", "车牌号","所属用户","IMEI","设备类型","行政区名称","出区时间","出区地点","返回时间","返回地点","累计时间"});
headMap.put(ExportExcelUtils.TITLE_TWOEXPENSE, new String[]{ "客户姓名", "车牌号","所属用户","IMEI","设备类型","二押点名称","报警类型","报警时间","处警情况","报警地点","定位方式"});
headMap.put(ExportExcelUtils.TITLE_OVERSPEED, new String[]{  "车主姓名", "车牌号", "所属用户", "IMEI", "设备类型","报警类型", "定位方式","报警时间","处警情况","报警地点"});
headMap.put(ExportExcelUtils.TITLE_TRACKPLAYBACK, new String[]{  "定位状态", "速度", "方向", "累积里程","状态信息", "位置","定位时间","接收时间"});
}
return headMap.get(excelType);
}
// 获取值容器
private static String[] getValueMap(String excelType){
if(valueMap == null){
valueMap = new HashMap<String,String[]>();
}
if(valueMap.size() == 0){
valueMap.put(ExportExcelUtils.TITLE_STOCK_IMEI, new String[]{"imeiId","versionType","organizeDesc","onlineStatus","uploadDate","stockStatus","activateDate","instockDate","instockTime"});
valueMap.put(ExportExcelUtils.TITLE_STOCK_SIM, new String[]{"custName","carNumber","ICCID","simId","organizeDesc","supplierName","simStatus","flow","simStockStatus","activateDate","simInstockDate","instockTime"});
valueMap.put(ExportExcelUtils.TITLE_RELEVANCE, new String[]{ "custName", "carNumber", "createdBy", "imeiId", "iType", "iActivateDate", "simId", "flow", "sActivateDate", "onlineStatus", "loanDate", "contractTerm","uploadDate" });
valueMap.put(ExportExcelUtils.TITLE_ONLINE, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "locationType","currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation","electricity" });
valueMap.put(ExportExcelUtils.TITLE_OFFLINE, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "locationType", "currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation" ,"electricity"});
valueMap.put(ExportExcelUtils.TITLE_TRAVEL, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "locationType", "currentStatus","speed", "locationTime", "recepitTime", "totalTime","currentLocation" ,"electricity"});
valueMap.put(ExportExcelUtils.TITLE_FLAMOUT, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "createdTime", "totalTime","currentLocation" ,"currentStatus"});
valueMap.put(ExportExcelUtils.TITLE_POWER, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "alarmType","alarmNum","onlineState" ,"alarmTime"});
valueMap.put(ExportExcelUtils.TITLE_ALARMDETAIL, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "alarmType","warningTime","alarmProcesse"});
valueMap.put(ExportExcelUtils.TITLE_MILEAGEDAYORMON, new String[]{ "licensePlate", "countDaytime","mileage"});
valueMap.put(ExportExcelUtils.TITLE_MILEAGEHANDORMON, new String[]{ "licensePlate", "starTtime","endedTime","mileage"});
valueMap.put(ExportExcelUtils.TITLE_IMPORTAREA, new String[]{ "custName", "carNumber","organizeDesc","imei","terminalType","fenceName","exitTime","exitLocation","returnTime","returnLocation","cumulativeTime"});
valueMap.put(ExportExcelUtils.TITLE_TWOEXPENSE, new String[]{ "custName", "carNumber","organizeDesc","imei","terminalType","fenceName","alarmType","alarmTime","alarmProcesse","alarmLocation","locationType"});
valueMap.put(ExportExcelUtils.TITLE_OVERSPEED, new String[]{ "custName", "carNumber","organizeDesc", "imei", "terminalType", "alarmType","locationType","alarmTime","alarmProcesse" ,"alarmLocation"});
valueMap.put(ExportExcelUtils.TITLE_TRACKPLAYBACK, new String[]{  "state", "speed",  "direction", "mileage","stateInfo", "location","receiveDate","createDate"});
}
return valueMap.get(excelType);
}

// IMEI的HEAD(中文转英文)
public static final String[] TITLE_IMEI_TO_ENGLISH =  new String[]{ "imeiId", "supplierId", "iType", "versionType", "description", "stockStatus","installDate","serviceDate"};
// SIM的HEAD(中文转英文)
public static final String[] TITLE_SIM_TO_ENGLISH = new String[]{ "simId","ICCID","simSupplierId","simStockStatus"};

// IMEI的HEAD
public static final String TITLE_STOCK_IMEI = "TITLE_STOCK_IMEI";
// IMEI的HEAD
public static final String TITLE_STOCK_SIM = "TITLE_STOCK_SIM";
// SIM的HEAD
public static final String TITLE_SIM = "TITLE_SIM";
// IMEI的HEAD
public static final String TITLE_IMEI = "TITLE_IMEI";
// Relevance
public static final String TITLE_RELEVANCE = "TITLE_RELEVANCE";
//车辆在线报表
public static final String TITLE_ONLINE = "TITLE_ONLINE";
//车辆离线报表
    public static final String TITLE_OFFLINE = "TITLE_OFFLINE";
    //车辆行驶报表
    public static final String TITLE_TRAVEL = "TITLE_TRAVEL";
    //车辆行驶报表
    public static final String TITLE_FLAMOUT = "TITLE_FLAMOUT";
    //车辆断电报表
    public static final String TITLE_POWER = "TITLE_POWER";
    //超速报警
    public static final String TITLE_OVERSPEED = "TITLE_OVERSPEED";
    //车辆断电报表
    public static final String TITLE_ALARMDETAIL = "TITLE_ALARMDETAIL";
    //里程报表按日或按月统计
    public static final String TITLE_MILEAGEDAYORMON = "TITLE_MILEAGEDAYORMON";
    //里程报表按日或按月统计
    public static final String TITLE_IMPORTAREA = "TITLE_IMPORTAREA";
    //二押点报表统计
    public static final String TITLE_TWOEXPENSE = "TITLE_TWOEXPENSE";
    //轨迹回放
    public static final String TITLE_TRACKPLAYBACK = "TITLE_TRACKPLAYBACK";
    public static final String TITLE_MILEAGEHANDORMON = "TITLE_MILEAGEHANDORMON";
@SuppressWarnings("rawtypes")
public static HSSFWorkbook getHSSFWorkbook(String excelType, List<Map> values) {
// 创建一个webbook和sheet，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("EXPORT_DATA");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = null;
// 创建header
String[] headerMap = ExportExcelUtils.getHeaderMap(excelType);
for (int i = 0; i < headerMap.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerMap[i]);
cell.setCellStyle(style);
}
// 创建内容
String[] valueMap = ExportExcelUtils.getValueMap(excelType);
if(values!=null){
for (int i = 0; i < values.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < valueMap.length; j++) {
if (values.get(i).get(valueMap[j]) != null) {
row.createCell(j).setCellValue(values.get(i).get(valueMap[j]) + "");
} else {
row.createCell(j).setCellValue("");
}
}
}
}
return wb;
}

}

控制类调用

@RequestMapping("/exportflamoutCurrent")
public String exportflamoutCurrent(ModelMap model,Pageable pageable,VehicleStatusDto relevanceDTO,HttpServletRequest request, HttpServletResponse response){
getOrganizeMap(model);
if (relevanceDTO.getSearchCondition() != null && "2".equals(relevanceDTO.getSearchCondition())) {
if (relevanceDTO.getSearchContent() != null) {
String[] temp = relevanceDTO.getSearchContent().split(" ");
if (temp.length == 2) {
relevanceDTO.setCustName(temp[0]);
relevanceDTO.setCarNumber(temp[1]);
}
}
}
HSSFWorkbook wb = ExportExcelUtils.getHSSFWorkbook(ExportExcelUtils.TITLE_FLAMOUT, reportService.findRunningStateA(relevanceDTO,pageable,exportCunrrent));
// 调用公共方法直接返回文件流
GPSResponseUtils.setResponseHeader(wb, "车辆行驶状态报表_"+System.currentTimeMillis()+".xls", response);
        return "";
}

页面

$("#listForm").attr("action","${base}/report/exportflamoutCurrent");
$("#listForm").submit();