jsp导出Excel-样式配置.doc

jsp导出excel之格式控制
2009-08-26 14:43:29| 分类： Java扩展 | 标签： |字号大中小 订阅
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="org.apache.poi.hssf.usermodel.*"%>
<%@ page import="org.apache.poi.hssf.util.Region" %>
<%
//获取参数
String searcWord = request.getParameter("searchword");

if (null == searcWord || "".equals(searcWord.trim())) {
return;

} else {

searcWord=new String(searcWord.getBytes("iso-8859-1"),"UTF-8");

Connection con = SQLServices.getConnection();
ResultSet rs = con.executeSelect("qsssd",
searcWord, false);

if (null == rs) {
return;
} else {
//开始构造excel;

try {

// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();

// 在Excel工作簿中建一工作表，其名为缺省值。
// 也可以指定工作表的名字。
HSSFSheet sheet = workbook.createSheet("Report");

//==========================设置字体样式start==========================
HSSFCellStyle cs = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("宋体");// 设置字体
font.setFontHeightInPoints((short) 18);// 字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
cs.setFont(font);
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

HSSFCellStyle cs2 = workbook.createCellStyle();
HSSFFont font2 = workbook.createFont();
font2.setFontName("宋体");// 设置字体
font2.setFontHeightInPoints((short) 12);// 字体大小
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
cs2.setFont(font2);
cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

HSSFCellStyle cs3 = workbook.createCellStyle();
HSSFFont font3 = workbook.createFont();
font3.setFontName("宋体");
font3.setFontHeightInPoints((short) 10);
cs3.setFont(font3);
cs3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
cs3.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
cs3.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cs3.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cs3.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框

HSSFCellStyle cs4 = workbook.createCellStyle();
HSSFFont font4 = workbook.createFont();
font4.setFontName("宋体");
font4.setFontHeightInPoints((short) 12);
cs4.setFont(font4);
cs4.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
cs4.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
cs4.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cs4.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cs4.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
HSSFCellStyle dateCellStyle=workbook.createCellStyle();
short defor=workbook.createDataFormat().getFormat("yyyy-mm-dd");
cs4.setDataFormat(defor);
//==========================设置字体样式end==========================

//设置列宽
sheet.setColumnWidth((short) 0, (short) (35.7*48));//n为列高的像素数
sheet.setColumnWidth((short) 1, (short) (35.7*100));
sheet.setColumnWidth((short) 2, (short) (35.7*120));
sheet.setColumnWidth((short) 3, (short) (35.7*130));
sheet.setColumnWidth((short) 4, (short) (35.7*310));
sheet.setColumnWidth((short) 5, (short) (35.7*100));
sheet.setColumnWidth((short) 6, (short) (35.7*80));
sheet.setColumnWidth((short) 7, (short) (35.7*150));

// 打印设置

HSSFPrintSetup hps = sheet.getPrintSetup();
hps.setPaperSize((short) 9); // 设置A4纸
// hps.setLandscape(true); // 将页面设置为横向打印模式
sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
sheet.setVerticallyCenter(true); // 设置打印页面为垂直居中
//wb.setPrintArea(0, "$A$2:$e$" + rowNum + 2);// 打印区域设置.

int rowNum = 0;// 行标
// int colNum = 0;// 列标
// 建立表头信息
// 在索引0的位置创建行（最顶端的行）

//第一行，表格标题
HSSFRow row0 = sheet.createRow((short) 0);
HSSFCell cell_0 = row0.createCell((short) 0);
cell_0.setCellType(HSSFCell.CELL_TYPE_STRING);
cell_0.setEncoding(HSSFCell.ENCODING_UTF_16);
cell_0.setCellStyle(cs);
cell_0.setCellValue("请示登记单");
sheet.addMergedRegion(new Region(0,(short)0,0,(short)7)); //合并单元格
row0.setHeightInPoints((float)22);//设置行高

//第二行 列标题
HSSFRow row = sheet.createRow((short) 1);
row.setHeightInPoints((float)22);//设置行高
// 单元格
// 在当前行的colNum列上创建单元格
HSSFCell cell_1 = row.createCell((short) 0);

// 定义单元格为字符类型，也可以指定为日期类型、数字类型
cell_1.setCellType(HSSFCell.CELL_TYPE_STRING);
// 定义编码方式，为了支持中文，这里使用了ENCODING_UTF_16
cell_1.setEncoding(HSSFCell.ENCODING_UTF_16);
// 为单元格设置格式
cell_1.setCellStyle(cs2);
// 添加内容至单元格
cell_1.setCellValue("序号");

HSSFCell cell1 = row.createCell((short) 1);
cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
cell1.setCellStyle(cs2);
cell1.setCellValue("文件序号");

HSSFCell cell2 = row.createCell((short) 2);
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
cell2.setCellStyle(cs2);
cell2.setCellValue("文件编号");

HSSFCell cell3 = row.createCell((short) 3);
cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
cell3.setCellStyle(cs2);
cell3.setCellValue("日期");

HSSFCell cell4 = row.createCell((short) 4);
cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
cell4.setCellStyle(cs2);
cell4.setCellValue("标题");

HSSFCell cell5 = row.createCell((short) 5);
cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
cell5.setCellStyle(cs2);
cell5.setCellValue("拟稿部门");

HSSFCell cell6 = row.createCell((short) 6);
cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
cell6.setCellStyle(cs2);
cell6.setCellValue("拟稿人");

HSSFCell cell7 = row.createCell((short) 7);
cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
cell7.setCellStyle(cs2);
cell7.setCellValue("备注");

long size = rs.getRecordCount();
System.out.println("==========>" + size);

HSSFCell data_cell0 = null;
HSSFCell data_cell1 = null;
HSSFCell data_cell2 = null;
HSSFCell data_cell3 = null;
HSSFCell data_cell4 = null;
HSSFCell data_cell5 = null;
HSSFCell data_cell6 = null;
HSSFCell data_cell7 = null;

for (int i = 0; i < size; i++) {
// 新建第rowNum行
row = sheet.createRow((short) i + 2);//从第三行开始
row.setHeightInPoints((float)22);//设置行高
data_cell0 = row.createCell((short) 0);
data_cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell0.setCellStyle(cs3);
data_cell0.setCellValue(i+1);
//文件序号
data_cell1 = row.createCell((short) 1);
data_cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell1.setCellStyle(cs3);
data_cell1.setCellValue(rs.getString("WJNumber"));
//文件编号
data_cell2 = row.createCell((short) 2);
data_cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell2.setCellStyle(cs3);
data_cell2.setCellValue(rs.getString("Fd_No"));
//日期
data_cell3 = row.createCell((short) 3);
data_cell3.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell3.setCellStyle(cs3);
String tempDate = rs.getString("QSDate");
if(tempDate.length()>10){
tempDate= tempDate.replaceAll(" 00:00:00","");
}
tempDate = tempDate.replaceAll("\\.","-");
data_cell3.setCellValue(tempDate);
//标题
data_cell4 = row.createCell((short) 4);
data_cell4.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell4.setCellStyle(cs3);
data_cell4.setCellValue(rs.getString("fldSubject"));
//拟稿部门
data_cell5 = row.createCell((short) 5);
data_cell5.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell5.setCellStyle(cs3);
data_cell5.setCellValue(rs.getString("fd_planDept"));
//拟稿人
data_cell6 = row.createCell((short) 6);
data_cell6.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell6.setCellStyle(cs3);
data_cell6.setCellValue(rs.getString("seJbMan"));

//备注
data_cell7 = row.createCell((short) 7);
data_cell7.setEncoding(HSSFCell.ENCODING_UTF_16);
data_cell7.setCellStyle(cs3);
data_cell7.setCellValue(rs.getString("Fd_Remark"));

}

//response.setCharacterEncoding("gbk");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=_Report.xls");
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();

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

}

}

%>