需要用到的jxl.jar包
package com.estar.company.util.excel;
public class DownExcelConstants
{
public static final String DEFAULT_TITLE_NAME = "珠海人力资源网导出表格";
public static final int SHEET_MAX = 65536;
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();
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;
}
public static HashMap cresateFieldNameMapForTable(ResultSet rs,String[] fieldNameArray)
throws Exception {
HashMap colMap = new HashMap();
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;
public class ExcelDownload
{
public static final int CURRENT_SHEET_MAX = DownExcelConstants.SHEET_MAX;
private WritableCellFormat contentLeftFormat = null;
private String fileName = "";
private String titleName = DownExcelConstants.DEFAULT_TITLE_NAME;
private int row = 0;
private List excelList = new ArrayList();
private int mapsize = 0;
protected String[] colTitleArray = null;
protected String[] fieldNameArray = null;
public ExcelDownload()
{
setFileName(createFileName());
}
public ExcelDownload(String titleName)
{
this.setTitleName(titleName);
setFileName(createFileName());
}
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);
}
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);
}
}
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;
}
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);
}
}
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();
}
private void deleteFile(String fileName)
{
File file = new File(fileName);
file.delete();
}
public void resetRow()
{
this.row = 0;
}
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();
}
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;
}
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;
}
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;
}
}
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异常");
}
}
private List getExcelList()
{
return excelList;
}
public void setExcelList(List excelList)
{
this.excelList = excelList;
}
public String getTitleName()
{
return titleName;
}
public void setTitleName(String titleName)
{
this.titleName = titleName;
}
public String getFileName()
{
return fileName;
}
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 {
public PersonInfoDownLoad() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
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();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
public void init() throws ServletException {
// Put your code here
}
}