/src/com/ptswitch/material/service/impl/ExcelServiceImpl.java
Java | 406 lines | 365 code | 35 blank | 6 comment | 59 complexity | a54f4a211ed7ae1a635fb168a844b662 MD5 | raw file
- package com.ptswitch.material.service.impl;
-
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- 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.Cell;
- 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.apache.poi.ss.usermodel.WorkbookFactory;
- import org.springframework.stereotype.Service;
-
- import com.hxzon.util.AntZipUtil;
- import com.hxzon.util.OgnlUtil;
- import com.ptswitch.material.model.Bom;
- import com.ptswitch.material.model.BomMaterial;
- import com.ptswitch.material.model.Material;
- import com.ptswitch.material.model.Upload;
- import com.ptswitch.material.service.ExcelService;
- import com.ptswitch.material.util.ColumnMap;
-
- @Service("excelSer")
- public class ExcelServiceImpl extends LoggerService implements ExcelService {
-
- private static ColumnMap columnMap = new ColumnMap();
-
- private Workbook wb;
- private Sheet sheet;
-
- public void read(String parentpath, String subpath) {
- String filePath = parentpath + subpath;
- logger.debug(filePath);
- wb = null;// must reset,or will wrong
- InputStream ins = null;
- try {
- ins = new FileInputStream(filePath);
- wb = WorkbookFactory.create(ins);
- } catch (Exception e) {// InvalidFormatException,IOException,RuntimeException
- logger.error(e.getMessage());
- throw new RuntimeException(e.getMessage());
- }finally{
- try {
- ins.close();
- } catch (IOException e) {
- logger.error(e.getMessage());
- }
-
- }
-
- }
-
- public void write(String absoluteFilePath) {
- try {
- OutputStream ous = FileUtils.openOutputStream(new File(absoluteFilePath));
- wb.write(ous);
- ous.close();
- } catch (IOException e) {
- throw new RuntimeException(e.getMessage());
- }
- }
-
- public void buildBomFromExcel(Bom bom) {
- if (wb == null) {
- return ;
- }
- int sheetNum = wb.getNumberOfSheets();
- List<BomMaterial> bomMaterials=new ArrayList<BomMaterial>();
- BomMaterial bomMaterial = null;
- for (int j = 0; j < sheetNum; j++) {
- sheet = wb.getSheetAt(j);
- if (sheet == null) {
- continue;
- }
- buildColumnMap(sheet, columnMap);
- if (columnMap.get("name") != -1) {
- throw new RuntimeException("找到‘物料名称’列,请确认导入的是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);
- bomMaterial.setBom(bom);
- bomMaterials.add(bomMaterial);
- }
- }
- bom.setBomMaterials(bomMaterials);
- }
-
- public void buildMaterialsFromExcel(List<Material> materials) {
- if (wb == null) {
- return ;
- }
- int sheetNum = wb.getNumberOfSheets();
- for (int j = 0; j < sheetNum; j++) {
- sheet = wb.getSheetAt(j);
- if (sheet == null) {
- continue;
- }
- buildColumnMap(sheet, columnMap);
- if (columnMap.get("name") == -1) {
- throw new RuntimeException("找不到‘物料名称’列,请确认导入的是物料表,而不是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);
- if (code.isEmpty()) {// fixme not code don't add
- logger.debug("row {} is no fine code", i);
- continue;
- }
- material = new Material();
- rowToMaterial(row, material);
- materials.add(material);
- }
- }
- }
-
- 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());
- }
- AntZipUtil.unzip(parentPath + "importZip", parentPath + zipFilePath);
- read(parentPath, "importZip/materials.xls");
- buildMaterialsFromExcel(materials);
- read(parentPath,"importZip/boms.xls");
- 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.setFileName(filePath);
- Upload upload=new Upload();
- upload.setFileName(fileName);
- upload.setFilePath(filePath);
- upload.setFileType("application/vnd.ms-excel");
- bom.setUpload(upload);
- boms.add(bom);
- }
- for (Bom bom : boms) {
- read(parentPath, bom.getFileName());
- buildBomFromExcel(bom);
- }
- }
-
- 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());
- }
- outputMaterials(materials);
- write(parentPath + "/tmpzip/materials.xls");
- outputBomList(boms,parentPath);
- write(parentPath + "/tmpzip/boms.xls");
- AntZipUtil.zip(parentPath + outputFile, parentPath + "/tmpzip");
- }
-
- //
- public void outputMaterials(List<Material> materials) {
- wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
- Row row = null;
- // title
- row = sheet.createRow(0);
- 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, "备注");
- // rows
- int i = 1;
- for (Material material : materials) {
- row = sheet.createRow(i);
- materialToRow(row, material);
- i++;
- }
- }
-
- public void outputBom(Bom bom) {
- wb = new HSSFWorkbook();
- 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");
- setCellString(row, 8, "警告");
- // bom materials
- int i = 1;
- for (BomMaterial bomMaterial : bom.getBomMaterials()) {
- row = sheet.createRow(i);
- bomMaterialToRow(row, bomMaterial);
- i++;
- }
- }
-
- public void outputBomList(List<Bom> boms, String parentPath) {
- wb = new HSSFWorkbook();
- 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());
- }
-
- }
- }
-
- private static String getCode(Row row) {
- return getCellString(row, "code");
- }
-
- private static Material rowToMaterial(Row row, Material material) {
- for (String key : OgnlUtil.getAllStringProperty(Material.class)) {
- OgnlUtil.setValue(material, key, getCellString(row, key));
- }
- String state = material.getState();
- if (state == null || state.isEmpty()) {
- state = "一般";
- }
- if (state.equals("推荐") || state.equals("一般") || state.equals("不推荐") || state.equals("停用")) {
-
- } else {
- state = state + "描述不规范,请检查";
- }
- material.setState(state);
- return material;
- }
-
- private static BomMaterial rowToBomMaterial(Row row, BomMaterial bomMaterial) {
- for (String key : OgnlUtil.getAllStringProperty(BomMaterial.class)) {
- OgnlUtil.setValue(bomMaterial, key, getCellString(row, key));
- }
- 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, material.getTmpCode());
- setCellString(row, 9, material.getState());
- setCellString(row, 10, material.getRemark());
- }
-
- private void bomMaterialToRow(Row row, BomMaterial bomMaterial) {
- 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());
- String error = "";
- if (bomMaterial.getCode().isEmpty()) {
- error = "编码为空";
- } else if (bomMaterial.getMaterial() == null) {
- error = "对应物料不存在";
- } else 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封装不一致";
- }
- CellStyle style = null;
- if (!error.isEmpty()) {
- style = wb.createCellStyle();
- style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
- style.setFillBackgroundColor(IndexedColors.RED.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- }
- setCellString(row, 8, error, style);
- }
-
- public static void setCellString(Row row, int colIndex, String value) {
- setCellString(row, colIndex, value, null);
- }
-
- public static void setCellString(Row row, int colIndex, String value, CellStyle cellStyle) {
- Cell cell = row.createCell(colIndex);
- cell.setCellType(Cell.CELL_TYPE_STRING);
- cell.setCellValue(value);
- if (cellStyle != null) {
- cell.setCellStyle(cellStyle);
- }
- }
-
- public static String getCellString(Sheet sheet, int rowIndex, int colIndex) {
- if (sheet == null) {
- return "";
- }
- return getCellString(sheet.getRow(rowIndex), colIndex);
-
- }
-
- public static String getCellString(Row row, String columnName) {
- return getCellString(row, columnMap.get(columnName));
- }
-
- public static String getCellString(Row row, int colIndex) {
- if (row == null) {
- return "";
- }
- return getCellString(row.getCell(colIndex));
- }
-
- public static String getCellString(Cell cell) {
- String value = "";
- if (cell != null) {
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_NUMERIC:
- value = String.valueOf(cell.getNumericCellValue());
- break;
- case Cell.CELL_TYPE_STRING:// not escape
- value = cell.getStringCellValue().trim();
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- value = String.valueOf(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_BLANK:
- }
- }
- return value;
-
- }
-
- public static boolean isEmptyRow(Row row) {
- for (int i = 0; i < 5; i++) {
- if (!getCellString(row, i).isEmpty()) {
- return false;
- }
- }
- return true;
- }
-
- public static void buildColumnMap(Sheet sheet, ColumnMap columnMap) {
- columnMap.clear();
- Row row = sheet.getRow(0);
- int colNum = row.getLastCellNum();
- String value = null;
- for (int i = 0; i < colNum; i++) {
- value = getCellString(sheet, 0, i);
- if (!value.isEmpty()) {
- columnMap.put(value, i);
- // logger.debug(value + ":" + i);
- }
- }
- }
-
- }