Excel导出工具类
import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.Arrays; import java.util.Iterator; import java.util.List; import java.util.stream.Collectors; public class ExportExcelUtil { private ExportExcelUtil() { } /** * @param workbook * @param sheetName * @param fields * @param dataList * @param pattern * @throws Exception */ public static <T> void exportExcel(HSSFWorkbook workbook, String sheetName, Field[] fields, List<T> dataList, String pattern) throws Exception { if (CollectionUtils.isEmpty(dataList)) return; HSSFSheet sheet = workbook.createSheet(sheetName); sheet.setDefaultColumnWidth((short) 20); HSSFCellStyle titleStyle = getTitleStyle(workbook); HSSFCellStyle contentStyle = getContentStyle(workbook); setTitleData(sheet, fields, titleStyle); setContentData(sheet, fields, dataList, pattern, contentStyle); } private static void setTitleData(HSSFSheet sheet, Field[] fields, HSSFCellStyle style) { List<String> headList = Arrays.stream(fields).map(Field::getName).collect(Collectors.toList()); HSSFRow row = sheet.createRow(0); for (int i = 0; i < headList.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headList.get(i)); cell.setCellValue(text); } } private static <T> void setContentData(HSSFSheet sheet, Field[] fields, List<T> dataList, String pattern, HSSFCellStyle style) throws IllegalAccessException { Iterator<T> it = dataList.iterator(); int index = 0; while (it.hasNext()) { index++; HSSFRow row = sheet.createRow(index); T t = it.next(); for (int i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); fields[i].setAccessible(true); Object value = fields[i].get(t); String formatString = ObjectUtil.convertToFormatString(value, pattern); cell.setCellValue(formatString); } } } private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) { // title style HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); // title font HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex()); font.setFontHeightInPoints((short) 12); font.setBold(true); style.setFont(font); return style; } private static HSSFCellStyle getContentStyle(HSSFWorkbook workbook) { // content style HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); // content font HSSFFont font2 = workbook.createFont(); font2.setBold(false); style.setFont(font2); return style; } }
public void exportExcelPositionMongo(@Context HttpServletResponse response) throws Exception { List<PositionMongo> positions = positionMongoProcessor.getCurrentDatePosition(); if (CollectionUtils.isEmpty(positions)) { return; } logger.info("Mongo return position size:{}", positions.size()); String fileName = "MongoPosition"; response.reset(); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls"); response.setContentType("application/x-xls"); HSSFWorkbook workbook = new HSSFWorkbook(); Field[] declaredFields = PositionMongo.class.getDeclaredFields(); declaredFields = Arrays.stream(declaredFields).filter(a -> !"serialVersionUID".equals(a.getName())).toArray(Field[]::new); ExportExcelUtil.exportExcel(workbook, fileName, declaredFields, positions, "yyyy-MM-dd HH:mm:ss"); workbook.write(response.getOutputStream()); response.flushBuffer(); }