jxlExport

来源：互联网发布：一手淘宝买家数据编辑：程序博客网时间：2024/06/06 02:06

package com.hutu.jxl;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelDemo extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

  response.setContentType("text/html");
  response.setCharacterEncoding("UTF-8");
  this.doPost(request, response);
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {

   response.setContentType("text/html");
   response.setCharacterEncoding("UTF-8");
   // 获得输出流，该输出流的介质是客户端浏览器

   /*
       文档注释部分代表将B/S输出模式。不是输出到本地磁盘
    OutputStream outputStream = response.getOutputStream();
    response.reset();// 清除缓冲区中的数据
    response.setHeader("Content-disposition","attachment;filename=temp.xls");
    response.setContentType("application/msexcel");
    // 创建可写入的Excel工作薄、且内容将写入的输出流。并并且通过输出流输出给客户端浏览器
    WritableWorkbook wk = Workbook.createWorkbook(outputStream);

   */

   OutputStream outputStream = response.getOutputStream();
   response.reset();// 清除缓冲区中的数据
   response.setHeader("Content-disposition",
     "attachment;filename=temp.xls");
   response.setContentType("application/msexcel");
   // 创建可写入的Excel工作薄、且内容将写入的输出流。并并且通过输出流输出给客户端浏览器
   WritableWorkbook wk = Workbook.createWorkbook(outputStream);
   // /创建可写入的Excel工作薄
   WritableSheet sheet = wk.createSheet("成绩表", 0);
   // 把单元格(column,row)到单元格(column1,row1)进行合并。
   // mergeCells(column,row,column1,row1);
   sheet.mergeCells(0, 0, 4, 0);// 单元格合并方法
   // 创建writableFont字体形象,参数一次表示黑体、字号12、粗体、非斜体、不带下划线、亮蓝色
   WritableFont titleFont = new WritableFont(WritableFont
     .createFont("黑体"), 12, WritableFont.BOLD, false,
     UnderlineStyle.NO_UNDERLINE, Colour.LIGHT_BLUE);
   // 创建WritableCellFormat对象,将该对象应用于单元格从而设置单元格的样式
   WritableCellFormat titleFormat = new WritableCellFormat();
   // 设置字体格式
   titleFormat.setFont(titleFont);
   // 设置文本水平绝种对其
   titleFormat.setAlignment(Alignment.CENTRE);
   // 设置文本垂直居中对齐
   titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
   // 设置背景颜色
   titleFormat.setBackground(Colour.GRAY_25);
   titleFormat.setWrap(true);
   // 添加Label对象，参数依次表示在第一列、第一行、内容、使用的格式
   Label lab_00 = new Label(0, 0, "学院考试成绩一览表", titleFormat);
   // 将定义好的Label对象添加到工作表上，这样工作表的第一列第一行的内容为"学员考试成绩一览表"并应用了titleFormat定义的样式
   sheet.addCell(lab_00);
   WritableCellFormat columnTitleFormat = new WritableCellFormat();
   columnTitleFormat.setFont(new WritableFont(WritableFont
     .createFont("宋体"), 10, WritableFont.BOLD, false));
   columnTitleFormat.setAlignment(Alignment.CENTRE);
   Label lab_01 = new Label(0, 1, "姓名", columnTitleFormat);
   Label lab_11 = new Label(1, 1, "班级", columnTitleFormat);
   Label lab_21 = new Label(2, 1, "笔试成绩", columnTitleFormat);
   Label lab_31 = new Label(3, 1, "上机成绩", columnTitleFormat);
   Label lab_41 = new Label(4, 1, "考试日期", columnTitleFormat);
   sheet.addCell(lab_01);
   sheet.addCell(lab_11);
   sheet.addCell(lab_21);
   sheet.addCell(lab_31);
   sheet.addCell(lab_41);
   sheet.addCell(new Label(0, 2, "李明"));
   sheet.addCell(new Label(1, 2, "As178"));
   // 定义数字格式
   NumberFormat nf = new NumberFormat("0.00");
   WritableCellFormat wcf = new WritableCellFormat(nf);
   // 类似于Label对象、区别Label表示文本数据、Number表示数值型数据
   Number numlab_22 = new Number(2, 2, 78, wcf);
   sheet.addCell(numlab_22);
   sheet.addCell(new Number(3, 2, 87, new WritableCellFormat(
     new NumberFormat("#.##"))));
   // 定义日期格式
   DateFormat df = new DateFormat("yyyy-MM-dd hh:mm:ss");
   // 创建WritableCellFormat对象
   WritableCellFormat datewcf = new WritableCellFormat(df);
   DateTime dtLab_42 = new DateTime(4, 2, new Date(), datewcf);
   sheet.addCell(dtLab_42);
   // 将定义的工作表输出到之前定义的介质中(这里是客户端浏览器)
   wk.write();
   // 操作完成时，关闭对象，释放占用的内存空间
   wk.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
}

}