Springboot使用POI实现导出Excel文件示例

前面讲述了使用POI导出Word文件和读取Excel文件，这两个例子都相对简单，接下来要讲述的使用POI导出Excel文件要复杂得多，内容也会比较长。

创建表头信息

表头信息用于自动生成表头结构及排序

public class ExcelHeader implements Comparable{
 
 private String title;
 
 private int order;
 
 private String methodName;


 public String getTitle() {
  return title;
 }
 public void setTitle(String title) {
  this.title = title;
 }
 public int getOrder() {
  return order;
 }
 public void setOrder(int order) {
  this.order = order;
 }
 public String getMethodName() {
  return methodName;
 }
 public void setMethodName(String methodName) {
  this.methodName = methodName;
 }

 public int compareTo(ExcelHeader o) {
  return order>o.order?1:(order

表头信息的Annotation




@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResources {
 
 String title();
 
 int order() default 9999;
}


创建数据实体



public class WebDto {

 //网站名称
 private String name;

 //网址
 private String url;

 //用户名
 private String username;

 //密码
 private String password;

 //日均访问量
 private Integer readCount;

 public WebDto(String name, String url, String username, String password, Integer readCount) {
  this.name = name;
  this.url = url;
  this.username = username;
  this.password = password;
  this.readCount = readCount;
 }

 public WebDto() {}

 @Override
 public String toString() {
  return "WebDto{" +
    "name='" + name + ''' +
    ", url='" + url + ''' +
    ", username='" + username + ''' +
    ", password='" + password + ''' +
    ", readCount=" + readCount +
    '}';
 }

 @ExcelResources(title="网站名称",order=1)
 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

 @ExcelResources(title="网址",order=2)
 public String getUrl() {
  return url;
 }

 public void setUrl(String url) {
  this.url = url;
 }

 @ExcelResources(title="用户名",order=3)
 public String getUsername() {
  return username;
 }

 public void setUsername(String username) {
  this.username = username;
 }

 @ExcelResources(title="密码",order=4)
 public String getPassword() {
  return password;
 }

 public void setPassword(String password) {
  this.password = password;
 }

 @ExcelResources(title="日均访问量",order=5)
 public Integer getReadCount() {
  return readCount;
 }

 public void setReadCount(Integer readCount) {
  this.readCount = readCount;
 }
}



注意：这里使用到了@ExcelResources来自动识别表头信息及序号
获取模板文件的工具类



public class TemplateFileUtil {

 public static FileInputStream getTemplates(String tempName) throws FileNotFoundException {
  return new FileInputStream(ResourceUtils.getFile("classpath:excel-templates/"+tempName));
 }
}


注意：从这里可以看出，所有的Excel模板文件都放在resources/excel-templates/目录下。
模板工具类


通过此类可以自动复制表样式等功能


public class ExcelTemplate {

 
 public final static String DATA_LINE = "datas";
 
 public final static String DEFAULT_STYLE = "defaultStyles";
 
 public final static String STYLE = "styles";
 
 public final static String SER_NUM = "sernums";
 private static ExcelTemplate et = new ExcelTemplate();
 private Workbook wb;
 private Sheet sheet;
 
 private int initColIndex;
 
 private int initRowIndex;
 
 private int curColIndex;
 
 private int curRowIndex;
 
 private Row curRow;
 
 private int lastRowIndex;
 
 private CellStyle defaultStyle;
 
 private float rowHeight;
 
 private Map styles;
 
 private int serColIndex;
 private ExcelTemplate(){

 }
 public static ExcelTemplate getInstance() {
  return et;
 }

 
 public ExcelTemplate readTemplateByClasspath(String path) {
  try {
   wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));
   initTemplate();
  } catch (IOException e) {
   e.printStackTrace();
   throw new RuntimeException("读取模板不存在！请检查");
  }
  return this;
 }
 
 public void writeToFile(String filepath) {
  FileOutputStream fos = null;
  try {
   fos = new FileOutputStream(filepath);
   wb.write(fos);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
   throw new RuntimeException("写入的文件不存在");
  } catch (IOException e) {
   e.printStackTrace();
   throw new RuntimeException("写入数据失败:"+e.getMessage());
  } finally {
   try {
    if(fos!=null) fos.close();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
 }
 
 public void wirteToStream(OutputStream os) {
  try {
   wb.write(os);
  } catch (IOException e) {
   e.printStackTrace();
   throw new RuntimeException("写入流失败:"+e.getMessage());
  }
 }
 
 public ExcelTemplate readTemplateByPath(String path) {
  try {
   wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));
   initTemplate();
  } catch (IOException e) {
   e.printStackTrace();
   throw new RuntimeException("读取模板不存在！请检查");
  }
  return this;
 }

 
 public void createCell(String value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value);
  curColIndex++;
 }
 public void createCell(int value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue((int)value);
  curColIndex++;
 }
 public void createCell(Date value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value);
  curColIndex++;
 }
 public void createCell(double value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value);
  curColIndex++;
 }
 public void createCell(boolean value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value);
  curColIndex++;
 }

 public void createCell(Calendar value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value);
  curColIndex++;
 }
 public void createCell(BigInteger value) {
  Cell c = curRow.createCell(curColIndex);
  setCellStyle(c);
  c.setCellValue(value==null?0:value.intValue());
  curColIndex++;
 }
 
 private void setCellStyle(Cell c) {
  if(styles.containsKey(curColIndex)) {
   c.setCellStyle(styles.get(curColIndex));
  } else {
   c.setCellStyle(defaultStyle);
  }
 }
 
 public void createNewRow() {
  if(lastRowIndex>curRowIndex&&curRowIndex!=initRowIndex) {
   sheet.shiftRows(curRowIndex, lastRowIndex, 1,true,true);
   lastRowIndex++;
  }
  curRow = sheet.createRow(curRowIndex);
  curRow.setHeightInPoints(rowHeight);
  curRowIndex++;
  curColIndex = initColIndex;
 }

 
 public void insertSer() {
  int index = 1;
  Row row = null;
  Cell c = null;
  for(int i=initRowIndex;i datas) {
  if(datas==null) return;
  for(Row row:sheet) {
   for(Cell c:row) {
//    if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;
    String str = c.getStringCellValue().trim();
    if(str.startsWith("#")) {
     if(datas.containsKey(str.substring(1))) {
      c.setCellValue(datas.get(str.substring(1)));
     }
    }
   }
  }
 }
 
 public void replaceFinalData(Properties prop) {
  if(prop==null) return;
  for(Row row:sheet) {
   for(Cell c:row) {
//    if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;
    String str = c.getStringCellValue().trim();
    if(str.startsWith("#")) {
     if(prop.containsKey(str.substring(1))) {
      c.setCellValue(prop.getProperty(str.substring(1)));
     }
    }
   }
  }
 }

 private void initTemplate() {
  sheet = wb.getSheetAt(0);
  initConfigData();
  lastRowIndex = sheet.getLastRowNum();
  curRow = sheet.createRow(curRowIndex);
 }
 
 private void initConfigData() {
  boolean findData = false;
  boolean findSer = false;
  for(Row row:sheet) {
   if(findData) break;
   for(Cell c:row) {
//    if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;
    String str = c.getStringCellValue().trim();
    if(str.equals(SER_NUM)) {
     serColIndex = c.getColumnIndex();
     findSer = true;
    }
    if(str.equals(DATA_LINE)) {
     initColIndex = c.getColumnIndex();
     initRowIndex = row.getRowNum();
     curColIndex = initColIndex;
     curRowIndex = initRowIndex;
     findData = true;
     defaultStyle = c.getCellStyle();
     rowHeight = row.getHeightInPoints();
     initStyles();
     break;
    }
   }
  }
  if(!findSer) {
   initSer();
  }
 }
 
 private void initSer() {
  for(Row row:sheet) {
   for(Cell c:row) {
//    if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;
    String str = c.getStringCellValue().trim();
    if(str.equals(SER_NUM)) {
     serColIndex = c.getColumnIndex();
    }
   }
  }
 }
 
 private void initStyles() {
  styles = new HashMap();
  for(Row row:sheet) {
   for(Cell c:row) {
//    if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;
    String str = c.getStringCellValue().trim();
    if(str.equals(DEFAULT_STYLE)) {
     defaultStyle = c.getCellStyle();
    }
    if(str.equals(STYLE)) {
     styles.put(c.getColumnIndex(), c.getCellStyle());
    }
   }
  }
 }
}



操作工具类




public class ExcelUtil {
 private static ExcelUtil eu = new ExcelUtil();
 private ExcelUtil(){}

 public static ExcelUtil getInstance() {
  return eu;
 }
 
 private ExcelTemplate handlerObj2Excel (String template, List objs, Class clz, boolean isClasspath) {
  ExcelTemplate et = ExcelTemplate.getInstance();
  try {
   if(isClasspath) {
    et.readTemplateByClasspath(template);
   } else {
    et.readTemplateByPath(template);
   }
   List headers = getHeaderList(clz);
   Collections.sort(headers);
   //输出标题
   et.createNewRow();
   for(ExcelHeader eh:headers) {
    et.createCell(eh.getTitle());
   }
   //输出值
   for(Object obj:objs) {
    et.createNewRow();
    for(ExcelHeader eh:headers) {
     //    Method m = clz.getDeclaredMethod(mn);
     //    Object rel = m.invoke(obj);
     et.createCell(BeanUtils.getProperty(obj,getMethodName(eh)));
    }
   }
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (InvocationTargetException e) {
   e.printStackTrace();
  } catch (NoSuchMethodException e) {
   e.printStackTrace();
  }
  return et;
 }
 
 private String getMethodName(ExcelHeader eh) {
  String mn = eh.getMethodName().substring(3);
  mn = mn.substring(0,1).toLowerCase()+mn.substring(1);
  return mn;
 }
 
 public void exportObj2ExcelByTemplate(Map datas, String template, OutputStream os, List objs, Class clz, boolean isClasspath) {
  try {
   ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
   et.replaceFinalData(datas);
   et.wirteToStream(os);
   os.flush();
   os.close();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 
 public void exportObj2ExcelByTemplate(Map datas,String template,String outPath,List objs,Class clz,boolean isClasspath) {
  ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
  et.replaceFinalData(datas);
  et.writeToFile(outPath);
 }

 
 public void exportObj2ExcelByTemplate(Properties prop, String template, OutputStream os, List objs, Class clz, boolean isClasspath) {
  ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
  et.replaceFinalData(prop);
  et.wirteToStream(os);
 }
 
 public void exportObj2ExcelByTemplate(Properties prop,String template,String outPath,List objs,Class clz,boolean isClasspath) {
  ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);
  et.replaceFinalData(prop);
  et.writeToFile(outPath);
 }

 private Workbook handleObj2Excel(List objs, Class clz) {
  Workbook wb = new HSSFWorkbook();
  try {
   Sheet sheet = wb.createSheet();
   Row r = sheet.createRow(0);
   List headers = getHeaderList(clz);
   Collections.sort(headers);
   //写标题
   for(int i=0;i readExcel2ObjsByClasspath(String path,Class clz,int readLine,int tailLine) {
  Workbook wb = null;
  try {
   wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));
   return handlerExcel2Objs(wb, clz, readLine,tailLine);
  } catch (IOException e) {
   e.printStackTrace();
  }
  return null;
 }
 
 public List