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

POOPE

需要预先新建编辑好一个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的实现方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。