/src_service/com/ptswitch/material/service/impl/MaterialExcelServiceImpl.java
Java | 343 lines | 312 code | 24 blank | 7 comment | 54 complexity | 7a364fcf2aff7fbc598d193fef04eeb0 MD5 | raw file
- package com.ptswitch.material.service.impl;
-
- import java.io.File;
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
-
- import org.apache.commons.io.FileUtils;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.IndexedColors;
- import org.apache.poi.ss.usermodel.Row;
- import org.apache.poi.ss.usermodel.Sheet;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.hxzon.project.AppRuntimeException;
- import org.hxzon.util.ApacheZipUtil;
- import org.hxzon.util.BitUtil;
- import org.hxzon.util.ExcelUtil;
- import org.hxzon.util.OgnlUtil;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import org.springframework.stereotype.Service;
-
- import com.ptswitch.material.model.material.Bom;
- import com.ptswitch.material.model.material.BomMaterial;
- import com.ptswitch.material.model.material.BomOrigFile;
- import com.ptswitch.material.model.material.Material;
- import com.ptswitch.material.service.MaterialExcelService;
- import com.ptswitch.material.util.ColumnMap;
- import com.ptswitch.material.util.MaterialUtil;
-
- @Service
- public class MaterialExcelServiceImpl extends ExcelUtil implements MaterialExcelService {
- private static final Logger logger = LoggerFactory.getLogger(MaterialExcelServiceImpl.class);
-
- public void buildBomFromExcel(Bom bom, Workbook wb) {
- if (wb == null) {
- return;
- }
- int sheetNum = wb.getNumberOfSheets();
- List<BomMaterial> bomMaterials = new ArrayList<BomMaterial>();
- BomMaterial bomMaterial = null;
- Sheet sheet;
- for (int j = 0; j < sheetNum; j++) {
- sheet = wb.getSheetAt(j);
- if (sheet == null) {
- continue;
- }
- ColumnMap columnMap = MaterialUtil.buildColumnMap(sheet);
- if (columnMap.get("name") != -1) {
- throw AppRuntimeException.error("找到‘物料名称’列,请确认导入的是Bom表,而不是物料表");
- }
- Row row = null;
- int lastrow = sheet.getLastRowNum();
- for (int i = 1; i <= lastrow; i++) {
- row = sheet.getRow(i);
- if (isEmptyRow(row)) {
- continue;
- }
- bomMaterial = new BomMaterial();
- rowToBomMaterial(row, bomMaterial, columnMap);
- bomMaterial.setBom(bom);
- bomMaterials.add(bomMaterial);
- }
- }
- bom.setBomMaterials(bomMaterials);
- }
-
- public void buildBomFromExcel(Bom bom, String filePath) {
- Workbook wb = read(filePath);
- buildBomFromExcel(bom, wb);
- }
-
- public void buildMaterialsFromExcel(List<Material> materials, Workbook wb) {
- if (wb == null) {
- return;
- }
- int sheetNum = wb.getNumberOfSheets();
- Sheet sheet;
- for (int j = 0; j < sheetNum; j++) {
- sheet = wb.getSheetAt(j);
- if (sheet == null) {
- continue;
- }
- ColumnMap columnMap = MaterialUtil.buildColumnMap(sheet);
- if (columnMap.get("name") == -1) {
- throw AppRuntimeException.error("找不到‘物料名称’列,请确认导入的是物料表,而不是Bom表");
- }
- Row row = null;
- Material material = null;
- String code = null;
- int lastrow = sheet.getLastRowNum();
- for (int i = 1; i <= lastrow; i++) {
- row = sheet.getRow(i);
- code = getCode(row, columnMap);
- if (code.isEmpty()) {// fixme not code don't add
- logger.debug("row {} is no fine code", i);
- continue;
- }
- material = new Material();
- rowToMaterial(row, material, columnMap);
- materials.add(material);
- }
- }
- }
-
- public void buildMaterialsFromExcel(List<Material> materials, String filePath) {
- Workbook wb = read(filePath);
- buildMaterialsFromExcel(materials, wb);
- }
-
- public void buildDataFromZip(List<Material> materials, List<Bom> boms, String parentPath, String zipFilePath) {// parentPath=all/upload
- FileUtils.deleteQuietly(new File(parentPath + "importZip"));
- try {
- FileUtils.forceMkdir(new File(parentPath + "importZip"));
- } catch (IOException e) {
- logger.error(e.getMessage());
- }
- ApacheZipUtil.unzip(parentPath + "importZip", parentPath + zipFilePath);
- Workbook wb = read(parentPath + "importZip/materials.xls");
- buildMaterialsFromExcel(materials, wb);
- wb = read(parentPath + "importZip/boms.xls");
- Sheet sheet = wb.getSheetAt(0);
- int lastrow = sheet.getLastRowNum();
- for (int i = 1; i <= lastrow; i++) {
- Bom bom = new Bom();
- Row row = sheet.getRow(i);
- String fileName = getCellString(row, 3);
- String filePath = "importZip/" + getCellString(row, 3);
- bom.setCode(getCellString(row, 1));
- bom.setVersion(getCellString(row, 2));
- bom.setRemark(getCellString(row, 4));
- bom.setFileName(filePath);
- BomOrigFile upload = new BomOrigFile();
- upload.setFileName(fileName);
- upload.setFilePath(filePath);
- upload.setFileType("application/vnd.ms-excel");
- bom.setOrigFile(upload);
- boms.add(bom);
- }
- for (Bom bom : boms) {
- wb = read(parentPath + bom.getFileName());
- buildBomFromExcel(bom, wb);
- }
- }
-
- public void outputDataToZip(List<Material> materials, List<Bom> boms, String parentPath, String outputFile) {
- try {
- FileUtils.deleteDirectory(new File(parentPath + "/tmpzip"));
- FileUtils.forceMkdir(new File(parentPath + "/tmpzip"));
- } catch (IOException e) {
- logger.debug(e.getMessage());
- }
- Workbook wb = outputMaterials(materials);
- write(parentPath + "/tmpzip/materials.xls", wb);
- wb = outputBomList(boms, parentPath);
- write(parentPath + "/tmpzip/boms.xls", wb);
- ApacheZipUtil.zip(parentPath + outputFile, parentPath + "/tmpzip");
- }
-
- //
- public Workbook outputMaterials(List<Material> materials) {
- Workbook wb = newWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = null;
- int rowIndex = 0;
- // setCellString(sheet, rowIndex, 1, 0, 10, "物料列表");
- // rowIndex += 2;
- // title
- row = sheet.createRow(rowIndex);
- setCellString(row, 0, "物料编号");
- setCellString(row, 1, "物料名称");
- setCellString(row, 2, "规格型号");
- setCellString(row, 3, "器件封装");
- setCellString(row, 4, "品牌");
- setCellString(row, 5, "供货商");
- setCellString(row, 6, "器件描述");
- setCellString(row, 7, "PCB封装");
- setCellString(row, 8, "临时编码");
- setCellString(row, 9, "采购状态");
- setCellString(row, 10, "备注");
- rowIndex++;
- // rows
- for (Material material : materials) {
- row = sheet.createRow(rowIndex);
- materialToRow(row, material);
- rowIndex++;
- }
- return wb;
- }
-
- public void outputMaterials(String absoluteFilePath, List<Material> materials) {
- Workbook wb = outputMaterials(materials);
- write(absoluteFilePath, wb);
- }
-
- public Workbook outputBom(Bom bom, int outputBomError) {
- Workbook wb = newWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = null;
- // title
- row = sheet.createRow(0);
- setCellString(row, 0, "code");
- setCellString(row, 1, "comment");
- setCellString(row, 2, "designator");
- setCellString(row, 3, "footprint");
- setCellString(row, 4, "voltage");
- setCellString(row, 5, "value");
- setCellString(row, 6, "quantity");
- setCellString(row, 7, "stuffOpt");
- if (outputBomError != MaterialExcelService.OutputBomError_NONE) {
- setCellString(row, 8, "警告");
- }
- // bom materials
- int i = 1;
- for (BomMaterial bomMaterial : bom.getBomMaterials()) {
- row = sheet.createRow(i);
- bomMaterialToRow(row, bomMaterial, outputBomError);
- i++;
- }
- return wb;
- }
-
- public void outputBom(String absoluteFilePath, Bom bom, int outputBomError) {
- Workbook wb = outputBom(bom, outputBomError);
- write(absoluteFilePath, wb);
- }
-
- public Workbook outputBomList(List<Bom> boms, String parentPath) {
- Workbook wb = newWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = null;
- int i = 1;
- for (Bom bom : boms) {
- try {
- row = sheet.createRow(i);
- setCellString(row, 1, bom.getCode());
- setCellString(row, 2, bom.getVersion());
- setCellString(row, 3, bom.getFileName());
- setCellString(row, 4, bom.getRemark());
- i++;
- FileUtils.copyFile(new File(parentPath + bom.getFileName()), new File(parentPath + "/tmpzip/" + bom.getFileName()));
- } catch (IOException e) {
- logger.debug(e.getMessage());
- }
-
- }
- return wb;
- }
-
- private static Material rowToMaterial(Row row, Material material, ColumnMap columnMap) {
- for (String key : OgnlUtil.getAllStringProperty(Material.class)) {
- OgnlUtil.setValue(material, key, MaterialUtil.getCellString(row, key, columnMap));
- }
- String state = material.getState();
- if (state == null || state.isEmpty()) {
- state = Material.State_Normal;
- }
- if (Material.State_Normal.equals(state) || Material.State_Recommend.equals(state) || Material.State_StopUse.equals(state) || Material.State_UnPublish.equals(state)
- || Material.State_UnRecommend.equals(state)) {
-
- } else {
- state = state + "描述不规范,请检查";
- }
- material.setState(state);
- return material;
- }
-
- private static BomMaterial rowToBomMaterial(Row row, BomMaterial bomMaterial, ColumnMap columnMap) {
- for (String key : OgnlUtil.getAllStringProperty(BomMaterial.class)) {
- OgnlUtil.setValue(bomMaterial, key, MaterialUtil.getCellString(row, key, columnMap));
- }
- return bomMaterial;
- }
-
- private static void materialToRow(Row row, Material material) {
- setCellString(row, 0, material.getCode());
- setCellString(row, 1, material.getName());
- setCellString(row, 2, material.getSpecification());
- setCellString(row, 3, material.getFootprint());
- setCellString(row, 4, material.getBrand());
- setCellString(row, 5, material.getSupplier());
- setCellString(row, 6, material.getDescription());
- setCellString(row, 7, material.getPcbDecal());
- setCellString(row, 8, "");
- setCellString(row, 9, material.getState());
- setCellString(row, 10, material.getRemark());
- }
-
- private static void bomMaterialToRow(Row row, BomMaterial bomMaterial, int outputBomError) {
- setCellString(row, 0, bomMaterial.getCode());
- setCellString(row, 1, bomMaterial.getComment());
- setCellString(row, 2, bomMaterial.getDesignator());
- setCellString(row, 3, bomMaterial.getFootprint());
- setCellString(row, 4, bomMaterial.getVoltage());
- setCellString(row, 5, bomMaterial.getValue());
- setCellString(row, 6, bomMaterial.getQuantity());
- setCellString(row, 7, bomMaterial.getStuffOpt());
- if (outputBomError == MaterialExcelService.OutputBomError_NONE) {
- return;
- }
- String error = "";
- if (bomMaterial.getCode().isEmpty()) {
- error = "编码为空";
- } else if (bomMaterial.getMaterial() == null) {
- error = "对应物料不存在";
- } else {
- if (BitUtil.isSet(outputBomError, MaterialExcelService.OutputBomError_State)) {
- if (bomMaterial.getMaterial().getState().contains("未发布")) {
- error = "物料未发布";
- } else if (bomMaterial.getMaterial().getState().contains("不推荐")) {
- error = "物料不推荐";
- } else if (bomMaterial.getMaterial().getState().contains("停用")) {
- error = "物料停用";
- } else if (!bomMaterial.getMaterial().getPcbDecal().equals(bomMaterial.getFootprint())) {
- error = "footprint与物料PCB封装不一致";
- }
- }
- if (BitUtil.isSet(outputBomError, OutputBomError_IndustryLevel)) {
- if (!Material.IndustryLevel_Industry.equals(bomMaterial.getMaterial().getIndustryLevel())) {
- error = bomMaterial.getMaterial().getIndustryLevel();
- }
- }
- }
- CellStyle style = null;
- if (!error.isEmpty()) {
- style = row.getSheet().getWorkbook().createCellStyle();
- style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
- style.setFillBackgroundColor(IndexedColors.RED.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- }
- setCellString(row, 8, error).setCellStyle(style);
- }
-
- private static String getCode(Row row, ColumnMap columnMap) {
- return MaterialUtil.getCellString(row, "code", columnMap);
- }
-
- private static Workbook newWorkbook() {
- return new HSSFWorkbook();
- }
-
- }