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.data.entity.PackingManage;
|
import com.zbooksoft.gdmis.service.CatSwajxxService;
|
import com.zbooksoft.gdmis.service.PackingManageService;
|
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 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("/importSuExcel")
|
public class ImportExcelSuController {
|
|
@Autowired
|
ImportTemplateService importTemplateService;
|
@Autowired
|
ImportColumnService importColumnService;
|
@Autowired
|
CatSwajxxService catSwajxxService;
|
@Autowired
|
PackingManageService packingManageService;
|
// @Autowired
|
// CatSwzkxxService catSwzkxxService;
|
// @Autowired
|
// CatSwyxxxService catSwyxxxService;
|
@Autowired
|
IFormData iform;
|
@Autowired
|
JdbcTemplate jdbcTemplate;
|
private static final Logger logger = LoggerFactory.getLogger(ImportExcelSuController.class);
|
|
|
/**
|
* 导入页面
|
*
|
* @param type 0:从转孔基本信息进入 1:从钻孔图幅进入
|
* @return
|
*/
|
@RequestMapping({"/importIndex"})
|
@RequiresUser
|
public ModelAndView importIndex(Integer type) {
|
ModelAndView mv = new ModelAndView("gh/excel/importSuExcel");
|
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 {
|
readExcelByAjxx(file);
|
return new AjaxResponse(true);
|
} catch (Exception e) {
|
logger.error(e.getMessage(), e);
|
return new AjaxResponse(new ErrorInfo(e.getMessage()), false);
|
}
|
}
|
|
|
private void readExcelByAjxx(MultipartFile file) throws IOException {
|
List<Long> importTemplateIds = Arrays.asList(1954734982477950977L, 1954736942581067778L);
|
// 创建一个可修改的ArrayList
|
String originalFilename = file.getOriginalFilename();
|
HashMap<String, Object> map = new HashMap<>();
|
InputStream inputStream = file.getInputStream();
|
if (originalFilename.matches("^.+\\.(?i)(xls)$")) {
|
//创建工作簿对象
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
|
// for (int i = 0; i < 2; i++) {
|
readExcel2003(hssfWorkbook, 1, importTemplateIds.get(1), map);
|
// }
|
}
|
if (originalFilename.matches("^.+\\.(?i)(xlsx)$")) {
|
//创建工作簿对象
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
|
for (int i = 0; i < 2; i++) {
|
readExcel2007(xssfWorkbook, i, importTemplateIds.get(i), map);
|
}
|
}
|
|
}
|
|
|
//Excel2003 xls格式
|
private void readExcel2003(HSSFWorkbook hssfWorkbook, int sheetAt, Long importTemplateId, Map<String, Object> mapData) {
|
|
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个工作表
|
HSSFSheet sheet = hssfWorkbook.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++) {
|
Long id = 0l;
|
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 + "'";
|
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
|
if (mapList.size() > 0) {
|
Map<String, Object> stringObjectMap = mapList.get(0);
|
String string = stringObjectMap.get("id").toString();
|
map.put("AJ_ID", string);
|
map.put("YSZT", 3);
|
}
|
map.put("AJ_DH", cellValue);
|
}
|
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);
|
}
|
}
|
Long insert = iform.insert(importTemplate.getTableId(), map);
|
if (sheetAt == 1) {
|
PackingManage packingManage = new PackingManage();
|
int columnIndex = excelColumns.indexOf("袋卷号");
|
if (columnIndex >= 0) {
|
Cell cell = rowValue.getCell(columnIndex);
|
Object cellValue = getExcelValue(cell);
|
packingManage.setBoxNumber(cellValue == null ? "" : cellValue.toString());
|
}
|
int columnInde = excelColumns.indexOf("档号");
|
if (columnIndex >= 0) {
|
Cell cell = rowValue.getCell(columnInde);
|
Object cellValue = getExcelValue(cell);
|
String itemCode = cellValue == null ? "" : cellValue.toString();
|
String newBarCode = itemCode.replace("底", "D").replace("测", "C").replace("观", "G").replace("探", "T").replace("样", "Y").replace("试", "S").replace("录", "L").replace("像", "X").replace("综", "Z").replace("文", "W").replace("实", "S").replace("物", "W").replace("钻", "Z").replace("设", "S").replace("相", "X").replace("油", "Y").replace("电", "D").replace("地", "D");
|
packingManage.setItemNum(newBarCode + "-" + packingManage.getBoxNumber());
|
}
|
packingManage.setItemId(insert == null ? "0" : insert.toString());
|
packingManage.setAjId(id == null ? "0" : id.toString());
|
packingManage.setType(0);
|
packingManage.setCreateTime(new java.util.Date());
|
packingManageService.saveOrUpdate(packingManage);
|
}
|
}
|
}
|
|
// Excel2007 xlsx
|
private void readExcel2007(XSSFWorkbook xssfWorkbook, int sheetAt, Long importTemplateId, Map<String, Object> mapData) {
|
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);
|
map.put("YSZT", 3);
|
map.put("AJ_DH", cellValue);
|
}
|
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);
|
} catch (Exception e) {
|
logger.info(e.getMessage(), e);
|
}
|
}
|
}
|
}
|
