Java export Excel to achieve multi-header export

Posted May 28, 20204 min read

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("Please select data!", 0, 0, true);

return;

}

if(my \ _anctionParams == null || my \ _anctionParams == undefined) {

ip.warnJumpMsg("Please configure the parameters on the button parameters in the menu resources!", 0, 0, true)

return;

}

if(my \ _anctionParams.vtCode == "" || my \ _anctionParams.vtCode == undefined) {

ip.warnJumpMsg("Please configure the credential type vtCode parameter on the button parameter in the menu resource!", 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"); //Set the date format

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);
Forex Broker News http://www.kaifx.cn/lists/news/

//Analytical data

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 = "Account reconciliation";

} else {

code = "Reconciliation does not match";

}

}

newCell1.setCellValue(code);

newCell1.setCellStyle(cellStyleWB);

}

}

}

}

workBook.write(fos);

fls.close();

fos.flush();

fos.close();

}

private static CellStyle setCellStyle(CellStyle cellStyle) {

cellStyle.setWrapText(true); //Auto word wrap

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()); //Non-error messages are not marked in red

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;

}