jxl生成Excel和读取Excel

2010年05月14日 星期五 07:30

生成： package com.student.ExcelOperate; import java.io.File; import java.io.IOException; import java.util.*; import java.sql.*; import com.student.db.StudentDB; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; public class ExcelOut { /**     * 传入SQL，智能型查询结果到List，包含表头，默认为全String类型     */ private List<List<String>> getDataList(String sql) {    List<List<String>> listList = new ArrayList<List<String>>();    List<String> list = null ;    StudentDB db = new StudentDB();    ResultSet rs = db.SelectTable(sql);    int i = 0 ;    try    {     ResultSetMetaData rsmd = rs.getMetaData();//ResultSet 对象的列的编号、类型和属性     list = new ArrayList<String>();     i = rsmd.getColumnCount();     for(int j=1;j<=i;j++)     {      list.add(rsmd.getColumnLabel(j));//输出和显示的指定列的建议标题      //list.add(getColumnName(j));//输出和显示的原数据列名     }     listList.add(list);     while(rs.next())     {      list = new ArrayList<String>();      for(int j=1;j<=i;j++)      {       list.add(rs.getString(j));      }      listList.add(list);     }    } catch (SQLException e)    {     // TODO Auto-generated catch block     e.printStackTrace();    }finally    {     try {      db.close();     } catch (SQLException e) {      // TODO Auto-generated catch block      e.printStackTrace();     }    }      return listList ; } /**     * 拆分List,因为Excel每sheet页只支持65535行，超过后会写入下一sheet，此处设置为60000行     */ private List<List<List<String>>> getSplitList(String sql) {    List<List<List<String>>> splitList = new ArrayList<List<List<String>>>();    List<List<String>> list = this.getDataList(sql);    int size = list.size();    int unit = 60000 ;    int num = size/unit ;    if(size%unit>0)    {     num+=1;    }    int fromIndex = 0 ; //包括    int toIndex = 0 ; //不包括    for(int i=0;i<num;i++)    {     fromIndex = i*unit ;     if((i+1)*unit>size)     {      toIndex = size ;     }else{      toIndex = (i+1)*unit ;     }        splitList.add(list.subList(fromIndex, toIndex));    }    return splitList ; } /**     * 外部调用接口，传入包含路径的Excel名称和SQL，会在指定路径下生成Excel     */ public WritableWorkbook exportToExcel(String name,String sql) {    WritableWorkbook excel = null;    WritableSheet sheet = null;    List<List<List<String>>> splitList = this.getSplitList(sql);    List<List<String>> infoList = null ;    List<String> list = null ;    File file = new File(name);    if (file.exists())    {     file.delete();    }    try    {     excel = Workbook.createWorkbook(file);     for(int k=0;k<splitList.size();k++)     {      sheet = excel.createSheet("Sheet"+(k+1), k);      infoList = splitList.get(k);      for(int i=0;i<infoList.size();i++)      {       list = infoList.get(i);       for(int j=0;j<list.size();j++)       {        sheet.addCell(new Label(j, i,list.get(j)));       }      }     }     excel.write();     excel.close();    } catch (RowsExceededException e)    {     // TODO Auto-generated catch block     e.printStackTrace();    } catch (WriteException e)    {     // TODO Auto-generated catch block     e.printStackTrace();    } catch (IOException e)    {     // TODO Auto-generated catch block     e.printStackTrace();    }    return excel; } } 读取: import java.io.File; import java.io.IOException; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; public class TestJXL { static File file=new File("D://excel//a.xls"); public static void main(String[] args) {    readExcel(file); } public static String readExcel(File file) {    StringBuffer buf=new StringBuffer("");    try {     Workbook book = Workbook.getWorkbook(file);     Sheet sheet = book.getSheet(0);     int rownum = sheet.getRows();     int colnum = sheet.getColumns();     if (rownum != 0) {      for (int j = 0; j < rownum; j++) {       for (int i = 0; i < colnum; i++){        Cell cell = sheet.getCell(i, j);        String content = cell.getContents();        buf.append(content+",");        System.out.println("(" + i + "," +j + ")="          + content);       }       buf.append("//r//n");      }     }    } catch (BiffException e) {     e.printStackTrace();    } catch (IndexOutOfBoundsException e) {     e.printStackTrace();    } catch (IOException e) {     e.printStackTrace();    }       return buf.toString(); } }     请注意，一定要将jxl.jar包拷到tomcat的lib目录下