由于数据量太大,页面无法显示全部,需要导出一个excel文件,查阅了一些资料,写了个工具类,方便以后使用,与大家共享,有啥不足的希望指出,一起学习,专业报表二十年。哈哈
- 下面是工具类代码,仅供参考:
package com.data.utils;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import sun.misc.BASE64Encoder;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
*
* @Title: ExcelUtil.java
* @author you.xu
* @version 1.0
*/
public class ExcelUtil {
/**
* 创建Excel文件流
*
* @return
*/
public static WritableWorkbook cWorkbook(OutputStream os) {
try {
return Workbook.createWorkbook(os);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
/**
* 创建工作表
*
* @param b
* @param idx
* 工作表索引,从1开始
* @param name
* 工作表显示名称
* @return
*/
public static WritableSheet cSheet(WritableWorkbook b, int idx, String name) {
return b.createSheet(name, idx - 1);
}
/**
* 创建单元格
*
* @param a
* 行
* @param b
* 列
* @param value
* 值
* @return
*/
public static Label cLabel(int a, int b, String value) {
return new Label(b - 1, a - 1, value);
}
/**
* 添加Label到Sheet
*
* @param l
* label
* @param s
* sheet
*/
public static void aLabelToSheet(Label l, WritableSheet s) {
try {
s.addCell(l);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置工作表列宽
*
* @param s
* @param idx
* 索引从1开始
* @param width
* 字符宽度
*/
public static void sColumnSize(WritableSheet s, int idx, int width) {
s.setColumnView(idx - 1, width);
}
/**
* 设置工作表高度
*
* @param s
* @param idx
* 索引从1开始
* @param height
* 字符高度
*/
public static void sRowSize(WritableSheet s, int idx, int height) {
try {
s.setRowView(idx - 1, height * 20);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置单元格合并
*
* @param a
* 起始单元格行
* @param b
* 起始单元格列
* @param c
* 终止单元格行
* @param d
* 终止单元格列
*/
public static void sMerge(WritableSheet s, int a, int b, int c, int d) {
try {
s.mergeCells(b - 1, a - 1, d - 1, c - 1);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置单元格样式
*
* @param l
* @param fontName
* 字体:字符串,如"黑体"
* @param fontSize
* 字号:数字,如24
* @param colour
* 字体颜色:Colour常量
* @param bgColour
* 单元格背景色:Colour常量
* @param align
* 对齐模式:0-左;1-中;2-右
* @param borderStyle
* 边框线样式:字符串,如0000代表上下左右都不要边框,
* 如1100代表上下要边框,如0011代表左右要边框,如果0220代表左边和下边要粗边框
*/
public static void sLabelStyle(Label l, String fontName, int fontSize,
Colour colour, Colour bgColour, int align, String borderStyle) {
try {
if (colour == null)
colour = Colour.BLACK;
if (bgColour == null)
bgColour = Colour.WHITE;
WritableFont wf = new WritableFont(
// 设置字体
WritableFont.createFont(fontName),
// 设置字号
fontSize,
// 设置加粗
WritableFont.NO_BOLD,
// 设置倾斜
false,
// 设置下划线
UnderlineStyle.NO_UNDERLINE,
// 设置字体颜色
colour);
WritableCellFormat wcf = new WritableCellFormat(wf);
// 设置背景色
wcf.setBackground(bgColour);
// 设置对其方式
wcf.setAlignment(Alignment.CENTRE);
// 设置边框
if (borderStyle != null && borderStyle.length() == 4) {
char[] bs = borderStyle.toCharArray();
if (bs[0] == '1') {
wcf.setBorder(Border.TOP, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
} else if (bs[0] == '2') {
wcf.setBorder(Border.TOP, BorderLineStyle.MEDIUM,
jxl.format.Colour.BLACK);
}
if (bs[1] == '1') {
wcf.setBorder(Border.BOTTOM, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
} else if (bs[1] == '2') {
wcf.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM,
jxl.format.Colour.BLACK);
}
if (bs[2] == '1') {
wcf.setBorder(Border.LEFT, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
} else if (bs[2] == '2') {
wcf.setBorder(Border.LEFT, BorderLineStyle.MEDIUM,
jxl.format.Colour.BLACK);
}
if (bs[3] == '1') {
wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN,
jxl.format.Colour.BLACK);
} else if (bs[3] == '2') {
wcf.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM,
jxl.format.Colour.BLACK);
}
}
l.setCellFormat(wcf);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void downExcel(String fileName, HttpServletRequest request,
HttpServletResponse response, ByteArrayOutputStream bos)
throws Exception {
// 当前excel文件的内容已经写入到流os对象中,该流是一个输出流
// 表现层需要的是输入流
// 输出流转输入流
String mimeType = request.getServletContext().getMimeType(fileName);
response.setContentType(mimeType);
String agent = request.getHeader("user-agent");
if (agent.contains("MSIE")) {
// IE浏览器
fileName = URLEncoder.encode(fileName, "utf-8");
} else if (agent.contains("Firefox")) {
// 火狐浏览器
BASE64Encoder base64Encoder = new BASE64Encoder();
fileName = "=?utf-8?B?"
+ base64Encoder.encode(fileName.getBytes("utf-8")) + "?=";
} else {
// 其它浏览器
fileName = URLEncoder.encode(fileName, "utf-8");
}
response.setContentType("application/msexcel;charset=GBK");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=\""
+ new String(fileName.getBytes(), "ISO8859-1") + "\"");
ByteArrayInputStream downloadExcelStreamn = new ByteArrayInputStream(
bos.toByteArray());
OutputStream os = response.getOutputStream(); // 将要下载的文件内容通过输出流写回到浏览器端.
int len = -1;
byte[] b = new byte[1024 * 100];
while ((len = downloadExcelStreamn.read(b)) != -1) {
os.write(b, 0, len);
os.flush();
}
os.close();
downloadExcelStreamn.close();
}
}
现在是我项目中目前所用到的其中的部分工具,代码如下:
//创建输出流
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//创建Excel文件流
WritableWorkbook w = Workbook.createWorkbook(bos);
//创建工作表
WritableSheet s = w.createSheet("工作表名称", 0);
//设置工作表列宽
ExcelUtil.sColumnSize(s, 1, 25);
ExcelUtil.sColumnSize(s, 2, 35);
ExcelUtil.sColumnSize(s, 3, 35);
ExcelUtil.sColumnSize(s, 4, 25);
//创建单元格
Label lab11 = ExcelUtil.cLabel(1, 1, "单元格值");
//设置单元格样式,具体看工具类
ExcelUtil.sLabelStyle(lab11, "黑体", 18, Colour.BLACK,
Colour.LIGHT_BLUE, 1, "2020");
//将单元格加入到工作表中
ExcelUtil.aLabelToSheet(lab11, s);
//同上
Label lab12 = ExcelUtil.cLabel(1, 2, "单元格值");
ExcelUtil.sLabelStyle(lab12, "黑体", 18, Colour.BLACK,
Colour.LIGHT_BLUE, 1, "2020");
ExcelUtil.aLabelToSheet(lab12, s);
//遍历list集合,将数据插入到单元格中
for (int j = 0; j < list.size(); j++) {
Label lab_data_1 = ExcelUtil.cLabel(j + 2, 1, radioTypeList
.get(j).getCode());
ExcelUtil.sLabelStyle(lab_data_1, "宋体", 14, Colour.BLACK,
Colour.WHITE, 1, "0110");
ExcelUtil.aLabelToSheet(lab_data_1, s);
Label lab_data_2 = ExcelUtil.cLabel(j + 2, 2, radioTypeList
.get(j).getM().toString());
ExcelUtil.sLabelStyle(lab_data_2, "宋体", 14, Colour.BLACK,
Colour.WHITE, 1, "0110");
ExcelUtil.aLabelToSheet(lab_data_2, s);
Label lab_data_3 = ExcelUtil.cLabel(j + 2, 3, radioTypeList
.get(j).getN().toString());
ExcelUtil.sLabelStyle(lab_data_3, "宋体", 14, Colour.BLACK,
Colour.WHITE, 1, "0110");
ExcelUtil.aLabelToSheet(lab_data_3, s);
Label lab_data_4 = ExcelUtil.cLabel(j + 2, 4, radioTypeList
.get(j).getValue());
ExcelUtil.sLabelStyle(lab_data_4, "宋体", 14, Colour.BLACK,
Colour.WHITE, 1, "0110");
ExcelUtil.aLabelToSheet(lab_data_4, s);
}
//写流,关流
w.write();
w.close();
String fileName = "单元格名称.xls";
ExcelUtil.downExcel(fileName, request, response, bos);
//通过response返回到前台,无需返回值
相关资料可以查看http://download.csdn.net/detail/super_man_x/9366341 包含jxl源码,和两个文档。