JAVA读写Excel(JXL)

来源：互联网发布：岁月号沉船事件知乎编辑：程序博客网时间：2024/06/05 04:52

1.找到JXL.jar包，导入程序。

2.读取Excel

publicstatic void readExcel() throws BiffException, IOException{
   //创建一个list 用来存储读取的内容
    List list =new ArrayList();
    Workbook rwb= null;
    Cell cell =null;

   //创建输入流
    InputStreamstream = new FileInputStream("d:\\testJXL.xls");

   //获取Excel文件对象
    rwb =Workbook.getWorkbook(stream);

    //获取文件的指定工作表默认的第一个
    Sheet sheet= rwb.getSheet(0);

   //行数(表头的目录不需要，从1开始)
    for(int i=0;i

    //创建一个数组用来存储每一列的值
    String[] str = new String[sheet.getColumns()];

    //列数
    for(int j=0; j

     //获取第i行，第j列的值
     cell =sheet.getCell(j,i);
     str[j] = cell.getContents();

    }
    //把刚获取的列存入list
    list.add(str);
    }
    for(inti=0;i
    String[] str = (String[])list.get(i);
    for(int j=0;j
     System.out.println(str[j]);
    }
    }
  }

3.写入Excel

public static voidwriteExcel(){
   String[] title ={"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};
        try {
            //获得开始时间
            long start =System.currentTimeMillis();
            //输出的excel的路径
            String filePath ="d:\\testJXL.xls";
             //创建Excel工作薄
            WritableWorkbookwwb;
             //新建立一个jxl文件,即在d盘下生成testJXL.xls
            OutputStream os = newFileOutputStream(filePath);
            wwb=Workbook.createWorkbook(os);
            //添加第一个工作表并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet("产品清单",0);
            Labellabel;
            for(int i=0;i
                // Label(x,y,z) 代表单元格的第x+1列，第y+1行,内容z
                //在Label对象的子对象中指明单元格的位置和内容
                label = new Label(i,0,title[i]);
                label = new Label(i, 0, title[i], getHeader());
                //将定义好的单元格添加到工作表中
                sheet.addCell(label);
            }
            //下面是填充数据

           //填充产品编号
            jxl.write.Number number = newjxl.write.Number(0,1,20071001);
            sheet.addCell(number);
            //填充产品名称
             label = newLabel(1,1,"金鸽瓜子");
            sheet.addCell(label);

            jxl.write.NumberFormat nf = newjxl.write.NumberFormat("#,###.00");
            jxl.write.WritableCellFormat wcf = newjxl.write.WritableCellFormat(nf);
             //填充产品价格
            jxl.write.Number nb = newjxl.write.Number(2,1,200000.45,wcf);
            sheet.addCell(nb);
            //填充产品数量
             jxl.write.Number numb = newjxl.write.Number(3,1,200);
            sheet.addCell(numb);

           SimpleDateFormat sdf = newSimpleDateFormat("yyyy-MM-dd");
            String newdate = sdf.format(newDate());
            //填充出产日期
            label = newLabel(4,1,newdate);
            sheet.addCell(label);
            // 填充产地
             label = newLabel(5,1,"陕西西安");
            sheet.addCell(label);

            jxl.write.Boolean bool = newjxl.write.Boolean(6,1,true);
            sheet.addCell(bool);

            sheet.mergeCells(0,3,2,3);
            label = newLabel(0,3,"合并了三个单元格");
            sheet.addCell(label);

            CellFormat cf = wwb.getSheet(0).getCell(1,0).getCellFormat();
            WritableCellFormat wc = newWritableCellFormat();
            // 设置居中
             wc.setAlignment(Alignment.CENTRE);
             //设置边框线
           wc.setBorder(Border.ALL,BorderLineStyle.THIN);
            //设置单元格的背景颜色
           wc.setBackground(jxl.format.Colour.RED);
            label = newLabel(1,5,"字体",wc);
           sheet.addCell(label);

            // 设置字体
           jxl.write.WritableFont wfont = newjxl.write.WritableFont(WritableFont.createFont("隶书"),20);
            WritableCellFormat font = newWritableCellFormat(wfont);
            label = newLabel(2,6,"隶书",font);
             sheet.addCell(label);

          // 写入数据
           wwb.write();
            // 关闭文件
            wwb.close();
           long end =System.currentTimeMillis();
            System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);
        } catch (Exception e){
            System.out.println("---出现异常---");
             e.printStackTrace();
       }
}

4.Excel样式

publicstatic WritableCellFormat getHeader(){
   WritableFont font =new WritableFont(WritableFont.TIMES, 10,WritableFont.BOLD);//定义字体
   try {
   font.setColour(Colour.BLUE);//蓝色字体
   } catch (WriteException e1){
    // TODO 自动生成catch 块
   e1.printStackTrace();
   }
   WritableCellFormat format =new WritableCellFormat(font);
   try {
   format.setAlignment(jxl.format.Alignment.CENTRE);//左右居中
   format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//上下居中
   format.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//黑色边框
   format.setBackground(Colour.YELLOW);//黄色背景
   } catch (WriteException e){
    // TODO 自动生成catch 块
   e.printStackTrace();
   }
   return format;
}

转载至博客：hlhpyasd

0 0