基于apache poi根据模板导出excel的实现方法

package com.icourt.util;

import org.apache.commons.collections4.collectionutils;
import org.apache.poi.openxml4j.exceptions.invalidformatexception;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.cellrangeaddress;

import java.io.*;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;
import java.util.map;
import java.util.map.entry;
import java.util.regex.matcher;
import java.util.regex.pattern;

/**
 * 描述:poi根据模板导出excel,根据excel坐标赋值,如(b1)
 */
public class excelexportutil {

  //模板map
  private map<string, workbook> tempworkbook = new hashmap<string, workbook>();
  //模板输入流map
  private map<string, inputstream> tempstream = new hashmap<string, inputstream>();

  /**
   * 功能:按模板向excel中相应地方填充数据
   */
  public void writedata(string templatefilepath, map<string, object> datamap, int sheetno) throws ioexception, invalidformatexception {
    if (datamap == null || datamap.isempty()) {
      return;
    }
    //读取模板
    workbook wbmodule = gettempworkbook(templatefilepath);
    //数据填充的sheet
    sheet wsheet = wbmodule.getsheetat(sheetno);

    for (entry<string, object> entry : datamap.entryset()) {
      string point = entry.getkey();
      object data = entry.getvalue();

      tempcell cell = getcell(point, data, wsheet);
      //指定坐标赋值
      setcell(cell, wsheet);
    }

    //设置生成excel中公式自动计算
    wsheet.setforceformularecalculation(true);
  }

  /**
   * 功能:按模板向excel中列表填充数据.只支持列合并
   */
  public void writedatelist(string templatefilepath, string[] heads, list<map<integer, object>> datalist, int sheetno) throws ioexception, invalidformatexception {
    if (heads == null || heads.length <= 0 || collectionutils.isempty(datalist)) {
      return;
    }
    //读取模板
    workbook wbmodule = gettempworkbook(templatefilepath);
    //数据填充的sheet
    sheet wsheet = wbmodule.getsheetat(sheetno);

    //列表数据模板cell
    list<tempcell> tempcells = new arraylist<tempcell>(heads.length);
    for (string point : heads) {
      tempcell tempcell = getcell(point, null, wsheet);
      //取得合并单元格位置 -1:表示不是合并单元格
      int pos = ismergedregion(wsheet, tempcell.getrow(), tempcell.getcolumn());
      if (pos > -1) {
        cellrangeaddress range = wsheet.getmergedregion(pos);
        tempcell.setcolumnsize(range.getlastcolumn() - range.getfirstcolumn());
      }
      tempcells.add(tempcell);
    }
    //赋值
    for (int i = 0; i < datalist.size(); i++) {//数据行
      map<integer, object> datamap = datalist.get(i);
      for (int j = 0; j < tempcells.size(); j++) {//列
        tempcell tempcell = tempcells.get(j);
        tempcell.setdata(datamap.get(j + 1));
        setcell(tempcell, wsheet);
        tempcell.setrow(tempcell.getrow() + 1);
      }
    }
  }

  /**
   * 功能:获取输入工作区
   */
  private workbook gettempworkbook(string templatefilepath) throws ioexception, invalidformatexception {
    if (!tempworkbook.containskey(templatefilepath)) {
      inputstream inputstream = getinputstream(templatefilepath);
      tempworkbook.put(templatefilepath, workbookfactory.create(inputstream));
    }
    return tempworkbook.get(templatefilepath);
  }

  /**
   * 功能:获得模板输入流
   */
  private inputstream getinputstream(string templatefilepath) throws filenotfoundexception {
    if (!tempstream.containskey(templatefilepath)) {
      tempstream.put(templatefilepath, new fileinputstream((templatefilepath)));
    }
    return tempstream.get(templatefilepath);
  }

  /**
   * 功能:获取单元格数据,样式(根据坐标:b3)
   */
  private tempcell getcell(string point, object data, sheet sheet) {
    tempcell tempcell = new tempcell();

    //得到列  字母
    string linestr = "";
    string reg = "[a-z]+";
    pattern p = pattern.compile(reg);
    matcher m = p.matcher(point);
    while (m.find()) {
      linestr = m.group();
    }
    //将列字母转成列号 根据ascii转换
    char[] ch = linestr.tochararray();
    int column = 0;
    for (int i = 0; i < ch.length; i++) {
      char c = ch[i];
      int post = ch.length - i - 1;
      int r = (int) math.pow(10, post);
      column = column + r * ((int) c - 65);
    }
    tempcell.setcolumn(column);

    //得到行号
    reg = "[1-9]+";
    p = pattern.compile(reg);
    m = p.matcher(point);
    while (m.find()) {
      tempcell.setrow((integer.parseint(m.group()) - 1));
    }

    //获取模板指定单元格样式,设置到tempcell(写列表数据的时候用)
    row rowin = sheet.getrow(tempcell.getrow());
    if (rowin == null) {
      rowin = sheet.createrow(tempcell.getrow());
    }
    cell cellin = rowin.getcell(tempcell.getcolumn());
    if (cellin == null) {
      cellin = rowin.createcell(tempcell.getcolumn());
    }
    tempcell.setcellstyle(cellin.getcellstyle());
    tempcell.setdata(data);
    return tempcell;
  }

  /**
   * 功能:给指定坐标单元格赋值
   */
  private void setcell(tempcell tempcell, sheet sheet) {
    if (tempcell.getcolumnsize() > -1) {
      cellrangeaddress rangeaddress = mergeregion(sheet, tempcell.getrow(), tempcell.getrow(), tempcell.getcolumn(), tempcell.getcolumn() + tempcell.getcolumnsize());
      setregionstyle(tempcell.getcellstyle(), rangeaddress, sheet);
    }

    row rowin = sheet.getrow(tempcell.getrow());
    if (rowin == null) {
      copyrows(tempcell.getrow() - 1, tempcell.getrow() - 1, tempcell.getrow(), sheet);//复制上一行
      rowin = sheet.getrow(tempcell.getrow());
    }
    cell cellin = rowin.getcell(tempcell.getcolumn());
    if (cellin == null) {
      cellin = rowin.createcell(tempcell.getcolumn());
    }
    //根据data类型给cell赋值
    if (tempcell.getdata() instanceof string) {
      cellin.setcellvalue((string) tempcell.getdata());
    } else if (tempcell.getdata() instanceof integer) {
      cellin.setcellvalue((int) tempcell.getdata());
    } else if (tempcell.getdata() instanceof double) {
      cellin.setcellvalue((double) tempcell.getdata());
    } else {
      cellin.setcellvalue((string) tempcell.getdata());
    }
    //样式
    if (tempcell.getcellstyle() != null && tempcell.getcolumnsize() == -1) {
      cellin.setcellstyle(tempcell.getcellstyle());
    }
  }

  /**
   * 功能:写到输出流并移除资源
   */
  public void writeandclose(string templatefilepath, outputstream os) throws ioexception, invalidformatexception {
    if (gettempworkbook(templatefilepath) != null) {
      gettempworkbook(templatefilepath).write(os);
      tempworkbook.remove(templatefilepath);
    }
    if (getinputstream(templatefilepath) != null) {
      getinputstream(templatefilepath).close();
      tempstream.remove(templatefilepath);
    }
  }

  /**
   * 功能:判断指定的单元格是否是合并单元格
   */
  private integer ismergedregion(sheet sheet, int row, int column) {
    for (int i = 0; i < sheet.getnummergedregions(); i++) {
      cellrangeaddress range = sheet.getmergedregion(i);
      int firstcolumn = range.getfirstcolumn();
      int lastcolumn = range.getlastcolumn();
      int firstrow = range.getfirstrow();
      int lastrow = range.getlastrow();
      if (row >= firstrow && row <= lastrow) {
        if (column >= firstcolumn && column <= lastcolumn) {
          return i;
        }
      }
    }
    return -1;
  }

  /**
   * 功能:合并单元格
   */
  private cellrangeaddress mergeregion(sheet sheet, int firstrow, int lastrow, int firstcol, int lastcol) {
    cellrangeaddress rang = new cellrangeaddress(firstrow, lastrow, firstcol, lastcol);
    sheet.addmergedregion(rang);
    return rang;
  }

  /**
   * 功能:设置合并单元格样式
   */
  private void setregionstyle(cellstyle cs, cellrangeaddress region, sheet sheet) {
    for (int i = region.getfirstrow(); i <= region.getlastrow(); i++) {
      row row = sheet.getrow(i);
      if (row == null) row = sheet.createrow(i);
      for (int j = region.getfirstcolumn(); j <= region.getlastcolumn(); j++) {
        cell cell = row.getcell(j);
        if (cell == null) {
          cell = row.createcell(j);
          cell.setcellvalue("");
        }
        cell.setcellstyle(cs);
      }
    }
  }

  /**
   * 功能:copy rows
   */
  private void copyrows(int startrow, int endrow, int pposition, sheet sheet) {
    int pstartrow = startrow - 1;
    int pendrow = endrow - 1;
    int targetrowfrom;
    int targetrowto;
    int columncount;
    cellrangeaddress region = null;
    int i;
    int j;
    if (pstartrow == -1 || pendrow == -1) {
      return;
    }
    // 拷贝合并的单元格
    for (i = 0; i < sheet.getnummergedregions(); i++) {
      region = sheet.getmergedregion(i);
      if ((region.getfirstrow() >= pstartrow)
          && (region.getlastrow() <= pendrow)) {
        targetrowfrom = region.getfirstrow() - pstartrow + pposition;
        targetrowto = region.getlastrow() - pstartrow + pposition;
        cellrangeaddress newregion = region.copy();
        newregion.setfirstrow(targetrowfrom);
        newregion.setfirstcolumn(region.getfirstcolumn());
        newregion.setlastrow(targetrowto);
        newregion.setlastcolumn(region.getlastcolumn());
        sheet.addmergedregion(newregion);
      }
    }
    // 设置列宽
    for (i = pstartrow; i <= pendrow; i++) {
      row sourcerow = sheet.getrow(i);
      columncount = sourcerow.getlastcellnum();
      if (sourcerow != null) {
        row newrow = sheet.createrow(pposition - pstartrow + i);
        newrow.setheight(sourcerow.getheight());
        for (j = 0; j < columncount; j++) {
          cell templatecell = sourcerow.getcell(j);
          if (templatecell != null) {
            cell newcell = newrow.createcell(j);
            copycell(templatecell, newcell);
          }
        }
      }
    }
  }

  /**
   * 功能:copy cell,不copy值
   */
  private void copycell(cell srccell, cell distcell) {
    distcell.setcellstyle(srccell.getcellstyle());
    if (srccell.getcellcomment() != null) {
      distcell.setcellcomment(srccell.getcellcomment());
    }
    int srccelltype = srccell.getcelltype();
    distcell.setcelltype(srccelltype);
  }

  /**
   * 描述:临时单元格数据
   */
  class tempcell {
    private int row;
    private int column;
    private cellstyle cellstyle;
    private object data;
    //用于列表合并,表示几列合并
    private int columnsize = -1;

    public int getcolumn() {
      return column;
    }

    public void setcolumn(int column) {
      this.column = column;
    }

    public int getrow() {
      return row;
    }

    public void setrow(int row) {
      this.row = row;
    }

    public cellstyle getcellstyle() {
      return cellstyle;
    }

    public void setcellstyle(cellstyle cellstyle) {
      this.cellstyle = cellstyle;
    }

    public object getdata() {
      return data;
    }

    public void setdata(object data) {
      this.data = data;
    }

    public int getcolumnsize() {
      return columnsize;
    }

    public void setcolumnsize(int columnsize) {
      this.columnsize = columnsize;
    }
  }

  public static void main(string[] args) throws filenotfoundexception, ioexception, invalidformatexception {
    string templatefilepath = excelexportutil.class.getclassloader().getresource("plugin/protiming.xlsx").getpath();
    file file = new file("/users/sql/downloads/test/data.xlsx");
    outputstream os = new fileoutputstream(file);

    excelexportutil excel = new excelexportutil();
    map<string, object> datamap = new hashmap<string, object>();
    datamap.put("b1", "03_alpha_项目工作时间统计表");
    datamap.put("b2", "统计时间：2017/01/01 - 2017/03/31");

    excel.writedata(templatefilepath, datamap, 0);

    list<map<integer, object>> datalist = new arraylist<map<integer, object>>();
    map<integer, object> data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");

    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);

    data = new hashmap<integer, object>();
    data.put(1, "3/10/17");
    data.put(2, "18:50");
    data.put(3, "19:00");
    data.put(4, "李子鹏");
    data.put(5, "新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用");
    data.put(6, "代码开发");
    data.put(7, "3.17");
    datalist.add(data);
    data = new hashmap<integer, object>();
    data.put(1, "");
    data.put(2, "");
    data.put(3, "");
    data.put(4, "");
    data.put(5, "");
    data.put(6, "");
    data.put(7, "");
    datalist.add(data);

    string[] heads = new string[]{"b4", "c4", "d4", "e4", "f4", "g4", "h4"};
    excel.writedatelist(templatefilepath, heads, datalist, 0);

    //写到输出流并移除资源
    excel.writeandclose(templatefilepath, os);

    os.flush();
    os.close();
  }

}