JAVA导出Excel封装
1、数据bean
public class ExcelBean { private String name; private String sheetName; private ExcelTitle[] titles; private List<String[]> dataList; private boolean headBold = true; /** * 列宽 （像素） */ private int columnWidth = 200; private int rowHeight; public ExcelBean(String name, String sheetName, ExcelTitle[] titles){ this.name = name; this.sheetName = sheetName; this.titles = titles; this.dataList = new ArrayList<String[]>(); } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public ExcelTitle[] getTitles() { return titles; } public void setTitles(ExcelTitle[] titles) { this.titles = titles; } public List<String[]> getDataList() { return dataList; } public void setDataList(List<String[]> dataList) { this.dataList = dataList; } public boolean isHeadBold() { return headBold; } public void setHeadBold(boolean headBold) { this.headBold = headBold; } public int getColumnWidth() { return columnWidth; } public void setColumnWidth(int columnWidth) { this.columnWidth = columnWidth; } public int getRowHeight() { return rowHeight; } public void setRowHeight(int rowHeight) { this.rowHeight = rowHeight; } public void add(String[] data){ this.dataList.add(data); } }
/** * excel 块状区域 * @author yanglizhe * */ public class ExcelBox { private int x1; private int y1; private int x2; private int y2; public ExcelBox(int width, int height, int colWidth, int rowHeight, int padding){ double ratio = 1; int innerWidth = colWidth - 2 * padding; int innerHeight = rowHeight - 2 * padding; if((double) width / height > (double) innerWidth/ innerHeight){ if(width > innerWidth){ ratio = (double) innerWidth / width; } } else{ if(height > innerHeight){ ratio = (double) innerHeight / height; } } int boxWidth = (int)(width * ratio); int boxHeight = (int)(height * ratio); x1 = (colWidth - boxWidth) / 2; y1 = (rowHeight - boxHeight) / 2; x2 = x1 + boxWidth; y2 = y1+ boxHeight; } public int getX1() { return x1; } public void setX1(int x1) { this.x1 = x1; } public int getY1() { return y1; } public void setY1(int y1) { this.y1 = y1; } public int getX2() { return x2; } public void setX2(int x2) { this.x2 = x2; } public int getY2() { return y2; } public void setY2(int y2) { this.y2 = y2; } }
/** * excel 图片 * @author yanglizhe * */ public class ExcelImage { private int width; private int height; private byte[] byteArray; public ExcelImage(String imageUrl) throws IOException{ BufferedImage bufferedImage = ImageIO.read(new URL(imageUrl)); ByteArrayOutputStream byteArrayOutputStream =new ByteArrayOutputStream(); ImageIO.write(bufferedImage,"png", byteArrayOutputStream); width = bufferedImage.getWidth(); height = bufferedImage.getHeight(); byteArray = byteArrayOutputStream.toByteArray(); byteArrayOutputStream.close(); } public int getWidth() { return width; } public void setWidth(int width) { this.width = width; } public int getHeight() { return height; } public void setHeight(int height) { this.height = height; } public byte[] getByteArray() { return byteArray; } public void setByteArray(byte[] byteArray) { this.byteArray = byteArray; } }
/** * Excel Title * @author yanglizhe * */ public class ExcelTitle { private String value; /** * 列宽（像素） */ private int width; public static ExcelTitle generate(String value, int width){ ExcelTitle title = new ExcelTitle(); title.setValue(value); title.setWidth(width); return title; } public String getValue() { return value; } public void setValue(String value) { this.value = value; } public int getWidth() { return width; } public void setWidth(int width) { this.width = width; } }
2、工具类
/** * excel 工具类 * @author yanglizhe * */ public class ExcelUtils { public static void export( ExcelBean excelBean, HttpServletResponse response) throws Exception{ String filename = excelBean.getName(); filename = new String(filename.replaceAll("\\s|;", "").getBytes("gbk"), "ISO8859-1"); response.setContentType("application/octet-stream;charset=utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream outputStream = response.getOutputStream(); export(excelBean, outputStream); } @SuppressWarnings("resource") public static void export( ExcelBean excelBean, OutputStream outputStream) throws Exception{ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(excelBean.getSheetName()); HSSFRow row = sheet.createRow(0); //设置样式 HSSFCellStyle style = wb.createCellStyle(); if(excelBean.isHeadBold()){ HSSFFont headfont = wb.createFont(); headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFont(headfont); } HSSFCell cell; ExcelTitle[] titles = excelBean.getTitles(); for(int i=0; i < titles.length; i++){ ExcelTitle title = titles[i]; cell= row.createCell(i); cell.setCellValue(title.getValue()); cell.setCellStyle(style); int columnWidth = title.getWidth() > 0 ? title.getWidth() : excelBean.getColumnWidth(); sheet.setColumnWidth(i, getColWidth(columnWidth)); } int rowNumber = 1; int rowHeihgt = excelBean.getRowHeight(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); for(String[] data : excelBean.getDataList()){ row = sheet.createRow(rowNumber ++ ); if(rowHeihgt > 0){ row.setHeight((short) getRowHeight(rowHeihgt)); } else{ rowHeihgt = 18; } for(int j=0; j<data.length; j ++){ String value = data[j]; cell = row.createCell(j); if(isUrl(value)){ if(isImage(value)){ int columnWidth = titles[j].getWidth() > 0 ? titles[j].getWidth() : excelBean.getColumnWidth(); ExcelImage excelImage = new ExcelImage(value); ExcelBox excelBox = new ExcelBox(excelImage.getWidth(), excelImage.getHeight(), rowHeihgt, columnWidth, 10); HSSFClientAnchor anchor = new HSSFClientAnchor(); int cw = getColWidth(columnWidth); int rh = getRowHeight(rowHeihgt); short col = (short)(j); int rowNum = rowNumber-1; anchor.setDx1(getAnchorX(excelBox.getX1(), cw)); anchor.setDy1(getAnchorY(excelBox.getY1(), rh)); anchor.setDx2(getAnchorX(excelBox.getX2(), cw)); anchor.setDy2(getAnchorY(excelBox.getY2(), rh)); anchor.setCol1(col); anchor.setRow1(rowNum); anchor.setCol2(col); anchor.setRow2(rowNum); anchor.setAnchorType(0); patriarch.createPicture(anchor , wb.addPicture(excelImage.getByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); } else{ cell.setCellValue(value); cell.setCellFormula("HYPERLINK(\"" + value + "\",\"" + value + "\")"); HSSFCellStyle linkStyle = wb.createCellStyle(); HSSFFont cellFont= wb.createFont(); cellFont.setUnderline((byte) 1); cellFont.setColor(HSSFColor.BLUE.index); linkStyle.setFont(cellFont); linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(linkStyle); } } else{ cell.setCellValue(value); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(cellStyle); } } } wb.write(outputStream); outputStream.flush(); outputStream.close(); } /** * 获取图片x方向长度坐标转换 * @param px * @param colWidth * @return */ public static int getAnchorX(int px, int colWidth){ return (int) Math.round(( (double) 701 * 16000.0 / 301)*((double)1/colWidth)*px); } /** * 获取图片y方向长度坐标转换 * @param px * @param rowHeight * @return */ public static int getAnchorY(int px, int rowHeight){ return (int) Math.round(( (double) 144 * 8000 / 301)*((double)1/rowHeight)*px); } /** * 行高转换 * @param px * @return */ public static int getRowHeight( int px ){ return (int) Math.round(((double) 4480 / 300 ) * px); } /** * 列宽转换 * @param px * @return */ public static int getColWidth( int px ){ return (int) Math.round(((double) 10971 / 300 ) * px); } /** * 判断是否为链接地址 */ public static boolean isUrl(String string){ Pattern pattern = Pattern.compile("^((http|https):\\/\\/([\\w\\-]+\\.)+[\\w\\-]+(\\/[\\w\\u4e00-\\u9fa5\\-\\.\\/?\\@\\%\\!\\&=\\+\\~\\:\\#\\;\\,]*)?)", Pattern.CASE_INSENSITIVE ); return pattern.matcher(string).matches(); } /** * 判断是否为图片 */ public static boolean isImage(String string){ Pattern pattern = Pattern.compile("\\S+\\.(jpg|jpeg|png|gif|bmp)(\\?\\S+)?$", Pattern.CASE_INSENSITIVE ); return isUrl(string) && pattern.matcher(string).matches(); } }
3、Demo
List<PictureTopVo> list = statisticsService.topList(from, to, limit, orderBy); ExcelTitle[] titles = { ExcelTitle.generate("图片名称", 400), ExcelTitle.generate("缩略图", 100), ExcelTitle.generate("访问次数", 100), ExcelTitle.generate("回复次数", 100)}; SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); String fileName = format.format(from) + "至"+ format.format(to) + "TOP" + limit +"统计.xls"; ExcelBean excelBean = new ExcelBean(fileName, "统计", titles); for(PictureTopVo pictureTopVo : list){ excelBean.add(new String[]{pictureTopVo.getName(), pictureTopVo.getPath() + "?size=80", String.valueOf(pictureTopVo.getVisitCount()), String.valueOf(pictureTopVo.getFillCount())}); } excelBean.setRowHeight(100); ExcelUtils.export(excelBean, respone);
4、maven
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency>
