这两天用jxl简单的实现了Excel文件的导入导出,下面是我的一些总结(当然有很多是参照别人的代码)。
jsp页面代码:
- /Excel文件导入到数据库中
- function importEmp(){
- //检验导入的文件是否为Excel文件
- var excelPath = document.getElementById("excelPath").value;
- if(excelPath == null || excelPath == ''){
- alert("请选择要上传的Excel文件");
- return;
- }else{
- var fileExtend = excelPath.substring(excelPath.lastIndexOf('.')).toLowerCase();
- if(fileExtend == '.xls'){
- }else{
- alert("文件格式需为'.xls'格式");
- return;
- }
- }
- //提交表单
- document.getElementById("empForm").action="<%=request.getContextPath()%>/EmpExcel.action.EmpExcelAction.do?method=importEmployeeInfos";
- document.getElementById("empForm").submit();
- }
- <input type="file" id="excelPath" name="excelPath"/>
- <input type="button" value="导入Excel" οnclick="importEmp()"/>
action:
- /**
- * Excel中的数据导入到数据库中(Excel中的字段已限定)
- * @param mapping
- * @param form
- * @param request
- * @param response
- * @return
- * @throws Exception
- */
- public ActionForward importEmployeeInfos(ActionMapping mapping, ActionForm form,
- HttpServletRequest request, HttpServletResponse response) throws Exception {
- logger.debug(">>>importEmployeeInfos()");
- //从页面接收参数:文件的路径
- String excelPath = request.getParameter("excelPath");
- //输入流
- InputStream fis = new FileInputStream(excelPath);
- //得到解析Excel的实体集合
- List<EmployeeInfo> infos = ImportEmployee.importEmployee(fis);
- //遍历解析Excel的实体集合
- for(EmployeeInfo info:infos) {
- //判断员工编号是否存在(存在:做修改操作;不存在:做新增操作)
- EmployeeInfo info1 = this.selectEmpByEmpNum(info.getEmployeeNumber());
- if(info1 == null) {
- //把实体新加到数据库中
- this.service.addEmployeeInfo(info);
- }else{
- //把personId封装到实体
- info.setPersonId(info1.getPersonId());
- //更新实体
- this.updatEmployeeInfo(info);
- }
- }
- //关闭流
- fis.close();
- logger.debug("<<<importEmployeeInfos()");
- return this.findEmployeeInfos(mapping, form, request, response);
- }
- /**
- * 根据员工编号查找一个员工实体
- * @param employeeNumber 员工编号
- * @return 实体
- */
- private EmployeeInfo selectEmpByEmpNum(String employeeNumber) {
- logger.debug(">>>selectEmpByEmpNum(String employeeNumber)");
- EmployeeInfo employeeInfo = new EmployeeInfo();
- employeeInfo.setEmployeeNumber(employeeNumber);
- EmployeeInfo info =this.service.selectEmployeeInfoByEmpNum(employeeInfo);
- logger.debug("<<<selectEmpByEmpNum(String employeeNumber)");
- return info;
- }
- /**
- * 更新一条员工信息
- * @param employeeInfo 已封装的实体
- *
- */
- private void updatEmployeeInfo(EmployeeInfo employeeInfo) {
- logger.debug(">>>selectEmpByEmpNum(String employeeNumber)");
- this.service.updateEmployeeInfo(employeeInfo);
- logger.debug(">>>selectEmpByEmpNum(String employeeNumber)");
- }
导入类:
- public class ImportEmployee {
- /**
- * 解析Excel文件中的数据并把每行数据封装成一个实体
- * @param fis 文件输入流
- * @return List<EmployeeInfo> Excel中数据封装实体的集合
- */
- public static List<EmployeeInfo> importEmployee(InputStream fis) {
- List<EmployeeInfo> infos = new ArrayList<EmployeeInfo>();
- EmployeeInfo employeeInfo = null;
- try {
- //打开文件
- Workbook book = Workbook.getWorkbook(fis);
- //得到第一个工作表对象
- Sheet sheet = book.getSheet(0);
- //得到第一个工作表中的总行数
- int rowCount = sheet.getRows();
- //日期格式化
- DateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
- //循环取出Excel中的内容
- for (int i = 1; i < rowCount; i++) {
- employeeInfo = new EmployeeInfo();
- Cell[] cells = sheet.getRow(i);
- employeeInfo.setOrgId(Long.parseLong(cells[0].getContents()));
- employeeInfo.setEmployeeNumber(cells[1].getContents().toString());
- employeeInfo.setFullName(cells[2].getContents().toString());
- employeeInfo.setSex(cells[3].getContents().toString());
- employeeInfo.setDateOfBirth(new Date());
- employeeInfo.setTownOfBirth(cells[5].getContents().toString());
- employeeInfo.setNationalIdentifier(cells[6].getContents().toString());
- infos.add(employeeInfo);
- }
- } catch (BiffException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return infos;
- }
- }