1 注解 在实体类上加@ExcelField(
title = “职务(必填)”,
align = 0,//0 是导入导出
sort = 20
)
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.aisino.aisinosite.common.utils.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
String value() default “”;
String title();
int type() default 0;
int align() default 0;
int sort() default 0;
String dictType() default "";
String dictName() default "";
Class<?> fieldType() default Class.class;
int[] groups() default {};
int length() default 0;
boolean notNull() default false;
}
// control写法
@RequestMapping(
value = {"import"},
method = {RequestMethod.POST}
)
public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
try {
int successNum = 0;
int failureNum = 0;
ImportExcel ei = new ImportExcel(file, 1, 0);
List<MoneyOfficialCard> list = ei.getDataList(MoneyOfficialCard.class);
for (MoneyOfficialCard moneyOfficialCard : list) {
try {
moneyOfficialCardService.save(moneyOfficialCard);
++successNum;
} catch (ConstraintViolationException ex) {
ex.printStackTrace();
++failureNum;
}
}
if(successNum>0){
redirectAttributes.addFlashAttribute("mes","导入成功"+successNum+"条公务卡");
}else{
redirectAttributes.addFlashAttribute("mes","导入失败,失败信息");
}
} catch (Exception e) {
e.printStackTrace();
redirectAttributes.addFlashAttribute("mes","导入模板不正确");
}
return "redirect:/";
}
、// 页面上加
$("#inport").click(function(){
layer.open({
type: 1,
title: ‘导入数据’,
closeBtn: true,
area: [‘390px’, ‘260px’],
shadeClose: true,
content: $("#importBox").html(),
});
});
});
<a class="layui-btn htupfilebtn" href="javascript:;"><input id="file" name="file" type="file" />上传文件</a>
<input id="btnImportSubmit" class="layui-btn" type="submit" value=" 开始导入 "/>
<a href="${ctx}/officecard/moneyOfficialCard/import/template" class="layui-btn">下载模板</a>
<div style="padding: 18px 30px 0 30px;"><input id="inportText" class="layui-input linporttext" style="border-color: #000 !important;" readonly/></div>
</form>
第二中用
@ResponseBody
@RequestMapping(value = “enquiryByAccount/export”)
public void enquiryByAccountExport(SysAccount sysAccount, HttpServletRequest request, HttpServletResponse response) throws IOException, TemplateException {
List list = sysAccountService.enquiryByAccount(sysAccount);
// 要导出的数据
Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put(“clist”, list);
dataMap.put(“dataSize”, list.size() + 10);
String basePath = request.getSession().getServletContext().getRealPath("/");
// 模板保存路径
String demoPath = basePath + “exportTemp”;
// 导出后文件存储位置
String savePath = “违规统计(按账号)”+ DateUtils.formatDate(new Date(),“yyyy-MM-dd”) +".xls";
// 要导出的数据的集合
TemplateParseUtil.parse(demoPath, “enquiryByAccount.ftl”, savePath, dataMap);
DownloadFile.downloadFile(savePath, response);
DownloadFile.deleteFile(savePath);
}
package com.aisino.aisinosite.modules.utils;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.DefaultObjectWrapper;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import java.io.*;
import java.util.Map;
public class TemplateParseUtil {
/**
* 解析模板生成Excel
* @param templateDir 模板目录
* @param templateName 模板名称
* @param path 生成的文件路径
* @param data 数据参数
* @throws IOException
* @throws TemplateException
*/
@SuppressWarnings(“deprecation”)
public static void parse(String templateDir,String templateName,String path,Map<String,Object> data) throws IOException, TemplateException {
//初始化工作
Configuration cfg = new Configuration();
//设置默认编码格式为UTF-8
cfg.setDefaultEncoding(“UTF-8”);
//全局数字格式
cfg.setNumberFormat(“0.00”);
//设置模板文件位置
cfg.setDirectoryForTemplateLoading(new File(templateDir));
cfg.setObjectWrapper(new DefaultObjectWrapper());
//加载模板
Template template = cfg.getTemplate(templateName,“utf-8”);
OutputStreamWriter writer = null;
try{
//填充数据至Excel
writer = new OutputStreamWriter(new FileOutputStream(path),“UTF-8”);
template.process(data, writer);
writer.flush();
}finally{
writer.close();
}
}
/**
* 解析模板返回字节数组
* @param templateDir 模板目录
* @param templateName 模板名称
* @param data 数据参数
* @throws IOException
* @throws TemplateException
*/
public static byte[] parse(String templateDir,String templateName,Map<String,Object> data) throws TemplateException, IOException{
Configuration cfg = new Configuration();
cfg.setDefaultEncoding("UTF-8");
cfg.setNumberFormat("0.00");
cfg.setDirectoryForTemplateLoading(new File(templateDir));
cfg.setObjectWrapper(new DefaultObjectWrapper());
Template template = cfg.getTemplate(templateName,"utf-8");
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
Writer out = new OutputStreamWriter(outStream,"UTF-8");
template.process(data, out);
return outStream.toByteArray();
}
/**
* 自定义模板字符串解析
* @param templateStr 模板字符串
* @param data 数据
* @return 解析后的字符串
* @throws IOException
* @throws TemplateException
*/
public static String parse(String templateStr, Map<String, Object> data)
throws IOException, TemplateException {
Configuration cfg = new Configuration();
cfg.setNumberFormat("#.##");
//设置装载模板
StringTemplateLoader stringLoader = new StringTemplateLoader();
stringLoader.putTemplate("myTemplate", templateStr);
cfg.setTemplateLoader(stringLoader);
//加载装载的模板
Template temp = cfg.getTemplate("myTemplate", "utf-8");
Writer out = new StringWriter();
temp.process(data, out);
return out.toString();
}
}
package com.aisino.aisinosite.modules.utils;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
public class DownloadFile {
public static void downloadFile(String filePath,HttpServletResponse response){
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
// path是指欲下载的文件的路径。
File file = new File(filePath);
// 取得文件名。
String filename = file.getName();
// 设置response的Header
String downLoadName = new String(filename.getBytes("gbk"), "iso8859-1");
response.setHeader("Content-Disposition", "attachment; filename=" + downLoadName);
response.addHeader("Content-Length", "" + file.length());
response.setContentType("application/octet-stream");
bis = new BufferedInputStream(new FileInputStream(filePath));
bos = new BufferedOutputStream(response.getOutputStream());
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bis.close();
bos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static boolean deleteFile(String filePath){
boolean flag = false;
File file = new File(filePath);
if(file.exists()){
flag = file.delete();
}
return flag;
}
}
//模板Word
-
-<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>2015-06-05T18:19:34Z</Created>
<LastSaved>2019-05-09T09:04:19Z</LastSaved>
<Version>16.00</Version>
-<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
<RemovePersonalInformation/>
-<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12720</WindowHeight>
<WindowWidth>23256</WindowWidth>
<WindowTopX>32767</WindowTopX>
<WindowTopY>32767</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
-<Styles>
-<Style ss:Name="Normal" ss:ID="Default">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:Color="#000000" ss:Size="11" ss:FontName="等线"/>
<Interior/>
<NumberFormat/>
<Protection/>
-<Style ss:ID="s16">
<Alignment ss:Vertical="Center" ss:Horizontal="Center"/>
-<Borders>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
</Borders>
<Font ss:Size="14" ss:FontName="宋体" ss:Bold="1" x:CharSet="134"/>
<Interior/>
-<Style ss:ID="s18">
<Alignment ss:Vertical="Center" ss:Horizontal="Center"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="等线" x:CharSet="134"/>
<Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
-<Style ss:ID="s19">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="等线" x:CharSet="134"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
-<Style ss:ID="s20">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="等线" x:CharSet="134"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
<NumberFormat ss:Format="Short Date"/>
-<Style ss:ID="s21">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="等线" x:CharSet="134"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
<NumberFormat ss:Format="h:mm:ss"/>
-<Style ss:ID="s22">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="等线" x:CharSet="134"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
-<Style ss:ID="s23">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="Tahoma" x:Family="Swiss"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
-<Style ss:ID="s24">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="Tahoma" x:Family="Swiss"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
<NumberFormat ss:Format="Short Date"/>
-<Style ss:ID="s25">
<Alignment ss:Vertical="Center" ss:Horizontal="Left" ss:Indent="1"/>
-<Borders>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Color="#E6E6E6" ss:Weight="2" ss:LineStyle="Continuous" ss:Position="Right"/>
</Borders>
<Font ss:Color="#666666" ss:Size="8" ss:FontName="Tahoma" x:Family="Swiss"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
<NumberFormat ss:Format="h:mm:ss"/>
-<Style ss:ID="s26">
<Font ss:Color="#666666" ss:Size="8" ss:FontName="Tahoma" x:Family="Swiss"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
-<Worksheet ss:Name="Sheet1">
-<Table ss:DefaultRowHeight="13.8" x:FullRows="1" x:FullColumns="1" ss:ExpandedRowCount="${dataSize}" ss:ExpandedColumnCount="10">
<Column ss:Span="4" ss:Width="68.399999999999991"/>
<Column ss:Width="101.4" ss:Index="6"/>
<Column ss:Width="147.6"/>
<Column ss:Width="100.19999999999999"/>
<Column ss:Width="147.6"/>
<Column ss:Width="100.19999999999999"/>
-<Row ss:Height="46.2" ss:AutoFitHeight="0">
-<Cell ss:StyleID="s16" ss:MergeAcross="9">
<Data ss:Type="String">账户统计(按账号)</Data>
-<Row ss:Height="17.399999999999999">
-<Cell ss:StyleID="s16">
<Data ss:Type="String">机构名称</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">监管户名</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">监管账号</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">账号别名</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">所属银行</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">账户余额(元)</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">提现违规金额(元)</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">提现违规次数</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">转账违规金额(元)</Data>
-<Cell ss:StyleID="s16">
<Data ss:Type="String">转账违规次数</Data>
<#list clist as item>
-
<Row ss:Height="17.399999999999999">
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.officeName!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.accountName!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="Number">${item.accountNumber!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.alias!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.accountBank!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.balance.balance!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.postalMoney!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.postalCount!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.transferMoney!}</Data>
</Cell>
<Cell ss:StyleID="s16">
<Data ss:Type="String">${item.transferCount!}</Data>
</Cell>
</Row>
</#list>
-<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
-<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Top="0.75" x:Right="0.7" x:Left="0.7" x:Bottom="0.75"/>
-<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
<Selected/>
-<Panes>
-<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
<ActiveCol>8</ActiveCol>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
、、页面
导出
$(function () {
KaTeX parse error: Expected 'EOF', got '#' at position 3: ('#̲export').on('cl…{ctx}/statistics/balance/export?officeid="+$("#officeId").val();
});
});