导出Excel工具类

public class ExcelUtil
{
  public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, OutputStream out)
    throws Exception
  {
    if ((list.size() == 0) || (list == null)) {
      throw new Exception("数据源中没有任何数据");
    }
    int sheetSize = list.size();
    if ((sheetSize > 65535) || (sheetSize < 1)) {
      sheetSize = 65535;
    }

    try
    {
      WritableWorkbook wwb = Workbook.createWorkbook(out);

      int sheetNum = list.size() % sheetSize == 0 ? list.size() / 
        sheetSize : list.size() / sheetSize + 1;

      for (int i = 0; i < sheetNum; i++)
      {
        if (1 == sheetNum) {
          WritableSheet sheet = wwb.createSheet(sheetName, i);
          fillSheet(sheet, list, fieldMap, 0, list.size() - 1);
        }
        else {
          WritableSheet sheet = wwb.createSheet(sheetName + (i + 1), i);

          int firstIndex = i * sheetSize;
          int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list
            .size() - 1 : (i + 1) * sheetSize - 1;

          fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
        }
      }
      wwb.write();
      wwb.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, HttpServletResponse response)
  {
    String fileName = new SimpleDateFormat("yyyy-MM-dd").format(
      new Date()).toString();

    response.reset();
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition", "attachment; filename=schedule_" + 
      fileName + ".xls");
    try
    {
      OutputStream out = response.getOutputStream();
      listToExcel(list, fieldMap, sheetName, out);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void setColumnAutoSize(WritableSheet ws, int extraWith)
  {
    for (int i = 0; i < ws.getColumns(); i++) {
      int colWith = 0;
      for (int j = 0; j < ws.getRows(); j++) {
        String content = ws.getCell(i, j).getContents().toString();
        int cellWith = content.length();
        if (colWith < cellWith) {
          colWith = cellWith;
        }
      }

      ws.setColumnView(i, colWith + extraWith);
    }
  }

  public static <T> List<T> excelToList(InputStream in, String sheetName, Class<T> entityClass, LinkedHashMap<String, String> fieldMap)
  {
    List resultList = new ArrayList();
    try
    {
      Workbook wb = Workbook.getWorkbook(in);

      Sheet sheet = wb.getSheet(sheetName);

      int realRows = 0;
      for (int i = 0; i < sheet.getRows(); i++)
      {
        int nullCols = 0;
        for (int j = 0; j < sheet.getColumns(); j++) {
          Cell currentCell = sheet.getCell(j, i);
          if ((currentCell == null) || 
            ("".equals(currentCell.getContents().toString()))) {
            nullCols++;
          }
        }
        if (nullCols == sheet.getColumns()) {
          break;
        }
        realRows++;
      }

      if (realRows <= 1) {
        throw new Exception("Excel文件中没有任何数据");
      }
      Cell[] firstRow = sheet.getRow(0);
      String[] excelFieldNames = new String[firstRow.length];

      for (int i = 0; i < firstRow.length; i++) {
        excelFieldNames[i] = firstRow[i].getContents().toString()
          .trim();
      }

      boolean isExist = true;
      List excelFieldList = Arrays.asList(excelFieldNames);
      for (String cnName : fieldMap.keySet()) {
        if (!excelFieldList.contains(cnName)) {
          isExist = false;
          break;
        }
      }

      if (!isExist) {
        throw new Exception("Excel中缺少必要的字段，或字段名称有误");
      }

      LinkedHashMap colMap = new LinkedHashMap();
      for (int i = 0; i < excelFieldNames.length; i++) {
        colMap.put(excelFieldNames[i], Integer.valueOf(firstRow[i].getColumn()));
      }

      for (int i = 1; i < realRows; i++)
      {
        Object entity = entityClass.newInstance();

        for (Map.Entry entry : fieldMap.entrySet())
        {
          String cnNormalName = (String)entry.getKey();

          String enNormalName = (String)entry.getValue();

          int col = ((Integer)colMap.get(cnNormalName)).intValue();

          String content = sheet.getCell(col, i).getContents()
            .toString().trim();
          ReflectUtil.setProperty(entity, enNormalName, content);
        }
        resultList.add(entity);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return resultList;
  }

  private static <T> void fillSheet(WritableSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, int firstIndex, int lastIndex)
    throws Exception
  {
    String[] enFields = new String[fieldMap.size()];
    String[] cnFields = new String[fieldMap.size()];

    int count = 0;
    for (Map.Entry entry : fieldMap.entrySet()) {
      enFields[count] = ((String)entry.getKey());
      cnFields[count] = ((String)entry.getValue());
      count++;
    }

    for (int i = 0; i < cnFields.length; i++)
    {
      WritableFont font1 = new WritableFont(WritableFont.TIMES, 
        12, WritableFont.BOLD);
      WritableCellFormat format1 = new WritableCellFormat(font1);
      format1.setAlignment(Alignment.CENTRE);
      Label label = new Label(i, 0, cnFields[i]);
      label.setCellFormat(format1);
      sheet.addCell(label);
    }

    int rowNo = 1;
    for (int index = firstIndex; index <= lastIndex; index++)
    {
      Object item = list.get(index);
      for (int i = 0; i < enFields.length; i++) {
        Object objValue = null;
        objValue = ReflectUtil.getNestedProperty(item, enFields[i]);
        String fieldValue = objValue == null ? "" : objValue.toString();
        Label label = new Label(i, rowNo, fieldValue);
        sheet.addCell(label);
      }
      rowNo++;
    }

    setColumnAutoSize(sheet, 5);
  }
}