Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createDataFormat

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createDataFormat.

Prototype

@Override
public XSSFDataFormat createDataFormat()

Source Link

Document

Returns the workbook's data format table (a factory for creating data format strings).

Usage

From source file:br.com.sose.utils.BigGridDemo_temp.java

License:Apache License

/**
 * Create a library of cell styles./*w w w  .  j a  va  2 s  . co  m*/
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, List<TesteCaseTSBean> listTestCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*  w ww. j av  a  2  s .  co  m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    if (listTestCase.size() > 0) {
        descriptionPlan.setCellValue(listTestCase.get(0).getTestPlan());
        prj.setCellValue(listTestCase.get(0).getSTIPRJ());
        fase.setCellValue(listTestCase.get(0).getFASE());
        testPhase.setCellValue(listTestCase.get(0).getTestPhase());
    }

    for (int i = 0; i < listTestCase.size(); i++) {

        //            estilo.setDataFormat(format.getFormat(formatData));
        //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        testScriptName.setCellValue(listTestCase.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTestCase.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTestCase.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTestCase.get(i).getStepDescription());
        expectedResults.setCellValue(listTestCase.get(i).getExpectedResults());
        product.setCellValue(listTestCase.get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(listTestCase.get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from  www .  j  av a2 s . co  m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet + ".xlsx");
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(FunctiosDates.getDateActual());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();// w  ww  . jav a  2 s  . com
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);

    descriptionPlan.setCellValue(testPlan.getName());
    prj.setCellValue(testPlan.getSti());
    fase.setCellValue(testPlan.getCrFase());
    testPhase.setCellValue(testPlan.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);
    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        stepNo.setCellValue(testPlan.getTestCase().get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(testPlan.getTestCase().get(i).getStepDescription());
        expectedResults.setCellValue(testPlan.getTestCase().get(i).getExpectedResults());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        linha = linha + 2;

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean updateTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from  ww  w  .  j ava 2 s  .  c o m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);

    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(testCase.getDataPlanejada());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.bc.util.XlsxExporter.java

public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) {
    try {//from  w ww  . j av  a2s .c  o  m
        if (output.exists()) {
            log.info(output.getName() + " exists. Deleting");
            output.delete();
            log.info("Deleted " + output.getName());
        }

        log.info("Creating xlsx file...");

        FileOutputStream fos = new FileOutputStream(output);
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("Order");
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.ALIGN_FILL);
        Font font = workBook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date",
                "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped",
                "Discount", "Extended Price" };

        log.info("Creating header row & columns");

        Row row = sheet.createRow(0);

        for (int i = 0; i < columnHeaders.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnHeaders[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 4500);
        }

        sheet.setColumnWidth(9, 13500);

        log.info("Writing " + items.size() + " records");

        XSSFDataFormat decimalFormat = workBook.createDataFormat();
        CellStyle dstyle = workBook.createCellStyle();
        dstyle.setDataFormat(decimalFormat.getFormat("0.00"));

        int i = 1;
        for (CustomerOrderItem orderItem : items) {
            Row drow = sheet.createRow(i++);
            Hibernate.initialize(order.getCustomerOrderItems());

            String strValue;
            Float floatValue;
            Integer intVal;

            Cell cInvoice = drow.createCell(0);
            strValue = order.getInvoiceNumber();
            if (strValue == null)
                strValue = "";
            cInvoice.setCellValue(order.getInvoiceNumber());

            Cell cSalesman = drow.createCell(1);
            strValue = order.getSalesman();
            if (strValue == null)
                strValue = "";
            cSalesman.setCellValue(strValue);

            Cell cCustomerName = drow.createCell(2);
            strValue = order.getCustomer().getCompanyName();
            if (strValue == null)
                strValue = "";
            cCustomerName.setCellValue(strValue);

            Cell cCustomerCode = drow.createCell(3);
            strValue = order.getCustomerCode();
            if (strValue == null)
                strValue = "";
            cCustomerCode.setCellValue(strValue);

            Cell cPo = drow.createCell(4);
            strValue = order.getPoNumber();
            if (strValue == null)
                strValue = "";
            cPo.setCellValue(strValue);

            Cell cShipDate = drow.createCell(5);
            Date d = order.getShipDate();
            if (d == null)
                cShipDate.setCellValue("");
            else
                cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));

            Cell cPostDate = drow.createCell(6);
            d = order.getPostDate();
            if (d == null)
                cPostDate.setCellValue("");
            else
                cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));
            Hibernate.initialize(orderItem.getInventoryItem());
            InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem();
            if (item != null) {
                Cell cIsbn = drow.createCell(7);
                strValue = item.getIsbn();
                if (strValue == null)
                    strValue = "";
                cIsbn.setCellValue(strValue);

                Cell cIsbn13 = drow.createCell(8);
                strValue = item.getIsbn13();
                if (strValue == null)
                    strValue = "";
                cIsbn13.setCellValue(strValue);

                Cell cTitle = drow.createCell(9);
                strValue = item.getTitle();
                if (strValue == null)
                    strValue = "";
                cTitle.setCellValue(strValue);

                Cell cListPrice = drow.createCell(10);
                floatValue = item.getListPrice();
                cListPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cListPrice.setCellValue(floatValue);

                Cell cPrice = drow.createCell(11);
                floatValue = item.getSellingPrice();
                cPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cPrice.setCellValue(floatValue);
            }
            Cell cQuantity = drow.createCell(12);
            intVal = orderItem.getQuantity();
            log.info("Quantity : " + intVal);
            if (intVal == null)
                intVal = 0;
            cQuantity.setCellValue(intVal);

            Cell cShipped = drow.createCell(13);
            intVal = orderItem.getFilled();
            log.info("Shipped QTY : " + intVal);
            if (intVal == null)
                intVal = 0;
            cShipped.setCellValue(intVal);

            Cell cDiscount = drow.createCell(14);
            cDiscount.setCellStyle(dstyle);
            floatValue = orderItem.getDiscount();
            if (floatValue == null)
                floatValue = 0.0f;
            cDiscount.setCellValue(floatValue);

            Cell cExtendedPrice = drow.createCell(15);
            cExtendedPrice.setCellStyle(dstyle);
            BigDecimal dValue = orderItem.getTotalPrice();
            if (dValue == null)
                dValue = BigDecimal.ZERO;
            cExtendedPrice.setCellValue(dValue.doubleValue());

        }

        workBook.write(fos);
        log.info("Finished writing data, closing...");

        fos.close();
        log.info("Completed exporting data to " + output.getAbsolutePath());
    } catch (Exception ex) {
        Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java

License:Apache License

/**
 * Create a library of cell styles./*from ww w.j  a  v  a 2  s  .  c  o  m*/
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(HorizontalAlignment.RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(HorizontalAlignment.RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoHojalateriaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();//from  w w  w  .ja v a 2 s.co m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Hojalteria y pintura");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroHojalateriaPintura()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalHojalateria", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.CostoMecanicaReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();/*from   w  w  w .  ja v  a 2s  .  co  m*/
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Cell cell;
    Row row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn);
    cell.setCellValue("Mecanica");
    XSSFCellStyle cellStyle = wb.createCellStyle();
    addHeaderStyle(cellStyle, wb);
    addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
    cell.setCellStyle(cellStyle);
    for (int i = 1; i < 3; i++) {
        cell = row.createCell(initialColumn + i);
        cellStyle = wb.createCellStyle();
        addBorders(wb, cellStyle, CellStyle.BORDER_MEDIUM);
        cell.setCellStyle(cellStyle);
    }
    //merge de celdas
    sheet.addMergedRegion(new CellRangeAddress(initialRow, //first row (0-based)
            initialRow, //last row  (0-based)
            initialColumn, //first column (0-based)
            initialColumn + 2 //last column  (0-based)
    ));

    //detalle
    cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    Cell inicio = null;
    Cell fin = null;
    for (RegistroCostoRC x : datos.getRegistroMecanica()) {
        initialRow = initialRow + 1;
        row = getRow(sheet, initialRow);
        //tipo
        cell = row.createCell(initialColumn);
        cell.setCellValue(x.getTipo());
        //descripcion
        cell = row.createCell(initialColumn + 1);
        cell.setCellValue(x.getDescripcion());
        //costo
        cell = row.createCell(initialColumn + 2);
        cell.setCellValue(x.getCosto());
        cell.setCellStyle(cellStyle);
        if (inicio == null) {
            inicio = cell;
        }
    }
    if (inicio != null) {
        fin = cell;
    }
    initialRow = initialRow + 1;
    row = getRow(sheet, initialRow);
    cell = row.createCell(initialColumn + 1);
    cell.setCellValue("Total");
    cellStyle = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setFont(font);
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell = row.createCell(initialColumn + 2);
    cell.setCellStyle(cellStyle);
    if (inicio != null) {
        String formula = "SUM(" + getSimpleReference(inicio) + ":" + getSimpleReference(fin) + ")";
        cell.setCellFormula(formula);
        contexto.put("totalMecanica", cell);
    } else {
        cell.setCellValue(0.0);
    }
    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 2);
    BordeSeccion sinTotal = new BordeSeccion(r);
    sinTotal.setLowerRow(r.getLowerRow() - 1);
    paintBorder(wb, sheet, CellStyle.BORDER_MEDIUM, sinTotal);
    return r;
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.TotalServicioReporteCliente.java

License:Apache License

@Override
public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) {
    Sheet sheet = contexto.getSheet();//from w w  w  . j a va2  s  .  c o m
    XSSFWorkbook wb = contexto.getWb();
    int initialRow = borde.getUpperRow();
    int initialColumn = borde.getLeftColumn();
    BordeSeccion r = new BordeSeccion();
    r.setLeftColumn(initialColumn);
    r.setUpperRow(initialRow);

    Row row = getRow(sheet, initialRow);
    row.createCell(initialColumn).setCellValue("Total del servicio:");
    //calculo del total
    Cell totalMecanica = (Cell) contexto.get("totalMecanica");
    Cell totalHojalateria = (Cell) contexto.get("totalHojalateria");
    Cell cell = row.createCell(initialColumn + 1);
    if (totalMecanica != null && totalHojalateria != null) {
        String formula = getSimpleReference(totalMecanica) + "+" + getSimpleReference(totalHojalateria);
        cell.setCellFormula(formula);
    } else {
        if (totalMecanica != null) {
            String formula = getSimpleReference(totalMecanica);
            cell.setCellFormula(formula);
        }
        if (totalHojalateria != null) {
            String formula = getSimpleReference(totalHojalateria);
            cell.setCellFormula(formula);
        }
    }
    if (totalMecanica == null && totalHojalateria == null) {
        cell.setCellValue(0d);
    }
    XSSFCellStyle cellStyle = wb.createCellStyle();
    XSSFDataFormat df = wb.createDataFormat();
    cellStyle.setDataFormat(df.getFormat("$#,##0.00"));
    cell.setCellStyle(cellStyle);

    r.setLowerRow(initialRow);
    r.setRightColumn(initialColumn + 1);
    return r;
}