POI将数据导入Excel，上传到服务器，并从客户端保存

mengxianhua

浏览: 31583 次
性别:
来自: 上海

最近访客更多访客>>

adminsusu

a1952377854

lsdc2004

woodding2008

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (36)

社区版块

存档分类

Excel SQL Cache J#DAO

<%@ page language="java" import="java.util.*,java.io.*" pageEncoding="gb2312"%>
<%@ page import="com.hzpcssys.dao.DBHelper"%>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>POI将数据导入Excel，上传到服务器，并从客户端保存</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">

</head>

<body>
<%
Map recordMap = null;
String key = "";
String value = "";

String sql = DBHelper.tostr(request.getParameter("sql").toString());
ArrayList recordList = (ArrayList)DBHelper.executeQuerySQL(sql);

try {
// 创建新的Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在excel中新建一个工作表，起名字为工作对象
HSSFSheet sheet = workbook.createSheet("工作对象");
for(int i = 0; i < recordList.size(); i++) {
// 创建第行
HSSFRow row = sheet.createRow(i);
// 结果集Map
recordMap = (Map)recordList.get(i);
// 遍历recordMap的键值
Iterator keyValuePairs = recordMap.entrySet().iterator();
for(int j = 0; j < recordMap.size(); j++) {
// 创建第列
HSSFCell cell = row.createCell((short)j);
// 定义单元格为字符串类型
cell.setEncoding(HSSFCell.ENCODING_UTF_16);

Map.Entry entry = (Map.Entry) keyValuePairs.next();
key = entry.getKey()==null?"":entry.getKey().toString();
value = entry.getValue()==null?"":entry.getValue().toString();
cell.setCellValue(value);
}
}

// 文件存放位置保存于服务器
String filePath = application.getRealPath("\\temp")+"\\recordToExcel.xls";
// 新建一输出流
FileOutputStream fout = new FileOutputStream(filePath);
// 存盘
workbook.write(fout);

// 清空缓冲区
fout.flush();
// 结束关闭
fout.close();
// 文件下载路径
String fileDownloadPath = request.getRequestURL().substring(0, request.getRequestURL().lastIndexOf("/"))
+ "/temp/recordToExcel.xls";
// 保存到本地文件名
String saveFileName = "recordToExcel.xls";

// 以下主要实现 Excel文件下载
// 设置响应头和下载保存的文件名
response.reset();
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename=\"" + saveFileName + "\"");

String temp = application.getRealPath("/temp/recordToExcel.xls");
FileInputStream fis = new FileInputStream(temp);

// 写出流信息
int i = 0;
while ((i=fis.read()) != -1) {
out.write(i);
}
fis.close();
out.flush();
out.close();

} catch(IOException e){
e.printStackTrace();
}
%>
</body>
</html>