基于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的实现方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。