Examples with HSSFFont org.apache.poi.hssf.usermodel.HSSFFont used on opensource projects

Example 26 with HSSFFont

use of org.apache.poi.hssf.usermodel.HSSFFont in project sinsim by WilsonHu.

the class TaskRecordController method exportToExcel.

/**
 *  在”生产管理”的“生产报表”导出到excel.
 */
@PostMapping("/exportToExcel")
public Result exportToExcel(Integer taskRecordId, String taskName, String machineOrderNumber, String queryStartTime, String queryFinishTime, String nameplate) {
    List<TaskRecordDetail> list = taskRecordService.searchTaskRecordDetail(taskRecordId, taskName, machineOrderNumber, queryStartTime, queryFinishTime, nameplate);
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
        返回给docker外部下载
         */
    String downloadPathForNginx = "";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
    String dateString;
    try {
        // 生成一个空的Excel文件
        wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("生产报表");
        // 设置标题行格式
        HSSFCellStyle headcellstyle = wb.createCellStyle();
        HSSFFont headfont = wb.createFont();
        headfont.setFontHeightInPoints((short) 10);
        // 粗体显示
        headfont.setBold(true);
        headcellstyle.setFont(headfont);
        Row row;
        // 创建行和列
        for (int r = 0; r < list.size() + 1; r++) {
            // 新创建一行，行号为row+1
            row = sheet1.createRow(r);
            // 序号，工序名称，订单号，机器编号，安装组长，安装的开始时间，安装的结束时间，耗时
            for (int c = 0; c < 8; c++) {
                // 创建一个单元格，列号为col+1
                row.createCell(c);
                sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
            }
        }
        for (int k = 1; k < 8; k++) {
            sheet1.setColumnWidth(k, 4500);
        }
        // 第一行为标题
        sheet1.getRow(0).getCell(0).setCellValue("序号");
        sheet1.getRow(0).getCell(1).setCellValue("工序名称");
        sheet1.getRow(0).getCell(2).setCellValue("订单号");
        sheet1.getRow(0).getCell(3).setCellValue("铭牌号/机器类型/针数/头数/头距/X行程/Y行程");
        sheet1.getRow(0).getCell(4).setCellValue("安装组长");
        sheet1.getRow(0).getCell(5).setCellValue("开始时间");
        sheet1.getRow(0).getCell(6).setCellValue("结束时间");
        sheet1.getRow(0).getCell(7).setCellValue("耗时(分钟)");
        // 第二行开始，填入值
        MachineType machineType1 = null;
        Byte taskStatus = 0;
        for (int r = 0; r < list.size(); r++) {
            row = sheet1.getRow(r + 1);
            row.getCell(0).setCellValue(r + 1);
            // 工序名称
            if (list.get(r).getTaskName() != null) {
                row.getCell(1).setCellValue(list.get(r).getTaskName());
            }
            // 订单号
            if (list.get(r).getMachineOrder().getOrderNum() != null) {
                row.getCell(2).setCellValue(list.get(r).getMachineOrder().getOrderNum());
            }
            // 机器编号等机器信息
            Machine machine = list.get(r).getMachine();
            MachineOrder machineOrder = list.get(r).getMachineOrder();
            MachineOrderDetail machineOrderDetail = machineOrderService.getOrderAllDetail(machineOrder.getId());
            if (machine != null && machineOrder != null) {
                String machineInfo = machine.getNameplate() + "/" + machineOrderDetail.getMachineType().getName() + "/" + machineOrder.getNeedleNum() + "/" + machineOrder.getHeadNum() + "/" + machineOrder.getHeadDistance() + "/" + machineOrder.getxDistance() + "/" + machineOrder.getyDistance();
                row.getCell(3).setCellValue(machineInfo);
            }
            // 安装组长
            if (list.get(r).getLeader() != null) {
                row.getCell(4).setCellValue(list.get(r).getLeader());
            }
            // 开始时间
            if (list.get(r).getInstallBeginTime() != null) {
                dateString = formatter.format(list.get(r).getInstallBeginTime());
                row.getCell(5).setCellValue(dateString);
            }
            // 结束时间
            if (list.get(r).getInstallEndTime() != null) {
                dateString = formatter.format(list.get(r).getInstallEndTime());
                row.getCell(6).setCellValue(dateString);
            }
            // 耗时
            if (list.get(r).getInstallBeginTime() != null && list.get(r).getInstallEndTime() != null) {
                long minHourDay = commonService.secondsToMin(list.get(r).getInstallEndTime().getTime() - list.get(r).getInstallBeginTime().getTime());
                row.getCell(7).setCellValue(minHourDay);
            }
        }
        downloadPath = taskRecordExcelOutputDir + "生产报表" + ".xls";
        downloadPathForNginx = "/excel/" + "生产报表" + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("异常导出失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}

Example 27 with HSSFFont

use of org.apache.poi.hssf.usermodel.HSSFFont in project sinsim by WilsonHu.

the class TaskQualityRecordController method export.

/**
 * 生成 质检异常的excel表格
 */
@PostMapping("/export")
public Result export(String nameplate, String orderNum, String taskName, Integer submitUser, Integer solutionUser, Integer finishStatus, String queryStartTime, String queryFinishTime) {
    List<TaskQualityRecordDetail> list = taskQualityRecordService.selectTaskQualityList(nameplate, orderNum, taskName, submitUser, solutionUser, finishStatus, queryStartTime, queryFinishTime);
    HSSFWorkbook wb = null;
    FileOutputStream out = null;
    String downloadPath = "";
    /*
        返回给docker外部下载
         */
    String downloadPathForNginx = "";
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
    String dateString;
    try {
        // 生成一个空的Excel文件
        wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("sheet1");
        // 设置标题行格式
        HSSFCellStyle headcellstyle = wb.createCellStyle();
        HSSFFont headfont = wb.createFont();
        headfont.setFontHeightInPoints((short) 10);
        headfont.setBold(true);
        headcellstyle.setFont(headfont);
        Row row;
        // 创建行和列
        for (int r = 0; r < list.size() + 1; r++) {
            row = sheet1.createRow(r);
            for (int c = 0; c < 8; c++) {
                row.createCell(c);
                sheet1.getRow(0).getCell(c).setCellStyle(headcellstyle);
            }
        }
        sheet1.setColumnWidth(0, 1500);
        sheet1.setColumnWidth(1, 4000);
        sheet1.setColumnWidth(2, 4000);
        sheet1.setColumnWidth(3, 4000);
        sheet1.setColumnWidth(4, 4000);
        sheet1.setColumnWidth(5, 10000);
        sheet1.setColumnWidth(6, 4000);
        sheet1.setColumnWidth(7, 4000);
        // 第一行为标题
        sheet1.getRow(0).getCell(0).setCellValue("序号");
        sheet1.getRow(0).getCell(1).setCellValue("机器编号");
        sheet1.getRow(0).getCell(2).setCellValue("工序");
        sheet1.getRow(0).getCell(3).setCellValue("提交者");
        sheet1.getRow(0).getCell(4).setCellValue("解决者");
        sheet1.getRow(0).getCell(5).setCellValue("解决方法");
        sheet1.getRow(0).getCell(6).setCellValue("创建时间");
        sheet1.getRow(0).getCell(7).setCellValue("解决时间");
        // 第二行开始，填入值
        for (int r = 0; r < list.size(); r++) {
            row = sheet1.getRow(r + 1);
            row.getCell(0).setCellValue(r + 1);
            row.getCell(1).setCellValue(list.get(r).getMachine().getNameplate());
            row.getCell(2).setCellValue(list.get(r).getTaskRecord().getTaskName());
            row.getCell(3).setCellValue(list.get(r).getSubmitUser());
            // 安装异常时， 还不知道SolutionUser， SolutionUser是null
            if (list.get(r).getSolutionUser() != null) {
                row.getCell(4).setCellValue(list.get(r).getSolutionUser());
            }
            row.getCell(5).setCellValue(list.get(r).getSolution());
            dateString = formatter.format(list.get(r).getCreateTime());
            row.getCell(6).setCellValue(dateString);
            if (list.get(r).getSolveTime() != null) {
                dateString = formatter.format(list.get(r).getSolveTime());
                row.getCell(7).setCellValue(dateString);
            }
        }
        downloadPath = taskQualityRecordExcelOutputDir + "质检异常统计" + ".xls";
        downloadPathForNginx = "/excel/" + "质检异常统计" + ".xls";
        out = new FileOutputStream(downloadPath);
        wb.write(out);
        out.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    if ("".equals(downloadPath)) {
        return ResultGenerator.genFailResult("质检异常导出失败!");
    } else {
        return ResultGenerator.genSuccessResult(downloadPathForNginx);
    }
}

Example 28 with HSSFFont

use of org.apache.poi.hssf.usermodel.HSSFFont in project adempiere by adempiere.

the class SmjXlsReport method reportTable.

// titleTable
/**
	 * llena los datos del reporte - fill report data
	 * @param book
	 * @param data
	 * @param sheet
	 * @param fila
	 */
public void reportTable(HSSFWorkbook book, LinkedList<ReportTO> data, HSSFSheet sheet, int fila) {
    HSSFRow row;
    // crea fuente - create font
    HSSFFont font = book.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName(HSSFFont.FONT_ARIAL);
    HSSFRichTextString text;
    Iterator<ReportTO> itRep = data.iterator();
    Boolean newRow = false;
    sheet.setColumnWidth((short) 0, (short) (13 * 256));
    sheet.setColumnWidth((short) 1, (short) (60 * 256));
    for (int i = 2; i < (cols); i++) {
        sheet.setColumnWidth((short) i, (short) (15 * 256));
    }
    //for
    // estio celda - cell style
    HSSFCellStyle cellStyle = book.createCellStyle();
    HSSFCellStyle cellStyleD = book.createCellStyle();
    HSSFCellStyle cellStyleN = book.createCellStyle();
    while (itRep.hasNext()) {
        short col = 0;
        ReportTO rpt = itRep.next();
        if (!newRow) {
            cellStyle = book.createCellStyle();
            cellStyleD = book.createCellStyle();
            cellStyleN = book.createCellStyle();
        }
        //if
        newRow = false;
        if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("T")) {
            // Coloca titulo - put title
            row = sheet.createRow(fila++);
            HSSFFont fontT = book.createFont();
            fontT.setFontHeightInPoints((short) 12);
            fontT.setFontName(HSSFFont.FONT_ARIAL);
            fontT.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            HSSFCellStyle cellStyleT = book.createCellStyle();
            cellStyleT.setWrapText(true);
            cellStyleT.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyleT.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
            cellStyleT.setFont(fontT);
            Region region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
            sheet.addMergedRegion(region);
            text = new HSSFRichTextString(rpt.getDescription());
            HSSFCell cellT = row.createCell(col);
            cellT.setCellStyle(cellStyleT);
            cellT.setCellValue(text);
            newRow = true;
        } else if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("L")) {
            // coloca linea en el reporte - Put under line in the report
            cellStyle.setWrapText(true);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setBottomBorderColor((short) 8);
            cellStyleD.setWrapText(true);
            cellStyleD.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyleD.setBottomBorderColor((short) 8);
            cellStyleN.setWrapText(true);
            cellStyleN.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyleN.setBottomBorderColor((short) 8);
            newRow = true;
        } else if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("X")) {
            // coloca linea de total - Put total line
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyle.setBottomBorderColor((short) 8);
            newRow = true;
        } else if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("Z")) {
            // coloca linea doble de total - Put total line doble
            cellStyle.setWrapText(true);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);
            cellStyle.setBottomBorderColor((short) 8);
            //--------------
            row = sheet.createRow(fila++);
            ReportTO rptD = new ReportTO();
            putRow(cellStyle, cellStyleD, cellStyleN, sheet, row, fila, rptD);
            cellStyle = book.createCellStyle();
            newRow = true;
        } else if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("D")) {
            // coloca liena de descripcion - put description line
            cellStyleD.setWrapText(true);
            cellStyleD.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
            cellStyleD.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
            cellStyleD.setBottomBorderColor((short) 8);
            newRow = true;
        } else if (rpt.getReportlinestyle() != null && rpt.getReportlinestyle().equals("S")) {
            // coloca linea en blanco - put empty line
            row = sheet.createRow(fila++);
            newRow = true;
        } else if (rpt.getTablevel() != null && rpt.getTablevel() > 0) {
            // coloca espacios a la izquierda para simular jeraquia - put
            // left spaces to simulate hierarchy
            row = sheet.createRow(fila++);
            String jerarchy = "";
            for (int i = 1; i <= rpt.getTablevel(); i++) {
                jerarchy = jerarchy + "   ";
            }
            //for
            Region region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
            sheet.addMergedRegion(region);
            text = new HSSFRichTextString(jerarchy + rpt.getDescription());
            HSSFCell cellJ = row.createCell(col);
            cellJ.setCellValue(text);
            newRow = true;
        } else {
            row = sheet.createRow(fila++);
            putRow(cellStyle, cellStyleD, cellStyleN, sheet, row, fila, rpt);
        }
    //else
    }
// while itData
}

Example 29 with HSSFFont

use of org.apache.poi.hssf.usermodel.HSSFFont in project adempiere by adempiere.

the class SmjXlsReport method generate.

public HSSFWorkbook generate(LinkedList<ReportTO> data, String[] generalTitle, String clientName, String clientNIT, String periodName, String currencyName, MReportColumn[] m_columns, String city, Integer logoId) {
    int fila = 0;
    HSSFRow row;
    cols = m_columns.length + 2;
    endRegion = (short) (cols - 1);
    try {
        // create workbook
        HSSFWorkbook book = new HSSFWorkbook();
        // crea hoja - create sheet
        // Goodwill BF: Invalid sheet name
        HSSFSheet sheet = book.createSheet(StringUtils.makePrefix(generalTitle[0]));
        // crea fuente - Create Font
        HSSFFont font = book.createFont();
        font.setFontHeightInPoints((short) 13);
        font.setFontName(HSSFFont.FONT_ARIAL);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // estio celda - cell style
        HSSFCellStyle cellStyle = book.createCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        cellStyle.setFont(font);
        // add logo
        if (logoId > 0) {
            MImage mimage = MImage.get(Env.getCtx(), logoId);
            byte[] imageData = mimage.getData();
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor;
            anchor = new HSSFClientAnchor(100, 50, 200, 255, (short) 0, 0, (short) 1, 1);
            anchor.setAnchorType(2);
            int pictureIndex = book.addPicture(imageData, HSSFWorkbook.PICTURE_TYPE_PNG);
            patriarch.createPicture(anchor, pictureIndex);
            for (int i = 0; i < 5; i++) row = sheet.createRow(fila++);
        }
        //if Logo report
        // Titulo General - general Title
        row = sheet.createRow(fila++);
        HSSFRichTextString text = new HSSFRichTextString(generalTitle[0]);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        Region region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        // empresa - Company
        row = sheet.createRow(fila++);
        text = new HSSFRichTextString(clientName);
        cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        // Ciudad - City
        row = sheet.createRow(fila++);
        text = new HSSFRichTextString(city);
        cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        // NIT
        row = sheet.createRow(fila++);
        text = new HSSFRichTextString(clientNIT);
        cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        // periodo - Period
        String pn = "";
        if (generalTitle[1] != null && generalTitle[1].length() > 0) {
            pn = generalTitle[1] + " " + periodName;
        } else {
            pn = periodName;
        }
        if (generalTitle[2] != null && generalTitle[2].length() > 0) {
            pn = pn + " " + generalTitle[2];
        }
        row = sheet.createRow(fila++);
        text = new HSSFRichTextString(pn);
        cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        // tipo moneda - currency
        row = sheet.createRow(fila++);
        text = new HSSFRichTextString(currencyName);
        cell = row.createCell((short) 0);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
        region = new Region(fila - 1, (short) 0, fila - 1, endRegion);
        sheet.addMergedRegion(region);
        row = sheet.createRow(fila++);
        titleTable(book, sheet, fila++, m_columns);
        // llena datos del reporte - fill data report
        reportTable(book, data, sheet, fila);
        return book;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
//try/catch
}

Example 30 with HSSFFont

use of org.apache.poi.hssf.usermodel.HSSFFont in project adempiere by adempiere.

the class SmjXlsReport method titleTable.

// generate
/**
	 * Crea la fila de titulos - create title row
	 * @param wb
	 * @param hs
	 * @param fila
	 * @param colsName
	 */
private void titleTable(HSSFWorkbook book, HSSFSheet sheet, int fila, MReportColumn[] m_columns) {
    short col = 0;
    // crea fuente - create font
    HSSFFont font = book.createFont();
    font.setFontHeightInPoints((short) 13);
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //		font.setColor(HSSFColor.BLUE.index);
    // estio celda - cell style
    HSSFCellStyle cellStyle = book.createCellStyle();
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    //		cellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
    //		cellStyle.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
    cellStyle.setFont(font);
    // //Titulos de la tabla - Table titles
    HSSFRow row = sheet.createRow(fila);
    // Nombre - name
    HSSFRichTextString text = new HSSFRichTextString(Msg.translate(Env.getCtx(), "name").toUpperCase());
    HSSFCell cell = row.createCell(col++);
    cell.setCellStyle(cellStyle);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(text);
    // Desripcion - description
    text = new HSSFRichTextString(Msg.translate(Env.getCtx(), "description").toUpperCase());
    cell = row.createCell(col++);
    cell.setCellStyle(cellStyle);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(text);
    // columnas de valores - Value Columns
    for (MReportColumn mcol : m_columns) {
        String colName = mcol.getName();
        text = new HSSFRichTextString(colName.toUpperCase());
        cell = row.createCell(col++);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);
    }
//for columnas
}