JXL实例（二）

 

package jxlpractice;

import jxl.*;
import jxl.write.*;
import java.io.*;
import java.io.File.*;
import java.util.*;

public class jxlexample {
  public static void main(String args[]) {
    String targetFile = "c:\\jxlexample.xls"; //输出的excel文件名
    String workSheet = "testjxl"; //输出的excel文件工作表名
    String[] title = {
        "aaa", "bbb", "ccc"}; //excel工作表的标题

    WritableWorkbook workbook;//创建可写入的excel工作bo
    try {

      System.out.println("begin");

      OutputStream os = new FileOutputStream(targetFile);
      workbook = Workbook.createWorkbook(os);

      WritableSheet sheet = workbook.createSheet(workSheet, 0); //添加第一个工作表
//WritableSheet sheet1 = workbook.createSheet("MySheet1", 1); //可添加第二个工作

      jxl.write.Label label;
      for (int i = 0; i < title.length; i++) {
//Label(列号,行号 ,内容 )
        label = new jxl.write.Label(i, 0, title[i]); //put the title in row1
        sheet.addCell(label);
      }

//下列添加的对字体等的设置均调试通过，可作参考用

//添加数字Number(列号,行号,内容)
      jxl.write.Number number = new jxl.write.Number(0, 1, 3.14159); //put the number 3.14159 in cell D5
      sheet.addCell(number);

//添加带有字型Formatting的对象
      jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES,
          10, WritableFont.BOLD, true);
      jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
      jxl.write.Label labelCF = new jxl.write.Label(4, 4, "文本", wcfF);
      sheet.addCell(labelCF);

//添加带有字体颜色,带背景颜色 Formatting的对象
      jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.
          ARIAL, 10, WritableFont.BOLD, false,
          jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
      jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
      wcfFC.setBackground(jxl.format.Colour.BLUE);
      jxl.write.Label labelCFC = new jxl.write.Label(1, 5, "带颜色", wcfFC);
      sheet.addCell(labelCFC);

//添加带有formatting的Number对象
      jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
      jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
      jxl.write.Number labelNF = new jxl.write.Number(1, 8, 3.1415926, wcfN);
      sheet.addCell(labelNF);

//3.添加Boolean对象
      jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false);
      sheet.addCell(labelB);

//4.添加DateTime对象
      jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3,
          new java.util.Date());
      sheet.addCell(labelDT);

//添加带有formatting的DateFormat对象
      jxl.write.DateFormat df = new jxl.write.DateFormat("ddMMyyyyhh:mm:ss");
      jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df);
      jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3,
          new java.util.Date(), wcfDF);
      sheet.addCell(labelDTF);

//和宾单元格
//sheet.mergeCells(int col1,int row1,int col2,int row2);//左上角到右下角
      sheet.mergeCells(4, 5, 8, 10); //左上角到右下角(4,5),(8,10)分别为左上角和右下角的坐标
      wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 40,
                                       WritableFont.BOLD, false,
                                       jxl.format.UnderlineStyle.NO_UNDERLINE,
                                       jxl.format.Colour.GREEN);
      jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
      wchB.setAlignment(jxl.format.Alignment.CENTRE);
      labelCFC = new jxl.write.Label(4, 5, "单元合并", wchB);
      sheet.addCell(labelCFC); //

//设置边框
      jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
      wcsB.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THICK);
      labelCFC = new jxl.write.Label(0, 6, "边框设置", wcsB);
      sheet.addCell(labelCFC);
      workbook.write();
      workbook.close();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    System.out.println("end");
    Runtime r = Runtime.getRuntime();
    Process p = null;
//String cmd[]={"notepad","exec.java"};
    /*String cmd[] = {
        "C:\Program Files\Microsoft Office\Office\EXCEL.EXE", "out.xls"};
    try {
      p = r.exec(cmd);
    }

  }

  catch (Exception ex) {

  }*/
}
}

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1754921

 

jxl操作Excel源码

/**
  * 将个人信息导出到Excel表
  */
 public void grxxExportXLS(HttpServletResponse response, Grxx grxx,
   PageBean p) {
  List list = queryGrxxList(grxx, p);
  try {
   // String targetfile = getServlet().getServletContext().getRealPath(
   // "/companymanage")
   // + "/grxxlistxls.xls";
   // Method 1：创建可写入的Excel工作薄
   // jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new
   // File(
   // targetfile));
   // Method 2：将WritableWorkbook直接写入到输出流
   OutputStream os = response.getOutputStream();
   String fs = "个人信息表";
   fs = new String(fs.getBytes("gb2312"), "iso8859-1");
   response.reset();// 清空输出流
   response.setContentType("application/msexcel");// 定义输出类型
   response.setHeader("Content-disposition", "attachment; filename="
     + fs + ".xls");// 设定输出文件头
   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

   // 创建Excel工作表
   jxl.write.WritableSheet ws = wwb.createSheet("个人信息表1", 0);
   /**
    * 19列,标题占据2行
    */
   int charTitle = 10;// 标题字体大小
   int charNormal = 10;// 标题字体大小
   // 添加带有字型Formatting的对象

   // 用于标题
   jxl.write.WritableFont titleFont = new jxl.write.WritableFont(
     WritableFont.createFont("宋体"), charTitle, WritableFont.BOLD);

   jxl.write.WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(
     titleFont);
   titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
   titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
   titleFormat.setAlignment(Alignment.CENTRE); // 水平对齐
   titleFormat.setWrap(true); // 是否换行
   titleFormat.setBackground(Colour.GRAY_25);// 背景色暗灰-25%
   // 用于正文
   WritableFont normalFont = new WritableFont(WritableFont
     .createFont("宋体"), charNormal);
   jxl.write.WritableCellFormat normalFormat = new jxl.write.WritableCellFormat(
     normalFont);
   normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
   normalFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
   normalFormat.setAlignment(Alignment.CENTRE);// 水平对齐
   normalFormat.setWrap(true); // 是否换行

   // 用于带有formatting的Number对象
   WritableFont nf = new WritableFont(WritableFont
     .createFont("Times New Roman"), charNormal);
   // jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
   jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(
     nf);
   wcfN.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
   wcfN.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
   wcfN.setAlignment(Alignment.CENTRE);// 水平对齐
   wcfN.setWrap(true); // 是否换行
   /**
    * 添加标题
    */
   // 第一行
   /** *********设置列宽每个字体占charset个**************** */
   ws.mergeCells(14, 0, 18, 0); // 合并单元格，
   ws.mergeCells(10, 0, 13, 0); // 合并单元格，

   ws.mergeCells(0, 0, 0, 1); // 合并单元格，序号
   ws.setColumnView(0, 6); // 设置列宽,第1列
   ws.mergeCells(1, 0, 1, 1); // 合并单元格，公民身份证号
   ws.setColumnView(1, 9); // 设置列宽,第2列
   ws.mergeCells(2, 0, 2, 1); // 合并单元格，电脑序号
   ws.setColumnView(2, 6); // 设置列宽,第3列
   ws.mergeCells(3, 0, 3, 1); // 合并单元格，四险姓名
   ws.setColumnView(3, 6); // 设置列宽,第4列
   ws.mergeCells(4, 0, 4, 1); // 合并单元格，医疗姓名
   ws.setColumnView(4, 6); // 设置列宽,第5列
   ws.mergeCells(5, 0, 5, 1); // 合并单元格，医疗保险手册号
   ws.setColumnView(5, 9); // 设置列宽,第6列
   ws.mergeCells(6, 0, 6, 1); // 合并单元格，四险缴费人员类别
   ws.setColumnView(6, 9); // 设置列宽,第7列
   ws.mergeCells(7, 0, 7, 1); // 合并单元格，医疗缴费人员类别
   ws.setColumnView(7, 9); // 设置列宽,第8列
   ws.mergeCells(8, 0, 8, 1); // 合并单元格，医疗参保人员类别
   ws.setColumnView(8, 9); // 设置列宽,第9列
   ws.mergeCells(9, 0, 9, 1); // 合并单元格，医疗参保状态
   ws.setColumnView(9, 7); // 设置列宽,第10列
   ws.setColumnView(10, 6); // 设置列宽,第11列
   ws.setColumnView(11, 6); // 设置列宽,第12列
   ws.setColumnView(12, 6); // 设置列宽,第13列
   ws.setColumnView(13, 6); // 设置列宽,第14列
   ws.setColumnView(14, 6); // 设置列宽,第15列
   ws.setColumnView(15, 6); // 设置列宽,第16列
   ws.setColumnView(16, 6); // 设置列宽,第17列
   ws.setColumnView(17, 6); // 设置列宽,第18列
   ws.setColumnView(18, 6); // 设置列宽,第19列
   jxl.write.Label tit00 = new jxl.write.Label(0, 0, "序号", titleFormat);
   ws.addCell(tit00);
   jxl.write.Label tit10 = new jxl.write.Label(1, 0, "公民身份证号",
     titleFormat);
   ws.addCell(tit10);
   jxl.write.Label tit20 = new jxl.write.Label(2, 0, "电脑序号",
     titleFormat);
   ws.addCell(tit20);
   jxl.write.Label tit30 = new jxl.write.Label(3, 0, "四险姓名",
     titleFormat);
   ws.addCell(tit30);
   jxl.write.Label tit40 = new jxl.write.Label(4, 0, "医疗姓名",
     titleFormat);
   ws.addCell(tit40);
   jxl.write.Label tit50 = new jxl.write.Label(5, 0, "医疗保险手册号",
     titleFormat);
   ws.addCell(tit50);
   jxl.write.Label tit60 = new jxl.write.Label(6, 0, "四险缴费人员类别",
     titleFormat);
   ws.addCell(tit60);
   jxl.write.Label tit70 = new jxl.write.Label(7, 0, "医疗缴费人员类别",
     titleFormat);
   ws.addCell(tit70);
   jxl.write.Label tit80 = new jxl.write.Label(8, 0, "医疗参保人员类别",
     titleFormat);
   ws.addCell(tit80);
   jxl.write.Label tit90 = new jxl.write.Label(9, 0, "医疗参保状态",
     titleFormat);
   ws.addCell(tit90);
   jxl.write.Label tit100 = new jxl.write.Label(10, 0, "四险缴费状态",
     titleFormat);
   ws.addCell(tit100);
   jxl.write.Label tit140 = new jxl.write.Label(14, 0, "缴费基数",
     titleFormat);
   ws.addCell(tit140);
   // 第二行
   jxl.write.Label tit101 = new jxl.write.Label(10, 1, "养老",
     titleFormat);
   ws.addCell(tit101);
   jxl.write.Label tit111 = new jxl.write.Label(11, 1, "失业",
     titleFormat);
   ws.addCell(tit111);
   jxl.write.Label tit121 = new jxl.write.Label(12, 1, "工伤",
     titleFormat);
   ws.addCell(tit121);
   jxl.write.Label tit131 = new jxl.write.Label(13, 1, "生育",
     titleFormat);
   ws.addCell(tit131);
   jxl.write.Label tit141 = new jxl.write.Label(14, 1, "养老",
     titleFormat);
   ws.addCell(tit141);
   jxl.write.Label tit151 = new jxl.write.Label(15, 1, "失业",
     titleFormat);
   ws.addCell(tit151);
   jxl.write.Label tit161 = new jxl.write.Label(16, 1, "工伤",
     titleFormat);
   ws.addCell(tit161);
   jxl.write.Label tit171 = new jxl.write.Label(17, 1, "生育",
     titleFormat);
   ws.addCell(tit171);
   jxl.write.Label tit181 = new jxl.write.Label(18, 1, "医疗",
     titleFormat);
   ws.addCell(tit181);

   double yljs = 0;// 医疗基数合计
   double yxjs = 0;// 养老基数合计
   double syjs = 0;// 失业基数合计
   double sxjs = 0;// 生育基数合计
   double gsjs = 0;// 工伤基数合计
   for (int i = 0; i < list.size(); i++) {
    Grxx xx = (Grxx) list.get(i);
    jxl.write.Label lab1 = new jxl.write.Label(0, i + 2, String
      .valueOf(i + 1), wcfN);
    ws.addCell(lab1);// 第1列：序号
    jxl.write.Label lab2 = new jxl.write.Label(1, i + 2, xx
      .getBzhm(), wcfN);
    ws.addCell(lab2);// 第2列：公民身份证号
    jxl.write.Label lab3 = new jxl.write.Label(2, i + 2, "字段不存在",
      wcfN);
    ws.addCell(lab3);// 第3列：电脑序号
    jxl.write.Label lab4 = new jxl.write.Label(3, i + 2,
      xx.getXm(), normalFormat);
    ws.addCell(lab4);// 第4列：四险姓名
    jxl.write.Label lab5 = new jxl.write.Label(4, i + 2,
      xx.getXm(), normalFormat);
    ws.addCell(lab5);// 第5列：医疗姓名
    jxl.write.Label lab6 = new jxl.write.Label(5, i + 2, xx
      .getBxh(), wcfN);
    ws.addCell(lab6);// 第6列：医疗保险手册号
    jxl.write.Label lab7 = new jxl.write.Label(6, i + 2, xx
      .getBxh(), normalFormat);
    ws.addCell(lab7);// 第7列：四险缴费人员类别
    jxl.write.Label lab8 = new jxl.write.Label(7, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab8);// 第8列：医疗缴费人员类别
    jxl.write.Label lab9 = new jxl.write.Label(8, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab9);// 第9列：医疗参保人员类别
    jxl.write.Label lab10 = new jxl.write.Label(9, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab10);// 第10列：医疗参保状态
    jxl.write.Label lab11 = new jxl.write.Label(10, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab11);// 第11列：养老缴费状态
    jxl.write.Label lab12 = new jxl.write.Label(11, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab12);// 第12列：失业缴费状态
    jxl.write.Label lab13 = new jxl.write.Label(12, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab13);// 第13列：工伤缴费状态
    jxl.write.Label lab14 = new jxl.write.Label(13, i + 2, "字段不存在",
      normalFormat);
    ws.addCell(lab14);// 第14列：生育缴费状态
    jxl.write.Number lab15 = new jxl.write.Number(14, i + 2, xx
      .getGrjs().getYxjfjs().doubleValue(), wcfN);
    ws.addCell(lab15);// 第15列：养老缴费基数
    yxjs += xx.getGrjs().getYxjfjs().doubleValue();
    jxl.write.Number lab16 = new jxl.write.Number(15, i + 2, xx
      .getGrjs().getSyjfjs().doubleValue(), wcfN);
    ws.addCell(lab16);// 第16列：失业缴费基数
    syjs += xx.getGrjs().getSyjfjs().doubleValue();
    jxl.write.Number lab17 = new jxl.write.Number(16, i + 2, xx
      .getGrjs().getGsjfjs().doubleValue(), wcfN);
    ws.addCell(lab17);// 第17列：工伤缴费基数
    gsjs += xx.getGrjs().getGsjfjs().doubleValue();
    jxl.write.Number lab18 = new jxl.write.Number(17, i + 2, xx
      .getGrjs().getSxjfjs().doubleValue(), wcfN);
    ws.addCell(lab18);// 第18列：生育缴费基数
    sxjs += xx.getGrjs().getSxjfjs().doubleValue();
    jxl.write.Number lab19 = new jxl.write.Number(18, i + 2, xx
      .getGrjs().getYljfjs().doubleValue(), wcfN);
    ws.addCell(lab19);// 第19列：医疗缴费基数
    yljs += xx.getGrjs().getYljfjs().doubleValue();
   }
   /**
    * 最后一行合计
    */
   int end = list.size() + 2;
   jxl.write.Label end0 = new jxl.write.Label(0, end, "合计",
     normalFormat);
   ws.addCell(end0);
   jxl.write.Label end1 = new jxl.write.Label(1, end, "--",
     normalFormat);
   ws.addCell(end1);
   jxl.write.Label end2 = new jxl.write.Label(2, end, "--",
     normalFormat);
   ws.addCell(end2);
   jxl.write.Label end3 = new jxl.write.Label(3, end, "--",
     normalFormat);
   ws.addCell(end3);
   jxl.write.Label end4 = new jxl.write.Label(4, end, "--",
     normalFormat);
   ws.addCell(end4);
   jxl.write.Label end5 = new jxl.write.Label(5, end, "--",
     normalFormat);
   ws.addCell(end5);
   jxl.write.Label end6 = new jxl.write.Label(6, end, "--",
     normalFormat);
   ws.addCell(end6);
   jxl.write.Label end7 = new jxl.write.Label(7, end, "--",
     normalFormat);
   ws.addCell(end7);
   jxl.write.Label end8 = new jxl.write.Label(8, end, "--",
     normalFormat);
   ws.addCell(end8);
   jxl.write.Label end9 = new jxl.write.Label(9, end, "--",
     normalFormat);
   ws.addCell(end9);
   jxl.write.Label end10 = new jxl.write.Label(10, end, "--",
     normalFormat);
   ws.addCell(end10);
   jxl.write.Label end11 = new jxl.write.Label(11, end, "--",
     normalFormat);
   ws.addCell(end11);
   jxl.write.Label end12 = new jxl.write.Label(12, end, "--",
     normalFormat);
   ws.addCell(end12);
   jxl.write.Label end13 = new jxl.write.Label(13, end, "--",
     normalFormat);
   ws.addCell(end13);
   // 养老
   jxl.write.Number end14 = new jxl.write.Number(14, end, yxjs, wcfN);
   ws.addCell(end14);
   // 失业
   jxl.write.Number end15 = new jxl.write.Number(15, end, syjs, wcfN);
   ws.addCell(end15);
   // 工伤
   jxl.write.Number end16 = new jxl.write.Number(16, end, gsjs, wcfN);
   ws.addCell(end16);
   // 生育
   jxl.write.Number end17 = new jxl.write.Number(17, end, sxjs, wcfN);
   ws.addCell(end17);
   // 医疗
   jxl.write.Number end18 = new jxl.write.Number(18, end, yljs, wcfN);
   ws.addCell(end18);
   // 写入Exel工作表
   wwb.write();
   // 关闭Excel工作薄对象
   wwb.close();
   os.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 

http://www.cnblogs.com/raymond19840709/archive/2008/09/23/1297261.html