excel导出功能
/*
* 导出 add by faby on 20180918
*/
public void export(){
HttpServletResponse response=ServletActionContext.getResponse();
HttpServletRequest request=ServletActionContext.getRequest();
ActionContext.initialize(ServletActionContext.getRequest(),ServletActionContext.getResponse());
formmail0001 = formService.getFormData("mail0001");
acLnAutoSend = new AcLnAutoSend();
getFormValue(formmail0001);
setObjValue(formmail0001, acLnAutoSend);
acLnAutoSendList = (List) acLnAutoSendBo.findAll(acLnAutoSend);
HSSFWorkbook wb = new HSSFWorkbook(); //创建一个excel文件
HSSFSheet sheet = wb.createSheet("sheet1"); //创建table工作薄
HSSFCellStyle style1 = wb.createCellStyle(); //新建单元格样式
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
//画出页面单元格表头
HSSFRow row;
HSSFCell cell;
//第一行------------------------------------------------------------------------
row = sheet.createRow(0);
//sheet.addMergedRegion(new CellRangeAddress(0,0,0,0));//控制合并单元格
cell = row.createCell(0);
cell.setCellValue("日期");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));
cell = row.createCell(1);
cell.setCellValue("存入款");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,2,2));
cell = row.createCell(2);
cell.setCellValue("提取款");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,3,3));
cell = row.createCell(3);
cell.setCellValue("累计净存入金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,4,4));
cell = row.createCell(4);
cell.setCellValue("备付金余额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,5,5));
cell = row.createCell(5);
cell.setCellValue("放款金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,0,13));
cell = row.createCell(6);
cell.setCellValue("还款本金金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,14,18));
cell = row.createCell(7);
cell.setCellValue("当日余额增加");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,19,23));
cell = row.createCell(8);
cell.setCellValue("累计余额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,24,28));
cell = row.createCell(9);
cell.setCellValue("还款利息");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,29,30));
cell = row.createCell(10);
cell.setCellValue("还款罚息");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(0,0,31,32));
cell = row.createCell(11);
cell.setCellValue("应收收益");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,1,6,8));
cell = row.createCell(12);
cell.setCellValue("实收收益");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,1,9,10));
cell = row.createCell(13);
cell.setCellValue("累计收益");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,11,11));
cell = row.createCell(14);
cell.setCellValue("累计客户数");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,12,12));
cell = row.createCell(15);
cell.setCellValue("客户平均金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,13,13));
cell = row.createCell(16);
cell.setCellValue("借据数");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,14,14));
cell = row.createCell(17);
cell.setCellValue("借据平均金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,15,15));
cell = row.createCell(18);
cell.setCellValue("加权平均利率");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,16,16));
cell = row.createCell(19);
cell.setCellValue("累计客户数");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,17,17));
cell = row.createCell(20);
cell.setCellValue("客户平均金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,18,18));
cell = row.createCell(21);
cell.setCellValue("借据数");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,19,19));
cell = row.createCell(22);
cell.setCellValue("借据平均金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,20,20));
cell = row.createCell(23);
cell.setCellValue("加权平均利率");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,21,21));
cell = row.createCell(24);
cell.setCellValue("当日已结清金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,22,22));
cell = row.createCell(25);
cell.setCellValue("累计结清金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,23,23));
cell = row.createCell(26);
cell.setCellValue("加权平均久期（天）");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,24,24));
cell = row.createCell(27);
cell.setCellValue("加权平均利率");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,25,25));
cell = row.createCell(28);
cell.setCellValue("累计收益");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,26,26));
cell = row.createCell(29);
cell.setCellValue("逾期金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,27,27));
cell = row.createCell(30);
cell.setCellValue("加权平均利率");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,28,28));
cell = row.createCell(31);
cell.setCellValue("不良借据金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,29,29));
cell = row.createCell(32);
cell.setCellValue("不良率");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,30,30));
cell = row.createCell(33);
cell.setCellValue("还款金额");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,31,31));
cell = row.createCell(34);
cell.setCellValue("累计客户数(结清)");
cell.setCellStyle(style1);
//sheet.addMergedRegion(new CellRangeAddress(1,2,32,32));
cell = row.createCell(35);
cell.setCellValue("累计笔数(结清)");
cell.setCellStyle(style1);
cell = row.createCell(36);
cell.setCellValue("新增笔数");
cell.setCellStyle(style1);
cell = row.createCell(37);
cell.setCellValue("新增笔数(结清)");
cell.setCellStyle(style1);
cell = row.createCell(38);
cell.setCellValue("新增笔数(未结清)");
cell.setCellStyle(style1);
cell = row.createCell(39);
cell.setCellValue("新增平均期数");
cell.setCellStyle(style1);
String [][] array = findAll2();
for(int i = 0; i < array.length; i++) {
row = sheet.createRow(i+1);//创建表格行
for(int j = 0; j < array[i].length; j++) {
cell = row.createCell(j);//根据表格行创建单元格
if(j==0) {//日期 和 xx率 不做千分位
cell.setCellValue(array[i][j]);
} else {
cell.setCellValue(fmtMicrometer(array[i][j]));
}
}
}
Map<String,String> sysPathMap =(Map<String,String>)MBaseCache.getCache().getBeanCache(CachecodeUtil.SYS_PATH_STR, CachecodeUtil.SYS_PATH);
//String tmppath = sysPathMap.get("040");
//String tmppath = "C:\Users\13671\Downloads";
//String tmppath="D:\home";
String fileName = "Daily";
OutputStream out =null;
try {
File file =new File(fileName);
if (!file .exists() && !file .isDirectory())
{
file.mkdir();
}
response.setContentType("application/xls;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");//导出的格式及默认本地浏览器
out = response.getOutputStream();
wb.write(out);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally{
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//遍历查询出来的数据
private String[][] findAll2() {
String[][] array = new String[acLnAutoSendList.size()][40];
for(int i=0;i<acLnAutoSendList.size();i++) {
array[i][0]= ((AcLnAutoSend) acLnAutoSendList.get(i)).getABatchDate()+"";
array[i][1] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getADepositMoney()+"";
array[i][2] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getAExtractMoney()+"";
array[i][3] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getASumNetDepositMoney()+"";
array[i][4] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getAReserveBalance()+"";
array[i][5] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getALoanAmt()+"";
array[i][6] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getARepayPrincipalAmt()+"";
array[i][7] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getATheDayBalanceAdd()+"";
array[i][8] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getASumBalance()+"";
array[i][9] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getARepayInterest()+"";
array[i][10] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getARepayPenaltyInterest()+"";
array[i][11] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getAIncomeReceivable()+"";
array[i][12] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getARealIncome()+"";
array[i][13] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getASumIncome()+"";
array[i][14] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getBCountCustNbr()+"";
array[i][15] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getBCustAvgAmt()+"";
array[i][16] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getBReceiptNbr()+"";
array[i][17] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getBReceiptAvgAmt()+"";
array[i][18] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getBAvggInterestRate()+"";
array[i][19] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getCCountCustNbr()+"";
array[i][20] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getCCustAvgAmt()+"";
array[i][21] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getCReceiptNbr()+"";
array[i][22] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getCReceiptAvgAmt()+"";
array[i][23] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getCAvggInterestRate()+"";
array[i][24] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getDTheDaySettledAmt()+"";
array[i][25] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getDSumSettledAmt()+"";
array[i][26] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getDAvggDuration()+"";
array[i][27] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getDAvggInterestRate()+"";
array[i][28] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getDSumIncome()+"";
array[i][29] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getEOverdueAmt()+"";
array[i][30] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getEOverdueRate()+"";
array[i][31] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getFBadAmt()+"";
array[i][32] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getFBadRate()+"";
array[i][33] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGRepayAmt()+"";
array[i][34] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGCountCustNbr()+"";
array[i][35] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGCount()+"";
array[i][36] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGNewCount()+"";
array[i][37] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGNewCountSettle()+"";
array[i][38] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGNewCountUnclear()+"";
array[i][39] = ((AcLnAutoSend) acLnAutoSendList.get(i)).getGNewAvgTerm()+"";
}
return array;
}
/**
* 格式化数字为千分位显示；
* @param 要格式化的数字；
* @return
*/
public static String fmtMicrometer(String text)
{
DecimalFormat df = null;
if(text.indexOf(".") > 0)
{
if(text.length() - text.indexOf(".")-1 == 0)
{
df = new DecimalFormat("###,##0.");
}else if(text.length() - text.indexOf(".")-1 == 1)
{
df = new DecimalFormat("###,##0.0");
}else
{
df = new DecimalFormat("###,##0.00");
}
}else
{
df = new DecimalFormat("###,##0");
}
double number = 0.0;
try {
number = Double.parseDouble(text);
} catch (Exception e) {
number = 0.0;
}
return df.format(number);
}