=======第一版:基本功能实现=======
import com.google.common.collect.Maps; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Iterator; import java.util.List; import java.util.Map; /** * 将读取到的数据库内容写到Excel模板表中,供下载需要 */ public class WriterExcelFile { private static final Logger LOGGER = Logger.getLogger(WriterExcelFile.class.getName()); /** * 将数据写入指定path下的Excel文件中 * 这里会有一个限制条件:列名的顺序必须和数据的存储顺序一致,否则会造成混乱;这是第一版,以后再改进这个 * @param path 文件存储路径 * @param name sheet名 * @param style Excel类型 * @param titles 标题串 * @param values 内容集 * @return T\F */ public static boolean generateWorkbook(String path, String name, String style, List<String> titles, List<Map<String, Object>> values) { Workbook workbook; if ("XLS".equals(style.toUpperCase())) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } // 生成一个表格 Sheet sheet = workbook.createSheet(name); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成样式 Map<String, CellStyle> styles = createStyles(workbook); /* * 创建标题行 */ Row row = sheet.createRow(0); for (int i = 0; i < titles.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(titles.get(i)); } /* * 写入正文 */ Iterator<Map<String, Object>> iterator = values.iterator(); int index = 0; while (iterator.hasNext()) { index++; row = sheet.createRow(index); Map<String, Object> value = iterator.next(); String content = ""; for (Map.Entry<String, Object> map : value.entrySet()) { Object object = map.getValue(); content = object.toString(); } for (int i = 0; i < value.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles.get("cell")); cell.setCellValue(content); } } /* * 写入到文件中 */ boolean isCorrect = false; File file = new File(path); // 如果文件存在,则删除已有的文件,重新创建一份新的 if (file.exists()) { file.deleteOnExit(); file = new File(path); } OutputStream outputStream = null; try { outputStream = new FileOutputStream(file); workbook.write(outputStream); isCorrect = true; } catch (IOException e) { LOGGER.error(e.getMessage()); } finally { try { if (null != outputStream) { outputStream.close(); } } catch (IOException e) { LOGGER.error(e.getMessage()); } } return isCorrect; } /** * Create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = Maps.newHashMap(); DataFormat dataFormat = wb.createDataFormat(); // 标题样式 CellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐 titleStyle.setLocked(true); titleStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex()); titleStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBold(true); titleFont.setFontName("微软雅黑"); titleStyle.setFont(titleFont); styles.put("title", titleStyle); // 文件头样式 CellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyle.setWrapText(true); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setColor(IndexedColors.WHITE.getIndex()); titleFont.setFontName("微软雅黑"); headerStyle.setFont(headerFont); styles.put("header", headerStyle); // 正文样式 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", cellStyle); return styles; } }
=======第二版:优化Excel表样式,添加数据类型判断以及一个demo测试=======
import com.google.common.collect.Lists; import com.google.common.collect.Maps; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.*; public class WriterExcelUtil { private static final Logger LOGGER = LoggerFactory.getLogger(WriterExcelUtil.class.getName()); public static void main(String[] args) { String path = "E://demo.xlsx"; String name = "test"; List<String> titles =Lists.newArrayList(); titles.add("id"); titles.add("name"); titles.add("age"); titles.add("birthday"); titles.add("gender"); titles.add("date"); List<Map<String, Object>> values = Lists.newArrayList(); for (int i = 0; i < 10; i++) { Map<String, Object> map = Maps.newHashMap(); map.put("id", i + 1D); map.put("name", "test_" + i); map.put("age", i * 1.5); map.put("gender", "man"); map.put("birthday", new Date()); map.put("date", Calendar.getInstance()); values.add(map); } System.out.println(writerExcel(path, name, titles, values)); } /** * 数据写入Excel文件 * * @param path 文件路径,包含文件全名,例如:D://file//demo.xls * @param name sheet名称 * @param titles 行标题列 * @param values 数据集合,key为标题,value为数据 * @return True\False */ public static boolean writerExcel(String path, String name, List<String> titles, List<Map<String, Object>> values) { LOGGER.info("path : {}", path); String style = path.substring(path.lastIndexOf("."), path.length()).toUpperCase(); // 从文件路径中获取文件的类型 return generateWorkbook(path, name, style, titles, values); } /** * 将数据写入指定path下的Excel文件中 * * @param path 文件存储路径 * @param name sheet名 * @param style Excel类型 * @param titles 标题串 * @param values 内容集 * @return True\False */ private static boolean generateWorkbook(String path, String name, String style, List<String> titles, List<Map<String, Object>> values) { LOGGER.info("file style : {}", style); Workbook workbook; if ("XLS".equals(style.toUpperCase())) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } // 生成一个表格 Sheet sheet; if (null == name || "".equals(name)) { sheet = workbook.createSheet(); // name 为空则使用默认值 } else { sheet = workbook.createSheet(name); } // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成样式 Map<String, CellStyle> styles = createStyles(workbook); /* * 创建标题行 */ Row row = sheet.createRow(0); // 存储标题在Excel文件中的序号 Map<String, Integer> titleOrder = Maps.newHashMap(); for (int i = 0; i < titles.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(styles.get("header")); String title = titles.get(i); cell.setCellValue(title); titleOrder.put(title, i); } /* * 写入正文 */ Iterator<Map<String, Object>> iterator = values.iterator(); int index = 0; // 行号 while (iterator.hasNext()) { index++; // 出去标题行,从第一行开始写 row = sheet.createRow(index); Map<String, Object> value = iterator.next(); for (Map.Entry<String, Object> map : value.entrySet()) { // 获取列名 String title = map.getKey(); // 根据列名获取序号 int i = titleOrder.get(title); // 在指定序号处创建cell Cell cell = row.createCell(i); // 设置cell的样式 if (index % 2 == 1) { cell.setCellStyle(styles.get("cellA")); } else { cell.setCellStyle(styles.get("cellB")); } // 获取列的值 Object object = map.getValue(); // 判断object的类型 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if (object instanceof Double) { cell.setCellValue((Double) object); } else if (object instanceof Date) { String time = simpleDateFormat.format((Date) object); cell.setCellValue(time); } else if (object instanceof Calendar) { Calendar calendar = (Calendar) object; String time = simpleDateFormat.format(calendar.getTime()); cell.setCellValue(time); } else if (object instanceof Boolean) { cell.setCellValue((Boolean) object); } else { cell.setCellValue(object.toString()); } } } /* * 写入到文件中 */ boolean isCorrect = false; try { File file = new File(path); OutputStream outputStream = new FileOutputStream(file); workbook.write(outputStream); outputStream.close(); isCorrect = true; } catch (IOException e) { isCorrect = false; LOGGER.error("write Excel file error : {}", e.getMessage()); } try { workbook.close(); } catch (IOException e) { isCorrect = false; LOGGER.error("workbook closed error : {}", e.getMessage()); } return isCorrect; } /** * Create a library of cell styles */ /** * @param wb * @return */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = Maps.newHashMap(); // 标题样式 CellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐 titleStyle.setLocked(true); // 样式锁定 titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBold(true); titleFont.setFontName("微软雅黑"); titleStyle.setFont(titleFont); styles.put("title", titleStyle); // 文件头样式 CellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 前景色 headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜色填充方式 headerStyle.setWrapText(true); headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界 headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setColor(IndexedColors.WHITE.getIndex()); titleFont.setFontName("微软雅黑"); headerStyle.setFont(headerFont); styles.put("header", headerStyle); Font cellStyleFont = wb.createFont(); cellStyleFont.setFontHeightInPoints((short) 12); cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex()); cellStyleFont.setFontName("微软雅黑"); // 正文样式A CellStyle cellStyleA = wb.createCellStyle(); cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置 cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleA.setWrapText(true); cellStyleA.setBorderRight(BorderStyle.THIN); cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderLeft(BorderStyle.THIN); cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderTop(BorderStyle.THIN); cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setBorderBottom(BorderStyle.THIN); cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleA.setFont(cellStyleFont); styles.put("cellA", cellStyleA); // 正文样式B:添加前景色为浅黄色 CellStyle cellStyleB = wb.createCellStyle(); cellStyleB.setAlignment(HorizontalAlignment.CENTER); cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyleB.setWrapText(true); cellStyleB.setBorderRight(BorderStyle.THIN); cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderLeft(BorderStyle.THIN); cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderTop(BorderStyle.THIN); cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setBorderBottom(BorderStyle.THIN); cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyleB.setFont(cellStyleFont); styles.put("cellB", cellStyleB); return styles; } }
=======第三版:使用注解来实现,请跳转到 https://www.cnblogs.com/SummerinShire/p/11045254.html =======