在java中使用FreeMark导出数据到excel表格

最新推荐文章于 2024-07-23 18:10:20 发布

ZeusLuna

最新推荐文章于 2024-07-23 18:10:20 发布

阅读量1w

点赞数 1

分类专栏： FreeMark

本文链接：https://blog.csdn.net/leipeng321123/article/details/51837362

版权

FreeMark 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

主要有一下几步：

1.导入需要的jar：freemarker-2.3.19.jar。

2.根据需要作出导出模板

3.在WebRoot下面建template文件夹，把制作的模板扔到这个文件夹下面

4.编写核心代码

具体实施细节：

制作模板：

把模板另存为XML电子表格2003（*.xml）的格式，然后修改表格中对应的地方

我在代码中给模板传入的数据为List<Map<String, String>>类型的，修改后的xml代码如下

<?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">
  <Created>2006-09-13T11:21:51Z</Created>
  <LastSaved>2016-06-23T01:02:55Z</LastSaved>
  <Version>12.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>11640</WindowHeight>
  <WindowWidth>19200</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>90</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Center"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="${size}" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
   <Column ss:AutoFitWidth="0" ss:Width="153"/>
   <Column ss:AutoFitWidth="0" ss:Width="164.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="138"/>
   <Column ss:AutoFitWidth="0" ss:Width="157.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="139.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="122.25"/>
   <Row>
    <Cell><Data ss:Type="String">学校名称 </Data></Cell>
    <Cell><Data ss:Type="String">所属区县 </Data></Cell>
    <Cell><Data ss:Type="String">学段 </Data></Cell>
    <Cell><Data ss:Type="String">资源上传数 </Data></Cell>
    <Cell><Data ss:Type="String">资源下载数 </Data></Cell>
    <Cell><Data ss:Type="String">资源收藏数</Data></Cell>
   </Row>
   <#list list as item>
   <#assign imap=item>
   <Row>

    <Cell><Data ss:Type="String">${imap.schoolname}</Data></Cell>
	<Cell><Data ss:Type="String">${imap.areaname}</Data></Cell>
	<Cell><Data ss:Type="String">${imap.educationphasename}</Data></Cell>
	<Cell><Data ss:Type="String">${imap.upload_count}</Data></Cell>
	<Cell><Data ss:Type="String">${imap.download_count}</Data></Cell>
	<Cell><Data ss:Type="String">${imap.collect_count}</Data></Cell>
   </Row>
   </#list>
  </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>200</HorizontalResolution>
    <VerticalResolution>200</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>7</ActiveRow>
     <ActiveCol>1</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="13.5">
  </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>200</HorizontalResolution>
    <VerticalResolution>200</VerticalResolution>
   </Print>
   <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="13.5">
  </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>200</HorizontalResolution>
    <VerticalResolution>200</VerticalResolution>
   </Print>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

接着就是把xml文件的后缀修改为ftl，扔到之前建好的文件夹下即可。

接下来就是导出的核心java代码了：

@Action("exportSchool")
	public void exportSchool(){
		try {
			ActionContext ac = ActionContext.getContext();
			HttpServletRequest request =(HttpServletRequest)ac.get(ServletActionContext.HTTP_REQUEST);
			ServletContext application = request.getSession().getServletContext();
			list = service.selectSchoolStatistics(school);
			Map<String, Object> dataMap = new HashMap<String, Object>();
			dataMap.put("size", String.valueOf(list.size()+2));
			dataMap.put("list", list);
			String  templateName = "school.ftl";   //模板名称
			String exportWord = application.getRealPath("template")+ DateUtils.getDateNow() + ".xls";
			ExportExcel.export(templateName, dataMap, exportWord, "学校报表统计("+DateUtils.getDateNow()+").xls");
		} catch (Exception e) {
			logger.error("学校统计数据导出到excel报错", e);
		}
	}

导出的核心类：

package net.firstelite.common.util;

import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.nio.charset.Charset;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionContext;

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

/**
 * 数据导出到excel表格
 * 
 * @author lenovo
 * 
 */
public class ExportExcel {
	public static void export(String templatePath, Map<String, Object> dataMap,
			String buildFile, String newName) {
		try {
			ActionContext ac = ActionContext.getContext();
			HttpServletRequest request =(HttpServletRequest)ac.get(ServletActionContext.HTTP_REQUEST);
			HttpServletResponse response = (HttpServletResponse)ac.get(ServletActionContext.HTTP_RESPONSE);
			ServletContext application = request.getSession().getServletContext();
			Configuration configuration = new Configuration();
			configuration.setDefaultEncoding("utf-8");
			String path = application.getRealPath("template");
			configuration.setDirectoryForTemplateLoading(new File(path));// 此处是本类Class.getResource()相对于模版文件的相对路径
			Template template = null;
			File outFile = new File(buildFile);
			Writer writer = null;
			template = configuration.getTemplate(templatePath);
			template.setEncoding("utf-8");
			writer = new BufferedWriter(new OutputStreamWriter(
					new FileOutputStream(outFile), Charset.forName("utf-8")));// 此处为输
																				// 出文档编码
			template.process(dataMap, writer);
			writer.flush();
			writer.close();

			// return true;

			// 设置response的编码方式
			response.setContentType("application/x-msdownload");
			// 设置附加文件名
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String(newName.getBytes("utf-8"), "iso-8859-1"));

			// 读出文件到i/o流
			FileInputStream fis = new FileInputStream(outFile);
			BufferedInputStream buff = new BufferedInputStream(fis);

			byte[] b = new byte[1024];// 相当于我们的缓存

			long k = 0;// 该值用于计算当前实际下载了多少字节

			// 从response对象中得到输出流,准备下载

			OutputStream myout = response.getOutputStream();

			// 开始循环下载

			while (k < outFile.length()) {

				int j = buff.read(b, 0, 1024);
				k += j;

				// 将b中的数据写到客户端的内存
				myout.write(b, 0, j);

			}

			// 将写入到客户端的内存的数据,刷新到磁盘
			myout.flush();
			myout.close();

		} catch (Exception e) {

			e.printStackTrace();
			// return false;
		}
	}
}