poi.jar包
public void downExcel(HttpServletResponse response,Page<ShopApply> page)
throws Exception{
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("xxx");
sheet.setDefaultColumnWidth((short) 15);//表格宽度
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell=null;
//表头
String[] headers={"申请单类型","编号","所属公司","主题","跳转地址","提交人","提交时间","审核状态","审核人","审核时间","资讯状态"};
for(int i=0;i<headers.length;i++){
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
}
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
StringBuffer str=new StringBuffer();
str.append("SELECT s.`c_checkid`,s.c_checkuser,s.c_applytype,g.c_companyname,s.`c_beforetopic`,s.`c_aftertopic`,s.`c_beforeimage`,s.`c_afterimage`,s.`c_checkstatus`,s.`dt_checkdate`,s.`c_adduser`,s.`dt_adddate`,m.`c_manageid`,m.`c_forwardurl`,m.c_isdel FROM shop_check s ,shop_website_manage m ,`global_company` g WHERE s.c_type=6 AND s.`c_businessid`=m.`c_manageid` AND m.c_companyid = g.c_companyid");
Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
page=this.makingApplyService.getList(str.toString(), page);
List<Object> rs=(List<Object>) page.getQueryList();
for (int i = 0; i < rs.size(); i++)
{
Object obj=rs.get(i);
Map<String, String> objs=(Map<String, String>)obj;
row = sheet.createRow((int) i + 1);
// 第四步，创建单元格，并设置值
row.createCell((short) 0).setCellValue(String.valueOf(objs.get("c_applytype")).equals("0")?"新申请单":"修改类申请单");
row.createCell((short) 1).setCellValue(objs.get("c_manageid"));
row.createCell((short) 2).setCellValue(objs.get("c_companyname"));
row.createCell((short) 3).setCellValue(objs.get("c_aftertopic"));
row.createCell((short) 4).setCellValue(objs.get("c_forwardurl"));
row.createCell((short) 5).setCellValue(objs.get("c_adduser"));
row.createCell((short) 6).setCellValue(df.format(objs.get("dt_adddate"))==null?"":df.format(objs.get("dt_adddate")));
String checkstate="";
if(objs.get("c_checkstatus").equals("0")){
checkstate="待审核";
}else if(objs.get("c_checkstatus").equals("1")){
checkstate="审核通过";
}else if(objs.get("c_checkstatus").equals("2")){
checkstate="审核不通过";
}
row.createCell((short) 7).setCellValue(checkstate);
row.createCell((short) 8).setCellValue(objs.get("c_checkuser"));
row.createCell((short) 9).setCellValue(String.valueOf(objs.get("dt_checkdate")).length()>0?String.valueOf(objs.get("dt_checkdate")):null);
row.createCell((short) 10).setCellValue(String.valueOf(objs.get("c_isdel")).equals("1")?"N":"Y");
}
// 第六步，将文件存到指定位置
try
{
//导出的文件名
String fileName="photo_"+(new SimpleDateFormat("yyyyMMddHH").format(new Date()))+".xls";;
//导出
this.export(wb, this.getFilePath(), fileName);
// 下载Excel
this.download(response, this.getFilePath(), fileName);
}
catch (Exception e)
{
e.printStackTrace();
}
}
/*
* 导出
* @Param 路径，文件名
*
* */
public void export(HSSFWorkbook wb,String filePath,String fileName){
// 第六步，将文件存到指定位置
try
{
long start = System.currentTimeMillis();
//全路径
String filefull=filePath+"/"+fileName;
File floder=new File(filePath);
if (!floder.exists() && !floder.isDirectory())
{
floder.mkdir();
}
FileOutputStream fout = new FileOutputStream(filefull);
wb.write(fout);
fout.flush();
fout.close();
System.out.println("*******导出EXCEL结束*********");
long end = System.currentTimeMillis();
System.out.println("----完成该操作共用的时间是:"+(end-start)/1000);
}
catch (Exception e)
{
e.printStackTrace();
}
}
/*
* 下载
* @Param 路径，文件名
*
* */
public void download(HttpServletResponse response,String filePath , String fileName){
try
{
System.out.println("*******下载EXCEL开始*********");
FileInputStream fis = null;
OutputStream os = null;
//全路径
String filefull=filePath+"/"+fileName;
File file = new File(filefull);
if (!file.exists()) {
System.out.println("文件下载失败：文件或路径错误");
}
fis = new FileInputStream(file);
os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
response.setContentType("application/x-download");
byte[] mybyte = new byte[8192];
int len = 0;
while ((len = fis.read(mybyte)) != -1) {
os.write(mybyte, 0, len);
}
fis.close();
os.close();
System.out.println("*******下载EXCEL结束*********");
}
catch (Exception e)
{
e.printStackTrace();
}
}
*****************************不生成文件，按一定的格式放到流里*********************
public InputStream getTxtFile(){
setFileName("mobiles_"+(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()))+".txt");//设置导出的Excel名称
Shop shopNum=new Shop();
StringBuffer sf=new StringBuffer();
List datas=this.getService().find();
try{
String str="";
String enter = "\r\n";
for (int i = 0; i < datas.size(); i++) {
shopNum=(Shop) datas.get(i);
if(shopNum.getCMobile() !=null && shopNum.getCMobile() !="" && shopNum.getCMobile().length()>10)
{
str=shopNum.getCMobile() + enter;
sf.append(str);
}
}
}catch(Exception e){
return null;
}
byte[] txt=null;
try{
txt=sf.toString().getBytes("UTF-8");
}catch (Exception e){
txt=sf.toString().getBytes();
}
InputStream input=new ByteArrayInputStream(txt);//用字节数组构造一个输入流
return input;
}