JAVA使用POI写入excel 工具类【通用】

使用写入excel工具类的例子

说明:改工具类是网上公开的，来源现在找不着了，我只是在原基础上修改了下，抽离了泛型。如有侵权，请通知博主

工具类代码:

package com.system.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * Author: laijieguan
 */
public abstract class ExcelWriter<T> {

	public List<String> CELL_HEADS = new ArrayList<String>(); // 列头

//	static {
//		// 类装载时就载入指定好的列头信息，如有需要，可以考虑做成动态生成的列头
//		CELL_HEADS = new ArrayList<>();
//		CELL_HEADS.add("姓名");
//		CELL_HEADS.add("年龄");
//		CELL_HEADS.add("居住城市");
//		CELL_HEADS.add("职业");
//	}

	/**
	 * 生成Excel并写入数据信息,无模板的读取方式
	 * 
	 * @param dataList 数据列表
	 * @return 写入数据后的工作簿对象
	 * @throws IOException 
	 */
	public Workbook exportData(List<T> dataList,String type) {
		Workbook workbook =null;
		if("xlsx".equals(type)) {
		// 生成xlsx的Excel
		workbook = new XSSFWorkbook();
		}
		else if("xls".equals(type)) {
		// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
		workbook = new HSSFWorkbook();
		}
		Sheet sheet = null;
		//判断是否需要生成第一行的数据头信息
		if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头
		 sheet = buildDataSheet(workbook);
		}else {//不要生成
		 sheet = workbook.getSheetAt(0);
		}
		// 构建每行的数据内容
		int rowNum = 1;
		for (Iterator<T> it = dataList.iterator(); it.hasNext();) {
			T data = it.next();
			if (data == null) {
				continue;
			}
			// 输出行数据
			Row row = sheet.createRow(rowNum++);
			convertDataToRow(data, row);
		}
		return workbook;
	}

	
	
	/**
	 * 生成Excel并写入数据信息,有模板的读取方式
	 * 
	 * @param dataList 数据列表
	 * @return 写入数据后的工作簿对象
	 * @throws IOException 
	 */
	public Workbook exportDataTem(List<T> dataList,String templateName) throws Exception {
		Workbook workbook =null;
		FileInputStream fis = new FileInputStream(templateName);
		if(templateName.endsWith("xlsx")) {
		// 生成xlsx的Excel
		workbook = new XSSFWorkbook(fis);
		}
		else if(templateName.endsWith("xls")) {
		// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
		workbook = new HSSFWorkbook(fis);
		}
		Sheet sheet = null;
		//判断是否需要生成第一行的数据头信息
		if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头
		 sheet = buildDataSheet(workbook);
		}else {//不要生成
		 sheet = workbook.getSheetAt(0);
		}
		// 构建每行的数据内容
		int rowNum = 1;
		for (Iterator<T> it = dataList.iterator(); it.hasNext();) {
			T data = it.next();
			if (data == null) {
				continue;
			}
			// 输出行数据
			Row row = sheet.createRow(rowNum++);
			convertDataToRow(data, row);
		}
		return workbook;
	}

	/**
	 * 生成sheet表，并写入第一行数据（列头）
	 * 
	 * @param workbook 工作簿对象
	 * @return 已经写入列头的Sheet
	 */
	public Sheet buildDataSheet(Workbook workbook) {
		Sheet sheet = workbook.createSheet();
		// 设置列头宽度
		for (int i = 0; i < CELL_HEADS.size(); i++) {
			sheet.setColumnWidth(i, 4000);
		}
		// 设置默认行高
		sheet.setDefaultRowHeight((short) 400);
		// 构建头单元格样式
		//CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
		// 写入第一行各列的数据
		Row head = sheet.createRow(0);
		for (int i = 0; i < CELL_HEADS.size(); i++) {
			Cell cell = head.createCell(i);
			cell.setCellValue(CELL_HEADS.get(i));
			//cell.setCellStyle(cellStyle);
		}
		return sheet;
	}

	/**
	 * 设置第一行列头的样式
	 * 
	 * @param workbook 工作簿对象
	 * @return 单元格样式对象
	 */
	/*
	 * private static CellStyle buildHeadCellStyle(Workbook workbook) { CellStyle
	 * style = workbook.createCellStyle(); // 对齐方式设置
	 * style.setAlignment(HorizontalAlignment.CENTER); // 边框颜色和宽度设置
	 * style.setBorderBottom(BorderStyle.THIN);
	 * style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
	 * style.setBorderLeft(BorderStyle.THIN);
	 * style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
	 * style.setBorderRight(BorderStyle.THIN);
	 * style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
	 * style.setBorderTop(BorderStyle.THIN);
	 * style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框 // 设置背景颜色
	 * style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	 * style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 粗体字设置 Font font =
	 * workbook.createFont(); font.setBold(true); style.setFont(font); return style;
	 * }
	 */

	
	/**
	 * 将数据转换成行 抽离泛型
	 * 
	 * @param data 源数据
	 * @param row  行对象
	 * @return
	 */
	public abstract void convertDataToRow(T data, Row row);
}

现在有一个实体类是这样的

public class Country {
    /**
     * 国别代码
     */
    private String countrycode;

    /**
     * 国别中文简称
     */
    private String countryname;

    /**
     * 国别英文简称
     */
    private String countryenname;

    /**
     * 国别英文缩写
     */
    private String countryenshort;

    /**
     * 优普税率
     */
    private String applytaxtype;

    /**
     * 国别描述
     */
    private String countrydesc;

    /**
     * 备注
     */
    private String comments;

    /**
     * 状态
     */
    private String states;

    /**
     * 最终目的国别代码
     */
    private String lastcountrycode;

    
    public String getCountrycode() {
        return countrycode;
    }

    
    public void setCountrycode(String countrycode) {
        this.countrycode = countrycode == null ? null : countrycode.trim();
    }

    
    public String getCountryname() {
        return countryname;
    }

   
    public void setCountryname(String countryname) {
        this.countryname = countryname == null ? null : countryname.trim();
    }

   
    public String getCountryenname() {
        return countryenname;
    }

   
    public void setCountryenname(String countryenname) {
        this.countryenname = countryenname == null ? null : countryenname.trim();
    }

    
    public String getCountryenshort() {
        return countryenshort;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column WL_COUNTRY.COUNTRYENSHORT
     *
     * @param countryenshort the value for WL_COUNTRY.COUNTRYENSHORT
     *
     * @mbg.generated Fri Jul 10 13:39:12 CST 2020
     */
    public void setCountryenshort(String countryenshort) {
        this.countryenshort = countryenshort == null ? null : countryenshort.trim();
    }
    public String getApplytaxtype() {
        return applytaxtype;
    }
    public void setApplytaxtype(String applytaxtype) {
        this.applytaxtype = applytaxtype == null ? null : applytaxtype.trim();
    }
    public String getCountrydesc() {
        return countrydesc;
    }
    public void setCountrydesc(String countrydesc) {
        this.countrydesc = countrydesc == null ? null : countrydesc.trim();
    }
    public String getComments() {
        return comments;
    }
    public void setComments(String comments) {
        this.comments = comments == null ? null : comments.trim();
    }

    public String getStates() {
        return states;
    }
    public void setStates(String states) {
        this.states = states == null ? null : states.trim();
    }
    public String getLastcountrycode() {
        return lastcountrycode;
    }
    public void setLastcountrycode(String lastcountrycode) {
        this.lastcountrycode = lastcountrycode == null ? null : lastcountrycode.trim();
    }

	@Override
	public String toString() {
		return "Country [countrycode=" + countrycode + ", countryname=" + countryname + ", countryenname="
				+ countryenname + ", countryenshort=" + countryenshort + ", applytaxtype=" + applytaxtype
				+ ", countrydesc=" + countrydesc + ", comments=" + comments + ", states=" + states
				+ ", lastcountrycode=" + lastcountrycode + "]";
	}
    
}

继承工具类

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.system.util.ExcelWriter;
import com.wl.differentCountries.entity.Country;

public class CountryExcelWriter extends ExcelWriter<Country> {

	@Override
	public void convertDataToRow(Country data, Row row) {
		int cellNum = 0;
		Cell cell;
		// 国别代码
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getCountrycode() ? "" : data.getCountrycode());
		// 国别中文简称
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getCountryname() ? "" : data.getCountryname());

		// 国别英文简称
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getCountryenname() ? "" : data.getCountryenname());

		// 国别英文缩写
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getCountryenshort() ? "" : data.getCountryenshort());

		// 优普税率
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getApplytaxtype() ? "" : data.getApplytaxtype());

		// 国别描述
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getCountrydesc() ? "" : data.getCountrydesc());

		// 备注
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getComments() ? "" : data.getComments());

		// 状态
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getStates() ? "" : data.getStates());

		// 最终目的国别代码
		cell = row.createCell(cellNum++);
		cell.setCellValue(null == data.getLastcountrycode() ? "" : data.getLastcountrycode());
		
	}

}

实际使用场景

	public void exportCountryData(Map<String, Object> queryMap, OutputStream os) {
		try {
            //new一个工具类，喜欢做成静态的也行，你们自己修改修改
			CountryExcelWriter countryExcelWriter = new CountryExcelWriter();
            //查询出符合业务的数据
			List<Country> dataList = wlCountryMapper.getCountrys(queryMap);
			PathUtils pathUtils = new PathUtils();
            //获取模板的全路径
			String tempalte = pathUtils.getWEBINFPath() + "resource"  + File.separator
					+ "countryList.xlsx";
            //把查询出来的数据传入，把模板全路径传入
			Workbook wb = countryExcelWriter.exportDataTem(dataList, tempalte);
            //写到对应的输出流，一般来说都是response的流，用于下载。看各自的业务了
			wb.write(os);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}