ExcelUtil——基本工具类

public class ExcelUtil {
 
 private static final Log log = LogFactory.getLog(ExcelUtil.class);//日志
 
 /**
  * 創建Excel對象
  * @param excelBean
  * @return
  */
 public static HSSFWorkbook creatExcel(ExcelBean excelBean){
  HSSFWorkbook wb = new HSSFWorkbook(); //創建Excel工作薄
  //在Excel工作薄中建一工作表，默認為缺省值
//  HSSFSheet sheet = wb.createSheet();
        HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
        //設置工作表各列寬度
       // setColumnWidth(sheet, excelBean.getWidth());
        //設置字體，樣式
        //HSSFFont font = setFont(wb, "宋體");
        //HSSFCellStyle style = setCellStyle(wb, font);
        //設置第一行
        List<String> titleList = excelBean.getTitleList();
        setRowValue(sheet, titleList, 0); //在索引0的位置創建行（最頂端的行）
        //設置數據行
        List dataList = excelBean.getDataList();
        if(dataList != null){
         for(int i=0; i<dataList.size(); i++){          
          List rowList = (List) dataList.get(i);
          setRowValue(sheet, rowList, i+1); //在索引1的位置創建行（第2行）          
         }
        }
        return wb;
 }
 
 /**
  * 設置行值
  * @param sheet
  * @param List
  * @return
  */
 public static HSSFSheet setRowValue(HSSFSheet sheet, List<String> list,int rowNo){
  if(list != null){
   //在索引rowNo的位置創建行
   HSSFRow rowTitle = sheet.createRow(rowNo);
   for(int i= 0 ; i<list.size();i++){
    String title = list.get(i);
    //從索引0的位置開始創建單元格（左上端）
    HSSFCell cell = rowTitle.createCell((short)i);
    cell.setCellValue(title); //設置單元格內容
   }
  }
  return sheet;
 }
 
 /**
  * 下載Excel
  * @param wb
  * @param filename MemberList.xls
  * @param response
  * @return
  */
 public static boolean downloadExcel(HSSFWorkbook wb, String filename, HttpServletResponse response){
  boolean flag = true;  
  //設置輸入流
  OutputStream output = null;
  try {
//   設置響應類型
   response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("gbk"),"ISO-8859-1"));
   response.setContentType("application/x-msdownload");
   
   output = response.getOutputStream();   
   wb.write(output);//輸出到網頁中
   output.flush();
   log.info("下載Excel：" + filename + ", 成功！");
  } catch (Exception e) {
   flag = false;
   log.info("下載Excel：" + filename + ", 失敗！");
   e.printStackTrace();
  } finally{
   try {
    output.close();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
  return flag;  
 }
 
 /**
  * 下載Excel
  * add by zhangde 2011-3-17
  * @param wb
  * @param filename MemberList.xls
  * @param response
  * @return
  */
 public static boolean downloadExcelEncode(String encoding, HSSFWorkbook wb, String filename, HttpServletResponse response){
  boolean flag = true;  
  //設置輸入流
  OutputStream output = null;
  try {
//   設置響應類型,用utf-8,可能会出现高位（前面）被截。gbk在简体操作系统中不会。
//   response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes(encoding),"ISO-8859-1"));
   response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(filename, encoding));
   System.out.println("filename=" + filename);
   System.out.println("URLEncoderfilename=" + URLEncoder.encode(filename, encoding));
   response.setContentType("application/x-msdownload");
   
   output = response.getOutputStream();   
   wb.write(output);//輸出到網頁中
   output.flush();
   log.info("下載Excel：" + filename + ", 成功！");
  } catch (Exception e) {
   flag = false;
   log.info("下載Excel：" + filename + ", 失敗！");
   e.printStackTrace();
  } finally{
   try {
    output.close();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
  return flag;  
 }
 
 
 /**
  * 下載Excel
  * @param wb
  * @param filename MemberList.xls
  * @param response
  * @param encoding 編碼參數 例如 gbk, utf-8
  * @return
  */
 public static boolean downloadExcel(String encoding, HSSFWorkbook wb, String filename, HttpServletResponse response){
  boolean flag = true;  
  //設置輸入流
  OutputStream output = null;
  String name = "";
  try {
//   設置響應類型
   name = FileUtil.toEncodeString(filename, encoding); // 文件名編碼
   response.setHeader("Content-disposition", "attachment;filename=" + name);
   response.setContentType("application/x-msdownload");
   output = response.getOutputStream();   
   wb.write(output);//輸出到網頁中
   output.flush();
   log.info("下載Excel：" + filename + ", 成功！");
  } catch (Exception e) {
   flag = false;
   log.info("下載Excel：" + filename + ", 失敗！");
   e.printStackTrace();
  } finally{
   try {
    output.close();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
  return flag;  
 }
 
 /**
  * 導入文件（excel）
  * @param path   路徑+檔案名
  * @param line   從第幾欄開始
  * @param rowLen 到第幾列結束
  * @return list
  * @throws Exception
  */
 public static List<Object> importData(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{ 
  List<Object> tmpList = new ArrayList<Object>(); 
  POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
  HSSFWorkbook wb = new HSSFWorkbook(pfs);
  HSSFSheet sheet = wb.getSheetAt(0);
  HSSFRow row = null;
  try{  
   int totalRow = sheet.getLastRowNum(); //excel資料行數
   if(totalRow>0){ //判斷excel是否為空
    for(int i = atRow ; i <=totalRow; i++){
        List<Object> rowList = new ArrayList<Object>(); 
        row = sheet.getRow(i);
        if(row!=null){ //判斷行是否為空
         for(int j = line; j < rowLen; j++){ 
       HSSFCell cell = row.getCell((short) j);
       if(cell == null){ //判斷列是否為空
        rowList.add("");
       }else{
        int ctype = cell.getCellType();
        switch(ctype){
         // 處理string部份
         case HSSFCell.CELL_TYPE_BLANK:
          rowList.add("");
         break;
         
         case HSSFCell.CELL_TYPE_STRING:
          rowList.add(cell.getRichStringCellValue().getString().trim());
         break;
         
         //處理number部份
         case HSSFCell.CELL_TYPE_NUMERIC:
          
          if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
           System.out.print( cell.getDateCellValue());
           //須轉date格式
           rowList.add(cell.getDateCellValue());
             }else{
              
           double tmpdouble = cell.getNumericCellValue();
           if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) {
            
            //int
            rowList.add(Integer.toString((int)tmpdouble));
           }else{
            
            //double
            rowList.add(Double.toString(cell.getNumericCellValue()));
           }
             }
         break;
         
         //處理boolean部份
         case HSSFCell.CELL_TYPE_BOOLEAN: 
          rowList.add(cell.getBooleanCellValue());
         break;
        }
       } 
         }
         tmpList.add(rowList);
        }
       }
   }
  }catch(Exception e){
   e.printStackTrace();
   throw e;
  }
     return tmpList;
 }

 /**
  * 導入文件（excel）包含空行
  * @param path   路徑+檔案名
  * @param line   從第幾欄開始
  * @param rowLen 到第幾列結束
  * @return list
  * @throws Exception
  */
 public static List<Object> importData1(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{ 
  List<Object> tmpList = new ArrayList<Object>(); 
  POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
  HSSFWorkbook wb = new HSSFWorkbook(pfs);
  HSSFSheet sheet = wb.getSheetAt(0);
  HSSFRow row = null;
  try{  
   int totalRow = sheet.getLastRowNum(); //excel資料行數
   if(totalRow>0){ //判斷excel是否為空
    for(int i = atRow ; i <=totalRow; i++){
        List<Object> rowList = new ArrayList<Object>(); 
        row = sheet.getRow(i);
        if(row!=null){ //判斷行是否為空
         for(int j = line; j < rowLen; j++){ 
       HSSFCell cell = row.getCell((short) j);
       if(cell == null){ //判斷列是否為空
        rowList.add("");
       }else{
        int ctype = cell.getCellType();
        switch(ctype){
         // 處理string部份
         case HSSFCell.CELL_TYPE_BLANK:
          rowList.add("");
         break;
         
         case HSSFCell.CELL_TYPE_STRING:
          rowList.add(cell.getRichStringCellValue().getString().trim());
         break;
         
         //處理number部份
         case HSSFCell.CELL_TYPE_NUMERIC:
          
          if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
           System.out.print( cell.getDateCellValue());
           //須轉date格式
           rowList.add(cell.getDateCellValue());
             }else{
              
           double tmpdouble = cell.getNumericCellValue();
           if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) {
            
            //int
            rowList.add(Integer.toString((int)tmpdouble));
           }else{
            
            //double
            rowList.add(Double.toString(cell.getNumericCellValue()));
           }
             }
         break;
         
         //處理boolean部份
         case HSSFCell.CELL_TYPE_BOOLEAN: 
          rowList.add(cell.getBooleanCellValue());
         break;
        }
       } 
         }
        }else{
         for(int k = line; k < rowLen; k++){ 
          rowList.add("");
         }
        }
        tmpList.add(rowList);
       }
   }
  }catch(Exception e){
   e.printStackTrace();
   throw e;
  }
     return tmpList;
 }
 
}