POI 生成表格通用方法

2019独角兽企业重金招聘Python工程师标准>>>

import java.util.Collection;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

[@Service](https://my.oschina.net/service)
public class ExcelExportServiceImpl implements ExcelExportService {

@Autowired
HttpServletResponse response;

    [@Override](https://my.oschina.net/u/1162528)
public <T> void excelData(String title, String[] headers, String[] colums, Collection<T> dataset)
		throws Exception {
	ExcelUtils.exportExcelData(title, headers, colums, dataset,response);
   	response.setCharacterEncoding("UTF-8");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8");

}

import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
private static String getName = "get";

private static String dataFormat = "yyyy-MM-dd";

public static <T> void exportExcelData(String title, String[] headers, String[] colums, Collection<T> dataset,HttpServletResponse response)
		throws Exception {
XSSFWorkbook workBook = null;

	try {
		// 创建一个workbook 对应一个excel应用文件
		workBook = new XSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workBook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);

		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < headers.length; i++) {
			cell = headRow.createCell(i);
			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}


		// 构建表体数据
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			XSSFRow bodyRow = sheet.createRow(index);
			T t = (T) it.next();

			for (int i = 0; i < colums.length; i++) {
				cell = bodyRow.createCell(i);

				String fieldName = colums[i];
				String getMethodName = getName + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

				Class<? extends Object> tCls = t.getClass();
				Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
				Object value = getMethod.invoke(t, new Object[] {});

				String textValue = null;
				if (value instanceof Date) {
					Date date = (Date) value;
					SimpleDateFormat sdf = new SimpleDateFormat(dataFormat);
					textValue = sdf.format(date);
				} else if (value != null) {
					// 其它数据类型都当作字符串简单处理
					textValue = value.toString();
				} else {
					textValue = " ";
				}

				cell.setCellValue(textValue);
			}
		}

		workBook.write(response.getOutputStream());
	} finally {
		if (workBook != null) {
			workBook.close();
		}
	}
}

}

转载于:https://my.oschina.net/u/2611678/blog/1612551