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

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

Flex端调用代码:

[html]

  1. private function export():void  
  2.             {  
  3.                 if (this.passVehicleList.length <= 0)  
  4.                 {  
  5.                     MessageDlg.confirm("数据为空,不能导出!", null);  
  6.                     return;  
  7.                 }  
  8.                 parentname=parentname.replace(" ","");//去除名称中空格  
  9.                 var startDate:Date = timeStart.appendTimevalue(dateStart.selectedDate);  
  10.                 var stopDate:Date = timeStop.appendTimevalue(dateStop.selectedDate)  
  11.                       
  12.                 var startTimes:String = MyStringUtil.datetimetoString(startDate);  
  13.                 var stopTimes:String = MyStringUtil.datetimetoString(stopDate);  
  14.                 var serarchstr:String = searchStr.text;  
  15.                 inputParams.getParam("parentid").value = parentid;  
  16.                 if(isOver){  
  17.                     inputParams.getParam("isover").value = "over";  
  18.                 }  
  19.                   
  20.                 var contentTitle:String = startTimes+"至"+stopTimes+parentname;  
  21.                 var reportName:String = parentname;  
  22.                 var variables:URLVariables=new URLVariables();  
  23.                 variables.parentid = parentid;  
  24.                 variables.startTime = startTimes;  
  25.                 variables.stopTime = stopTimes;  
  26.                 variables.serarchstr = serarchstr;  
  27.                 var coloumTitle:Array;  
  28.                 if(!isOver){  
  29.                     contentTitle=contentTitle+"日常报表";  
  30.                     reportName=reportName+"日常报表";  
  31.                     variables.isover = "";  
  32.                     coloumTitle = new Array("名称","牌号","时间","类型","自重","载重","货重","备注");  
  33.                 }else{  
  34.                     contentTitle=contentTitle+"超载报表";  
  35.                     reportName=reportName+"超载报表";  
  36.                     variables.isover = "over";  
  37.                     coloumTitle = new Array("名称","牌号","时间","车型","限重","载重","超载","车主","移动电话","备注");  
  38.                 }  
  39.                 variables.fileName=reportName+".xls";  
  40.                 variables.contentTitle=contentTitle;  
  41.                 variables.coloumTitle=coloumTitle;  
  42.                 var u:URLRequest=new URLRequest(MyStringUtil.getUrlPath(Application.application.loaderInfo.url) + '/rest/ProjectAction.action');  
  43.                 u.data=variables; //Pass the variables  
  44.                 u.method=URLRequestMethod.POST; //Don't forget that we need to send as POST  
  45.                 navigateToURL(u, "_self");  
  46.             }  

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

Java服务端代码:

说明:需要jxl.jar包
[html]

  1. import java.util.ArrayList;  
  2. import java.util.List;  
  3.   
  4.   
  5. import javax.servlet.http.HttpServletResponse;  
  6.   
  7.   
  8. import jxl.Workbook;  
  9. import jxl.format.Alignment;  
  10. import jxl.format.Border;  
  11. import jxl.format.BorderLineStyle;  
  12. import jxl.format.VerticalAlignment;  
  13. import jxl.write.Label;  
  14. import jxl.write.WritableCellFormat;  
  15. import jxl.write.WritableFont;  
  16. import jxl.write.WritableSheet;  
  17. import jxl.write.WritableWorkbook;  
  18. import jxl.write.DateFormat;  
  19. import jxl.write.NumberFormat;  
  20. import jxl.write.NumberFormats;  
  21.   
  22.   
  23. import net.zdsoft.keel.action.ActionContext;  
[html]
  1. /*  
  2.      * Excel 导出使用 文件名  标题 行标题 导出内容  
  3.      */  
  4.     String fileName;//文件名  
  5.     String contentTitle;//内容标题  
  6.     String[] coloumTitle;//列标题  
  7.     /*******   查询条件       ********/  
  8.     String parentid;  
  9.     String startTime;  
  10.     String stopTime;  
  11.     String serarchstr;  
  12.     String isover;  
  13.     public String exportExcel() {  
  14.         // 以下开始输出到EXCEL  
  15.         List<PassVehicle> contentList = new ArrayList<PassVehicle>();  
  16.         InputParamList params = new InputParamList();  
  17.         List<InputParam> paramList = new ArrayList<InputParam>();  
  18.         if(parentid!=null&&!parentid.equals("")){  
  19.             InputParam param = new InputParam();  
  20.             param.setName("parentid");  
  21.             param.setValue(parentid);  
  22.             paramList.add(param);  
  23.         }  
  24.         if(startTime!=null&&!startTime.equals("")){  
  25.             InputParam param = new InputParam();  
  26.             param.setName("startTime");  
  27.             param.setValue(startTime);  
  28.             paramList.add(param);  
  29.         }  
  30.         if(stopTime!=null&&!stopTime.equals("")){  
  31.             InputParam param = new InputParam();  
  32.             param.setName("endTime");  
  33.             param.setValue(stopTime);  
  34.             paramList.add(param);  
  35.         }  
  36.         if(isover!=null&&!isover.equals("")){  
  37.             InputParam param = new InputParam();  
  38.             param.setName("isover");  
  39.             param.setValue(isover);  
  40.             paramList.add(param);  
  41.         }  
  42.         if(serarchstr!=null&&!serarchstr.equals("")){  
  43.             InputParam param1 = new InputParam();  
  44.             param1.setName("type");  
  45.             param1.setValue("plateno");  
  46.             paramList.add(param1);  
  47.             InputParam param = new InputParam();  
  48.             param.setName("data");  
  49.             param.setValue(serarchstr);  
  50.             paramList.add(param);  
  51.         }  
  52.         params.setList(paramList);  
  53.         contentList = SpringUtils.getPassVehicleService().queryBySQL(params);  
  54.         try {  
  55.             // 定义输出流,以便打开保存对话框  
  56.             ActionContext.getRequest().setCharacterEncoding("UTF-8");  
  57.             System.out.println("文件名:"+fileName);  
  58.             HttpServletResponse response = ActionContext.getResponse();  
  59.             OutputStream os = response.getOutputStream();// 取得输出流  
  60.             response.reset();// 清空输出流  
  61.             response.setCharacterEncoding("UTF-8");  
  62.             response.setHeader("pragma", "no-cache");   
  63.             response.setHeader("cache-control", "no-cache");  
  64.             response.setDateHeader("Expires", 0);  
  65. //          response.setContentType("application/vnd.ms-excel");  
  66.             response.setHeader("Content-disposition", "attachment; filename="  
  67.                     + new String(fileName.getBytes("GBK"), "ISO8859-1"));  
  68.             // 设定输出文件头  
  69.             response.setContentType("application/msexcel");// 定义输出类型  
  70.             // 设定输出文件头  
  71.             response.setContentType("application/msexcel");// 定义输出类型  
  72.             // 定义输出流,以便打开保存对话框_______________________end  
  73.   
  74.             /** **********创建工作簿************ */  
  75.             WritableWorkbook workbook = Workbook.createWorkbook(os);  
  76.   
  77.             /** **********创建工作表************ */  
  78.   
  79.             WritableSheet sheet = workbook.createSheet("Sheet1", 0);  
  80.               
  81.             /** **********设置行高列宽************ */  
  82. //          sheet.setRowView( 0 , 100 );  
  83.             sheet.setColumnView( 0 , 20 );  
  84.             sheet.setColumnView( 1 , 20 );  
  85.             sheet.setColumnView( 2 , 30 );  
  86.             sheet.setColumnView( 3 , 10 );  
  87.             sheet.setColumnView( 4 , 15 );  
  88.             sheet.setColumnView( 5 , 15 );  
  89.             sheet.setColumnView( 6 , 15 );  
  90.             sheet.setColumnView( 7 , 20 );  
  91.             if(isover!=null&&!isover.equals("")){  
  92.                 sheet.setColumnView( 8 , 20 );  
  93.                 sheet.setColumnView( 9 , 20 );  
  94.                 sheet.mergeCells(0, 0, 9, 0);  
  95.             }else{  
  96.                 sheet.mergeCells(0, 0, 7, 0);  
  97.             }  
  98.             /** **********设置纵横打印(默认为纵打)、打印纸***************** */  
  99.             jxl.SheetSettings sheetset = sheet.getSettings();  
  100.             sheetset.setProtected(false);  
  101.   
  102.             /** ************设置单元格字体************** */  
  103.             WritableFont TitleFont = new WritableFont(WritableFont.createFont("宋体"), 16,  
  104.                     WritableFont.BOLD);  
  105.             WritableFont HeadFont = new WritableFont(WritableFont.createFont("宋体"), 14,  
  106.                     WritableFont.BOLD);  
  107.             WritableFont NormalFont = new WritableFont(WritableFont.createFont("宋体"), 12);  
  108.               
  109.             /** ************设置时间、数字 格式************** */  
  110.             DateFormat df=new jxl.write.DateFormat("yyyy/MM/dd HH:mm:ss");  
  111.             NumberFormat nf = new jxl.write.NumberFormat("#.##");  
  112.             /** ************以下设置三种单元格样式,灵活备用************ */  
  113.             // 用于标题居中  
  114.             WritableCellFormat wcf_title = new WritableCellFormat(TitleFont);  
  115.             wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
  116.             wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
  117.             wcf_title.setAlignment(Alignment.CENTRE); // 文字水平对齐  
  118.             wcf_title.setWrap(false); // 文字是否换行  
  119.             // 用于标题头居中  
  120.             WritableCellFormat wcf_head = new WritableCellFormat(HeadFont);  
  121.             wcf_head.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
  122.             wcf_head.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
  123.             wcf_head.setAlignment(Alignment.CENTRE); // 文字水平对齐  
  124.             wcf_head.setWrap(false); // 文字是否换行  
  125.             // 用于正文居左  
  126.             WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);  
  127.             wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
  128.             wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
  129.             wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐  
  130.             wcf_left.setWrap(false); // 文字是否换行  
  131.             // 用于时间单元格  
  132.             WritableCellFormat wcf_date = new WritableCellFormat(NormalFont,df);  
  133.             wcf_date.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
  134.             wcf_date.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
  135.             wcf_date.setAlignment(Alignment.LEFT); // 文字水平对齐  
  136.             wcf_date.setWrap(false); // 文字是否换行  
  137.             // 用于数字单元格  
  138.             WritableCellFormat wcf_num = new WritableCellFormat(NormalFont,NumberFormats.FLOAT);  
  139.             wcf_num.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
  140.             wcf_num.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
  141.             wcf_num.setAlignment(Alignment.LEFT); // 文字水平对齐  
  142.             wcf_num.setWrap(false); // 文字是否换行             
  143.               
  144.             /** ***************以下是EXCEL开头大标题********************* */  
  145.             if(contentTitle!=null&&!contentTitle.equals("")){  
  146.                 sheet.addCell(new Label(0, 0, contentTitle, wcf_title));  
  147.             }  
  148.             /** ***************以下是EXCEL第一行列标题********************* */  
  149.             for (int i = 0; i < coloumTitle.length; i++) {  
  150.                 sheet.addCell(new Label(i, 1, coloumTitle[i], wcf_head));  
  151.             }  
  152.             /** ***************以下是EXCEL正文数据********************* */  
  153.             int i = 2;  
  154.             if(contentList!=null){  
  155.                 if(isover==null||isover.equals("")){  
  156.                     for (PassVehicle obj : contentList) {  
  157.                         sheet.addCell(new Label(0, i, obj.getName(), wcf_left));  
  158.                         sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));  
  159.                         sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));  
  160.                         sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));  
  161.                         sheet.addCell(new jxl.write.Number(4, i, obj.getSelfWeight(), wcf_left));  
  162.                         sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_left));  
  163.                         sheet.addCell(new jxl.write.Number(6, i, obj.getCommodityWeight(), wcf_num));  
  164.                         sheet.addCell(new Label(7, i, obj.getRemarks(), wcf_left));  
  165.                         i++;  
  166.                     }  
  167.                 }else{  
  168.                     for (PassVehicle obj : contentList) {  
  169.                         sheet.addCell(new Label(0, i, obj.getName(), wcf_left));  
  170.                         sheet.addCell(new Label(1, i, obj.getPlateNo(), wcf_left));  
  171.                         sheet.addCell(new jxl.write.DateTime(2, i, obj.getPassTime(), wcf_date));  
  172.                         sheet.addCell(new Label(3, i, obj.getVehicleType(), wcf_left));  
  173.                         sheet.addCell(new jxl.write.Number(4, i, obj.getLimitWeight(), wcf_num));  
  174.                         sheet.addCell(new jxl.write.Number(5, i, obj.getLoadWeight(), wcf_num));  
  175.                         sheet.addCell(new jxl.write.Number(6, i, obj.getOverWeight(), wcf_num));  
  176.                         sheet.addCell(new Label(7, i, obj.getCarOwer(), wcf_left));  
  177.                         sheet.addCell(new Label(8, i, obj.getMobile(), wcf_left));  
  178.                         sheet.addCell(new Label(9, i, obj.getRemarks(), wcf_left));  
  179.                         i++;  
  180.                     }  
  181.                 }  
  182.             }  
  183.             /** **********将以上缓存中的内容写到EXCEL文件中******** */  
  184.             workbook.write();  
  185.             /** *********关闭文件************* */  
  186.             workbook.close();  
  187.   
  188.         } catch (Exception e) {  
  189.             System.out.println("系统提示:Excel文件导出失败,原因:"+ e.toString());  
  190.             e.printStackTrace();  
  191.         }  
  192.         return SUCCESS;  
  193.     }  

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

[html]

  1. import java.io.File;    
  2. import java.io.FileOutputStream;    
  3. import java.io.OutputStream;    
  4. import java.util.ArrayList;    
  5. import java.util.Date;    
  6.    
  7. import jxl.Cell;    
  8. import jxl.CellType;    
  9. import jxl.Sheet;    
  10. import jxl.Workbook;    
  11. import jxl.WorkbookSettings;    
  12. import jxl.format.Alignment;    
  13. import jxl.format.Border;    
  14. import jxl.format.BorderLineStyle;    
  15. import jxl.format.Colour;    
  16. import jxl.format.VerticalAlignment;    
  17. import jxl.write.Formula;    
  18. import jxl.write.Label;    
  19. import jxl.write.NumberFormat;    
  20. import jxl.write.WritableCellFeatures;    
  21. import jxl.write.WritableCellFormat;    
  22. import jxl.write.WritableFont;    
  23. import jxl.write.WritableSheet;    
  24. import jxl.write.WritableWorkbook;    
  25. import jxl.write.WriteException;    
  26.    
  27. public class JExcelUtils {    
  28.    
  29.     /**   
  30.      * 生成Excel文件   
  31.      * @param path         文件路径   
  32.      * @param sheetName    工作表名称   
  33.      * @param dataTitles   数据标题   
  34.      */   
  35.    public void createExcelFile(String path,String sheetName,String[] dataTitles){    
  36.        WritableWorkbook workbook;    
  37.        try{    
  38.            OutputStream os=new FileOutputStream(path);     
  39.            workbook=Workbook.createWorkbook(os);     
  40.    
  41.            WritableSheet sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表    
  42.            initialSheetSetting(sheet);    
  43.                
  44.            Label label;    
  45.            for (int i=0; i<dataTitles.length; i++){    
  46.                //Label(列号,行号,内容,风格)    
  47.                label = new Label(i, 0, dataTitles[i],getTitleCellFormat());     
  48.                sheet.addCell(label);     
  49.            }    
  50.    
  51.            //插入一行    
  52.            insertRowData(sheet,1,new String[]{"200201001","张三","100","60","100","260"},getDataCellFormat(CellType.STRING_FORMULA));    
  53.                
  54.            //一个一个插入行    
  55.            label = new Label(0, 2,"200201002",getDataCellFormat(CellType.STRING_FORMULA));     
  56.            sheet.addCell(label);    
  57.                
  58.            label = new Label(1, 2,"李四",getDataCellFormat(CellType.STRING_FORMULA));     
  59.            sheet.addCell(label);    
  60.                
  61.            insertOneCellData(sheet,2,2,70.5,getDataCellFormat(CellType.NUMBER));    
  62.            insertOneCellData(sheet,3,2,90.523,getDataCellFormat(CellType.NUMBER));    
  63.            insertOneCellData(sheet,4,2,60.5,getDataCellFormat(CellType.NUMBER));    
  64.    
  65.            insertFormula(sheet,5,2,"C3+D3+E3",getDataCellFormat(CellType.NUMBER_FORMULA));    
  66.                
  67.            //插入日期    
  68.            mergeCellsAndInsertData(sheet, 0, 3, 5, 3, new Date(), getDataCellFormat(CellType.DATE));    
  69.                
  70.            workbook.write();     
  71.            workbook.close();    
  72.        }catch(Exception e){    
  73.            e.printStackTrace();    
  74.        }    
  75.    }    
  76.        
  77.    /**   
  78.     * 初始化表格属性   
  79.     * @param sheet   
  80.     */   
  81.    public void initialSheetSetting(WritableSheet sheet){    
  82.       try{    
  83.            //sheet.getSettings().setProtected(true); //设置xls的保护,单元格为只读的    
  84.            sheet.getSettings().setDefaultColumnWidth(10); //设置列的默认宽度    
  85.            //sheet.setRowView(2,false);//行高自动扩展     
  86.            //setRowView(int row, int height);--行高     
  87.            //setColumnView(int  col,int width); --列宽    
  88.            sheet.setColumnView(0,20);//设置第一列宽度    
  89.       }catch(Exception e){    
  90.           e.printStackTrace();    
  91.       }    
  92.    }    
  93.        
  94.    /**   
  95.     * 插入公式   
  96.     * @param sheet   
  97.     * @param col   
  98.     * @param row   
  99.     * @param formula   
  100.     * @param format   
  101.     */   
  102.    public void insertFormula(WritableSheet sheet,Integer col,Integer row,String formula,WritableCellFormat format){    
  103.        try{    
  104.            Formula f = new Formula(col, row, formula, format);    
  105.            sheet.addCell(f);    
  106.        }catch(Exception e){    
  107.            e.printStackTrace();    
  108.        }    
  109.    }    
  110.        
  111.    /**   
  112.     * 插入一行数据   
  113.     * @param sheet       工作表   
  114.     * @param row         行号   
  115.     * @param content     内容   
  116.     * @param format      风格   
  117.     */   
  118.    public void insertRowData(WritableSheet sheet,Integer row,String[] dataArr,WritableCellFormat format){    
  119.        try{    
  120.            Label label;    
  121.            for(int i=0;i<dataArr.length;i++){    
  122.                label = new Label(i,row,dataArr[i],format);    
  123.                sheet.addCell(label);    
  124.            }    
  125.        }catch(Exception e){    
  126.            e.printStackTrace();    
  127.        }    
  128.    }    
  129.        
  130.    /**   
  131.     * 插入单元格数据   
  132.     * @param sheet   
  133.     * @param col   
  134.     * @param row   
  135.     * @param data   
  136.     */   
  137.    public void insertOneCellData(WritableSheet sheet,Integer col,Integer row,Object data,WritableCellFormat format){    
  138.        try{    
  139.            if(data instanceof Double){    
  140.                jxl.write.Number  labelNF = new jxl.write.Number(col,row,(Double)data,format);     
  141.                sheet.addCell(labelNF);     
  142.            }else if(data instanceof Boolean){    
  143.                jxl.write.Boolean labelB = new jxl.write.Boolean(col,row,(Boolean)data,format);     
  144.                sheet.addCell(labelB);     
  145.            }else if(data instanceof Date){    
  146.                jxl.write.DateTime labelDT = new jxl.write.DateTime(col,row,(Date)data,format);     
  147.                sheet.addCell(labelDT);     
  148.                setCellComments(labelDT, "这是个创建表的日期说明!");    
  149.            }else{    
  150.                Label label = new Label(col,row,data.toString(),format);    
  151.                sheet.addCell(label);                   
  152.            }    
  153.        }catch(Exception e){    
  154.            e.printStackTrace();    
  155.        }    
  156.    
  157.   }    
  158.        
  159.    /**   
  160.     * 合并单元格,并插入数据   
  161.     * @param sheet   
  162.     * @param col_start   
  163.     * @param row_start   
  164.     * @param col_end   
  165.     * @param row_end   
  166.     * @param data   
  167.     * @param format   
  168.     */   
  169.    public void mergeCellsAndInsertData(WritableSheet sheet,Integer col_start,Integer row_start,Integer col_end,Integer row_end,Object data, WritableCellFormat format){    
  170.       try{    
  171.           sheet.mergeCells(col_start,row_start,col_end,row_end);// 左上角到右下角    
  172.           insertOneCellData(sheet, col_start, row_start, data, format);    
  173.       }catch(Exception e){    
  174.           e.printStackTrace();    
  175.       }    
  176.    
  177.    }    
  178.        
  179.    /**   
  180.     * 给单元格加注释   
  181.     * @param label   
  182.     * @param comments   
  183.     */   
  184.    public void setCellComments(Object label,String comments){    
  185.        WritableCellFeatures cellFeatures = new WritableCellFeatures();    
  186.        cellFeatures.setComment(comments);    
  187.        if(label instanceof jxl.write.Number){    
  188.            jxl.write.Number num = (jxl.write.Number)label;    
  189.            num.setCellFeatures(cellFeatures);    
  190.        }else if(label instanceof jxl.write.Boolean){    
  191.            jxl.write.Boolean bool = (jxl.write.Boolean)label;    
  192.            bool.setCellFeatures(cellFeatures);    
  193.        }else if(label instanceof jxl.write.DateTime){    
  194.            jxl.write.DateTime dt = (jxl.write.DateTime)label;    
  195.            dt.setCellFeatures(cellFeatures);    
  196.        }else{    
  197.            Label _label = (Label)label;    
  198.            _label.setCellFeatures(cellFeatures);    
  199.        }    
  200.    }    
  201.        
  202.    /**   
  203.    * 读取excel   
  204.    * @param inputFile   
  205.    * @param inputFileSheetIndex   
  206.    * @throws Exception   
  207.    */   
  208.    public ArrayList<String> readDataFromExcel(File inputFile, int inputFileSheetIndex){    
  209.       ArrayList<String> list = new ArrayList<String>();    
  210.       Workbook book = null;    
  211.       Cell cell = null;    
  212.       WorkbookSettings setting = new WorkbookSettings();     
  213.       java.util.Locale locale = new java.util.Locale("zh","CN");     
  214.       setting.setLocale(locale);    
  215.       setting.setEncoding("ISO-8859-1");    
  216.       try{    
  217.           book = Workbook.getWorkbook(inputFile, setting);    
  218.       }catch(Exception e){    
  219.           e.printStackTrace();      
  220.       }    
  221.    
  222.       Sheet sheet = book.getSheet(inputFileSheetIndex);    
  223.       for (int rowIndex = 0; rowIndex < sheet.getRows(); rowIndex++) {//行    
  224.        for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {//列    
  225.            cell = sheet.getCell(colIndex, rowIndex);    
  226.            //System.out.println(cell.getContents());    
  227.            list.add(cell.getContents());    
  228.        }    
  229.       }    
  230.       book.close();    
  231.    
  232.       return list;    
  233.    }    
  234.    
  235.    /**   
  236.     * 得到数据表头格式   
  237.     * @return   
  238.     */   
  239.    public WritableCellFormat getTitleCellFormat(){    
  240.        WritableCellFormat wcf = null;    
  241.        try {    
  242.            //字体样式    
  243.            WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic    
  244.            wf.setColour(Colour.RED);    
  245.            wcf = new WritableCellFormat(wf);    
  246.            //对齐方式    
  247.            wcf.setAlignment(Alignment.CENTRE);    
  248.            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);    
  249.            //边框    
  250.            wcf.setBorder(Border.ALL,BorderLineStyle.THIN);    
  251.                
  252.            //背景色    
  253.            wcf.setBackground(Colour.GREY_25_PERCENT);    
  254.        } catch (WriteException e) {    
  255.         e.printStackTrace();    
  256.        }    
  257.        return wcf;    
  258.    }    
  259.        
  260.    /**   
  261.     * 得到数据格式   
  262.     * @return   
  263.     */   
  264.    public WritableCellFormat getDataCellFormat(CellType type){    
  265.        WritableCellFormat wcf = null;    
  266.        try {    
  267.            //字体样式    
  268.            if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字    
  269.               NumberFormat nf = new NumberFormat("#.00");    
  270.               wcf = new WritableCellFormat(nf);     
  271.            }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期    
  272.                jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");     
  273.                wcf = new jxl.write.WritableCellFormat(df);     
  274.            }else{    
  275.                WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic    
  276.                wcf = new WritableCellFormat(wf);    
  277.            }    
  278.            //对齐方式    
  279.            wcf.setAlignment(Alignment.CENTRE);    
  280.            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);    
  281.            //边框    
  282.            wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);    
  283.            wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);    
  284.            wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);    
  285.            //背景色    
  286.            wcf.setBackground(Colour.WHITE);    
  287.                
  288.            wcf.setWrap(true);//自动换行    
  289.                
  290.        } catch (WriteException e) {    
  291.         e.printStackTrace();    
  292.        }    
  293.        return wcf;    
  294.    }    
  295.        
  296.    /**   
  297.     * 打开文件看看   
  298.     * @param exePath   
  299.     * @param filePath   
  300.     */   
  301.    public void openExcel(String exePath,String filePath){    
  302.        Runtime r=Runtime.getRuntime();     
  303.        String cmd[]={exePath,filePath};     
  304.        try{     
  305.            r.exec(cmd);     
  306.        }catch(Exception e){    
  307.            e.printStackTrace();    
  308.        }    
  309.    }    
  310.        
  311.    public static void main(String[] args){    
  312.        String[] titles = {"学号","姓名","语文","数学","英语","总分"};     
  313.        JExcelUtils jxl = new JExcelUtils();    
  314.        String filePath = "E:/test.xls";    
  315.        jxl.createExcelFile(filePath," 成绩单",titles);    
  316.        jxl.readDataFromExcel(new File(filePath),0);    
  317.        jxl.openExcel("C:/Program Files/Microsoft Office/OFFICE11/EXCEL.EXE",filePath);    
  318.    }    
  319. }   
[html]
  1. import java.io.File;  
  2. import java.util.ArrayList;  
  3. import java.util.HashMap;  
  4. import java.util.List;  
  5. import java.util.Map;  
  6. import java.util.regex.Matcher;  
  7. import java.util.regex.Pattern;  
  8.   
  9. import jxl.Cell;  
  10. import jxl.CellView;  
  11. import jxl.Sheet;  
  12. import jxl.SheetSettings;  
  13. import jxl.Workbook;  
  14. import jxl.format.Alignment;  
  15. import jxl.write.Label;  
  16. import jxl.write.WritableFont;  
  17. import jxl.write.WritableSheet;  
  18. import jxl.write.WritableWorkbook;  
  19.   
  20. /**  
  21.  * jxl操作excel的工具类.  
  22.  *  
  23.  */  
  24. public class JxlTool {  
  25.     public static int count = 1;  
  26.     //存储带有级别信息的内容到位置的映射关系.  
  27.     private static Map levelToLocation = new HashMap();  
  28.       
  29.     public static void readExcel(String fileName) {  
  30.         Workbook wb = null;  
  31.         try {  
  32.             wb = Workbook.getWorkbook(new File(fileName));  
  33.             Sheet[] sheets = wb.getSheets();  
  34.             for(int i=0;i<sheets.length;i++){  
  35.                 Sheet ii = sheets[i];  
  36.                 System.out.println("第"+i+"个sheet的名字是"+ii.getName());  
  37.             }  
  38.         } catch (Exception e) {  
  39.             System.out.println("出现异常" + e);  
  40.             e.printStackTrace();  
  41.         } finally {  
  42.             wb.close();  
  43.         }  
  44.     }  
  45.       
  46.     private static String allChar = "abcdefghijklmnopqrstuvwxyz";  
  47.     /**  
  48.      * 从字符中得到列数.例如K-->10,A-->0,AA-->27  
  49.      * @return  
  50.      */  
  51.     public static int getNumFromExcelStr(String code)  
  52.  {  
  53.   int result = 0;  
  54.   code = code.toLowerCase();  
  55.   if(code.length()>1){  
  56.    char[] c = code.toCharArray();  
  57.    int len = c.length;  
  58.    for(int i=0;i<len;i++){  
  59.     if(i<len-1){  
  60.      result+=(allChar.indexOf(c[i])+1)*26;   
  61.     }else{  
  62.      result+=allChar.indexOf(c[i])+1;  
  63.     }  
  64.    }  
  65.    result-=1;  
  66.   }  
  67.   else  
  68.    return allChar.indexOf(code);  
  69.   return result;  
  70.  }   
  71.     /**  
  72.      * 根据行号和列号得到所在的单元格.例如(3,4)-->"E4"  
  73.      * @param vNum 纵坐标  
  74.      * @param hNum 横坐标  
  75.      * @return  
  76.      */  
  77.     public static String getCellInfo(int hNum,int vNum){  
  78.         char[] cs = allChar.toCharArray();  
  79.         String hStr = "";  
  80.         if(vNum>25){  
  81.             hStr = String.valueOf(cs[vNum/26-1])+String.valueOf(cs[vNum%26-1]);  
  82.         }else{  
  83.             hStr = String.valueOf(cs[vNum]);  
  84.         }  
  85.         return (hStr+Integer.toString((hNum+1))).toUpperCase();  
  86.     }  
  87.   
  88.     /**  
  89.      * 得到一个字符串里面的字符.A12-->A  
  90.      * @param oldStr  
  91.      * @return  
  92.      */  
  93.     public static String getCodeFromStr(String oldStr){  
  94.         return oldStr.replaceAll("\\d", "");  
  95.     }  
  96.       
  97.     /**  
  98.      * 得到一个字符串里面的字符.A12-->12  
  99.      * @param oldStr  
  100.      * @return  
  101.      */  
  102.     public static int getNumFromStr(String oldStr){  
  103.         return Integer.parseInt(oldStr.replaceAll("[a-zA-Z]", ""))-1;  
  104.     }  
  105.       
  106.     /**  
  107.      * 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.  
  108.      * @param fileName  
  109.      * @param sheetIndex  
  110.      * @param startRow  
  111.      * @param endRow  
  112.      * @param startColumn  
  113.      * @param endColumn  
  114.      */  
  115.     public static List readExcel(String fileName, int sheetIndex, int startRow,  
  116.             int endRow, int startColumn, int endColumn) {  
  117.         Workbook wb = null;  
  118.         List allData = new ArrayList();  
  119.         Cell cell = null;  
  120.         try {  
  121.             wb = Workbook.getWorkbook(new File(fileName));  
  122.             Sheet sheet = wb.getSheet(sheetIndex);  
  123.             int rowCount = sheet.getRows();  
  124.             int columnCount = sheet.getColumns();  
  125.             for (int r = startRow; r < rowCount && r <= endRow; r++) {// 行  
  126.                 for (int c = startColumn; c < columnCount && c <= endColumn; c++) {// 列  
  127.                     cell = sheet.getCell(c, r);  
  128.                     // System.out.println(cell.getContents());  
  129.                     allData.add(cell.getContents());  
  130.                 }  
  131.             }  
  132.         } catch (Exception e) {  
  133.             System.out.println("出现异常" + e);  
  134.             e.printStackTrace();  
  135.         } finally {  
  136.             wb.close();  
  137.         }  
  138.         return allData;  
  139.     }  
  140.       
  141.     /**  
  142.      * 读取指定excel中的指定sheet的某一块的数据....用于模板里面读取单元格.  
  143.      * @param fileName  
  144.      * @param sheetIndex  
  145.      * @param startCell  
  146.      * @param endCell  
  147.      * @return  
  148.      */  
  149.     public static List readExcel(String fileName, int sheetIndex,String startCell, String endCell) {  
  150.         int startRow = getNumFromStr(startCell);  
  151.         int endRow = getNumFromStr(endCell);  
  152.         int startColumn=getNumFromExcelStr(getCodeFromStr(startCell));  
  153.         int endColumn = getNumFromExcelStr(getCodeFromStr(endCell));  
  154.         return readExcel(fileName, sheetIndex, startRow, endRow, startColumn,  
  155.                 endColumn);  
  156.     }  
  157.           
  158.     /**  
  159.      * 设置excel中的sheet页全部隐藏  
  160.      * @param fileName  
  161.      */  
  162.     public static void setAllHiddenSheet(String fileName) {  
  163.         Workbook wb = null;  
  164.         try {  
  165.             wb = Workbook.getWorkbook(new File(fileName));  
  166.             // 打开一个文件副本,并指定数据写回原文件.  
  167.             WritableWorkbook book = Workbook.createWorkbook(new File(fileName),  
  168.                     wb);  
  169.             Sheet[] sheets = book.getSheets();  
  170.             for(int i=3;i<sheets.length;i++){  
  171.                 Sheet ii = sheets[i];  
  172.                 ii.getSettings().setHidden(true);  
  173.             }  
  174.             book.write();  
  175.             book.close();  
  176.         } catch (Exception e) {  
  177.             System.out.println("出现异常" + e);  
  178.             e.printStackTrace();  
  179.         } finally {  
  180.             wb.close();  
  181.             System.out.print(111);  
  182.         }  
  183.     }   
  184.   
  185.        /**  
  186.           * 从行号和列号,得到所在的位置字符串,例如:row=7col=7--->i8  
  187.           */  
  188.         public  static String getcodefromRC(int row,int col){  
  189.         char[] cc = allChar.toCharArray();  
  190.         return String.valueOf(cc[col])+(++row);  
  191.     }  
  192.       
  193.     /**  
  194.      * 添加一个新的sheet到指定excel文件  
  195.      * @param fileName  
  196.      * @param sheetName sheet的name  
  197.      */  
  198.     public static void addNewSheet(String fileName,String sheetName) {  
  199.         Workbook wb = null;  
  200.         try {  
  201.             wb = Workbook.getWorkbook(new File(fileName));  
  202.             // 打开一个文件副本,并指定数据写回原文件.  
  203.             WritableWorkbook book = Workbook.createWorkbook(new File(fileName),  
  204.                     wb);  
  205.             // 创建一个新的sheet到第2页的位置             
  206.             String[] sheetNames = wb.getSheetNames();  
  207.             for(int i=0;i<sheetNames.length;i++){  
  208.                 if(sheetNames[i].equals(sheetName)){  
  209.                     System.out.println("已经存在了,不用添加了." );  
  210.                     return ;  
  211.                 }  
  212.             }  
  213.             WritableSheet sheet = book.createSheet(sheetName, 1);  
  214.             sheet.addCell(new Label(0, 0, "新加的测试数据"));  
  215.             book.write();  
  216.             book.close();  
  217.         } catch (Exception e) {  
  218.             System.out.println("出现异常" + e);  
  219.             e.printStackTrace();  
  220.         } finally {  
  221.             wb.close();  
  222.         }  
  223.     }   
  224.   
  225.         /**  
  226.           * 得到单元格的double内容,不可以直接使用cell.getContents(),因为这个方法是直接打印单元格内容  
  227. ,单元格内容可能隐藏了后面的小数点!!          
  228.           */  
  229.          public static double getNumber(Cell cell){  
  230.              NumberCell numberCell = (NumberCell)cell;   
  231.             double namberValue = numberCell.getValue();   
  232.              return   namberValue ;           
  233.          }  
  234.   
  235.          /**  
  236.      * 如果是公式返回公式的内容,否则返回单元格字符串表面内容  
  237.      * @param c  
  238.      * @return  
  239.      */  
  240.          public static String getForJmulaStr(Cell c) {  
  241.         String ans = "";  
  242.         try {  
  243.             System.out.println(c.getType());  
  244.             if (c.getType() == CellType.NUMBER_FORMULA  
  245.                     || c.getType() == CellType.STRING_FORMULA  
  246.                     || c.getType() == CellType.BOOLEAN_FORMULA  
  247.                     || c.getType() == CellType.DATE_FORMULA  
  248.                     || c.getType() == CellType.FORMULA_ERROR) {  
  249.                 FormulaCell nfc = (FormulaCell) c;  
  250.                 ans = nfc.getFormula();  
  251.                   
  252.             } else {  
  253.                 ans = c.getContents();  
  254.             }  
  255.         } catch (FormulaException e) {  
  256.             return "出现异常" + e.getMessage();  
  257.         }   
  258.         return ans;  
  259.     }  
  260.   
  261.       //得到指定位置单元格的值(普通单元格,数字单元格,日期单元格)  
  262.        private String getValue(Sheet sheet,int row,int col){  
  263.         Cell cell=sheet.getCell(col, row);     
  264.         CellType cellType=cell.getType();  
  265.         NumberCell numberCell = null;  
  266.         String cellValue = "";  
  267.         //得到单元格的值  
  268.         if (cellType == CellType.NUMBER) {  
  269.           numberCell = (NumberCell) cell;  
  270.           cellValue = String.valueOf(numberCell.getValue());  
  271.        } else if (cellType == CellType.DATE) {  
  272.                 cellValue = df.format(((DateCell) cell).getDate());  
  273.      } else if (cellType == CellType.NUMBER_FORMULA) {   
  274.          // 形如:=123.232+3423.12  
  275.          // 或者 =B2+123.12  
  276.          NumberFormulaCell numberFormulaCell = (NumberFormulaCell) cell;  
  277.          cellValue = String.valueOf(numberFormulaCell.getValue());  
  278.      } else {  
  279.          cellValue = cell.getContents();  
  280.      }         
  281.        cellValue=cellValue.replace(" ", "");    
  282.         return cellValue;  
  283.     }  
  284. }  


本文固定链接: http://www.devba.com/index.php/archives/5101.html | 开发吧

报歉!评论已关闭.