将List数据保存到Excel中

  1 import java.io.File;
  2 import java.io.IOException;
  3 import java.util.Arrays;
  4 import java.util.HashMap;
  5 import java.util.Iterator;
  6 import java.util.List;
  7 import java.util.Map;
  8   
  9 import jxl.Workbook;
 import jxl.read.biff.BiffException;
 import jxl.write.Label;
 import jxl.write.WritableSheet;
 import jxl.write.WritableWorkbook;
 import jxl.write.WriteException;
 import jxl.write.biff.RowsExceededException;
 
 import org.springframework.beans.BeanWrapper;
 import org.springframework.beans.BeanWrapperImpl;
 
 import com.awd.bean.XsjlBean;
 
 /**
  * 2010年12月21日17:25:30
  * @author Administrator
  *
  */
 public class ExcelUtils {
     
     /**
      * 将List数据保存到Excel中
      *   2010年12月21日17:58:22
      * @param list                记录List
      * @param field2TitleMap    JavaBean变量和表格标题
      * @param filePath            文件存放路径
      * @param cls                JavaBean  
      */
     public static void saveAsToExcel(List list,Map field2TitleMap,String filePath,Class cls) {
         if (list == null) {
             throw new IllegalArgumentException("ListRange 未被初始化");
         }
         if (field2TitleMap == null) {
             throw new IllegalArgumentException("field2TitleMap 未被初始化");
         }
         if (filePath == null) {
             throw new IllegalArgumentException("filePath 未被初始化");
         }
         if (cls == null) {
             throw new IllegalArgumentException("Class 未被初始化");
         }
         
         Object[] keySortArray = field2TitleMap.keySet().toArray();
         Arrays.sort(keySortArray);
         
         //javaBean变量名称Map
         Map<Object,String> fieldMap = new HashMap<Object,String>();
         //字段中文描述Map
         Map<Object,String> titleMap = new HashMap<Object,String>();
         
         //初始化字段名称与字段描述信息
         Iterator iter = field2TitleMap.entrySet().iterator();
         String[] valArr ;
         Map.Entry entry;
         Object key;
         Object val;
         while (iter.hasNext()) {
             entry = (Map.Entry) iter.next();
             key = entry.getKey();  
             val = entry.getValue();
             
             if (val != null) {  
                 valArr = (val.toString()).split("\\|");
                 if (valArr.length == 2) {
                     fieldMap.put(key, valArr[0]);
                     titleMap.put(key, valArr[1]);
                 } else {
                     fieldMap.put(key, val.toString());
                     titleMap.put(key, val.toString());
                 }
             }
         }  
         
         //创建Excel文件
         File file = new File(filePath);
         
         //创建表头
         createExcelTitle(file, keySortArray, titleMap);
         //生成文件内容   
         createExcelContent(file, keySortArray, fieldMap, list, cls);
         
     }
     
     /**
      * 创建Excel标题行
      * 2010年12月21日11:01:07  
      * @param file                文件
      * @param keySortArray        字段顺序数组
      * @param titleMap            标题Map
      */
     protected static void createExcelTitle(File file,Object[] keySortArray,Map titleMap) {
         if(file == null) {
             throw new IllegalArgumentException("文件不存在，无法创建Excel表格。");
         }
           
         if(keySortArray == null){
             throw new IllegalArgumentException("keySortArray 为null，请初始化后在操作。");
         }
 
         if(titleMap == null){
             throw new IllegalArgumentException("titleMap 为 null，无法创建。");
         }
         
         WritableWorkbook book = null;
         try {
             book = Workbook.createWorkbook(file);
             // 生成名为“第一页”的工作表，参数0表示这是第一页
             WritableSheet sheet1 = book.createSheet("sheet1", 0);
             
             int n = 0;
             // 添加标题行
             Label label = null;
             for(Object keyObj:keySortArray) {
                 if(titleMap.containsKey(keyObj)) {
                     label = new Label(n++, 0, titleMap.get(keyObj).toString());
                     sheet1.addCell(label);
                 }
             }
         } catch (IOException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿标题写入失败："
                     + e.getMessage());  
         } catch (RowsExceededException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿标题写入失败："
                     + e.getMessage());
         } catch (WriteException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿标题写入失败："
                     + e.getMessage());
         }finally{
             try{
                 book.write();
                 book.close();
                 book = null;
             }catch (Exception e) {
                 throw new IllegalArgumentException("Excel文件写入失败，可能因为该文件正在被打开中："
                         + e.getMessage());
             }
         }
     }
     
     /**
      * 生成Excel内容
      * 2010年12月21日11:01:41
      * @param file                文件
      * @param keySortArray        字段顺序数组
      * @param list                数据列表
      * @param cls                指定的Class
      */
     protected static void createExcelContent(File file, Object[] keySortArray,
             Map fieldMap, List list, Class cls) {
         if (file == null) {
             throw new IllegalArgumentException("文件不存在，无法创建Excel表格。");
         }
 
         if (keySortArray == null) {
             throw new IllegalArgumentException("keySortArray 为null，请初始化后在操作。");
         }
 
         if (fieldMap == null) {
             throw new IllegalArgumentException("fieldMap 为 null，无法生成。");
         }
         if (list == null) {
             throw new IllegalArgumentException("数据列表 为 null，无法读取数据。");
         }
         if (cls == null) {
             throw new IllegalArgumentException("Class 为 null，无法进行类的映射关系。");
         }
 
         WritableWorkbook book = null;
         try {
             // Excel获得文件
             Workbook wb = Workbook.getWorkbook(file);
            // 打开一个文件的副本，并且指定数据写回到原文件
             book = Workbook.createWorkbook(file,wb);
             // 生成名为“第一页”的工作表，参数0表示这是第一页
             WritableSheet sheet1 = book.getSheet("sheet1");
 
             int cols = 0;
             int rows = 1;
             BeanWrapper bw = null;
             Label label = null;
             for (Object obj : list) {
                 bw = new BeanWrapperImpl(cls.cast(obj));
                 for (Object keyObj : keySortArray) {
                     if (fieldMap.containsKey(keyObj)) {
                         try{
                             label = new Label(cols++, rows, obj2Str(bw.getPropertyValue(
                                     fieldMap.get(keyObj).toString())));
                             sheet1.addCell(label);   
                         }catch(org.springframework.beans.NotReadablePropertyException ex) {
                             label = new Label(cols, rows, "找不到属性为'" + fieldMap.get(keyObj) + "'的方法");
                             sheet1.addCell(label);
                         }
                     }
                 }  
                 cols = 0;
                 rows++;
             }
         } catch (IOException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿内容写入失败1："
                     + e.getMessage());
         } catch (RowsExceededException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿内容写入失败2："
                     + e.getMessage());
         } catch (WriteException e) {
             throw new IllegalArgumentException("Excel的Sheet工作簿内容写入失败3："
                     + e.getMessage());
         } catch (BiffException e) {
             throw new IllegalArgumentException("源Excel文件读取失败4："
                     + e.getMessage());
         } finally {
             try {
                 book.write();  
                 book.close();
                 book = null;
             } catch (Exception e) {
                 throw new IllegalArgumentException("Excel文件写入失败，可能因为该文件正在被打开中："
                         + e.getMessage());
             }
         }
     }
     
     private static String obj2Str(Object o) {
         if (o== null){
             return "";
         }
         return o.toString();
     }

 }

调用

        List<XsjlBean> xsjlList = (List) page.getResult();

        Map map =new HashMap();
        map.put("01", "xm|姓名");　　//Map KEY的“01“、”02“、"03"..代表字段排列显示的顺序 ；Map value”xm|姓名“，”xm“ 表示JavaBean的变量，”姓名“表示Excel显示的标题名称；导出那些字段完全依据Map所指定的

        map.put("02", "jsh|监室号");
        map.put("03", "jlsjString|记录时间");
        map.put("04", "wgqkZH|违规情况");
        map.put("05", "clr|处理人、");
        map.put("06", "cljgZH|处理结果");
//另存为Excel文件

        ExcelUtils.saveAsToExcel(xsjlList,map,"c:/abc.xls",XsjlBean.class);

....