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 block
e.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();
}
}
}
}