CP1251

var version = java.lang.System.getProperty("java.version");
if (version.startsWith("1.8.0")) {
    load("nashorn:mozilla_compat.js");
}

importPackage(java.io);
importPackage(org.apache.poi.hssf.usermodel);
importPackage(org.apache.poi.hssf.util);
importPackage(org.apache.log4j);

logger = LogManager.getLogger('Logger');


wb = new HSSFWorkbook();

sheet = wb.createSheet('_');
ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight(1000);
ps.setFitWidth(1);


font_header = wb.createFont(); font_header.setFontHeightInPoints(16);
style1 = wb.createCellStyle(); style1.setFont(font_header);

font = wb.createFont(); font.setFontHeightInPoints(12); font.setFontName("Arial");
style2 = wb.createCellStyle(); style2.setFont(font);

logger.debug('style2.CENTER: ' + style1.CENTER);
logger.debug('style2.ALIGN_CENTER: ' + style2.ALIGN_CENTER);


row = sheet.createRow(0);
row.setHeightInPoints(20);

cell = row.createCell(1);
cell.setCellValue("Отчет по использованию материальных средств за период с " + REPORT.getValue('date1') + " по " + REPORT.getValue('date2') + (SKLAD_NAME.getValue()==null ? ", все склады" : ", склад: " + SKLAD_NAME.getValue()));
cell.setCellStyle(style1);
sheet.addMergedRegion(new CellRangeAddress(0,0,1,10));
sheet.createFreezePane(0, 3); 

row = sheet.createRow(1);
row.setHeightInPoints(30);
cell = row.createCell(0); cell.setCellValue("№ п/п"); cell.setCellStyle(style2);
cell = row.createCell(1); cell.setCellValue("Наименование материала"); cell.setCellStyle(style2);
cell = row.createCell(2); cell.setCellValue("Потребность \nна план"); cell.setCellStyle(style2);
cell = row.createCell(3); cell.setCellValue("Наличие"); cell.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(1,1,3,5));
cell = row.createCell(6); cell.setCellValue("Израсходовано"); cell.setCellStyle(style2);
sheet.addMergedRegion(new CellRangeAddress(1,1,6,9));
cell = row.createCell(10); cell.setCellValue("Остатки цех"); cell.setCellStyle(style2);
cell = row.createCell(11); cell.setCellValue("Примечание"); cell.setCellStyle(style2);
row = sheet.createRow(2);
cell = row.createCell(3); cell.setCellValue("В цехе на начало месяца"); cell.setCellStyle(style2);
cell = row.createCell(4); cell.setCellValue("Выдано со склада"); cell.setCellStyle(style2);
cell = row.createCell(5); cell.setCellValue("Всего"); cell.setCellStyle(style2);
cell = row.createCell(6); cell.setCellValue("По потребности"); cell.setCellStyle(style2);
cell = row.createCell(7); cell.setCellValue("Тех. потери"); cell.setCellStyle(style2);
cell = row.createCell(8); cell.setCellValue("Брак"); cell.setCellStyle(style2);
cell = row.createCell(9); cell.setCellValue("Всего"); cell.setCellStyle(style2);

for (i = 0; i < REPORT.getRowCount(); i ++)
{
   row = sheet.createRow(3 + i);
   row.setHeightInPoints(15);
   cell = row.createCell(0);
   cell.setCellValue(i + 1);
   cell = row.createCell(1);
   cell.setCellValue(REPORT.getValue(i, 'name_active'));
   cell = row.createCell(2);
   cell.setCellValue(REPORT.getValue(i, 'treba'));
   cell = row.createCell(3);
   cell.setCellValue(REPORT.getValue(i, 'ost_tseh1'));
   cell = row.createCell(4);
   cell.setCellValue(REPORT.getValue(i, 'perem'));
   cell = row.createCell(5);
   cell.setCellValue(REPORT.getValue(i, 'ost_tseh1') + REPORT.getValue(i, 'perem'));
   cell = row.createCell(6);
   cell.setCellValue(REPORT.getValue(i, 'kolvo1'));
   cell = row.createCell(7);
   cell.setCellValue(REPORT.getValue(i, 'kolvo2'));
   cell = row.createCell(8);
   cell.setCellValue(REPORT.getValue(i, 'kolvo3'));
   cell = row.createCell(9);
   cell.setCellValue(REPORT.getValue(i, 'kolvo_itogo'));
   cell = row.createCell(10);
   cell.setCellValue(REPORT.getValue(i, 'ost_tseh2'));
}

for (i = 0; i < 12; i ++) sheet.autoSizeColumn(i);

// формируем имя файла
str = java.text.DateFormat.getDateTimeInstance().format(new java.util.Date);
str = str.replaceAll(':', '_');
str = str.replaceAll(' ', '_');
f = new File(java.lang.System.getProperty('java.io.tmpdir') + 'tmp_' + str + '.xls');

// записываем файл
try
{
  logger.debug('creating file ' + f.getAbsoluteFile());
  fileOut = new FileOutputStream(f.getAbsoluteFile());
  wb.write(fileOut);
  fileOut.close();
  java.awt.Desktop.getDesktop().open(f);
}
catch (e)
{
  logger.debug('Error creating file: ' + e);
}
f['delete']();


ret = '';
