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

Flex端调用代码：

private function export():void{if (this.passVehicleList.length <= 0){MessageDlg.confirm("数据为空，不能导出！", null);return;}parentname=parentname.replace(" ","");//去除名称中空格var startDate:Date = timeStart.appendTimevalue(dateStart.selectedDate);var stopDate:Date = timeStop.appendTimevalue(dateStop.selectedDate)var startTimes:String = MyStringUtil.datetimetoString(startDate);var stopTimes:String = MyStringUtil.datetimetoString(stopDate);var serarchstr:String = searchStr.text;inputParams.getParam("parentid").value = parentid;if(isOver){inputParams.getParam("isover").value = "over";}var contentTitle:String = startTimes+"至"+stopTimes+parentname;var reportName:String = parentname;var variables:URLVariables=new URLVariables();variables.parentid = parentid;variables.startTime = startTimes;variables.stopTime = stopTimes;variables.serarchstr = serarchstr;var coloumTitle:Array;if(!isOver){contentTitle=contentTitle+"日常报表";reportName=reportName+"日常报表";variables.isover = "";coloumTitle = new Array("名称","牌号","时间","类型","自重","载重","货重","备注");}else{contentTitle=contentTitle+"超载报表";reportName=reportName+"超载报表";variables.isover = "over";coloumTitle = new Array("名称","牌号","时间","车型","限重","载重","超载","车主","移动电话","备注");}variables.fileName=reportName+".xls";variables.contentTitle=contentTitle;variables.coloumTitle=coloumTitle;var u:URLRequest=new URLRequest(MyStringUtil.getUrlPath(Application.application.loaderInfo.url) + '/rest/ProjectAction.action');u.data=variables; //Pass the variablesu.method=URLRequestMethod.POST; //Don't forget that we need to send as POSTnavigateToURL(u, "_self");}

使用variables传递所需要参数（包含Java端需要的查询条件、文件名、列标题）

Java服务端代码：

说明：需要jxl.jar包

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;}

相关资源下载地址：

jxl.jar工具包

jxl Api开发文档

其他jxl相关操作参考代码：

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;    }}