Excel工具类分享（导出报表等场景，实用！极简！）

 直接上菜：

1. pom依赖

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

2. 工具类

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

/**
 * 
 * @Description: excel处理工具 
 * 1.读取现有excel; 
 * 2.读取指定sheet； 
 * 3.找到单元格，设置格式，填充数据
 * @author reece
 * @date 2017年12月5日 下午4:44:01
 */
public class ExcelUtil {

	private ExcelUtil() {
	}

	public static ExcelUtil getInstance() {
		return new ExcelUtil();
	}

	private WorkbookWrapper wbWrapper = new WorkbookWrapper();

	private SheetWrapper sheetWrapper = new SheetWrapper();

	private RowWrapper rowWrapper = new RowWrapper();

	private CellWrapper cellWrapper = new CellWrapper();

	public WorkbookWrapper newWorkbook() {
		Workbook workbook = new XSSFWorkbook();
		wbWrapper.setWorkbook(workbook);
		return wbWrapper;
	}
	
	public WorkbookWrapper load(InputStream in) {
		try {
			Workbook workbook = WorkbookFactory.create(in);
			wbWrapper.setWorkbook(workbook);
		} catch (Exception e) {
			throw new RuntimeException("Excel读取失败！");
		}
		return wbWrapper;
	}
	
	public void export(OutputStream out) {
		try {
			wbWrapper.getWorkbook().write(out);
		} catch (Exception e) {
			throw new RuntimeException("Excel写出失败！");
		}
	}

	/**
	 * 
	 * @Description: workbook包装
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class WorkbookWrapper {

		private Workbook workbook;

		public SheetWrapper sheet(int sheetnum) {
			Sheet sheet = null;
			try {
				sheet = workbook.getSheetAt(sheetnum);
			} catch (Exception e) {
				sheet = workbook.createSheet();
				workbook.setActiveSheet(sheetnum);
			}
			sheetWrapper.setSheet(sheet);
			return sheetWrapper;
		}

		public SheetWrapper sheet(int sheetnum, String name) {
			Sheet sheet = null;
			try {
				sheet = workbook.getSheetAt(sheetnum);
			} catch (Exception e) {
				sheet = workbook.createSheet();
				workbook.setActiveSheet(sheetnum);
			}
			workbook.setSheetName(sheetnum, name);
			sheetWrapper.setSheet(sheet);
			return sheetWrapper;
		}

		public Workbook getWorkbook() {
			return workbook;
		}

		public void setWorkbook(Workbook workbook) {
			this.workbook = workbook;
		}

	}

	/**
	 * 
	 * @Description: sheet包装
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class SheetWrapper {

		private Sheet sheet;

		public RowWrapper row(int rownum) {
			Row row = sheet.getRow(rownum);
			if (row == null) {
				row = sheet.createRow(rownum);
			}
			rowWrapper.setRow(row);
			return rowWrapper;
		}
		
		public SheetWrapper columnWidth(int columnIndex, int width) {
			sheet.setColumnWidth(columnIndex, 256*width);
			return this;
		}

		public SheetWrapper insertRow(int rownum, int rows) {
			sheet.shiftRows(rownum, sheet.getLastRowNum(), rows, true, false);
			return this;
		}

		public SheetWrapper addMergeRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
			sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
			return this;
		}

		public SheetWrapper setValidation(String[] textList,int firstRow, int endRow, int firstCol, int endCol) {
			ExcelUtil.this.setValidation(sheet,textList,firstRow,endRow,firstCol,endCol);
			return this;
		}

		public WorkbookWrapper and() {
			return wbWrapper;
		}

		public Sheet getSheet() {
			return sheet;
		}

		public void setSheet(Sheet sheet) {
			this.sheet = sheet;
		}

	}

	/**
	 * 
	 * @Description: row包装
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class RowWrapper {

		private Row row;

		public CellWrapper cell(int cellnum) {
			Cell cell = row.getCell(cellnum);
			if (cell == null) {
				cell = row.createCell(cellnum);
			}
			cellWrapper.setCell(cell);
			return cellWrapper;
		}
		
		public RowWrapper setHeight(int height) {
			row.setHeight((short)(height*20));
			return this;
		}

		public RowWrapper setDefaultStyle() {
			return setStyle("宋体", 12, false);
		}

		public RowWrapper setStyle(String fontName, int fontSize, boolean bold) {
			return setStyle(fontName,(short)fontSize, bold,null,false);
		}

		public RowWrapper setStyle(String fontName, int fontSize, boolean bold, Short color,
								 boolean alignCenter) {
			return setStyle(fontName,(short)fontSize,color,null,alignCenter,false,null);
		}
		
		public RowWrapper setStyle(String fontName, Short fontSize,
									 Short fontColor, Short backgroundColor,
								 boolean alignCenter,
								 boolean bold, Short border) {
			CellStyle cellStyle = cellStyle(wbWrapper.getWorkbook(), fontName, fontSize, fontColor, backgroundColor, alignCenter,
					bold, border);
			row.setRowStyle(cellStyle);
			return this;
		}

		public SheetWrapper and() {
			return sheetWrapper;
		}

		public Row getRow() {
			return row;
		}

		public void setRow(Row row) {
			this.row = row;
		}

	}

	/**
	 * 
	 * @Description: cell包装
	 * @author reece
	 * @date 2017年12月6日 上午10:04:52
	 */
	public class CellWrapper {

		private Cell cell;

		public CellWrapper fillText(Object value) {
			if(value == null) {
				return this;
			}
			if (value instanceof String) {
				cell.setCellValue((String) value);
			}
			if (Number.class.isAssignableFrom(value.getClass())) {
				cell.setCellValue(((Number)value).doubleValue());
			}
			if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			}
			if (value instanceof Calendar) {
				cell.setCellValue((Calendar) value);
			}
			if (value instanceof Date) {
				cell.setCellValue((Date) value);
			}
			return this;
		}

		public CellWrapper setDefaultStyle() {
			return setStyle("宋体", 12, false);
		}

		public CellWrapper setStyle(String fontName, int fontSize, boolean bold) {
			return setStyle(fontName,(short)fontSize, bold,null,false);
		}

		public CellWrapper setStyle(String fontName, int fontSize, boolean bold, Short color,
								  boolean alignCenter) {
			return setStyle(fontName,(short)fontSize,color,null,alignCenter,false,null);
		}

		public CellWrapper setStyle(String fontName, Short fontSize,
									  Short fontColor, Short backgroundColor,
								  boolean alignCenter,
								  boolean bold, Short border) {
			CellStyle cellStyle = cellStyle(wbWrapper.getWorkbook(), fontName, fontSize, fontColor, backgroundColor, alignCenter,
					bold, border);
			cell.setCellStyle(cellStyle);
			return this;
		}

		/**
		 * 设置单元格数据有效性
		 */
		public CellWrapper setValidation(String[] textList) {
			ExcelUtil.this.setValidation(cell.getSheet(),textList,cell.getRowIndex(),cell.getRowIndex(),cell.getColumnIndex(),cell.getColumnIndex());
			return this;
		}

		public RowWrapper and() {
			return rowWrapper;
		}

		public Cell getCell() {
			return cell;
		}

		public void setCell(Cell cell) {
			this.cell = cell;
		}

	}

	/**
	 * 设置单元格样式 color/border
	 * @see HSSFColor
	 * @see HSSFCellStyle
	 */
	private CellStyle cellStyle(Workbook wb, String fontName, Short fontSize,
								Short fontColor, Short backgroundColor,
								boolean alignCenter,
								boolean bold, Short border) {
		// 单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		if(backgroundColor != null){
			//填充单元格
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			//填颜色
			cellStyle.setFillForegroundColor(backgroundColor);
		}
		// 字体样式
		Font fontStyle = wb.createFont();
		fontStyle.setColor(fontColor);
		fontStyle.setFontName(fontName);
		fontStyle.setFontHeightInPoints(fontSize);
		// 将字体样式添加到单元格样式中
		cellStyle.setFont(fontStyle);
		// 边框，居中
		if(alignCenter){
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		}
		if(bold){
			cellStyle.setBorderRight(border);
			cellStyle.setBorderLeft(border);
			cellStyle.setBorderRight(border);
			cellStyle.setBorderTop(border);
		}
		return cellStyle;
	}

	/**
	 * 批量设置有效值,显示下拉框.
	 */
	private void setValidation(Sheet sheet, String[] textlist,
									  int firstRow, int endRow, int firstCol, int endCol) {
		XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
		// 加载下拉列表内容
		XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
				.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
		// 数据有效性
		XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(
				dvConstraint, regions);
		sheet.addValidationData(validation);
	}

	public static void main(String[] args) throws FileNotFoundException {
		InputStream in = null;
		try {
			in = new BufferedInputStream(new FileInputStream(new File("D:\\test.xlsx")));
			File outfile = new File("D:\\test_out.xlsx");
			if (!outfile.exists()) {
				outfile.createNewFile();
			}
			OutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));
			ExcelUtil excelUtil = ExcelUtil.getInstance();
			SheetWrapper sheetWrapper = excelUtil.newWorkbook().sheet(0, "测试");

			String[] textlist = { "列表1", "列表2", "列表3", "列表4", "列表5" };
			sheetWrapper.row(0).cell(0).setValidation(textlist);
			sheetWrapper.row(1)
					.cell(0)
					.setStyle("宋体",(short)12, HSSFColor.RED.index,HSSFColor.YELLOW.index,true,true,HSSFCellStyle.BORDER_THIN)
					.fillText("测试样式");

			excelUtil.export(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		System.out.println("处理完毕！");
	}

}

3. 使用说明

        项目中经常遇到的是将一些数据导出到指定excel模板中，笔者在开发过程中也经常遇到这类需求，于是干脆整理了一个工具类专门处理此类需求。

        大致使用方式见main方法，有几点需要说明：1.该工具类仅处理2013以上版本excel，不支持老版本。（当然，实在要用，自己稍微做点改造）；2.常用方式是手动创建好excel空模板，这样一些excel头部样式就不用通过程序去设置了，使用该工具类去创建新的行，合并单元格，填充数据等；3.可以指定任意输出流，可以输出到本地，也可以直接输出到阿里云OSS等，不需要存储到本地后再上传。

        由于个人水平有限，有任何问题，欢迎评论斧正。