Example usage for org.apache.poi.hssf.usermodel HSSFRow createCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. createCell

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFRow createCell.

Prototype

@Override
public HSSFCell createCell(int column)

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java

public /*static*/ void criaXLS(JTable tabela, String arqSaida, String nomePlan)
        throws IOException, NullPointerException {
    int numLinhas = tabela.getRowCount();
    int numCelulas = tabela.getColumnCount();

    //Cria pasta de trabalho
    wb = new HSSFWorkbook();
    //Cria planilha
    HSSFSheet s = wb.createSheet(nomePlan);
    //wb.setSheetName(0, nomePlan);

    //Alterar para a quantidade de linhas retornada por cada relatrio
    for (int i = 0; i < numLinhas; i++) {

        //Cria a linha
        HSSFRow linha = s.createRow(i);

        //Alterar para a quantidade de clulas retornada por cada relatrio
        for (int j = 0; j < numCelulas; j++) {
            HSSFCell celula = linha.createCell(j);

            /*O valor passado deve ser de acordo com aquele recebido por cada relatrio, 
                        provavelmente String;*/
            celula.setCellValue((String) tabela.getValueAt(i, j));
        }/*from   w w w  .j  a  va2  s  . c om*/
    }

    FileOutputStream fileOutputStream = new FileOutputStream(arqSaida + ".xls");
    try {
        wb.write(fileOutputStream);
        //Fecha o fileOutputStream
        //Melhorar este catch    
    } catch (IOException ex) {
        System.out.println("Teste");
    } finally {
        fileOutputStream.close();
        wb.close(); //Fecha a pasta de trabalho
    }
}

From source file:com.brick.customer.util.CustomerInfoExcel.java

@SuppressWarnings("unchecked")
public HSSFWorkbook createReport(Map<String, Object> params, Context context) throws Exception {

    ExcelFileWriter efw = new ExcelFileWriter();
    HSSFSheet sheet = efw.createSheet(context.contextMap.get("sheetName") == null ? "summary"
            : (String) context.contextMap.get("sheetName"));
    List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) params.get("cusInfo");

    sheet.setColumnWidth(0, 5000);//  w w w .  j av a2 s  .c  om
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 5300);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 4600);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 6000);
    sheet.setColumnWidth(7, 4000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 3500);
    sheet.setColumnWidth(10, 3500);
    sheet.setColumnWidth(11, 4200);
    sheet.setColumnWidth(12, 4200);
    sheet.setColumnWidth(13, 4200);
    sheet.setColumnWidth(14, 4800);
    sheet.setColumnWidth(15, 5000);
    sheet.setColumnWidth(16, 5000);
    sheet.setColumnWidth(17, 3000);
    sheet.setColumnWidth(18, 4500);
    sheet.setColumnWidth(19, 4500);
    sheet.setColumnWidth(20, 3000);
    sheet.setColumnWidth(21, 4300);
    sheet.setColumnWidth(22, 4000);
    sheet.setColumnWidth(23, 7000);
    sheet.setColumnWidth(24, 10000);

    HSSFFont headFont0 = null;
    HSSFCellStyle headStyle0 = null;

    headFont0 = efw.getWorkbook().createFont();
    headFont0.setFontHeightInPoints((short) 13); //?
    headFont0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //    

    headStyle0 = efw.getWorkbook().createCellStyle(); //?
    headStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?   
    headStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //     
    headStyle0.setWrapText(true); // ?  
    headStyle0.setFillBackgroundColor((short) 59);
    headStyle0.setFont(headFont0);

    //??
    HSSFCellStyle cellMoney = efw.getWorkbook().createCellStyle();
    HSSFDataFormat format = efw.getWorkbook().createDataFormat();
    cellMoney.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellMoney.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellMoney.setDataFormat(format.getFormat("#,###,##0"));

    HSSFRow row0 = sheet.createRow(0);
    HSSFCell cell0 = row0.createCell(0);
    cell0.setCellValue("?");
    cell0.setCellStyle(headStyle0);

    HSSFCell cell1 = row0.createCell(1);
    cell1.setCellValue("??");
    cell1.setCellStyle(headStyle0);

    HSSFCell cell2 = row0.createCell(2);
    cell2.setCellValue("???");
    cell2.setCellStyle(headStyle0);

    HSSFCell cell3 = row0.createCell(3);
    cell3.setCellValue("??");
    cell3.setCellStyle(headStyle0);

    HSSFCell cell4 = row0.createCell(4);
    cell4.setCellValue("?");
    cell4.setCellStyle(headStyle0);

    HSSFCell cell5 = row0.createCell(5);
    cell5.setCellValue("??");
    cell5.setCellStyle(headStyle0);

    HSSFCell cell6 = row0.createCell(6);
    cell6.setCellValue("??");
    cell6.setCellStyle(headStyle0);

    HSSFCell cell7 = row0.createCell(7);
    cell7.setCellValue("?");
    cell7.setCellStyle(headStyle0);

    HSSFCell cell8 = row0.createCell(8);
    cell8.setCellValue("?");
    cell8.setCellStyle(headStyle0);

    HSSFCell cell9 = row0.createCell(9);
    cell9.setCellValue("");
    cell9.setCellStyle(headStyle0);

    HSSFCell cell10 = row0.createCell(10);
    cell10.setCellValue("");
    cell10.setCellStyle(headStyle0);

    HSSFCell cell11 = row0.createCell(11);
    cell11.setCellValue("??");
    cell11.setCellStyle(headStyle0);

    HSSFCell cell12 = row0.createCell(12);
    cell12.setCellValue("?");
    cell12.setCellStyle(headStyle0);

    HSSFCell cell13 = row0.createCell(13);
    cell13.setCellValue("?");
    cell13.setCellStyle(headStyle0);

    HSSFCell cell14 = row0.createCell(14);
    cell14.setCellValue("");
    cell14.setCellStyle(headStyle0);

    HSSFCell cell15 = row0.createCell(15);
    cell15.setCellValue("?");
    cell15.setCellStyle(headStyle0);

    HSSFCell cell16 = row0.createCell(16);
    cell16.setCellValue("??");
    cell16.setCellStyle(headStyle0);

    HSSFCell cell17 = row0.createCell(17);
    cell17.setCellValue("?");
    cell17.setCellStyle(headStyle0);

    HSSFCell cell18 = row0.createCell(18);
    cell18.setCellValue("??");
    cell18.setCellStyle(headStyle0);

    HSSFCell cell19 = row0.createCell(19);
    cell19.setCellValue("?");
    cell19.setCellStyle(headStyle0);

    HSSFCell cell20 = row0.createCell(20);
    cell20.setCellValue("");
    cell20.setCellStyle(headStyle0);

    HSSFCell cell21 = row0.createCell(21);
    cell21.setCellValue("???");
    cell21.setCellStyle(headStyle0);

    HSSFCell cell22 = row0.createCell(22);
    cell22.setCellValue("??");
    cell22.setCellStyle(headStyle0);

    HSSFCell cell23 = row0.createCell(23);
    cell23.setCellValue("??");
    cell23.setCellStyle(headStyle0);

    HSSFCell cell24 = row0.createCell(24);
    cell24.setCellValue("");
    cell24.setCellStyle(headStyle0);

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row1 = sheet.createRow(i + 1);

        HSSFCell cellr0 = row1.createCell(0);
        cellr0.setCellValue((String) list.get(i).get("CUST_CODE"));

        HSSFCell cellr1 = row1.createCell(1);
        cellr1.setCellValue((String) list.get(i).get("NAME"));

        HSSFCell cellr2 = row1.createCell(2);
        cellr2.setCellValue((String) list.get(i).get("CUST_NAME"));

        HSSFCell cellr3 = row1.createCell(3);
        cellr3.setCellValue((String) list.get(i).get("CORP_ORAGNIZATION_CODE"));

        HSSFCell cellr4 = row1.createCell(4);
        cellr4.setCellValue((String) list.get(i).get("CUST_AREA"));

        HSSFCell cellr5 = row1.createCell(5);
        cellr5.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr6 = row1.createCell(6);
        cellr6.setCellValue((String) list.get(i).get("VIRTUAL_CODE"));

        HSSFCell cellr7 = row1.createCell(7);
        String s = null;
        int type = Integer.parseInt(list.get(i).get("STATETYPE").toString());
        switch (type) {
        case 0:
            s = "";
            break;
        case 1:
            s = "";
            break;
        case 2:
            s = "??";
            break;
        case 3:
            s = "";
            break;
        case 4:
            s = "";
            break;
        }
        cellr7.setCellValue(s);

        HSSFCell cellr8 = row1.createCell(8);
        cellr8.setCellValue((String) list.get(i).get("CORP_SETUP_DATE"));

        HSSFCell cellr9 = row1.createCell(9);
        double n = list.get(i).get("CORP_REGISTE_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_REGISTE_CAPITAL");
        cellr9.setCellValue(n);
        cellr9.setCellStyle(cellMoney);

        HSSFCell cellr10 = row1.createCell(10);
        double m = list.get(i).get("CORP_PAICLUP_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_PAICLUP_CAPITAL");
        cellr10.setCellValue(m);
        cellr10.setCellStyle(cellMoney);

        HSSFCell cellr11 = row1.createCell(11);
        cellr11.setCellValue((String) list.get(i).get("CORP_BUSINESS_LICENSE"));

        HSSFCell cellr12 = row1.createCell(12);
        cellr12.setCellValue((String) list.get(i).get("TAX_CODE"));

        HSSFCell cellr13 = row1.createCell(13);
        cellr13.setCellValue((String) list.get(i).get("CORP_TAX_CODE"));

        HSSFCell cellr14 = row1.createCell(14);
        cellr14.setCellValue((String) list.get(i).get("CORP_PERIOD_VALIDITY"));

        HSSFCell cellr15 = row1.createCell(15);
        cellr15.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr16 = row1.createCell(16);
        cellr16.setCellValue((String) list.get(i).get("CORP_BUSINESS_RANGE"));

        HSSFCell cellr17 = row1.createCell(17);
        cellr17.setCellValue((String) list.get(i).get("CORP_COMPANY_ZIP"));

        HSSFCell cellr18 = row1.createCell(18);
        cellr18.setCellValue((String) list.get(i).get("CORP_COMPANY_WEBSITE"));

        HSSFCell cellr19 = row1.createCell(19);
        cellr19.setCellValue((String) list.get(i).get("CORP_COMPANY_EMAIL"));

        HSSFCell cellr20 = row1.createCell(20);
        cellr20.setCellValue((String) list.get(i).get("CORP_HEAD_SIGNATURE"));

        HSSFCell cellr21 = row1.createCell(21);
        cellr21.setCellValue((String) list.get(i).get("CORP_HS_IDCARD"));

        HSSFCell cellr22 = row1.createCell(22);
        cellr22.setCellValue((String) list.get(i).get("CORP_HS_LINK_MODE"));

        HSSFCell cellr23 = row1.createCell(23);
        cellr23.setCellValue((String) list.get(i).get("CORP_HS_HOME_ADDRESS"));

        HSSFCell cellr24 = row1.createCell(24);
        cellr24.setCellValue((String) list.get(i).get("REMARK"));
    }
    return efw.getWorkbook();
}

From source file:com.carfinance.module.common.controller.DocumentDownloadController.java

/**
 * ????Excel// w  ww  . j a  v  a 2s .  co m
 * @param model
 * @param request
 * @param response
 */
@RequestMapping(value = "/cashierstatement", method = RequestMethod.GET)
public void cashierStatement(Model model, HttpServletRequest request, HttpServletResponse response) {
    String contrace_id_str = request.getParameter("contrace_id");

    String contrace_no = "";
    String customer_name = "";
    String customer_no = "";
    String daily_available_km = "";
    String contrace_type = "";

    VehicleContraceInfo vehicleContraceInfo = this.vehicleServiceManageService
            .getVehicleContraceInfoById(Long.valueOf(contrace_id_str));
    if (vehicleContraceInfo != null) {
        contrace_no = vehicleContraceInfo.getContrace_no();
        customer_name = vehicleContraceInfo.getCustomer_name();
        daily_available_km = vehicleContraceInfo.getDaily_available_km() + "";
    } else {
        PropertyContraceInfo propertyContraceInfo = this.vehicleServiceManageService
                .getPropertyContraceInfoById(Long.valueOf(contrace_id_str));
        if (propertyContraceInfo != null) {
            contrace_no = propertyContraceInfo.getContrace_no();
            customer_name = propertyContraceInfo.getCustomer_name();
            contrace_type = "?";
        }
    }

    List<VehicleContraceVehsInfo> vehicleContraceVehsInfoList = this.vehicleServiceManageService
            .getVehicleContraceVehsListByContraceId(Long.valueOf(contrace_id_str));

    //1.ContentType
    response.setContentType("multipart/form-data");
    //2.????(??a.pdf)
    response.setHeader("Content-Disposition", "attachment;fileName=" + contrace_no + ".xls");

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();

    for (VehicleContraceVehsInfo v : vehicleContraceVehsInfoList) {
        // webbooksheet,Excelsheet
        HSSFSheet sheet = wb.createSheet(v.getLicense_plate());
        // sheet0,??poiExcel?short
        HSSFRow row = sheet.createRow((int) 0);
        // ? 
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("??");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("?");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("?");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("");
        cell.setCellStyle(style);

        row = sheet.createRow(1);
        row.createCell((short) 0).setCellValue(customer_name);
        row.createCell((short) 1).setCellValue(customer_no);
        row.createCell((short) 2).setCellValue(contrace_type);
        row.createCell((short) 3).setCellValue("");
        row.createCell((short) 4).setCellValue("");

        row = sheet.createRow(2);
        HSSFCell cell2 = row.createCell((short) 0);
        cell2.setCellValue("?");
        cell2.setCellStyle(style);
        cell2 = row.createCell((short) 1);
        cell2.setCellValue("??");
        cell2.setCellStyle(style);
        cell2 = row.createCell((short) 2);
        cell2.setCellValue("");
        cell2.setCellStyle(style);
        cell2 = row.createCell((short) 3);
        cell2.setCellValue("");
        cell2.setCellStyle(style);
        cell2 = row.createCell((short) 4);
        cell2.setCellValue("??");
        cell2.setCellStyle(style);

        row = sheet.createRow(3);
        row.createCell((short) 0).setCellValue(v.getKm());
        row.createCell((short) 1).setCellValue(v.getOil_percent());
        long over_km = (v.getReturn_km() - v.getKm()) > 0 ? (v.getReturn_km() - v.getKm()) : 0;
        row.createCell((short) 2).setCellValue(over_km);
        row.createCell((short) 3).setCellValue("");
        row.createCell((short) 4).setCellValue("");

        row = sheet.createRow(4);
        HSSFCell cell4 = row.createCell((short) 0);
        cell4.setCellValue("");
        cell4.setCellStyle(style);
        cell4 = row.createCell((short) 1);
        cell4.setCellValue("?");
        cell4.setCellStyle(style);
        cell4 = row.createCell((short) 2);
        cell4.setCellValue("");
        cell4.setCellStyle(style);
        cell4 = row.createCell((short) 3);
        cell4.setCellValue("");
        cell4.setCellStyle(style);
        cell4 = row.createCell((short) 4);
        cell4.setCellValue("");
        cell4.setCellStyle(style);

        row = sheet.createRow(5);
        row.createCell((short) 0).setCellValue(v.getReturn_km());
        row.createCell((short) 1).setCellValue(v.getRevert_oil_percent());
        long over_oil = (v.getOil_percent() - v.getRevert_oil_percent()) > 0
                ? (v.getOil_percent() - v.getRevert_oil_percent())
                : 0;
        row.createCell((short) 2).setCellValue(over_oil);
        row.createCell((short) 3).setCellValue("");
        row.createCell((short) 4).setCellValue(v.getActually_price());
    }

    // ?
    try {

        String path = appProps.get("contrace.over.download.path") + contrace_no + ".xls";
        FileOutputStream fout = new FileOutputStream(path);
        wb.write(fout);
        fout.close();

        ServletOutputStream out;
        //File(?download.pdf)
        File file = new File(path);

        try {
            FileInputStream inputStream = new FileInputStream(file);
            //3.response?ServletOutputStream(out)
            out = response.getOutputStream();
            int b = 0;
            byte[] buffer = new byte[512];
            while (b != -1) {
                b = inputStream.read(buffer);
                //4.?(out)
                out.write(buffer, 0, b);
            }
            inputStream.close();
            out.close();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.cimmyt.reports.impl.ServiceReportCustomQueryImpl.java

License:Apache License

private void createCell(HSSFRow rowData, int sizeColumn, String strValue, HSSFCellStyle style) {
    HSSFCell dataCell = rowData.createCell(sizeColumn);
    HSSFRichTextString cellValue = new HSSFRichTextString(strValue);
    if (style != null)
        dataCell.setCellStyle(style);/*w  w  w  .ja  v  a2  s  .  c o m*/
    dataCell.setCellType(HSSFCell.CELL_TYPE_STRING);
    dataCell.setCellValue(cellValue);
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

/**
 * Method that load the sample detail into the HSSFWorkbook
 *//*from   ww  w .j av a 2 s.c o m*/
private void loadExcelList() {
    String rutaResumen = property.getKey(REPORT_PATH_KBIO, Bundle.conf);
    InputStream inputStream = getClass().getResourceAsStream(rutaResumen);
    try {
        listBook = new HSSFWorkbook(inputStream);
        HSSFSheet sheetDetails = listBook.getSheetAt(0);
        loadDetailSheet(sheetDetails);
        HSSFSheet sheetList = listBook.getSheetAt(1);
        HSSFSheet actsheetUsefull = listBook.getSheetAt(2);
        int inidatsheetList = 2;
        int inidatsheetUsefull = 1;
        String wellkbio;
        String platenameact = "";
        Integer platevoy = 0;
        Integer rowusefull = 0;
        int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
        int plates = setShipmentDetail.size() / mod;
        //Crear hojas usefull que necesitare dependiendo del numero de platos
        for (int j = 1; j < plates; j++) {
            listBook.cloneSheet(2);
        }
        int i = 0;
        for (ShipmentDetail shipmentDetail : setShipmentDetail) {
            SampleDetail sampledet = shipmentDetail.getStSampleDetail();
            HSSFRow actrowsheetList;
            //****************    sheetList  **************************
            //
            if (sheetList.getRow(i + inidatsheetList) == null)
                sheetList.createRow(i + inidatsheetList);
            actrowsheetList = sheetList.getRow(i + inidatsheetList);
            if (actrowsheetList.getCell(0) == null)
                actrowsheetList.createCell(0);
            if (sampledet.getSamplegid() != null)
                actrowsheetList.getCell(0).setCellValue(
                        sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded()
                                ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                                : String.valueOf(sampledet.getSamplegid())));
            if (actrowsheetList.getCell(1) == null)
                actrowsheetList.createCell(1);
            actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename());
            if (actrowsheetList.getCell(2) == null)
                actrowsheetList.createCell(2);
            wellkbio = sampledet.getPlateloc();
            if (wellkbio.length() == 2)
                wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
            actrowsheetList.getCell(2).setCellValue(wellkbio);
            if (actrowsheetList.getCell(3) == null)
                actrowsheetList.createCell(3);
            actrowsheetList.getCell(3).setCellValue("");
            //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
            if (sampledet.getControltype() != null)
                if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B"))
                    actrowsheetList.getCell(3).setCellValue("C");

            if (sampledet.getSamplegid() == null)
                actrowsheetList.getCell(3).setCellValue("C");

            if (actrowsheetList.getCell(4) == null)
                actrowsheetList.createCell(4);
            actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize());

            // ********************   sheetUsefull  ***********************

            if (!platenameact.equals(sampledet.getPlatename())) {
                platenameact = sampledet.getPlatename();
                platevoy = platevoy + 1;
                actsheetUsefull = listBook.getSheetAt(platevoy + 1);
                rowusefull = inidatsheetUsefull;
            }
            HSSFRow actrowsheetUsefull;
            if (actsheetUsefull.getRow(rowusefull) == null)
                actsheetUsefull.createRow(rowusefull);
            actrowsheetUsefull = actsheetUsefull.getRow(rowusefull);

            if (actrowsheetUsefull.getCell(1) == null)
                actrowsheetUsefull.createCell(1);
            actrowsheetUsefull.getCell(1).setCellValue(wellkbio);

            if (actrowsheetUsefull.getCell(2) == null)
                actrowsheetUsefull.createCell(2);
            actrowsheetUsefull.getCell(2).setCellValue(i + 1);
            rowusefull = rowusefull + 1;
            i = i + 1;
        }
    } catch (IOException e) {
        logger.error(e.getMessage());
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListGrid() {

    String rutaResumen;//from   w  ww. j a  va2s.  c om
    int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
    int plates = setShipmentDetail.size() / size;

    if (size == SIZE_PLATE_96) {

        //PlateContentList.letters=PlateContentList.letters96;
        rutaResumen = property.getKey(REPORT_PATH_KBIO_G96, Bundle.conf);
        rowsplate = 8;
        colsplate = 12;
    } else {

        rowsplate = 16;
        colsplate = 24;
        rutaResumen = property.getKey(REPORT_PATH_KBIO_G384, Bundle.conf);
        //PlateContentList.letters=PlateContentList.letters384;
    }

    InputStream inputStream = null;

    String platenameac = "";
    int rowplatename;

    Integer rowItemNo;
    Integer rowinidatsamples = 4;
    Integer actualrow = 0;
    Integer actualcol = 1;
    Integer cuanhojasvoy = 1;

    try {

        inputStream = getClass().getResourceAsStream(rutaResumen);
        listBook = new HSSFWorkbook(inputStream);
        HSSFSheet sheetDetails = listBook.getSheetAt(0);
        loadDetailSheet(sheetDetails);

        //Crear las hojas que voy a necesitar dependiendo el numero de platos
        Integer cuanhojasnecesito = 1;
        if (size == SIZE_PLATE_96) {
            cuanhojasnecesito = plates / 45;
            if (plates % 45 != 0)
                cuanhojasnecesito = cuanhojasnecesito + 1;
        }

        if (size == SIZE_PLATE_384) {
            cuanhojasnecesito = plates / 26;
            if (plates % 26 != 0)
                cuanhojasnecesito = cuanhojasnecesito + 1;
        }

        for (int i = 1; i < cuanhojasnecesito; i++) {
            listBook.cloneSheet(1);
        }

        HSSFSheet sheetGridActual;

        sheetGridActual = listBook.getSheetAt(cuanhojasvoy);

        int cuanplatosvoy = 0;
        int i = 0;

        for (ShipmentDetail shipmentDetail : setShipmentDetail) {
            SampleDetail sampledetail = shipmentDetail.getStSampleDetail();
            HSSFRow actrowsheetGrid;

            if (sheetGridActual.getRow(i) == null)
                sheetGridActual.createRow(i);

            //poner nombre del plato
            if (!platenameac.equals(sampledetail.getPlatename())) {
                platenameac = sampledetail.getPlatename();

                cuanplatosvoy = cuanplatosvoy + 1;

                //si hay mas de 45 platos entonces hacer una nueva hoja 
                if (cuanplatosvoy % 46 == 0 && size == SIZE_PLATE_96) {
                    cuanhojasvoy = cuanhojasvoy + 1;
                    actualrow = 0;
                    actualcol = 1;
                    cuanplatosvoy = 1;
                    sheetGridActual = listBook.getSheetAt(cuanhojasvoy);
                }

                //si hay mas de 26 platos entonces hacer una nueva hoja 
                if (cuanplatosvoy % 26 == 0 && size == SIZE_PLATE_384) {
                    cuanhojasvoy = cuanhojasvoy + 1;
                    actualrow = 0;
                    actualcol = 1;
                    cuanplatosvoy = 1;
                    sheetGridActual = listBook.getSheetAt(cuanhojasvoy);
                }

                rowplatename = (cuanplatosvoy - 1) * (rowsplate + 2) + cuanplatosvoy;

                if (cuanplatosvoy > 8) {
                    rowplatename = rowplatename + 1;
                }

                //Indica que area se va a combinar (roiwini, rowfin, colini, colfin)

                if (sheetGridActual.getRow(rowplatename) == null)
                    sheetGridActual.createRow(rowplatename);
                actrowsheetGrid = sheetGridActual.getRow(rowplatename);

                if (actrowsheetGrid.getCell(1) == null)
                    actrowsheetGrid.createCell(1);
                actrowsheetGrid.getCell(1).setCellValue(platenameac);

                if (cuanplatosvoy == 1)
                    rowinidatsamples = 2;
                else if (cuanplatosvoy == 9 && size == SIZE_PLATE_96)
                    rowinidatsamples = rowinidatsamples + rowsplate + 4;
                else
                    rowinidatsamples = rowinidatsamples + rowsplate + 3;
            }

            //sacar la siguiente posicion

            actualrow = actualrow + 1;
            if (actualrow > rowsplate) {
                actualcol = actualcol + 1;
                actualrow = 1;
            }

            if (actualcol > colsplate) {
                actualrow = 1;
                actualcol = 1;
            }

            //PONER EL SAMPLEID
            rowItemNo = actualrow + rowinidatsamples;

            if (sheetGridActual.getRow(rowItemNo) == null)
                sheetGridActual.createRow(rowItemNo);
            actrowsheetGrid = sheetGridActual.getRow(rowItemNo);

            if (actrowsheetGrid.getCell(actualcol) == null)
                actrowsheetGrid.createCell(actualcol);

            //SI NO TIENE SAMPLEID COLOCAR "BLANK"
            if (sampledetail.getSamplegid() == null
                    || (sampledetail.getControltype() != null && sampledetail.getControltype().equals("B")))
                actrowsheetGrid.getCell(actualcol).setCellValue("BLANK");
            else
                actrowsheetGrid.getCell(actualcol).setCellValue(
                        sampledetail.getLabstudyid().getPrefix() + (sampledetail.getLabstudyid().isUsePadded()
                                ? StrUtils.getPaddingCeros(sampledetail.getSamplegid())
                                : String.valueOf(sampledetail.getSamplegid())));
            i = i + 1;
        }

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadPlateID(HSSFSheet sheetList) {
    if (mapPlate != null && !mapPlate.isEmpty()) {
        Iterator<Map.Entry<Integer, String>> it = mapPlate.entrySet().iterator();
        int inidatsheetList = 12;
        int indexRow = 0;
        while (it.hasNext()) {
            HSSFRow actrowsheetList;
            if (sheetList.getRow(indexRow + inidatsheetList) == null)
                sheetList.createRow(indexRow + inidatsheetList);
            actrowsheetList = sheetList.getRow(indexRow + inidatsheetList);
            if (actrowsheetList.getCell(1) == null)
                actrowsheetList.createCell(1);
            Map.Entry<Integer, String> pair = it.next();
            actrowsheetList.getCell(1).setCellValue(pair.getValue());
            indexRow++;//from   w w  w  .  ja  v a 2s .  co  m
        }
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListSampleIDGenotypingService(HSSFSheet sheetList) {
    int inidatsheetList = 15;
    String wellkbio;//from   w  w  w. ja v  a2s.  c o m
    int i = 0;
    int indexBlank = 1;
    for (ShipmentDetail shipmentDetail : setShipmentDetail) {
        SampleDetail sampledet = shipmentDetail.getStSampleDetail();
        HSSFRow actrowsheetList;
        //****************    sheetList  **************************
        //
        if (sheetList.getRow(i + inidatsheetList) == null)
            sheetList.createRow(i + inidatsheetList);
        actrowsheetList = sheetList.getRow(i + inidatsheetList);
        if (actrowsheetList.getCell(1) == null)
            actrowsheetList.createCell(1);
        if (sampledet.getSamplegid() != null)
            actrowsheetList.getCell(1)
                    .setCellValue(sampledet.getLabstudyid().getPrefix()
                            + (sampledet.getLabstudyid().isUsePadded()
                                    ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                                    : String.valueOf(sampledet.getSamplegid()))
                            + "-" + sampledet.getStudysampleid());
        if (actrowsheetList.getCell(2) == null)
            actrowsheetList.createCell(2);
        actrowsheetList.getCell(2)
                .setCellValue(getPlate(sampledet.getPlatename(), sampledet.getLabstudyid().getPrefix()));
        if (actrowsheetList.getCell(3) == null)
            actrowsheetList.createCell(3);
        wellkbio = sampledet.getPlateloc();
        if (wellkbio.length() == 2)
            wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
        actrowsheetList.getCell(3).setCellValue(wellkbio);

        if (sampledet.getPlateloc().equals("H11") || sampledet.getPlateloc().equals("H12")) {
            actrowsheetList.getCell(1).setCellValue("");
        } else {
            //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
            if (sampledet.getControltype() != null) {
                if (!sampledet.getControltype().equals("")) {
                    actrowsheetList.getCell(1)
                            .setCellValue(getPathEmpty(indexBlank, sampledet.getStudysampleid()));
                    indexBlank++;
                }
            } else if (sampledet.getSamplegid() == null) {
                actrowsheetList.getCell(1).setCellValue(getPathEmpty(indexBlank, sampledet.getStudysampleid()));
                indexBlank++;
            }

        }
        if (actrowsheetList.getCell(7) == null)
            actrowsheetList.createCell(7);
        actrowsheetList.getCell(7).setCellValue(
                sampledet.getBreedergid() != null ? String.valueOf(sampledet.getBreedergid()) : "");
        if (actrowsheetList.getCell(8) == null)
            actrowsheetList.createCell(8);
        actrowsheetList.getCell(8)
                .setCellValue(sampledet.getNplanta() != null ? String.valueOf(sampledet.getNplanta()) : "");
        i = i + 1;
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListSampleID(HSSFSheet sheetList) {

    int inidatsheetList = 2;
    String wellkbio;/*from   w  ww. j  a  v  a 2 s. c  o m*/
    int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
    int plates = setShipmentDetail.size() / mod;

    int i = 0;
    for (ShipmentDetail shipmentDetail : setShipmentDetail) {
        SampleDetail sampledet = shipmentDetail.getStSampleDetail();
        HSSFRow actrowsheetList;
        //****************    sheetList  **************************
        //
        if (sheetList.getRow(i + inidatsheetList) == null)
            sheetList.createRow(i + inidatsheetList);
        actrowsheetList = sheetList.getRow(i + inidatsheetList);
        if (actrowsheetList.getCell(0) == null)
            actrowsheetList.createCell(0);
        if (sampledet.getSamplegid() != null)
            actrowsheetList.getCell(0).setCellValue(
                    sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded()
                            ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                            : String.valueOf(sampledet.getSamplegid())));
        if (actrowsheetList.getCell(1) == null)
            actrowsheetList.createCell(1);
        actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename());
        if (actrowsheetList.getCell(2) == null)
            actrowsheetList.createCell(2);
        wellkbio = sampledet.getPlateloc();
        if (wellkbio.length() == 2)
            wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
        actrowsheetList.getCell(2).setCellValue(wellkbio);
        if (actrowsheetList.getCell(3) == null)
            actrowsheetList.createCell(3);
        actrowsheetList.getCell(3).setCellValue("");
        //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
        if (sampledet.getControltype() != null)
            if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B"))
                actrowsheetList.getCell(3).setCellValue("C");

        if (sampledet.getSamplegid() == null)
            actrowsheetList.getCell(3).setCellValue("C");

        if (actrowsheetList.getCell(4) == null)
            actrowsheetList.createCell(4);
        actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize());

        i = i + 1;
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java

License:Apache License

private HSSFWorkbook createBookPlateExcel(StudyLabReportBean beanReport) {
    HSSFWorkbook book = new HSSFWorkbook();
    // Create a new Sheet in book
    HSSFSheet sheet = book.createSheet();
    // Creates a new row for headers
    HSSFRow row = sheet.createRow(0);
    // Creates a new cell for title
    HSSFCell cell = row.createCell(0);
    // Create cell contents.
    HSSFRichTextString text = new HSSFRichTextString("");
    cell.setCellValue(text);// ww w.java2s .c om
    // Creates a new
    HSSFRow rowHeaders = sheet.createRow(2);
    styleCellNormallyHeader = styleCellNormally(book, true);
    styleCellNormally = styleCellNormally(book, false);
    styleCellControl = getStyleCeldSolidForeground(book, cellControl);
    styleCellControlDART = getStyleCeldSolidForeground(book, cellControlDART);
    styleCellControlRandom = getStyleCeldSolidForeground(book, cellControlRandom);
    styleCellControlKBIo = getStyleCeldSolidForeground(book, cellKBiocontrolRandom);
    styleCellBlank = getStyleCeldSolidForeground(book, cellBlankForegroundColor);
    if (beanReport.getMapPlateSamples().size() > 0) {
        Iterator iteratorMapFirst = beanReport.getMapPlateSamples().entrySet().iterator();
        int rowCounter = 1;
        while (iteratorMapFirst.hasNext()) {
            Map.Entry entry = (Map.Entry) iteratorMapFirst.next();
            Map<String, SampleDetail> mapInner = (Map<String, SampleDetail>) entry.getValue();
            Integer key = (Integer) entry.getKey();
            sheet.createRow(rowCounter);
            rowCounter++;
            rowCounter = createHeaderPlate(sheet, rowCounter, beanReport.getNumberColumn(),
                    styleCellNormallyHeader, beanReport.getPatternPlate() + key.toString());
            int rowCounterLabel = 0;

            for (int sizeRow = 0; sizeRow < beanReport.getNameRow().length; sizeRow++) {
                HSSFRow rowData = sheet.createRow(rowCounter);

                for (int sizeColumn = 0; sizeColumn <= beanReport.getNumberColumn(); sizeColumn++) {
                    if (sizeColumn == 0) {
                        writeCell(rowData, sizeColumn, beanReport.getNameRow()[rowCounterLabel],
                                styleCellNormallyHeader);
                        rowCounterLabel++;
                    } else {
                        SampleDetail detail = mapInner.get(beanReport.getPatternPlate() + key.toString()
                                + beanReport.getNameRow()[sizeRow] + (sizeColumn));
                        if (detail != null) {
                            HSSFCellStyle style = null;
                            String sampleName = "";
                            if (detail.getControltype() != null && !detail.getControltype().equals(""))
                                style = validateStatusSample(detail.getControltype());
                            else
                                sampleName = getFieldsReport(beanReport, detail);
                            String strDetail = getTemplateFiled(detail);
                            if (strDetail != null && !strDetail.isEmpty()) {
                                if (!sampleName.isEmpty()) {
                                    sampleName = sampleName + "\n" + strDetail;
                                } else {
                                    sampleName = sampleName + strDetail;
                                }
                            }

                            writeCell(rowData, sizeColumn, sampleName, style);
                        }
                    }
                }
                rowCounter++;
            }
        }
        return book;
    }
    return null;
}