public class ExcelUtil {
   private static final String EXCEL_XLS = "xls";
   private static final String EXCEL_XLSX = "xlsx";
   
   public static int DEFAULT_COLOUMN_WIDTH = 20;

   /**
    * 将excel文件解析为list
    * 
    * @param file
    * @return List<List<Object>>
    * @throws IOException
    */
   public static List<Map<String, String>> getListByExcel(MultipartFile file) throws Exception {
      List<Map<String, String>> list = new ArrayList<Map<String, String>>();

      InputStream in = file.getInputStream();
      
      // 创建Excel工作薄
      Workbook work = getWorkbook(in, file.getOriginalFilename());
      if (null == work) {
         throw new Exception("创建Excel工作薄为空！");
      }
      Sheet sheet = null;
      Row row = null;
      Cell cell = null;

      // 遍历Excel中所有的sheet
      for (int i = 0; i < work.getNumberOfSheets(); i++) {
         sheet = work.getSheetAt(i);
         if (sheet == null) {
            continue;
         }

         // 遍历当前sheet中的所有行
         for (int j = sheet.getFirstRowNum()+1; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null) {
               continue;
            }

            // 遍历所有的列
            Map<String, String> map = new HashMap<String, String>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
               cell = row.getCell(y);
               if(cell != null) {
                  if(cell.getCellType()==0) {
                     if(HSSFDateUtil.isCellDateFormatted(cell)){
                        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                        map.put(y+"", sdf.format(cell.getDateCellValue()));
                     }else {
                        DecimalFormat df = new DecimalFormat("########");
                        map.put(y+"", df.format(cell.getNumericCellValue()));
                     }
                  }else {
                     map.put(y+"", cell.getRichStringCellValue().getString());
                  }
               }else {
                  map.put(y+"", null);
               }
            }
            list.add(map);
         }
      }
      in.close();
      return list;
   }

   /**
    * 描述：根据文件后缀，自适应上传文件的版本
    * 
    * @param inStr,fileName
    * @return
    * @throws Exception
    */
   private static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
      Workbook wb = null;
      String fileType = fileName.substring(fileName.lastIndexOf("."));
      if (("."+EXCEL_XLS).equals(fileType)) {
         wb = new HSSFWorkbook(inStr); // 2003-
      }else if(("."+EXCEL_XLSX).equals(fileType)) {
         wb = new XSSFWorkbook(inStr);
      }else {
         throw new Exception("解析的文件格式有误！");
      }
      return wb;
   }
   
   
   public static void Excel2007AboveOperate(String filePath) throws IOException {
        XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(new File(filePath)));
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
        Sheet first = sxssfWorkbook.getSheetAt(0);
        for (int i = 0; i < 100000; i++) {
            Row row = first.createRow(i);
            for (int j = 0; j < 11; j++) {
                if(i == 0) {
                    // 首行
                    row.createCell(j).setCellValue("column" + j);
                } else {
                    // 数据
                    if (j == 0) {
                        CellUtil.createCell(row, j, String.valueOf(i));
                    } else
                        CellUtil.createCell(row, j, String.valueOf(Math.random()));
                }
            }
        }
        FileOutputStream out = new FileOutputStream("workbook.xlsx");
        sxssfWorkbook.write(out);
        out.close();
    }
   
   /**
     * 导出到Excel文件
     *
     * @param title      Excel标题
     * @param headMap    列名与汉字map集合
     * @param properties 列名集合数组
     * @param list       报表数据
     * @param out        输出文件流
     */
    public static void export(String title, Map<String, String> headMap, String[] properties, List<Map<String, Object>> list, OutputStream out) {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 缓存
        workbook.setCompressTempFiles(true);
        // 表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 15);
        titleFont.setBoldweight((short) 600);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.NO_FILL);// SOLID_FOREGROUND
        // headerStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.NO_FILL);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
        // 设置列宽
        int minBytes = DEFAULT_COLOUMN_WIDTH;
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] headers = new String[headMap.size()];
        for (int i = 0; i < headers.length; i++) {
            headers[i] = headMap.get(properties[i]);
        }
        for (int i = 0; i < headers.length; i++) {
            int bytes = properties[i].getBytes().length;
            arrColWidth[i] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(i, arrColWidth[i] * 256);
        }

        // 遍历集合数据，产生数据行
        Map<String, Object> sumMap = new HashMap<String, Object>();
        int rowIndex = 0;
        for (Map<String, Object> map : list) {
            if (rowIndex == 0) {
                SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);// 表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1); // 列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 2;// 数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
                Object objCell = map.get(properties[i]);
                String cellValue = "";
                if (objCell instanceof Date) {
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = format.format(objCell);
                } else if (objCell == null) {
                    cellValue = "";
                } else {
                    cellValue = objCell.toString();
                }
                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }

        try {
            workbook.write(out);
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}