freemarker 应用模板导出Execle(解决生成弹出版本不适合)

1. jsp 页面链接

------------------------------------------------------------

2.准备Execle导出模板 flhzbb.ftl 生成 ftl格式文档：字段为：实体类.属性另存为 ftl

模板一些地方要自己改成活的：可以到网上找一下

-----------------------

<Cell ss:StyleID="s71"><Data ss:Type="String">总计</Data></Cell>
<Cell ss:StyleID="s71"><Data ss:Type="String">满意度</Data></Cell>
</Row>
<#if list?? >
<#list list as ReportFlhzbbBean>
<Row ss:Height="34">
<Cell ss:StyleID="s71"><Data ss:Type="String">${ReportFlhzbbBean.type}</Data></Cell>

.......

.........

</Row>
</#list>
</#if>
</Table>

freemarker 应用模板导出Execle(解决生成弹出版本不适合)

3.controller层：准备导出数据

/**
   * 导出网民留言分类汇总报表
   *
   * @param model
   * @param request
   * @param response
   * @param year
   * @param type
   * @param quarter
   * @param sort
   * @return
   */
   @RequestMapping(value = "/wmly/flhzbbExport")
   public String flhzbbExport(Model model, HttpServletRequest request, HttpServletResponse response, String year,
           String type, String quarter,String sort) {
       List<ReportFlhzbbBean> list = reportService.typeQuarterDetail(year, type,"", quarter);
       if(CollectionUtils.isNotEmpty(list)){
           String sortName = "get"+sort.substring(0, 1).toUpperCase()+sort.substring(1);
           ListSortUtil.Sort(list, sortName, "desc");//排序
           reportService.setListCount(list);//总计
       }
       Map<String, Object> map = new HashMap<String, Object>();
       map.put("list", list);
       setMap(map, year, quarter);
       String quarterStr = String.valueOf(map.get("quarter"));
       map.put("title",year+"年"+quarterStr+"网民留言分类情况汇总表");
       reportService.export(request, response, map,quarter.equals("all")?"flhzbb_year.ftl":"flhzbb.ftl" ,"网民留言分类汇总报表");
       return null;
   }

4 service 层进入导出方法：

/**
* 导出
* @
*
* @param request
* @param response
* @param list
* @param modelName
* @param xlsName
*/
public void export(HttpServletRequest request, HttpServletResponse response, Map<String, Object> map,String modelName,String xlsName) {

File file = null;
InputStream inputStream = null;
ServletOutputStream out = null;
try {
DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
request.setCharacterEncoding("utf-8");
file = ReportUtil.createExcel(map, modelName);// 调用创建excel帮助类
String filePath = URLEncoder.encode(xlsName+df.format(new Date()) + ".xlsx", "UTF-8");
//String filePath = URLEncoder.encode(xlsName+df.format(new Date()) + ".xls", "UTF-8");//没加解决弹出报错

// String filePath = xlsName+df.format(new Date()) + ".xlsx";
inputStream = new FileInputStream(file);
response.setCharacterEncoding("utf-8");
response.setContentType("application/msexcel");//用execle形式打开文件
response.setHeader("content-disposition","attachment;filename=" + filePath);//设置content-disposition响应头控制浏览器以下载的形式打开文件
out = response.getOutputStream();
byte[] buffer = new byte[512]; // 缓冲区
int bytesToRead = -1;
// 通过循环将读入的Excel文件的内容输出到浏览器中
while ((bytesToRead = inputStream.read(buffer)) != -1) {
out.write(buffer, 0, bytesToRead);
}
out.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (inputStream != null)
inputStream.close();
if (out != null)
out.close();
if (file != null){
file.delete(); // 删除临时文件
String xml = file.getAbsolutePath().replace("xlsx", "xml");
new File(xml).delete();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

5.编写导出Execle 工具类

package com.tfkj.business.report.utils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.Map;

import com.ckfinder.connector.ServletContextFactory;

import freemarker.template.Configuration;
import freemarker.template.Template;

/**
* 导出excel Utils
* @author Administrator
*
*/
public class ReportUtil {
private static Configuration configuration =null;

/**
* 创建excel
* @param dataMap
* @param type
* @return
* @throws Exception
*/
public static File createExcel(Map<?, ?> dataMap, String valueName) throws Exception{
try {
configuration = new Configuration();
configuration.setDefaultEncoding("UTF-8");
configuration.setDirectoryForTemplateLoading(new File(ServletContextFactory.getServletContext().getRealPath("/")+"WEB-INF/model"));
} catch (IOException ex) {
ex.printStackTrace();
throw new RuntimeException(ex);
}
String name = "temp" + (int) (Math.random() * 100000) + ".xml";
File file = new File(name);
Template template = configuration.getTemplate(valueName);
try {
Writer w = new OutputStreamWriter(new FileOutputStream(file), "utf-8");
template.process(dataMap, w);
w.close();
//===============调用excel的另存为方法 start=========================
String[] split = file.getAbsolutePath().split("\\.");
            JacobExcelTool tool = new JacobExcelTool();
            //打开
            tool.OpenExcel(file.getAbsolutePath(),false);
            tool.SaveAs(split[0]+".xlsx");
            tool.CloseExcel(true, true);
            // 返回另存为文件对象
            file = new File(split[0]+".xlsx");
            //===============调用excel的另存为方法 end=========================
} catch (Exception e) {
e.printStackTrace();
       }
return file;
}
}

6.编写后台默认保存Execle工具类 JacobExcelTool (解决Execle弹出版本不适合问题)

注意：

freemarker 应用模板导出Execle(解决生成弹出版本不适合)

必读：把dll放在 jdk bin目录下 myelipse重新引入jdk

使用前操作
1、把dll文件放在%JAVA_HOME%\bin***意系统是32位还是64位），也可以放在C:\Windows\System32下，如果是64位应该放在C:\Windows\SysWOW64 下。建议放在jdk的bin目录下
2、如果是在eclipse下开发，需要重新引入jdk（Preference/Java/Installed JREs）
3、开发时将jacab.jar包放在项目lib下并add到liabraries中即可。

package com.tfkj.business.report.utils;
import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class JacobExcelTool {

private ActiveXComponent xl = null; // Excel对象

private Dispatch workbooks = null; // 工作簿对象

private Dispatch workbook = null; // 具体工作簿

public ActiveXComponent getXl() {
return xl;
}

public Dispatch getWorkbooks() {
return workbooks;
}

public Dispatch getWorkbook() {
return workbook;
}

/**
*
* 打开excel文件
* @param filepath 文件路径名称
* @param visible 是否显示打开
*
*/

public void OpenExcel(String filepath, boolean visible) {
try {
initComponents(); // 清空原始变量
ComThread.InitSTA();//仅允许同时运行一个线程，其他线程锁住
//ComThread.InitMTA(true);//可同时运行多个
if (xl == null)
xl = new ActiveXComponent("Excel.Application"); // Excel对象
xl.setProperty("Visible", new Variant(visible));// 设置是否显示打开excel
if (workbooks == null)
workbooks = xl.getProperty("Workbooks").toDispatch(); // 工作簿对象
workbook = Dispatch.invoke( // 打开具体工作簿
workbooks, "OpenXML", Dispatch.Method,
new Object[] { filepath},
new int[1]).toDispatch();
/* workbook = Dispatch.invoke( // 打开具体工作簿
       workbooks, "Open", Dispatch.Method,
       new Object[] { filepath, new Variant(false), new Variant(readonly) }, // 是否以只读方式打开
       new int[1]).toDispatch();
*/ } catch (Exception e) {
e.printStackTrace();
releaseSource();
}
}

/**
*
* 工作簿另存为
* @param filePath 另存为的路径
*
*/
public void SaveAs(String filePath) {
Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { filePath, new Variant(51)}, new int[1]);
}

/**
*
* 关闭excel文档
* @param f 含义不明（关闭是否保存？默认false）
*/

public void CloseExcel(boolean f, boolean quitXl) {
try {
//Dispatch.call(workbook, "Save");
Dispatch.call(workbook, "Close", new Variant(f));
} catch (Exception e) {
e.printStackTrace();
} finally {
if (quitXl) {
releaseSource();
}
}
}

/**
*
* 释放资源
*
*/
public void releaseSource() {
if (xl != null) {
xl.invoke("Quit", new Variant[] {});
xl = null;
}
workbooks = null;
ComThread.Release();
System.gc();
}

private void initComponents() {
workbook = null;
}
}

freemarker 应用模板导出Execle(解决生成弹出版本不适合)

freemarker 应用模板导出Execle(解决生成弹出版本不适合)

相关推荐