Java web实现Excle导入详细案列

最新推荐文章于 2024-05-17 11:22:12 发布

flqchl

最新推荐文章于 2024-05-17 11:22:12 发布

阅读量2.2k

点赞数

本文链接：https://blog.youkuaiyun.com/flqchl/article/details/49176591

版权

该案例通过Ajax、SpringMVC和POI库实现Java Web应用中的Excel导入功能。详细介绍了获取文件完整路径、处理文件路径乱码、使用Ajax发起请求、验证文件格式以及前端与后台交互的步骤。此外，还提供了JavaScript函数和Java Controller、DAO实现的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

说明：该案列是基于Ajax、SpringMVC进行的，Excele操作采用POI方式，主要目的在于分享在功能实现过程中遇到的各种问题及解决方法。

1、input标签file类型获取选择文件完整路径（直接以.value方式获取到的路径不是完整路径）：

传入参数为input对象

获取：var filepath = getFullPath(fileObj);//文件路径包括文件名

2、路径由jsp页面到后台的乱码问题：

选择的文件的完整路径中会包含中文，如果不进行处理传到后台会出现中文乱码。

解决方式：在jsp页面对路径进行两次编码，在后台再进行解码。

两次编码：var file = encodeURI(encodeURI(filepath));//解决文件路径乱码

后台解码：String file = request.getParameter("file");
file = URLDecoder.decode(file, "UTF-8");

3、Ajax发起请求（也可以采用其它方式发起请求）：

$.ajax({
      async : false,
      cache:false,
      type: 'POST',
      dataType : "text",
      url: "<%=request.getContextPath()%>/queryService/importdata/importfile?sjlx="+sjlx+"&file="+file,
      error: function() {//请求失败处理函数
       alert('导入数据发生异常！');
      },
      success:function(data){ //请求成功后处理函数
          if(data =="" || data == null){
           alert('数据导入成功！');
          }else{
           alert(data);
          }
   }
    });

注意：dataType类型应该与后台返回的数据类型保持一致，否则会报错。

4、判断选择文件的文件格式：

var file = document.getElementById("drfile").value;//文件路径包括文件名
var fileext = file.replace(/.+\./,"");//获取选中的文件的格式
if(fileext!="xls"&&fileext!="xlsx"){
alert("请选择正确的格式的文件！");
return false;
}

5、

jsp页面：

<div id="root" class="easyui-layout" data-options="fit:true" style="padding:4px">
    <div id="filter" data-options="region:'north',title:'数据导入'" data-bind="show:elements.length>0" style="padding:8px;background-color:#F9F7F4; height:150px" >
    <ntais:form action="/login.do?method=login">
        <table width="100%" cellpadding="0" cellspacing="4">
            <tr>
    <td class="td_title" align="right" width="15%">数据类型:</td>
    <td class="td_normal" width="35%">
     <ntais:codeList codeListName="T_SYS_DRDZB" catalog="" id="DRID" name="string(SJLXMC)" defaultshow="--请选择--" style="width: 60%;" >
     </ntais:codeList>
     <span style="color:red">*</span>
    </td>
    <td class="td_title" align="right" width="15%">选择文件:</td>
    <td class="td_normal" width="35%">
     <input type="file" id="drfile" style="width: 60%;"/>
     <a href="#" οnclick="download()">模板下载</a>
    </td>
   </tr>
   <tr>
    <td class="td_normal" align="right" colspan="2">
     <input type="button" id="dr" value="导入" οnclick="importfile()" class="button_normal"/>
     <input type="button" id="qk" value="清空" onClick="reset()" class="button_normal"/>
                </td>
   </tr>
        </table>
        </ntais:form>
</div>
</div>

//模板下载
function download(){
var sjlx = document.getElementsByName("string(SJLXMC)")[0].value;//导入数据类型
if(sjlx == "" || sjlx == null){
  alert("请选择需要下载模板的数据类型！");
  return;
}else{
  var frm = document.forms(0);
  frm.action = "<%=request.getContextPath()%>/queryService/importdata/download?sjlx="+sjlx;
  frm.submit();
}
}
//判断条件
function valiCondition(){
var sjlx = document.getElementsByName("string(SJLXMC)")[0].value;//导入数据类型
//var rqSel = document.getElementById("rqSel").value;//按期导入
var file = document.getElementById("drfile").value;//文件路径包括文件名
//获取选中的文件的格式
var fileext = file.replace(/.+\./,"");
if (sjlx == "") {
  alert("请选择导入数据的类型！");
  return false;
}
if(file == ""){
  alert("请选择导入数据的文件！");
  return false;
}
if(fileext!="xls"&&fileext!="xlsx"){
  alert("请选择正确的格式的文件！");
  return false;
}
return true;
}
function reset(){
window.location.href = window.location.href;
}

</script>

controller类：

@Controller
@RequestMapping("/importdata")
public class ImportFileController {

@Autowired
private ImportFileService importfileservice;

@RequestMapping("/openPage")
public String openPage(HttpServletRequest request, Model model) throws Exception{
  return "common/importdata/importdata";
}

@RequestMapping("/importfile")
@ResponseBody
public String importfile(HttpServletRequest request, Model model) throws Exception{
  User user = (User) UniConfigHelper.getUniUserInfo(request);
  String lrry_dm = user.getSwjgj_bm();
  String sjlx = request.getParameter("sjlx");
  String file = request.getParameter("file");
  file = URLDecoder.decode(file, "UTF-8");
  return importfileservice.importfile(sjlx, file,lrry_dm);
}

@RequestMapping("download")
@ResponseBody
public void download(HttpServletRequest request, HttpServletResponse response) throws Exception{
  String sjlx = request.getParameter("sjlx");
  String filepath = "";//文件路径
  String filename = "";//下载时保存的文件名
  if("db_dw.t_jh_ndjh".equals(sjlx)||"db_dw.t_jh_ndjh"==sjlx){
   filepath = "/templates/importTemplates/temp_ndjhdr.xls";
   filename = "年度计划导入模板";
  }if("db_dw.t_jh_ydjh".equals(sjlx)||"db_dw.t_jh_ydjh"==sjlx){
   filepath = "/templates/importTemplates/temp_ydjhdr.xls";
   filename = "月度计划导入模板";
  }
  this.fileDownload(request, response, filepath, filename);
}

public void fileDownload(HttpServletRequest request, HttpServletResponse response,String filepath, String filename) throws Exception{
  //获取文件路径
  String file = request.getSession().getServletContext().getRealPath(filepath);
  //创建file
  File fileload = new File(file);
  //下载文件操作
  POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileload));
  HSSFWorkbook wb = new HSSFWorkbook(fs);
  response.setContentType("application/vnd.ms-excel");// 定义导出格式为excel
  response.setHeader("Content-Disposition","attachment;filename=" + new String(filename.getBytes("GBK"), "ISO-8859-1") + ".xls");
  OutputStream excelTarget = response.getOutputStream();
  try {
   wb.write(excelTarget);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   excelTarget.close();
  }
}
}

dao接口实现类：

@Repository("importfiledao")
public class ImportFileDaoImpl extends JdbcDaoSupport implements ImportFileDao {

@Override
public String importfile(String sjlx, String file,String lrry_dm){
  //异常返回信息
  String message ="";
  //取得文件后缀，判断导入excle版本
  String prefix = file.substring(file.lastIndexOf(".")+1);
  //年度计划导入
  if(sjlx.equals("db_dw.t_jh_ndjh") || sjlx == "db_dw.t_jh_ndjh"){
   try {
    message = this.importNdjh(file,lrry_dm,prefix);
   } catch (Exception e) {
    message = "数据导入发生异常，请检查导入模板是否与选择条件一致！";
    e.printStackTrace();
   }
  }
  //月度计划导入
  if(sjlx.equals("db_dw.t_jh_ydjh") || sjlx == "db_dw.t_jh_ydjh"){
   try {
    this.importYdjh(file, lrry_dm,prefix);
   } catch (Exception e) {
    message = "数据导入发生异常，请检查导入模板是否与选择条件一致！";
    e.printStackTrace();
   }
  }
  return message;
}

/**
* 导入年度计划
* @param file
* @param lrry_dm
* @throws Exception
*/
public String importNdjh(String file,String lrry_dm,String prefix) throws Exception{
  String message = "";
  Date date = new Date();//操作时间
  //插入记录sql
  String sqlinsert = "INSERT INTO DB_DW.T_JH_NDJH " +
       "(NF,GLJG_DM,RKJH_JE,ZYJJH_JE,SJJH_JE,SGXJH_JE,SGDJH_JE,DSJJH_JE,LR_SJ,LRR)" +
       "VALUES(?,?,?,?,?,?,?,?,?,?)";
  //更新记录sql
  String sqlupdate = "UPDATE DB_DW.T_JH_NDJH T SET T.RKJH_JE = ? , T.ZYJJH_JE = ?,"+
             "T.SJJH_JE = ? , T.SGXJH_JE = ?,T.SGDJH_JE = ? , T.DSJJH_JE = ? ,T.XG_SJ = ? ,T.XGR = ?" +
             "WHERE T.NF = ? AND T.GLJG_DM = ?";
  //初始化数据对象
  NdjhdrBean ndjhdrbean = null;
  Workbook book = null;//初始化book
  if("xls"==prefix||"xls".equals(prefix)){
   InputStream is = new FileInputStream(file);
   POIFSFileSystem fs = new POIFSFileSystem(is);
   book = new HSSFWorkbook(fs);
  }
  if("xlsx"==prefix||"xlsx".equals(prefix)){
   book = new XSSFWorkbook(file);
  }
  Sheet sheet = book.getSheetAt(0);;//获得sheet
  Row row = sheet.getRow(0);//获得row
  int rowNum = sheet.getLastRowNum();//获得行数
  int colNum = row.getPhysicalNumberOfCells();//获得列数
  //循环操作excle每一行数据
  for (int i = 1; i <= rowNum; i++) {
   row = sheet.getRow(i);
   ndjhdrbean = new NdjhdrBean();
   for (int j = 0; j < colNum; j++) {
    ndjhdrbean.setNf(getStringCellValue(row.getCell(0)));
    ndjhdrbean.setGljgDm(getStringCellValue(row.getCell(1)));
    ndjhdrbean.setRkjhJe(Double.parseDouble(getStringCellValue(row.getCell(2))));
    ndjhdrbean.setZyjjhJe(Double.parseDouble(getStringCellValue(row.getCell(3))));
    ndjhdrbean.setSjjhJe(Double.parseDouble(getStringCellValue(row.getCell(4))));
    ndjhdrbean.setSgxjhJe(Double.parseDouble(getStringCellValue(row.getCell(5))));
    ndjhdrbean.setSgdjhJe(Double.parseDouble(getStringCellValue(row.getCell(6))));
    ndjhdrbean.setDsjjhje(Double.parseDouble(getStringCellValue(row.getCell(7))));
   }
   String sql = "SELECT T.GLJG_DM FROM DB_DW.T_JH_NDJH T WHERE T.GLJG_DM = '"+ndjhdrbean.getGljgDm()+"'"+"AND T.NF = '"+ndjhdrbean.getNf()+"'";
   List<JSONObject> resultlist = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>(){
    @Override
             public JSONObject mapRow(ResultSet rs, int index) throws SQLException {
                 JSONObject jhlrObj = new JSONObject();
                 return jhlrObj;
             }
    });
   if(resultlist.size()<=0){//记录不存在则插入
    com.neusoft.unieap.util.DBAccessHelper.getPMByName("YWSJ").executeUpdate(sqlinsert,new Object[]{
      ndjhdrbean.getNf(),
      ndjhdrbean.getGljgDm(),
      ndjhdrbean.getRkjhJe(),
      ndjhdrbean.getZyjjhJe(),
      ndjhdrbean.getSjjhJe(),
      ndjhdrbean.getSgxjhJe(),
      ndjhdrbean.getSgdjhJe(),
      ndjhdrbean.getDsjjhje(),
      date,
      lrry_dm
    });
   }else{//记录存在则更新
    message = "您导入的数据已存在！";
    com.neusoft.unieap.util.DBAccessHelper.getPMByName("YWSJ").executeUpdate(sqlupdate,new Object[]{
      ndjhdrbean.getRkjhJe(),
      ndjhdrbean.getZyjjhJe(),
      ndjhdrbean.getSjjhJe(),
      ndjhdrbean.getSgxjhJe(),
      ndjhdrbean.getSgdjhJe(),
      ndjhdrbean.getDsjjhje(),
      date,
      lrry_dm,
      ndjhdrbean.getNf(),
      ndjhdrbean.getGljgDm()
    });
   }

  }
  return message;
}

/**
* 导入月度计划
* @param file
* @param lrry_dm
* @throws Exception
*/
public String importYdjh(String file,String lrry_dm,String prefix) throws Exception{
  String message = "";
  Date date = new Date();//操作时间
  //插入记录sql
  String sqlinsert = "INSERT INTO DB_DW.T_JH_YDJH " +
       "(NY,GLJG_DM,RKJH_JE,ZYJJH_JE,SJJH_JE,SGXJH_JE,SGDJH_JE,DSJJH_JE,LR_SJ,LRR) " +
       "VALUES(?,?,?,?,?,?,?,?,?,?)";
  //更新记录sql
  String sqlupdate = "UPDATE DB_DW.T_JH_YDJH T SET T.RKJH_JE = ? , T.ZYJJH_JE = ?,"+
             "T.SJJH_JE = ? , T.SGXJH_JE = ?,T.SGDJH_JE = ? , T.DSJJH_JE = ? ,T.XG_SJ = ? ,T.XGR = ?" +
             "WHERE T.NY = ? AND T.GLJG_DM = ?";
  YdjhdrBean ydjhdrbean = null;//初始化数据对象

  Workbook book = null;//初始化book
  if("xls"==prefix||"xls".equals(prefix)){
   InputStream is = new FileInputStream(file);
   POIFSFileSystem fs = new POIFSFileSystem(is);
   book = new HSSFWorkbook(fs);
  }
  if("xlsx"==prefix||"xlsx".equals(prefix)){
   book = new XSSFWorkbook(file);
  }
  Sheet sheet = book.getSheetAt(0);;//获得sheet
  Row row = sheet.getRow(0);//获得row
  int rowNum = sheet.getLastRowNum();//获得行数
  int colNum = row.getPhysicalNumberOfCells();//获得列数
  //循环操作excle每一行数据
  for (int i = 1; i <= rowNum; i++) {
   row = sheet.getRow(i);
   ydjhdrbean = new YdjhdrBean();
   for (int j = 0; j < colNum; j++) {
    if(getStringCellValue(row.getCell(0)).length()!=6){
     throw new Exception();
    }
    ydjhdrbean.setNy(getStringCellValue(row.getCell(0)));
    ydjhdrbean.setGljgDm(getStringCellValue(row.getCell(1)));
    ydjhdrbean.setRkjhJe(Double.parseDouble(getStringCellValue(row.getCell(2))));
    ydjhdrbean.setZyjjhJe(Double.parseDouble(getStringCellValue(row.getCell(3))));
    ydjhdrbean.setSjjhJe(Double.parseDouble(getStringCellValue(row.getCell(4))));
    ydjhdrbean.setSgxjhJe(Double.parseDouble(getStringCellValue(row.getCell(5))));
    ydjhdrbean.setSgdjhJe(Double.parseDouble(getStringCellValue(row.getCell(6))));
    ydjhdrbean.setDsjjhje(Double.parseDouble(getStringCellValue(row.getCell(7))));

   }
   String sql = "SELECT T.GLJG_DM FROM DB_DW.T_JH_YDJH T WHERE T.GLJG_DM = '"+ydjhdrbean.getGljgDm()+"'"+"AND T.NY = '"+ydjhdrbean.getNy()+"'";
   List<JSONObject> resultlist = this.getJdbcTemplate().query(sql, new RowMapper<JSONObject>(){
    @Override
             public JSONObject mapRow(ResultSet rs, int index) throws SQLException {
                 JSONObject jhlrObj = new JSONObject();
                 return jhlrObj;
             }
    });
   if(resultlist.size()<=0){//记录不存在则插入
    com.neusoft.unieap.util.DBAccessHelper.getPMByName("YWSJ").executeUpdate(sqlinsert,new Object[]{
      ydjhdrbean.getNy(),
      ydjhdrbean.getGljgDm(),
      ydjhdrbean.getRkjhJe(),
      ydjhdrbean.getZyjjhJe(),
      ydjhdrbean.getSjjhJe(),
      ydjhdrbean.getSgxjhJe(),
      ydjhdrbean.getSgdjhJe(),
      ydjhdrbean.getDsjjhje(),
      date,
      lrry_dm
    });
   }else{//记录存在则更新
    message = "您导入的数据已存在！";
    com.neusoft.unieap.util.DBAccessHelper.getPMByName("YWSJ").executeUpdate(sqlupdate,new Object[]{
      ydjhdrbean.getRkjhJe(),
      ydjhdrbean.getZyjjhJe(),
      ydjhdrbean.getSjjhJe(),
      ydjhdrbean.getSgxjhJe(),
      ydjhdrbean.getSgdjhJe(),
      ydjhdrbean.getDsjjhje(),
      date,
      ydjhdrbean.getXgr(),
      ydjhdrbean.getNy(),
      lrry_dm
    });
   }

  }
  return message;
}

/**
     * 获取单元格数据内容为字符串类型的数据
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(Cell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(new DecimalFormat("#").format(cell.getNumericCellValue()));
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

}