java实现把数据库数据导出成word文件实现文件下载功能

迷南。 2022-06-16 07:25 710阅读 0赞

一 html代码：（注必须用<a>标签）  
<a  id="download" href="/visit/out/download?visitId=$\{page.visitId\}&peopleVisitId=$\{page.peopleId\}"   
class="btn btn-success"  target="\_parent" style="margin-top: 5px;">下载报告</a>

截图：

![Center][]

二  后台controller方法：

1、

/\*\*  
       \* 下载pc端提交的外访报告  
       \*   
       \* @param  companyCode  
       \* @author qixin  
       \* @return list  
       \*/    
  @RequestMapping("/out/download")  
  public ResponseEntity<byte\[\]> downloadTask(String peopleVisitId,String visitId,Model model,HttpServletRequest request,HttpSession session)\{  
     
   String userId=null;

//判断用户  
  Object sessions = session.getAttribute("user");  
  if (sessions == null) \{  
 return null;  
  \}  
  if (User.class.equals(sessions.getClass())) \{  
 User user = (User) sessions;  
             userId=Integer.toString(user.getId());  
  \}  
  //获取外访报告所有内容  
  PeopleVisit task=outVisitServiceimpl.getPeopleAllInfo(visitId,peopleVisitId);//获取你想要的数据  
    
  //生成文件名称  
  String fileName=task.getDdTitle()+DateUtil.formatDate(new Date())+".zip";//生成文件名称  
  //生成下载文件  
  TaskDownLoad download=new TaskDownLoad(task,userId);//此方法最重要，生成下载文件  
  byte\[\] taskFile=download.createZip(); //生成zip压缩包  
  HttpHeaders headers = new HttpHeaders();  
  //下载  
  if(taskFile!=null)\{  
  headers.setContentLength(taskFile.length);  
  \}  
  headers.setContentType(MediaType.APPLICATION\_OCTET\_STREAM);  
  setFileName(request, headers, fileName);//解决下载文件名中文乱码的浏览器兼容问题  
  //下载文件  
  return new ResponseEntity<byte\[\]>(taskFile, headers, HttpStatus.OK);  
  \}

在controller里写

/\*\*  
\*   
\* @Description:解决下载文件名中文乱码的浏览器兼容问题  
\* @Date:2015-11-11  
\* @param request  
\* @param headers  
\* @param fileName  
\*/  
public void setFileName(HttpServletRequest request, HttpHeaders headers,  
String fileName) \{  
String browserName =  getBrowserName(request);  
if (browserName.indexOf("ie") > -1) \{  
try \{  
headers.add("Content-Disposition", "attachment;filename=\\""  
\+ URLEncoder.encode(fileName, "UTF-8") + "\\"");  
\} catch (UnsupportedEncodingException e) \{  
\}  
\}  
  
if (browserName.indexOf("firefox") > -1||browserName.indexOf("webkit") > -1) \{  
try \{  
headers.add("Content-Disposition", "attachment;filename=\\""  
\+ new String(fileName.getBytes("UTF-8"), "ISO-8859-1")  
\+ "\\"");  
\} catch (UnsupportedEncodingException e) \{  
e.printStackTrace();  
\}  
\} else \{  
headers.add("Content-Disposition", "attachment;filename="  
\+ fileName);  
\}  
\}  
  
public String getBrowserName(HttpServletRequest request) \{  
String agent = request.getHeader("User-Agent").toLowerCase();// 获取浏览器的版本  
if (agent.indexOf("msie 7") > 0) \{  
return "ie7";  
\} else if (agent.indexOf("msie 8") > 0) \{  
return "ie8";  
\} else if (agent.indexOf("msie 9") > 0) \{  
return "ie9";  
\} else if (agent.indexOf("msie 10") > 0) \{  
return "ie10";  
\} else if (agent.indexOf("msie") > 0) \{  
return "ie";  
\} else if (agent.indexOf("chrome") > 0) \{  
return "chrome";  
\} else if (agent.indexOf("opera") > 0) \{  
return "opera";  
\} else if (agent.indexOf("firefox") > 0) \{  
return "firefox";  
\} else if (agent.indexOf("webkit") > 0) \{  
return "webkit";  
\} else if (agent.indexOf("gecko") > 0 && agent.indexOf("rv:11") > 0) \{  
return "ie11";  
\} else \{  
return "Others";  
\}  
\}

2、生成下载文件的方法 TaskDownLoad

package org.uz.dxt.service.bankbase.impl;

import java.io.ByteArrayOutputStream;

import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.io.OutputStream;  
import java.net.HttpURLConnection;  
import java.net.URL;  
import java.util.Date;  
import java.util.HashMap;  
import java.util.Iterator;  
import java.util.List;  
import java.util.Map;  
import java.util.zip.Adler32;  
import java.util.zip.CheckedOutputStream;  
import java.util.zip.ZipEntry;  
import java.util.zip.ZipOutputStream;  
  
  
import org.apache.poi.util.Units;  
import org.apache.poi.xwpf.usermodel.BreakType;  
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;  
import org.apache.poi.xwpf.usermodel.TextAlignment;  
import org.apache.poi.xwpf.usermodel.XWPFDocument;  
import org.apache.poi.xwpf.usermodel.XWPFParagraph;  
import org.apache.poi.xwpf.usermodel.XWPFRun;  
   
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
   
import org.uz.dxt.model.bankbase.Contact;  
import org.uz.dxt.model.bankbase.FirmVisit;  
import org.uz.dxt.model.bankbase.PeopleAsset;  
import org.uz.dxt.model.bankbase.PeopleVisit;  
import org.uz.dxt.model.common.Attachment;  
import org.uz.dxt.util.DateUtil;  
import org.uz.dxt.util.StringUtils;  
  
  
   
   
/\*\*  
 \*   
 \* @Description：Task下载生成流  
 \*/  
public class TaskDownLoad \{  
private Logger log = LoggerFactory.getLogger(TaskDownLoad.class);  
// Word生成的格式  
private final String FONT\_FAMILY = "宋体";  
private final int TITLE\_FONT\_SIZE = 20;  
private final int BODY\_FONT\_SIZE = 12;  
private final String TITLE\_FONT\_COLOR = "000000";  
private final String BOFY\_FONT\_COLOR = "555555";  
// 下载文件的地址  
private final String HOST\_URL = "http://wiscc6911.qnoddns.org.cn:8400/file/web/download/";//根据不同的服务器写不同的地址  
  
  
    private String userId;  
private PeopleVisit task;  
private Map<String, byte\[\]> fileMap=new HashMap<String, byte\[\]>();  
  
private TaskDownLoad()\{   
\}  
public TaskDownLoad(PeopleVisit task,String userId) \{  
this.task = task;  
this.userId=userId;  
CreateWord();  
\}  
/\*\*  
 \*   
 \* @Description:生成word 并把word 流 和录音流放入 fileMap中  
 \* @Date:2015-12-17  
 \* @return  
 \*/  
private void CreateWord() \{ 主要使用的方法  
  
  
XWPFDocument doc = new XWPFDocument();  
ByteArrayOutputStream bt = new ByteArrayOutputStream();  
try \{  
  
  
// 目的//word文件大标题基本设置  
XWPFParagraph p1 = doc.createParagraph();  
XWPFRun run1 = p1.createRun();  
run1.setColor(TITLE\_FONT\_COLOR);//设置颜色  
run1.setFontFamily(FONT\_FAMILY);//设置字体  
run1.setFontSize(TITLE\_FONT\_SIZE);//设置字号  
run1.setBold(true);//设置加粗  
run1.setText("目的");//设置标题  
run1.addBreak();//设置换行  
  
  
XWPFRun r1 = p1.createRun();  
r1.setColor(BOFY\_FONT\_COLOR);//设置内容字体颜色  
r1.setFontFamily(FONT\_FAMILY);//设置类型  
r1.setFontSize(BODY\_FONT\_SIZE);//设置内容字体字号  
r1.setTextPosition(10);//设置位置  
  
  
r1.setText("任务编号：" + task.getDdTitle());//设置具体内容  
r1.addBreak();//设置换行  
  
//设置具体内容 数据是数字的不同含义的设置方式  
String dd\_type = task.getDdType() == null ? "" : task.getDdType();  
if (dd\_type.equals("01"))  
dd\_type = "贷前调查";  
if (dd\_type.equals("02"))  
dd\_type = "贷中走访";  
if (dd\_type.equals("03"))  
dd\_type = "贷后走访";  
r1.setText("目的：" + dd\_type);  
r1.addBreak();  
  
  
r1.setText("地点：" + task.getVisitaddress());//申请表内容  
r1.addBreak();  
  
  
r1.setText("日期：" + task.getSerdate());  
r1.addBreak();  
  
//同上面的解释  
// 基本信息  
XWPFParagraph p2 = doc.createParagraph();  
XWPFRun run2 = p2.createRun();  
run2.setColor(TITLE\_FONT\_COLOR);  
run2.setFontFamily(FONT\_FAMILY);  
run2.setFontSize(TITLE\_FONT\_SIZE);  
run2.setBold(true);  
run2.setText("基本信息");  
run2.addBreak();  
  
  
XWPFRun r2 = p2.createRun();  
r2.setColor(BOFY\_FONT\_COLOR);  
r2.setFontFamily(FONT\_FAMILY);  
r2.setFontSize(BODY\_FONT\_SIZE);  
r2.setTextPosition(10);  
  
  
r2.setText("姓名：" + task.getDdName());//申请表内容  
r2.addBreak();  
r2.setText("学历：" + task.getDdEducation());  
r2.addBreak();  
r2.setText("毕业院校：" + task.getCollegeTime());  
r2.addBreak();  
r2.setText("身份证号：" + task.getDdDoccode());//申请表内容  
r2.addBreak();  
  
  
String marriage = task.getMarriage() == null ? "" : task  
.getMarriage();  
if (dd\_type.equals("01"))  
dd\_type = "已婚";  
if (dd\_type.equals("02"))  
dd\_type = "未婚";  
r2.setText("婚姻状况：" + marriage);  
r2.addBreak();  
  
  
r2.setText("健康状况：" + task.getHealthStat());  
r2.addBreak();  
r2.setText("工作单位：" + task.getWorkAddr());  
r2.addBreak();  
r2.setText("资产估值：" + task.getAssetPrice());  
r2.addBreak();  
r2.setText("户型:" + task.getDoortype());  
r2.addBreak();  
r2.setText("产权：" + task.getPropertyStat());  
r2.addBreak();  
r2.setText("年收入：" + task.getIncome());  
r2.addBreak();  
r2.setText("收入来源：" + task.getMarginStat());  
r2.addBreak();  
r2.setText("家庭情况：" + task.getFamilyStat());  
r2.addBreak();  
  
  
// 联络信息  
//此为获取到的实体力有list类型的数据 循环遍历  
XWPFParagraph p3 = doc.createParagraph();  
XWPFRun run3 = p3.createRun();  
run3.setColor(TITLE\_FONT\_COLOR);  
run3.setFontFamily(FONT\_FAMILY);  
run3.setFontSize(TITLE\_FONT\_SIZE);  
run3.setBold(true);  
run3.setText("联络信息");  
run3.addBreak();  
  
  
XWPFRun r3 = p3.createRun();  
r3.setColor(BOFY\_FONT\_COLOR);  
r3.setFontFamily(FONT\_FAMILY);  
r3.setFontSize(BODY\_FONT\_SIZE);  
r3.setTextPosition(10);  
  
  
List<Contact> contactInfos = task.getContactInfos();  
if (contactInfos != null && contactInfos.size() > 0) \{  
for (Contact contact : contactInfos) \{  
  
  
r3.setText("姓名：" + contact.getName());  
r3.addBreak();  
r3.setText("单位：" + contact.getUnit());  
r3.addBreak();  
r3.setText("身份证号：" + contact.getDoccode());  
r3.addBreak();  
r3.setText("收入：" + contact.getIncome());  
r3.addBreak();  
r3.setText("关系：" + contact.getRelation());  
r3.addBreak();  
r3.setText("联系方式：" + contact.getMobile());  
r3.addBreak();  
\}  
\}  
  
  
 //此为获取到的实体里有list类型的附件，显示方式循环遍历  
// 附件图片=================  
XWPFParagraph p8 = doc.createParagraph();  
XWPFRun run8 = p8.createRun();  
run8.setColor(TITLE\_FONT\_COLOR);  
run8.setFontFamily(FONT\_FAMILY);  
run8.setFontSize(TITLE\_FONT\_SIZE);  
run8.setBold(true);  
run8.setText("图片附件");  
run8.addBreak();  
  
  
/\*List<Attachment> fileList = task.getFileList();  
if (fileList != null && fileList.size() > 0) \{  
  
  
XWPFParagraph p9 = doc.createParagraph();  
p9.setAlignment(ParagraphAlignment.CENTER);  
p9.setVerticalAlignment(TextAlignment.CENTER);  
// 图片  
XWPFRun r8 = p9.createRun();  
r8.setColor(BOFY\_FONT\_COLOR);  
r8.setFontFamily(FONT\_FAMILY);  
r8.setFontSize(BODY\_FONT\_SIZE);  
r8.setTextPosition(10);  
  
for (Attachment attachment : fileList) \{  
// 文件类型  
String type = attachment.getType();  
                    int fileSize=Integer.parseInt(attachment.getFileSize());  
if ("01".equals(type)&&fileSize>0) \{  
String fileName = attachment.getFileName();  
//获取图片流  
InputStream in = getImage(HOST\_URL+attachment.getFileUrl()+"?userId="+userId);  
r8.addPicture(in, XWPFDocument.PICTURE\_TYPE\_JPEG,  
fileName, Units.toEMU(300), Units.toEMU(400));  
r8.addBreak();  
  
fileName = StringUtils.substringBefore(fileName, ".");  
r8.setText(fileName);  
r8.addBreak();  
in.close();  
\} else if ("02".equals(type)&&fileSize>0) \{  
//录音获取  
InputStream in = getImage(HOST\_URL+attachment.getFileUrl());  
byte\[\] buffer=inputStreamToByte(in);  
fileMap.put(attachment.getFileName(), buffer);  
in.close();  
\}  
\}  
\}   
          \*/  
doc.write(bt);  
//word文件名拼装  
String wordFileName=task.getDdTitle();  
   wordFileName+="-"+DateUtil.formatDate(new Date())+".docx";  
     
   //word 放入fileMap中  
   fileMap.put(wordFileName, bt.toByteArray());  
     
  // bt.close();  
\} catch (Exception e) \{  
e.printStackTrace();  
log.error("create word \{\}" + e.getMessage());  
\}   
\}

3、打包方法createZip  
 /\*\*  
  \*   
  \* @Description:zip 打包  
  \* @Date:2015-12-17  
  \* @return  
  \*/  
 public byte\[\] createZip()\{   
ByteArrayOutputStream zipByte = new ByteArrayOutputStream();  
//输出流校验，采用Adler32 更快  
CheckedOutputStream csum = new CheckedOutputStream(zipByte, new Adler32());  
ZipOutputStream zos=new ZipOutputStream(csum);  
try\{   
Iterator<String> fileNames=fileMap.keySet().iterator();  
while(fileNames.hasNext())\{   
String fileName=fileNames.next();  
byte\[\] fileByte=fileMap.get(fileName);  
ZipEntry entry = new ZipEntry(fileName);    
entry.setSize(fileByte.length);    
zos.putNextEntry(entry);   
zos.write(fileByte);  
\}     
  zos.closeEntry();  
  zos.close();    
 return zipByte.toByteArray();  
\}catch (Exception e) \{   
e.printStackTrace();  
try \{   
zipByte.close();  
\} catch (IOException e1) \{   
e1.printStackTrace();  
\}  
\}  
return null;   
 \}  
   
   
    /\*\*  
\*   
\* @Description:从文件服务器获取文件流  
\* @Date:2015-12-16  
\* @param fileDownUrl  
\* @param fileId  
\* @return  
\* @throws Exception   
\*/  
private  InputStream getImage(String fileDownUrl) throws Exception\{   
log.debug(fileDownUrl);  
  
URL url;  
   InputStream in=null;  
url = new URL(fileDownUrl);  
HttpURLConnection request=(HttpURLConnection) url.openConnection();  
request.setRequestMethod("GET");  
request.setConnectTimeout(5\*1000);  
   request.setDoInput(true);  
     
log.debug(""+request.getResponseCode());  
   if(request.getResponseCode()==200)  
   \{   
     log.debug(""+request.getResponseCode());  
     in=request.getInputStream();  
     return in;  
   \}  
 log.debug("return null");  
return null;  
\}  
/\*\*  
\*   
\* @Description: input转byte\[\]  
\* @Date:2015-12-17  
\* @param in  
\* @return  
\* @throws IOException  
\*/  
private byte\[\] inputStreamToByte(InputStream in) throws IOException\{   
ByteArrayOutputStream out=new ByteArrayOutputStream();  
int len=0;  
byte\[\] buffer=new byte\[1024\];  
while((len=in.read(buffer))!=-1)\{   
  
out.write(buffer, 0, len);  
  
\}  
          
return out.toByteArray();  
  
\}  
  
  
\}

运行测试。谢谢观看

[Center]: /images/20220616/434cf3a582984eadb46cb9685bbd4027.png