上传excel Java后端读取文件

简介:Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。

1.pom.xml

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.9</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>

2.后端代码:
package cn.com.lnyun.yq.controller;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import cn.com.lnyun.yq.datasource.SaveArt;
import cn.com.lnyun.yq.entity.Article;
import cn.com.lnyun.yq.entity.Scheme;
import cn.com.lnyun.yq.params.Result;
import cn.com.lnyun.yq.service.ArticleService;
import cn.com.lnyun.yq.service.SchemeService;
import cn.com.lnyun.yq.tools.SimHashTool;

@RestController
@RequestMapping("/art")
public class ExcelArtController {
	
	@Autowired
	private SaveArt saveArtUtil;
	
	@Autowired
	private ArticleService articleService;
	
	@Autowired
	private SchemeService schemeService;
	
	@PostMapping("/excel")
	public Result uploadExcelArt(@RequestParam MultipartFile file) throws IOException {
		System.out.println(file);
		long starttime = System.currentTimeMillis();
		String fileName = file.getOriginalFilename();
		String schemeStr = fileName.substring(0, fileName.indexOf("."));
		String fileType = fileName.substring(fileName.indexOf(".") + 1);
//		System.out.println("fileType --- >" + fileType);
//		System.out.println("schemeStr---->" + schemeStr);
		QueryWrapper<Scheme> queryWrapper = new QueryWrapper<Scheme>();
		queryWrapper.eq("scheme", schemeStr);
		Scheme scheme = schemeService.getScheme(queryWrapper);
		if(scheme == null) {
			return new Result(500, "文件名称请对应类型!");
		}	
		Workbook workbook =null;
		
		
		//把MultipartFile转化为File 第一种
		CommonsMultipartFile cmf= (CommonsMultipartFile)file;
		DiskFileItem dfi=(DiskFileItem) cmf.getFileItem();
		File fo=dfi.getStoreLocation();
 
		//创建Excel，读取文件内容
//		workbook = WorkbookFactory.create(file.getInputStream());
//		  Sheet hssfSheet = workbook.getSheetAt(0);  //示意访问sheet
		
		try {
			workbook = WorkbookFactory.create(file.getInputStream());
		} catch (InvalidFormatException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
 
		//获取第一个工作表
		Sheet sheet = workbook.getSheetAt(0);
		
		//获取sheet中第一行行号
		int firstRowNum = sheet.getFirstRowNum();
		//获取sheet中最后一行行号
		int lastRowNum = sheet.getLastRowNum();
		System.out.println(firstRowNum);
		System.out.println(lastRowNum);
		
		List<Article> arts = new LinkedList<Article>();
		
		try {
			//循环插入数据
			for(int i=firstRowNum+1;i<=lastRowNum;i++){
				Row row = sheet.getRow(i);
				
				Article art = new Article();
				
				Cell news_title = row.getCell(1);
				if(news_title!=null){
					news_title.setCellType(Cell.CELL_TYPE_STRING);
					art.setNewsTitle((news_title.getStringCellValue()));
				}
				
				Cell news_url = row.getCell(3);
				if(news_url!=null){
					news_url.setCellType(Cell.CELL_TYPE_STRING);
					art.setNewsUrl((news_url.getStringCellValue()));
				}
				
				Cell media_name = row.getCell(4);
				if(media_name!=null){
					media_name.setCellType(Cell.CELL_TYPE_STRING);
					art.setMediaName((media_name.getStringCellValue()));
				}
				
				Cell news_posttime = row.getCell(5);
				if(news_posttime!=null){
					news_posttime.setCellType(Cell.CELL_TYPE_STRING);
					art.setNewsPosttime(news_posttime.getStringCellValue()+":00");
				}else {
					return new Result(502, "表格中发送时间字段不能为空!或者其他数据类型");
				}
				Cell media_type = row.getCell(6);
				if(media_type!=null){
					media_type.setCellType(Cell.CELL_TYPE_STRING);
					art.setMediaType(media_type.getStringCellValue());
				}
				Cell mood_type = row.getCell(8);
				if(mood_type!=null){
					mood_type.setCellType(Cell.CELL_TYPE_STRING);
					art.setMoodType(mood_type.getStringCellValue());
				}
				Cell news_degist = row.getCell(9);
				if(news_degist!=null){
					news_degist.setCellType(Cell.CELL_TYPE_STRING);
					art.setNewsDigest(news_degist.getStringCellValue());
				}
				Cell news_postion = row.getCell(10);
				if(news_postion!=null){
					news_postion.setCellType(Cell.CELL_TYPE_STRING);
					art.setNewsPosition(news_postion.getStringCellValue());
				}
				
				art.setScheme(scheme.getSid());
				art.setSimHash(new SimHashTool(art.getNewsTitle()).getSimHash().toString());
				arts.add(art);
				Boolean flag = saveArtUtil.saveArt(art);
//				if(!flag) {
//					return new Result(503, "上传失败!");
//				}
			}
//			articleService.saveList(arts);
			long endtime = System.currentTimeMillis();
//			System.out.println(endtime - starttime);
		} catch (Exception e) {
			e.printStackTrace();
		} 
//		finally {
//			workbook.close();
//		}
		return new Result(200, "上传成功!");
	}
}

3.修改配置:
如果上传的文件大小超过1M,前端部署在nginx,需要在nginx的配置文件location下加入

client_max_body_size xxm; xx自定义

如果仍然报错application.properties下
#配置文件上传

spring.http.multipart.max-file-size=xxMb
spring.http.multipart.maxRequestSize=xxMb xx自定义