private static final Integer pageSize=100000;
/**
* zcc
* @param fixmedinsCode
* @param title
* @param sheetName
*/
public void exportAudtMorethanVo(String fixmedinsCode,String title, String sheetName) {
String filePath = excelPath + fixmedinsCode;
FileUtils.createFile(filePath);
// 指定导出的文件路径
FileOutputStream fos = null;
Workbook workbook = null;
try {
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
ExportParams exportParams = new ExportParams(title + "报表", "导出人:" + sysUser.getRealname(), sheetName);
exportParams.setMaxNum(1048576);
exportParams.setType(ExcelType.XSSF);
workbook = ExcelExportUtil.exportBigExcel(exportParams, AudtMorethanVo.class, new IExcelExportServer() {
/**
* obj 限制条件
* page 是页数，他是在分页进行文件转换，page每次+1
*/
@Override
public List<Object> selectListForExcelExport(Object obj, int page) {
//page每次加一，当等于obj的值时返回空，代码结束；
if (((int) obj) == page) {
return null;
}
List<AudtMorethanVo> audtMorethanVos = null;
try {
audtMorethanVos = audtFixmedinsService.selectAudtMorethanVoByfixmedinsCodeByPage(fixmedinsCode,null, page, pageSize);
} catch (Exception e) {
e.printStackTrace();
}
return CollectionUtil.isNotEmpty(audtMorethanVos)?Arrays.asList(audtMorethanVos.toArray()):null;
}
}, pageSize);
String fileName = workbook.getSheetAt(0).getSheetName();//sheet名字当文件名称
fos = new FileOutputStream(filePath + "/" + fileName + ".xlsx");
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
try {
fos.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* zcc
* 创建文件夹;
* @param path 路径
*/
public static void createFile(String path) {
File file = new File(path);
//判断文件是否存在;
if (!file.exists()) {
//创建文件;
file.mkdirs();
}
}
@Data
@ApiModel(value="AudtMorethanVo对象")
public class AudtMorethanVo implements Serializable {
private static final long serialVersionUID = 1L;
/**问题分类ID*/
// @Excel(name = "问题分类ID", width = 15)
@ApiModelProperty(value = "问题分类ID")
private String proclationid;
/**项目*/
@Excel(name = "项目", width = 15)
@ApiModelProperty(value = "项目")
private String protName;
/**问题描述*/
@Excel(name = "问题描述", width = 60)
@ApiModelProperty(value = "问题描述")
private String probDestion;
/**定点医药机构编号*/
@Excel(name = "定点医药机构编号", width = 15)
@ApiModelProperty(value = "定点医药机构编号")
private String fixmedinsCode;
/**定点医药机构名称*/
@Excel(name = "定点医药机构名称", width = 15)
@ApiModelProperty(value = "定点医药机构名称")
private String fixmedinsName;
/**结算ID*/
@Excel(name = "结算ID", width = 15)
@ApiModelProperty(value = "结算ID")
private String setlId;
/**证件号码*/
@Excel(name = "证件号码", width = 15)
@ApiModelProperty(value = "证件号码")
private String certno;
/**人员姓名*/
@Excel(name = "人员姓名", width = 15)
@ApiModelProperty(value = "人员姓名")
private String psnName;
/**性别*/
@Excel(name = "性别", width = 15)
@ApiModelProperty(value = "性别")
private String gend;
/**年龄*/
@Excel(name = "年龄", width = 15)
@ApiModelProperty(value = "年龄")
private String age;
/**住院号*/
@Excel(name = "住院号", width = 15)
@ApiModelProperty(value = "住院号")
private String iptOtpNo;
/**出院科室*/
@Excel(name = "出院科室", width = 15)
@ApiModelProperty(value = "出院科室")
private String dscgDeptName;
/**住院天数*/
@Excel(name = "住院天数", width = 15)
@ApiModelProperty(value = "住院天数")
private String iptDays;
/**入院日期*/
@Excel(name = "入院日期", width = 15, format = "yyyy-MM-dd")
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern="yyyy-MM-dd")
@ApiModelProperty(value = "入院日期")
private Date begndate;
/**出院日期*/
@Excel(name = "出院日期", width = 15, format = "yyyy-MM-dd")
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern="yyyy-MM-dd")
@ApiModelProperty(value = "出院日期")
private Date enddate;
/**医疗类别*/
@Excel(name = "医疗类别", width = 15)
@ApiModelProperty(value = "医疗类别")
private String medType;
/**医保目录名称*/
@Excel(name = "医保目录名称", width = 35)
@ApiModelProperty(value = "医保目录名称")
private String mxHilistName;
/**医保目录编码*/
@Excel(name = "医保目录编码", width = 35)
@ApiModelProperty(value = "医保目录编码")
private String mxHilistCode;
/**收费时间*/
@Excel(name = "收费时间", width = 15, format = "yyyy-MM-dd")
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")
@DateTimeFormat(pattern="yyyy-MM-dd")
@ApiModelProperty(value = "收费时间")
private Date mxFeeOcurTime;
/**收取数量*/
@Excel(name = "收取数量", width = 15,type = 4)
@ApiModelProperty(value = "收取数量")
private Integer mxCnt;
/**金额*/
@Excel(name = "金额", width = 15,type = 4)
@ApiModelProperty(value = "金额")
private BigDecimal mxFeeSumamt;
/**单价*/
@Excel(name = "单价", width = 15,type = 4)
@ApiModelProperty(value = "单价")
private Integer mxPric;
/**超出数量*/
@Excel(name = "超出数量", width = 15,type = 4)
@ApiModelProperty(value = "超出数量")
private Integer iptDaysExceed;
/**超出金额*/
@Excel(name = "超出金额", width = 15,type = 4)
@ApiModelProperty(value = "超出金额")
private BigDecimal feeSumamtExceed;
/**违规数量*/
@Excel(name = "违规数量", width = 15,type = 4)
@ApiModelProperty(value = "违规数量")
private Integer mxVitionNumber;
/**违规金额*/
@Excel(name = "违规金额", width = 15,type = 4)
@ApiModelProperty(value = "违规金额")
private BigDecimal mxVitionAmount;
}
Mapper:
List<AudtMorethanVo> selectAudtMorethanVoByfixmedinsCodeByPage(@Param("fixmedinsCode") String fixmedinsCode, @Param("protName") String protName,@Param("pageNum") Integer pageNum,@Param("pageSize")Integer pageSize);
XML:
<select id="selectAudtMorethanVoByfixmedinsCodeByPage" resultType="com.frkj.modules.fund.common.vo.AudtMorethanVo">
SELECT * FROM (
SELECT e.*, ROWNUM rnum FROM (
select
b.proclation,
b.prot_name ,
b.prob_destion ,
a.fixmedins_code,
a.fixmedins_name,
a.setl_id,
a.certno,
a.psn_name,
a.gend,
a.age,
a.ipt_otp_no,
a.dscg_dept_name,
a.ipt_days,
a.begndate,
a.enddate,
a.med_type,
a.mx_hilist_name,
a.mx_hilist_code,
a.mx_fee_ocur_time,
a.mx_cnt,
a.mx_fee_sumamt,
a.mx_pric,
a.ipt_days_exceed,
a.fee_sumamt_exceed,
a.mx_vition_number,
a.mx_vition_amount
from AUDT_Morethan a,AUDT_RU b
where a.vtionsueid=b.vtionsueid
<if test="fixmedinsCode!=null and fixmedinsCode!=''">
and a.fixmedins_code=#{fixmedinsCode}
</if>
<if test="protName!=null and protName!=''">
and b.prot_name=#{protName}
</if>
order by a.vtionsueid,a.setl_id,a.mx_fee_ocur_time
) e WHERE ROWNUM <= #{pageNum} * #{pageSize}
) WHERE rnum > (#{pageNum} - 1) * #{pageSize}
</select>
个人电脑测试结果：
