Java自定义模板导出Excel

该工具类是本人在实际开发中总结出来的，全是满满的干货，如需转载，请注明原文地址。

功能概述

1. 支持在任意位置填充可以是字符，数字，图片
2. 支持在任意位置填充一行值
3. 支持在任意位置填充列表值
4. 支持设置列宽，行高
5. 支持在任意位置填充标题

数据实体

ExcelSheetData

Sheet内容实体

ExcelSheetData.TextData

列的内容实体（字符、数字、时间）

ExcelSheetData.PictureData

图片内容实体

ExcelSheetData.RowSignData

单行内容实体

ExcelSheetData.RowMultiData

多行内容实体

/** * excel数据实体 * @author Vachel.Wang * @date 2016年7月4日 下午4:10:03   * @version V1.0 */public class ExcelSheetData {// 单列内容集合private List<TextData> textDataList = new ArrayList<TextData>();// 图片内容集合private List<PictureData> pictureDataList = new ArrayList<PictureData>();// 单行内容集合private List<RowSignData> rowSignDataList = new ArrayList<RowSignData>();// 多行内容集合private List<RowMultiData> rowMultiDataList = new ArrayList<RowMultiData>();// 多title集合private List<TitleData> titleDataList = new ArrayList<TitleData>();/** * 添加单列内容 * @param rowIndex 起始行 * @param colIndex 起始列 * @param data 内容 */public void addTextData(int rowIndex,int colIndex,Object data,Integer colWidth,Integer colHeight){ExcelSheetData.TextData textData = new ExcelSheetData.TextData(rowIndex, colIndex, data,colWidth,colHeight);textDataList.add(textData);}/** * 添加图片 * @param startRowIndex 起始行 * @param endRowIndex 结束行 * @param startCellIndex 起始列 * @param endCellIndex 结束列 * @param data  */public void addPictureData(int startCellIndex,int startRowIndex,int endCellIndex,int endRowIndex,Object data,Integer colWidth,Integer colHeight){ExcelSheetData.PictureData pictureData = new ExcelSheetData.PictureData(startCellIndex, startRowIndex, endCellIndex, endRowIndex, data,colWidth,colHeight);pictureDataList.add(pictureData);}/** * 添加标题行 * @param rowIndex * @param titleArr     */public void addTitleData(int rowIndex , String[] titleArr){titleDataList.add(new TitleData(titleArr,rowIndex));}/** * 添加一行数据 * @param rowIndex 起始行 * @param colIndex 起始列 * @param rowData 行的内容 */public void addRowSignData(int rowIndex,int colIndex,List<ColumnData> rowData){ExcelSheetData.RowSignData rowSignData = new ExcelSheetData.RowSignData(rowIndex, colIndex, rowData);rowSignDataList.add(rowSignData);}/** * 添加多行数据 * @param rowIndex 起始行 * @param colIndex 起始列 * @param rowData 行的内容 */public void addRowMultiData(int rowIndex,int colIndex,List<ArrayList<ColumnData>> rowData){ExcelSheetData.RowMultiData rowMultiData = new ExcelSheetData.RowMultiData(rowIndex, colIndex, rowData);rowMultiDataList.add(rowMultiData);}/** * 列的内容 * @author Vachel.Wang * @date 2016年7月4日 下午4:13:47   * @version V1.0 */public static abstract class ColumnData{protected Object data ;protected Integer colWidth ;protected Integer colHeight ;public Integer getColWidth() {return colWidth;}public void setColWidth(Integer colWidth) {this.colWidth = colWidth;}public Integer getColHeight() {return colHeight;}public void setColHeight(Integer colHeight) {this.colHeight = colHeight;}public Object getData() {return data;}public void setData(Object data) {this.data = data;}}/** * 文本内容实体 * @author Vachel.Wang * @date 2016年7月4日 下午3:45:48   * @version V1.0 */public static class TextData extends ColumnData{public TextData() {}public TextData(Integer rowIndex, Integer colIndex , Object data,Integer colWidth,Integer colHeight) {super();this.rowIndex = rowIndex;this.colIndex = colIndex;this.data = data;this.colWidth = colWidth;this.colHeight = colHeight;}private Integer rowIndex = 0;private Integer colIndex = 0;public Integer getRowIndex() {return rowIndex;}public void setRowIndex(Integer rowIndex) {this.rowIndex = rowIndex;}public Integer getColIndex() {return colIndex;}public void setColIndex(Integer colIndex) {this.colIndex = colIndex;}}/** * 图片内容实体 * @author Vachel.Wang * @date 2016年7月4日 下午4:19:51   * @version V1.0 */public static class PictureData extends ColumnData{public PictureData() {}public PictureData(int startCellIndex,int startRowIndex,int endCellIndex,int endRowIndex, Object data ,Integer colWidth,Integer colHeight) {super();this.startCellIndex = startCellIndex;this.startRowIndex = startRowIndex;this.endCellIndex = endCellIndex;this.endRowIndex = endRowIndex;this.data = data;this.colWidth = colWidth;this.colHeight = colHeight;}private Integer startRowIndex = 0;private Integer endRowIndex = 0;private Integer startCellIndex = 0;private Integer endCellIndex = 0;public Integer getStartRowIndex() {return startRowIndex;}public void setStartRowIndex(Integer startRowIndex) {this.startRowIndex = startRowIndex;}public Integer getEndRowIndex() {return endRowIndex;}public void setEndRowIndex(Integer endRowIndex) {this.endRowIndex = endRowIndex;}public Integer getStartCellIndex() {return startCellIndex;}public void setStartCellIndex(Integer startCellIndex) {this.startCellIndex = startCellIndex;}public Integer getEndCellIndex() {return endCellIndex;}public void setEndCellIndex(Integer endCellIndex) {this.endCellIndex = endCellIndex;}}public static class TitleData extends ColumnData{private Integer rowIndex ;public TitleData(Object data,int rowIndex) {super();this.data = data ;this.rowIndex = rowIndex ;}public TitleData(Object data,int rowIndex,Integer colWidth,Integer colHeight) {super();this.data = data ;this.rowIndex = rowIndex ;this.colWidth = colWidth ;this.colHeight = colHeight;}public Integer getRowIndex() {return rowIndex;}public void setRowIndex(Integer rowIndex) {this.rowIndex = rowIndex;}}/** * 单行的内容实体 * @author Vachel.Wang * @date 2016年7月4日 下午3:51:59   * @version V1.0 */public static class RowSignData{public RowSignData(){}public RowSignData(Integer rowIndex, Integer colIndex, List<ColumnData> rowData) {super();this.rowIndex = rowIndex;this.colIndex = colIndex;this.rowData = rowData;}private Integer rowIndex = 0;private Integer colIndex = 0;private List<ColumnData> rowData = new ArrayList<ColumnData>() ;public Integer getRowIndex() {return rowIndex;}public void setRowIndex(Integer rowIndex) {this.rowIndex = rowIndex;}public Integer getColIndex() {return colIndex;}public void setColIndex(Integer colIndex) {this.colIndex = colIndex;}public List<ColumnData> getRowData() {return rowData;}public void setRowData(List<ColumnData> rowData) {this.rowData = rowData;}}/** * 多行内容实体 * @author Vachel.Wang * @date 2016年7月4日 下午3:57:27   * @version V1.0 */public static class RowMultiData{public RowMultiData(Integer rowIndex, Integer colIndex, List<ArrayList<ColumnData>> rowData) {super();this.rowIndex = rowIndex;this.colIndex = colIndex;this.rowData = rowData;}private Integer rowIndex = 0;private Integer colIndex = 0;private List<ArrayList<ColumnData>> rowData = new ArrayList<ArrayList<ColumnData>>() ;public Integer getRowIndex() {return rowIndex;}public void setRowIndex(Integer rowIndex) {this.rowIndex = rowIndex;}public Integer getColIndex() {return colIndex;}public void setColIndex(Integer colIndex) {this.colIndex = colIndex;}public List<ArrayList<ColumnData>> getRowData() {return rowData;}public void setRowData(List<ArrayList<ColumnData>> rowData) {this.rowData = rowData;}}public List<TextData> getTextDataList() {return textDataList;}public void setTextDataList(List<TextData> textDataList) {this.textDataList = textDataList;}public List<PictureData> getPictureDataList() {return pictureDataList;}public void setPictureDataList(List<PictureData> pictureDataList) {this.pictureDataList = pictureDataList;}public List<RowSignData> getRowSignDataList() {return rowSignDataList;}public void setRowSignDataList(List<RowSignData> rowSignDataList) {this.rowSignDataList = rowSignDataList;}public List<RowMultiData> getRowMultiDataList() {return rowMultiDataList;}public void setRowMultiDataList(List<RowMultiData> rowMultiDataList) {this.rowMultiDataList = rowMultiDataList;}public List<TitleData> getTitleDataList() {return titleDataList;}public void setTitleDataList(List<TitleData> titleDataList) {this.titleDataList = titleDataList;}}

模板导出工具类

/** * 通过自定义模板导出excel * @author Vachel.Wang * @date 2016年7月4日 下午1:52:23   * @version V1.1 */public class ExportExcelCustom {private XSSFWorkbook workbook = null;    private ExcelSheetData[] excelSheetDataArray ;private static final String REPLACE_CHAR = "%s";private static final double HEIGIT_TIMES = 15.625 ;private static final double WIDTH_TIMES = 35.7 ;    public ExportExcelCustom(String templatePath , ExcelSheetData ... excelSheetDataArray) throws IOException, InvalidFormatException{File file = new File(templatePath);if(!file.exists()) throw new IOException("文件不存在："+templatePath);if(excelSheetDataArray.length==0) throw new RuntimeException("sheetData 不能为空");this.excelSheetDataArray = excelSheetDataArray;workbook = (XSSFWorkbook) WorkbookFactory.create(file);}/** * 填充并导出excel * @param fileName * @param response */public void fillAndExport(String fileName,HttpServletResponse response){fillSheet();export(fileName,response);}/** * 导出 * @param fileName * @param response */public void export(String fileName,HttpServletResponse response){OutputStream outputStream = null ;try {response.reset();        response.setContentType("application/octet-stream; charset=utf-8");        response.setHeader("Content-Disposition", "attachment; filename="+URLEncoder.encode(fileName,"UTF-8"));outputStream = response.getOutputStream();workbook.write(outputStream);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();throw new GenericException("导出excel异常");} finally{try {if(null!=outputStream){outputStream.flush();outputStream.close();}} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/** * 设置单元格宽高度 * @param columnData * @param sheet * @param row */private void setWidthAndHeight(ColumnData columnData,XSSFSheet sheet,XSSFRow row){if(null==columnData || null == sheet || null==row)return ;if(columnData instanceof ExcelSheetData.TextData){TextData textData = (TextData)columnData;if(textData.getColHeight()!=null) row.setHeight((short)(HEIGIT_TIMES*textData.getColHeight()));if(textData.getColWidth()!=null) sheet.setColumnWidth(textData.getColIndex(), (short)(WIDTH_TIMES*textData.getColWidth()));}else if(columnData instanceof ExcelSheetData.PictureData){PictureData pictureData = (PictureData)columnData;if(pictureData.getColHeight()!=null) row.setHeight((short)(HEIGIT_TIMES*pictureData.getColHeight()));if(pictureData.getColWidth()!=null) sheet.setColumnWidth(pictureData.getStartCellIndex(), (short)(WIDTH_TIMES*pictureData.getColWidth()));}}/** * 设置列index * @param columnData * @param index     */private void setColumnIndex(ColumnData columnData,int index){if(columnData instanceof ExcelSheetData.TextData){TextData textData = (TextData)columnData;textData.setColIndex(index);}}/** * 填充sheet */private void fillSheet(){for (int i = 0; i < excelSheetDataArray.length; i++) {ExcelSheetData sheetData = excelSheetDataArray[i];XSSFSheet sheet = workbook.getSheetAt(i);if(null==sheet){sheet = workbook.createSheet("sheet"+(i+1));}// 填充单列数据for(ExcelSheetData.TextData textData: sheetData.getTextDataList()){XSSFRow row = sheet.getRow(textData.getRowIndex());if(null==row) row = sheet.createRow(textData.getRowIndex());XSSFCell cell = row.getCell(textData.getColIndex());// 设置尺寸setWidthAndHeight(textData, sheet, row);if(null==cell) cell=row.createCell(textData.getColIndex());fillCellContent(cell, textData.getData());}// 填充图片数据for(ExcelSheetData.PictureData pictureData:sheetData.getPictureDataList()){// 设置尺寸setWidthAndHeight(pictureData, sheet, sheet.getRow(pictureData.getStartRowIndex()));fillCellPicture(sheet, pictureData);}// 填充一行数据for(ExcelSheetData.RowSignData rowSignData:sheetData.getRowSignDataList()){XSSFRow row = sheet.getRow(rowSignData.getRowIndex());for(int j=0 ;j<rowSignData.getRowData().size();j++){ExcelSheetData.ColumnData columnData = rowSignData.getRowData().get(j);XSSFCell cell = row.getCell(rowSignData.getColIndex()+j);// 设置列indexsetColumnIndex(columnData,j);// 设置尺寸setWidthAndHeight(columnData, sheet, row);if(null==cell) cell=row.createCell(rowSignData.getColIndex()+j);if(columnData instanceof ExcelSheetData.TextData)fillCellContent(cell, columnData.getData());else if(columnData instanceof ExcelSheetData.PictureData)fillCellPicture(sheet, (ExcelSheetData.PictureData) columnData);}}// 填充多行数据for(ExcelSheetData.RowMultiData rowMultiData:sheetData.getRowMultiDataList()){// 行for(int k=0;k<rowMultiData.getRowData().size();k++){XSSFRow row = sheet.getRow(rowMultiData.getRowIndex()+k);if(null==row) row = sheet.createRow(rowMultiData.getRowIndex()+k);ArrayList<ColumnData> columnDataList = rowMultiData.getRowData().get(k);// 列for(int j=0;j<columnDataList.size();j++){ColumnData columnData = columnDataList.get(j);// 设置列indexsetColumnIndex(columnData,j);// 设置尺寸setWidthAndHeight(columnData, sheet, row);XSSFCell cell = row.getCell(rowMultiData.getColIndex()+j);if(null==cell) cell=row.createCell(rowMultiData.getColIndex()+j);if(columnData instanceof ExcelSheetData.TextData)fillCellContent(cell, columnData.getData());else if(columnData instanceof ExcelSheetData.PictureData)fillCellPicture(sheet, (ExcelSheetData.PictureData) columnData);}}}// 填充标题for(int j=0;j<sheetData.getTitleDataList().size();j++){ExcelSheetData.TitleData titleData = sheetData.getTitleDataList().get(j);XSSFRow row = sheet.getRow(titleData.getRowIndex()) == null ? sheet.createRow(titleData.getRowIndex()) : sheet.getRow(titleData.getRowIndex());fillTitle(row,titleData);}}}/** * 填充内容 * @param cell * @param data */private void fillCellContent(XSSFCell cell,Object data){if(cell==null) return ;if(data == null){String cellData = getStringCellValue(cell);cell.setCellValue(cellData.replaceAll(REPLACE_CHAR, ""));return ;}// 字符串if(data instanceof String){String strData = data.toString();String cellData = getStringCellValue(cell);if(cellData.indexOf(REPLACE_CHAR)!=-1){cellData = String.format(cellData, strData.split(";"));}else{cellData += strData;}cell.setCellValue(cellData);}// 整数else if(data instanceof Integer){Integer integerData = (Integer)data;String cellData = getStringCellValue(cell);if(cellData.equals("")){cell.setCellValue(integerData);}else if(cellData.indexOf(REPLACE_CHAR)!=-1){cellData = cellData.replaceAll(REPLACE_CHAR, String.valueOf(integerData));cell.setCellValue(cellData);}else {cell.setCellValue(cellData+integerData);}}// 长整数else if(data instanceof Long){Long longData = (Long)data;String cellData = getStringCellValue(cell);if(cellData.equals("")){cell.setCellValue(longData);}else if(cellData.indexOf(REPLACE_CHAR)!=-1){cellData = cellData.replaceAll(REPLACE_CHAR, String.valueOf(longData));cell.setCellValue(cellData);}else {cell.setCellValue(cellData+longData);}}// 小数else if(data instanceof Double){Double doubleData = (Double)data;String cellData = getStringCellValue(cell);if(cellData.equals("")){cell.setCellValue(doubleData);}else if(cellData.indexOf(REPLACE_CHAR)!=-1){cellData = cellData.replaceAll(REPLACE_CHAR, String.valueOf(doubleData));cell.setCellValue(cellData);}else {cell.setCellValue(cellData+doubleData);}}// 时间else if(data instanceof Date){Date dateData = (Date)data;String cellData = getStringCellValue(cell);if(cellData.equals("")){cell.setCellValue(dateData);}else if(cellData.indexOf(REPLACE_CHAR)!=-1){cellData = cellData.replace(REPLACE_CHAR, com.gjw.utils.DateUtil.formatDateTime(dateData, com.gjw.utils.DateUtil.DATE_TIME_FORMAT));cell.setCellValue(cellData);}else {cell.setCellValue(cellData+com.gjw.utils.DateUtil.formatDateTime(dateData, com.gjw.utils.DateUtil.DATE_TIME_FORMAT));}}else{cell.setCellValue(data+"");}}/** * 填充图片 * @param sheet * @param pictureData */private void fillCellPicture(XSSFSheet sheet,ExcelSheetData.PictureData pictureData){if(pictureData==null || pictureData.getData()==null) return;byte bytes[] = (byte[])pictureData.getData();int pictureIdx = workbook.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);Drawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255, pictureData.getStartCellIndex(), pictureData.getStartRowIndex(),pictureData.getEndCellIndex(),pictureData.getEndRowIndex());    drawing.createPicture(anchor, pictureIdx);}/** * 填充标题 * @param cell * @param titleData     */private void fillTitle(XSSFRow row,ExcelSheetData.TitleData titleData){/*标题字体*/XSSFFont titleFont = workbook.createFont();titleFont.setFontHeightInPoints((short)16);titleFont.setFontName("宋体");titleFont.setBold(true);/*标题样式*/XSSFCellStyle titleStyle = workbook.createCellStyle();titleStyle.setAlignment(CellStyle.ALIGN_CENTER);titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);titleStyle.setFont(titleFont);titleStyle.setBorderBottom(BorderStyle.MEDIUM);String[] titleDataArr = (String[]) titleData.getData();for(int i=0;i<titleDataArr.length;i++){XSSFCell cell = row.getCell(i)==null?row.createCell(i):row.getCell(i);cell.setCellValue(titleDataArr[i]);cell.setCellStyle(titleStyle);}}public XSSFWorkbook getWorkbook() {return workbook;}public void setWorkbook(XSSFWorkbook workbook) {this.workbook = workbook;}}

模板存储路径：

模板内容：

填充散列效果：

填充列表效果：