public class CreateSimpleExcelToDisk {
/**
* @功能:手工构建一个简单格式的Excel
*/
private static List<News> getNews() throws Exception
{
List<News> data = new ArrayList<News>();
NewsDao dao = new NewsDao();
ArrayList<News> list=dao.queryAll();
SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
for(News news:list){
// News user1 = new News(news.getNewsId(),news.getTypeId(), news.getTitle(), news.getPublishTime(), news.getBody(),news.getTag(),news.getAuthor(),news.getClicks(),news.getImgUrl());
data.add(new News(news.getNewsId(),news.getTypeId(), news.getTitle(), news.getPublishTime(), news.getBody(),news.getTag(),news.getAuthor(),news.getClicks(),news.getImgUrl()));
}
return list;
}
public void ExpExcel(OutputStream out) throws Exception{
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("Sheet1");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("newsid");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("typeid");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("title");
HSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居左格式
cell.setCellStyle(style2);
cell = row.createCell((short) 3);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("publishtime");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("body");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("tag");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("author");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("clicks");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
// 设置编码格式主要是解决导出中文等的乱码
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue("imgUrl");
cell.setCellStyle(style);
// 第五步,写入实体数据 实际应用中这些数据从数据库得到,
List list = CreateSimpleExcelToDisk.getNews();
for (int i = 0; i < list.size(); i++)
{
row = sheet.createRow((int) i + 1);
News news = (News) list.get(i);
// 第四步,创建单元格,并设置值
cell = row.createCell((short) 0);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getNewsId());
cell.setCellStyle(style);
cell = row.createCell((short) 1);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getTypeId());
cell.setCellStyle(style);
cell = row.createCell((short) 2);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getTitle());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getPublishTime());
cell.setCellStyle(style);
cell = row.createCell((short) 4);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getBody());
cell.setCellStyle(style);
cell = row.createCell((short) 5);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getTag());
cell.setCellStyle(style);
cell = row.createCell((short) 6);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getAuthor());
cell.setCellStyle(style);
cell = row.createCell((short) 7);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getClicks());
cell.setCellStyle(style);
cell = row.createCell((short) 8);
// 设置编码格式
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(news.getImgUrl());
cell.setCellStyle(style);
/*row.createCell((short) 0).setCellValue( news.getNewsId());
row.createCell((short) 1).setCellValue(news.getTypeId());
row.createCell((short) 2).setCellValue(news.getTitle());
// cell = row.createCell((short) 3);
// cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(news.getPublishTime()));
row.createCell((short) 3).setCellValue(news.getPublishTime());
row.createCell((short) 4).setCellValue(news.getBody());
row.createCell((short) 5).setCellValue(news.getTag());
row.createCell((short) 6).setCellValue(news.getAuthor());
row.createCell((short) 7).setCellValue(news.getClicks());
row.createCell((short) 8).setCellValue(news.getImgUrl());*/
}
// 第六步,将文件存到指定位置
try
{
// String outputFile = "E:/news.xls";
// FileOutputStream fout = new FileOutputStream(outputFile);//"E:/news.xls"
System.out.println("list="+list);
wb.write(out);
out.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
用servlet实现下载:此代码可以套用
public class ExpExcelServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String fileName = "news.et";
response.setContentType("octets/stream");
response.addHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName, "utf-8"));
try {
NewsDao dao = new NewsDao();
ArrayList<News> list = dao.queryAll();
CreateSimpleExcelToDisk ce=new CreateSimpleExcelToDisk();
OutputStream out = response.getOutputStream();
ce.ExpExcel(out);
out.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("et导出成功!");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request,response);
}
}
网上的资料
可以采用:
1、RequestDispatcher的方式进行;
2、采用文件流输出的方式下载。
1、采用RequestDispatcher的方式进行
jsp页面中添加如下代码:
<%
response.setContentType("application/x-download");//设置为下载application/x-download
String filedownload = "/要下载的文件名";//即将下载的文件的相对路径
String filedisplay = "最终要显示给用户的保存文件名";//下载文件时显示的文件保存名称
filenamedisplay = URLEncoder.encode(filedisplay,"UTF-8");
response.addHeader("Content-Disposition","attachment;filename=" + filedisplay);
try
{
RequestDispatcher dis = application.getRequestDispatcher(filedownload);
if(dis!= null)
{
dis.forward(request,response);
}
response.flushBuffer();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
}
%>
2、采用文件流输出的方式下载
<%@page language="java" contentType="application/x-msdownload" pageEncoding="gb2312"%><%
//关于文件下载时采用文件流输出的方式处理:
//加上response.reset(),并且所有的%>后面不要换行,包括最后一个;
response.reset();//可以加也可以不加
response.setContentType("application/x-download");
String filedownload = "想办法找到要提供下载的文件的物理路径+文件名";
String filedisplay = "给用户提供的下载文件名";
filedisplay = URLEncoder.encode(filedisplay,"UTF-8");
response.addHeader("Content-Disposition","attachment;filename=" + filedisplay);
OutputStream outp = null;
FileInputStream in = null;
try
{
outp = response.getOutputStream();
in = new FileInputStream(filenamedownload);
byte[] b = new byte[1024];
int i = 0;
while((i = in.read(b)) > 0)
{
outp.write(b, 0, i);
}
outp.flush();
}
catch(Exception e)
{
System.out.println("Error!");
e.printStackTrace();
}
finally
{
if(in != null)
{
in.close();
in = null;
}
if(outp != null)
{
outp.close();
outp = null;
}
}
%>
在wsad里面写JSP文件下载,总是出现这个异常,getOutputStream() has already been called for this response,输出流已经被调用了.
上网查半天终于明白一点,JSP早下载文件的时候用到了OutputStream,而在Application Server在处理编译jsp时对于%>和<%之间的内容一般是原样输出,而且默认是PrintWriter.