首先依赖！
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14</version> </dependency>
其次工具类
import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import java.text.SimpleDateFormat; /** * <pre> * Excel生成工具类 * </pre> */ public class ExcelUtil { static Logger logger = Logger.getLogger(ExcelUtil.class); /** * 分隔符 */ private final static String SEPARATOR = "|"; /** * 创建excel文档， * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet（页），并命名 Sheet sheet = wb.createSheet(); // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); row.setHeight((short)600); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); //设置列名 for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (short i = 0; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行，在页sheet上 PS：第一行默认设置了标题，所以这里加1展示数据 Row row1 = sheet.createRow(i+1); row1.setHeight((short)400); // 在row行上创建一个方格 for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; } /** * 读取excel数据 * @param path */ public static void readExcelToObj(String path) { Workbook wb = null; try { wb = WorkbookFactory.create(new File(path)); readExcel(wb, 0, 0, 0); } catch (InvalidFormatException e) { logger.error(e.getMessage(),e); } catch (IOException e) { logger.error(e.getMessage(),e); } } /** * 读取excel文件 * * @param wb * @param sheetIndex * sheet页下标：从0开始 * @param startReadLine * 开始读取的行:从0开始 * @param tailLine * 去除最后读取的行 */ public static void readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) { Sheet sheet = wb.getSheetAt(sheetIndex); Row row = null; for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) { row = sheet.getRow(i); for (Cell c : row) { c.setCellType(Cell.CELL_TYPE_STRING); boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); System.out.print(rs + " "); } else { System.out.print(c.getRichStringCellValue() + " "); } } System.out.println(); } } /** * 获取合并单元格的值 * * @param sheet * @param row * @param column * @return */ public static String getMergedRegionValue(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { Row fRow = sheet.getRow(firstRow); Cell fCell = fRow.getCell(firstColumn); return getCellValue(fCell); } } } return null; } /** * 判断合并了行 * * @param sheet * @param row * @param column * @return */ public static boolean isMergedRow(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row == firstRow && row == lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } /** * 判断指定的单元格是否是合并单元格 * * @param sheet * @param row * 行下标 * @param column * 列下标 * @return */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } /** * 判断sheet页中是否含有合并单元格 * * @param sheet * @return */ public static boolean hasMerged(Sheet sheet) { return sheet.getNumMergedRegions() > 0 ? true : false; } /** * 合并单元格 * * @param sheet * @param firstRow * 开始行 * @param lastRow * 结束行 * @param firstCol * 开始列 * @param lastCol * 结束列 */ public static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } /** * 获取单元格的值 * * @param cell * @return */ public static String getCellValue(Cell cell) { if (cell == null) return ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } return ""; } /** * Description: 方法功能说明 * @param workbook Excel工作簿对象 * @param sheetNum Sheet页编号 * @return Excel的所有行数据列表 */ public static List<String> exportListFromExcel(Workbook workbook, int sheetNum) { Sheet sheet = workbook.getSheetAt(sheetNum); short minColIx = 0; short maxColIx = 0; short totalCol = 0; // 解析公式结果 FormulaEvaluator evaluator = workbook.getCreationHelper() .createFormulaEvaluator(); List<String> list = new ArrayList<String>(); StringBuilder sb = new StringBuilder(); int minRowIx = sheet.getFirstRowNum(); int maxRowIx = sheet.getLastRowNum(); System.out.println("最小行数："+minRowIx); System.out.println("最大行数："+maxRowIx); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd"); for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) { Row row = sheet.getRow(rowIx); //空行 if(row == null || row.getFirstCellNum()!=0){ list.add(null); continue; } minColIx = row.getFirstCellNum(); //System.out.println("================="+minColIx); maxColIx = row.getLastCellNum(); //System.out.println("================="+maxColIx); if(rowIx==0){ totalCol = maxColIx; System.out.println("最大列数"+totalCol); } for (short colIx = minColIx; colIx < maxColIx; colIx++) { Cell cell = row.getCell(new Integer(colIx)); CellValue cellValue = evaluator.evaluate(cell); if(colIx != minColIx){ //不是第一列的场合 sb.append(SEPARATOR); } if (cellValue == null) { sb.append(" "); continue; } // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了 // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: sb.append(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: // 这里的日期类型会被转换为数字类型，需要判别后区分处理 if (DateUtil.isCellDateFormatted(cell)) { sb.append(dateFormat.format(cell.getDateCellValue())); } else { sb.append(String.valueOf(cellValue.getNumberValue())); } break; case Cell.CELL_TYPE_STRING: sb.append(cellValue.getStringValue()); break; case Cell.CELL_TYPE_FORMULA: sb.append(""); break; case Cell.CELL_TYPE_BLANK: sb.append(""); break; case Cell.CELL_TYPE_ERROR: sb.append(""); break; default: sb.append(""); break; } }//循环列结束 if(maxColIx<totalCol){ for (short i = maxColIx; i < totalCol; i++) { sb.append(SEPARATOR); sb.append(" "); } } list.add(sb.toString()); sb.delete(0, sb.length()); } return list; } /** * Description: 编辑导入的行数据 * @param rowStr 导入行数据 * @return 列数据数组(Trim空格) */ public static String[] editRowData(String rowStr){ String[] dataArys = rowStr.split("\\|"); int i = 0; if(dataArys != null && dataArys.length > 0){ for(i = 0; i<dataArys.length; i++){ dataArys[i] = dataArys[i].trim(); } } return dataArys; } }
再然后就是实现类
@Override public JsonResult exportExcel(HttpServletResponse response) { JsonResult jsonResult = new JsonResult(); List<Map<String, Object>> list = studentMapper.searchList(null);//要导出的结果集 String[] keys = new String[]{"name","age","sex"};//查询结果字段名 String[] columnNames = new String[]{"名称","年龄","性别"};//Excle对应列名,和上面一一对应。 try { Workbook workbook = ExcelUtil.createWorkBook(list,keys,columnNames);//调用工具类创建工作簿 FileOutputStream os = new FileOutputStream("F://other/workbook.xls");//导出的url response.reset();// 清空输出流 String filename = "Customer Behavior Detail" + DateUtils.getStrDay(new Date()) + ".xls"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename,"UTF-8").replace("+","%20"));// 设定输出文件头 response.setContentType("application/msexcel");// 定义输出类型 workbook.write(os); // 写入文件 os.close(); // 关闭流 }catch(Exception ex) { logger.error(ex.getMessage() ,ex); logger.error("===========================导出列表出错"); jsonResult.setResultCode(JsonCode.SERVER_ERROR); return jsonResult; } return jsonResult; }
主要逻辑就是这些！实体类什么的就不上了。
1031
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
