java使用freemarker作为模板导出Excel表格
1：首先新建一个excel表格自己弄好格式如下图
2：把excel 表格另存为xml格式文件如下图
3：这个时候的文件就是xml 格式的文件了,在myeclipse里面项目工程里面新建一个文件后缀为.ftl 然后把弄好的xml文件内容直接复制粘贴到.fl文件里面
如图
4：好了，现在我们直接java 后台action 类代码如下
/**
* 导出订单表
* @throws Exception
*/
public void exportOrder() throws Exception{
HttpServletRequest request = ServletActionContext.getRequest();
HttpServletResponse response = ServletActionContext.getResponse();
Map<String, Object> map = new HashMap<String, Object>();
BookOrder bookOrder = new BookOrder();
bookOrder.setOrderNo(params.getOrderNo());
bookOrder.setName(params.getName());
bookOrder.setPhone(params.getPhone());
if(params.getCompany() !=null && params.getCompany().getId() !=null){
bookOrder.setCompany(companyService.load(params.getCompany().getId()));
}
bookOrder.setOrderStatus(params.getOrderStatus());
bookOrder.setDistType(params.getDistType());
bookOrder.setFoodType(params.getFoodType());
bookOrder.setStartTime(params.getStartTime());
bookOrder.setEndTime(params.getEndTime());
bookOrder.setRefundStatus(params.getRefundStatus());
bookOrder.setReminder(params.getReminder());
if(params.getStall() !=null && params.getStall().getId() !=null){
bookOrder.setStall(stallService.load(params.getStall().getId()));
}
List<BookOrder> bookOrderList = (List<BookOrder>)targetService.loadList(bookOrder);
map.put("bookOrderList", bookOrderList);
File file = null;
InputStream inputStream = null;
ServletOutputStream out = null;
try {
request.setCharacterEncoding("UTF-8");
file = ExcelUtils.createExcel(map, "myExcel","order.ftl");//调用创建excel帮助类
inputStream = new FileInputStream(file);
response.setCharacterEncoding("utf-8");
response.setContentType("application/msexcel");
response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode("订单统计" + ".xls", "UTF-8"));
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 {
if (inputStream != null)
inputStream.close();
if (out != null)
out.close();
if (file != null)
file.delete(); // 删除临时文件
}
}
5：下面这个是Utils 公共方法如下
package com.funcanteen.business.action.pay.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.HashMap;
import java.util.Map;
import org.apache.struts2.ServletActionContext;
import freemarker.template.Configuration;
import freemarker.template.Template;
/**
* 导出excel Utils
* @author Administrator
*
*/
public class ExcelUtils {
private static Configuration configuration =null;
private static Map<String, Template> allTemplates =null;
private static String realPath = ServletActionContext.getServletContext().getRealPath("/");
/*static{
configuration = new Configuration();
configuration.setDefaultEncoding("UTF-8");
try {
configuration.setDirectoryForTemplateLoading(new File(realPath+"WEB-INF/mailtemplate"));
allTemplates = new HashMap<String, Template>();
allTemplates.put("myExcel", configuration.getTemplate("order.ftl"));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}*/
public ExcelUtils(){
throw new AssertionError();
}
/**
* 创建excel
* @param dataMap
* @param type
* @return
*/
public static File createExcel(Map<?, ?> dataMap, String type,String valueName){
try {
configuration = new Configuration();
configuration.setDefaultEncoding("UTF-8");
configuration.setDirectoryForTemplateLoading(new File(realPath+"WEB-INF/mailtemplate"));
allTemplates = new HashMap<String, Template>();
allTemplates.put(type, configuration.getTemplate(valueName));
} catch (IOException ex) {
ex.printStackTrace();
throw new RuntimeException(ex);
}
String name = "temp" + (int) (Math.random() * 100000) + ".xls";
File file = new File(name);
Template template = allTemplates.get(type);
try {
Writer w = new OutputStreamWriter(new FileOutputStream(file), "utf-8");
template.process(dataMap, w);
w.close();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return file;
}
}
6下面是ftl 模板文件
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Administrator</Author>
<LastAuthor>Administrator</LastAuthor>
<Created>2008-09-11T17:22:52Z</Created>
<LastSaved>2016-12-22T05:40:25Z</LastSaved>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>5715</WindowHeight>
<WindowWidth>12765</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s66">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#00B050" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s68">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
<Interior ss:Color="#00B050" ss:Pattern="Solid"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s76">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<NumberFormat ss:Format="General Date"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="${bookOrderList?size+2}" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s65" ss:DefaultColumnWidth="54"
ss:DefaultRowHeight="14.25">
<Column ss:Index="5" ss:StyleID="s65" ss:Width="76.5"/>
<Column ss:StyleID="s65" ss:Width="101.25"/>
<Row>
<Cell ss:StyleID="s66"><Data ss:Type="String">所属饭堂</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">所属档口</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">订单号</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">下单人</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">联系电话</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">地址</Data></Cell>
<Cell ss:StyleID="s68"><Data ss:Type="String">下单时间</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">订单原价</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">订单价格</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">优惠价格</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">快递费</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">打包费</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">快递方式</Data></Cell>
<Cell ss:StyleID="s66"><Data ss:Type="String">用餐类型</Data></Cell>
</Row>
<#if bookOrderList?? >
<#list bookOrderList as bookOrder>
<Row>
<#if bookOrder.company ??>
<Cell ss:StyleID="s64"><Data ss:Type="String">${bookOrder.company.simpleName?if_exists}</Data></Cell>
<#else>
<Cell ss:StyleID="s64"><Data ss:Type="String"></Data></Cell>
</#if>
<#if bookOrder.stall ??>
<Cell ss:StyleID="s64"><Data ss:Type="String">${bookOrder.stall.simpleName?if_exists}</Data></Cell>
<#else>
<Cell ss:StyleID="s64"><Data ss:Type="String"></Data></Cell>
</#if>
<Cell><Data ss:Type="String">${bookOrder.orderNo?if_exists}</Data></Cell>
<Cell ss:StyleID="s64"><Data ss:Type="String">${bookOrder.name?if_exists}</Data></Cell>
<Cell><Data ss:Type="String">${bookOrder.phone?if_exists}</Data></Cell>
<Cell><Data ss:Type="String">${bookOrder.address?if_exists}</Data></Cell>
<Cell ss:StyleID="s76"><Data ss:Type="String">${bookOrder.createTime?string("yyyy-MM-dd HH:mm:ss")}</Data></Cell>
<Cell><Data ss:Type="Number">${bookOrder.orderPrice?default(0)?double + bookOrder.benefitPrice?default(0)?double - bookOrder.expressCharge?default(0)?double - bookOrder.packCharge?default(0)?double}</Data></Cell>
<Cell><Data ss:Type="Number">${bookOrder.orderPrice?if_exists}</Data></Cell>
<Cell><Data ss:Type="Number">${bookOrder.benefitPrice?if_exists}</Data></Cell>
<Cell><Data ss:Type="Number">${bookOrder.expressCharge?if_exists}</Data></Cell>
<Cell><Data ss:Type="Number">${bookOrder.packCharge?if_exists}</Data></Cell>
<#if bookOrder.distType ?? && bookOrder.distType==0>
<Cell ss:StyleID="s64"><Data ss:Type="String">自提</Data></Cell>
</#if>
<#if bookOrder.distType ?? && bookOrder.distType==1>
<Cell ss:StyleID="s64"><Data ss:Type="String">快递</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==1>
<Cell ss:StyleID="s64"><Data ss:Type="String">早餐</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==2>
<Cell ss:StyleID="s64"><Data ss:Type="String">午餐</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==3>
<Cell ss:StyleID="s64"><Data ss:Type="String">晚餐</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==4>
<Cell ss:StyleID="s64"><Data ss:Type="String">宵夜</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==5>
<Cell ss:StyleID="s64"><Data ss:Type="String">下午茶</Data></Cell>
</#if>
<#if bookOrder.foodType ?? && bookOrder.foodType==6>
<Cell ss:StyleID="s64"><Data ss:Type="String">零食</Data></Cell>
</#if>
</Row>
</#list>
</#if>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>4</ActiveRow>
<ActiveCol>10</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
posted on 2020-02-29 00:15 nickTimer 阅读(1322) 评论(0) 编辑 收藏 举报
