Загрузка данных из 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);