package com.demo.excel.utils;
import com.demo.excel.Vo.ExportTitleInfo;
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* 导出Excel工具类
*
*/
public class ExcelExportUtil {
/**
* 导出Excel
*
*/
public static <T> void Export(String[] titles, List<T> list, String filePath) {
// 创建一个workbook 对应一个excel应用文件
HSSFWorkbook workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
// Sheet名称,可以自定义中文名称
HSSFSheet sheet = workBook.createSheet("Sheet1");
ExcelInternalUtil exportUtil = new ExcelInternalUtil(workBook, sheet);
HSSFCellStyle headStyle = exportUtil.getHeadStyle();
HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
// 构建表头
HSSFRow headRow = sheet.createRow(0);
HSSFCell cell = null;
// 输出标题
for (int i = 0; i < titles.length; i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
// 构建表体数据
for (int i = 0; i < list.size(); i++) {
// 创建行
HSSFRow bodyRow = sheet.createRow(i + 1);
sheet.autoSizeColumn(i + 1);
// Object item = list.get(j);
Class<?> bean = list.get(i).getClass();
Field[] fields = bean.getDeclaredFields();
int columnIndex = 0;
for (int j = 0; j < fields.length; j++) {
Field f = fields[j];
f.setAccessible(true); // 设置些属性是可以访问的
// String name = f.getName();
// Class type = f.getType();
Object val = null;
try {
// 这里需要用list.get(i),如果用bean则会抛类型转换异常
val = f.get(list.get(i));
} catch (Exception e) {
e.printStackTrace();
}
if (val == null)
continue;
cell = bodyRow.createCell(columnIndex);
cell.setCellStyle(bodyStyle);
cell.setCellValue(val.toString());
columnIndex++;
/*
* cell = bodyRow.createCell(0); cell.setCellStyle(bodyStyle);
* cell.setCellValue(user.getLastIp());
*
* cell = bodyRow.createCell(1); cell.setCellStyle(bodyStyle);
* cell.setCellValue(user.getLastVisit());
*
* cell = bodyRow.createCell(2); cell.setCellStyle(bodyStyle);
* cell.setCellValue(user.getPassword());
*
* cell = bodyRow.createCell(3); cell.setCellStyle(bodyStyle);
* cell.setCellValue(user.getUserName());
*
* cell = bodyRow.createCell(4); cell.setCellStyle(bodyStyle);
* cell.setCellValue(user.getUserId());
*/
}
}
exportFile(filePath, workBook);
}
/**
* 导出Excel
*
* @param 字段名和字段描述对应关系
* @param 数据列表
* @param 导出路径
* @throws Exception
*/
public static <T> void Export(List<ExportTitleInfo> titles, List<T> list, String filePath,
String sheetName) throws Exception {
HSSFWorkbook workBook = getWorkBook(titles, list, sheetName);
exportFile(filePath, workBook);
}
/**
* 导出Excel
*
* @param 字段名和字段描述对应关系
* @param 数据列表
* @param 导出路径
* @throws Exception
*/
public static <T> HSSFWorkbook Export(List<ExportTitleInfo> titles, List<T> list,
String sheetName) throws Exception {
HSSFWorkbook workBook = getWorkBook(titles, list, sheetName);
return workBook;
}
public static <T> HSSFWorkbook Export(List<LinkedHashMap<String, String>> list, String sheetName)
throws Exception {
HSSFWorkbook workBook = getReportWorkBook(list, sheetName);
return workBook;
}
/** 获取Workbook对象 */
private static <T> HSSFWorkbook getReportWorkBook(List<LinkedHashMap<String, String>> list,
String sheetName) throws NoSuchFieldException, IllegalAccessException {
// 创建一个workbook 对应一个excel应用文件
HSSFWorkbook workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
// Sheet名称,可以自定义中文名称
HSSFSheet sheet = workBook.createSheet(sheetName);
ExcelInternalUtil exportUtil = new ExcelInternalUtil(workBook, sheet);
HSSFCellStyle headStyle = exportUtil.getHeadStyle();
HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
HSSFCell cell = null;
// 输出标题
int iRow = 0, iCol = 0;
// cell.setCellValue(subItem.getValue());
for (LinkedHashMap<String, String> item : list) {
// 创建行
HSSFRow bodyRow = sheet.createRow(iRow);
for (Map.Entry<String, String> subItem : item.entrySet()) {
sheet.autoSizeColumn(iCol); // 列宽自适应内容
cell = bodyRow.createCell(iCol);
if (0 == iRow)
cell.setCellStyle(headStyle);
else
cell.setCellStyle(bodyStyle);
String val = subItem.getValue();
if (val == null)
val = ""; // val为null时,默认给空字符串
cell.setCellValue(val);
iCol++;
}
iRow++;
iCol = 0;
}
return workBook;
}
/**
* 获取Workbook对象
*
*/
private static <T> HSSFWorkbook getWorkBook(List<ExportTitleInfo> titles, List<T> list,
String sheetName) throws NoSuchFieldException, IllegalAccessException {
// 创建一个workbook 对应一个excel应用文件
HSSFWorkbook workBook = new HSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
// Sheet名称,可以自定义中文名称
HSSFSheet sheet = workBook.createSheet(sheetName);
ExcelInternalUtil exportUtil = new ExcelInternalUtil(workBook, sheet);
HSSFCellStyle headStyle = exportUtil.getHeadStyle();
HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
// 构建表头
HSSFRow titleRow = sheet.createRow(0);
HSSFCell cell = null;
// 输出标题
for (int i = 0; i < titles.size(); i++) {
// sheet.autoSizeColumn(i);
cell = titleRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles.get(i).getTitleName());
}
// 构建表体数据
for (int i = 0; i < list.size(); i++) {
// 创建行
HSSFRow bodyRow = sheet.createRow(i + 1);
Class<?> bean = list.get(i).getClass();
for (int j = 0; j < titles.size(); j++) {
sheet.autoSizeColumn(j); // 列宽自适应内容
cell = bodyRow.createCell(j);
Field f = bean.getDeclaredField(titles.get(j).getFieldName()); // 获取字段名称
if (f == null) {
continue;
}
f.setAccessible