easyexcel可以用于大数据量的excel导入导出,并且不占多少内存
1.导入easyexcel的依赖,最新版本可以查找maven仓库
<!--JAVA解析Excel工具easyexcel-->
<groupId>com.alibaba
<artifactId>easyexcel
<version>1.1.2-beta5
</dependency>
2.逻辑层代码:
@Controller
@RequestMapping("/InvoiceExportAction.do")
public class InvoiceExportActionextends BaseController {
static Loggerlog=Logger.getLogger(InvoiceExportAction.class);
@Autowired
private InvoiceExportServiceinvoiceExportService;
/**
* 发票导出
* @param request
* @param response
* @return
* @throws IOException
*/
@RequestMapping(params ="method=exportGrid")
public StringexportGrid(HttpServletRequest request,HttpServletResponse response) {
String result=null;
ServletOutputStream out =null;
ExcelWriter writer =null;
try{
String numPerPage = request.getParameter("numPerPage");//查询分页，每页几条
String pageNum = request.getParameter("pageNum");//查询分页为第几页
String invoiceType=request.getParameter("invoiceType");//发票类型
String invoiceSource=request.getParameter("invoiceSource");//发票来源
String invoiceStatus=request.getParameter("invoiceStatus");//发票匹配状态
String invstate=request.getParameter("invstate");//发票状态
String invoicenote = request.getParameter("invoicenote");//对账单号
String ticketno = request.getParameter("ticketno");//开票单号
String uptime_min = request.getParameter("uptime_min");//上传最小日期
String uptime_max = request.getParameter("uptime_max");//上传最大日期
String invoicetime_min = request.getParameter("invoicetime_min");//发票最小日期
String invoicetime_max = request.getParameter("invoicetime_max");//发票最大日期
String invoiceCode = request.getParameter("invoiceCode");//发票代码
String invoiceno = request.getParameter("invoiceno");//发票代码
String venderCode = request.getParameter("venderCode");//供应商编号
String scanman = request.getParameter("scanman");//扫描人
String vender_cancel = request.getParameter("vender_cancel");//供应商作废
String[] companyArray = request.getParameterValues("company_fpcx");//公司代码
String taxFree = request.getParameter("taxFree");//是否免税
String ratifydate=request.getParameter("ratifydate");//过账日期
String selectInvoiceType=request.getParameter("selectInvoiceType");//发票类型前台传值
if(StringUtils.isEmpty(uptime_min)
|| StringUtils.isEmpty(uptime_max)){
String msg ="上传日期不能为空，请重新登录后再试";
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Type", "text/html;charset=UTF-8");
response.getOutputStream().write(msg.getBytes());
response.getOutputStream().flush();
return null;
}
List invoiceSheetInfoList =invoiceExportService.queryInvoiceList(invoiceType,invoiceSource,invoiceStatus,invoicenote,ticketno,uptime_min,
uptime_max,invoicetime_min,invoicetime_max,numPerPage,pageNum,invoiceCode,venderCode,companyArray,invoiceno,
scanman,vender_cancel,"1","",taxFree,invstate,ratifydate,selectInvoiceType);
//使用easyexcel进行导出
out = response.getOutputStream();
writer =new ExcelWriter(out, ExcelTypeEnum.XLSX);
String fileName ="我的发票查询信息";
Sheet sheet1 =new Sheet(1, 0,InvoiceSheetInfo.class);
sheet1.setSheetName("发票信息");
writer.write(invoiceSheetInfoList, sheet1);
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="
+new String((fileName +".xlsx").getBytes(), "ISO8859-1"));
out.flush();
return null;
}catch(Exception e){
log.error(e.getMessage());
e.printStackTrace();
result="404";
}finally {
if(Objects.nonNull(writer)){
writer.finish();
}
try {
if(Objects.nonNull(out)){
out.close();
}
}catch (IOException e) {
e.printStackTrace();
}
return result;
}
}
}
3.domain类
@Data
public class InvoiceSheetInfoextends BaseRowModel {
@ExcelProperty(value ="公司代码" ,index =0)
private Stringcompanycode;
@ExcelProperty(value ="公司名称" ,index =1)
private Stringcompanyname;
@ExcelProperty(value ="公司税号" ,index =2)
private Stringcompanytaxpaying;
@ExcelProperty(value ="供应商编码" ,index =3)
private Stringvendercode;
@ExcelProperty(value ="供应商名称" ,index =4)
private Stringvendername;
@ExcelProperty(value ="供应商税号" ,index =5)
private Stringvendertaxpaying;
@ExcelProperty(value ="开票单号" ,index =6)
private Stringinvnoteno;
@ExcelProperty(value ="发票号码" ,index =7)
private Stringinvoiceno;
@ExcelProperty(value ="发票代码" ,index =8)
private Stringinvoicecode;
@ExcelProperty(value ="发票日期" ,index =9)
private Stringinvoicedate;
@ExcelProperty(value ="未税金额" ,index =10)
private Stringtaxableamt;
@ExcelProperty(value ="税率" ,index =11)
private Stringtaxrate;
@ExcelProperty(value ="税额" ,index =12)
private Stringtaxamt;
@ExcelProperty(value ="含税金额" ,index =13)
private Stringpayment;
@ExcelProperty(value ="对账单号" ,index =14)
private Stringsheetid;
@ExcelProperty(value ="供应商作废" ,index =15)
private Stringvender_cancle;
@ExcelProperty(value ="发票来源" ,index =16)
private Stringinvoicesource;
@ExcelProperty(value ="发票匹配状态" ,index =17)
private Stringinvoicecodestatus;
@ExcelProperty(value ="发票状态" ,index =18)
private Stringinvstate;
@ExcelProperty(value ="失败原因" ,index =19)
private Stringfailreason;
@ExcelProperty(value ="过账日期" ,index =20)
private Stringratifydate;
@ExcelProperty(value ="上传日期" ,index =21)
private Stringuploaddate;
@ExcelProperty(value ="认证日期" ,index =22)
private Stringauthtime;
@ExcelProperty(value ="备注" ,index =23)
private Stringremarks;
@ExcelProperty(value ="扫描人" ,index =24)
private Stringscanman;
@ExcelProperty(value ="是否免税" ,index =25)
private Stringtaxfree;
}
