用户注册



邮箱：

密码：

用户登录


邮箱：

密码：
记住登录一个月忘记密码？

发表随想


还能输入：200字
云代码 - java代码库

excel导出

2013-08-26 作者： 云代码会员举报

[java]代码库

view sourceprint?
  //定义列宽
   sheet.setColumnView(0, 13);
 //定义行高
   sheet.setRowView(0, 380);
 
            创建日期   
            Calendar c = Calendar.getInstance();
            Date date = c.getTime();
            WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);
            DateTime dt = new DateTime(0, 10, date, cf1);
            sheet.addCell(dt);
 
一.读取Excel文件内容
 
    java 代码
 
           
        public static String readExcel(File file){  
            StringBuffer sb = new StringBuffer();  
                
            Workbook wb = null;  
            try {  
                //构造Workbook（工作薄）对象  
                wb=Workbook.getWorkbook(file);  
            } catch (BiffException e) {  
                e.printStackTrace();  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
                
            if(wb==null)  
                return null;  
                
            //获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了  
            Sheet[] sheet = wb.getSheets();  
                
            if(sheet!=null&&sheet.length>0){  
                //对每个工作表进行循环  
                for(int i=0;i
                    //得到当前工作表的行数  
                    int rowNum = sheet[i].getRows();  
                    for(int j=0;j
                        //得到当前行的所有单元格  
                        Cell[] cells = sheet[i].getRow(j);  
                        if(cells!=null&&cells.length>0){  
                            //对每个单元格进行循环  
                            for(int k=0;k
                                //读取当前单元格的值  
                                String cellValue = cells[k].getContents();  
                                sb.append(cellValue+"\t");  
                            }  
                        }  
                        sb.append("\r\n");  
                    }  
                    sb.append("\r\n");  
                }  
            }  
            //最后关闭资源，释放内存  
            wb.close();  
            return sb.toString();  
        } 
 
二.写入Excel文件
 
    这里有很多格式了，比如文本内容加粗，加上某些颜色等，可以参考jxl的api，同时还推荐一篇不错的文章：http://www.ibm.com/developerworks/cn/java/l-javaExcel/?ca=j-t10
    java 代码
 
           
            public static void writeExcel(String fileName){  
                WritableWorkbook wwb = null;  
                try {  
                    //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象  
                    wwb = Workbook.createWorkbook(new File(fileName));  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }  
                if(wwb!=null){  
                    //创建一个可写入的工作表  
                    //Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置  
                    WritableSheet ws = wwb.createSheet("sheet1", 0);  
                        
                    //下面开始添加单元格  
                    for(int i=0;i<10;i++){  
                        for(int j=0;j<5;j++){  
                            //这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行  
                            Label labelC = new Label(j, i, "这是第"+(i+1)+"行，第"+(j+1)+"列");  
                            try {  
                                //将生成的单元格添加到工作表中  
                                ws.addCell(labelC);  
                            } catch (RowsExceededException e) {  
                                e.printStackTrace();  
                            } catch (WriteException e) {  
                                e.printStackTrace();  
                            }  
           
                        }  
                    }  
           
                    try {  
                        //从内存中写入文件中  
                        wwb.write();  
                        //关闭资源，释放内存  
                        wwb.close();  
                    } catch (IOException e) {  
                        e.printStackTrace();  
                    } catch (WriteException e) {  
                        e.printStackTrace();  
                    }  
                }  
            }  
 
三.在一个Excel文件中查找是否包含某一个关键字
 
    java 代码
 
           
            public static boolean searchKeyWord(File file,String keyWord){  
                boolean res = false;  
                    
                Workbook wb = null;  
                try {  
                    //构造Workbook（工作薄）对象  
                    wb=Workbook.getWorkbook(file);  
                } catch (BiffException e) {  
                    return res;  
                } catch (IOException e) {  
                    return res;  
                }  
                    
                if(wb==null)  
                    return res;  
                    
                //获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了  
                Sheet[] sheet = wb.getSheets();  
                    
                boolean breakSheet = false;  
                    
                if(sheet!=null&&sheet.length>0){  
                    //对每个工作表进行循环  
                    for(int i=0;i
                        if(breakSheet)  
                            break;  
                            
                        //得到当前工作表的行数  
                        int rowNum = sheet[i].getRows();  
                            
                        boolean breakRow = false;  
                            
                        for(int j=0;j
                            if(breakRow)  
                                break;  
                            //得到当前行的所有单元格  
                            Cell[] cells = sheet[i].getRow(j);  
                            if(cells!=null&&cells.length>0){  
                                boolean breakCell = false;  
                                //对每个单元格进行循环  
                                for(int k=0;k
                                    if(breakCell)  
                                        break;  
                                    //读取当前单元格的值  
                                    String cellValue = cells[k].getContents();  
                                    if(cellValue==null)  
                                        continue;  
                                    if(cellValue.contains(keyWord)){  
                                        res = true;  
                                        breakCell = true;  
                                        breakRow = true;  
                                        breakSheet = true;  
                                    }  
                                }  
                            }  
                        }  
                    }  
                }  
                //最后关闭资源，释放内存  
                wb.close();  
                    
                return res;  
            } 
 
四.往Excel中插入图片图标
 
    插入图片的实现很容易，参看以下代码：
    java 代码
 
           
        public static void insertImg(WritableSheet dataSheet, int col, int row, int width,  
                int height, File imgFile){  
            WritableImage img = new WritableImage(col, row, width, height, imgFile);  
            dataSheet.addImage(img);  
        }  
 
    以上代码的注释已经很清楚了，大概也就不用再解释了，我们可以用如下程序验证：
    java 代码
 
            try {  
                //创建一个工作薄  
        WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));  
        //待插入的工作表  
        WritableSheet imgSheet = workbook.createSheet("Images",0);  
        //要插入的图片文件  
        File imgFile = new File("D:/1.png");  
        //图片插入到第二行第一个单元格，长宽各占六个单元格  
        insertImg(imgSheet,0,1,6,6,imgFile);  
        workbook.write();  
        workbook.close();  
         catch (IOException e) {  
        e.printStackTrace();  
         catch (WriteException e) {  
        e.printStackTrace();  
 
     但是jxl只支持png格式的图片，jpg格式和gif格式都不支持
 
五.插入页眉页脚
 
    一般的页眉页脚都分为三个部分,左,中,右三部分,利用如下代码可实现插入页眉页脚
    java 代码
 
           
        public static void setHeader(WritableSheet dataSheet,String left,String center,String right){  
            HeaderFooter hf = new HeaderFooter();  
            hf.getLeft().append(left);  
            hf.getCentre().append(center);  
            hf.getRight().append(right);  
            //加入页眉  
            dataSheet.getSettings().setHeader(hf);  
            //加入页脚  
            //dataSheet.getSettings().setFooter(hf);  
        } 
 
    我们可以用如下代码测试该方法:
    java 代码
 
            try {  
                //创建一个工作薄  
            WritableWorkbook workbook = Workbook.createWorkbook(new File("D:/test1.xls"));  
            //待插入的工作表  
            WritableSheet dataSheet = workbook.createSheet("加入页眉",0);  
            ExcelUtils.setHeader(dataSheet, "chb", "2007-03-06", "第1页,共3页");  
            workbook.write();  
            workbook.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } catch (WriteException e) {  
            e.printStackTrace();  
        }  
          }  
 
      
 
    合并单元格：
 
    //首先创建一个sheet
 
    WritableSheet sheetWrite=writeWorkbook.createSheet("sheet的名称",0);
 
    //把 单元格（column, row）到单元格（column1, row1）进行合并。
 
    sheetWrite.mergeCells(column, row, column1, row1);
 
      
 
      
 
      
 
      
    java 操作excel，jxl加边框，jxl合并单元格,单元格的设置，单元
 
    WritableCellFormat headerFormat = new WritableCellFormat(
                        HEADER_FONT_STYLE);
                //水平居中对齐
                headerFormat.setAlignment(Alignment.CENTRE);
                //竖直方向居中对齐
                headerFormat.setVerticalAlignment
 
    (VerticalAlignment.CENTRE);
                
                //建立标签，参数依次为：列索引、行索引、内容、格式
                Label seqLabel = new Label(0, 0, "序号", headerFormat);
                //增加单元格
                sheet.addCell(seqLabel);
                //合并单元格，参数依次为：列索引、行索引、列索引+需要合并
 
    的列的个数、行索引+需要合并的行的个数
                sheet.mergeCells(0, 0, 0, 1);
                //设置单元格宽度，以字符为单位
                sheet.setColumnView(0, "序号".length() + 10);
 
                Label basicInfoLabel = new Label(1, 0, "基本信息",
 
    headerFormat);
                sheet.addCell(basicInfoLabel);
                sheet.mergeCells(1, 0, 3, 0);//合并垮了3行。
 
             sheet.mergeCells(1, 1, 1, 3);//合并垮了3列。
 
      
 
             label = new Label(0, 1, "苹果”);
             sheet.mergeCells(0,1, 0, 3- 1);//合并垮了3列。
             WritableCellFormat cellFormat = new WritableCellFormat();
             cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
 
    // 垂直居中
             label.setCellFormat(cellFormat);
             sheet.addCell(label);
 
             注：再次使cellFormat时，需要重新NEW WritableCellFormat 。
 
      
 
            
 
 
      
 
      
 
      
 
     


网友评论    (发表评论)


发表评论：

评论须知：

  • 1、评论每次加2分，每天上限为30；
  • 2、请文明用语，共同创建干净的技术交流环境；
  • 3、若被发现提交非法信息，评论将会被删除，并且给予扣分处理，严重者给予封号处理；
  • 4、请勿发布广告信息或其他无关评论，否则将会删除评论并扣分，严重者给予封号处理。


扫码下载

加载中，请稍后...

输入口令后可复制整站源码

加载中，请稍后...