java poi excel 导出_java poi Excel文件导出工具类

一、基本工具类

/**

* otoc.cn ltd.

* Copyright (c) 2016-2018 All Rights Reserved.

*/

package com.aibton;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.BorderStyle;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.VerticalAlignment;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.util.RegionUtil;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.util.List;

/**

* poi生成excel文件工具类

*

* @author huzhihui

* @version $: v 0.1 2018 2018/2/23 14:16 huzhihui Exp $$

*/

public class PoiExcelUtils {

private static final Logger LOGGER = LoggerFactory.getLogger(PoiExcelUtils.class);

/**

* 创建Excel文件

*

* @param outputStream

* @param sheetMain

* @return

* @throws Exception

*/

public static OutputStream createExcelFile(OutputStream outputStream, SheetMain sheetMain) throws Exception {

int headWidth = 0;

int contentHeight = 0;

int currentRow = 0;

//--数据检查

if (null == sheetMain) {

throw new RuntimeException("创建EXCEL文件-主对象为空");

}

if (null == sheetMain.getSheetTitle()) {

LOGGER.warn("创建EXCEL文件-主标题为空");

}

if (null == sheetMain.getSheetHeads() || sheetMain.getSheetHeads().size() == 0) {

LOGGER.warn("创建EXCEL文件-列标题为空");

} else {

headWidth = sheetMain.getSheetHeads().size();

}

if (null == sheetMain.getTs() || sheetMain.getTs().size() == 0) {

LOGGER.warn("创建EXCEL文件-列内容为空");

} else {

contentHeight = sheetMain.getTs().size();

}

//1.创建工作簿

HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

//1.1创建合并单元格对象

CellRangeAddress callRangeAddress = null;

if (null != sheetMain.getSheetTitle()) {

callRangeAddress = new CellRangeAddress(sheetMain.getStartRow(), sheetMain.getStartRow(), sheetMain.getStartCell(), (headWidth + sheetMain.getStartCell()) - (headWidth == 0 ? 0 : 1));//起始行,结束行,起始列,结束列

}

//1.2头标题样式

HSSFCellStyle headStyle = createHSSFCellStyle(hssfWorkbook, "宋体", sheetMain.getTitleCellStyle().getFontSize(), sheetMain.getTitleCellStyle().isFontBold(), sheetMain.getTitleCellStyle().isBorderFlag(), sheetMain.getTitleCellStyle().isAlignmentCenter(), sheetMain.getTitleCellStyle().isVerticalAlignmentCenter());

//1.3列标题样式

HSSFCellStyle colHeadStyle = createHSSFCellStyle(hssfWorkbook, "宋体", sheetMain.getHeadCellStyle().getFontSize(), sheetMain.getHeadCellStyle().isFontBold(), sheetMain.getHeadCellStyle().isBorderFlag(), sheetMain.getHeadCellStyle().isAlignmentCenter(), sheetMain.getHeadCellStyle().isVerticalAlignmentCenter());

//1.4列样式

HSSFCellStyle colStyle = createHSSFCellStyle(hssfWorkbook, "宋体", sheetMain.gettCellStyle().getFontSize(), sheetMain.gettCellStyle().isFontBold(), sheetMain.gettCellStyle().isBorderFlag(), sheetMain.gettCellStyle().isAlignmentCenter(), sheetMain.gettCellStyle().isVerticalAlignmentCenter());

//1.4列样式

HSSFCellStyle colFooterStyle = createHSSFCellStyle(hssfWorkbook, "宋体", sheetMain.getFooterCellStyle().getFontSize(), sheetMain.getFooterCellStyle().isFontBold(), sheetMain.getFooterCellStyle().isBorderFlag(), sheetMain.getFooterCellStyle().isAlignmentCenter(), sheetMain.getFooterCellStyle().isVerticalAlignmentCenter());

//2.创建工作表

HSSFSheet sheet = hssfWorkbook.createSheet(sheetMain.getSheetName());

//2.1加载合并单元格对象

if (headWidth != 0 && null != sheetMain.getSheetTitle()) {

sheet.addMergedRegion(callRangeAddress);

}

//设置默认列宽

if (null != sheetMain.getCellWidths() && sheetMain.getCellWidths().size() != 0) {

for (int i = 0; i < sheetMain.getCellWidths().size(); i++) {

sheet.setColumnWidth(i, ((int) sheetMain.getCellWidths().get(i)) * 256);

}

} else {

sheet.setDefaultColumnWidth(15);

}

//3.创建行

//3.1创建头标题行;并且设置头标题

HSSFRow row = sheet.createRow(sheetMain.getStartRow());

HSSFCell cell = row.createCell(sheetMain.getStartCell());

//加载单元格样式

cell.setCellStyle(headStyle);

if (null != sheetMain.getSheetTitle()) {

cell.setCellValue(sheetMain.getSheetTitle());

}

if (sheetMain.getTitleCellStyle().isBorderFlag()) {

RegionUtil.setBorderBottom(BorderStyle.THIN, callRangeAddress, sheet); // 下边框

RegionUtil.setBorderLeft(BorderStyle.THIN, callRangeAddress, sheet); // 左边框

RegionUtil.setBorderRight(BorderStyle.THIN, callRangeAddress, sheet); // 有边框

RegionUtil.setBorderTop(BorderStyle.THIN, callRangeAddress, sheet); // 上边框

}

if (null != sheetMain.getSheetTitle()) {

currentRow = currentRow + 1;

}

//3.2创建列标题;并且设置列标题

if (null != sheetMain.getSheetHeads() && sheetMain.getSheetHeads().size() != 0) {

HSSFRow row2 = sheet.createRow(currentRow);

String[] titles = (String[]) sheetMain.getSheetHeads().toArray();

for (int i = 0; i < titles.length; i++) {

HSSFCell cell2 = row2.createCell(sheetMain.getStartCell() + i);

//加载单元格样式

cell2.setCellStyle(colHeadStyle);

cell2.setCellValue(titles[i]);

}

}

//4创建列表内容

if (null != sheetMain.getSheetHeads() && sheetMain.getSheetHeads().size() != 0) {

currentRow = currentRow + 1;

}

List ts = sheetMain.getTs();

if (ts != null) {

for (int j = 0; j < ts.size(); j++) {

currentRow = currentRow + j;

HSSFRow row3 = sheet.createRow(sheetMain.getStartRow() + currentRow);

// 得到类对象

Class shellContentCla = (Class) ts.get(j).getClass();

/* 得到类中的所有属性集合 */

Field[] shellContentField = shellContentCla.getDeclaredFields();

for (int i = 0; i < shellContentField.length; i++) {

Field field = shellContentField[i];

field.setAccessible(true);

Object val = field.get(ts.get(j));

//创建数据行,前面有两行,头标题行和列标题行

HSSFCell cell1 = row3.createCell(sheetMain.getStartCell() + i);

cell1.setCellStyle(colStyle);

cell1.setCellValue("");

if (null != val) {

cell1.setCellValue(val.toString());

}

}

}

}

//5创建脚标内容

currentRow = currentRow + 1;

List sheetFooters = sheetMain.getSheetFooters();

HSSFRow row4 = sheet.createRow(currentRow);

for (int i = 0; i < sheetFooters.size(); i++) {

SheetFooter sheetFooter = sheetFooters.get(i);

HSSFCell cell1 = row4.createCell(sheetMain.getStartCell() + 2 * i);

cell1.setCellStyle(colHeadStyle);

cell1.setCellValue(sheetFooter.getName());

HSSFCell cell2 = row4.createCell(sheetMain.getStartCell() + 2 * i + 1);

cell2.setCellStyle(colFooterStyle);

cell2.setCellValue(sheetFooter.getValue());

}

//写入文件

hssfWorkbook.write(outputStream);

return outputStream;

}

/**

* 初始化行样式

*

* @param workbook 工作薄对象

* @param fontName 字体名称：宋体

* @param fontSize 字号大小：11

* @param fontBold 是否加粗：true/false

* @param borderFlag 是否边框：true/false

* @param alignmentCenter 水平居中：true/false

* @param verticalAlignmentCenter 垂直居中：true/false

* @return

*/

private static HSSFCellStyle createHSSFCellStyle(HSSFWorkbook workbook, String fontName, short fontSize, boolean fontBold, boolean borderFlag, boolean alignmentCenter, boolean verticalAlignmentCenter) {

HSSFCellStyle hssfCellStyle = workbook.createCellStyle();

//--水平居中

if (alignmentCenter) {

hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);

}

//--垂直居中

if (verticalAlignmentCenter) {

hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

}

//--创建边框

if (true == borderFlag) {

hssfCellStyle.setBorderTop(BorderStyle.THIN);

hssfCellStyle.setBorderRight(BorderStyle.THIN);

hssfCellStyle.setBorderBottom(BorderStyle.THIN);

hssfCellStyle.setBorderLeft(BorderStyle.THIN);

hssfCellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

hssfCellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

}

//--创建字体

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints(fontSize);

//--设置字体名称

font.setFontName(fontName);

if (null == fontName || fontName.trim().equals("")) {

font.setFontName("宋体");

}

//--设置是否加粗

font.setBold(fontBold);

//加载字体

hssfCellStyle.setFont(font);

return hssfCellStyle;

}

/**

* 创建Excel文件对象主体

*/

public static class SheetMain {

/**

* 开始行

*/

private int startRow = 0;

/**

* 开始列

*/

private int startCell = 0;

/**

* 工作表名称

*/

private String sheetName;

/**

* 工作表标题

*/

private String sheetTitle;

/**

* 工作表标题样式

*/

private CellStyle titleCellStyle = CellStyle.getSingleCellStyle();

/**

* 工作表头标题

*/

private List sheetHeads;

/**

* 工作表列宽度

*/

private List cellWidths;

/**

* 工作表头标题样式

*/

private CellStyle headCellStyle = CellStyle.getSingleCellStyle();

/**

* 工作表内容主体

*/

private List Ts;

/**

* 工作表内容主体样式

*/

private CellStyle tCellStyle = CellStyle.getSingleCellStyle();

/**

* 工作表页面列表对象

*/

private List sheetFooters;

/**

* 工作表脚标内容样式

*/

private CellStyle footerCellStyle = CellStyle.getSingleCellStyle();

/**

* 初始化样式

*

* @param startRow 开始行

* @param startCell 开始列

* @param cellWidths 列宽

* @param titleCellStyle 总标题样式

* @param headCellStyle 列表标题样式

* @param tCellStyle 列表样式

* @param footerCellStyle 列表脚标样式

*/

public void init(Integer startRow, Integer startCell, List cellWidths, CellStyle titleCellStyle, CellStyle headCellStyle, CellStyle tCellStyle, CellStyle footerCellStyle) {

if (null != startRow) {

this.startRow = startRow;

}

if (null != startCell) {

this.startCell = startCell;

}

this.cellWidths = cellWidths;

if (null != titleCellStyle) {

this.titleCellStyle = titleCellStyle;

}

if (null != headCellStyle) {

this.headCellStyle = headCellStyle;

}

if (null != tCellStyle) {

this.tCellStyle = tCellStyle;

}

if (null != footerCellStyle) {

this.footerCellStyle = footerCellStyle;

}

}

/**

* 创建样式对象

*

* @param fontSize

* @param fontBold

* @param borderFlag

* @param alignmentCenter

* @param verticalAlignmentCenter

* @return

*/

public static CellStyle createCellStyle(Short fontSize, Boolean fontBold, Boolean borderFlag, Boolean alignmentCenter, Boolean verticalAlignmentCenter) {

CellStyle cellStyle = new CellStyle();

if (null != fontSize) {

cellStyle.setFontSize(fontSize);

}

if (null != fontBold) {

cellStyle.setFontBold(fontBold);

}

if (null != borderFlag) {

cellStyle.setBorderFlag(borderFlag);

}

if (null != alignmentCenter) {

cellStyle.setAlignmentCenter(alignmentCenter);

}

if (null != verticalAlignmentCenter) {

cellStyle.setVerticalAlignmentCenter(verticalAlignmentCenter);

}

return cellStyle;

}

public String getSheetName() {

return sheetName;

}

public void setSheetName(String sheetName) {

this.sheetName = sheetName;

}

public String getSheetTitle() {

return sheetTitle;

}

public void setSheetTitle(String sheetTitle) {

this.sheetTitle = sheetTitle;

}

public List getSheetHeads() {

return sheetHeads;

}

public void setSheetHeads(List sheetHeads) {

this.sheetHeads = sheetHeads;

}

public List getTs() {

return Ts;

}

public void setTs(List ts) {

Ts = ts;

}

public List getSheetFooters() {

return sheetFooters;

}

public void setSheetFooters(List sheetFooters) {

this.sheetFooters = sheetFooters;

}

public int getStartRow() {

return startRow;

}

public void setStartRow(int startRow) {

this.startRow = startRow;

}

public int getStartCell() {

return startCell;

}

public void setStartCell(int startCell) {

this.startCell = startCell;

}

public CellStyle getTitleCellStyle() {

return titleCellStyle;

}

public void setTitleCellStyle(CellStyle titleCellStyle) {

this.titleCellStyle = titleCellStyle;

}

public CellStyle getHeadCellStyle() {

return headCellStyle;

}

public void setHeadCellStyle(CellStyle headCellStyle) {

this.headCellStyle = headCellStyle;

}

public CellStyle gettCellStyle() {

return tCellStyle;

}

public void settCellStyle(CellStyle tCellStyle) {

this.tCellStyle = tCellStyle;

}

public CellStyle getFooterCellStyle() {

return footerCellStyle;

}

public void setFooterCellStyle(CellStyle footerCellStyle) {

this.footerCellStyle = footerCellStyle;

}

public List getCellWidths() {

return cellWidths;

}

public void setCellWidths(List cellWidths) {

this.cellWidths = cellWidths;

}

}

/**

* 工作表页脚对象

*/

public static class SheetFooter {

/**

* 名称

*/

private String name;

/**

* 值

*/

private String value;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getValue() {

return value;

}

public void setValue(String value) {

this.value = value;

}

}

/**

* 单元格样式

*/

private static class CellStyle {

/**

* 字体大小

*/

private short fontSize = 11;

/**

* 字体名称

*/

private String fontName = "宋体";

/**

* 是否加粗

*/

private boolean fontBold = false;

/**

* 是否有边框

*/

private boolean borderFlag = false;

/**

* 是否水平居中

*/

private boolean alignmentCenter = false;

/**

* 是否垂直居中

*/

private boolean verticalAlignmentCenter = true;

private static CellStyle cellStyle = new CellStyle();

/**

* 获取到单独对象

*

* @return

*/

public static CellStyle getSingleCellStyle() {

return cellStyle;

}

public short getFontSize() {

return fontSize;

}

public void setFontSize(short fontSize) {

this.fontSize = fontSize;

}

public String getFontName() {

return fontName;

}

public void setFontName(String fontName) {

this.fontName = fontName;

}

public boolean isFontBold() {

return fontBold;

}

public void setFontBold(boolean fontBold) {

this.fontBold = fontBold;

}

public boolean isBorderFlag() {

return borderFlag;

}

public void setBorderFlag(boolean borderFlag) {

this.borderFlag = borderFlag;

}

public boolean isAlignmentCenter() {

return alignmentCenter;

}

public void setAlignmentCenter(boolean alignmentCenter) {

this.alignmentCenter = alignmentCenter;

}

public boolean isVerticalAlignmentCenter() {

return verticalAlignmentCenter;

}

public void setVerticalAlignmentCenter(boolean verticalAlignmentCenter) {

this.verticalAlignmentCenter = verticalAlignmentCenter;

}

}

}

二、使用说明

@Test

public void test02() throws Exception {

PoiExcelUtils.SheetMain sheetMain = new PoiExcelUtils.SheetMain<>();

sheetMain.init(0, 0, Arrays.asList(15, 25, 15, 15, 10, 20),

PoiExcelUtils.SheetMain.createCellStyle((short) 16, true, false, true, true),

PoiExcelUtils.SheetMain.createCellStyle((short) 11, true, false, false, true),

PoiExcelUtils.SheetMain.createCellStyle((short) 11, false, false, false, true),

PoiExcelUtils.SheetMain.createCellStyle((short) 11, false, false, false, true));

sheetMain.setSheetName("学生成绩");

sheetMain.setSheetTitle("学生成绩");

sheetMain.setSheetHeads(Arrays.asList("ID", "用户名", "账号", "所属部门", "性别", "电子邮箱"));

sheetMain.setTs(getUsers());

sheetMain.setSheetFooters(createSheetFooters());

PoiExcelUtils.createExcelFile(new FileOutputStream("D://a.xls"), sheetMain);

}