excel的生成与表头的合并的一个例子

来源：互联网发布：搜款网广州网络批发1 编辑：程序博客网时间：2024/06/01 13:39
package test1;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.Map;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class TestExcel111 {public static void main(String[] args){try {expExcelAll();} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}private static void expExcelAll() throws FileNotFoundException {// 声明一个工作薄            XSSFWorkbook workbook = new XSSFWorkbook();            // 生成一个表格            XSSFSheet sheet = workbook.createSheet("详细数据");          sheet.setDefaultColumnWidth(20);          XSSFCellStyle style = workbook.createCellStyle();          style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);          style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          int num=0;            /*单元格,createCell(i),这里的i代表单元格是第几列，        CellRangeAddress(firstRow,lastRow,firstCol,lastCol)里的参数                       分别表示需要合并的单元格起始行，起始列  */        /*表头创建与合并开始*/        XSSFRow firstRow = sheet.createRow(num);          CreateExcel.getValue(firstRow.createCell(0),"放款日期",style);          sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));          CreateExcel.getValue(firstRow.createCell(1),"合同信息",style);          sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 15));          CreateExcel.getValue(firstRow.createCell(16),"本次付款信息",style);          sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 31));          XSSFRow secondRow = sheet.createRow(++num);          CreateExcel.getValue(secondRow.createCell(16),"其他收费",style);          sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 30));          CreateExcel.getValue(secondRow.createCell(31),"费用合计",style);          sheet.addMergedRegion(new CellRangeAddress(1, 2, 31, 31));                    XSSFRow thirdRow = sheet.createRow(++num);          CreateExcel.getValue(thirdRow.createCell(1),"借款人",style);          CreateExcel.getValue(thirdRow.createCell(2),"合同号",style);          CreateExcel.getValue(thirdRow.createCell(3),"分公司",style);          CreateExcel.getValue(thirdRow.createCell(4),"是否直销",style);          CreateExcel.getValue(thirdRow.createCell(5),"合作机构",style);          CreateExcel.getValue(thirdRow.createCell(6),"贷款类型",style);           CreateExcel.getValue(thirdRow.createCell(7),"产品类型",style);          CreateExcel.getValue(thirdRow.createCell(8),"还款方式",style);          CreateExcel.getValue(thirdRow.createCell(9),"贷款期数",style);          CreateExcel.getValue(thirdRow.createCell(10),"客户主任",style);          CreateExcel.getValue(thirdRow.createCell(11),"客户经理",style);          CreateExcel.getValue(thirdRow.createCell(12),"计息本金",style);          CreateExcel.getValue(thirdRow.createCell(13),"付款类型",style);          CreateExcel.getValue(thirdRow.createCell(14),"付款金额",style);          CreateExcel.getValue(thirdRow.createCell(15),"履约保证金",style);          CreateExcel.getValue(thirdRow.createCell(16),"考察费",style);          CreateExcel.getValue(thirdRow.createCell(17),"GPS费",style);          CreateExcel.getValue(thirdRow.createCell(18),"抵押登记费",style);          CreateExcel.getValue(thirdRow.createCell(19),"停车费",style);          CreateExcel.getValue(thirdRow.createCell(20),"盗抢险",style);          CreateExcel.getValue(thirdRow.createCell(21),"刑侦费",style);          CreateExcel.getValue(thirdRow.createCell(22),"评估费",style);          CreateExcel.getValue(thirdRow.createCell(23),"律师签证费",style);          CreateExcel.getValue(thirdRow.createCell(24),"加急费",style);          CreateExcel.getValue(thirdRow.createCell(25),"风险金",style);          CreateExcel.getValue(thirdRow.createCell(26),"抵押登记",style);          CreateExcel.getValue(thirdRow.createCell(27),"手续费",style);          CreateExcel.getValue(thirdRow.createCell(28),"征信费",style);          CreateExcel.getValue(thirdRow.createCell(29),"快递费",style);          CreateExcel.getValue(thirdRow.createCell(30),"其他",style);         /*表头创建与合并结束*/                //单元格里面的值对应的实体bean字段          String[] keyBean = {"confirmDate","custName","contractNo","siteName","isDirect","cooperation","loanType","productTypeName","paymentTypeName",                  "totalPhases","customerDirector","customerManager","auditamt","payType","payAmt","lybzjFees","kcFeeS",                  "gpsFees","dydjFees","tcFees","dqxFees","xzFees","pgFees","lsjzFees","jjFees","kdFees","gzFees","sxFees","zxsxFees","shouldglf","qtdsFees","total"};                ArrayList<Map> list=new ArrayList<Map>();        Map<String,Object> map=new HashMap<String,Object>();        map.put("name", "张三");        map.put("sex", "男");        map.put("age", 18);        list.add(map);        Map<String,Object> map1=new HashMap<String,Object>();        map1.put("name", "晓晓");        map1.put("sex", "女");        map1.put("age", 19);        list.add(map1);                XSSFRow row = sheet.createRow(++num);        for(int j=0;j<keyBean.length;j++){        CreateExcel.getValue(row.createCell(j), keyBean[j],style);        }                int rowNum=3;        for(int i=0,j=list.size();i<j;i++,rowNum++){         XSSFRow rows = sheet.createRow(++num);         CreateExcel.getValue(rows.createCell(0),list.get(i).get("name"),style);        CreateExcel.getValue(rows.createCell(1),list.get(i).get("sex"),style);        CreateExcel.getValue(rows.createCell(2),list.get(i).get("age"),style);        }        String fileName="E:"+File.separator+"xie";        //输出Excel文件          FileOutputStream outputStream=new FileOutputStream(fileName + File.separator +"workbook.xlsx");try  {  workbook.write(outputStream);      outputStream.flush();      outputStream.close();  }  catch (IOException e)  {      e.printStackTrace();  }  finally  {      try      {          outputStream.close();      }      catch (IOException e)      {          e.printStackTrace();      }  } }}
阅读全文
0 0