excel util；读取模板，动态加载模板

最新推荐文章于 2024-10-03 08:26:33 发布

资深java

最新推荐文章于 2024-10-03 08:26:33 发布

阅读量1.5k

点赞数

本文链接：https://blog.csdn.net/oqqlhh/article/details/78744197

版权

本文介绍如何使用Java的ExcelUtil工具库，动态加载并读取Excel模板，实现数据填充和导出的功能。

摘要由CSDN通过智能技术生成

jar

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

util

package com.ciicsh.gto.settlementcenter.invoiceservice.util;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

/**
 * excel util
 *
 * @author linhaihai
 */
public class ExcelUtil {

    /**
     * 默认日期格式
     */
    private static final String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";

    private static final int DEFAULT_COLUMN_WIDTH = 17;

    private static ExcelUtil excelUtil = new ExcelUtil();

    private ExcelUtil() {

    }

    public static ExcelUtil getExcelUtil() {
        return excelUtil;
    }

    /**
     * 创建excel
     */
    public void createExcel() {


    }


    /**
     * 导出Excel 97(.xls)格式 ，少量数据
     *
     * @param title       标题行
     * @param headMap     属性-列名
     * @param jsonArray   数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray,
                            String datePattern, int colWidth, OutputStream out) {
        HSSFWorkbook workbook = null;
        try{
            if (datePattern == null) {
                datePattern = DEFAULT_DATE_PATTERN;
            }
            // 声明一个工作薄
            workbook = new HSSFWorkbook();
            workbook.createInformationProperties();
            workbook.getDocumentSummaryInformation().setCompany("*****公司");
            SummaryInformation si = workbook.getSummaryInformation();
            //填加xls文件作者信息
            si.setAuthor("JACK");
            //填加xls文件创建程序信息
            si.setApplicationName("导出程序");
            //填加xls文件最后保存者信息
            si.setLastAuthor("最后保存者信息");
            //填加xls文件作者信息
            si.setComments("JACK is a programmer!");
            //填加xls文件标题信息
            si.setTitle("POI导出Excel");
            //填加文件主题信息
            si.setSubject("POI导出Excel");
            si.setCreateDateTime(new Date());
            //表头样式
            HSSFCellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints((short) 20);
            titleFont.setBoldweight((short) 700);
            titleStyle.setFont(titleFont);
            // 列头样式
            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            headerStyle.setFont(headerFont);
            // 单元格样式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORD