/**
* 创建Excel,支持.xls格式,当数据小于30000条,该方法完全没问题
* 最大支持65535条数据,Excel2003最大支持数据条数
* @param title 表名称
* @param head 数据标题
* @param list 数据集合
* @return Excel
*/
public static HSSFWorkbook createExcel(String title, Map<String, String> head, List list){
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = workbook.createSheet(title);
//添加表头
HSSFRow row = sheet.createRow(0);
//设置单元格格式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//添加表头内容
List<String> cells = createHeadCell(row, head, cellStyle);
//遍历数据集
createDataCell(sheet, list, row, cellStyle, cells);
return workbook;
}
/**
* 创建Excel,仅支持.xlsx格式 Excel2007 之后
* @param title 表名称
* @param head 数据标题
* @param list 数据集合
* @return Excel
*/
public static SXSSFWorkbook createBigExcel(String title, Map<String, String> head, List list){
SXSSFWorkbook workbook = new SXSSFWorkbook();
//创建工作表
SXSSFSheet sheet = workbook.createSheet(title);
//添加表头
SXSSFRow row = sheet.createRow(0);
//设置单元格格式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//添加表头内容
List<String> cells = createHeadCell(row, head, cellStyle);
//遍历数据集
createDataCell(sheet, list, row, cellStyle, cells);
return workbook;
}
private static void createDataCell(Sheet sheet, List list, Row row, CellStyle cellStyle, List<String> cells){
Cell cell;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
Object t = list.get(i);
try {
for (int j = 0; j < cells.size(); j++) {
Field field = t.getClass().getDeclaredField(cells.get(j));
Class<?> type = field.getType();
String fieldName = field.getName();
String methodName;
if (boolean.class == type) {
methodName = "is" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
} else {
methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
}
Method method = t.getClass().getMethod(methodName);
//创建单元格并设置值
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
if (Integer.class == type) {
Integer value = (Integer) method.invoke(t);
cell.setCellValue(value);
} else if (String.class == type) {
String value = (String) method.invoke(t);
cell.setCellValue(value);
} else if (Long.class == type) {
Long value = (Long) method.invoke(t);
cell.setCellValue(value);
} else if (Short.class == type) {
Short value = (Short) method.invoke(t);
cell.setCellValue(value);
} else if (Character.class == type) {
Character value = (Character) method.invoke(t);
cell.setCellValue(value);
} else if (Double.class == type) {
Double value = (Double) method.invoke(t);
cell.setCellValue(value);
} else if (Date.class == type) {
Date value = (Date) method.invoke(t);
cell.setCellValue(value);
} else if (Boolean.class == type) {
Boolean value = (Boolean) method.invoke(t);
cell.setCellValue(value);
}
}
} catch (Exception e) {
log.error("创建Excel文件发生异常》》》》》》》》", e);
}
}
}
private static List<String> createHeadCell(Row row, Map<String, String> head, CellStyle cellStyle){
Cell headCell;
int cellNum = 0;
List<String> cells = new ArrayList<>();
for (Iterator<String> iterator = head.keySet().iterator(); iterator.hasNext();) {
String name = iterator.next();
cells.add(name);
headCell = row.createCell(cellNum);
headCell.setCellStyle(cellStyle);
headCell.setCellValue(head.get(name));
cellNum ++;
}
return cells;
}
POI创建Excel文件
最新推荐文章于 2024-08-19 03:00:00 发布