util之ExportExcelUtil （excel导出工具类 poi 3.1.7）
package com.xxx.yjfhltjb.controller.yjfhltjb; import java.io.OutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCellStyle; /** * excel导出工具类 poi 3.17 以前的版本 */ public class ExportExcelUtil { /** * @param request * @param response * @param wb * @param fileName 自定义导出的文件取名（导出后文件名叫什么） * @throws Exception * 调用后浏览器自动生成excel */ public static void exportExcel(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook wb, String fileName) throws Exception { response.setContentType("application/octet-stream;charset=UTF-8"); response.setHeader("Content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GB2312"), "8859_1")); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } /** * * @param title 标题 * * @param headers 表头 * 使用方法： String[] headers = {"序号","单位","机组","容量","开始","结束","工期","类型","发电类型"}; * * @param values 表中元素 * 使用方法： * String[][] values = new String[query.size()][headers.length]; * for (int i = 0; i < query.size(); i++) { * Jzjxjh e = query.get(i); * values[i][0]=e.getXh()+""; * values[i][1]=e.getDw(); * values[i][2]=e.getJz(); * values[i][3]=e.getRl()+""; * values[i][4]=sdf.format(e.getKs()); * } * * @param columnWidth 每一列的宽度 * 使用方法： 转入null 或者 int[] columnWidth={18,18,18,18,20,20,18,18,18}; * * @return 返回 HSSFWorkbook wb */ public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], String[][] values,Integer[] columnWidth) throws Exception { // 创建一个HSSFWorkbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(title); // 创建标题合并行 sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) headers.length - 1)); HSSFCellStyle style = getTitleStyle(wb); // 设置标题字体 Font titleFont = getTitleFont(wb); //设置粗体 // titleFont.setBold(true); style.setFont(titleFont); // 设置表内容样式 // 创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style1 = getContextStyle(wb); // 产生标题行 HSSFRow hssfRow = sheet.createRow(0); HSSFCell cell = hssfRow.createCell(0); cell.setCellValue(title); cell.setCellStyle(style); // 产生表头 HSSFCellStyle hssfCellStyle = getHeadStyle(wb); HSSFRow row1 = sheet.createRow(1); for (int i = 0; i < headers.length; i++) { HSSFCell hssfCell = row1.createCell(i); hssfCell.setCellValue(headers[i]); hssfCell.setCellStyle(hssfCellStyle); } //自适应列宽度(实际效果并不理想) // sheet.autoSizeColumn(1); //设置表头宽度 if(columnWidth!=null&&columnWidth.length>0){ for(int i =0;i<columnWidth.length;i++){ sheet.setColumnWidth(i, columnWidth[i]*256); } } // 创建内容 for (int i = 0; i < values.length; i++) { row1 = sheet.createRow(i + 2); for (int j = 0; j < values[i].length; j++) { // 将内容按顺序赋给对应列对象 HSSFCell hssfCell = row1.createCell(j); hssfCell.setCellValue(values[i][j]); hssfCell.setCellStyle(style1); } } return wb; } /** * @param wb * @return 设置表内容样式 创建单元格，并设置值表头 设置表头居中 */ private static HSSFCellStyle getContextStyle(HSSFWorkbook wb) throws Exception { HSSFCellStyle style1 = wb.createCellStyle(); // style1.setBorderBottom(BorderStyle.THIN); // style1.setBorderLeft(BorderStyle.THIN); // style1.setBorderRight(BorderStyle.THIN); // style1.setBorderTop(BorderStyle.THIN); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); return style1; } /** * @param wb * @return 设置标题字体 */ private static Font getTitleFont(HSSFWorkbook wb) throws Exception { Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); return titleFont; } /** * @param wb * @return 设置标题样式 */ private static HSSFCellStyle getTitleStyle(HSSFWorkbook wb) throws Exception { // 设置标题样式 HSSFCellStyle style = wb.createCellStyle(); // XSSFCellStyle cellStyle = wb.createCellStyle(); // style.setAlignment(HorizontalAlignment.CENTER); // 设置居中样式 // style.setVerticalAlignment(VerticalAlignment.CENTER); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中 style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直 return style; } /** * @param wb * @return 设置值表头样式 设置表头居中 */ private static HSSFCellStyle getHeadStyle(HSSFWorkbook wb) throws Exception { // 设置值表头样式 设置表头居中 HSSFCellStyle style1 = wb.createCellStyle(); // hssfCellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置居中样式 // hssfCellStyle.setBorderBottom(BorderStyle.THIN); // hssfCellStyle.setBorderLeft(BorderStyle.THIN); // hssfCellStyle.setBorderRight(BorderStyle.THIN); // hssfCellStyle.setBorderTop(BorderStyle.THIN); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); return style1; } /** * @param wb * @param sheet * @param starRow 从哪行开始插入 * @param rows 插入多少行 * void * 原方法 void org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) */ public static void excelInsertRow(HSSFWorkbook wb, HSSFSheet sheet, int starRow, int rows) { sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows, true, false); starRow = starRow - 1; for (int i = 0; i < rows; i++) { HSSFRow sourceRow = null; HSSFRow targetRow = null; HSSFCell sourceCell = null; HSSFCell targetCell = null; short m; starRow = starRow + 1; sourceRow = sheet.getRow(starRow); targetRow = sheet.createRow(starRow + 1); targetRow.setHeight(sourceRow.getHeight()); for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) { sourceCell = sourceRow.getCell(m); targetCell = targetRow.createCell(m); targetCell.setCellStyle(sourceCell.getCellStyle()); targetCell.setCellType(sourceCell.getCellType()); } } } }
添加一个方法，可以传入cell返回一个string类型值
/** * @param cell * @return //获取单元格各类型值，返回字符串类型 String * */ public String getCellValueByCell(Cell cell) { // 判断是否为null或空串 if (cell == null || cell.toString().trim().equals("")) { return ""; } String cellValue = ""; int cellTypeEnum = cell.getCellType(); switch (cellTypeEnum) { case Cell.CELL_TYPE_NUMERIC: // 数字 short format = cell.getCellStyle().getDataFormat(); if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf; // System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat()); if (format == 20 || format == 32) { sdf = new SimpleDateFormat("HH:mm"); } else if (format == 14 || format == 31 || format == 57 || format == 58) { // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) sdf = new SimpleDateFormat("yyyy/MM/dd"); double value = cell.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); cellValue = sdf.format(date); } else {// 日期 sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); } try { cellValue = sdf.format(cell.getDateCellValue());// 日期 } catch (Exception e) { try { throw new Exception("exception on get date data !".concat(e.toString())); } catch (Exception e1) { e1.printStackTrace(); } } finally { sdf = null; } } else { BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()); cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值 } break; case Cell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: // 故障 cellValue = "ERROR VALUE"; break; default: cellValue = "UNKNOW VALUE"; break; } return cellValue; }
