apache poi export excel based on template
You need to create and edit an excel file in advance and set the style.
Edit the output data and make one-to-one correspondence according to the excel coordinates.
Supports list data output and column merging in the list.
The code is as follows:
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 {//模板mapprivate Map<String, Workbook> tempWorkbook = new HashMap<String, Workbook>();//模板输入流mapprivate 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);//数据填充的sheetSheet 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);//数据填充的sheetSheet wsheet = wbModule.getSheetAt(sheetNo);//列表数据模板cellList<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();
}
}
General idea:
The most important thing is to make a good template
The code reads the format of the set columns according to the template, and loops Data row, read the corresponding row in the template, and get it if the row exists. If it does not exist, check whether you need to copy a certain row. If not, manually create a row without a specified format, and then give a corresponding number for each column of the row. Cells specify formats and data.
The above is the detailed content of apache poi export excel based on template. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to set the cgi directory in apache
Apr 13, 2025 pm 01:18 PM
To set up a CGI directory in Apache, you need to perform the following steps: Create a CGI directory such as "cgi-bin", and grant Apache write permissions. Add the "ScriptAlias" directive block in the Apache configuration file to map the CGI directory to the "/cgi-bin" URL. Restart Apache.
How to connect to the database of apache
Apr 13, 2025 pm 01:03 PM
Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.
What to do if the apache80 port is occupied
Apr 13, 2025 pm 01:24 PM
When the Apache 80 port is occupied, the solution is as follows: find out the process that occupies the port and close it. Check the firewall settings to make sure Apache is not blocked. If the above method does not work, please reconfigure Apache to use a different port. Restart the Apache service.
How to view your apache version
Apr 13, 2025 pm 01:15 PM
There are 3 ways to view the version on the Apache server: via the command line (apachectl -v or apache2ctl -v), check the server status page (http://<server IP or domain name>/server-status), or view the Apache configuration file (ServerVersion: Apache/<version number>).
How to view the apache version
Apr 13, 2025 pm 01:00 PM
How to view the Apache version? Start the Apache server: Use sudo service apache2 start to start the server. View version number: Use one of the following methods to view version: Command line: Run the apache2 -v command. Server Status Page: Access the default port of the Apache server (usually 80) in a web browser, and the version information is displayed at the bottom of the page.
How to configure zend for apache
Apr 13, 2025 pm 12:57 PM
How to configure Zend in Apache? The steps to configure Zend Framework in an Apache Web Server are as follows: Install Zend Framework and extract it into the Web Server directory. Create a .htaccess file. Create the Zend application directory and add the index.php file. Configure the Zend application (application.ini). Restart the Apache Web server.
How to solve the problem that apache cannot be started
Apr 13, 2025 pm 01:21 PM
Apache cannot start because the following reasons may be: Configuration file syntax error. Conflict with other application ports. Permissions issue. Out of memory. Process deadlock. Daemon failure. SELinux permissions issues. Firewall problem. Software conflict.
How to delete more than server names of apache
Apr 13, 2025 pm 01:09 PM
To delete an extra ServerName directive from Apache, you can take the following steps: Identify and delete the extra ServerName directive. Restart Apache to make the changes take effect. Check the configuration file to verify changes. Test the server to make sure the problem is resolved.
