java导出excel树结构工具类

package com.jmw.utils.contract; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFCellUtil; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.hssf.util.Region; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; @SuppressWarnings("unchecked") public class AmountExcelUtils { /** * * @param list 数据集合 * @param dims 格式为{key:中文列名,value:表中的列字段，format:格式} * @param fileName 文件名称 * @param acctDate 日期 * @param response */ public static void toExcel(List list,List<Map> dims,String fileName,String acctDate,HttpServletResponse response){ HttpServletResponse re=response; re.setCharacterEncoding("UTF-8"); re.setContentType("application/vnd.ms-excel"); String extName=".xls"; String contentDisposition=null; try { contentDisposition = "attachment; filename=\"" + new String((fileName).getBytes(), "ISO8859-1") + extName+"\""; } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } re.setHeader("Content-Disposition", contentDisposition); try { OutputStream out=re.getOutputStream(); //ExportUtils.toExcel(out, list); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(getInputStream(list,dims,fileName,acctDate)); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff))) { bos.write(buff, 0, bytesRead); bos.flush(); } //bos.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); if (out != null) out.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * * @param list 数据集合 * @param dims 格式为{key:中文列名,value:表中的列字段，format:格式} * @param fileName 文件名称 * @param acctDate 日期 * @param response * @param header 处理合并表头的列 */ /* public static void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) { Row row=null;Cell cell=null; int firstRow=region.getRowFrom(),lastRow=region.getRowTo(),firstCol=region.getColumnFrom(),lastCol=region.getColumnTo(); while(firstRow<=lastRow){ row=sheet.getRow(firstRow++); while(firstCol<=lastCol){ cell=row.getCell(firstCol++); if(cell!=null){ cell.setCellStyle(cs); } } } }*/ private static void setRegionStyle(HSSFSheet sheet, Region region , HSSFCellStyle cs) { int toprowNum = region.getRowFrom(); for (int i = region.getRowFrom(); i <= region.getRowTo(); i ++) { HSSFRow row = HSSFCellUtil.getRow(i, sheet); for (int j = region.getColumnFrom(); j <= region.getColumnTo(); j++) { HSSFCell cell = HSSFCellUtil.getCell(row, (short)j); cell.setCellStyle(cs); } } } public static InputStream getInputStream(List<Map> datas,List<Map> dims,String fileName,String acctDate) { HSSFWorkbook wb = createWorkBook(datas,dims,fileName,acctDate); // 捕获内存缓冲区的数据，转换成字节数组 ByteArrayOutputStream os = new ByteArrayOutputStream(); InputStream is = null; try { wb.write(os); // 获取内存缓冲中的数据 byte[] content = os.toByteArray(); is = new ByteArrayInputStream(content); os.close(); } catch (IOException e) { e.printStackTrace(); } return is; } /** * * @param wb 设置单元格样式 * @return */ private static HSSFCellStyle getGreyCellStyle(HSSFWorkbook wb){ HSSFCellStyle cellStyle= wb.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.FINE_DOTS ); cellStyle.setFillForegroundColor(new HSSFColor.GREY_50_PERCENT().getIndex()); cellStyle.setFillBackgroundColor(new HSSFColor.GREY_50_PERCENT().getIndex()); cellStyle.setTopBorderColor(HSSFColor.BLACK.index); /*cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直 */ cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中 return cellStyle; } /** * * @param wb * @param isFont isFont=false设置边框加粗,为true设置字体为微软雅黑并加粗,设置边框加粗。 * @param format format为空则不格式化； * @return */ private static HSSFCellStyle getCellStyle(HSSFWorkbook wb,boolean isFont,String format){ HSSFCellStyle cellStyle= wb.createCellStyle(); if(isFont){ HSSFFont fontStyle=wb.createFont(); fontStyle.setFontName("微软雅黑"); fontStyle.setFontHeightInPoints((short)10); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); } //cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体 //cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色． //cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); //cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //cellStyle.setRightBorderColor(HSSFColor.BLACK.index); //cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //cellStyle.setTopBorderColor(HSSFColor.BLACK.index); /*cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直 cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中 cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中 */ if(format!=null){ if(format.indexOf("%")>-1){ cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%")); }else if(format.indexOf(".")>-1){ cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); }else{ cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); } } return cellStyle; } private static HSSFWorkbook createWorkBook(List<Map> datas,List<Map> dims,String fileName,String acctDate) { HSSFWorkbook wb = new HSSFWorkbook(); try{ HSSFSheet sheet = wb.createSheet(fileName); int j = 0, k = 0; HSSFRow row = null; HSSFCell cell = null; //设置样式 HSSFCellStyle cellStyleBlack = getCellStyle(wb,true,null); HSSFCellStyle cellStyle = getCellStyle(wb,false,null); HSSFCellStyle cellGreyStyle = getGreyCellStyle(wb); //根据dims获取列 遍历datas 获取需要合并单元格的区间 从第二行开始 int col=0; //第一行生成表头 row = sheet.createRow(0); HSSFCell cellStart = row.createCell((short) 0); HSSFRichTextString ss = new HSSFRichTextString(fileName+" "+"数据日期:"+acctDate); cellStart.setCellValue(ss); Regi