上传excel文件实现批量导入数据及导出数据为excel文件

最新推荐文章于 2024-06-12 17:22:01 发布

轮子飞了

最新推荐文章于 2024-06-12 17:22:01 发布

阅读量5.5k

点赞数 2

分类专栏： javaEE 文章标签： java poi excel

本文链接：https://blog.csdn.net/aishenla/article/details/52890235

版权

javaEE 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前段时间要做一个excel表格的上传下载功能，上网找了各种资料及源码，最后借用别人的代码及思路完成了符合自己需求的代码，总结一下excel的上传下载实质是通过第三方的类库比如poi，jxl等对excel单元格的设置与读写，通过类库的api，你可以发现各种各样的操作excel的方法，基本上只要你能在office的excel上进行的基本操作通过代码都能实现。

上传文件：

首先新建一个工具类，我命名为ImportExcelUtil，代码如下：

public class ImportExcelUtil {
	
	private final static String excel2003L =".xls";    //2003- 版本的excel
	private final static String excel2007U =".xlsx";   //2007+ 版本的excel
	
	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 * @param in,fileName
	 * @return
	 * @throws IOException 
	 */
	public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
		List<List<Object>> list = null;
		
		//创建Excel工作薄
		Workbook work = this.getWorkbook(in,fileName);
		if(null == work){
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		
		list = new ArrayList<List<Object>>();
		//遍历Excel中所有的sheet
		int s=work.getNumberOfSheets();
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if(sheet==null){continue;}
			int s2=sheet.getLastRowNum();
			//遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if(row==null||row.getFirstCellNum()==j){continue;}
				
				//遍历所有的列
				List<Object> li = new ArrayList<Object>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(this.getCellValue(cell));
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}
	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(excel2003L.equals(fileType)){
			wb = new HSSFWorkbook(inStr);  //2003-
		}else if(excel2007U.equals(fileType)){
			wb = new XSSFWorkbook(inStr);  //2007+
		}else{
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * @param cell
	 * @return
	 */
	public  Object getCellValue(Cell cell){
		Object value = null;
		DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
		
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if("General".equals(cell.getCellStyle().getDataFormatString())){
				value = df.format(cell.getNumericCellValue());
			}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
				value = sdf.format(cell.getDateCellValue());
			}else{
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}

这个工具类是支持2003和2007的，excel2003和2007所用到的包是不一样的，所以如何你的工程师maven构建的话需要在pom.xml上引入下面两个包：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

工具类写好以后，接下来就可以在具体业务逻辑实现方法里调用了，我这边有个上传的controller

public class UploadExcelControl {
	@Autowired
	DetailInfoVoService detailInfoVoService;
	/**
	 * 描述：通过传统方式form表单提交方式导入excel文件
	 * @param request
	 * @throws Exception
	 */
	@RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST})
	public  String  uploadExcel(HttpServletRequest request) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  
		System.out.println("通过传统方式form表单提交方式导入excel文件！");
		
		InputStream in =null;
		List<List<Object>> listob = null;
		MultipartFile file = multipartRequest.getFile("upfile");
		if(file.isEmpty()){
			throw new Exception("文件不存在！");
		}
 		in = file.getInputStream();
		listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
		in.close();
		//因为要导入的excel比较特殊，不是常规的一行类别，其他行都是数据，所以我需要根据具体模板来读取excel
		for (int i = 0; i < listob.size(); i+=11) {
			DetailInfoVo vo = new DetailInfoVo();
			vo.setSiteId(String .valueOf(listob.get(i).get(1)));
			vo.setSiteName(String.valueOf(listob.get(i).get(3)));
			vo.setSitePhone(String.valueOf(listob.get(i).get(5)));
			vo.setSiteAddress(String.valueOf(listob.get(i+1).get(1)));
			vo.setIsOpened(String.valueOf(listob.get(i+1).get(5)));
			vo.setSiteClass(String.valueOf(listob.get(i+2).get(1)).split("，")[0]);
			vo.setSiteScale(String.valueOf(listob.get(i+2).get(1)).split("，")[1]);
			vo.setSiteDetail(String.valueOf(listob.get(i+3).get(0)));
			vo.setIsPartnership(String.valueOf(listob.get(i+4).get(1)));
			vo.setReason(String.valueOf(listob.get(i+5).get(0)));
			vo.setBossName(String.valueOf(listob.get(i+7).get(1)));
			vo.setBossAge(String.valueOf(listob.get(i+7).get(3)));
			vo.setBossJiguan(String.valueOf(listob.get(i+7).get(5)));
			vo.setBossYear(String.valueOf(listob.get(i+8).get(1)));
			vo.setBossPeople(String.valueOf(listob.get(i+8).get(3)));
			vo.setOthers(String.valueOf(listob.get(i+9).get(1)));
			vo.setEventLog(String.valueOf(listob.get(i+10).get(0)));
			int resultCode=detailInfoVoService.insert(vo);
			if(resultCode!=0){
				System.out.println("=======");
			}
		}
		return "result";
	}
}

前台使用的是form表单提交的方式，首先在jsp页面定义一个form，action指向对应的controller

<pre name="code" class="html"><span style="white-space:pre">	</span><form class="form-inline clearfix " method="POST" enctype="multipart/form-data" id="form1" action="../uploadExcel/upload.do">
          <div class="row">
            <div class="form-group col-xs-12">
              <label class="fl">上传文件: </label>
              <input type="file" class="form-control" id="upfile" name="upfile">
            </div>
          </div>
          <hr />
        </form>

上传的内容就介绍完了，相比上传，下载更加容易。

导出excel新建一个工具类ExportExcelUtil：

public class ExportExcelUtil {
	
	/**
	 * 描述：根据文件路径获取项目中的文件
	 * @param fileDir 文件路径
	 * @return
	 * @throws Exception
	 */
	public  File getExcelDemoFile(String fileDir) throws Exception{
		String classDir = null;
		String fileBaseDir = null;
		File file = null;
		classDir = Thread.currentThread().getContextClassLoader().getResource("/").getPath();
		fileBaseDir = classDir.substring(0, classDir.lastIndexOf("classes"));
		
		file = new File(fileBaseDir+fileDir);
		if(!file.exists()){
			throw new Exception("模板文件不存在！");
		}
		return file;
	}
	
	public  Workbook writeNewExcel(File file,String sheetName,List<DetailInfoVo> lis) throws Exception{
		Workbook wb = null;
		Row row = null; 
		Cell cell = null;
		
		FileInputStream fis = new FileInputStream(file);
		wb = new ImportExcelUtil().getWorkbook(fis, file.getName());	//获取工作薄
		Sheet sheet = wb.getSheet(sheetName);
		
		//循环插入数据
		int lastRow = sheet.getLastRowNum()+1;    //插入数据的数据ROW
		CellStyle cs = setSimpleCellStyle(wb);    //Excel单元格样式
		for (int i = 0; i < lis.size(); i++) {
			row = sheet.createRow(lastRow+i); //创建新的ROW，用于数据插入
			
			//按项目实际需求，在该处将对象数据插入到Excel中
			DetailInfoVo vo  = lis.get(i);
			if(null==vo){
				break;
			}
			//Cell赋值开始
			cell = row.createCell(0);
			cell.setCellValue(vo.getSiteId());
			cell.setCellStyle(cs);
			
			cell = row.createCell(1);
			cell.setCellValue(vo.getSiteName());
			cell.setCellStyle(cs);
			
			cell = row.createCell(2);
			cell.setCellValue(vo.getSiteAddress());
			cell.setCellStyle(cs);
			
			cell = row.createCell(3);
			cell.setCellValue(vo.getSiteClass());
			cell.setCellStyle(cs);
			
			cell = row.createCell(4);
			cell.setCellValue(vo.getSiteScale());
			cell.setCellStyle(cs);
			
			cell = row.createCell(5);
			cell.setCellValue(vo.getIsPartnership());
			cell.setCellStyle(cs);
			
			cell = row.createCell(6);
			cell.setCellValue(vo.getSitePhone());
			cell.setCellStyle(cs);
			
			cell = row.createCell(7);
			cell.setCellValue(vo.getIsOpened());
			cell.setCellStyle(cs);
		}
		return wb;
	}
	
	/**
	 * 描述：设置简单的Cell样式
	 * @return
	 */
	public  CellStyle setSimpleCellStyle(Workbook wb){
		CellStyle cs = wb.createCellStyle();
		
		cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框
		cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框
		cs.setBorderTop(CellStyle.BORDER_THIN);//上边框
		cs.setBorderRight(CellStyle.BORDER_THIN);//右边框

		cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中
		
		return cs;
	}

}

具体的实现类如下

public class ExportExcelControl {
	@Autowired
	DetailInfoVoService detailInfoVoService;
	/**
	 * 描述：导出Excel
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping(value="export.do",method={RequestMethod.GET,RequestMethod.POST})
	public  String  exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
		OutputStream os = null;  
		Workbook wb = null;    //工作薄
		List<DetailInfoVo> list=detailInfoVoService.findList();
		try {
			List<DetailInfoVo> lo = new ArrayList<DetailInfoVo>();
			for (int i = 0; i < list.size(); i++) {
				//根据具体需求向excel即将导出的vo模型填充数据
				DetailInfoVo vo = new DetailInfoVo();
				vo.setSiteId(list.get(i).getSiteId());
				vo.setSiteName(list.get(i).getSiteName());
				vo.setSiteAddress(list.get(i).getSiteAddress());
				vo.setSiteClass(list.get(i).getSiteClass());
				vo.setSiteScale(list.get(i).getSiteScale());
				vo.setIsPartnership(list.get(i).getIsPartnership());
				vo.setSitePhone(list.get(i).getSitePhone());
				vo.setIsOpened(list.get(i).getIsOpened());
				lo.add(vo);
			}
			
			//导出Excel文件数据
			ExportExcelUtil util = new ExportExcelUtil();
			File file =util.getExcelDemoFile("/ExcelDemoFile/模板.xlsx");
			String sheetName="sheet1";  
			wb = util.writeNewExcel(file, sheetName,lo); 
			
			String fileName="导出信息.xlsx";
		    response.setContentType("application/vnd.ms-excel");
		    response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
		    os = response.getOutputStream();
			wb.write(os);  
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			os.flush();
			os.close();
			wb.close();
		} 
		return null;
	}


}

更详细的代码我就不贴了，主要是上传下载这个思路大家要理清，原理搞清楚了啥都会搞了，比如加特技，通过poi提供的api让你的excel更加炫酷。