• jxl导入导出实例


      1 package com.tgb.test;
      2 
      3 import java.io.File;
      4 import java.io.IOException;
      5 import java.util.ArrayList;
      6 import java.util.List;
      7 
      8 import jxl.Cell;
      9 import jxl.Sheet;
     10 import jxl.Workbook;
     11 import jxl.write.Label;
     12 import jxl.write.Number;
     13 import jxl.write.WritableImage;
     14 import jxl.write.WritableSheet;
     15 import jxl.write.WritableWorkbook;
     16 import jxl.write.WriteException;
     17 
     18 import org.apache.commons.lang3.math.NumberUtils;
     19 import org.junit.Test;
     20 //import org.junit.Test;
     21 public class JxlDemo {
     22      /**
     23      * 导入(导入到内存)
     24      */
     25     @Test
     26     public void importExcel() {
     27         Workbook book = null;
     28         try {
     29             book = Workbook.getWorkbook(new File("D:/test/test.xls"));
     30             // 获得第一个工作表对象
     31             Sheet sheet = book.getSheet(0);
     32             int rows=sheet.getRows();
     33             int columns=sheet.getColumns();
     34             // 遍历每行每列的单元格
     35             for(int i=0;i<rows;i++){
     36                 for(int j=0;j<columns;j++){
     37                     Cell cell = sheet.getCell(j, i);
     38                     String result = cell.getContents();
     39                     if(j==0){
     40                         System.out.print("姓名:"+result+" ");
     41                     }
     42                     if(j==1){
     43                         System.out.print("年龄:"+result+" ");
     44                     }
     45                     if((j+1)%2==0){ 
     46                         System.out.println();
     47                     }
     48                 }
     49             }
     50             System.out.println("========");
     51             // 得到第一列第一行的单元格
     52             Cell cell1 = sheet.getCell(0, 0);
     53             String result = cell1.getContents();
     54             System.out.println(result);
     55             System.out.println("========");
     56         } catch (Exception e) {
     57             System.out.println(e);
     58         }finally{
     59             if(book!=null){
     60                 book.close();
     61             }
     62         }
     63     }
     64 
     65     /**
     66      * 导出(导出到磁盘)
     67      */
     68     @Test
     69     public void exportExcel() {
     70         WritableWorkbook book = null;
     71         try {
     72             // 打开文件
     73             book = Workbook.createWorkbook(new File("D:/test/test.xls"));
     74             // 生成名为"学生"的工作表,参数0表示这是第一页
     75             WritableSheet sheet = book.createSheet("学生", 0);
     76             // 指定单元格位置是第一列第一行(0, 0)以及单元格内容为张三
     77             Label label = new Label(0, 0, "张三");
     78             // 将定义好的单元格添加到工作表中
     79             sheet.addCell(label);
     80             // 保存数字的单元格必须使用Number的完整包路径
     81             jxl.write.Number number = new jxl.write.Number(1, 0, 30);
     82             sheet.addCell(number);
     83             // 写入数据并关闭文件
     84             book.write();
     85         } catch (Exception e) {
     86             System.out.println(e);
     87         }finally{
     88             if(book!=null){
     89                 try {
     90                     book.close();
     91                 } catch (Exception e) {
     92                     e.printStackTrace();
     93                 } 
     94             }
     95         }
     96     }
     97     
     98     /**
     99      * 对象数据写入到Excel
    100      */
    101     @Test
    102     public void writeExcel() {
    103         WritableWorkbook book = null;
    104         try {
    105             // 打开文件
    106             book = Workbook.createWorkbook(new File("D:/test/stu.xls"));
    107             // 生成名为"学生"的工作表,参数0表示这是第一页
    108             WritableSheet sheet = book.createSheet("学生", 0);
    109             
    110             List<Student> stuList=queryStudentList();
    111             if(stuList!=null && !stuList.isEmpty()){
    112                 for(int i=0; i<stuList.size(); i++){
    113                     sheet.addCell(new Label(0, i, stuList.get(i).getName()));
    114                     sheet.addCell(new Number(1, i, stuList.get(i).getAge()));
    115                 }
    116             }
    117             
    118             // 写入数据并关闭文件
    119             book.write();
    120         } catch (Exception e) {
    121             System.out.println(e);
    122         }finally{
    123             if(book!=null){
    124                 try {
    125                     book.close();
    126                 } catch (Exception e) {
    127                     e.printStackTrace();
    128                 } 
    129             }
    130         }
    131     
    132     }
    133     
    134     /**
    135      * 读取Excel数据到内存
    136      */
    137     @Test
    138     public void readExcel() {
    139         Workbook book = null;
    140         try {
    141             // 打开文件
    142             book = Workbook.getWorkbook(new File("D:/test/stu.xls"));
    143             // 获得第一个工作表对象
    144             Sheet sheet = book.getSheet(0);
    145             int rows=sheet.getRows();
    146             int columns=sheet.getColumns();
    147             List<Student> stuList=new ArrayList<Student>();
    148             // 遍历每行每列的单元格
    149             for(int i=0;i<rows;i++){
    150                 Student stu = new Student();
    151                 for(int j=0;j<columns;j++){
    152                     Cell cell = sheet.getCell(j, i);
    153                     String result = cell.getContents();
    154                     if(j==0){
    155                         stu.setName(result);
    156                     }
    157                     if(j==1){
    158                         stu.setAge(NumberUtils.toInt(result));
    159                     }
    160                     if((j+1)%2==0){
    161                         stuList.add(stu);
    162                         stu=null;
    163                     }
    164                 }
    165             }
    166             
    167             //遍历数据
    168             for(Student stu : stuList){
    169                 System.out.println(String.format("姓名:%s, 年龄:%s", 
    170                         stu.getName(), stu.getAge()));
    171             }
    172             
    173         } catch (Exception e) {
    174             System.out.println(e);
    175         }finally{
    176             if(book!=null){
    177                 try {
    178                     book.close();
    179                 } catch (Exception e) {
    180                     e.printStackTrace();
    181                 } 
    182             }
    183         }
    184     
    185     }
    186     
    187     /**
    188      * 图片写入Excel,只支持png图片
    189      */
    190     @Test
    191     public void writeImg() {
    192         WritableWorkbook wwb = null;
    193         try {
    194             wwb = Workbook.createWorkbook(new File("D:/test/image.xls"));
    195             WritableSheet ws = wwb.createSheet("图片", 0);
    196             File file = new File("D:\test\png.png");
    197             //前两位是起始格,后两位是图片占多少个格,并非是位置
    198             WritableImage image = new WritableImage(1, 4, 6, 18, file);
    199             ws.addImage(image);
    200             wwb.write();
    201         } catch (Exception e) {
    202             e.printStackTrace();
    203         }finally{
    204             if(wwb!=null){
    205                 try {
    206                     wwb.close();
    207                 } catch (Exception e) {
    208                     e.printStackTrace();
    209                 }  
    210             }
    211         }
    212     }
    213     
    214     private List<Student> queryStudentList(){
    215         List<Student> stuList=new ArrayList<Student>();
    216         stuList.add(new Student("zhangsan", 20));
    217         stuList.add(new Student("lisi", 25));
    218         stuList.add(new Student("wangwu", 30));
    219         return stuList;
    220     }
    221     
    222     public class Student {
    223         private String name;
    224         private int age;
    225 
    226         public Student() {
    227         }
    228 
    229         public Student(String name, int age) {
    230             super();
    231             this.name = name;
    232             this.age = age;
    233         }
    234 
    235         public String getName() {
    236             return name;
    237         }
    238 
    239         public void setName(String name) {
    240             this.name = name;
    241         }
    242 
    243         public int getAge() {
    244             return age;
    245         }
    246 
    247         public void setAge(int age) {
    248             this.age = age;
    249         }
    250     }
    251 }

      以上的代码简单明了的示范了JXL的导入导出功能,具体的导入导出工具类都是在此基础上建立起来的。在最近的信用办的项目中出现了一个小问题,就是导出Excel的文件名如果是中文就会出现乱码,所以需要做一些简单的处理,如下:(实例借鉴:http://www.cnblogs.com/linjiqin/p/3540266.html

    response.setHeader("Content-disposition", "attachment; filename="+ new String( fileName.getBytes("gb2312"), "ISO8859-1" )+ ".xls");

      更加详细健壮的设置如下:

    fileName = new String(fileName.getBytes(),"iso-8859-1");
    response.setCharacterEncoding("gb2312");
    response.reset();
    response.setContentType("application/OCTET-STREAM;charset=gb2312");
    response.setHeader("pragma", "no-cache");
    response.addHeader("Content-Disposition", "attachment;filename=""
    + fileName + ".xls"");// 点击导出excle按钮时候页面显示的默认名称
    workbook = Workbook.createWorkbook(response.getOutputStream());

       了解这些基础知识,使用jxl导入导出Excel就轻而易举了。

  • 相关阅读:
    素数路径Prime Path POJ3126 素数,BFS
    Fliptile POJ3279 DFS
    Find the Multiple POJ1426
    洗牌Shuffle'm Up POJ3087 模拟
    棋盘问题 POJ1321 DFS
    抓住那只牛!Catch That Cow POJ3278 BFS
    Dungeon Master POJ2251 三维BFS
    Splitting into digits CodeForce#1104A
    Ubuntu下手动安装Nvidia显卡驱动
    最大连续子序列和
  • 原文地址:https://www.cnblogs.com/jyh317/p/4065906.html
Copyright © 2020-2023  润新知