【无标题】

小学生求早恋

于 2024-01-30 23:22:29 发布

阅读量1.3k

点赞数 30

文章标签： excel

本文链接：https://blog.csdn.net/weixin_44713941/article/details/135943530

版权

文章详细描述了如何使用Java的ApachePOI库在SpringMVC控制器中处理上传的Excel文件，包括解析Excel、合并单元格、读取和保存数据到数据库的过程。

摘要由CSDN通过智能技术生成

Controller
@PostMapping(“/import”)
public String importEx(@RequestPart(“file”) MultipartFile file) throws IOException{
try {
//解析Excel,根据Excel文件创建工作簿
//传入输入流的对象InputStream,获取Workbook
//Workbook工作簿的高级表现形式，是sheet的上级对象。一个excel就是一个工作簿，一个工作簿含有多个工作表(sheet)
Workbook wb = WorkbookFactory.create(file.getInputStream());
//1.2.获取Sheet
Sheet sheet = wb.getSheetAt(0);//参数：索引
//row函数的含义,返回所选择的某一个单元格的行数。
Row row;
//合并单元格处理,获取合并行
List cras = io.renren.common.utils.PoiExcelUtils.getCombineCell(sheet);
//getPhysicalNumberOfRows()获得的实际行数，不一定有数据的行数。
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
//sheet.getRow(i)获取第i行的所有数据
Row BigRow = sheet.getRow(i);
if (BigRow == null) {
break;
}
row = sheet.getRow(i);
//判断指定的单元格是否是合并单元格
if (io.renren.common.utils.PoiExcelUtils.isMergedRegion(sheet, i, 0)) {
//sheet.getRow(i).getCell(0)获取第i行第1个格
int lastRow = io.renren.common.utils.PoiExcelUtils.getRowNum(cras, sheet.getRow(i).getCell(0), sheet);
for (; i <= lastRow; i++) {
row = sheet.getRow(i);
// 判断该行第1列是合并单元格
if (io.renren.common.utils.PoiExcelUtils.isMergedRegion(sheet, i, 0)) {
int lastRow2 = io.renren.common.utils.PoiExcelUtils.getRowNum(cras, sheet.getRow(i).getCell(0), sheet);
for (; i <= lastRow2; i++) {
Row nextRow = sheet.getRow(i);
String id = io.renren.common.utils.PoiExcelUtils.getCellString(BigRow.getCell(0));
//实体类
Student target = new Student();
target.setId(id);
buildTarget(row, BigRow, nextRow, target);
studentService.save(target);
}
} else {
Student target = new Student();
buildTarget(row, BigRow, row, target);
studentService.save(target);
}
}
i–;
} else {
row = sheet.getRow(i);
Student target = new Student();
buildTarget(row, BigRow, row, target);
studentService.save(target);
}
}
return “导入成功”;
}catch (IOException | InvalidFormatException e){
e.printStackTrace();
return “导入失败”;
}
}
private void buildTarget(Row row, Row BigRow, Row nextRow, Student target) {
//下标从0开始读取
/* String id = io.renren.common.utils.PoiExcelUtils.getCellString(BigRow.getCell(0)); // 姓名
target.setId(id);*/
String name = io.renren.common.utils.PoiExcelUtils.getCellString(BigRow.getCell(1)); // 性别(1:男2:女)
target.setName(name);

    String age = io.renren.common.utils.PoiExcelUtils.getCellString(BigRow.getCell(2));
    target.setAge(Integer.parseInt(age));

    String sex = io.renren.common.utils.PoiExcelUtils.getCellString(BigRow.getCell(3));
    target.setSex(sex);


}

PoiExcelUtils
package io.renren.common.utils;

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.CellType;
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.util.CellRangeAddress;

/**

功能: poi导出excel工具类
*/
public class PoiExcelUtils {

/**
- 获取单元格的值
- @param fCell
- @return
  */
  public static String getCellValue(Cell fCell) {
  if (fCell == null)
  return “”;
  return fCell.toString();
  }
/**
- 把单元格内容全部转为String
- @param cell
- @return
  */
  public static String getCellString(Cell cell){
  if (cell == null ) return “”;
  cell.setCellType(CellType.STRING);
  return cell.getStringCellValue();
  }
/**
- 合并单元格处理,获取合并行
- @param sheet
- @return List
  */
  public static List getCombineCell(Sheet sheet) {
  List list = new ArrayList();
  // 获得一个 sheet 中合并单元格的数量
  int sheetmergerCount = sheet.getNumMergedRegions();
  // 遍历所有的合并单元格
  for (int i = 0; i < sheetmergerCount; i++) {
  // 获得合并单元格保存进list中
  CellRangeAddress ca = sheet.getMergedRegion(i);
  list.add(ca);
  }
  return list;
  }
public static int getRowNum(List listCombineCell, Cell cell, Sheet sheet) {
int xr = 0;
int firstC = 0;
int lastC = 0;
int firstR = 0;
int lastR = 0;
for (CellRangeAddress ca : listCombineCell) {
// 获得合并单元格的起始行, 结束行, 起始列, 结束列
firstC = ca.getFirstColumn();
lastC = ca.getLastColumn();
firstR = ca.getFirstRow();
lastR = ca.getLastRow();
//cell.getRowIndex()获取单元格所在行号 cell.getColumnIndex()获取列号
if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
xr = lastR;
}
}
```
 }
 return xr;
```
}

/**
- 判断单元格是否为合并单元格，是的话则将单元格的值返回
- @param listCombineCell
- ```
       存放合并单元格的list
```
- @param cell
- ```
       需要判断的单元格
```
- @param sheet
- ```
       sheet
```
- @return
  */
  public static String isCombineCell(List listCombineCell, Cell cell, Sheet sheet) throws Exception {
  int firstC = 0;
  int lastC = 0;
  int firstR = 0;
  int lastR = 0;
  String cellValue = null;
  for (CellRangeAddress ca : listCombineCell) {
  // 获得合并单元格的起始行, 结束行, 起始列, 结束列
  firstC = ca.getFirstColumn();
  lastC = ca.getLastColumn();
  firstR = ca.getFirstRow();
  lastR = ca.getLastRow();
  if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
  if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
  Row fRow = sheet.getRow(firstR);
  Cell fCell = fRow.getCell(firstC);
  cellValue = getCellValue(fCell);
  break;
  }
  } else {
  cellValue = “”;
  }
  }
  return cellValue;
  }
/**
- 获取合并单元格的值
- @param sheet
- @param row
- @param column
- @return
  */
  public static String getMergedRegionValue(Sheet sheet, int row, int column) {
  int sheetMergeCount = sheet.getNumMergedRegions();
  
  for (int i = 0; i < sheetMergeCount; i++) {
  CellRangeAddress ca = sheet.getMergedRegion(i);
  int firstColumn = ca.getFirstColumn();
  int lastColumn = ca.getLastColumn();
  int firstRow = ca.getFirstRow();
  int lastRow = ca.getLastRow();
```
 if (row >= firstRow && row <= lastRow) {
     if (column >= firstColumn && column <= lastColumn) {
         Row fRow = sheet.getRow(firstRow);
         Cell fCell = fRow.getCell(firstColumn);
         return getCellValue(fCell);
     }
 }
```
  }
  
  return null;
  }
/**
- 判断指定的单元格是否是合并单元格
- @param sheet
- @param row
- ```
       行下标
```
- @param column
- ```
       列下标
```
- @return
  */
  public static boolean isMergedRegion(Sheet sheet, int row, int column) {
  int sheetMergeCount = sheet.getNumMergedRegions();
  for (int i = 0; i < sheetMergeCount; i++) {
  CellRangeAddress range = sheet.getMergedRegion(i);
  int firstColumn = range.getFirstColumn();
  int lastColumn = range.getLastColumn();
  int firstRow = range.getFirstRow();
  int lastRow = range.getLastRow();
  if (row >= firstRow && row <= lastRow) {
  if (column >= firstColumn && column <= lastColumn) {
  return true;
  }
  }
  }
  return false;
  }