//输出类
package em.util;
import java.io.*;
import jxl.LabelCell;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import jxl.write.Number;
import jxl.write.Boolean;
import jxl.write.DateTime;
import jxl.write.DateFormats;
import java.util.Date;
import java.util.List;
import java.io.IOException;
import java.util.List;
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.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.NumberFormats;
import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.io.*;
import java.util.*;
public class ComplexDataExcelWrite {
private WritableFont NormalFont, BoldFont, tableFont, baodanFont;
private WritableCellFormat wcf_title, wcf_tabletitle, wcf_baodan, wcf_left, wcf_center;
private WritableCellFormat wcf_merge, wcf_right, wcf_table, dtCellFormat;
private static ComplexDataExcelWrite instance = null;
private ComplexDataExcelWrite() {
}
public static synchronized ComplexDataExcelWrite getInstance() throws WriteException, IOException {
try {
if (instance == null) {
instance = new ComplexDataExcelWrite();
instance.formatFont();
instance.formatTableCell();
}
return instance;
} catch (Exception e) {
return null;
}
}
private void formatFont() {
/** ************设置单元格字体************** */
// 字体
NormalFont = new WritableFont(WritableFont.ARIAL, 10);
BoldFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
tableFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
baodanFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
}
private void formatTableCell() throws WriteException, IOException {
/** ************以下设置几种格式的单元格************ */
try {
// 用于标题
wcf_title = new WritableCellFormat(BoldFont);
wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_title.setWrap(true); // 是否换行
// 用于标题
wcf_tabletitle = new WritableCellFormat(tableFont);
wcf_tabletitle.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_tabletitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_tabletitle.setAlignment(Alignment.CENTRE); // 水平对齐
wcf_tabletitle.setWrap(true); // 是否换行
// 用于保单标题
wcf_baodan = new WritableCellFormat(baodanFont);
wcf_baodan.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_baodan.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_baodan.setAlignment(Alignment.CENTRE);
wcf_baodan.setWrap(true); // 是否换行
// 用于正文左
wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_left.setAlignment(Alignment.LEFT);
wcf_left.setWrap(true); // 是否换行
// 用于正文左
wcf_center = new WritableCellFormat(NormalFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_center.setAlignment(Alignment.CENTRE);
wcf_center.setWrap(true); // 是否换行
// 用于正文右
wcf_right = new WritableCellFormat(NormalFont);
wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_right.setAlignment(Alignment.RIGHT);
wcf_right.setWrap(false); // 是否换行
// 用于跨行
wcf_merge = new WritableCellFormat(NormalFont);
wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
wcf_merge.setAlignment(Alignment.LEFT);
wcf_merge.setWrap(true); // 是否换行
wcf_table = new WritableCellFormat(NormalFont);
wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_table.setAlignment(Alignment.CENTRE);
wcf_table.setBackground(Colour.GRAY_25);
wcf_table.setWrap(true); // 是否换行
// 时间格式
dtCellFormat = new WritableCellFormat(DateFormats.FORMAT1);
dtCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
dtCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
dtCellFormat.setAlignment(Alignment.CENTRE);
dtCellFormat.setBackground(Colour.GRAY_25);
dtCellFormat.setWrap(true); // 是否换行
} catch (Exception e) {
}
}
private void formatSheetColView(WritableSheet sheet) throws WriteException, IOException {
try {
/** *********设置列宽**************** */
sheet.setColumnView(0, 15); // 第1列
sheet.setColumnView(1, 15); // 第2列
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 15);
sheet.setColumnView(5, 15);
// 设置页边距
sheet.getSettings().setRightMargin(0.5);
} catch (Exception e) {
// TODO: handle exception
}
}
public void creatExcel(List list, OutputStream os) throws WriteException, IOException {
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet("first sheet", 0);
formatSheetColView(sheet);
// 添加主体
int rowNum = 0;
for (Iterator it = list.iterator(); it.hasNext();) {
rowNum += 1;
// 设置行高
sheet.setRowView(rowNum, 400, false);
String[] str = (String[]) it.next();
for (int i = 0; i < str.length; i++) {
Label example = new Label(i, rowNum, str[i], wcf_table);
sheet.addCell(example);
}
}
workbook.write();
workbook.close();
os.close();
} catch (Exception e) {
}
}
public void creatExcel(String sheetName, String[] title, List list, OutputStream os) throws WriteException, IOException {
try {
WritableWorkbook workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
formatSheetColView(sheet);
formatFont();
formatTableCell();
/** ****** 填充数据 ******** */
// 添加标题，第一行
sheet.mergeCells(0, 0, title.length - 1, 0);
Label titleLabel;
if (sheetName == null) {
titleLabel = new Label(0, 0, "sheetName", wcf_baodan);
} else {
titleLabel = new Label(0, 0, sheetName, wcf_baodan);
}
// 设置行高
sheet.setRowView(0, 400, false);
sheet.addCell(titleLabel);
// 添加表头
for (int i = 0; i < title.length; i++) {
Label formate = new Label(i, 1, title[i], wcf_tabletitle);
sheet.addCell(formate);
}
// 添加主体
int rowNum = 1;
for (Iterator it = list.iterator(); it.hasNext();) {
rowNum += 1;
// 设置行高
sheet.setRowView(rowNum, 400, false);
String[] str = (String[]) it.next();
for (int i = 0; i < str.length; i++) {
Label example = new Label(i, rowNum, str[i], wcf_table);
sheet.addCell(example);
}
}
workbook.write();
workbook.close();
workbook = null;
sheet = null;
} catch (RuntimeException e) {
e.printStackTrace();
}
}
}
//调用类
try {
List resultItems = new ArrayList();
resultItems.clear();
String[] title = { "第一列", "第二列", "第三列", "第四列" };
String[] s1 = { "11", "12", "13", "14" };
String[] s2 = { "21", "22", "23", "24" };
resultItems.add(s1);
resultItems.add(s2);
OutputStream os = response.getOutputStream();
response.reset();
String fname = "学校专业竞争力情况";
response.setCharacterEncoding("UTF-8");
fname = URLEncoder.encode(fname, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".XLS");
response.setContentType("application/msexcel");
ComplexDataExcelWrite.getInstance().creatExcel("标题", title, resultItems, os);
//ComplexDataExcelWrite sw=new ComplexDataExcelWrite();
// sw.creatExcel("标题", title, resultItems, os);
} catch (Exception e) {
// TODO: handle exception
}
return null;
//如果用jsp直接调用，注意out的处理方法，用他屏蔽一个getOutputStream() has already been called for this response
//异常
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@ page import="java.io.*"%>
<%@page import="java.net.URLEncoder"%>
<%@page import="em.util.ComplexDataExcelWrite"%>
<%
List resultItems = new ArrayList();
resultItems.clear();
String[] title = { "第一列", "第二列", "第三列", "第四列" };
String[] s1 = { "11", "12", "13", "14" };
String[] s2 = { "21", "22", "23", "24" };
resultItems.add(s1);
resultItems.add(s2);
OutputStream os = response.getOutputStream();
response.reset();
String fname = "学校专业竞争力情况";
response.setCharacterEncoding("UTF-8");
fname = URLEncoder.encode(fname, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".XLS");
response.setContentType("application/msexcel");
ComplexDataExcelWrite.getInstance().creatExcel("标题", title, resultItems, os);
out.clear();
out = pageContext.pushBody();
//ComplexDataExcelWrite sw=new ComplexDataExcelWrite();
// sw.creatExcel("标题", title, resultItems, os);
%>
<html>
<head>
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="0">
</head>
</html>