基于apache poi根據(jù)模板導(dǎo)出excel的實(shí)現(xiàn)方法
更新時(shí)間：2017年06月05日 08:50:14 投稿：jingxian
下面小編就為大家?guī)硪黄赼pache poi根據(jù)模板導(dǎo)出excel的實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧
需要預(yù)先新建編輯好一個(gè)excel文件，設(shè)置好樣式。
編輯好輸出的數(shù)據(jù)，根據(jù)excel坐標(biāo)一一對應(yīng)。
支持列表數(shù)據(jù)輸出，列表中列合并。
代碼如下：
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根據(jù)模板導(dǎo)出excel,根據(jù)excel坐標(biāo)賦值,如(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中相應(yīng)地方填充數(shù)據(jù)
*/
public void writeData(String templateFilePath, Map<String, Object> dataMap, int sheetNo) throws IOException, InvalidFormatException {
if (dataMap == null || dataMap.isEmpty()) {
return;
}
//讀取模板
Workbook wbModule = getTempWorkbook(templateFilePath);
//數(shù)據(jù)填充的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);
//指定坐標(biāo)賦值
setCell(cell, wsheet);
}
//設(shè)置生成excel中公式自動(dòng)計(jì)算
wsheet.setForceFormulaRecalculation(true);
}
/**
* 功能:按模板向Excel中列表填充數(shù)據(jù).只支持列合并
*/
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);
//數(shù)據(jù)填充的sheet
Sheet wsheet = wbModule.getSheetAt(sheetNo);
//列表數(shù)據(jù)模板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++) {//數(shù)據(jù)行
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);
}
}
}
/**
* 功能:獲取輸入工作區(qū)
*/
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);
}
/**
* 功能:獲取單元格數(shù)據(jù),樣式(根據(jù)坐標(biāo):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();
}
//將列字母轉(zhuǎn)成列號 根據(jù)ascii轉(zhuǎn)換
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));
}
//獲取模板指定單元格樣式,設(shè)置到tempCell(寫列表數(shù)據(jù)的時(shí)候用)
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;
}
/**
* 功能:給指定坐標(biāo)單元格賦值
*/
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);//復(fù)制上一行
rowIn = sheet.getRow(tempCell.getRow());
}
Cell cellIn = rowIn.getCell(tempCell.getColumn());
if (cellIn == null) {
cellIn = rowIn.createCell(tempCell.getColumn());
}
//根據(jù)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;
}
/**
* 功能:設(shè)置合并單元格樣式
*/
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);
}
}
// 設(shè)置列寬
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);
}
/**
* 描述:臨時(shí)單元格數(shù)據(jù)
*/
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_項(xiàng)目工作時(shí)間統(tǒng)計(jì)表");
dataMap.put("B2", "統(tǒng)計(jì)時(shí)間：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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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, "新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用新增項(xiàng)目鍵值對接口,供任務(wù)計(jì)時(shí)調(diào)用");
data.put(6, "代碼開發(fā)");
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();
}
}
大體思路：
最主要是制作好模版
代碼根據(jù)模版，讀取設(shè)置好的列的格式，在循環(huán)數(shù)據(jù)行，讀取模版中的對應(yīng)的行，存在該行就取得，不存在看是否需要copy某一行，不需要就手動(dòng)創(chuàng)建無制定格式的行，后面在為該行的每一列對應(yīng)的給個(gè)單元格制定格式和數(shù)據(jù)。
以上這篇基于apache poi根據(jù)模板導(dǎo)出excel的實(shí)現(xiàn)方法就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了Java多線程中Lock鎖的使用總結(jié)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08
AOP是一種思想,是對某一類事情的集中處理,切面就是指某一類特定的問題,所以AOP可以理解為面向特定方法編程,這篇文章主要介紹了Spring AOP的實(shí)現(xiàn)方式,需要的朋友可以參考下2024-02-02
spring boot整合redis實(shí)現(xiàn)RedisTemplate三分鐘快速入門
這篇文章主要介紹了spring boot整合redis實(shí)現(xiàn)RedisTemplate三分鐘快速入門，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
@Scheduled定時(shí)器原理及@RefreshScope相互影響
這篇文章主要為大家介紹了@Scheduled定時(shí)器原理及@RefreshScope相互影響詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-07-07
JAVA中實(shí)現(xiàn)原生的 socket 通信機(jī)制原理
本篇文章主要介紹了JAVA中實(shí)現(xiàn)原生的 socket 通信機(jī)制原理，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-08-08
mybatis-plus讀取JSON類型的方法實(shí)現(xiàn)
這篇文章主要介紹了mybatis-plus讀取JSON類型的方法實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
這篇文章主要介紹了java 中http請求為了防止亂碼解決方案的相關(guān)資料,需要的朋友可以參考下2017-02-02
Java設(shè)計(jì)模式之Iterator模式介紹
所謂Iterator模式，即是Iterator為不同的容器提供一個(gè)統(tǒng)一的訪問方式。本文以java中的容器為例，模擬Iterator的原理。需要的朋友可以參考下2013-07-07
動(dòng)態(tài)代理指的是，代理類和目標(biāo)類的關(guān)系在程序運(yùn)行的時(shí)候確定的，客戶通過代理類來調(diào)用目標(biāo)對象的方法，是在程序運(yùn)行時(shí)根據(jù)需要?jiǎng)討B(tài)的創(chuàng)建目標(biāo)類的代理對象。本文將通過案例詳細(xì)講解一下Java動(dòng)態(tài)代理的原理及實(shí)現(xiàn)，需要的可以參考一下2022-08-08
Springboot內(nèi)置tomcat配置虛擬路徑過程解析
這篇文章主要介紹了Springboot內(nèi)置tomcat配置虛擬路徑過程解析,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-04-04