import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Sheet; import java.util.List; import java.util.Map; /** * <p>excel 简单表格 赋值接口<p> * @version 1.0 * @author li_hao * @date 2017年1月18日 */ public interface ColVal { void setColVal(List<Map<String, Object>> list, String[] keys, Sheet sheet, CellStyle colValCellStyle); }
import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import java.util.List; import java.util.Map; import java.util.Objects; /** * <p>excel 简单表格 赋值 实现<p> * @version 1.0 * @author li_hao * @date 2017年1月18日 */ public class CommonColVal implements ColVal { /** * @param list 数据map * @param keys 列值 key * @param sheet {@link Sheet} * @param colValCellStyle {@link CellStyle} */ @Override public void setColVal(List<Map<String, Object>> list, String[] keys, Sheet sheet, CellStyle colValCellStyle) { int rowNum = 1; // 行号 int i = 0; for (; i < list.size(); i++, rowNum++) { // 创建行 Row row = sheet.createRow(rowNum); // 获取当前行 数据map Map<String, Object> rowValMap = list.get(i); // 遍历 key值 给每个列赋值 for (int j = 0; j < keys.length; j++) { Object cellVal = rowValMap.get(keys[j]); Cell cell = row.createCell(j); cell.setCellValue(Objects.toString(cellVal, "")); cell.setCellStyle(colValCellStyle); } } } }
import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.util.List; import java.util.Map; /** * <p>导出Excel文档工具类<p> * @version 1.0 * @author li_hao * @date 2017年1月18日 */ public class PoiUtils { private static CellStyle buildColValCellStyle(Workbook wb) { CellStyle filedValCellStyle = wb.createCellStyle(); Font f2 = wb.createFont(); // 创建第二种字体样式（用于值） f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // 设置第二种单元格的样式（用于值） filedValCellStyle.setFont(f2); filedValCellStyle.setBorderLeft(CellStyle.BORDER_THIN); filedValCellStyle.setBorderRight(CellStyle.BORDER_THIN); filedValCellStyle.setBorderTop(CellStyle.BORDER_THIN); filedValCellStyle.setBorderBottom(CellStyle.BORDER_THIN); filedValCellStyle.setAlignment(CellStyle.ALIGN_CENTER); filedValCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return filedValCellStyle; } private static CellStyle buildColNameCellStyle(Workbook wb) { // 创建两种单元格格式 CellStyle filedNameCellStyle = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); // 创建第一种字体样式（用于列名） f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 设置第一种单元格的样式（用于列名） filedNameCellStyle.setFont(f); filedNameCellStyle.setBorderLeft(CellStyle.BORDER_THIN); filedNameCellStyle.setBorderRight(CellStyle.BORDER_THIN); filedNameCellStyle.setBorderTop(CellStyle.BORDER_THIN); filedNameCellStyle.setBorderBottom(CellStyle.BORDER_THIN); filedNameCellStyle.setAlignment(CellStyle.ALIGN_CENTER); return filedNameCellStyle; } /** * 创建excel文档， * * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 */ public static Workbook createSheet(Workbook wb, String sheetName, String titleName, List<Map<String, Object>> list, String[] keys, String columnNames[], ColVal colVal) { // 创建第一个sheet（页），并命名 Sheet sheet = wb.createSheet(sheetName); setColLength(keys, sheet); CellStyle colNameCellStyle = buildColNameCellStyle(wb); CellStyle colValCellStyle = buildColValCellStyle(wb); setTitle(titleName, sheet, colNameCellStyle, new CellRangeAddress(0, 0, 0, keys.length - 1)); setColName(columnNames, sheet, colNameCellStyle, 1); colVal.setColVal(list, keys, sheet, colValCellStyle); return wb; } /** * 创建excel文档， * * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 */ public static Workbook createSheetNoTitle(Workbook wb, String sheetName, List<Map<String, Object>> list, String[] keys, String columnNames[], ColVal colVal) { Sheet sheet = wb.createSheet(sheetName); setColLength(keys, sheet); CellStyle colNameCellStyle = buildColNameCellStyle(wb); CellStyle colValCellStyle = buildColValCellStyle(wb); setColName(columnNames, sheet, colNameCellStyle, 0); colVal.setColVal(list, keys, sheet, colValCellStyle); return wb; } private static void setTitle(String titleName, Sheet sheet1, CellStyle colNameCellStyle, CellRangeAddress cellRangeAddress) { //表头 Row titleRow = sheet1.createRow(0); Cell titleRowCell = titleRow.createCell(0); titleRowCell.setCellValue(titleName); titleRowCell.setCellStyle(colNameCellStyle); sheet1.addMergedRegion(cellRangeAddress); } private static void setColLength(String[] keys, Sheet sheet1) { // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for (int i = 0; i < keys.length; i++) { sheet1.setColumnWidth((short) i, (short) (35.7 * 150)); } } private static void setColName(String[] columnNames, Sheet sheet1, CellStyle cs, int rowNum) { // 列名行 Row row = sheet1.createRow(rowNum); //设置列名 for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } } public static void setResponse(HttpServletResponse response, String fileName, ByteArrayOutputStream os) throws IOException { byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } } }
代码示例：
