java上传excel并读取excel

最新推荐文章于 2024-07-03 14:13:18 发布

jinkun520

最新推荐文章于 2024-07-03 14:13:18 发布

阅读量994

点赞数

本文链接：https://blog.csdn.net/jinkun520/article/details/49302371

版权

jsp

<%@page language="java" contentType="text/html; charset=UTF-8"
   pageEncoding="UTF-8"%>
<%@page import="net.risesoft.soa.framework.session.SessionUser"%>
<%@page import="net.risesoft.soa.framework.session.SessionConst"%>
<%@taglib prefix="s" uri="/struts-tags"%>
<%
   String path = request.getContextPath();
   String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
   SessionUser sessionUser = (SessionUser) request.getSession().getAttribute(SessionConst.USER);
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
<title>工资导入</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<style type="text/css">
body {
   font-size: 14px;
}

input {
   vertical-align: middle;
   margin: 0;
   padding: 0
}

.file-box {
   padding-left:340px;
   text-align:center;
   position: relative;
   width: 340px
}

.txt {
   height: 22px;
   border: 1px solid #cdcdcd;
   width: 180px;
}

.btn {
   background-color: #FFF;
   border: 1px solid #CDCDCD;
   height: 24px;
   width: 70px;
}

.file {
   position: absolute;
   top: 0;
   right: 80px;
   height: 24px;
   filter: alpha(opacity : 0);
   opacity: 0;
   width: 260px
}
</style>
</head>
<body>
   <div style="height:100px;"></div>
   <div class="file-box">
       <form id="importForm" method="post" action="config_execute.action" enctype="multipart/form-data">
           <input type='text' name='fileName' id='textfield' class='txt' />
           <input type='button' class='btn' value='浏览...' />
           <input type="file" name="file" class="file" required=true size="28" οnchange='$("#textfield").val(this.value)' />
           <input type="submit" name="submit" class="btn" value="保存" onClick="return save2Form();"/>
       </form>
       <div style="height:10px;"></div>
       <p><b style="color:red;">注释：</b><span style="color:red;">请严格按照模版规格上传。</span></p>
   </div>
</body>
<script type="text/javascript">
   function save2Form() {
       var flag = $('#importForm').form('validate');
       if (flag) {
           true;
       } else {
           false
       }
   };
</script>
</html>

action

    private File file;

   private String fileFileName;

   private String fileContentType;

    @Override
   public String execute() throws Exception {
       _isalaryConfig.setExcelDate(file, getUser().getPerson().getTenantID());
       write(getJson(true, "工资导入成功!"));
       return SUCCESS;
   }

impl

@Transactional
   public void setExcelDate(File file, String tenantID) throws Exception {
       // 取得单位工资条配置信息
       List<ExcelDate> excelDateList = new ArrayList<ExcelDate>();
       SalaryConfig salaryConfig = getSalaryConfig(tenantID);
       // 从文件流中获取Excel工作区对象（WorkBook)
       Workbook wb = Workbook.getWorkbook(file);
       if (salaryConfig != null) {
           excelDateList = readExcel(wb, tenantID, salaryConfig.getNameLine(), salaryConfig.getCodeLine(), salaryConfig.getYearLine(), salaryConfig.getMonthLine());
       } else {
           excelDateList = readExcel(wb, tenantID, 0, 1, 2, 3);
       }
       _excelDateDao.save(excelDateList);
   }

   /**
   *
   * @Title: readExcel
   * @Description: 读取excel返回list
   * @param @param wb
   * @param @param tenantID
   * @param @param nameColumn
   * @param @param codeColumn
   * @param @param yearColumn
   * @param @param monthColumn
   * @param @return
   * @param @throws Exception    设定文件
   * @return List<ExcelDate>    返回类型
   * @throws
   */
   @Transactional
   private List<ExcelDate> readExcel(Workbook wb, String tenantID, int nameColumn, int codeColumn, int yearColumn, int monthColumn) throws Exception {
       List<ExcelDate> excelDateList = new ArrayList<ExcelDate>();
       // 从工作区中取得页（Sheet）
       Sheet sheet = wb.getSheet(0);
       for (int i = 1; i < sheet.getRows(); i++) {
           ExcelDate excelDate = new ExcelDate();

           Cell cellPersonName = sheet.getCell(nameColumn, i);
           Cell cellPersonCode = sheet.getCell(codeColumn, i);
           Cell cellSalaryYear = sheet.getCell(yearColumn, i);
           Cell cellSalaryMonth = sheet.getCell(monthColumn, i);

           excelDate.setPersonCode(cellPersonCode.getContents());
           excelDate.setPersonName(cellPersonName.getContents());
           excelDate.setSalaryYear(cellSalaryYear.getContents());
           excelDate.setSalaryMonth(cellSalaryMonth.getContents());
           excelDate.setTenantID(tenantID);
           // 循环打印Excel表中的内容
           StringBuffer sb = new StringBuffer();
           for (int j = 0; j < sheet.getColumns(); j++) {
               Cell cell = sheet.getCell(j, i);
               Cell cellt = sheet.getCell(j, 0);
               sb.append(cellt.getContents()).append(",").append(cell.getContents()).append(";");
           }
           excelDate.setSalaryDetails(sb.toString());
           excelDateList.add(excelDate);
       }
       return excelDateList;

   }