Java导出Excel和Word

1.导出word

a.打开需要导出的word模板,另存为xml文件

 

b.java中使用

//生成word文件

public void downWordAcceptRegist(HttpServletRequest request, HttpServletResponse response, String reportId){

Map<String,Object> resMap=returnWordInfor(reportId,"wordDown");

List<Map<String,Object>> myResultList=new ArrayList<>();

if(EmptyUtil.isNotEmpty(resMap.get("reportimg"))){

String imgStr=resMap.get("reportimg")+"";

int aaa=imgStr.split(";").length;

for(int i=0;i<imgStr.split(";").length;i++){

Map<String,Object> that_map=new HashMap<>();

that_map.put("t_num",i);

String imgstr1=imgStr.split(";")[i];

that_map.put("t_pic",GetImageBase.getImageBase(imgstr1));

if(EmptyUtil.isNotEmpty(imgstr1)){

myResultList.add(that_map);

}

}

}else{

Map<String,Object> that_map=new HashMap<>();

that_map.put("t_num",0);

that_map.put("t_pic",GetImageBase.getImageBase("noImg.png"));

myResultList.add(that_map);

}

resMap.put("myPicList",myResultList);

 

try {

WordUtils.exportMillCertificateWord(request,response,resMap,resMap.get("reportid").toString(),"priceAccpetRegist.ftl");

} catch (IOException e) {

e.printStackTrace();

}

}

c.XML文件中的编写

${reportcontent}

<#list myPicList as list>

${list.t_pic}

</#list>

d.创建并下载word文件

public static void exportMillCertificateWord(HttpServletRequest request, HttpServletResponse response, Map map,String title,String ftlFile) throws IOException {

//System.out.println(request.getSession().getServletContext().getRealPath("")+"\\resources\\resource\\");

Template freemarkerTemplate = configuration.getTemplate(ftlFile);

File file = null;

InputStream fin = null;

ServletOutputStream out = null;

try {

// 调用工具类的createDoc方法生成Word文档

file = createDoc(map,freemarkerTemplate,title,"temp_export");

fin = new FileInputStream(file);

 

response.setCharacterEncoding("utf-8");

response.setContentType("application/msword");

// 设置浏览器以下载的方式处理该文件名

String fileName = title + ".docx";

response.setHeader("Content-Disposition", "attachment;filename="

.concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));

 

out = response.getOutputStream();

// 缓冲区

byte[] buffer = new byte[512];

int bytesToRead = -1;

// 通过循环将读入的Word文件的内容输出到浏览器中

while((bytesToRead = fin.read(buffer)) != -1) {

out.write(buffer, 0, bytesToRead);

}

} finally {

if(fin != null) fin.close();

if(out != null) out.close();

// 删除临时文件暂时不删除

if(file != null) file.delete();

}

}

 

private static File createDoc(Map<?, ?> dataMap, Template template,String title,String saveFile) {

String name = title+".doc";

File f = new File(wordsave_folder+saveFile+"/"+name);

try {

/*当文件夹不存在时，新建文件夹*/

File fileParent = f.getParentFile();

if(!fileParent.exists()){

fileParent.mkdirs();

}

f.createNewFile();

Template t = template;

 

// 这个地方不能使用FileWriter因为需要指定编码类型否则生成的Word文档会因为有无法识别的编码而无法打开

Writer w = new OutputStreamWriter(new FileOutputStream(f), "utf-8");

t.process(dataMap, w);

w.close();

} catch (Exception ex) {

ex.printStackTrace();

throw new RuntimeException(ex);

}

return f;

}

2.导出Excel

a.数据准备

List<ReportDetailListWebVo> reportDetailListWebVos = Lists.newArrayList();

Map<String, Object> reqmap = new HashMap(18);

reqmap.put("SERVICE_CODE", "szsti.conspace.ReportDetail.selExportExcelReportDetailList");

reqmap.put("searchMsg",searchMsg);

reqmap.put("beginDate",beginDate);

reqmap.put("endDate",endDate);

reqmap.put("reportMethod",reportMethod);

reqmap.put("placeType",placeType);

reqmap.put("category",category);

reqmap.put("reportType",reportType);

reqmap.put("priceType",priceType);

reqmap.put("CONSUMER_ID", CONSUMER_ID);

//获取数据

String test = MyJsonParse.parseJsonString(ProxyUtil.doDispatcher(reqmap).get("data")+"");

if (EmptyUtil.isNotEmpty(test)){

reportDetailListWebVos = JSON.parseObject(test,new TypeReference<List<ReportDetailListWebVo>>() {});

}

//excel标题

String[] title = {"编号","登记号","街道","举报人姓名","举报人手机号","地址","举报单位","隐患场所",

"隐患类别","举报内容","是否匿名(0:否 1:是)","处理状态","奖励金额"};

//excel文件名

String fileName = "12350隐患举报汇总信息" + System.currentTimeMillis() + ".xls";

//sheet名

String sheetName = "隐患举报汇总信息";

String [][]content = new String[reportDetailListWebVos.size()][title.length];

int i = 0;

for(ReportDetailListWebVo reportDetailListWebVo : reportDetailListWebVos){

content[i][0] = String.valueOf(i+1);//编号

content[i][1] = reportDetailListWebVo.getReportId();//登记号

content[i][2] = reportDetailListWebVo.getStreetName();//街道

content[i][3] = reportDetailListWebVo.getRealname();//举报人姓名

content[i][4] = reportDetailListWebVo.getTelPhone();//举报人手机号

content[i][5] = reportDetailListWebVo.getReportAddress();//地址

content[i][6] = reportDetailListWebVo.getReportCompany();//举报单位

content[i][7] = reportDetailListWebVo.getPlacename();//隐患场所

content[i][8] = reportDetailListWebVo.getTypename();//隐患类别

content[i][9] = reportDetailListWebVo.getReportcontent();//举报内容

content[i][10] = reportDetailListWebVo.getAnonymous();//是否匿名

content[i][11] = reportDetailListWebVo.getStatusname();//处理状态

content[i][12] = reportDetailListWebVo.getPriceAmount();//奖励金额

i++;

}

//创建HSSFWorkbook

HSSFWorkbook wb = ExcelUtil.getRiskHSSFWorkbook(sheetName,title,content,null);

//响应到客户端

try {

this.setResponseHeader(response,fileName);

OutputStream os = response.getOutputStream();

wb.write(os);

os.flush();

os.close();

} catch (Exception e) {

log.error("下载excel出现异常"+e);

e.printStackTrace();

}

b.个性化设置

public static HSSFWorkbook getRiskHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

 

// 第一步，创建一个HSSFWorkbook，对应一个Excel文件

if(wb == null){

wb = new HSSFWorkbook();

}

 

// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet

HSSFSheet sheet = wb.createSheet(sheetName);

 

CellStyle myStyle = wb.createCellStyle();

//设置样式

CellStyle blackStyle = wb.createCellStyle();

//自动换行*重要*

blackStyle.setWrapText(true);

 

//判断这一列的最长字符串，然后

String str = "广东省深圳市福田区福民路";

int length = str.getBytes().length;

sheet.setColumnWidth((short)5,(short)(length*256));

str = "711便利店有限公司";

length = str.getBytes().length;

sheet.setColumnWidth((short)6,(short)(length*256));

str = "该单位一号消防楼梯间有大量杂物堆放，无疏散指示图。";

length = str.getBytes().length;

sheet.setColumnWidth((short)9,(short)(length*256));

 

// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制

HSSFRow row = sheet.createRow(0);

 

// 第四步，创建单元格，并设置值表头 设置表头居中

HSSFCellStyle style = wb.createCellStyle();

 

// 创建一个居中格式

style.setAlignment(HorizontalAlignment.CENTER);

HSSFFont font = wb.createFont();

//加粗

font.setBold(true);

style.setFont(font);

 

//声明列对象

HSSFCell cell = null;

 

//创建标题

for(int i=0;i<title.length;i++){

cell = row.createCell(i);

cell.setCellValue(title[i]);

cell.setCellStyle(style);

}

 

//创建内容

for(int i=0;i<values.length;i++){

row = sheet.createRow(i + 1);

for(int j=0;j<values[i].length;j++){

//将内容按顺序赋给对应的列对象

if (j==5 || j==6 || j==9){

row.setRowStyle(blackStyle);

} else {

row.setRowStyle(myStyle);

}

row.createCell(j).setCellValue(values[i][j]);

}

}

return wb;

}