需要用到的jxl.jar包
package com.estar.company.util.excel;
/**
* <p>
* Title: 下载EXCEL常量定义类
* </p>
* <p>
* Description: 本类主要用于定义下载业务的常量定义类等。
* </p>
*
* @author biran
* @version 1.0.0.0
*/
public class DownExcelConstants
{
/** 默认Excel表格标题头 */
public static final String DEFAULT_TITLE_NAME = "珠海人力资源网导出表格";
/** macrosoft excel 中，一个sheet的最大行数 */
public static final int SHEET_MAX = 65536;
/** Excel表格列宽常量 */
public static final int DEFAULT_COLUMN_WIDTH = 25;
}
package com.estar.company.util.excel;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.LinkedHashMap;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class DownExl {
public static void down(HttpServletRequest request,HttpServletResponse response,String[] sql,LinkedHashMap map,String fileShowName){
OutputStream out = null;
ExcelDownload down = new ExcelDownload();
InputStream br = null;
String fileSize = "0";
try {
byte[] rdbyte= null;
try {
rdbyte = down.getReportBuf(sql,map);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
InputStream inputs=new ByteArrayInputStream(rdbyte);
br = new BufferedInputStream(inputs);
byte[] buf = new byte[1024];
int len = 0;
System.out.println("------len-------------->>>"+len);
fileSize=String.valueOf(len);
response.reset();
response.setContentType("text/plan; charset=GB2312");
response.setHeader("Content-Disposition", "attachment; filename="+fileShowName);
response.setHeader("Content-Length", fileSize);
out = response.getOutputStream();
// //out.flush();
// while((len=out.read(buf))!=-1)
// out.write(buf,0,len);
while ((len = br.read(buf))!=-1)
out.write(buf, 0, len);
out.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null)
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (br != null)
try {
br.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package com.estar.company.util.excel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
//import com.kx.db.DBConnection;
//import com.kx.pq.staff.StaffVO;
import com.estar.database.DataSourceWeblogic;
import com.estar.person.vo.PersonInfo;
public class ExcelDAO
{
private static PersonInfo vo = new PersonInfo();
/**
* 执行SQL查询，查询结果每条结果以map形式放入List中
* @param sql String 提取数据的SQL
* @param paras Object 动态参数数组
* @param fieldNameArray String[] 数据库字段名数组
* @return List类型 返回包含记录的list
* @throws DAOException
*/
public static List excute(String[] sql, String[] fieldNameArray)
{
List list = new ArrayList();
ResultSet rs = null;
Connection conn = null;
rs = null;
try
{
conn=DataSourceWeblogic.getConnection();
for(int i=0;i<sql.length;i++){
String temp=sql[i].split(",")[0];
rs = conn.createStatement().executeQuery("select PERSONNAME,PERSONSEX,to_char(BIRTHDAY,'yyyy-mm-dd')as BIRTHDAY,CONTACTADDR,IDCARD,TELEPHONE,EMAIL,GRADSCHOOLE,SPECIALTYDETAIL,GRADUATEYEAR,WORKLENGTH from personinfo where personid="+Integer.parseInt(temp));
if (rs != null && rs.next())
{
HashMap map=cresateFieldNameMapForTable(rs, fieldNameArray);
list.add(map);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try {
rs.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return list;
}
/**
* 获取ResultSet对象中列的类型和属性信息的对象，
* 一个字段对应一个键-值映射关系(以调用者定义的字段名为key,key所对应的表中字段值为value),最终保存到map中
* @param rs ResultSet类型
* @param fieldNameArray Object数组类型
* @return
* @throws Exception
*/
public static HashMap cresateFieldNameMapForTable(ResultSet rs,String[] fieldNameArray)
throws Exception {
HashMap colMap = new HashMap();
/* ResultSetMetaData rsMeta = rs.getMetaData();
// 获得当前记录的列数
int fieldCount = rsMeta.getColumnCount();*/
for (int i = 0; i <fieldNameArray.length; i++) {
// 获取数据表字段名
// String key = rsMeta.getColumnName(i);
String key = fieldNameArray[i].toString();
String value= rs.getString(key);
if (value == null) {
value="";
}
// key=equalsIgnoreCase(key,fieldNameArray);
colMap.put(key, value);
}
return colMap;
}
}
package com.estar.company.util.excel;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import jxl.Workbook;
import jxl.format.Alignment;
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.WriteException;
/**
* <p>
* Title: 创建EXCEL下载文件类
* </p>
* <p>
* Description: 用jxl第三方组件生成EXCEL文件供下载使用
* </p>
* <p>
* Copyright: Copyright (c) 2007
* </p>
*
* @author biran
* @version 1.0.0.0
*/
public class ExcelDownload
{
/**
* 当前使用的sheet 的最大行数CURRENT_SHEET_MAX>=4，并且为偶数
*/
public static final int CURRENT_SHEET_MAX = DownExcelConstants.SHEET_MAX;
/**
* 设置Excel文件表格内容对齐方式
*/
private WritableCellFormat contentLeftFormat = null;
/**
* 初始化默认的Excel文件名称
*/
private String fileName = "";
/**
* 初始化默认的Excel列标题文件名称
*/
private String titleName = DownExcelConstants.DEFAULT_TITLE_NAME;
/**
* 记录Excel中的行数
*/
private int row = 0;
/**
* 满足条件的记录结果集
*/
private List excelList = new ArrayList();
/**
* map的长度
*/
private int mapsize = 0;
/**
* 保存Excel表格的各个列标题名称
*/
protected String[] colTitleArray = null;
/**
* 保存Excel表格中每列要获取的
*/
protected String[] fieldNameArray = null;
/**
* 默认构造方法
* 以默认的标题名称和当前时间生成临时Excel文件名
*/
public ExcelDownload()
{
setFileName(createFileName());
}
/**
* @param titleName 设置自定义Excel首行标题名称，并以此标题和当前时间生成临时Excel文件名
*/
public ExcelDownload(String titleName)
{
this.setTitleName(titleName);
setFileName(createFileName());
}
/**
* 创建Excel之前必要的操作，初始化Excel对齐方式，解析map,获取excel表格内容集合list
*
* @param sql String 查询数据的sql语句
* @param paras Object数组 与sql对应的参数值
* @param map 保存Excel表格的标题-表字段映射 格式为key(Excel列标题)-value(所要获取的字段名，一般为VO的属性名称)
* @throws WriteException
*/
private void init(String[] sql, LinkedHashMap map)
throws WriteException
{
// 设置Excel文件表格内容不定长数据对齐方式为左对齐
contentLeftFormat = new WritableCellFormat();
contentLeftFormat.setAlignment(Alignment.LEFT);
// 解析map
this.mapSplit(map);
this.excelList = ExcelDAO.excute(sql, this.fieldNameArray);
}
/**
* 根据map(报表列格式)和excelList(报表展示内容)，创建EXCEL报表文件
*
* @param sql String 查询数据的sql语句
* @param paras Object数组 与sql对应的参数值
* @param map LinkedHashMap类型 格式为key(Excel列标题)-value(所要获取的字段名，一般为VO的属性名称)
* 保存Excel表格的标题-表字段映射
* 为了保证获取数据按插入时的顺序,必须是LinkedHashMap类型,因LinkedHashMap获取数据时不做排序处理
* @return FileInputStream类型输出流
* @throws Exception
*/
public ByteArrayOutputStream getReportOutput(String[] sql, LinkedHashMap map) throws Exception
{
// 创建Excel文件名
String excelFileName = "";
// 文件下载流
InputStream input = null;
try
{
init(sql, map);
// 创建Excel文件名
excelFileName = createExcel(this.excelList);
// 创建输出流
input = new FileInputStream(excelFileName);
// 缓冲区的有效内容
ByteArrayOutputStream out = this.getOutput(input);
return out;
}
catch (Exception e)
{
throw new Exception("创建EXCEL报表文件失败");
}
finally
{
inputClose(excelFileName, input);
}
}
/**
*
* 从一个输入流中根据一定的编码方式读取出二进制数据
*
* @param in InputStream，输入流
*
* @return byte[] 二进制数据
* @throws IOException
*
* @throws IOException
*
*/
private ByteArrayOutputStream getOutput(InputStream input)
throws IOException
{
ByteArrayOutputStream out = null;
out = new ByteArrayOutputStream();
byte[] buf = new byte[1024];
int count = -1;
while (true)
{
count = input.read(buf);
if (count == -1)
{
break;
}
out.write(buf, 0, count);
}
out.flush();
return out;
}
/**
* 根据map(报表列格式)和excelList(报表展示内容)，创建EXCEL报表文件
*
* @param sql String 查询数据的sql语句
* @param paras Object数组 与sql对应的参数值
* @param map LinkedHashMap类型 格式为key(Excel列标题)-value(所要获取的字段名，一般为VO的属性名称)
* 保存Excel表格的标题-表字段映射
* 为了保证获取数据按插入时的顺序,必须是LinkedHashMap类型,因LinkedHashMap获取数据时不做排序处理
* @return byte[] 返回一个字节数组，数组为输出流缓冲区的有效内容。
* @throws Exception
*/
public byte[] getReportBuf(String[] sql, LinkedHashMap map) throws Exception
{
// 创建Excel文件名
String excelFileName = "";
// 文件下载流
FileInputStream input = null;
try
{
init(sql, map);
// 创建Excel文件名
excelFileName = createExcel(this.excelList);
// 输出流定向到请求浏览器
input = new FileInputStream(excelFileName);
// 获取一个包含缓冲区有效内容的数组
return this.getOutput(input).toByteArray();
}
catch (Exception e)
{
throw new Exception("创建EXCEL报表文件失败");
}
finally
{
inputClose(excelFileName, input);
}
}
/**
*
* 创建Excel临时报表文件，使用完将文件在服务器上删除
*
* @param excelList List 列表类型，存放具体报表的记录，一条记录为一个map
* @return String 返回生成的临时报表文件在系统的位置
* @throws IOException
* @throws WriteException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
private String createExcel(List excelList) throws IOException,
WriteException, IllegalAccessException,
InvocationTargetException, NoSuchMethodException
{
// 创建一个临时文件
File excelFile = File.createTempFile(getFileName(), ".xls");
// 创建Excel文件
WritableWorkbook wwb = Workbook.createWorkbook(excelFile);
// 创建Excel文件的一个sheet
WritableSheet ws = wwb.createSheet("第1页", 0);
// 设置Excel文件标题字体格式、颜色、对齐方式
WritableFont titleFont = new WritableFont(WritableFont.TIMES,
14,
WritableFont.BOLD); // 粗体14PT
titleFont.setColour(Colour.RED); // 字体颜色红色
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
titleFormat.setAlignment(Alignment.CENTRE); // 对齐方式居中对齐
// 设置Excel文件表格列标题对齐方式
WritableCellFormat colTitleFormat = new WritableCellFormat();
colTitleFormat.setAlignment(Alignment.CENTRE);
colTitleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 设置Excel文件表格内容对齐方式
WritableCellFormat contentFormat = new WritableCellFormat();
contentFormat.setAlignment(Alignment.CENTRE);
// 表格第一行加入报表名称
Label titleLable = new Label(0, 0, getTitleName(), titleFormat);
ws.addCell(titleLable);
row++; // 行数加1，指向下一行
// 根据colTitleArray创建具体表格列标题，返回报表列数
int columnNum = createTitle(ws, colTitleFormat);
ws.mergeCells(0, 0, columnNum - 1, 0); // jxl方法，实现创建单元格功能
// sheet的页数
int sheetNum = 1;
// 如果到达最大行，创建新页，并初始化表格页数，表格标题，表格样式
int listSize = excelList.size();
for (int i = 0; i < listSize; i++)
{
if ((row % CURRENT_SHEET_MAX) == 0)
{
sheetNum++;
row = 0;
ws = wwb.createSheet("第" + sheetNum + "页", sheetNum - 1);
titleLable = new Label(0, 0, getTitleName(), titleFormat);
ws.addCell(titleLable);
row++;
createTitle(ws, colTitleFormat);
ws.mergeCells(0, 0, columnNum - 1, 0);
}
// 读取列表中一个报表数据类数据
HashMap colmap = ( HashMap ) excelList.get(i);
// 将报表中一行的实际数据存入Excel文件对应表格中
createContent(ws, contentFormat, colmap);
}
wwb.write();
wwb.close();
return excelFile.getPath();
}
/**
* 删除EXCEL临时文件的方法
*
* @param fileName 服务器创建的临时文件
*/
private void deleteFile(String fileName)
{
File file = new File(fileName);
file.delete();
}
/**
* 将表格行数复位为首行
*/
public void resetRow()
{
this.row = 0;
}
/**
* 根据报表首行标题名称和系统时间，产生一个Excel文件名
*
* @return 标题名称_系统时间(年_月_日_小时分钟毫秒)组合的字符串
*/
private String createFileName()
{
// 定义日期和时间格式的模式
String dateFormat = "yyyy_MM_dd_hhmmsss";
Date date = new Date();
// 设置日期和时间格式的模式
SimpleDateFormat simDate = new SimpleDateFormat(dateFormat);
String path = simDate.format(date);
StringBuffer strBuff = new StringBuffer();
strBuff.append(getTitleName());
strBuff.append(path);
return strBuff.toString();
}
/**
* 创建包括标题的列，(标题可能占用两行，只能使用全局的row)
*
* @param ws WritableSheet
* @param colTitleFormat WritableCellFormat
* @throws WriteException
* @return int 返回写的列数
*/
private int createTitle(WritableSheet ws, WritableCellFormat colTitleFormat)
throws WriteException
{
int col = 0;
// 获取表格列取值name集合的长度
int len = this.colTitleArray.length;
for (int i = 0; i < len; i++)
{
ws.addCell(new Label(col++,
row,
( String ) this.colTitleArray[i],
colTitleFormat));
}
row++;
// 设置宽度
for (int i = 0; i < col; i++)
{
ws.setColumnView(i, DownExcelConstants.DEFAULT_COLUMN_WIDTH);
}
return col;
}
/**
* 打印表格，即设置表格内容， 每增加一行就更改row的值
*
* @param ws WritableSheet
* @param contentFormat Excel内容格式
* @param colMap 为一条记录
* @return 返回插入行数
* @throws WriteException
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws NoSuchMethodException
*/
private int createContent(WritableSheet ws,
WritableCellFormat contentFormat, Map colMap)
throws WriteException, IllegalAccessException,
InvocationTargetException, NoSuchMethodException
{
int col = 0;
// 获取表格列取值name集合的长度
int len = this.fieldNameArray.length;
// 循环获取每列对应VO的属性值
for (int i = 0; i < len; i++)
{
// 插入一行中的一列
ws.addCell(new Label(col++,
row,
( String ) colMap.get(this.fieldNameArray[i]),
contentFormat));
}
row++;
return col;
}
/**
* 分解map中存储的键-值映射，将键和值分别保存到对应的数组中 因要求map中获取的数据要与之前插入的顺序相同，故使用LinkedHashMap
* key 保存Excel表格的各个列标题名称 value 保存Excel表格中每列要获取的数据库字段值所对应的名字
*
* @param map LinkedHashMap类型
* @throws NullPointerException
*
*/
private void mapSplit(LinkedHashMap map) throws NullPointerException
{
mapsize = map.size();
// 判断map是否为空
if (map.isEmpty() || mapsize == 0)
{
throw new NullPointerException("map is null or mapsize equal null ");
}
// 定义 excel列标题和获取VO字段名的 数组长度
colTitleArray = new String[mapsize];
fieldNameArray = new String[mapsize];
Object[] keyValuePairs2 = map.entrySet().toArray();
for (int i = mapsize - 1; i > -1; i--)
{
Map.Entry entry = ( Map.Entry ) keyValuePairs2[i];
Object key = entry.getKey();
Object value = entry.getValue();
// 判断map中键、值均不能为空数据
if (key == null || "".equals(key) || value == null
|| "".equals(value))
{
throw new NullPointerException("map中未包含键-值映射关系");
}
colTitleArray[i] = ( String ) key;
fieldNameArray[i] = ( String ) value;
}
}
/**
* 关闭文件流及删除文件
*
* @param excelFileName
* @param input
* @throws IOException
*/
private void inputClose(String excelFileName, InputStream input) throws IOException
{
try
{
if (null != input)
{
input.close();
}
// 删除临时文件
File file = new File(excelFileName);
if (file.exists())
{
deleteFile(excelFileName);
}
}
catch (IOException ex)
{
throw new IOException("关闭EXCEL文件输入流,IO异常");
}
}
/**
* @return 返回满足条件的记录结果集
*/
private List getExcelList()
{
return excelList;
}
/**
* 设置满足条件的记录结果集，即EXCEL表格行内容 List中的保存map,一个map为一条记录
*
* @param excelList List类型
*/
public void setExcelList(List excelList)
{
this.excelList = excelList;
}
/**
* 表格第一行，
*
* @return String 获取报表首行标题名称
*/
public String getTitleName()
{
return titleName;
}
/**
* 设置表格第一行，报表名称， 根据不同的报表，返回不同的报表名称(初始化或自定义的titleName)
*
* @param titleName
*/
public void setTitleName(String titleName)
{
this.titleName = titleName;
}
/**
* @return 返回Excel文件名
*/
public String getFileName()
{
return fileName;
}
/**
* @param fileName 设置Excel文件名
*/
public void setFileName(String fileName)
{
this.fileName = fileName;
}
public static void main(String[] args) {
ExcelDownload down = new ExcelDownload();
String[] sql = null;
LinkedHashMap map = new LinkedHashMap();
map.put("代码类别", "Ucodetype");
//map.put("代码类别名称", "Ucodetypename");
//map.put("代码属性", "Ucodeproper");
//map.put("代码编号", "Ucodeid");
//map.put("代码名称", "Ucodename");
try {
down.getReportBuf(sql,map);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.estar.company.util.excel;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.estar.company.util.excel.ExcelDownload;
public class PersonInfoDownLoad extends HttpServlet {
/**
* Constructor of the object.
*/
public PersonInfoDownLoad() {
super();
}
/**
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/**
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String companyId = (String)request.getSession().getAttribute(com.estar.company.util.Symbols.COMPANY_SESSION_ID);
if(companyId==null){
return;
}
//response.setContentType("text/html");
//response.setCharacterEncoding("utf-8");
String[] sql=request.getParameterValues("personid");
if(sql==null||sql.length<1){
response.setContentType("text/html; charset=GBK");
PrintWriter out=response.getWriter();
out.println("<script>alert('请选择求职者！');history.back(-1);</script>");
return;
}
//String sql = "select * from PERSONINFO where rownum<=10";
LinkedHashMap map = new LinkedHashMap();
map.put("姓名", "PERSONNAME");
map.put("性别", "PERSONSEX");
map.put("出生时间", "BIRTHDAY");
map.put("身份证", "IDCARD");
map.put("电话", "TELEPHONE");
map.put("E-MAIL", "EMAIL");
map.put("联系地址", "CONTACTADDR");
map.put("毕业学校", "GRADSCHOOLE");
map.put("专业", "SPECIALTYDETAIL");
map.put("毕业时间", "GRADUATEYEAR");
map.put("工作年限", "WORKLENGTH");
downLoadFile(request, response,sql,map,"珠海人力资源网.xls");
}
private void downLoadFile(HttpServletRequest request, HttpServletResponse response,String[] sql,LinkedHashMap map,String fileShowName)
throws ServletException, IOException {
OutputStream out = null;
fileShowName = URLEncoder.encode(fileShowName, "utf-8");
InputStream br = null;
try {
ExcelDownload down = new ExcelDownload();
byte[] rdbyte=down.getReportBuf(sql, map);
InputStream inputs=new ByteArrayInputStream(rdbyte);
br = new BufferedInputStream(inputs);
byte[] buf = new byte[1024];
int len = rdbyte.length;
response.reset();
// response.setContentType("application/octet-stream");
response.setContentType("text/plan; charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename="+fileShowName);
out = response.getOutputStream();
out.flush();
while ((len = br.read(buf)) > 0)
out.write(buf, 0, len);
out.flush();
} catch (Exception e) {
//Logger.getLogger(this.getClass().toString()).error(e.getMessage());
//Logger.getLogger(this.getClass().toString()).info(e.getMessage(), e);
} finally {
if (out != null)
out.close();
if (br != null)
br.close();
}
}
/**
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
/**
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occure
*/
public void init() throws ServletException {
// Put your code here
}
}