Flex使用JXL导出Excel文档(包含设置时间、数字格式以及其他相关设置)

import java.util.ArrayList;
import java.util.List;


import javax.servlet.http.HttpServletResponse;


import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.DateFormat;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;


import net.zdsoft.keel.action.ActionContext;

/*
     * Excel 导出使用 文件名  标题 行标题 导出内容
     */
    String fileName;//文件名
    String contentTitle;//内容标题
    String[] coloumTitle;//列标题
    /*******   查询条件       ********/
    String parentid;
    String startTime;
    String stopTime;
    String serarchstr;
    String isover;
    public String exportExcel() {
		// 以下开始输出到EXCEL
    	List<PassVehicle> contentList = new ArrayList<PassVehicle>();
    	InputParamList params = new InputParamList();
    	List<InputParam> paramList = new ArrayList<InputParam>();
    	if(parentid!=null&&!parentid.equals("")){
	    	InputParam param = new InputParam();
	    	param.setName("parentid");
	    	param.setValue(parentid);
	    	paramList.add(param);
    	}
    	if(startTime!=null&&!startTime.equals("")){
	    	InputParam param = new InputParam();
	    	param.setName("startTime");
	    	param.setValue(startTime);
	    	paramList.add(param);
    	}
    	if(stopTime!=null&&!stopTime.equals("")){
	    	InputParam param = new InputParam();
	    	param.setName("endTime");
	    	param.setValue(stopTime);
	    	paramList.add(param);
    	}
    	if(isover!=null&&!isover.equals("")){
	    	InputParam param = new InputParam();
	    	param.setName("isover");
	    	param.setValue(isover);
	    	paramList.add(param);
    	}
    	if(serarchstr!=null&&!serarchstr.equals("")){
    		InputParam param1 = new InputParam();
	    	param1.setName("type");
	    	param1.setValue("plateno");
	    	paramList.add(param1);
	    	InputParam param = new InputParam();
	    	param.setName("data");
	    	param.setValue(serarchstr);
	    	paramList.add(param);
    	}
    	params.setList(paramList);
    	contentList = SpringUtils.getPassVehicleService().queryBySQL(params);
		try {
			// 定义输出流，以便打开保存对话框
			ActionContext.getRequest().setCharacterEncoding("UTF-8");
			System.out.println("文件名："+fileName);
			HttpServletResponse response = ActionContext.getResponse();
			OutputStream os = response.getOutputStream();// 取得输出流
			response.reset();// 清空输出流
			response.setCharacterEncoding("UTF-8");
			response.setHeader("pragma", "no-cache"); 
			response.setHeader("cache-control", "no-cache");
			response.setDateHeader("Expires", 0);
//			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment; filename="
					+ new String(fileName.getBytes("GBK"), "ISO8859-1"));
			// 设定输出文件头
			response.setContentType("application/msexcel");// 定义输出类型
			// 设定输出文件头
			response.setContentType("application/msexcel");// 定义输出类型
			// 定义输出流，以便打开保存对话框_______________________end

			/** **********创建工作簿************ */
			WritableWorkbook workbook = Workbook.createWorkbook(os);

			/** **********创建工作表************ */

			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
			
			/** **********设置行高列宽************ */
//			sheet.setRowView( 0 , 100 );
			sheet.setColumnView( 0 , 20 );
			sheet.setColumnView( 1 , 20 );
			sheet.setColumnView( 2 , 30 );
			sheet.setColumnView( 3 , 10 );
			sheet.setColumnView( 4 , 15 );
			sheet.setColumnView( 5 , 15 );
			sheet.setColumnView( 6 , 15 );
			sheet.setColumnView( 7 , 20 );
			if(isover!=null&&!isover.equals("")){
				sheet.setColumnView( 8 , 20 );
				sheet.setColumnView( 9 , 20 );
				sheet.mergeCells(0, 0, 9, 0);
			}else{
				sheet.mergeCells(0, 0, 7, 0);
			}
			/** **********设置纵横打印（默认为纵打）、打印纸***************** */
			jxl.SheetSettings sheetset = sheet.getSettings();
			sheetset.setProtected(false);

			/** ************设置单元格字体************** */
			WritableFont TitleFont = new WritableFont(WritableFont.createFont("宋体"), 16,
					WritableFont.BOLD);
			WritableFont HeadFont = new WritableFont(WritableFont.createFont("宋体"), 14,
					WritableFont.BOLD);
			WritableFont NormalFont = new WritableFont(WritableFont.createFont("宋体"), 12);
			
			/** ************设置时间、数字 格式************** */
			DateFormat df=new jxl.write.DateFormat("yyyy/MM/dd HH:mm:ss");
			NumberFormat nf = new jxl.write.NumberFormat("#.##");
			/** ************以下设置三种单元格样式，灵活备用************ */
			// 用于标题居中
			WritableCellFormat wcf_title = new WritableCellFormat(TitleFont);
			wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_title.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_title.setWrap(false); // 文字是否换行
			// 用于标题头居中
			WritableCellFormat wcf_head = new WritableCellFormat(HeadFont);
			wcf_head.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_head.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_head.setAlignment(Alignment.CENTRE); // 文字水平对齐
			wcf_head.setWrap(false); // 文字是否换行
			// 用于正文居左
			WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
			wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
			wcf_left.setWrap(false); // 文字是否换行
			// 用于时间单元格
			WritableCellFormat wcf_date = new WritableCellFormat(NormalFont,df);
			wcf_date.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_date.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_date.setAlignment(Alignment.LEFT); // 文字水平对齐
			wcf_date.setWrap(false); // 文字是否换行
			// 用于数字单元格
			WritableCellFormat wcf_num = new WritableCellFormat(NormalFont,NumberFormats.FLOAT);
			wcf_num.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			wcf_num.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			wcf_num.setAlignment(Alignment.LEFT); // 文字水平对齐
			wcf_num.setWrap(false); // 文字是否换行			
			
			/** ***************以下是EXCEL开头大标题********************* */
			if(contentTitle!=null&&!contentTitle.equals("")){
				sheet.addCell(new Label(0, 0, contentTitle, wcf_title));
			}
			/** ***************以下是EXCEL第一行列标题********************* */
			for (int i = 0; i < coloumTitle.length; i++) {
				sheet.addCell(new Label(i, 1, coloumTitle[i], wcf_head));
			}
			/** ***************以下是EXCEL正文数据********************* */
			int i = 2;
			if(contentList!=null){
				if(isover==null||isover.equals("")){
					for (PassVehicle obj : contentList) {
						sheet.addCell(new Label(0, i, obj.getName(), wcf_left));
						sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));
						sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));
						sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));
						sheet.addCell(new jxl.write.Number(4, i, obj.getSelfWeight(), wcf_left));
						sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_left));
						sheet.addCell(new jxl.write.Number(6, i, obj.getCommodityWeight(), wcf_num));
						sheet.addCell(new Label(7, i, obj.getRemarks(), wcf_left));
						i++;
					}
				}else{
					for (PassVehicle obj : contentList) {
						sheet.addCell(new Label(0, i, obj.getName(), wcf_left));
						sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));
						sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));
						sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));
						sheet.addCell(new jxl.write.Number(4, i, obj.getLimitWeight(), wcf_num));
						sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_num));
						sheet.addCell(new jxl.write.Number(6, i, obj.getOverWeight(), wcf_num));
						sheet.addCell(new Label(7, i, obj.getCarOwer(), wcf_left));
						sheet.addCell(new Label(8, i, obj.getMobile(), wcf_left));
						sheet.addCell(new Label(9, i, obj.getRemarks(), wcf_left));
						i++;
					}
				}
			}
			/** **********将以上缓存中的内容写到EXCEL文件中******** */
			workbook.write();
			/** *********关闭文件************* */
			workbook.close();

		} catch (Exception e) {
			System.out.println("系统提示：Excel文件导出失败，原因："+ e.toString());
			e.printStackTrace();
		}
		return SUCCESS;
	}

import java.io.File;  
import java.io.FileOutputStream;  
import java.io.OutputStream;  
import java.util.ArrayList;  
import java.util.Date;  
 
import jxl.Cell;  
import jxl.CellType;  
import jxl.Sheet;  
import jxl.Workbook;  
import jxl.WorkbookSettings;  
import jxl.format.Alignment;  
import jxl.format.Border;  
import jxl.format.BorderLineStyle;  
import jxl.format.Colour;  
import jxl.format.VerticalAlignment;  
import jxl.write.Formula;  
import jxl.write.Label;  
import jxl.write.NumberFormat;  
import jxl.write.WritableCellFeatures;  
import jxl.write.WritableCellFormat;  
import jxl.write.WritableFont;  
import jxl.write.WritableSheet;  
import jxl.write.WritableWorkbook;  
import jxl.write.WriteException;  
 
public class JExcelUtils {  
 
    /** 
     * 生成Excel文件 
     * @param path         文件路径 
     * @param sheetName    工作表名称 
     * @param dataTitles   数据标题 
     */ 
   public void createExcelFile(String path,String sheetName,String[] dataTitles){  
       WritableWorkbook workbook;  
       try{  
           OutputStream os=new FileOutputStream(path);   
           workbook=Workbook.createWorkbook(os);   
 
           WritableSheet sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表  
           initialSheetSetting(sheet);  
             
           Label label;  
           for (int i=0; i<dataTitles.length; i++){  
               //Label(列号,行号,内容,风格)  
               label = new Label(i, 0, dataTitles[i],getTitleCellFormat());   
               sheet.addCell(label);   
           }  
 
           //插入一行  
           insertRowData(sheet,1,new String[]{"200201001","张三","100","60","100","260"},getDataCellFormat(CellType.STRING_FORMULA));  
             
           //一个一个插入行  
           label = new Label(0, 2,"200201002",getDataCellFormat(CellType.STRING_FORMULA));   
           sheet.addCell(label);  
             
           label = new Label(1, 2,"李四",getDataCellFormat(CellType.STRING_FORMULA));   
           sheet.addCell(label);  
             
           insertOneCellData(sheet,2,2,70.5,getDataCellFormat(CellType.NUMBER));  
           insertOneCellData(sheet,3,2,90.523,getDataCellFormat(CellType.NUMBER));  
           insertOneCellData(sheet,4,2,60.5,getDataCellFormat(CellType.NUMBER));  
 
           insertFormula(sheet,5,2,"C3+D3+E3",getDataCellFormat(CellType.NUMBER_FORMULA));  
             
           //插入日期  
           mergeCellsAndInsertData(sheet, 0, 3, 5, 3, new Date(), getDataCellFormat(CellType.DATE));  
             
           workbook.write();   
           workbook.close();  
       }catch(Exception e){  
           e.printStackTrace();  
       }  
   }  
     
   /** 
    * 初始化表格属性 
    * @param sheet 
    */ 
   public void initialSheetSetting(WritableSheet sheet){  
      try{  
           //sheet.getSettings().setProtected(true); //设置xls的保护，单元格为只读的  
           sheet.getSettings().setDefaultColumnWidth(10); //设置列的默认宽度  
           //sheet.setRowView(2,false);//行高自动扩展   
           //setRowView(int row, int height);--行高   
           //setColumnView(int  col,int width); --列宽  
           sheet.setColumnView(0,20);//设置第一列宽度  
      }catch(Exception e){  
          e.printStackTrace();  
      }  
   }  
     
   /** 
    * 插入公式 
    * @param sheet 
    * @param col 
    * @param row 
    * @param formula 
    * @param format 
    */ 
   public void insertFormula(WritableSheet sheet,Integer col,Integer row,String formula,WritableCellFormat format){  
       try{  
           Formula f = new Formula(col, row, formula, format);  
           sheet.addCell(f);  
       }catch(Exception e){  
           e.printStackTrace();  
       }  
   }  
     
   /** 
    * 插入一行数据 
    * @param sheet       工作表 
    * @param row         行号 
    * @param content     内容 
    * @param format      风格 
    */ 
   public void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){  
       try{  
           Label label;  
           for(int i=0;i<dataArr.length;i++){  
               label = new Label(i,row,dataArr[i],format);  
               sheet.addCell(label);  
           }  
       }catch(Exception e){  
           e.printStackTrace();  
       }  
   }  
     
   /** 
    * 插入单元格数据 
    * @param sheet 
    * @param col 
    * @param row 
    * @param data 
    */ 
   public void insertOneCellData(WritableSheet sheet,Integer col,Integer row,Object data,WritableCellFormat format){  
       try{  
           if(data instanceof Double){  
               jxl.write.Number  labelNF = new jxl.write.Number(col,row,(Double)data,format);   
               sheet.addCell(labelNF);   
           }else if(data instanceof Boolean){  
               jxl.write.Boolean labelB = new jxl.write.Boolean(col,row,(Boolean)data,format);   
               sheet.addCell(labelB);   
           }else if(data instanceof Date){  
               jxl.write.DateTime labelDT = new jxl.write.DateTime(col,row,(Date)data,format);   
               sheet.addCell(labelDT);   
               setCellComments(labelDT, "这是个创建表的日期说明！");  
           }else{  
               Label label = new Label(col,row,data.toString(),format);  
               sheet.addCell(label);                 
           }  
       }catch(Exception e){  
           e.printStackTrace();  
       }  
 
  }  
     
   /** 
    * 合并单元格，并插入数据 
    * @param sheet 
    * @param col_start 
    * @param row_start 
    * @param col_end 
    * @param row_end 
    * @param data 
    * @param format 
    */ 
   public void mergeCellsAndInsertData(WritableSheet sheet,Integer col_start,Integer row_start,Integer col_end,Integer row_end,Object data, WritableCellFormat format){  
      try{  
          sheet.mergeCells(col_start,row_start,col_end,row_end);// 左上角到右下角  
          insertOneCellData(sheet, col_start, row_start, data, format);  
      }catch(Exception e){  
          e.printStackTrace();  
      }  
 
   }  
     
   /** 
    * 给单元格加注释 
    * @param label 
    * @param comments 
    */ 
   public void setCellComments(Object label,String comments){  
       WritableCellFeatures cellFeatures = new WritableCellFeatures();  
       cellFeatures.setComment(comments);  
       if(label instanceof jxl.write.Number){  
           jxl.write.Number num = (jxl.write.Number)label;  
           num.setCellFeatures(cellFeatures);  
       }else if(label instanceof jxl.write.Boolean){  
           jxl.write.Boolean bool = (jxl.write.Boolean)label;  
           bool.setCellFeatures(cellFeatures);  
       }else if(label instanceof jxl.write.DateTime){  
           jxl.write.DateTime dt = (jxl.write.DateTime)label;  
           dt.setCellFeatures(cellFeatures);  
       }else{  
           Label _label = (Label)label;  
           _label.setCellFeatures(cellFeatures);  
       }  
   }  
     
   /** 
   * 读取excel 
   * @param inputFile 
   * @param inputFileSheetIndex 
   * @throws Exception 
   */ 
   public ArrayList<String> readDataFromExcel(File inputFile, int inputFileSheetIndex){  
      ArrayList<String> list = new ArrayList<String>();  
      Workbook book = null;  
      Cell cell = null;  
      WorkbookSettings setting = new WorkbookSettings();   
      java.util.Locale locale = new java.util.Locale("zh","CN");   
      setting.setLocale(locale);  
      setting.setEncoding("ISO-8859-1");  
      try{  
          book = Workbook.getWorkbook(inputFile, setting);  
      }catch(Exception e){  
          e.printStackTrace();    
      }  
 
      Sheet sheet = book.getSheet(inputFileSheetIndex);  
      for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {//行  
       for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {//列  
           cell = sheet.getCell(colIndex, rowIndex);  
           //System.out.println(cell.getContents());  
           list.add(cell.getContents());  
       }  
      }  
      book.close();  
 
      return list;  
   }  
 
   /** 
    * 得到数据表头格式 
    * @return 
    */ 
   public WritableCellFormat getTitleCellFormat(){  
       WritableCellFormat wcf = null;  
       try {  
           //字体样式  
           WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic  
           wf.setColour(Colour.RED);  
           wcf = new WritableCellFormat(wf);  
           //对齐方式  
           wcf.setAlignment(Alignment.CENTRE);  
           wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
           //边框  
           wcf.setBorder(Border.ALL,BorderLineStyle.THIN);  
             
           //背景色  
           wcf.setBackground(Colour.GREY_25_PERCENT);  
       } catch (WriteException e) {  
        e.printStackTrace();  
       }  
       return wcf;  
   }  
     
   /** 
    * 得到数据格式 
    * @return 
    */ 
   public WritableCellFormat getDataCellFormat(CellType type){  
       WritableCellFormat wcf = null;  
       try {  
           //字体样式  
           if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字  
              NumberFormat nf = new NumberFormat("#.00");  
              wcf = new WritableCellFormat(nf);   
           }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期  
               jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");   
               wcf = new jxl.write.WritableCellFormat(df);   
           }else{  
               WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic  
               wcf = new WritableCellFormat(wf);  
           }  
           //对齐方式  
           wcf.setAlignment(Alignment.CENTRE);  
           wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
           //边框  
           wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);  
           wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);  
           wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);  
           //背景色  
           wcf.setBackground(Colour.WHITE);  
             
           wcf.setWrap(true);//自动换行  
             
       } catch (WriteException e) {  
        e.printStackTrace();  
       }  
       return wcf;  
   }  
     
   /** 
    * 打开文件看看 
    * @param exePath 
    * @param filePath 
    */ 
   public void openExcel(String exePath,String filePath){  
       Runtime r=Runtime.getRuntime();   
       String cmd[]={exePath,filePath};   
       try{   
           r.exec(cmd);   
       }catch(Exception e){  
           e.printStackTrace();  
       }  
   }  
     
   public static void main(String[] args){  
       String[] titles = {"学号","姓名","语文","数学","英语","总分"};   
       JExcelUtils jxl = new JExcelUtils();  
       String filePath = "E:/test.xls";  
       jxl.createExcelFile(filePath," 成绩单",titles);  
       jxl.readDataFromExcel(new File(filePath),0);  
       jxl.openExcel("C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE",filePath);  
   }  
} 

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * jxl操作excel的工具类.
 *
 */
public class JxlTool {
	public static int count = 1;
	//存储带有级别信息的内容到位置的映射关系.
	private static Map levelToLocation = new HashMap();
	
	public static void readExcel(String fileName) {
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(new File(fileName));
			Sheet[] sheets = wb.getSheets();
			for(int i=0;i<sheets.length;i++){
				Sheet ii = sheets[i];
				System.out.println("第"+i+"个sheet的名字是"+ii.getName());
			}
		} catch (Exception e) {
			System.out.println("出现异常" + e);
			e.printStackTrace();
		} finally {
			wb.close();
		}
	}
	
	private static String allChar = "abcdefghijklmnopqrstuvwxyz";
	/**
	 * 从字符中得到列数.例如K-->10,A-->0,AA-->27
	 * @return
	 */
	public static int getNumFromExcelStr(String code)
 {
  int result = 0;
  code = code.toLowerCase();
  if(code.length()>1){
   char[] c = code.toCharArray();
   int len = c.length;
   for(int i=0;i<len;i++){
    if(i<len-1){
     result+=(allChar.indexOf(c[i])+1)*26; 
    }else{
     result+=allChar.indexOf(c[i])+1;
    }
   }
   result-=1;
  }
  else
   return allChar.indexOf(code);
  return result;
 }	
	/**
	 * 根据行号和列号得到所在的单元格.例如(3,4)-->"E4"
	 * @param vNum 纵坐标
	 * @param hNum 横坐标
	 * @return
	 */
	public static String getCellInfo(int hNum,int vNum){
		char[] cs = allChar.toCharArray();
		String hStr = "";
		if(vNum>25){
			hStr = String.valueOf(cs[vNum/26-1])+String.valueOf(cs[vNum%26-1]);
		}else{
			hStr = String.valueOf(cs[vNum]);
		}
		return (hStr+Integer.toString((hNum+1))).toUpperCase();
	}

	/**
	 * 得到一个字符串里面的字符.A12-->A
	 * @param oldStr
	 * @return
	 */
	public static String getCodeFromStr(String oldStr){
		return oldStr.replaceAll("\\d", "");
	}
	
	/**
	 * 得到一个字符串里面的字符.A12-->12
	 * @param oldStr
	 * @return
	 */
	public static int getNumFromStr(String oldStr){
		return Integer.parseInt(oldStr.replaceAll("[a-zA-Z]", ""))-1;
	}
	
	/**
	 * 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.
	 * @param fileName
	 * @param sheetIndex
	 * @param startRow
	 * @param endRow
	 * @param startColumn
	 * @param endColumn
	 */
	public static List readExcel(String fileName, int sheetIndex, int startRow,
			int endRow, int startColumn, int endColumn) {
		Workbook wb = null;
		List allData = new ArrayList();
		Cell cell = null;
		try {
			wb = Workbook.getWorkbook(new File(fileName));
			Sheet sheet = wb.getSheet(sheetIndex);
			int rowCount = sheet.getRows();
			int columnCount = sheet.getColumns();
			for (int r = startRow; r < rowCount && r <= endRow; r++) {// 行
				for (int c = startColumn; c < columnCount && c <= endColumn; c++) {// 列
					cell = sheet.getCell(c, r);
					// System.out.println(cell.getContents());
					allData.add(cell.getContents());
				}
			}
		} catch (Exception e) {
			System.out.println("出现异常" + e);
			e.printStackTrace();
		} finally {
			wb.close();
		}
		return allData;
	}
	
	/**
	 * 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.
	 * @param fileName
	 * @param sheetIndex
	 * @param startCell
	 * @param endCell
	 * @return
	 */
	public static List readExcel(String fileName, int sheetIndex,String startCell, String endCell) {
		int startRow = getNumFromStr(startCell);
		int endRow = getNumFromStr(endCell);
		int startColumn=getNumFromExcelStr(getCodeFromStr(startCell));
		int endColumn = getNumFromExcelStr(getCodeFromStr(endCell));
		return readExcel(fileName, sheetIndex, startRow, endRow, startColumn,
				endColumn);
	}
		
	/**
	 * 设置excel中的sheet页全部隐藏
	 * @param fileName
	 */
	public static void setAllHiddenSheet(String fileName) {
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(new File(fileName));
			// 打开一个文件副本,并指定数据写回原文件.
			WritableWorkbook book = Workbook.createWorkbook(new File(fileName),
					wb);
			Sheet[] sheets = book.getSheets();
			for(int i=3;i<sheets.length;i++){
				Sheet ii = sheets[i];
				ii.getSettings().setHidden(true);
			}
			book.write();
			book.close();
		} catch (Exception e) {
			System.out.println("出现异常" + e);
			e.printStackTrace();
		} finally {
			wb.close();
			System.out.print(111);
		}
	} 

       /**
          * 从行号和列号，得到所在的位置字符串，例如：row=7，col=7--->i8
          */
        public  static String getcodefromRC(int row,int col){
		char[] cc = allChar.toCharArray();
		return String.valueOf(cc[col])+(++row);
	}
    
	/**
	 * 添加一个新的sheet到指定excel文件
	 * @param fileName
	 * @param sheetName sheet的name
	 */
	public static void addNewSheet(String fileName,String sheetName) {
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(new File(fileName));
			// 打开一个文件副本,并指定数据写回原文件.
			WritableWorkbook book = Workbook.createWorkbook(new File(fileName),
					wb);
			// 创建一个新的sheet到第2页的位置			
			String[] sheetNames = wb.getSheetNames();
			for(int i=0;i<sheetNames.length;i++){
				if(sheetNames[i].equals(sheetName)){
					System.out.println("已经存在了,不用添加了." );
					return ;
				}
			}
			WritableSheet sheet = book.createSheet(sheetName, 1);
			sheet.addCell(new Label(0, 0, "新加的测试数据"));
			book.write();
			book.close();
		} catch (Exception e) {
			System.out.println("出现异常" + e);
			e.printStackTrace();
		} finally {
			wb.close();
		}
	} 

        /**
          * 得到单元格的double内容，不可以直接使用cell.getContents()，因为这个方法是直接打印单元格内容
，单元格内容可能隐藏了后面的小数点！！        
          */
         public static double getNumber(Cell cell){
             NumberCell numberCell = (NumberCell)cell; 
            double namberValue = numberCell.getValue(); 
             return   namberValue ;         
         }

         /**
	 * 如果是公式返回公式的内容，否则返回单元格字符串表面内容
	 * @param c
	 * @return
	 */
         public static String getForJmulaStr(Cell c) {
		String ans = "";
		try {
			System.out.println(c.getType());
			if (c.getType() == CellType.NUMBER_FORMULA
					|| c.getType() == CellType.STRING_FORMULA
					|| c.getType() == CellType.BOOLEAN_FORMULA
					|| c.getType() == CellType.DATE_FORMULA
					|| c.getType() == CellType.FORMULA_ERROR) {
				FormulaCell nfc = (FormulaCell) c;
				ans = nfc.getFormula();
				
			} else {
				ans = c.getContents();
			}
		} catch (FormulaException e) {
			return "出现异常" + e.getMessage();
		} 
		return ans;
	}

      //得到指定位置单元格的值(普通单元格,数字单元格,日期单元格)
       private String getValue(Sheet sheet,int row,int col){
    	Cell cell=sheet.getCell(col, row);   
    	CellType cellType=cell.getType();
		NumberCell numberCell = null;
		String cellValue = "";
        //得到单元格的值
        if (cellType == CellType.NUMBER) {
	      numberCell = (NumberCell) cell;
	      cellValue = String.valueOf(numberCell.getValue());
       } else if (cellType == CellType.DATE) {
				cellValue = df.format(((DateCell) cell).getDate());
	 } else if (cellType == CellType.NUMBER_FORMULA) { 
		 // 形如：=123.232+3423.12
		 // 或者 =B2+123.12
		 NumberFormulaCell numberFormulaCell = (NumberFormulaCell) cell;
		 cellValue = String.valueOf(numberFormulaCell.getValue());
	 } else {
		 cellValue = cell.getContents();
	 }       
       cellValue=cellValue.replace(" ", "");  
        return cellValue;
    }
}