poi导出excel
/**
*
* <p>方法名称: exportUtil|描述: 导出指定格式的数据</p>
* @param projPlanList
* @return
*/
public String exportUtil(List projPlanList){
try{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
int columnum = 0;
// sheet.setColumnWidth((short)columnum++, (short)35.7*180);
sheet.setColumnWidth((short)columnum++, (short)35.7*260);
sheet.setColumnWidth((short)columnum++, (short)35.7*180);
sheet.setColumnWidth((short)columnum++, (short)35.7*90);
sheet.setColumnWidth((short)columnum++, (short)35.7*90);
sheet.setColumnWidth((short)columnum++, (short)35.7*90);
sheet.setColumnWidth((short)columnum++, (short)35.7*60);
sheet.setColumnWidth((short)columnum++, (short)35.7*60);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
sheet.setColumnWidth((short)columnum++, (short)35.7*120);
Map<String,HSSFCellStyle> styleMap = new HashMap<String,HSSFCellStyle>();
//标题样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
HSSFFont titleFont = workbook.createFont();
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
titleStyle.setFont(titleFont);
styleMap.put("titleStyle", titleStyle);
//内容样式
HSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
styleMap.put("contentStyle", contentStyle);
HSSFRow row = null;
//报表头
row = sheet.createRow(0);
HSSFCell cell = row.createCell((short) 0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("项目综合信息统计表");
HSSFCellStyle titlebStyle = workbook.createCellStyle();
titlebStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
HSSFFont titlebFont = workbook.createFont();
titlebFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
titlebFont.setFontHeightInPoints((short) 12);
titlebStyle.setFont(titlebFont);
styleMap.put("titleStyle", titleStyle);
cell.setCellStyle(titlebStyle);
//报表时间
row = sheet.createRow(1);
cell = row.createCell((short) 0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("时间："+DateUtil.formatYYYY_MM_DD(new Date()));
GkjrProPlan gkjrProPlan=new GkjrProPlan("平台公司","出资平台", "项目名称","部门", "所属板块", "项目类型", "币种", "期限", "投资期结束日期", "最晚回收本息日期", "承诺出资额（万元）", "承诺未出资（万元）", "原币（万元）", "人民币（万元）", "原币（万元）", "人民币（万元）", "原币（万元）", "人民币（万元）", "出资计划时间", "出资计划金额（万元）", "回收计划时间", "本金回收（万元）", "利息收入（万元）", "已出资时间", "已出资金额（万元）", "已回收时间","已回收金额（万元）");
//生成标题
row = sheet.createRow(2);
makeExcelRow(gkjrProPlan, sheet, row, 0, 0, true, styleMap);
//内容起始行数
int rownum = 3;
//查询项目所关联的数据
int cellSize=0;
if(projPlanList!=null&&projPlanList.size()>0){
for(int i = 0; i < projPlanList.size(); i++){
Map projMap=(Map)projPlanList.get(i);
GkjrProPlan gl=createProPlanData(projMap);
Object size=projMap.get("size");
if(size!=null){
cellSize=(Integer)size;
}else{
cellSize=0;
}
makeExcelRow(gl, sheet, sheet.createRow(rownum), rownum, cellSize, false, styleMap);
rownum++;
}
}
// 将workbook中的数据输出
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
byte[] data = baos.toByteArray();
//queryTpye 可以queryType 查询类型 命名不同名字
String excelName = "项目综合信息统计表.xls";
if (data != null) {
HttpServletResponse response = WebUtil.getResponse();
response.reset();
response.setContentType("application/x-download;charset=UTF-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
OutputStream out = response.getOutputStream();
out.write(data);
out.flush();
out.close();
}
}catch (Exception e){
e.printStackTrace();
}
return "";
}
/**
*
* <p>方法名称: transParamType|描述:设置double的样式 </p>
* @param flag
* @param cell
* @param param
*/
public void transParamType(boolean flag,HSSFCell cell,String param){
if(flag){
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(transStringData(param));
}else{
if(StringUtil.isStringEmpty(param)||param== " "){
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(transStringData(param));
}else{
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(transZDoubleData(param));
}
}
}
public String transStringData(Object obj){
if(obj==null){
return "";
}else{
return obj.toString();
}
}
public String transDateData(Object obj){
if(obj==null||StringUtil.isStringEmpty(obj.toString())){
return "";
}else{
return DateUtil.formatYYYY_MM_DD((Timestamp)obj);
}
}
public double transZDoubleData(Object obj){
return Double.parseDouble(obj.toString());
}
/**
*
* <p>方法名称: transOutDoubleData|描述: 转换出资金额</p>
* @param obj
* @return
*/
public String transOutDoubleData(Object obj){
if(obj==null||StringUtil.isStringEmpty(obj.toString())){
return "";
}else{
double empAmount= Double.parseDouble(obj.toString());
BigDecimal amount=new BigDecimal(empAmount);
return "-"+amount.divide(new BigDecimal(10000)).toString();
}
}
public String transDoubleData(Object obj){
if(obj==null||StringUtil.isStringEmpty(obj.toString())){
return "";
}else{
double empAmount= Double.parseDouble(obj.toString());
BigDecimal amount=new BigDecimal(empAmount);
return amount.divide(new BigDecimal(10000)).toString();
}
}
public BigDecimal sumAmount(BigDecimal sumValue,Object obj){
if(obj==null||StringUtil.isStringEmpty(obj.toString())){
sumValue=sumValue;
}else{
double empAmount= Double.parseDouble(obj.toString());
sumValue=sumValue.add(new BigDecimal(empAmount));
}
return sumValue;
}
/**
*
* <p>方法名称: makeExcelRow|描述: </p>
* @param gkjrProPlan
* @param sheet
* @param row
* @param rownum
* @param size
* @param flag true:标题,flase:内容
* @param styleMap
*/
public void makeExcelRow(GkjrProPlan gkjrProPlan,HSSFSheet sheet,HSSFRow row,int rownum,int size,boolean flag,Map<String,HSSFCellStyle> styleMap){
HSSFCell cell = null;
int j = 0;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getPltName());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
// cell.setCellValue(gkjrProPlan.getPltNameAndCode());
// cell.setCellStyle(selectStyle(styleMap, flag));
// if(size!=0){
// sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
// }
// j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getProName());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getOrgName());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getPlateName());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getProjectTypeName());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getCurrency());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getTerm());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getInvestEndTime());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getLastRecycelTime());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getPermitAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getUnPermitAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getOriginalCurrency());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getCnyCommitAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getOriginalFlowCurrency());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getCnyCommitFlowAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getUnOriginalFlowCurrency());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getUnCnyCommitFlowAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
if(size!=0){
sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
}
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getFmFuOutFundTime());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getFmFuOutFund());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getFmFuInFundTime());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getFmFuInFundAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getFmFuInFundAmountInterest());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getFmFuOutFundFlowTime());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getFmFuOutFundFlowAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
cell.setCellValue(gkjrProPlan.getFmFuInFundFlowTime());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
cell = row.createCell((short) j);
transParamType(flag,cell,gkjrProPlan.getFmFuInFundFlowAmount());
cell.setCellStyle(selectStyle(styleMap, flag));
j++;
}
/**
*
* <p>方法名称: selectStyle|描述: 选则单元格样式</p>
* @param styleMap
* @param flag
* @return
*/
public HSSFCellStyle selectStyle(Map<String,HSSFCellStyle> styleMap, boolean flag) {
if(flag){
return styleMap.get("titleStyle");
}else {
return styleMap.get("contentStyle");
}
}
