/**
* 上传EXECL
* @param file
* @param request
* @return
*/
@Override
@Transactional
public String **uploadExecl**(MultipartFile[] file, HttpServletRequest request){
//本地路径
String filePath = this.saveFilePath ;
//线上访问路径
String viewFilePath = this.fileAddress;
try {
List<JSONObject> dataList = new ArrayList<>();
MultipartFile fileUpload = file[0];//execl文件
//获取文件名
String fileName = fileUpload.getOriginalFilename();
//文件访问地址
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return "上传失败，文件格式错误";
}
File newFile = new File(filePath);
if (!newFile .exists()) {
newFile .mkdirs();
}
//将文件保存到文件夹里
fileUpload.transferTo(new File(filePath+fileName));
//更新execl
updateExecl(filePath+fileName);
return viewFilePath+fileName;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 删除指定行
* @param filePath
* @throws Exception
*/
public void **updateExecl**(String filePath) throws Exception{
FileInputStream fs = new FileInputStream(filePath);
//使用POI提供的方法得到excel的信息
XSSFWorkbook workbook = new XSSFWorkbook(fs);
int numSheets = workbook.getNumberOfSheets();//获取sheet数量
for (int sheetIndex = 0; sheetIndex < numSheets; sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
//sheet名称
String sheetName = sheet.getSheetName();
//得到所有的行
int rows = sheet.getLastRowNum();
Row row = sheet.getRow(5);
row.getCell(1).setCellType(CellType.STRING);
if(!InstallUtil.isEmpty(row.getCell(1)) && "分组".equals(row.getCell(1).getStringCellValue())) {
Map<String, String> res = new LinkedHashMap();
for (int i = 6; i <= rows; i++) {
row = sheet.getRow(i);
boolean isMerge = isMergedRegion(sheet, i, 1);
String cpmc = "";
//判断是否具有合并单元格
if (isMerge) {
cpmc = getMergedRegionValue(sheet, row.getRowNum(), 1);
}
if (!InstallUtil.isEmpty(row.getCell(1)) || !InstallUtil.isEmpty(cpmc)) {
if (!InstallUtil.isEmpty(cpmc)) {
if (!InstallUtil.isEmpty(res.get(cpmc))) {
res.put(cpmc, res.get(cpmc).toString() + "," + i);
} else {
res.put(cpmc, String.valueOf(i));
}
} else {
row.getCell(1).setCellType(CellType.STRING);
cpmc = row.getCell(1).getStringCellValue();
if (!InstallUtil.isEmpty(res.get(cpmc))) {
res.put(cpmc, res.get(cpmc).toString() + "," + i);
} else {
res.put(cpmc, String.valueOf(i));
}
}
}
}
if(res.size() >1){
//负责sheet 记录sheetname
List<String> sheetNameList = new ArrayList<>();
for (int i=1;i<res.size();i++) {
if(i>0){
String newSheetName = sheetName + "("+String.valueOf(i)+")";
workbook.setSheetName(workbook.getSheetIndex(workbook.cloneSheet(sheetIndex)), newSheetName);
sheetNameList.add(newSheetName);
}
}
//拆分个数
int i = 0;
for (String key : res.keySet()) {
// 要删除的行的行号列表
List<Integer> rowsToDelete = new ArrayList<>();
for (String key1 : res.keySet()) {
if(!key1.equals(key)){
String[] split = res.get(key1).split(",");
for (int j = 0; j < split.length; j++) {
int s = Integer.parseInt(split[j]);
rowsToDelete.add(s);
}
}
}
if(i != 0) {
sheet = workbook.getSheet(sheetNameList.get(i-1));
deleteRowWithMergedCells(sheet,rowsToDelete,filePath,workbook);
}else{
System.out.println(sheet.getSheetName());
deleteRowWithMergedCells(sheet,rowsToDelete,filePath,workbook);
}
i++;
}
}
}
}
fs.close();
}
/**
* 删除指定行
* @param sheet
* @param rowsToDelete
* @param filePath
* @param workbook
* @param s
* @throws IOException
*/
public void **deleteRowWithMergedCells**(Sheet sheet, List<Integer> rowsToDelete, String filePath, XSSFWorkbook workbook) throws IOException {
FileOutputStream out = new FileOutputStream(filePath);
rowsToDelete.sort((a, b) -> b - a);
// 暂存含有合并单元格的行号
for (int rowIndex : rowsToDelete) {
Row row = sheet.getRow(rowIndex);
if (row != null) {
if (hasCellsInRow(row)){
//获取指定行在那些合并合并单元格里
List<CellRangeAddress> mergedRegionsInRow = getMergedRegionsInRow(sheet, rowIndex);
int [] rows;
int firstRow;
int firstColumn;
String stringCellValue;
for (int i = 0;i<mergedRegionsInRow.size();i++){
//直接把要操作的行其中的单元格给删除
for (int j = sheet.getNumMergedRegions() - 1; j >= 0; j--) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(j);
// 检查合并单元格是否包含指定的行索引
if (mergedRegion.getFirstRow() <= rowIndex && mergedRegion.getLastRow() >= rowIndex) {
sheet.removeMergedRegion(j); // 移除合并单元格
}
}
firstRow = mergedRegionsInRow.get(i).getFirstRow();
if (rowIndex == firstRow){//说明要删除的行是这个合并单元格的第一行
//将这一行的数据复制到下一行
firstColumn = mergedRegionsInRow.get(i).getFirstColumn();
stringCellValue = row.getCell(firstColumn).getStringCellValue();
//将数据复制到第二行
int nextRowIndex = rowIndex + 1;
Cell nextCell = sheet.getRow(nextRowIndex).getCell(firstColumn);
//根据具体的单元格类型进行复制
Cell cell = row.getCell(firstColumn);
CellType cellType = row.getCell(firstColumn).getCellType();
if (cellType == CellType.STRING) {
stringCellValue = cell.getStringCellValue();
if(!InstallUtil.isEmpty(stringCellValue) && !InstallUtil.isEmpty(nextCell)){
nextCell.setCellValue(stringCellValue);
}
} else if (cellType == CellType.NUMERIC) {
double numericCellValue = cell.getNumericCellValue();
if(!InstallUtil.isEmpty(numericCellValue) && !InstallUtil.isEmpty(nextCell)){
nextCell.setCellValue(numericCellValue);
}
} else if (cellType == CellType.BOOLEAN) {
boolean booleanCellValue = cell.getBooleanCellValue();
if(!InstallUtil.isEmpty(booleanCellValue) && !InstallUtil.isEmpty(nextCell)){
nextCell.setCellValue(booleanCellValue);
}
} else if (cellType == CellType.FORMULA) {
String formula = cell.getCellFormula();
if(!InstallUtil.isEmpty(formula) && !InstallUtil.isEmpty(nextCell)){
nextCell.setCellFormula(formula);
}
}
}
}
//删除本行 一这一行
//sheet.removeRow(row);
if (rowIndex+1 <= sheet.getLastRowNum()) {
sheet.shiftRows(rowIndex+1 , sheet.getLastRowNum(), -1);
}
//移动后本单元格的格式就乱了 在根据获取的本单元格信息 再创建一个新的单元格
for (int i = 0;i<mergedRegionsInRow.size();i++){
CellRangeAddress cellRangeAddress = mergedRegionsInRow.get(i);
//sheet.addMergedRegion(new CellRangeAddress(cellRangeAddress.getFirstRow(), cellRangeAddress.getLastRow()-1, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()));
}
}else {
// 删除指定行
sheet.shiftRows(rowIndex+1, sheet.getLastRowNum(), -1);
}
}
}
/*
删除空白行
for (int rowNum : rowsToDelete) {
Row row = sheet.getRow(rowNum);
if (row != null) {
sheet.removeRow(row);
}
}*/
//得到所有的行
int rows = sheet.getLastRowNum();
int num13 = 0;
BigDecimal n14 = new BigDecimal("0");
for(int i = 6; i <= rows-1; i++) {
//获取行对象
Row row = sheet.getRow(i);
if(!InstallUtil.isEmpty(row.getCell(13))){
row.getCell(13).setCellType(CellType.STRING);
num13 += Integer.valueOf(row.getCell(13).getStringCellValue());
}
if(!InstallUtil.isEmpty(row.getCell(14))){
n14 = n14.add(new BigDecimal(row.getCell(14).getStringCellValue()));
}
}
Row row = sheet.getRow(rows);
XSSFCell supplypriceCell = (XSSFCell) row.createCell(13);
supplypriceCell.setCellValue(num13);
XSSFCell supplypriceCell1 = (XSSFCell) row.createCell(14);
supplypriceCell1.setCellValue(n14.toString());
workbook.write(out);
out.close();
}
//判断指定行有没有合并单元格
public boolean **hasCellsInRow**(Row row) {
if (row == null) {
return false;
}
int lastCellNum = row.getLastCellNum();
return lastCellNum >= 0;
}
//获取指定行做在的合并单元格的信息
private List<CellRangeAddress> **getMergedRegionsInRow**(Sheet sheet, int rowIndex) {
List<CellRangeAddress> mergedRegions = new ArrayList<>();
for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
int firstRow = mergedRegion.getFirstRow();
int lastRow = mergedRegion.getLastRow();
if (rowIndex >= firstRow && rowIndex <= lastRow) {
mergedRegions.add(mergedRegion);
}
}
return mergedRegions;
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
public 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;
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
public 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;
}
2601
评论 1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
