poi管道流的导入导出
/**
* 导入学生信息
*
* @param classid
* @param uploadFilePath
* @return
*/
public boolean uploadStudentFile(String schUuid, String classid, String uploadFilePath) {
if (StringUtils.isBlank(uploadFilePath)) {
throw new ServiceException(MessageConstants.CD50012);
}
String fileDir = globalProperty.getFileUploadPath();
// TODO
String filePath = fileDir + System.getProperty("file.separator") + uploadFilePath;
// windows
// String filePath = fileDir + "\\" + uploadFilePath;
File uploadFile = new File(filePath);
String[][] s = ExcelUtils.readExcel(uploadFile);
Map<String, Integer> numMap = new HashMap<String, Integer>();
Map<String, Integer> cardMap = new HashMap<String, Integer>();
Map<String, Integer> testNumMap = new HashMap<String, Integer>();
List<Student> students = new ArrayList<Student>();
if (s == null || s.length < 1 || !"学生姓名".equals(s[0][0]) || !"学号".equals(s[0][1]) || !"考号".equals(s[0][2])
|| !"身份证".equals(s[0][3])) {
throw new ServiceException(MessageConstants.CD50001);
}
// if (s == null || s.length < 1 || !s[0][0].equals("学生姓名")
// || !s[0][1].equals("学号") || !s[0][2].equals("考号")
// || !s[0][3].equals("身份证")) {
// throw new ServiceException(MessageConstants.CD50001);
// }
Integer fag = 0;// 判断模板数据为空
for (int i = 1; i < s.length; i++) {
if (!((s[i][0] == null || "".equals(s[i][0])) && (s[i][1] == null || "".equals(s[i][1]))
&& (s[i][2] == null || "".equals(s[i][2])) && (s[i][3] == null || "".equals(s[i][3])))) {
fag++;
Student student = new Student();
// 学生姓名
if (s[i][0] == null || "".equals(s[i][0]) || s[i][0].length() > 30) {
throw new ServiceException(MessageConstants.CD50002, new String[] { String.valueOf(i), "学生姓名" });
} else {
student.setStuName(s[i][0]);
// 学生姓名拼音
student.setPinyinAll(PinyinHelper.convertToPinyinString(s[i][0], "", PinyinFormat.WITHOUT_TONE));
// 学生姓名拼音首字母
student.setPinyinFirst(PinyinHelper.getShortPinyin(s[i][0]).substring(0, 1).toUpperCase());
}
// 学号
if (s[i][1] == null || "".equals(s[i][1]) || s[i][1].length() > 20) {
throw new ServiceException(MessageConstants.CD50002, new String[] { String.valueOf(i), "学号" });
} else {
if (!numMap.containsKey(s[i][1])) {
numMap.put(s[i][1], i);
student.setStuNum(s[i][1]);
Map<String, Object> params = Maps.newHashMap();
params.put("stuNum", s[i][1]);
// params.put("schYear", Integer.valueOf(DateUtils.getCurrentYear()));
if (studentMyBatisDao.selectStudentClassInfo(params) > 0) {
throw new ServiceException(MessageConstants.CD50007, new String[] { s[i][1],
String.valueOf(i) });
}
} else {
throw new ServiceException(MessageConstants.CD50005, new String[] { s[i][1],
String.valueOf(numMap.get(s[i][1])), String.valueOf(i) });
}
}
// 考号
if (s[i][2] != null && s[i][2].length() > 20) {
throw new ServiceException(MessageConstants.CD50002, new String[] { String.valueOf(i), "考号" });
} else {
if (!"".equals(s[i][2])) {
if (!testNumMap.containsKey(s[i][2])) {
testNumMap.put(s[i][2], i);
student.setStuTestNum(s[i][2]);
Map<String, String> map = Maps.newHashMap();
map.put("testNum", s[i][2]);
if (studentMyBatisDao.selectStuCntByTestNum(map) > 0) {
throw new ServiceException(MessageConstants.CD50008, new String[] { s[i][2],
String.valueOf(i) });
}
} else {
throw new ServiceException(MessageConstants.CD50009, new String[] { s[i][2],
String.valueOf(testNumMap.get(s[i][2])), String.valueOf(i) });
}
}
}
// 身份证号
if (s[i][3] != null && !"".equals(s[i][3])) {
if (s[i][3].length() > 18) {
throw new ServiceException(MessageConstants.CD50002, new String[] { String.valueOf(i), "身份证号" });
} else {
if (!cardMap.containsKey(s[i][3])) {
cardMap.put(s[i][3], i);
student.setStuCardid(s[i][3]);
} else {
throw new ServiceException(MessageConstants.CD50006, new String[] { s[i][3],
String.valueOf(cardMap.get(s[i][3])), String.valueOf(i) });
}
if (studentMyBatisDao.selectStuCntByCard(s[i][3]) > 0) {
throw new ServiceException(MessageConstants.CD50010, new String[] { s[i][3],
String.valueOf(i) });
}
}
}
/*
* if(s[i][3] != null && !"".equals(s[i][3]) && studentService.queryStudentCountByCard(s[i][3]) > 0) {
* addActionError(MessageConstants.E00008, new String[] { s[i][3], String.valueOf(i) }); }
*/
students.add(student);
}
}
if (fag == 0) {
throw new ServiceException(MessageConstants.CD50011);
}
// 数据验证通过
for (Student stu : students) {
// 学生表已存在此学生记录
Class cla = classJpaDao.findOne(classid);
Student dbStu = studentJpaDao.findByStuNum(stu.getStuNum());
if (null != dbStu) {
/*
* ClassstudentDto cs = new ClassstudentDto(); cs.setClassid(classId);
* cs.setStuid(studentManagerDao.queryStudentByNum (stu.getStuNum()).getStuid());
* classStudentDao.insertClassstudent(cs);
*/
Classstudent classstudent = new Classstudent();
classstudent.setClassid(classid);
classstudent.setStuid(dbStu.getGuuid());
classstudent.setSchcode(cla.getSchcode());
classstudent.setSchyear(cla.getSchyear());
classstudent.setStuid(cla.getSchoolid());
classstudent.setSchZoneCode(cla.getSchcode());
classstudent.setClassid(cla.getClassname());
classstudent.setGrade(cla.getGrade());
classstudent.setArts(cla.getArts());
classstudentJpaDao.save(classstudent);
} else {
// 学生表不存在此记录
String stuId = commonService.getSequence(Constants.SEQ_T_STUDENT, 8);
stu.setGuuid(stuId);
stu.setSchcode(cla.getSchcode());
studentMyBatisDao.insertStudent(stu);
Classstudent classstudent = new Classstudent();
classstudent.setClassid(classid);
classstudent.setStuid(stuId);
classstudent.setSchcode(cla.getSchcode());
classstudent.setSchyear(cla.getSchyear());
classstudent.setSchZoneCode(cla.getSchcode());
classstudent.setClassname(cla.getClassname());
classstudent.setGrade(cla.getGrade());
classstudent.setArts(cla.getArts());
classstudent.setSchoolid(schUuid);
classstudentJpaDao.save(classstudent);
}
}
return true;
}
//-------------------------------------------------我是分隔线--------------------------------------------------
模板导出
/**
* 学生模板导出
*/
public ExamStatistVo DownloadStudent(String classId, String schoolUuid, Integer grade) {
System.gc();
// 参数
Map<String, Object> map = Maps.newHashMap();
map.put("classId", classId);
List<Student> list=studentMyBatisDao.selectStudentDownload(map);
for(Student entity:list){
if(entity.getGrade()!=null){
entity.setGradeShow(Grade.GradeName(entity.getGrade()));
}
}
String fileName=list.get(0).getClassName();
// 文件模板路径
// String templateFileName = globalProperty.getTemplateFilePath() + File.separator
// + "STUDENT.xls";
// 生成路径
String resultFileName = globalProperty.getFileUploadPath() + File.separator + "stu_Download"
+ File.separator + "STUDENT.xls";
//检查路径
checkFileDirExist(globalProperty.getFileUploadPath() + File.separator + "stu_Download");
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("学生信息表-1");
Row row = sheet.createRow(0);
row.setHeight((short) (25 * 20));
Cell cell = row.createCell(0);
cell.setCellStyle(getTitleStyle(wb));
cell.setCellValue("学生姓名");
cell = row.createCell(1);
cell.setCellValue("学号");
cell.setCellStyle(getTitleStyle(wb));
cell = row.createCell(2);
cell.setCellValue("身份证");
cell.setCellStyle(getTitleStyle(wb));
// cell = row.createCell(3);
// cell.setCellValue("年级");
// cell.setCellStyle(getTitleStyle(wb));
// cell = row.createCell(4);
// cell.setCellValue("班级名称");
// cell.setCellStyle(getTitleStyle(wb));
for(int i=0;i<list.size();i++){
row = sheet.createRow(i+1);
row.setHeight((short) (25 * 10));
cell = row.createCell(0);
cell.setCellValue(list.get(i).getStuName());
// cell.setCellStyle(getValueStyle(wb));
cell = row.createCell(1);
cell.setCellValue(list.get(i).getStuNum());
// cell.setCellStyle(getValueStyle(wb));
cell = row.createCell(2);
cell.setCellValue(list.get(i).getStuCardid());
// cell.setCellStyle(getValueStyle(wb));
// cell = row.createCell(3);
// cell.setCellValue(list.get(i).getGradeShow());
// cell.setCellStyle(getValueStyle(wb));
// cell = row.createCell(4);
// cell.setCellValue(list.get(i).getClassName());
// cell.setCellStyle(getValueStyle(wb));
}
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
sheet.autoSizeColumn(4);
FileOutputStream fileOut = null;
try {
fileOut = new FileOutputStream(resultFileName);
wb.write(fileOut);
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
System.out.println("学生信息导出关闭异常 ");
}
}
ExamStatistVo examStatistVo = new ExamStatistVo();
examStatistVo.setPath(resultFileName);
examStatistVo.setResultFileName(fileName+".xls");
return examStatistVo;
}
/**
* 设置模板文件的输入项表格样式
* @param wb
* @return
*/
private static CellStyle getValueStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
//对齐方式设置
style.setAlignment(CellStyle.ALIGN_LEFT);
//边框颜色和宽度设置
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BROWN.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BROWN.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BROWN.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BROWN.getIndex());
return style;
}
/**
* 设置模板文件的横向表头单元格的样式
* @param wb
* @return
*/
private static CellStyle getTitleStyle(Workbook wb){
CellStyle style = wb.createCellStyle();
//对齐方式设置
style.setAlignment(CellStyle.ALIGN_CENTER);
//边框颜色和宽度设置
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//设置背景颜色
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//粗体字设置
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
// public void createExcel(String templateFileName, Map<String, Object> map, String resultFileName) {
// try {
// // 创建XLSTransformer对象
// XLSTransformer transformer = new XLSTransformer();
// // 生成Excel文件
// transformer.transformXLS(templateFileName, map, resultFileName);
// } catch (Exception e) {
// throw new RuntimeException("error happens...", e);
// }
// }
private void checkFileDirExist(String filePath) {// 判断路径是否存在
File fp = new File(filePath);
// 创建目录
if (!fp.exists()) {
fp.mkdirs();// 目录不存在的情况下，创建目录。
}
}
//-----------------------------------------controller 页面读取 ------------------------------------------
/**
* 导出学生信息
*/
@RequestMapping(value = "/studentDownload")
public ResponseEntity<byte[]> KSXL004Download(HttpServletRequest request) throws IOException,
ServletRequestBindingException {
String classId = ServletRequestUtils.getStringParameter(request, "classId");
String schoolUuid = ServletRequestUtils.getStringParameter(request, "schoolUuid");
Integer grade = ServletRequestUtils.getIntParameter(request, "grade");
ExamStatistVo examStatistVo = stuCom.getStudentDownload(classId,schoolUuid,grade);
String resultFileName = examStatistVo.getResultFileName();
//文件路径
String filePath =examStatistVo.getPath();
File file = new File(filePath);
HttpHeaders headers = new HttpHeaders();
//重新命名
String fileName = this.getFileName(request,resultFileName);
headers.setContentDispositionFormData("attachment", fileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);
}
/**
* 下载文件中文名
* @param request
* @param sourceFileName
* @return
* @throws IOException
*/
private String getFileName(HttpServletRequest request,String sourceFileName) throws IOException{
String filename = null;
//判断是否是IE11
Boolean flag= request.getHeader("User-Agent").indexOf("like Gecko")>0;
//IE11 User-Agent字符串:Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
//IE6~IE10版本的User-Agent字符串:Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.0; Trident/6.0)
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") >0||flag){
filename = URLEncoder.encode(sourceFileName, "UTF-8");//IE浏览器
}else {
//转换编码格式为utf-8,保证不出现乱码,
//这个文件名称用于浏览器的下载框中自动显示的文件名
filename = new String(sourceFileName.getBytes("UTF-8"), "iso8859-1");
//firefox浏览器
//firefox浏览器User-Agent字符串:
//Mozilla/5.0 (Windows NT 6.1; WOW64; rv:36.0) Gecko/20100101 Firefox/36.0
}
return filename;
}
