POI导出Excel工具类

最新推荐文章于 2024-09-03 20:52:56 发布

叮咚细语

最新推荐文章于 2024-09-03 20:52:56 发布

阅读量648

点赞数

分类专栏： POI java 文章标签： java POI

本文链接：https://blog.csdn.net/m819177045/article/details/52700402

版权

java 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

POI

2 篇文章 0 订阅

订阅专栏

POI是很好的Excel解析和创建工具，现在提供下自己写的导出工具类：

载入的数据类型分List和Dto数组类型两种

package com.ztesoft.iom.common;

import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportUtils {
    /**
     * 带NEW适用2007新版格式xlsx，不带的是xls
     */
    private static Logger logger = Logger.getLogger(ExportUtils.class.getName());
    
    /**
     * 填写表头
     * @author liu.xiangfei
     * 2016年9月29日 上午11:10:14
     * @param headersInfo
     * @param workbook
     * @param sheet
     * @param rowIndex
     */
    public static void outputHeaders(String[] headersInfo,HSSFWorkbook workbook,HSSFSheet sheet,int rowIndex){
        // 列名样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        HSSFRow row = sheet.createRow(rowIndex);
        for(int i = 0;i<headersInfo.length;i++){
            HSSFCell nextCell = row.createCell(i);
            nextCell.setCellStyle(style);
            nextCell.setCellValue(headersInfo[i]);
            sheet.autoSizeColumn(i, true);//列宽自适应
        }
    }
    
    /**
     * 载入List数据
     * @author liu.xiangfei
     * 2016年9月29日 上午11:10:52
     * @param headersInfo
     * @param list
     * @param workbook
     * @param sheet
     * @param rowIndex
     */
    public static void outputColumns(String[] headersInfo,List<Map<String,Object>> list,HSSFWorkbook workbook,HSSFSheet sheet,int rowIndex){
        // 列名样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            HSSFRow nextRow = sheet.createRow(rowIndex+i);
            for(int j = 0; j<headersInfo.length;j++){
                HSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = map.get(headersInfo[j]);
                nextCell.setCellValue(value==null?"":value.toString());
            }
        }
    }
    
    /**
     * 载入dto类型数组数据
     * @author liu.xiangfei
     * 2016年9月29日 上午11:13:07
     * @param headersInfo
     * @param objs
     * @param workbook
     * @param sheet
     * @param rowIndex
     */
    public static void outputColumnsByDtos(String[] headersInfo,Object[] objs,HSSFWorkbook workbook,HSSFSheet sheet,int rowIndex){
        // 列名样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        for(int i=0;i<objs.length;i++){
            Object obj = objs[i];
            HSSFRow nextRow = sheet.createRow(rowIndex+i);
            for(int j = 0; j<headersInfo.length;j++){
                HSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = getFiledValueByName(headersInfo[j],obj);
                nextCell.setCellValue(value==null?"":value.toString());
            }
        }
    }
    /**
     * 动态合并表头单元格，headnum为合并单元格的起始和结束的rowindex和colindex
     * @author liu.xiangfei
     * 2016年9月29日 上午11:14:48
     * @param headNum
     * @param sheet
     */
    public static void outputHeadersMerge(String[] headNum,HSSFSheet sheet){
        //动态合并单元格
        for (int i = 0; i < headNum.length; i++) {
            String[] temp = headNum[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
    }
    
    public static void outputHeadersNew(String[] headersInfo,XSSFWorkbook workbook,XSSFSheet sheet,int rowIndex){
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        XSSFRow row = sheet.createRow(rowIndex);
        for(int i = 0;i<headersInfo.length;i++){
            XSSFCell nextCell = row.createCell(i);
            nextCell.setCellStyle(style);
            nextCell.setCellValue(headersInfo[i]);
            sheet.autoSizeColumn(i, true);//列宽自适应
        }
    }
    

    
    public static void outputColumnsNew(String[] headersInfo,List<Map<String,Object>> list,XSSFWorkbook workbook,XSSFSheet sheet,int rowIndex){
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            XSSFRow nextRow = sheet.createRow(rowIndex+i);
            for(int j = 0; j<headersInfo.length;j++){
                XSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = map.get(headersInfo[j]);
                nextCell.setCellValue(value==null?"":value.toString());
            }
        }
    }
    
    public static void outputHeadersMergeNew(String[] headNum,XSSFSheet sheet){
        //动态合并单元格
        for (int i = 0; i < headNum.length; i++) {
            String[] temp = headNum[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
    }
    
    public static void outputColumnsByDtosNew(String[] headersInfo,Object[] objs,XSSFWorkbook workbook,XSSFSheet sheet,int rowIndex){
        // 列名样式
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);// 字体大小
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        style.setFont(font);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        style.setLocked(true);
        
        for(int i=0;i<objs.length;i++){
            Object obj = objs[i];
            XSSFRow nextRow = sheet.createRow(rowIndex+i);
            for(int j = 0; j<headersInfo.length;j++){
                XSSFCell nextCell = nextRow.createCell(j);
                nextCell.setCellStyle(style);
                Object value = getFiledValueByName(headersInfo[j],obj);
                nextCell.setCellValue(value==null?"":value.toString());
            }
        }
    }
    
    public static Object getFiledValueByName(String filedName,Object obj){
        String firstLetter = filedName.substring(0,1).toUpperCase();
        String getter = "get"+firstLetter+filedName.substring(1);
        try {
            Method method = obj.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(obj, new Object[]{});
            return value;
        } catch (Exception e) {
            logger.error("没有该属性"+filedName);
            return null;
        }
    }
}

动态合并单元格怎么使用比较难以理解，现在提供下示例

package com.ztesoft.iom.order.client;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ztesoft.iom.common.ExportUtils;
import com.ztesoft.iom.order.dto.ResXmlDataDto;

public class Test {

	public static void main(String[] args) {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet2 = workbook.createSheet("清查工单统计");
		String[] titleArr21 = {"地市","清查工单总数","清查成功总数","清查失败总数","清查在途数","清查失败","清查失败","清查在途数","清查在途数","清查在途数","清查在途数","清查在途数"};
		String[] titleArr22 = {"地市","清查工单总数","清查成功总数","清查失败总数","清查在途数","自动激活失败","自动激活成功且外线清查失败","资源配置","自动激活","装机","自动激活（拆）","资源配置（拆）"};
		String[] headNum ={"0,1,0,0","0,1,1,1,","0,1,2,2","0,1,3,3","0,1,4,4","0,0,5,6","0,0,7,11"};
	    ExportUtils.outputHeadersNew(titleArr21, workbook, sheet2, 0);//初始化表头
	    ExportUtils.outputHeadersNew(titleArr22, workbook, sheet2, 1);//初始化表头
	    ExportUtils.outputHeadersMergeNew(headNum, sheet2);//动态合并单元格
		//创建一个文件
		File file = new File("e:/poi_test.xlsx");
		try {
			file.createNewFile();
			//将Excel内容存盘
			FileOutputStream stream = FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

表头效果图：