流浪天涯

import java.io.File;
import java.util.ArrayList;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class XlsManager
{
 
 /***************
  导出数据到Excel文件中
  下面的例子，设置了数字、日期的格式，还有字体，颜色等。
 **************/
 public static void writeXLS()throws Exception
 {
  
  File tempFile=new File("e:/work/output.xls");
  WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
  WritableSheet sheet = workbook.createSheet("TestCreateExcel", 0);

//  一些临时变量，用于写到excel中
  Label l=null;
  jxl.write.Number n=null;
  jxl.write.DateTime d=null;

//  预定义的一些字体和格式，同一个Excel中最好不要有太多格式
  WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);
  WritableCellFormat headerFormat = new WritableCellFormat (headerFont);

  WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
  WritableCellFormat titleFormat = new WritableCellFormat (titleFont);

  WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
  WritableCellFormat detFormat = new WritableCellFormat (detFont);

  NumberFormat nf=new NumberFormat("0.00000");  //用于Number的格式
  WritableCellFormat priceFormat = new WritableCellFormat (detFont, nf);

  DateFormat df=new DateFormat("yyyy-MM-dd");//用于日期的
  WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);

//  剩下的事情，就是用上面的内容和格式创建一些单元格，再加到sheet中
  l=new Label(0, 0, "用于测试的Excel文件", headerFormat);
  sheet.addCell(l);

//  add Title
  int column=0;
  l=new Label(column++, 2, "标题", titleFormat);
  sheet.addCell(l);
  l=new Label(column++, 2, "日期", titleFormat);
  sheet.addCell(l);
  l=new Label(column++, 2, "货币", titleFormat);
  sheet.addCell(l);
  l=new Label(column++, 2, "价格", titleFormat);
  sheet.addCell(l);

//  add detail
  int i=0;
  column=0;
  l=new Label(column++, i+3, "标题 "+i, detFormat);
  sheet.addCell(l);
  d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);
  sheet.addCell(d);
  l=new Label(column++, i+3, "CNY", detFormat);
  sheet.addCell(l);
  n=new jxl.write.Number(column++, i+3, 5.678, priceFormat);
  sheet.addCell(n);

  i++;
  column=0;
  l=new Label(column++, i+3, "标题 "+i, detFormat);
  sheet.addCell(l);
  d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);
  sheet.addCell(d);
  l=new Label(column++, i+3, "SGD", detFormat);
  sheet.addCell(l);
  n=new jxl.write.Number(column++, i+3, 98832, priceFormat);
  sheet.addCell(n);

//  设置列的宽度
  column=0;
  sheet.setColumnView(column++, 20);
  sheet.setColumnView(column++, 20);
  sheet.setColumnView(column++, 10);
  sheet.setColumnView(column++, 20);

  workbook.write();
  workbook.close();
 }
 public static ArrayList readXLS() throws Exception
 {
  Workbook workbook = null;
  ArrayList list = new ArrayList();
  
  try {
     workbook = Workbook.getWorkbook(new File("d:\\test.xls"));
  } catch (Exception e) {
     throw new Exception("file to import not found!");
  }

  Sheet sheet = workbook.getSheet(0);
  Cell cell = null;

  int columnCount=8;
  int rowCount=sheet.getRows();
  for (int i = 1; i <rowCount; i++) {
   ArrayList sublist =new ArrayList();
     for (int j = 0; j <columnCount; j++) {
         //注意，这里的两个参数，第一个是表示列的，第二才表示行
         cell=sheet.getCell(j, i);
         //要根据单元格的类型分别做处理，否则格式化过的内容可能会不正确
         /*
         if(cell.getType()==CellType.NUMBER){
             System.out.print(((NumberCell)cell).getValue());
         }
         else if(cell.getType()==CellType.DATE){
             System.out.print(((DateCell)cell).getDate());
         }
         else{
             System.out.print(cell.getContents());
         }  
         ***/     

         sublist.add(cell.getContents());
     }
     list.add(sublist);
  }
  //关闭它，否则会有内存泄露
  workbook.close();
  return list;
 }