Exel表格文件上传_excel表如何上传闪传-CSDN博客

本文链接：https://blog.csdn.net/lamboo_cn/article/details/50964939

需要的架包，进行盘点
创建上传Exel表格jsp页面核心代码采用struts2
<form action="upload.action" method="post"
           enctype="multipart/form-data">
           <input type="file" name="uploadFile" /> <input name="submit"
               type="submit" value="导入exel" />
</form>
写出对应Action：
package com.pdsu.Action;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;
import com.pdsu.service.exelService;
import com.pdsu.util.ExcelUtil;

public class ExelAction extends ActionSupport{

   /**
   *
   */
   private static final long serialVersionUID = 6892451861166250944L;
   private static final Logger logger = Logger.getLogger(ExelAction.class);
   private HttpServletRequest request = ServletActionContext.getRequest();
   private HttpServletResponse response = ServletActionContext.getResponse();
   private File uploadFile;
   private String uploadFileFileName;
   exelService service=new exelService();
   public File getUploadFile() {
       return uploadFile;
   }
   public void setUploadFile(File uploadFile) {
       this.uploadFile = uploadFile;
   }

   public String getUploadFileFileName() {
       return uploadFileFileName;
   }
   public void setUploadFileFileName(String uploadFileFileName) {
       this.uploadFileFileName = uploadFileFileName;
   }
   @Override
   public String execute() throws Exception {
       if (uploadFile == null) {
           request.setAttribute("message", "上传的资源为空");
           return SUCCESS;
       }
       System.out.println("uploadFileFileName"+uploadFileFileName);
       Workbook wb = ExcelUtil.loadExcel(uploadFile);
       if (wb == null) {
           request.setAttribute("message", "文件类型错误，这不是一个excel文件！");
           return SUCCESS;
       }

       boolean isLegalFile = service.checkIngotFile(wb);
       if (!isLegalFile) {
           request.setAttribute("message", "这不是一个合法的会员excel文件！");
           return SUCCESS;
       }

       String result = service.checkIngotData(wb);
       if (result != null && !result.isEmpty()) {
           request.setAttribute("message", result);
           return SUCCESS;
       }
       String message="";
       int zoneid = 0;
       List<Integer> failRows = service.importIngot(wb, zoneid);
       if (failRows.size() == 0) {
           message = "导入成功！";
       } else {
           String rows = "";
           for (Integer rowNum : failRows) {
               rows = rows + rowNum + ",";
           }
           rows.substring(0, rows.length() - 1);
           message = "导入失败！失败行号：" + rows;
       }

       logger.warn(message);
       request.setAttribute("message", message);
       return SUCCESS;
   }
}
写出对应的service以及工具类ExcelUtil
service：
package com.pdsu.service;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.pdsu.bean.User;
import com.pdsu.util.ExcelUtil;
import com.pdsu.util.Validator;

public class exelService {
   private static final String RETRUN = "\\r\\n";

   private static final String[] ENAMES = new String[] { "id", "name","description"};
    private static final String[] CNAMES = new String[] { "会员ID", "会员名称", "会员描述" };
   public boolean checkIngotFile(Workbook wb) {
       Sheet sheet = wb.getSheetAt(0);

       Row enames = sheet.getRow(1);
       Row cnames = sheet.getRow(0);

       if (enames == null || cnames == null) {
           return false;
       }

       for (int i = 0; i < ENAMES.length; i++) {
           if (!ENAMES[i]
                   .equalsIgnoreCase(ExcelUtil.getStringOfRow(enames, i))) {
               return false;
           }
           if (!CNAMES[i]
                   .equalsIgnoreCase(ExcelUtil.getStringOfRow(cnames, i))) {
               return false;
           }
       }

       return true;
   }
   public String checkIngotData(Workbook wb) {
       StringBuilder message = new StringBuilder();
       Sheet sheet = wb.getSheetAt(0);
       int rowCount = sheet.getLastRowNum();//获取exel表格内数据的行数
       for (int i = 2; i < rowCount; i++) {
           Row row=sheet.getRow(i);
           try {
               int id = (int) ExcelUtil.getNumericOfRow(row, 0);
               if (id == 0) {
                   message.append(CNAMES[0] + "不合法!行号：" + (i + 1));
                   message.append(RETRUN);
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
           if(Validator.isNullOrEmpty(ExcelUtil.getStringOfRow(row, 1))){
               message.append(CNAMES[1] + "不能为空！行号：" + (i + 1));
               message.append(RETRUN);
           }
           if(Validator.isNullOrEmpty(ExcelUtil.getStringOfRow(row, 2))){
               message.append(CNAMES[2] + "不能为空！行号：" + (i + 1));
               message.append(RETRUN);
           }

       }
       return null;
   }
   public List<Integer> importIngot(Workbook wb, int zoneid) {
       List<Integer> failRows = new ArrayList<Integer>();
       List<User> userlist=new ArrayList<User>();
       User user=new User();
       Sheet sheet = wb.getSheetAt(0);
       int rowCount = sheet.getPhysicalNumberOfRows();
       for (int i = 2; i < rowCount; i++) {
           Row row = sheet.getRow(i);

           int id = (int) ExcelUtil.getNumericOfRow(row, 0);
           String name = ExcelUtil.getStringOfRow(row, 1);
           String description = ExcelUtil.getStringOfRow(row, 2);
           user.setId(id);
           user.setName(name);
           user.setDescription(description);
           System.out.println("id="+id+"name"+name+"description"+description);
           userlist.add(user);
       }

       return failRows;
   }

}
ExcelUtil：
package com.pdsu.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

   public static Workbook loadExcel(File file) {
       InputStream is = null;
       Workbook wb = null;
           try {
               is = new FileInputStream(file);
               wb = new HSSFWorkbook(is);
           } catch (Exception e) {
               e.printStackTrace();
           }finally{
               if (is != null){
                   try {
                       is.close();
                   } catch (IOException e) {
                       e.printStackTrace();
                   }
               }
       }
           return wb;

   }

   public static String getStringOfRow(Row row, int columnIndex) {
       Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);
       if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
           cell.setCellType(Cell.CELL_TYPE_STRING);
       }
       return cell.getStringCellValue();
   }
       // 获取指定行的指定数字列的值
       public static double getNumericOfRow(Row row, int columnIndex) {
           Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);

           return cell.getNumericCellValue();
       }

       // 获取指定行的指定数字列的值
       public static Date getDateOfRow(Row row, int columnIndex){
           Cell cell = row.getCell(columnIndex, Row.CREATE_NULL_AS_BLANK);

           return cell.getDateCellValue();
       }
}
最后在struts对Action进行配置