简洁Excel快速导出

首先依赖！

<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;
}

主要逻辑就是这些！实体类什么的就不上了。