java excel 操作 poi_如何用java poi操作excel

展开全部

注解类(将62616964757a686964616fe4b893e5b19e31333363393064实体类加上该注解)

@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 void writeExcel(HttpServletResponse response, String fileName, List list, Class 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 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;

}

}

本回答由提问者推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起