这里有新鲜出炉的精品教程,程序狗速度看过来!
Apache 是世界使用排名第一的 Web 服务器软件。它可以运行在几乎所有广泛使用的计算机平台上,由于其跨平台和安全性被广泛使用,是最流行的 Web 服务器端软件。
下面小编就为大家带来一篇基于 apache poi 根据模板导出 excel 的实现方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
需要预先新建编辑好一个 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 的实现方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持 PHPERZ。
来源: http://www.phperz.com/article/17/0827/338451.html