jxl操作excel

最新推荐文章于 2017-08-02 10:19:49 发布

ytlizhen1

最新推荐文章于 2017-08-02 10:19:49 发布

阅读量526

点赞数

文章标签： exception string 工作 null excel date

public ActionForward toExcelYk(ActionMapping arg0, ActionForm arg1,
   HttpServletRequest arg2, HttpServletResponse arg3) {
  ClientDataSetForm form = (ClientDataSetForm) arg1;
  try {
   System.err.println("开始导出");
   BufferedInputStream bis = null;
   ServletOutputStream outfile = null;
   arg3.reset();
   arg3.setContentType("application/octet-stream");
   Record rd = form.getRecord();
   String temp = rd.getString("CREATE_DATE");
   if (null == temp || "".equals(temp)) {
    throw new Exception("导出日期为空");
   }
   String dateStr = temp.substring(0, 4) + temp.substring(5, 7)
     + temp.substring(8, 10);

   String fileName = "地调EMS遥控动作情况表（佛山" + dateStr + "）.xls";
   String fileNameNew = new String(fileName.getBytes("gb2312"),
     "iso8859-1");
   arg3.setHeader("Content-disposition", "attachment; filename="
     + fileNameNew);
   outfile = arg3.getOutputStream();

   BufferedOutputStream bos = new BufferedOutputStream(outfile);
   // 打开文件
   WritableWorkbook book = Workbook.createWorkbook(bos);
   // 生成名为"第一页"的工作表，参数0标示这是第一页
   WritableSheet sheet = book.createSheet("Sheet1", 0);
   // 设置列的宽度
   sheet.setColumnView(0, 6);
   sheet.setColumnView(1, 12);
   sheet.setColumnView(2, 12);
   sheet.setColumnView(3, 12);
   sheet.setColumnView(4, 12);
   sheet.setColumnView(5, 12);
   sheet.setColumnView(6, 12);
   sheet.setColumnView(7, 12);
   sheet.setColumnView(8, 12);
   sheet.setColumnView(9, 12);
   // 设置行的高度

   // 在label对象的构造子中指明单元格位置是第一列第一行（0,0）
   // 以及单元格内容为test
   Label label0 = new Label(0, 0, "表1：遥控操作情况", setTitle(10));
   Label label1 = new Label(0, 1, "序号", setTitle(10));
   Label label2 = new Label(1, 1, "地调", setTitle(10));
   Label label3 = new Label(2, 1, "具备遥控功能的变电站个数", setTitle(10));
   Label label4 = new Label(2, 2, "500KV", setTitle(10));
   Label label5 = new Label(3, 2, "220KV", setTitle(10));
   Label label6 = new Label(4, 1, "遥控操作次数", setTitle(10));
   Label label7 = new Label(5, 1, "AVC遥控次数", setTitle(10));
   Label label8 = new Label(6, 1, "遥控总次数", setTitle(10));
   Label label9 = new Label(7, 1, "遥控拒动次数", setTitle(10));
   Label label10 = new Label(8, 1, "遥控误动次数", setTitle(10));
   Label label11 = new Label(9, 1, "遥控正确动作率", setTitle(10));

   Label label12 = new Label(0, 7, "表2：遥控失败情况", setTitle(10));
   Label label13 = new Label(0, 8, "序号", setTitle(10));
   Label label14 = new Label(1, 8, "地调", setTitle(10));
   Label label15 = new Label(2, 8, "电压等级", setTitle(10));
   Label label16 = new Label(3, 8, "遥控失败情况", setTitle(10));
   Label label17 = new Label(6, 8, "遥控失败原因", setTitle(10));
   Label label18 = new Label(0, 13, "备注", setTitle(10));
   Label label19 = new Label(
     1,
     13,
     "1、遥控操作次数：调度监控人员在地调EMS上对500/220/110/35/10kV断路器、刀闸设备进行遥控操作总次数",
     setTitleL(10));
   Label label20 = new Label(1, 14, "2、AVC遥控次数：地调AVC遥控总次数",
     setTitleL(10));
   Label label21 = new Label(1, 15, "3、“遥控操作次数、AVC遥控次数”不包括遥控试验进行的操作",
     setTitleL(10));
   Label label22 = new Label(1, 16, "4、认真统计遥控拒动次数、遥控误动次数，深入分析原因",
     setTitleL(10));
   Label label23 = new Label(1, 17,
     "5、x=x1+x2，z=(x1+x2-y1-y2)/(x1+x2)", setTitleL(10));
   Label label24 = new Label(
     1,
     18,
     "6、统计时段：工作日（昨天16:00-今天16:00）,休息日/节假日（上一工作日16:00-休息日/节假日最后一日16:00，如周日统计上周五16:00-周日16:00）",
     setTitleL(10));
   Label label25 = new Label(1, 20,
     "7、上报时间：工作日（16:30前）,休息日/节假日（休息日/节假日最后一日16:30前）",
     setTitleL(10));
   Label label26 = new Label(1, 21,
     "8、上报方式：通过OAK发送到“自动化值班/自动化部/调控中心/Geph”邮箱", setTitleL(10));

   // 将定义好的单元格添加到工作表中
   sheet.addCell(label0);
   sheet.addCell(label1);
   sheet.addCell(label2);
   sheet.addCell(label3);
   sheet.addCell(label4);
   sheet.addCell(label5);
   sheet.addCell(label6);
   sheet.addCell(label7);
   sheet.addCell(label8);
   sheet.addCell(label9);
   sheet.addCell(label10);
   sheet.addCell(label11);
   sheet.addCell(label12);
   sheet.addCell(label13);
   sheet.addCell(label14);
   sheet.addCell(label15);
   sheet.addCell(label16);
   sheet.addCell(label17);
   sheet.addCell(label18);
   sheet.addCell(label19);
   sheet.addCell(label20);
   sheet.addCell(label21);
   sheet.addCell(label22);
   sheet.addCell(label23);
   sheet.addCell(label24);
   sheet.addCell(label25);
   sheet.addCell(label26);

   // 从col1到col2列从row1行到row2行
   // mergecells(int col1,int row1,int col2,int row2);
   sheet.mergeCells(0, 0, 9, 0);// 合并单元格
   sheet.mergeCells(0, 1, 0, 2);// 合并单元格
   sheet.mergeCells(1, 1, 1, 2);// 合并单元格
   sheet.mergeCells(2, 1, 3, 1);// 合并单元格
   sheet.mergeCells(2, 2, 2, 2);// 合并单元格
   sheet.mergeCells(3, 2, 3, 2);// 合并单元格
   sheet.mergeCells(4, 1, 4, 2);// 合并单元格
   sheet.mergeCells(5, 1, 5, 2);// 合并单元格
   sheet.mergeCells(6, 1, 6, 2);// 合并单元格
   sheet.mergeCells(7, 1, 7, 2);// 合并单元格
   sheet.mergeCells(8, 1, 8, 2);// 合并单元格
   sheet.mergeCells(9, 1, 9, 2);// 合并单元格

   sheet.mergeCells(0, 7, 9, 7);// 合并单元格
   sheet.mergeCells(3, 8, 5, 8);// 合并单元格
   sheet.mergeCells(6, 8, 9, 8);// 合并单元格

   sheet.mergeCells(0, 13, 0, 21);// 合并单元格
   sheet.mergeCells(1, 13, 9, 13);// 合并单元格
   sheet.mergeCells(1, 14, 9, 14);// 合并单元格
   sheet.mergeCells(1, 15, 9, 15);// 合并单元格
   sheet.mergeCells(1, 16, 9, 16);// 合并单元格
   sheet.mergeCells(1, 17, 9, 17);// 合并单元格
   sheet.mergeCells(1, 18, 9, 19);// 合并单元格
   sheet.mergeCells(1, 20, 9, 20);// 合并单元格
   sheet.mergeCells(1, 21, 9, 21);// 合并单元格

   Label lab0 = new Label(0, 3, "1", setTitle(10));
   Label lab1 = new Label(1, 3, rd.getString("PLACE"), setTitle(10));
   Label lab2 = new Label(2, 3, rd.getString("NUM500"), setTitle(10));
   Label lab3 = new Label(3, 3, rd.getString("NUM220"), setTitle(10));
   Label lab4 = new Label(4, 3, rd.getString("YK_NUM"), setTitle(10));
   Label lab5 = new Label(5, 3, rd.getString("AVC_NUM"), setTitle(10));
   Label lab6 = new Label(6, 3, rd.getString("YK_SUM"), setTitle(10));
   Label lab7 = new Label(7, 3, rd.getString("YKJD_NUM"), setTitle(10));
   Label lab8 = new Label(8, 3, rd.getString("YKWD_NUM"), setTitle(10));
   Label lab9 = new Label(9, 3, rd.getString("YK_CGL"), setTitle(10));

   sheet.addCell(lab0);
   sheet.addCell(lab1);
   sheet.addCell(lab2);
   sheet.addCell(lab3);
   sheet.addCell(lab4);
   sheet.addCell(lab5);
   sheet.addCell(lab6);
   sheet.addCell(lab7);
   sheet.addCell(lab8);
   sheet.addCell(lab9);

   // 写入数据并关闭文件
   book.write();
   book.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }

  return null;
}
public static WritableCellFormat setTitle(int fontSize)
   throws WriteException {
  // Colour color = Colour.GRAY_25;
  // 设置字体颜色
  WritableFont font = new jxl.write.WritableFont(WritableFont.ARIAL,
    fontSize, WritableFont.NO_BOLD, false,
    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
  WritableCellFormat cellFormat = new jxl.write.WritableCellFormat(font);
  // 设置单元格背景颜色
  // cellFormat.setBackground(color);
  // cellFormat.setShrinkToFit(true);
  cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);// 增加边框
  cellFormat.setVerticalAlignment((jxl.format.VerticalAlignment.CENTRE));// 上下居中
  cellFormat.setAlignment((jxl.format.Alignment.CENTRE));// 左右居中
  cellFormat.setWrap(true);
  return cellFormat;
}