Java操作Excel 并 保存新Excel

/**

 * like12 add,20180306,申请表Excel

 * @param parameter

 * @return

 */

public String generateStudentApplyExcel(Map<String, Object> parameter) {

String employeeId = (String)parameter.get("employeeId");

String savePath = (String)parameter.get("savePath");

if(StringUtils.isEmpty(employeeId) || StringUtils.isEmpty(savePath)){

return "fail";

}

//查询学员

Employee emp = this.findEmployeeById(employeeId);

//在类中取得路径 

String currentPath = this.getClass().getResource("/").getPath();

//结果：F:/Java/dorado-7.3.0-win64/apache-tomcat-7.0.20/

//webapps/GS1014J_MCF/WEB-INF/classes/

currentPath = currentPath.substring(1);

//System.out.println(currentPath);

//模板文件路径

String fileInName = "StudentApply.xls";

String fileInPath = currentPath +

"com/gs/oms/view/employee/student/freemarker/template/" + fileInName;

//输出文件路径

SimpleDateFormat dfTemp = new SimpleDateFormat("yyyyMMddHHmmssSSS");

String fileOutName = dfTemp.format(new Date()) + "_" + emp.getIdCard() + ".xls";

//String fileOutPath = currentPath + "../../../temp_word/" + fileOutName;

String fileOutPath = savePath + fileOutName;//js传入地址

//读取指定的文件

File fIn = new File(fileInPath);

FileInputStream readFile = null;

HSSFWorkbook wb = null;

//打开并读取文件

try {

readFile = new FileInputStream(fIn);

wb = new HSSFWorkbook(readFile);

} catch (Exception e2) {

// TODO Auto-generated catch block

e2.printStackTrace();

}

//关闭文件

try {

readFile.close();

} catch (IOException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

//处理

HSSFSheet sheet = wb.getSheetAt(0);

//HSSFRow row = sheet.getRow(0);

/*//获取总行数

int rowNum = sheet.getLastRowNum();

System.out.println("---Size:" + rowNum);*/

/*//打印测试

HSSFCell cell = row.getCell(0);

System.out.println(cell.getRichStringCellValue());*/

//填表

//第2行 档案编号

HSSFRow row = sheet.getRow(1);

HSSFCell cell = row.getCell(24);

cell.setCellValue(emp.getOld_filenumber());

//第3行 姓名

row = sheet.getRow(2);

cell = row.getCell(2);

cell.setCellValue(emp.getEmployeeName());

//性别

String sex = emp.getEmployeeSex();

if("1".equals(sex)){

sex = "男";

}else if("0".equals(sex)){

sex = "女";

}

cell = row.getCell(13);

cell.setCellValue(sex);

//出生日期

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");

String birthDay = df.format(emp.getEmployeeBirthday());

cell = row.getCell(18);

cell.setCellValue(birthDay);

//国籍

cell = row.getCell(26);

cell.setCellValue("中国");

//第4行 身份证号

row = sheet.getRow(3);

cell = row.getCell(2);

cell.setCellValue("居民身份证");

//身份证号

String idCard = emp.getIdCard();

for(int i=0; i<18; i++){

cell = row.getCell(6 + i);

cell.setCellValue(idCard.substring(i, i + 1));

}

//第5行 居住证号

row = sheet.getRow(4);

String tempAddrNumb = emp.getTempAddrNumb();

if(!StringUtils.isEmpty(tempAddrNumb)){

cell = row.getCell(2);

cell.setCellValue("居住证");

for(int i=0; i<18; i++){

cell = row.getCell(6 + i);

cell.setCellValue(tempAddrNumb.substring(i, i + 1));

}

}

//第6行 邮寄地址

row = sheet.getRow(5);

cell = row.getCell(2);

cell.setCellValue(emp.getRegisteredResidence());

/*//第7行 电子信箱

row = sheet.getRow(6);

cell = row.getCell(15);

cell.setCellValue(emp.getEmail2());*/

//第8行 移动电话

row = sheet.getRow(7);

cell = row.getCell(2);

cell.setCellValue(emp.getPhone());

//邮政编码

cell = row.getCell(15);

cell.setCellValue(emp.getZipCode());

//第9行 申请车型

row = sheet.getRow(8);

String applyModels = stdTypeDAO.findTypenameByTypeid(emp.getApplyModels());

cell = row.getCell(6);

cell.setCellValue(applyModels);

//增驾

if(!StringUtils.isEmpty(emp.getNewDrivingLicense())){//增驾

//取消 初次申领的勾选

cell = row.getCell(1);

cell.setCellValue("口初次申领");

cell = row.getCell(11);

cell.setCellValue("□驾校培训    □有驾驶经历    □自学直考 ");

}

//初次申领

else{

//第10行 初次申领(取消 增驾的勾选)

row = sheet.getRow(9);

cell = row.getCell(1);

cell.setCellValue("口增加准驾车型");

cell = row.getCell(11);

cell.setCellValue("口驾校培训 □全日制职业教育 □最高准驾车型被注销 □自学直考");

}

//保存文件

try {

OutputStream outputStream = new FileOutputStream(fileOutPath);

wb.write(outputStream);

outputStream.flush();

outputStream.close();

} catch (FileNotFoundException e) {

System.err.println("获取不到位置");

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

}

return fileOutName;

}