package com.zbooksoft.gdmis.controller;
|
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.ruili.wcp.data.entity.config.ImportColumn;
|
import com.ruili.wcp.data.entity.config.ImportTemplate;
|
import com.ruili.wcp.engine.form.IFormData;
|
import com.ruili.wcp.service.config.ImportColumnService;
|
import com.ruili.wcp.service.config.ImportTemplateService;
|
import com.ruili.wcp.web.common.LicenseException;
|
import com.ruili.wcp.web.model.AjaxResponse;
|
import com.ruili.wcp.web.model.ErrorInfo;
|
import com.zbooksoft.gdmis.common.BoxEnum;
|
import com.zbooksoft.gdmis.service.CatSwajxxService;
|
import org.apache.commons.lang.StringUtils;
|
import org.apache.poi.hssf.usermodel.HSSFSheet;
|
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.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.apache.shiro.authz.annotation.RequiresUser;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.RequestParam;
|
import org.springframework.web.bind.annotation.ResponseBody;
|
import org.springframework.web.multipart.MultipartFile;
|
import org.springframework.web.servlet.ModelAndView;
|
|
import javax.crypto.spec.PSource;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.*;
|
|
import static com.ruili.wcp.web.controller.general.DataImportController.getExcelValue;
|
|
/**
|
* @Description:导入Excel文件
|
* @Author: zhai
|
* @Date: 2024/9/11
|
**/
|
@Controller
|
@RequestMapping("/importExcel")
|
public class ImportExcelController {
|
|
@Autowired
|
ImportTemplateService importTemplateService;
|
@Autowired
|
ImportColumnService importColumnService;
|
@Autowired
|
CatSwajxxService catSwajxxService;
|
// @Autowired
|
// CatSwzkxxService catSwzkxxService;
|
// @Autowired
|
// CatSwyxxxService catSwyxxxService;
|
@Autowired
|
IFormData iform;
|
@Autowired
|
JdbcTemplate jdbcTemplate;
|
private static final Logger logger = LoggerFactory.getLogger(ImportExcelController.class);
|
|
|
/**
|
* 导入页面
|
*
|
* @param type 0:从转孔基本信息进入 1:从钻孔图幅进入
|
* @return
|
*/
|
@RequestMapping({"/importIndex"})
|
@RequiresUser
|
public ModelAndView importIndex(Integer type) {
|
ModelAndView mv = new ModelAndView("gh/excel/importExcel");
|
return mv;
|
}
|
|
/**
|
* @param file 0 导入转孔信息 1 导入图幅信息 2 导入实物案卷信息
|
* @return
|
* @throws LicenseException
|
*/
|
|
@RequestMapping({"/importExcel"})
|
@ResponseBody
|
@RequiresUser
|
public Object importExcel(@RequestParam(value = "file", required = false) MultipartFile file) throws LicenseException {
|
try {
|
readExcelBySwyp(file);
|
return new AjaxResponse(true);
|
} catch (Exception e) {
|
logger.error(e.getMessage(), e);
|
return new AjaxResponse(new ErrorInfo("导入失败"), false);
|
}
|
}
|
|
|
private void readExcelBySwyp(MultipartFile file) throws IOException {
|
List<Long> importTemplateIds = Arrays.asList(1954734982477950977L, 1954736942581067778L);
|
|
String originalFilename = file.getOriginalFilename();
|
|
InputStream inputStream = file.getInputStream();
|
if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
|
//创建工作簿对象
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
|
HashMap<String, Object> map = readProject2003(hssfWorkbook);
|
readExcel2003(hssfWorkbook, map);
|
|
}
|
if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
|
//创建工作簿对象
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
|
for (int i = 0; i < 2; i++) {
|
HashMap<String, Object> map = null;
|
readExcel2007(xssfWorkbook, i, importTemplateIds.get(i), map, 0);
|
}
|
}
|
|
}
|
|
//Excel2003 xls格式
|
private HashMap<String, Object> readProject2003(HSSFWorkbook hssfWorkbook) {
|
HashMap<String, Object> map = new HashMap<>();
|
//读取第i个工作表
|
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
|
//项目名称：1:25万南沙幅海域区域地质调查
|
String xmmc = getValue(sheet, 6, 3);
|
map.put("XMMC", xmmc);
|
|
String xmbh = getValue(sheet, 7, 3);
|
map.put("XMBH", xmbh);
|
|
String selectProject = "SELECT ID FROM CAT_FILE_AJJXX WHERE XMKYQMC = '" + xmmc + "' AND XMKYQBH = '" + xmbh + "'";
|
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(selectProject);
|
if (mapList.size() > 0) {
|
String ajId = mapList.get(0).get("ID").toString();
|
map.put("AJ_ID", ajId);
|
}
|
String dcchc = getValue(sheet, 8, 3);
|
map.put("DCCHC", dcchc);
|
|
String dchy = getValue(sheet, 9, 3);
|
map.put("DCHY", dchy);
|
|
String dcqu = getValue(sheet, 10, 3);
|
map.put("DCQU", dcqu);
|
|
String dcsj = getValue(sheet, 11, 3);
|
map.put("DCSJ", dcsj);
|
|
String jyr = getValue(sheet, 12, 3);
|
map.put("JYR", jyr);
|
|
String syr = getValue(sheet, 13, 3);
|
map.put("SYR", syr);
|
|
String syrq = getValue(sheet, 14, 3);
|
map.put("SYRQ", syrq);
|
|
return map;
|
|
}
|
|
private String getValue(HSSFSheet sheet, int rowIndex, int cellIndex) {
|
Row row = sheet.getRow(rowIndex);
|
if (row != null) {
|
Cell cell = row.getCell(cellIndex);
|
if (cell != null) {
|
String excelValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();
|
return subString(excelValue);
|
}
|
}
|
return null;
|
}
|
|
private String subString(String cellValue) {
|
if (cellValue != null && cellValue.contains("：")) {
|
int index = cellValue.indexOf("：");
|
return cellValue.substring(index + 1).trim();
|
}
|
return cellValue;
|
}
|
|
|
//Excel2003 xls格式
|
private void readExcel2003(HSSFWorkbook hssfWorkbook, Map<String, Object> mapData) {
|
|
ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(1966070853328244738L);
|
QueryWrapper<ImportColumn> query = new QueryWrapper();
|
query.eq("import_template_id", importTemplate.getImportTemplateId());
|
List<ImportColumn> columnList = importColumnService.list(query);
|
List<String> excelColumns = new ArrayList();
|
|
//读取第i个工作表
|
HSSFSheet sheet = hssfWorkbook.getSheetAt(1);
|
//获取最后一行的num，即总行数。此处从6行开始
|
int maxRow = sheet.getLastRowNum();
|
Row row = sheet.getRow(3);
|
// Row row = sheet.getRow(4);
|
int totalCells = row.getPhysicalNumberOfCells();
|
for (int j = 0; j < totalCells; ++j) {
|
Cell cell = row.getCell(j);
|
String cellValue = getExcelValue(cell) == null ? "" : getExcelValue(cell).toString();
|
|
if (StringUtils.isNotEmpty(cellValue) && "站位位置".equals(cellValue)) {
|
Row rowJd = sheet.getRow(4);
|
Cell cellJd = rowJd.getCell(j);
|
String excelValueJd = getExcelValue(cellJd);
|
excelColumns.add(excelValueJd);
|
|
Cell cellWd = rowJd.getCell(j + 1);
|
String excelValueWd = getExcelValue(cellWd);
|
excelColumns.add(excelValueWd);
|
} else if (StringUtils.isNotEmpty(cellValue)) {
|
excelColumns.add(cellValue.toString());
|
}
|
}
|
for (int j = 6; j <= maxRow; j++) {
|
Row rowValue = sheet.getRow(j);
|
Map<String, Object> map = new HashMap();
|
map.putAll(mapData);
|
for (int z = 0; z < columnList.size(); z++) {
|
ImportColumn column = columnList.get(z);
|
int columnIndex = excelColumns.indexOf(column.getExcelColunm());
|
if (columnIndex >= 0) {
|
Cell cell = rowValue.getCell(columnIndex);
|
Object cellValue = getExcelValue(cell);
|
map.put(column.getColumnName(), cellValue);
|
}
|
}
|
iform.insert(importTemplate.getTableId(), map);
|
}
|
|
}
|
|
// Excel2007 xlsx
|
private void readExcel2007(XSSFWorkbook xssfWorkbook, int sheetAt, Long importTemplateId, Map<String, Object> mapData, int rowNum) {
|
ImportTemplate importTemplate = importTemplateService.getByIdImportTemplate(importTemplateId);
|
QueryWrapper<ImportColumn> query = new QueryWrapper();
|
query.eq("import_template_id", importTemplate.getImportTemplateId());
|
List<ImportColumn> columnList = importColumnService.list(query);
|
List<String> excelColumns = new ArrayList();
|
int dhIndex = 0;
|
//读取第i个工作表
|
XSSFSheet sheet = xssfWorkbook.getSheetAt(sheetAt);
|
//获取最后一行的num，即总行数。此处从0开始
|
int maxRow = sheet.getLastRowNum();
|
Row row = sheet.getRow(0);
|
int totalCells = row.getPhysicalNumberOfCells();
|
for (int j = 0; j < totalCells; ++j) {
|
Cell cell = row.getCell(j);
|
Object cellValue = getExcelValue(cell);
|
if (sheetAt == 1 && "案卷档号".equals(cellValue.toString())) {
|
dhIndex = j;
|
}
|
excelColumns.add(cellValue.toString());
|
}
|
for (int j = 1; j <= maxRow; j++) {
|
try {
|
Row rowValue = sheet.getRow(j);
|
Map<String, Object> map = new HashMap();
|
if (sheetAt == 1) {
|
Cell cell = rowValue.getCell(dhIndex);
|
Object cellValue = getExcelValue(cell);
|
String sql = "select id from cat_file_ajjxx where dh='" + cellValue + "'";
|
Long id = jdbcTemplate.queryForObject(sql, Long.class);
|
map.put("AJ_ID", id);
|
}
|
for (int z = 0; z < columnList.size(); z++) {
|
ImportColumn column = columnList.get(z);
|
if ("2".equals(column.getLinkedData())) {
|
String linkedType = column.getLinkedType();
|
String excelColunm = column.getExcelColunm();
|
int columnIndex = excelColumns.indexOf(excelColunm);
|
if (columnIndex >= 0) {
|
Cell cell = rowValue.getCell(columnIndex);
|
Object cellValue = getExcelValue(cell);
|
String[] split = linkedType.split("\\|");
|
String tableName = split[0];
|
String columnName = split[1];
|
String valueName = split[2];
|
String sql = "select " + valueName + " from " + tableName + " where " + columnName + "='" + cellValue + "'";
|
String columnValue = jdbcTemplate.queryForObject(sql, String.class);
|
map.put(column.getColumnName(), columnValue);
|
}
|
} else {
|
int columnIndex = excelColumns.indexOf(column.getExcelColunm());
|
if (columnIndex >= 0) {
|
Cell cell = rowValue.getCell(columnIndex);
|
Object cellValue = getExcelValue(cell);
|
if ("工作时间".equals(column.getColumnName().toString())) {
|
String string = cellValue.toString();
|
boolean contains = string.contains("-");
|
if (contains) {
|
String[] split = string.split("-");
|
map.put("QSSJ", split[0]);
|
map.put("ZZSJ", split[1]);
|
} else {
|
map.put("QSSJ", string);
|
}
|
} else {
|
map.put(column.getColumnName(), cellValue);
|
}
|
}
|
}
|
}
|
iform.insert(importTemplate.getTableId(), map);
|
} catch (Exception e) {
|
logger.info(e.getMessage(), e);
|
}
|
}
|
}
|
|
/**
|
* 关联盒号关联
|
*
|
* @param
|
* @return
|
*/
|
@RequestMapping({"/packingIndex"})
|
@RequiresUser
|
public ModelAndView packingIndex() {
|
ModelAndView mv = new ModelAndView("gh/excel/packingExcel");
|
return mv;
|
}
|
|
/**
|
* @param file 0 导入转孔信息 1 导入图幅信息 2 导入实物案卷信息
|
* @return
|
* @throws LicenseException
|
*/
|
|
@RequestMapping({"/packingExcel"})
|
@ResponseBody
|
@RequiresUser
|
public Object packingExcel(@RequestParam(value = "file", required = false) MultipartFile file) throws LicenseException {
|
try {
|
relationPacking(file);
|
return new AjaxResponse(true);
|
} catch (Exception e) {
|
logger.error(e.getMessage(), e);
|
return new AjaxResponse(new ErrorInfo("导入失败"), false);
|
}
|
}
|
|
private void relationPacking(MultipartFile file) throws IOException {
|
|
String originalFilename = file.getOriginalFilename();
|
|
InputStream inputStream = file.getInputStream();
|
if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
|
//创建工作簿对象
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
|
//读取第i个工作表
|
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
|
|
int maxRow = sheet.getLastRowNum();
|
|
for (int j = 1; j <= maxRow; j++) {
|
Row rowValue = sheet.getRow(j);
|
Cell boxNumberCell = rowValue.getCell(0);
|
Object boxNumber = getExcelValue(boxNumberCell);
|
|
Cell storageLocationCell = rowValue.getCell(1);
|
Object storageLocation = getExcelValue(storageLocationCell);
|
|
//04 1 10 1 02 06
|
//ZO1 A10左 02列 06层
|
String update = "update BUS_PACKING_MANAGE set STORAGE_LOCATION ='" + storageLocation + "' where BOX_NUMBER = '" + boxNumber + "'";
|
jdbcTemplate.update(update);
|
}
|
|
}
|
if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
|
//创建工作簿对象
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
|
|
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
|
//获取最后一行的num，即总行数。此处从0开始
|
int maxRow = sheet.getLastRowNum();
|
Row row = sheet.getRow(0);
|
for (int j = 1; j <= maxRow; j++) {
|
Row rowValue = sheet.getRow(j);
|
Cell boxNumberCell = rowValue.getCell(0);
|
Object boxNumber = getExcelValue(boxNumberCell);
|
|
Cell storageLocationCell = rowValue.getCell(1);
|
Object storageLocationCode = getExcelValue(storageLocationCell);
|
|
String storageLocation = parseStorageLocationWithEnum(storageLocationCode == null ? "" : storageLocationCode.toString());
|
String update = "update BUS_PACKING_MANAGE set STORAGE_LOCATION_CODE = '" + storageLocationCode + "', STORAGE_LOCATION ='" + storageLocation + "' where BOX_NUMBER = '" + boxNumber + "'";
|
jdbcTemplate.update(update);
|
}
|
}
|
|
}
|
|
public static String parseStorageLocationWithEnum(String code) {
|
if (code == null || code.length() != 10) {
|
throw new IllegalArgumentException("Invalid code format, expected 10 characters");
|
}
|
|
// 提取各部分
|
String zoneCode = code.substring(0, 2);
|
String zoneName = code.substring(2, 3);
|
String rackCode = code.substring(3, 5);
|
String sideCode = code.substring(5, 6);
|
String column = code.substring(6, 8);
|
String layer = code.substring(8, 10);
|
|
// 使用枚举获取区域名称
|
String zoneStr = BoxEnum.getNameByCode(zoneCode);
|
|
// 处理方向映射
|
String sideStr = "1".equals(sideCode) ? "左" : "右";
|
|
// 根据数字获取对应字母
|
String letter = BoxEnum.RackLetter.getLetterByNumber(zoneName); // 返回 "A"
|
|
// 构造架号
|
String rackStr = letter + rackCode;
|
|
// 组合最终结果
|
return String.format("%s-%s%s-%s列-%s层", zoneStr, rackStr, sideStr, column, layer);
|
}
|
|
public static String generateStorageLocationCode(String location) {
|
// 示例输入: Z01-A01左-08列-02层
|
String[] parts = location.split("-");
|
|
String zoneStr = parts[0]; // Z01
|
String rackAndSide = parts[1]; // A01左
|
|
// 区域代码 (例如 Z01 -> 04)
|
String zoneCode = BoxEnum.getCodeByName(zoneStr);
|
|
// 架号中的字母与数字 (例如 A01 -> A=1, 01)
|
String rackLetter = rackAndSide.substring(0, 1); // A
|
String rackNumber = rackAndSide.substring(1, 3); // 01
|
String sideStr = rackAndSide.substring(3); // 左 or 右
|
|
// 获取字母对应的数字 (例如 A -> 1)
|
// 根据数字获取对应字母
|
String rackLetterNum = BoxEnum.RackLetter.getNumberByLetter(rackLetter); // 返回 "A"
|
|
|
// 方向代码 (左 -> 1, 右 -> 2)
|
String sideCode = "左".equals(sideStr) ? "1" : "0";
|
|
|
// 拼接最终结果
|
return zoneCode + rackLetterNum + rackNumber + sideCode;
|
}
|
|
public static void main(String[] args) {
|
// String s = parseStorageLocationWithEnum("0411010206");
|
String locationNumber = "Z01-A01左-08列-02层";
|
//0410110802
|
String s = generateStorageLocationCode(locationNumber);
|
System.out.println(s);
|
}
|
}
|
