简介: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自定义