Java导出Excel实现多表头导出

doExport = function (my_anctionParams) {

var viewId = pageParts.voucherViewId;

var gridData = $(‘#’ + viewId.substring(1, 37) + ”).parent()[0][‘u-meta’].grid;

var dataArr = gridData.getAllRows();

var pageParams = getPageParams();

var selectData = pageParts.billGridModel.gridData.getSimpleData({

type: ‘select’,

fields: [‘agency_code’]

});

if(dataArr.length === 0) {

ip.warnJumpMsg(“ 请选择数据!”, 0, 0, true);

return;

}

if (my_anctionParams == null || my_anctionParams == undefined) {

ip.warnJumpMsg(“ 请在菜单资源中的按钮参数上配置参数!”, 0, 0, true)

return;

}

if (my_anctionParams.vtCode == “” || my_anctionParams.vtCode == undefined) {

ip.warnJumpMsg(“ 请在菜单资源中的按钮参数上配置凭证类型 vtCode 参数!”, 0, 0, true)

return;

}

pageParams[“dataArr”] = JSON.stringify(dataArr);

pageParams[“vt_code”] = my_anctionParams.vtCode;

pageParams[“agency_code”] = selectData[0].agency_code;

$.ajax({

url: “/df/ebalance/jxOperation/exportExcel.do”,

type: “POST”,

dataType: “json”,

async: false,

data: pageParams,

success: function (data) {

}

});

};

@RequestMapping(value = “/exportExcel.do”)

@ResponseBody

public void exportExcel(HttpServletRequest request) throws Exception {

String dataArr = request.getParameter(“dataArr”);

String vt_code = request.getParameter(“vt_code”);

String agency_code = request.getParameter(“agency_code”);

List dataArrList = JSONArray.fromObject(dataArr.replaceAll(“:null,”, “:\”\”,”));

String fileNamew = “”;

switch (vt_code) {

case “5551” :

fileNamew = “5551_dw.xlsx”;

break;

case “5552” :

fileNamew = “5552_dw.xlsx”;

break;

}

String srcFilePath = getClass().getResource(“/template/” + fileNamew).getPath();

SimpleDateFormat df = new SimpleDateFormat(“yyyyMMdd”);// 设置日期格式

String date = df.format(new Date());

String fileName = agency_code + “_” + vt_code + “_” + date + “.xlsx”;

FileSystemView fsv = FileSystemView.getFileSystemView();

File home = fsv.getHomeDirectory();

String path = home.getPath();

String desFilePath = path + “/” + fileName;

EPayUtil.exportExcel(srcFilePath, desFilePath, dataArrList, vt_code);

}

public static void exportExcel(String srcFilePath, String desFilePath, List dataArrList, String vt_code) throws Exception {

FileInputStream fls = new FileInputStream(srcFilePath);

XSSFWorkbook workBook = new XSSFWorkbook(fls);

FileOutputStream fos = new FileOutputStream(desFilePath);

XSSFSheet sheet = workBook.getSheetAt(0);

int currentLastRowIndex = sheet.getLastRowNum();

int newRowIndex = currentLastRowIndex + 1;

XSSFDataFormat format = workBook.createDataFormat();

CellStyle cellStyleWB = workBook.createCellStyle();

cellStyleWB.setFillPattern(CellStyle.SOLID_FOREGROUND);

cellStyleWB.setDataFormat(format.getFormat(“@”));

cellStyleWB = setCellStyle(cellStyleWB);

System.out.println(dataArrList);
外汇经纪商动态 http://www.kaifx.cn/lists/news/

// 解析数据

HashMap totalData = new HashMap();

for (int i = 0; i < dataArrList.size(); i++) {

HashMap data = new HashMap();

JSONObject jsonObject = (JSONObject) dataArrList.get(i);

Iterator it = jsonObject.keys();

while (it.hasNext()) {

String key = String.valueOf(it.next());

String value = (String) jsonObject.get(key);

data.put(key, value);

}

totalData.put(i,data);

}

EPayUtil ePayUtil = new EPayUtil();

List list = ePayUtil.addList(vt_code);

for (int j = 0; j < totalData.size(); j++) {

int cellIndex = 0;

XSSFRow newRow = sheet.createRow(newRowIndex++);

Map map = totalData.get(j);

List mapKeyList = new ArrayList(map.keySet());

for(String ss : list){

for (String key : mapKeyList) {

if(ss.equals(key)){

XSSFCell newCell1 = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);

String code = (String) map.get(key);

if (“check_result”.equals(key)) {

if (“0”.equals(code)) {

code = “ 对账相符 ”;

} else {

code = “ 对账不符 ”;

}

}

newCell1.setCellValue(code);

newCell1.setCellStyle(cellStyleWB);

}

}

}

}

workBook.write(fos);

fls.close();

fos.flush();

fos.close();

}

private static CellStyle setCellStyle(CellStyle cellStyle){

cellStyle.setWrapText(true);// 自动换行

cellStyle.setBorderRight(CellStyle.BORDER_THIN);

cellStyle.setBorderTop(CellStyle.BORDER_THIN);

cellStyle.setBorderLeft(CellStyle.BORDER_THIN);

cellStyle.setBorderBottom(CellStyle.BORDER_THIN);

cellStyle.setAlignment(CellStyle.ALIGN_LEFT);

cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 非错误信息不标红

cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

return cellStyle;

}

public List addList(String vt_code) {

List list = new ArrayList();

switch (vt_code) {

case “5551” :

list.add(“fundtype_code”); list.add(“fundtype_name”); list.add(“expfunc_code”); list.add(“expfunc_name”); list.add(“depexpeco_code”);

list.add(“depexpeco_name”); list.add(“biamt1”); list.add(“biamt2”);list.add(“biamt3”); list.add(“planamt1”); list.add(“planamt2”);

list.add(“planamt3”); list.add(“planamt4”); list.add(“planamt5”); list.add(“planamt6”); list.add(“planamt7”);list.add(“planamt8”);

list.add(“planamt9”); list.add(“check_result”); list.add(“check_reason”); list.add(“check_remark”);

break;

case “5552” :

list.add(“fundtype_code”); list.add(“fundtype_name”); list.add(“expfunc_code”); list.add(“expfunc_name”); list.add(“depexpeco_code”);

list.add(“depexpeco_name”); list.add(“sum_pay_amt”); list.add(“dir_sum_amt”); list.add(“salary_pay_amt”); list.add(“gb_pay_amt”);

list.add(“far_pay_amt”); list.add(“other_pay_amt”); list.add(“sq_pay_amt”); list.add(“check_result”); list.add(“check_reason”);

list.add(“check_remark”);

}

return list;

}