Перейти к основному содержанию
`Ctrl`+`K`

# Загрузка данных из Excel в загрузочную таблицу

Показывает пример обработки табличного файла: задаёт структуру колонок, читает строки листа, преобразует значения, сопоставляет данные со справочниками и создаёт записи в загрузочной таблице. Если часть данных не удалось сопоставить, для записи устанавливается признак ошибки.

Место применения: `Сервис > Инструменты > Выполнить JEXL-скрипт`

Внимание

В текущем виде скрипт содержит захардкоженные названия файла, листа, ресурс и проектные классы. Перед использованием замените значения и API-классы на актуальные для своей задачи.

Тип: JEXL-скрипт

    const sFile = "Производственные задания ККЦ за 2508"; // Название файла
    const sSheetName = "Производственные задания ККЦ 25"; // Название листа
    const Bts_XlsxPkg = lib("Bts_XlsxPkg");

    if (isNull(sFile) || sFile == "") raise(`Не указано название файла\n\n`);
    if (isNull(sSheetName) || sSheetName == "") raise(`Не указано название листа\n\n`);

    const CellDataType = Bts_XlsxPkg.getCellDataTypes();
    const avRowColumnDesc = [
      ["Бизнес-единица", CellDataType.String], // 0
      ["Организация (склад)", CellDataType.String], // 1
      ["Формула", CellDataType.String], // 2
      ["Версия формулы", CellDataType.String], // 3
      ["Номер ПЗ", CellDataType.String], // 4
      ["Тип позиции", CellDataType.String], // 5
      ["Позиция", CellDataType.String], // 6
      ["Описание", CellDataType.String], // 7
      ["Кол-во", CellDataType.String], // 8
      ["ЕИ", CellDataType.String], // 9
      ["Партия", CellDataType.String], // 10
      ["Дата выполнения", CellDataType.String], // 11
      ["Размещение затрат", CellDataType.String], // 12
      ["Техкарта", CellDataType.String], // 13
      ["Описание техкарты", CellDataType.String], // 14
      ["Шаг техкарты", CellDataType.String], // 15
      ["Описание шага", CellDataType.String], // 16
      ["Операция", CellDataType.String], // 17
      ["Дата начала операции", CellDataType.String], // 18
      ["Дата завершения операции", CellDataType.String] // 19
    ];

    const getIdBisObj = (sBisObj) -> {
      const sBisObjQuery = `
    select
      t.id
    from Bs_BisObj t
    where lower(t.sCaption) = lower('${sBisObj}')`;
      const rowaBisObj = sql(sBisObjQuery).asList();
      if (rowaBisObj.size() == 0) return null;
      return rowaBisObj[0].id;
    }

    const getIdMsr = (sMsr) -> {
      const sMsrQuery = `
    select
      t.id
    from msr_measureitem t
    where lower(t.smnemocode_dz) = lower('${sMsr}') or lower(t.sshortname) = lower('${sMsr}')`;
      const rowaMsr = sql(sMsrQuery).asList();
      if (rowaMsr.size() == 0) return null;
      return rowaMsr[0].id;
    }

    const getsCodeMatNormType = (sTypePos) -> {
      if (sTypePos == "Побочный продукт") return "byproduct";
      else if (sTypePos == "Продукт") return "product";
      else if (sTypePos == "Ингредиент") return "ingredient";
      else raise(`Ошибка сопоставления мнемокода Bs_MaterialNormType с sTypePos\n\n`);
    }

    const getGidGds = (sArticle) -> {
      const sProductQuery = `
    select
      t.gid
    from Bs_Goods t
    where lower(t.sArticle) = lower('${sArticle}')`;
      const rowaProduct = sql(sProductQuery).asList();
      if (rowaProduct.size() == 0) return null;
      return rowaProduct[0].gid;
    }

    const getIdCons = (sCons) -> {
      const sConsQuery = `
    select
      t.id
    from Stk_Cons t
    where lower(t.sCaption) = lower('${sCons}')`;
      const rowaCons = sql(sConsQuery).asList();
      if (rowaCons.size() == 0) return null;
      return rowaCons[0].id;
    }

    const getIdStock = (sStock) -> {
      const sQuery = `
    select
      t.id
    from Stk_Stock t
    where lower(t.sMnemoCode_dz) = lower('${sStock}')`;
      const rowaStock = sql(sQuery).asList();
      if (rowaStock.size() == 0) return null;
      return rowaStock[0].id;
    }

    const ExcelStrToDate = (sExcelStr) -> {
      if (sExcelStr == null || sExcelStr == '') return null;
      return sql(`select to_timestamp('${sExcelStr}', 'MM/dd/yy HH24:MI:SS') as date`).asSingle().date;
    }

    const getidQuotaVers = (sQuotaVers, sQuota) -> {
      const sQuotaVersQuery = `
    select
      t.id
    from Qt_QuotaVersion t
    left join Qt_Quota t2 on t2.id = t.idQuota
    where t.nRevision = cast('${sQuotaVers}' as int) and t2.sCode = '${sQuota}'`;
      const rowaQuotaVers = sql(sQuotaVersQuery).asList();
      if (rowaQuotaVers.size() == 0) return null;
      return rowaQuotaVers[0].id;
    }

    const getIdOper = (sOper) -> {
      const sQuery = `
    select
      t.idOper as id
    from Zmmkimp_ProductOperLoad t
    where lower(t.sCode) = lower('${sOper}')
    limit 1
    `;
      const rowaOper = sql(sQuery).asList();
      if (rowaOper.size() == 0) return null;
      return rowaOper[0].id;
    }

    const fun = (x) -> {

      const vFormulaEvaluator = x.getCreationHelper().createFormulaEvaluator();
      const sheet = x.getSheet(sSheetName);
      if (sheet == null) raise(`Лист '${sSheetName}' не существует\n`);
      const lastRowNum = sheet.getLastRowNum();
      if (lastRowNum == -1 || lastRowNum == 0) raise(`Пустой лист '${sSheetName}'\n`);

      var i = 1;

      while (i <= lastRowNum) {
        const vRow = sheet.getRow(i);
        const avCellValue = Bts_XlsxPkg.extractRowAttrs(vFormulaEvaluator, vRow, avRowColumnDesc);

    	const ropWorkAssignmentLoad = Zmmkimp_WorkAssignmentLoadApi.insert();
        var bError = 0;

        const apiWAL = Zmmkimp_WorkAssignmentLoadApi;

    	// В данном случае Ресурс константа
        const sResource = '078';
        if (sResource != "ПУСТОЕ") apiWAL.setsResource(ropWorkAssignmentLoad, sResource);

        const idResource = Rss_ResourceApi.findByMnemoCode(sResource);
        if (isNull(idResource)) bError = 1;
        else apiWAL.setidResource(ropWorkAssignmentLoad, idResource);

        const sBisObj = avCellValue.get(0).asNString();
        if (sBisObj != "ПУСТОЕ") apiWAL.setsBisObj(ropWorkAssignmentLoad, sBisObj);

        const idBisObj = getIdBisObj(sBisObj);
        if (isNull(idBisObj)) bError = 1;
        else apiWAL.setidBisObj(ropWorkAssignmentLoad, idBisObj);

    	const sStock = avCellValue.get(1).asNString();
        if (sStock != "ПУСТОЕ") apiWAL.setsStock(ropWorkAssignmentLoad, sStock);

    	const idStock = getIdStock(sStock);
    	if (isNull(idStock)) bError = 1;
        else apiWAL.setidStock(ropWorkAssignmentLoad, idStock);

    	const sQuota = avCellValue.get(2).asNString();
        if (sQuota != "ПУСТОЕ") apiWAL.setsQuota(ropWorkAssignmentLoad, sQuota);

    	const sQuotaVers = avCellValue.get(3).asNString();
        if (sQuotaVers != "ПУСТОЕ") apiWAL.setsQuotaVers(ropWorkAssignmentLoad, sQuotaVers);

    	const idQuotaVers = getidQuotaVers(sQuotaVers, sQuota);
        if (isNull(idQuotaVers)) bError = 1;
        else apiWAL.setidQuotaVers(ropWorkAssignmentLoad, idQuotaVers);

    	const sWorkAssignment = avCellValue.get(4).asNString();
        if (sWorkAssignment != "ПУСТОЕ") apiWAL.setsWorkAssignment(ropWorkAssignmentLoad, sWorkAssignment);

    	const sTypePos = avCellValue.get(5).asNString();
        if (sTypePos != "ПУСТОЕ") apiWAL.setsTypePos(ropWorkAssignmentLoad, sTypePos);

    	const sCodeMatNormType = getsCodeMatNormType(sTypePos);
        const idMatNormType = Bs_MaterialNormTypeApi.findByMnemoCode(sCodeMatNormType);
        if (isNull(idMatNormType)) bError = 1;
        else apiWAL.setidMatNormType(ropWorkAssignmentLoad, idMatNormType);

    	if (isNotNull(idMatNormType)) {
          const nDirection = Bs_MaterialNormTypeApi.load(idMatNormType).nDirection;
          apiWAL.setnDirection(ropWorkAssignmentLoad, nDirection);
        } else bError = 1;

        const sGdsCode = avCellValue.get(6).asNString();
        if (sGdsCode != "ПУСТОЕ") apiWAL.setsGdsCode(ropWorkAssignmentLoad, sGdsCode);

    	const sGdsDesc = avCellValue.get(7).asNString();
        if (sGdsDesc != "ПУСТОЕ") apiWAL.setsGdsDesc(ropWorkAssignmentLoad, sGdsDesc);

        const gidGds = getGidGds(sGdsCode);
        if (isNull(gidGds)) bError = 1;
        else apiWAL.setgidGds(ropWorkAssignmentLoad, gidGds);

        const nQty = avCellValue.get(8).asNString();
        if (nQty != "ПУСТОЕ") apiWAL.setnQty(ropWorkAssignmentLoad, nQty.toBigDecimal());

        const sMsr = avCellValue.get(9).asNString();
        if (sMsr != "ПУСТОЕ") apiWAL.setsMsr(ropWorkAssignmentLoad, sMsr);

        const idMsr = getIdMsr(sMsr);
        if (isNull(idMsr)) bError = 1;
        else apiWAL.setidMsr(ropWorkAssignmentLoad, idMsr);

        const nConvert = Bs_GoodMsrItemApi.getGdsnRate(parseId(gidGds), idMsr);
        if (isNull(nConvert)) bError = 1;
        else apiWAL.setnConvert(ropWorkAssignmentLoad, nConvert.toBigDecimal());

        // nQtyBase
    	const idGds = parseId(gidGds);
    	if (nQty != "ПУСТОЕ" and isNotNull(idGds) and isNotNull(idMsr)) {
    		const nQtyBase = nQty.toBigDecimal() * nConvert;
    		if (isNull(nQtyBase)) bError = 1;
    		else {
    			// Округление
    			const nvRoundPlaces = Bs_GoodMsrItemApi.getGdsnRoundPlaces(idGds, idMsr);
    			const nvRoundPlacesToInt = round(nvRoundPlaces, 0).toString().toLong().intValue();
    			const nQtyBaseNew = round(nQtyBase, nvRoundPlacesToInt);
    			apiWAL.setnQtyBase(ropWorkAssignmentLoad, nQtyBaseNew);
    		}
    	}

        const sCons = avCellValue.get(10).asNString();
        if (sCons != "ПУСТОЕ") apiWAL.setsCons(ropWorkAssignmentLoad, sCons);

        const idCons = getIdCons(sCons);
        if (isNotNull(idCons)) apiWAL.setidCons(ropWorkAssignmentLoad, idCons);

        const sDate = avCellValue.get(11).asNString();
        if (sDate != "ПУСТОЕ") apiWAL.setdDate(ropWorkAssignmentLoad, ExcelStrToDate(sDate));

        //Размещение затрат
        const nCostAllocation = avCellValue.get(12).asNString();
        if (nCostAllocation != "ПУСТОЕ") apiWAL.setnCostAllocation(ropWorkAssignmentLoad, nCostAllocation.toBigDecimal());

        const sTechCardDoc = avCellValue.get(13).asNString();
        if (sTechCardDoc != "ПУСТОЕ") apiWAL.setsTechCardDoc(ropWorkAssignmentLoad, sTechCardDoc);

    	const sTechCardDocName = avCellValue.get(14).asNString();
        if (sTechCardDocName != "ПУСТОЕ") apiWAL.setsTechCardDocName(ropWorkAssignmentLoad, sTechCardDocName);

    	const nRowOper = avCellValue.get(15).asNString();
        if (nRowOper != "ПУСТОЕ") apiWAL.setnRowOper(ropWorkAssignmentLoad, nRowOper.toBigDecimal());

    	const sOperDesc = avCellValue.get(16).asNString();
        if (sOperDesc != "ПУСТОЕ") apiWAL.setsOperDesc(ropWorkAssignmentLoad, sOperDesc);

    	const sOper = avCellValue.get(17).asNString();
        if (sOper != "ПУСТОЕ") apiWAL.setsOper(ropWorkAssignmentLoad, sOper);

        const idOper = getIdOper(sOper);
        if (isNull(idOper) && sOper != "ПУСТОЕ") bError = 1;
        else apiWAL.setidOper(ropWorkAssignmentLoad, idOper);

        const dPlanBeginOper = avCellValue.get(18).asNString();
        if (dPlanBeginOper != "ПУСТОЕ") apiWAL.setdPlanBeginOper(ropWorkAssignmentLoad, ExcelStrToDate(dPlanBeginOper));

    	const dPlanEndOper = avCellValue.get(19).asNString();
        if (dPlanBeginOper != "ПУСТОЕ") apiWAL.setdPlanEndOper(ropWorkAssignmentLoad, ExcelStrToDate(dPlanEndOper));

    	if (isNotNull(idGds) and isNotNull(idStock) and isNotNull(idBisObj)) {
          const idAcc = Gds_ValLevelApi.getAcc(
                        idpGds = idGds,
                        idpStock = idStock,
                        idpValKind = new ('ru.bitec.app.gtk.lang.NLong', null),
                        idpAccountType = Bs_AccountTypeApi.findByMnemoCode("Acсount"),
                        idpAccKind = Bs_AccKindApi.idAcc(),
                        idpDepOwner = Bs_BisObjApi.load(idBisObj).idDepOwner
                      );
          if (isNotNull(idAcc)) {
    	    const sBC = Bs_AccApi.getMnemoCode(idAcc);
    	    apiWAL.setsBC(ropWorkAssignmentLoad, sBC);
    	  }
        }
    	if(isNull(ropWorkAssignmentLoad.sBC)) bError = 1;

        const sFile = sFile;
        apiWAL.setsFile(ropWorkAssignmentLoad, sFile);

        const sSheet = sSheetName;
        apiWAL.setsSheet(ropWorkAssignmentLoad, sSheet);

        if (bError != 0) apiWAL.setbError(ropWorkAssignmentLoad, 1.toBigDecimal());

        i = i + 1;
        commit();
      }

      return true;
    }

    lib("Btk_XlsxLib").uploadParseFile(fun);