Question

如何用java poi操作excel

Answer 1

注解类（将实体类加上该注解）
@Target({ ElementType.METHOD, ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField
{
//导出字段在excel中的名字
String title();
}

操作类（调用方法进行导出）
@Slf4j
public class ExcelUtil {

private static final int EXCEL_NUM_LIMIT = 200000;

/**
* 通用导出方法
*/
public static <T> void writeExcel(HttpServletResponse response, String fileName, List<T> list, Class<T> cls) {
// 1.创建一个workbook，对应一个Excel文件
SXSSFWorkbook workBook = new SXSSFWorkbook();
int page = list.size() % EXCEL_NUM_LIMIT == 0 ? list.size() / EXCEL_NUM_LIMIT : list.size() / EXCEL_NUM_LIMIT + 1;
log.info("sheet数量为：{}", page);
for (int m = 0; m < page; m++) {

Field[] fields = cls.getDeclaredFields();

ArrayList<String> headList = new ArrayList<>();

for (Field f : fields) {
ExcelField field = f.getAnnotation(ExcelField.class);
if (field != null) {
headList.add(field.title());
}
}

CellStyle style = getCellStyle(workBook);
Sheet sheet = workBook.createSheet();
workBook.setSheetName(m, "sheet" + String.valueOf(m + 1));
Header header = sheet.getHeader();
header.setCenter("sheet");

// 设置Excel表的第一行即表头
Row row = sheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
Cell headCell = row.createCell(i);
headCell.setCellType(CellType.STRING);
headCell.setCellStyle(style);//设置表头样式
headCell.setCellValue(String.valueOf(headList.get(i)));
sheet.setColumnWidth(i, 15 * 256);
}

int rowIndex = 1;
log.info("开始创建sheet{}", m);
int start = (EXCEL_NUM_LIMIT * m);
int end = list.size() - start >= EXCEL_NUM_LIMIT ? start + EXCEL_NUM_LIMIT : list.size();
log.info("开始{},结束{}", start, end);
for (int i = start; i < end; i++) {
Row rowData = sheet.createRow(rowIndex);//创建数据行
T q = list.get(i);
Field[] ff = q.getClass().getDeclaredFields();
int j = 0;
for (Field f : ff) {
ExcelField field = f.getAnnotation(ExcelField.class);
if (field == null) {
continue;
}
f.setAccessible(true);
Object obj = null;
try {
obj = f.get(q);
} catch (IllegalAccessException e) {
log.error("", e);
}
Cell cell = rowData.createCell(j);
cell.setCellType(CellType.STRING);
// 当数字时
if (obj instanceof Integer) cell.setCellValue((Integer) obj);
// 当Long时
if (obj instanceof Long) cell.setCellValue((Long) obj);
// 当为字符串时
if (obj instanceof String) cell.setCellValue((String) obj);
// 当为布尔时
if (obj instanceof Boolean) cell.setCellValue((Boolean) obj);
// 当为时间时
if (obj instanceof Date) cell.setCellValue(getFormatDate((Date) obj));
// 当为时间时
if (obj instanceof Calendar) cell.setCellValue((Calendar) obj);
// 当为小数时
if (obj instanceof Double) cell.setCellValue((Double) obj);
// 当为BigDecimal
if (obj instanceof BigDecimal) cell.setCellValue(Double.parseDouble(obj.toString()));
// 当ZonedDateTime
if (obj instanceof ZonedDateTime)
cell.setCellValue(((ZonedDateTime) obj).format(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss")));
j++;
}
rowIndex++;
}
}

responseStream(workBook, response, fileName);
}

private static void responseStream(SXSSFWorkbook workBook, HttpServletResponse response, String fileName) {
OutputStream outputStream = null;
try {
response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1"));
response.setCharacterEncoding("utf-8");
//根据传进来的file对象创建可写入的Excel工作薄
outputStream = response.getOutputStream();
log.info("数据导出Excel成功！");
workBook.write(outputStream);
} catch (IOException e) {
log.error("", e);
} finally {
try {
outputStream.close();
} catch (IOException e) {
log.error("", e);
}
}
}

/**
* 设置表头样式
*
* @param wb
* @return
*/
public static CellStyle getCellStyle(SXSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);//设置字体大小
font.setBold(true);//加粗
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);//让单元格居中
style.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 左右居中
style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
style.setWrapText(true);//设置自动换行
style.setFont(font);
return style;
}

public static String getFormatDate(Date date) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String dateString = formatter.format(date);
return dateString;
}

}

Answer 2

"a young woman not