在Java web应用中,上传和解析Excel文件是一个常见的需求。你可以使用Spring框架来处理文件上传,并借助Apache POI库来解析Excel内容。以下是一个完整的示例,包括文件上传和Excel解析。
依赖配置
首先,你需要在pom.xml
中添加相关的依赖项:
添加Spring Web依赖和Apache POI依赖
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>excel-upload-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<java.version>11</java.version>
<spring.boot.version>2.5.5</spring.boot.version>
</properties>
<dependencies>
<!-- Spring Boot Starter Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${spring.boot.version}</version>
</dependency>
<!-- Apache POI for Excel parsing -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- Optional: Spring Boot DevTools for easier development -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<version>${spring.boot.version}</version>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring.boot.version}</version>
</plugin>
</plugins>
</build>
</project>
创建Controller类
创建一个控制器类,用于处理文件上传请求和解析Excel文件。
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@RestController
@RequestMapping("/api/excel")
public class ExcelUploadController {
@PostMapping("/upload")
public List<List<String>> uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
// 检查文件类型
if (!file.getOriginalFilename().endsWith(".xlsx")) {
throw new IllegalArgumentException("Invalid file type. Only .xlsx files are supported.");
}
// 解析Excel文件
List<List<String>> data = new ArrayList<>();
try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
for (Row row : sheet) {
List<String> rowData = new ArrayList<>();
for (Cell cell : row) {
rowData.add(getCellValue(cell));
}
data.add(rowData);
}
}
return data; // 返回解析后的数据
}
private String getCellValue(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
} else {
return String.valueOf(cell.getNumericCellValue());
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
return cell.getCellFormula();
default:
return "";
}
}
}
创建Spring Boot主应用类
确保你有一个Spring Boot主应用类来启动整个程序。
测试文件上传
你可以使用Postman或类似工具来测试文件上传接口。
- URL:
http://localhost:8080/api/excel/upload
- Method:
POST
- Body: form-data
- Key:
file
(type: File) - Value: Select an
.xlsx
file to upload
- Key:
总结
以上步骤展示了如何在Spring Boot中实现Excel文件的上传和解析。通过使用Spring的文件上传功能和Apache POI库,你可以轻松处理Excel文件,并将其内容解析成所需的数据结构。根据实际需求,你可以进一步扩展和优化这个示例。
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class ExcelUploadDemoApplication {
public static void main(String[] args) {
SpringApplication.run(ExcelUploadDemoApplication.class, args);
}
}
controller层代码事例:
/**
* 上传资源excel文件并进行解析
*
* @param request
* @return
* @throws Exception
*/
@RequestMapping(value = "/publish/importRes")
public ModelAndView importRes(HttpServletRequest request, MultipartFile uploadFile) throws Exception
{
AdminContext context = (AdminContext) request.getAttribute("context");
String supplierId = request.getParameter("supplierId");
Supplier supplier = resourceAO.getSupplierById(NumberUtils.parseLong(supplierId));
if (ObjectUtils.isNull(supplier))
{
Map<String, Object> returnMap = new HashMap<String, Object>(2);
returnMap.put("message", "对不起,您设置的供应商无效!");
returnMap.put("refererURL", "/resource/publish/index.jsp");
return new ModelAndView("message", "map", returnMap);
}
List<Map<String, String>> list = null;
int flag = 0; // 0:上传文件符合规范 1:上传文件大小超过2M 2:上传文件总记录超过500条
int exceed = 0;// 超过多少条=当前上传总条数-500
try
{
if (uploadFile != null && !uploadFile.isEmpty())
{
// 首先将上传的文件拷贝到服务器响应的目录中
long size = uploadFile.getSize();
// 判断文件上传大小 2M 1048576B
if (size > 2097152)
{
flag = 1;
}
else
{
byte[] data = new byte[(int) size];
InputStream input = uploadFile.getInputStream();
input.read(data);
File folder = new File(request.getSession().getServletContext().getRealPath("/") + "resource/");
if (!folder.exists())
{
folder.mkdir();
}
String uploadFileName = uploadFile.getOriginalFilename();
String ExName = uploadFileName.substring(uploadFileName.lastIndexOf("."), uploadFileName.length());
File outFile = new File(request.getSession().getServletContext().getRealPath("/") + "resource/" + context.getAdminId() + ExName);
if (!outFile.exists())
{
outFile.createNewFile();
}
FileOutputStream outStream = new FileOutputStream(outFile);
outStream.write(data);
outStream.close();
input.close();
// 对当前资源文件进行解析
list = resourceAO.importResource(context, outFile, supplier.getMemberId());
if (ObjectUtils.notEmpty(list))
{
int length = list.size();
if (length > 500)
{
flag = 2;
exceed = length - 500;
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
throw e;
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("list", list);
map.put("breed", resourceAO.getMarketBreedJson());
map.put("city", resourceAO.getCityJsons());
map.put("exceed", exceed);
return new ModelAndView("/resource/publish/batch", "map", map);
}
AO层解析文件事例:
/**
* 解析excel并返回资源信息
*
* @param context
* @param file
* @param memberId
* @return
*/
@Override
public List<Map<String, String>> importResource(AdminContext context, File file, long memberId)
{
// 最终的解析结果
List<Map<String, String>> result = null;
// 读取excel中的内容
List<Map<String, String>> excelContent = ExcelUtils.readExcel(file, context);
if (ObjectUtils.notEmpty(excelContent))
{
result = new ArrayList<Map<String, String>>(excelContent.size());
for (Map<String, String> row : excelContent)
{
Map<String, String> resourceMap = new HashMap<String, String>();
resourceMap.put("number", row.hashCode() + "");
// 如果品名为空,直接跳过
String breedName = row.get(ExcelUtils.RESOURCE_HEAD_BREED);
if (StringUtils.isTrimEmpty(breedName))
{
continue;
}
// 判断当前的品名是否非标
MarketBreed breed = baseDataService.getMarketBreedByIdOrName(breedName);
if (ObjectUtils.notNull(breed))
{
resourceMap.put("breedId", breed.getId());
resourceMap.put("breed", breed.getName());
}
else
{
breed = baseDataService.getMarketBreedByBreedAlias(breedName);
if (ObjectUtils.notNull(breed))
{
resourceMap.put("breedAlias", breedName);
resourceMap.put("breed", breed.getName());
resourceMap.put("breedId", breed.getId());
}
else
{
resourceMap.put("breed", breedName);
addTag(resourceMap, "breed");
}
}
// 规格
String spec = row.get(ExcelUtils.RESOURCE_HEAD_SPEC);
if (StringUtils.isTrimEmpty(spec) || spec.length() > 60)
{
addTag(resourceMap, "spec");
}
resourceMap.put("spec", spec);
// 材质,牌号
String material = row.get(ExcelUtils.RESOURCE_HEAD_MATERIAL);
if (StringUtils.notTrimEmpty(material) && material.length() <= 20)
{
resourceMap.put("material", material);
}
else
{
resourceMap.put("material", material);
addTag(resourceMap, "material");
}
// 品牌,产地
String brand = row.get(ExcelUtils.RESOURCE_HEAD_BRAND);
if (StringUtils.notTrimEmpty(brand) && brand.length() <= 20)
{
resourceMap.put("brand", brand);
}
else
{
resourceMap.put("brand", brand);
addTag(resourceMap, "brand");
}
// 仓库
String warehouse = row.get(ExcelUtils.RESOURCE_HEAD_WAREHOUSE);
if (StringUtils.isTrimEmpty(warehouse) || warehouse.length() > 20)
{
addTag(resourceMap, "warehouse");
}
Warehouse warehouseEntity = baseDataService.getWarehouseByKey(warehouse);
if (ObjectUtils.isNull(warehouseEntity))
{
String warehouseAlias = warehouse;
warehouse = baseDataService.getAliasWarehouse(warehouseAlias);
if (StringUtils.isTrimEmpty(warehouse))
{
resourceMap.put("warehouse", warehouseAlias);
warehouse = warehouseAlias;
}
else
{
resourceMap.put("warehouse", warehouse);
}
}
else
{
resourceMap.put("warehouse", warehouseEntity.getName());
}
// 交货地
String cityName = row.get(ExcelUtils.RESOURCE_HEAD_CITY1);
resourceMap.put("city", cityName);
City city = baseDataService.getCityByKey(cityName);
if (ObjectUtils.notNull(city))
{
resourceMap.put("cityId", city.getId());
}
else
{
if (ObjectUtils.notNull(warehouseEntity))
{
city = baseDataService.getCityByWarehouse(warehouseEntity.getName());
if (ObjectUtils.notNull(city))
{
resourceMap.put("city", city.getName());
resourceMap.put("cityId", city.getId());
}
else
{
addTag(resourceMap, "city");
}
}
else
{
addTag(resourceMap, "city");
}
}
// 计重方式weightWay 0-理重 1-过磅 2-抄码
String weightWay = row.get(ExcelUtils.RESOURCE_HEAD_WEIGHTWAY);
int weightWayValue = SteelResource.getWeightWayValueByName(weightWay);
resourceMap.put("weightWay", weightWayValue + "");
// 件重averageQty件重不大于100
String averageQty = row.get(ExcelUtils.RESOURCE_HEAD_AVERAGEQTY);
if (!(!StringUtils.isTrimEmpty(averageQty) && NumberUtils.parseDouble(averageQty) > 0 && NumberUtils.parseDouble(averageQty) < 100))
{
addTag(resourceMap, "averageQty");
}
resourceMap.put("averageQty", averageQty);
// 件数inItems
String inItems = row.get(ExcelUtils.RESOURCE_HEAD_INITEMS);
if (!(!StringUtils.isTrimEmpty(inItems) && NumberUtils.parseDouble(inItems) > 0))
{
addTag(resourceMap, "inItems");
}
resourceMap.put("inItems", (int) NumberUtils.parseDouble(inItems) + "");
// 价格onboardPrice价格不能小于1000
String onboardPrice = row.get(ExcelUtils.RESOURCE_HEAD_BASEPRICE);
if (StringUtils.isTrimEmpty(onboardPrice) || NumberUtils.parseDouble(onboardPrice) <= 0)
{
addTag(resourceMap, "basePrice");
}
resourceMap.put("basePrice", onboardPrice);
// 捆包号
String serialNo = row.get(ExcelUtils.RESOURCE_HEAD_SERIALNO);
resourceMap.put("serialNo", serialNo);
// 备注
String note = row.get(ExcelUtils.RESOURCE_HEAD_NOTE);
if (!StringUtils.isTrimEmpty(note) && note.length() <= 200)
{
resourceMap.put("note", note);
}
else
{
resourceMap.put("note", "");
}
// 表面质量
String quality = row.get(ExcelUtils.RESOURCE_HEAD_QUALITY);
if (!StringUtils.isTrimEmpty(quality) && quality.length() <= 100)
{
resourceMap.put("quality", quality);
}
else
{
resourceMap.put("quality", "");
}
// 质量标准
String standard = row.get(ExcelUtils.RESOURCE_HEAD_STANDARD);
if (!StringUtils.isTrimEmpty(standard) && ("国标".equals(standard) || "优质品".equals(standard)))
{
resourceMap.put("standard", standard);
}
// 质保书
String warranty = row.get(ExcelUtils.RESOURCE_HEAD_WARRANTY);
if (!StringUtils.isTrimEmpty(warranty) && ("有".equals(warranty) || "无".equals(warranty)))
{
resourceMap.put("warranty", warranty);
}
// 仓库类型
String warehouseType = row.get(ExcelUtils.RESOURCE_HEAD_WAREHOUSETYPE);
if (!StringUtils.isTrimEmpty(warehouseType) && ("室内库".equals(warehouseType) || "室外库".equals(warehouseType)))
{
resourceMap.put("warehouseType", warehouseType);
}
result.add(resourceMap);
}
}
return result;
}
工具类excelUtil:
package com.banksteel.resource.util;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import cn.mysteel.util.ObjectUtils;
import cn.mysteel.util.StringUtils;
import com.banksteel.admin.entity.AdminContext;
/**
* 导入文件工具类
*
* @author Rick
* @time 2013-10-21上午9:13:20
*/
public class ExcelUtils
{
private static final String RESOURCE_HEAD_FIELD = "品名,规格,牌号,产地/品牌,交货地,城市,仓库,计重方式,件重,件数,价格,底价,捆包号(选填),备注,表面质量,质量标准,质保书,仓库类型";
public static final String RESOURCE_HEAD_BREED = "品名";
public static final String RESOURCE_HEAD_SPEC = "规格";
public static final String RESOURCE_HEAD_MATERIAL = "牌号";
public static final String RESOURCE_HEAD_BRAND = "产地/品牌";
public static final String RESOURCE_HEAD_CITY1 = "交货地";
public static final String RESOURCE_HEAD_CITY2 = "城市";
public static final String RESOURCE_HEAD_WAREHOUSE = "仓库";
public static final String RESOURCE_HEAD_WEIGHTWAY = "计重方式";
public static final String RESOURCE_HEAD_AVERAGEQTY = "件重";
public static final String RESOURCE_HEAD_INITEMS = "件数";
public static final String RESOURCE_HEAD_BASEPRICE = "底价";
public static final String RESOURCE_HEAD_NOTE = "备注";
public static final String RESOURCE_HEAD_QUALITY = "表面质量";
public static final String RESOURCE_HEAD_STANDARD = "质量标准";
public static final String RESOURCE_HEAD_WARRANTY = "质保书";
public static final String RESOURCE_HEAD_WAREHOUSETYPE = "仓库类型";
public static final String RESOURCE_HEAD_SERIALNO = "捆包号(选填)";
public static List<Map<String, String>> readExcel(File excel, AdminContext context) throws ExcelParseException
{
List<Map<String, String>> listMap = null;
try
{
Workbook workbook = null;
try
{
workbook = new XSSFWorkbook(excel.getPath());
}
catch (Exception e)
{
workbook = new HSSFWorkbook(new FileInputStream(excel));
}
for (int j = 0; j < 1; j++)
{
Sheet sheet = workbook.getSheetAt(j);
Row row;
Map<String, String> rows;
if (sheet != null && sheet.getLastRowNum() > 0)
{
// 先获取sheet行数
int iRowCount = sheet.getLastRowNum() + 1;
// 获取整个表格的列数
int iColumns = 0;
for (int i = 0; i < iRowCount; i++)
{
if (sheet.getRow(i) != null)
{
int tempColumns = sheet.getRow(i).getLastCellNum();
if (tempColumns > iColumns)
{
iColumns = tempColumns;
}
}
}
Map<Integer, String> m = new HashMap<Integer, String>(iColumns);
int iRow = 0;
// 获取表头
for (iRow = 0; iRow < iRowCount; iRow++)
{
row = sheet.getRow(iRow);
for (int iCol = 0; iCol < iColumns; iCol++)
{
if (row != null)
{
String cv = getCellValue(row.getCell(iCol));
if (!"-".equals(cv) && !StringUtils.isTrimEmpty(cv))
{
String h = contain(cv.replace(" ", ""));
if (!StringUtils.isTrimEmpty(h))
{
m.put(iCol, h);
}
}
}
}
// 如果获取的m中没有品名等,则视为不是表头
if (ObjectUtils.notEmpty(m) && m.size() > 1)
{
boolean headTag = false;
for (Entry<Integer, String> e : m.entrySet())
{
if ("品名".equals(e.getValue()))
{
headTag = true;
break;
}
}
if (headTag)
{
break;
}
}
else
{
m = new HashMap<Integer, String>(iColumns);
}
}
// 判断是否取到表头
if (ObjectUtils.notEmpty(m))
{
// 判断数据是否为空
if (ObjectUtils.isEmpty(listMap))
{
listMap = new ArrayList<Map<String, String>>();
}
// 获取数据
for (int i = iRow + 1; i < iRowCount; i++)
{
row = sheet.getRow(i);
rows = new HashMap<String, String>(iColumns);
for (int iCol = 0; iCol < iColumns; iCol++)
{
if (m.containsKey(iCol) && row != null)
{
rows.put(m.get(iCol), (getCellValue(row.getCell(iCol))).replace(" ", ""));
}
}
listMap.add(rows);
}
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
if (e instanceof ExcelParseException)
{
throw new ExcelParseException(e.getMessage());
}
else
{
throw new ExcelParseException("读取Excel文档发生错误");
}
}
return listMap;
}
private static String getCellValue(Cell cell)
{
if (cell == null)
{
return "";
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
return StringUtils.isTrimEmpty(cell.getStringCellValue()) ? "-" : cell.getStringCellValue();
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
return String.valueOf(cell.getNumericCellValue());
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK)
{
return "";
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN)
{
return String.valueOf(cell.getBooleanCellValue());
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA)
{
String sValue = "";
try
{
sValue = String.valueOf(cell.getNumericCellValue());
}
catch (Exception e)
{
sValue = cell.getStringCellValue();
}
return sValue;
}
else if (cell.getCellType() == XSSFCell.CELL_TYPE_ERROR)
{
return String.valueOf(cell.getErrorCellValue());
}
else
{
return "";
}
}
/**
* 匹配表头信息
*
* @param cv
* @param headMap
* @return
*/
private static String contain(String cv)
{
String head = "";
if (RESOURCE_HEAD_FIELD.contains(cv))
{
head = cv;
if ("城市".equals(head))
{
head = "交货地";
}
if ("价格".equals(head))
{
head = "底价";
}
}
return head;
}
}
pom文件引入jar包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.7</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
导入直接解析文件事例:
@RequestMapping(value="/addStudents")
public ModelAndView getExample(HttpServletRequest request,HttpServletResponse response,MultipartFile uploadFile)
{
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=utf-8");
String id=request.getParameter("class");
Academy academy = academyService.getAcademyById(Integer.parseInt(id));
Map<String,Object> returnMap=new HashMap<String,Object>();
int count=0;
String message="";
try
{
Workbook wb = Workbook.getWorkbook(uploadFile.getInputStream());
Sheet st = wb.getSheet("Sheet1");
int row = st.getRows();
for(int i = 1;i<row;i++)
{
Cell cell0 = st.getCell(0,i); //学号
Cell cell1 = st.getCell(1,i); //姓名
Cell cell2 = st.getCell(2,i); //手机
Cell cell3 = st.getCell(3,i); //邮箱
String sid = cell0.getContents();
String name = cell1.getContents();
String phone = cell2.getContents();
String email = cell3.getContents();
if(ObjectUtils.isNull(sid)) continue;
if(ObjectUtils.isNull(name)) continue;
if(ObjectUtils.isNull(phone)) continue;
if(ObjectUtils.isNull(email)) continue;
if(ObjectUtils.isNull(id))
{
returnMap.put("message","请检查操作步骤,班级一定要选!");
break;
}
User user=new User();
user.setSid(sid);
user.setName(name);
user.setPassword("111111");
user.setType(3);
user.setAcademyid(Integer.parseInt(id));
user.setPhone(phone);
user.setEmail(email);
user.setAcademyName(academy.getName());
if(userService.validate(user.getType(), name, user.getPassword()))
{
message=user.getName()+"已经存在,请检查导入表格!";
returnMap.put("message", message);
break;
}
userService.createOrUpdate(user);
count++;
}
if("".equals(message) || message.length() ==0)
{
message="共导入"+count+"条学生记录";
}
returnMap.put("message", message);
}
catch (BiffException | IOException e)
{
e.printStackTrace();
returnMap.put("message","导入学生失败,请联系管理员!");
}
returnMap.put("refererURL","/user/importStudents.jsp");
return new ModelAndView("message","map",returnMap);
}