java使用POI导出 Excel工具类

package com.yym.service.admin.insurance; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.net.HttpURLConnection; import java.net.URL; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Random; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import com.yym.dtoadmin.insurance.ExcelUtilsDTO; /** * 导出公共类 * @author Lin * @2018/6/29 */ public class ExcelUtil { /** * Excel导出保险项目信息 * @throws Exception * */ public void createExcel(HttpServletResponse response,ExcelUtilsDTO dto) throws Exception { List<Map<String, Object>> list=dto.getData(); //导出文件名 Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); String str = sdf.format(date); Random random = new Random(); String result=""; for (int j=0;j<6;j++) { result+=random.nextInt(10); } OutputStream out=response.getOutputStream(); response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename=" + new String((dto.getExcelTitle()+"信息表_"+str+result).getBytes(),"iso-8859-1") + ".xls"); //创建Excel //创建HSSFWorkbook对象(excel的文档对象) HSSFWorkbook wb = new HSSFWorkbook(); //建立新的sheet对象（excel的表单） Sheet sheets=wb.createSheet(dto.getExcelTitle()+"信息"); //在sheet里创建第一行，参数为数据信息标题 Row rowTotle=sheets.createRow(0); rowTotle.setHeight((short)(36*20)); HSSFCellStyle styleTitle = wb.createCellStyle(); //水平居中 styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //垂直居中 styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置背景色 styleTitle.setFillForegroundColor(IndexedColors.TEAL.getIndex()); styleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND); //设置字体 HSSFFont font = wb.createFont(); font.setFontName("楷体"); font.setColor(HSSFColor.WHITE.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 font.setFontHeightInPoints((short) 12); styleTitle.setFont(font);//选择需要用到的字体格式 for(int l=0;l<dto.getCellTitle().length;l++) { Cell cell=rowTotle.createCell(l); cell.setCellValue(dto.getCellTitle()[l]); cell.setCellStyle(styleTitle); } //如果导出信息为空 if(dto.getData().size()<1) { //创建没有数据的Excel样式 //创建信息为空显示样式 HSSFCellStyle styleNoData = wb.createCellStyle(); //水平居中 styleNoData.setAlignment(HSSFCellStyle.ALIGN_CENTER); //垂直居中 styleNoData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置背景色 styleNoData.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); styleNoData.setFillPattern(CellStyle.SOLID_FOREGROUND); //设置字体 HSSFFont fontNoData = wb.createFont(); fontNoData.setFontName("隶书"); fontNoData.setColor(HSSFColor.RED.index); fontNoData.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示 fontNoData.setFontHeightInPoints((short) 12); styleNoData.setFont(fontNoData);//选择需要用到的字体格式 Row rowBody=sheets.createRow(1); CellRangeAddress notData = new CellRangeAddress(1, 1, 0, dto.getCellTitle().length-1);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号 sheets.addMergedRegion(notData); Cell cellNoData=rowBody.createCell(0); cellNoData.setCellValue("数据为空"); cellNoData.setCellStyle(styleNoData); }else { int i=0;//一定要放在循环外,只能声明一次 HSSFPatriarch patriarch = (HSSFPatriarch) sheets.createDrawingPatriarch(); for(Map<String, Object> dateList:list) { Row rowBody=sheets.createRow(i+1); for(int j=0;j<dateList.size();j++){ if(dateList.get(Integer.valueOf(j).toString())==null){ rowBody.createCell(j).setCellValue(""); }else if(dateList.get(Integer.valueOf(j).toString()).toString().length()>10&&dateList.get(Integer.valueOf(j).toString()).toString().substring(dateList.get(Integer.valueOf(j).toString()).toString().length()-4, dateList.get(Integer.valueOf(j).toString()).toString().length()).equals(".jpg")) { String[] pic=dateList.get(Integer.valueOf(j).toString()).toString().split(","); for(String pi:pic) { URL url = new URL(pi); //打开链接 HttpURLConnection conn = (HttpURLConnection)url.openConnection(); //设置请求方式为"GET" conn.setRequestMethod("GET"); //超时响应时间为5秒 conn.setConnectTimeout(5 * 1000); //通过输入流获取图片数据 InputStream inStream = conn.getInputStream(); //得到图片的二进制数据，以二进制封装得到数据，具有通用性 byte[] data = readInputStream(inStream); //anchor主要用于设置图片的属性 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250,(short) j, i+1, (short) j, i+1); //Sets the anchor type （图片在单元格的位置） //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells. anchor.setAnchorType(0); patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG)); } }else{ rowBody.createCell(j).setCellValue(dateList.get(Integer.valueOf(j).toString()).toString()); } } i++; } } for(int l=0;l<dto.getCellTitle().length;l++) { sheets.autoSizeColumn(l, true); } //数据写入Excel wb.write(out); out.flush(); } private static byte[] readInputStream(InputStream inStream) throws Exception{ ByteArrayOutputStream outStream = new ByteArrayOutputStream(); //创建一个Buffer字符串 byte[] buffer = new byte[1024]; //每次读取的字符串长度，如果为-1，代表全部读取完毕 int len = 0; //使用一个输入流从buffer里把数据读取出来 while( (len=inStream.read(buffer)) != -1 ){ //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度 outStream.write(buffer, 0, len); } //关闭输入流 inStream.close(); //把outStream里的数据写入内存 return outStream.toByteArray(); } }