IDE:IDEA
编程语言:JAVA
controller 类
业务处理类
model 类
Controller
import com.你的路径.ImportTaskExcel;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@CrossOrigin
@Service
public class ProjectTaskExcelController {
//从表格导入任务;
//先将excel解析为json,然后将json数据进行处理,保存到数据库
@RequestMapping(value = "/import_excel_task",headers = "content-type=multipart/*", method = RequestMethod.POST)
@ResponseBody
public Map importExcel(HttpServletRequest request, HttpServletResponse response, @RequestParam MultipartFile file) throws IOException {
String originalFilename = file.getOriginalFilename();
String type = file.getContentType();
System.out.println("目标文件名称:" + originalFilename + ",目标文件类型:" + type);
InputStream is = file.getInputStream();
ImportTaskExcel importExcel = new ImportTaskExcel();
List list= importExcel.importDataFromExcel( is, originalFilename);
for(int i = 0;i
System.out.println(list.get(i).toString());
}
Map map = new HashMap<>();
map.put("list", list);
map.put("code", 0);
return map;
}
}
业务处理类
```
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.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ImportTaskExcel {
private static final Logger logger = LoggerFactory.getLogger("ImportTaskExcel");
/**
* @Title: createWorkbook
* @Description: 判断excel文件后缀名,生成不同的workbook * @param @param is
* @param @param excelFileName
* @param @return
* @param @throws IOException
* @return Workbook
* @throws
*/
public Workbook createWorkbook(InputStream is, String excelFileName) throws IOException {
if (excelFileName.endsWith(".xls")) {
return new HSSFWorkbook(is);
}else if (excelFileName.endsWith(".xlsx")) {
return new XSSFWorkbook(is);
}
return null;
}
/**
* @Title: getSheet
* @Description: 根据sheet索引号获取对应的sheet
* @param @param workbook
* @param @param sheetIndex
* @param @return
* @return Sheet
* @throws
*/
public Sheet getSheet(Workbook workbook, int sheetIndex){
return workbook.getSheetAt(0);
}
public List importDataFromExcel(InputStream is, String excelFileName){
List list = new ArrayList();
try {
//创建工作簿
Workbook workbook = this.createWorkbook(is, excelFileName);
//创建工作表sheet
Sheet sheet = this.getSheet(workbook, 0);
//获取sheet中数据的行数
int rows = sheet.getPhysicalNumberOfRows();
//获取表头单元格个数
int cells = sheet.getRow(0).getPhysicalNumberOfCells();
if(cells > 8) {
cells = 8;
}
for (int i = 1; i < rows; i++) {//第一行为标题栏,从第二行开始取数据
//利用反射,给JavaBean的属性进行赋值
ExcelTaskRow vo = new ExcelTaskRow();
Field[] fields = vo.getClass().getDeclaredFields();
Row row = sheet.getRow(i);
int index = 0;
while (index < cells) {
Cell cell = row.getCell(index);