POI-Excel生成------JAVA实现Excel表单数据导出功能

1、一些常量

package com.dsanjun.poi.constant;

/**
 * 常量
 * 
 * @author dyw
 * @date 2019年9月1日
 */
public class Constants {
	/**
	 * 07版excel后缀名
	 */
	public static String EXCEL_SUFFIX_07 = "xlsx";
	/**
	 * 03版excel后缀名
	 */
	public static String EXCEL_SUFFIX_03 = "xls";
}

2、Excel后缀名错误异常

package com.dsanjun.poi.exception;

/**
 * Excel后缀名错误异常
 * 
 * @author dyw
 *
 */
public class NoSuchExcelSuffixException extends RuntimeException {
	private static final long serialVersionUID = 7849681800154791175L;
	public static final String MESSAGE = "Excel form suffix name error";

	public NoSuchExcelSuffixException() {
		super(MESSAGE);
	}
}

3、列标题对象

package com.dsanjun.poi.create;

/**
 * 列标题
 * 
 * @author dyw
 * @date 2019年9月3日
 */
public class Title {
	private Integer index;
	private String name;

	public Integer getIndex() {
		return index;
	}

	public void setIndex(Integer index) {
		this.index = index;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

4、Excel表格数据对象

package com.dsanjun.poi.create;

import java.util.List;
import java.util.Map;

/**
 * Excel表格数据对象
 * 
 * @author dyw
 * @date 2019年9月3日
 */
public class ExcelData {
	/** 表头 */
	private List<Title> titleList;
	/** 表单数据 */
	private List<Map<String, Object>> content;

	public List<Title> getTitleList() {
		return titleList;
	}

	public void setTitleList(List<Title> titleList) {
		this.titleList = titleList;
	}

	public List<Map<String, Object>> getContent() {
		return content;
	}

	public void setContent(List<Map<String, Object>> content) {
		this.content = content;
	}
}

5、创建表单(链式构造器)

package com.dsanjun.poi.create;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.XSSFWorkbook;
import com.dsanjun.poi.constant.Constants;
import com.dsanjun.poi.exception.NoSuchExcelSuffixException;

/**
 * 创建表单(链式构造器)
 * 
 * @author dyw
 * @date 2019年9月2日
 */
public class ExcelCreater {
	/**
	 * POI-Workbook表单对象
	 */
	private Workbook workbook;

	/**
	 * 构造完成
	 * 
	 * @return Workbook
	 */
	public Workbook create() {
		return workbook;
	}

	/**
	 * 创建行数据(当ExcelData不包含行数据时，只创建表头)
	 */
	public static void createRows(Sheet sheet, ExcelData excelData) {
		List<Title> titleList = excelData.getTitleList();
		List<Map<String, Object>> content = excelData.getContent();
		createExcelTilte(sheet, titleList);
		if (content != null && content.size() > 0) {
			for (int i = 0; i < content.size(); i++) {
				createRow(sheet, i + 1, titleList, content.get(i));
			}
		}
	}

	/**
	 * 创建表头
	 * 
	 * @param sheet
	 * @param titleList
	 */
	public static void createExcelTilte(Sheet sheet, List<Title> titleList) {
		Row titleRow = sheet.createRow(0);
		for (Title title : titleList) {
			Cell titleCell = titleRow.createCell(title.getIndex());
			titleCell.setCellValue(title.getName());
		}
	}

	/**
	 * 创建指定sheet的指定行
	 * 
	 * @param sheet
	 * @param titleList
	 * @param rowData
	 */
	public static void createRow(Sheet sheet, int rowIndex, List<Title> titleList, Map<String, Object> rowData) {
		for (int i = 0; i < rowData.size(); i++) {
			Row contentRow = sheet.createRow(rowIndex);
			for (int j = 0; j < titleList.size(); j++) {
				Title headTitle = titleList.get(j);
				String headerName = headTitle.getName();
				Object data = rowData.get(headerName);
				if (data instanceof String) {
					contentRow.createCell(j).setCellValue((String) data);
				} else if (data instanceof Double) {
					contentRow.createCell(j).setCellValue((Double) data);
				} else if (data instanceof Date) {
					String formatStr = "yyyy年MM月dd日 HH:mm:ss";
					SimpleDateFormat format = new SimpleDateFormat(formatStr);
					contentRow.createCell(j).setCellValue(format.format((Date) data));
				} else if (data instanceof Long) {
					contentRow.createCell(j).setCellValue((Long) data);
				} else if (data instanceof Float) {
					contentRow.createCell(j).setCellValue((Float) data);
				} else if (data instanceof Integer) {
					contentRow.createCell(j).setCellValue((Integer) data);
				}
			}
		}
	}

	/**
	 * 指定sheetname创建sheet
	 * 
	 * @param sheetname
	 * @return
	 */
	public ExcelCreater initSheet(String sheetname) {
		workbook.createSheet(sheetname);
		return this;
	}

	/**
	 * 使用默认sheetname创建sheet
	 * 
	 * @return
	 */
	public ExcelCreater initSheet() {
		workbook.createSheet();
		return this;
	}

	/**
	 * 根据后缀名创建Workbook
	 * 
	 * @param suffix 后缀（xlsx，xls）
	 * @return Workbook
	 */
	public ExcelCreater initWorkbook(String suffix) {
		if (!Constants.EXCEL_SUFFIX_03.equals(suffix) && !Constants.EXCEL_SUFFIX_07.equals(suffix)) {
			throw new NoSuchExcelSuffixException();
		}
		if (Constants.EXCEL_SUFFIX_03.equals(suffix)) {
			workbook = new HSSFWorkbook();
		}
		if (Constants.EXCEL_SUFFIX_07.equals(suffix)) {
			workbook = new XSSFWorkbook();
		}
		return this;
	}
}

6、对外工具

package com.dsanjun.poi;

import org.apache.poi.ss.usermodel.Workbook;
import com.dsanjun.poi.create.ExcelCreater;
import com.dsanjun.poi.create.ExcelData;

/**
 * 对外工具
 * 
 * @author dyw
 * @date 2019年9月5日
 */
public class POIExcelUtils {

	/**
	 * 创建Excel工作簿
	 * 
	 * @param suffix    文件后缀
	 * @param excelData 表单数据对象
	 * @return Workbook
	 */
	public static Workbook workbook(String suffix, ExcelData excelData) {
		Workbook workbook = new ExcelCreater().initWorkbook(suffix).initSheet().create();
		ExcelCreater.createRows(workbook.getSheetAt(0), excelData);
		return workbook;
	}
}