java实现excel文件上传，解析，导入

最新推荐文章于 2024-09-18 14:11:48 发布

小秋蜀黍

最新推荐文章于 2024-09-18 14:11:48 发布

阅读量1.3w

点赞数 3

分类专栏： java-web 文章标签： excel导入上传

本文链接：https://blog.csdn.net/sinat_32133675/article/details/78019092

版权

java-web 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

首先要新建excel的变量，创建工作布，这里要用到poi相关jar包，我会直接上传到我的资源中

下面以面试管理的试题管理系统为例，上代码：

package it.com.excel;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.stereotype.Repository;

@Repository
public class InputOutput {

/**
 * 到入Excel文件
 */
	public List<Map> importExcel(String xlsPath){
		List<Map> qstList = new ArrayList<Map>();
	
		//需要解析的Excel文件
		File file = new File(xlsPath);
		try {
			//创建Excel，读取文件内容
			HSSFWorkbook workbook = 
				new HSSFWorkbook(FileUtils.openInputStream(file));
			//获取第一个工作表workbook.getSheet("Sheet0");
		   // HSSFSheet sheet = workbook.getSheet("Sheet0");
			//读取默认第一个工作表sheet
			HSSFSheet sheet = workbook.getSheetAt(0);
			int firstRowNum = 1;
			//获取sheet中最后一行行号
			int lastRowNum = sheet.getLastRowNum();
			
			for (int i = firstRowNum; i <=lastRowNum; i++) {
				HSSFRow row = sheet.getRow(i);
				//获取当前行最后单元格列号
				//int lastCellNum = row.getLastCellNum();
				
				//HSSFCell cell = row.getCell(i);
//					String value = cell.getStringCellValue();
//					System.out.print(value + "  ");
					
					//创建实体类
				
					Map<String, String> map = new HashMap<String, String>();
					if(row.getCell(0)!=null){
				          row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("detail",row.getCell(0).getStringCellValue());  
				          
				     }else {
				    	 map.put("detail",null); 
				     }
					if(row.getCell(1)!=null){
				          row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("A", row.getCell(1).getStringCellValue());     
				     }else {
				    	 map.put("A", null);   
				     }
					if(row.getCell(2)!=null){
				          row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("B", row.getCell(2).getStringCellValue());   
				     }else {
				    	  map.put("B",null);   
				     }
					if(row.getCell(3)!=null){
				          row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("C", row.getCell(3).getStringCellValue());				          
				     }else {
				    	 map.put("C", null);		
				     }
					if(row.getCell(4)!=null){
				          row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("D", row.getCell(4).getStringCellValue());
				     }else {
				    	 map.put("D",null);
				     }
					if(row.getCell(5)!=null){
				          row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("quesanswer", row.getCell(5).getStringCellValue());
				     }else {
				    	 map.put("quesanswer",null);
				     }
					if(row.getCell(6)!=null){
				          row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("diffculty", row.getCell(6).getStringCellValue());
				     }else {
				    	  map.put("diffculty",null);
				     }
					if(row.getCell(7)!=null){
				          row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("position", row.getCell(7).getStringCellValue());
				     }else {
				    	 map.put("position", null);
				     }
					if(row.getCell(8)!=null){
				          row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("kpoint", row.getCell(8).getStringCellValue());
				     }else {
				    	  map.put("kpoint",null);
				     }
					if(row.getCell(9)!=null){
				          row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
				          map.put("type", row.getCell(9).getStringCellValue());
				     }else {
				    	 map.put("type",null);
				     }
				
					
					qstList.add(map);
				}
				System.out.println();
			
			
		} catch (IOException e) {
			e.printStackTrace();
		}
	       return qstList;
	}
	
	//导入    .xlsx格式
	public List<?> in1(){		
		InputOutput te=new InputOutput();
		List<?> list = te.importExcel("d:\\testExcelOut.xlsx");
		return list;
	}
	//导入    .xls格式
	public List<Map> in2(String truepath){		
		InputOutput te=new InputOutput();
		List<Map> list = te.importExcel(truepath);
		return list;
	}	
}

action中实现将excel文件上传到服务器，然后在服务器中找到该文件路径，在通过路径解析，将文件内容解析进行操作

//导入excel表信息
	@RequestMapping(value="ImportExcel.action")
	public void ImportExcel(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException {
		System.out.println("进入到servlet");
		request.setCharacterEncoding("utf-8");
		//设置上传文件保存路径
		String filePath =request.getSession().getServletContext().getRealPath("/") + "excle";
		System.out.println(filePath);
		File file = new File(filePath);
		if(!file.exists()){
			file.mkdir();
		}
		
		SmartUpload su = new SmartUpload();
		//初始化对象
		su.initialize(servletConfig, request, response);
		//设置上传文件大小
		su.setMaxFileSize(1024*1024*100);
		//设置所有文件的大小
		su.setTotalMaxFileSize(1024*1024*100);
		//设置允许上传文件类型
		su.setAllowedFilesList("xls");
		String result = "上传成功！";
		//设置禁止上传的文件类型
		try {
			su.setDeniedFilesList("rar,jsp,js");
			//上传文件
			su.upload();
			
			int count = su.save(filePath);
			System.out.println("上传成功" +  count + "个文件！");
		} catch (Exception e) {
			result = "上传失败！";
			e.printStackTrace();
		}
		String filenameString ="";
		for(int i =0; i < su.getFiles().getCount(); i++){
			com.jspsmart.upload.File tempFile = su.getFiles().getFile(i);
			System.out.println("---------------------------");
			System.out.println("表单当中name属性值：" + tempFile.getFieldName());
			System.out.println("上传文件名：" + tempFile.getFieldName());
			System.out.println("上传文件长度:" + tempFile.getSize());
			System.out.println("上传文件的拓展名：" + tempFile.getFileExt());
			filenameString=tempFile.getFilePathName();
			System.out.println("上传文件的全名：" + tempFile.getFilePathName());
			System.out.println("---------------------------");
		}
				StringBuffer realpath = new StringBuffer();
		realpath.append(filePath).append("\\").append(filenameString);
		System.out.println(realpath);
		//realpath.toString().replaceAll("\\", "\\\\");
		String finalpath =realpath.toString();
		System.out.println(finalpath.replace("\\", "\\\\"));
		//String realpath = filePath+"\"+filenameString;
	/*	InputOutput test1 = new InputOutput();*/
		//test1.in2(finalpath.replace("\\", "\\\\"));
		List<Map> list=importExcel.in2(finalpath.replace("\\", "\\\\"));
        System.out.println(list);
        int count=0;
        List num=new  ArrayList();
        StringBuffer str=new StringBuffer();
        List<Map>  pos= sta.findAllPosition();
        for (Map map : pos) {
		str.append(map.get("pos"));
		}
        System.out.println("list:"+list.toString());
    	PrintWriter out =response.getWriter();
        for (Map map : list) {
        	  System.out.println("次数");
        	  System.out.println(map.get("A")==null);
        	count++;
			if(map.get("detail")==null||map.get("quesanswer")==null||map.get("diffculty")==null||map.get("position")==null||map.get("kpoint")==null||map.get("type")==null)
			{
				num.add(count);
			     continue;
			}
			
			System.out.println(str.toString());
			System.out.println(map.get("position").toString());
			System.out.println(str.toString().indexOf(map.get("position").toString()));
			if((str.toString().indexOf(map.get("position").toString()))==-1)
			{
				num.add(count);
			     continue;
			}
			System.out.println("222");
			if(map.get("type").equals("选择题"))
			{
				
				if(map.get("A")==null||map.get("B")==null||map.get("C")==null||map.get("D")==null)
				{
					 num.add(count);
					  continue;
				}
				
				if(map.get("quesanswer").toString().length()!=1||"ABCD".indexOf(map.get("quesanswer").toString())==-1)
				{
					num.add(count);
					 continue;
				}
				paper.insertAll(map.get("diffculty").toString(), map.get("position").toString(), map.get("kpoint").toString(), map.get("type").toString(), map.get("A").toString(),  map.get("B").toString(),  map.get("C").toString(),  map.get("D").toString(), map.get("detail").toString(), map.get("quesanswer").toString());
			}
			System.out.println("333");
			if(map.get("type").equals("多选题"))
			{
				
				if(map.get("A")==null||map.get("B")==null||map.get("C")==null||map.get("D")==null)
				{
					num.add(count);
					 continue;
				}
				boolean rs=Pattern.compile("^[A-D]{1,4}$").matcher(map.get("quesanswer").toString()).matches();
				if (!rs) {
					num.add(count);
					 continue;
				}

				paper.insertAll(map.get("diffculty").toString(), map.get("position").toString(), map.get("kpoint").toString(), map.get("type").toString(), map.get("A").toString(),  map.get("B").toString(),  map.get("C").toString(),  map.get("D").toString(), map.get("detail").toString(), map.get("quesanswer").toString());
			}
		
			if(map.get("type").equals("判断题"))
			{
				
				if(map.get("A")!=null||map.get("B")!=null||map.get("C")!=null||map.get("D")!=null)
				{
					num.add(count);
					 continue;
				}
				
				System.out.println(map.get("diffculty")+"-++++++++++++++++++-"+map.get("position"));
				//String diffculty,String position,String kpoint,String type,String A,String B,String C,String D,String detail,String quesanswer
				paper.insertAll(map.get("diffculty").toString(), map.get("position").toString(), map.get("kpoint").toString(), map.get("type").toString(), null,  null,  null,  null, map.get("detail").toString(), map.get("quesanswer").toString());
				
			}
		}
		JSONArray jsonArray=JSONArray.fromObject(num);
		out.print(jsonArray);
        
	}

注意在serverlet中getServletContext和getservletConfig会报错

解决办法：

1. getServletContext前面加上request.getSession()，即request.getSession().getServletContext()即可。

2.getservletConfig则需要

@Controller
public class ShitiAction implements ServletConfigAware,ServletContextAware{

@Override  
    public void setServletContext(ServletContext arg0) {  
        this.servletContext = arg0;  
    }  
    private ServletConfig servletConfig;  
    @Override  
    public void setServletConfig(ServletConfig arg0) {  
        this.servletConfig = arg0;  
    }

}