public static void main(String[] args) throws IOException, Exception {
ImportExcel<Student> test = new ImportExcel<Student>(Student.class);
File file = new File("/Users/yangdongdong/Downloads/叫中心没有推送人以及推送时间的人.xls");
List<Student> results = (List<Student>) test.importExcel(file);
int i = 1;
for (Student student : results) {
if (student.getName() != null) {
String sql =
"(select id from tb_p_e_user where org_id=1 and full_name='"
+ student.getPushName()
+ "' limit 1)";
// System.out.println(student.getPhone());
if (student.getMobile() != null && !student.getMobile().equals("")) {
System.out.println(
"update tb_e_student set push_time='"
+ DateUtil.getDate(student.getPushDate(), "yyyy-MM-dd")
+ "',push_id="
+ sql
+ " where mobile='"
+ student.getMobile().trim()
+ "';");
} else if (student.getPhone() != null && !student.getPhone().equals("")) {
System.out.println(
"update tb_e_student set push_time='"
+ DateUtil.getDate(student.getPushDate(), "yyyy-MM-dd")
+ "',push_id="
+ sql
+ " where phone='"
+ student.getPhone().trim()
+ "';");
}
}
}
}
/**
* 导出学生集合
*
* @return
* @throws Exception
*/
@Action(
value = "excel_export_list_student",
results = {
@Result(
name = "success",
type = "json",
params = {"contentType", "text/json"})
})
public String excelExportListStudent() throws Exception {
if (stuStage != null
&& !"".equals(stuStage)
&& !stuStage.equals("0")
&& student.getStatus() == 0) {
List<StudentStatus> stuStatusList = this.stuStatusBiz.findStatusNamesByStageCode(stuStage);
StringBuffer statusSB = new StringBuffer("");
if (stuStatusList != null && stuStatusList.size() > 0) {
for (StudentStatus ss : stuStatusList) {
if (statusSB.toString().equals("")) {
statusSB = new StringBuffer(ss.getId() + "");
} else {
statusSB.append("," + ss.getId());
}
}
if (!statusSB.toString().equals("")) {
student.setStatusIds(statusSB.toString());
}
}
}
PageResult<Student> pr = new PageResult<Student>();
// 检查导出结果是否为空
int count = studentBiz.findStudentsPageCountByBaseConditions(student);
if (count == 0) {
downloadUrl = "error" + Constants.EXPORT_EXCEL_ERROR_NO_3;
return SUCCESS;
}
// 设置下载任务导出数据数量上限
dataMaxCount = Constants.EXPORT_EXCEL_MAX_COUNT; // 如果配置文件异常则默认导出数据数量上限
if (Config.getProperty("export.maxcount") != null) {
try {
dataMaxCount = Config.getIntProperty("export.maxcount");
} catch (Exception e) {
e.printStackTrace();
}
}
// 检查导入结果是否超过上限
if (count > dataMaxCount) {
downloadUrl = "error" + Constants.EXPORT_EXCEL_ERROR_NO_4;
return SUCCESS;
}
int $page = 1; // 起始索引
// 设置一次导入的数据量
int $pageSize = Constants.EXPORT_EXCEL_MAX_PAGE_SIZE; // 如果配置文件异常则默认导出数据数量上限
if (Config.getProperty("export.maxpagesize") != null) {
try {
$pageSize = Config.getIntProperty("export.maxpagesize");
} catch (Exception e) {
e.printStackTrace();
}
}
boolean only = true;
OutputStream os = null;
// 导出的excel数据集合
List<StudentDataExportTemplate> studentDataExportTemplateList =
new ArrayList<StudentDataExportTemplate>();
// 导出的随机目录
long ctm = System.currentTimeMillis();
UserTask userTask = new UserTask();
userTask.setCreateTime(new Date());
userTask.setDownloadSumCount(0);
userTask.setPath(ctm + "");
userTask.setTitle("呼叫中心|在线客服|学生列表|学生导出");
userTask.setUserId(super.getUser().getUserId());
// 任务添加失败
int errorno = taskBiz.addTask(userTask);
if (errorno != Constants.EXPORT_EXCEL_FINISH) {
downloadUrl = "error" + errorno;
return SUCCESS;
}
String path = Config.getProperty("export.excel.tmp") + ctm;
// 创建导出的工具类对象
ExcelExport<StudentDataExportTemplate> ex = new ExcelExport<StudentDataExportTemplate>();
// 创建随机目录
FileUtil.CheckCreateDR(application.getRealPath(path));
// 不分页
pr.setPageSize($pageSize);
pr.setPage(true);
pr.setOrder("branchId");
List<Student> studentList = null;
while (only || (studentList != null && studentList.size() != 0)) {
pr.setCurrentPageIndex($page);
studentList = studentBiz.findStudentsPageListByBaseConditions(student, pr);
if (studentList != null) {
for (Student student : studentList) {
// 市场途径
BaseDict basedict = basedictBiz.findBaseDictById(student.getEnrollmentWay());
// 合作方
Channel channel = channelBiz.findChannel(student.getChannelId());
StudentDataExportTemplate studentDataExportTemplate = new StudentDataExportTemplate();
studentDataExportTemplate.setBranchName(student.getBranchName());
studentDataExportTemplate.setFollowUpUserName(student.getFollowUpName());
studentDataExportTemplate.setStudentName(student.getName());
studentDataExportTemplate.setEnrollmentBatchName(
student.getAcademyenrollbatchName()); // ???
studentDataExportTemplate.setAcademyName(student.getSchoolName());
studentDataExportTemplate.setLevelName(student.getLevelName());
studentDataExportTemplate.setMajorName(student.getMajorName());
studentDataExportTemplate.setStudentDataSource(
ConstantsStudentDataSourceMap.getCode(student.getStudentDataSource()));
studentDataExportTemplate.setEnrollmentSource(student.getEnrollmentSourceName1());
studentDataExportTemplate.setChannelName(channel == null ? "未知" : channel.getName());
studentDataExportTemplate.setEnrollmentWay(basedict == null ? "--" : basedict.getName());
studentDataExportTemplate.setCallStatus(
ConstantsCallStatusMap.getCode(student.getCallStatus()));
studentDataExportTemplate.setIsChannelTypeChecked(
ConstantsChannelTypeCheckedMap.getCode(student.getIsChannelTypeChecked()));
studentDataExportTemplate.setStatus(
ConstantsStudentStatusMap.getCode(student.getStatus()));
studentDataExportTemplate.setCertNo(student.getCertNo());
studentDataExportTemplate.setMobile(student.getMobile());
studentDataExportTemplate.setPhone(student.getPhone());
studentDataExportTemplate.setRegistrationTime(
student.getRegistrationTime() == null
? ""
: student.getRegistrationTime().toString().substring(0, 10));
studentDataExportTemplate.setRemark(student.getRemark());
studentDataExportTemplateList.add(studentDataExportTemplate);
}
// 随机产生的文件名称前缀
String dateStr = DateUtil.dateToStringWithTime(new Date());
// 随机产生的文件名称以及后缀
String filePath = dateStr + ".xls";
// 文件全目录
String tempPath = application.getRealPath(path) + File.separator + filePath;
// 创建流对象
os = new FileOutputStream(tempPath);
// 开始导出
ex.exportExcel("导出结果", studentDataExportTemplateList, os);
// 关闭流
os.close();
studentDataExportTemplateList.clear();
os = null;
$page++;
only = false;
if (studentList.size() < $pageSize) {
break;
}
}
}
// 压缩文件
File inFile = new File(application.getRealPath(path));
ZipOutputStream zos =
new ZipOutputStream(new FileOutputStream((application.getRealPath(path) + ".zip.tmp")));
ZipUtil.zipFile(inFile, zos, "");
zos.close();
zos = null;
// 执行改名
FileUtil.renamed(application.getRealPath(path), ".zip.tmp", ".zip");
System.out.println(downloadUrl = path + ".zip");
return SUCCESS;
}