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> tempWorkbook = new HashMap<string>();//模板输入流mapprivate Map<string> tempStream = new HashMap<string>();/** * 功能:按模板向Excel中相应地方填充数据     */public void writeData(String templateFilePath, Map<string> dataMap, int sheetNo) throws IOException, InvalidFormatException {if (dataMap == null || dataMap.isEmpty()) {return;
        }//读取模板Workbook wbModule = getTempWorkbook(templateFilePath);//数据填充的sheetSheet wsheet = wbModule.getSheetAt(sheetNo);for (Entry<string> 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>> datalist, int sheetNo) throws IOException, InvalidFormatException {if (heads == null || heads.length  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  dataMap = datalist.get(i);for (int j = 0; j  -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 = firstRow && row = firstColumn && column = pStartRow)&& (region.getLastRow()  dataMap = new HashMap<string>();
        dataMap.put("B1", "03_Alpha_项目工作时间统计表");
        dataMap.put("B2", "统计时间：2017/01/01 - 2017/03/31");

        excel.writeData(templateFilePath, dataMap, 0);

        List<map>> datalist = new ArrayList<map>>();
        Map<integer> data = new HashMap<integer>();
        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>();
        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>();
        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>();
        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>();
        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>();
        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>();
        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>();
        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>();
        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>();
        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();
    }

}</integer></integer></integer></integer></integer></integer></integer></integer></integer></integer></integer></map></map></string></tempcell></map></string></string></string></string></string></string>

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!