欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-19 14:10:46
需要预先新建编辑好一个excel文件,设置好样式。 编辑好输出的数据,根据excel坐标一一对应。 支持列表数据输出,列表中列合并。 代码如下: packa...

需要预先新建编辑好一个excel文件,设置好样式。

编辑好输出的数据,根据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();
  }

}

大体思路:

最主要是制作好模版

代码根据模版,读取设置好的列的格式,在循环数据行,读取模版中的对应的行,存在该行就取得,不存在看是否需要copy某一行,不需要就手动创建无制定格式的行,后面在为该行的每一列对应的给个单元格制定格式和数据。

以上这篇基于apache poi根据模板导出excel的实现方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。

上一篇:

下一篇: