Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setAlignment

Introduction

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

Prototype

@Override
public void setAlignment(HorizontalAlignment align)

Source Link

Document

set the type of horizontal alignment for the cell

Usage

From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java

License:BSD License

public void generateExcel(String summary3ReportXml, String file) throws Exception {

    // creating the workbook and the spreadsheet
    try {/*from  www  .j ava2 s .  c o m*/

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        String fileName = "";

        fileName = file;

        InputStream inputStream = new FileInputStream((fileName + ".xml"));
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

        PrintSetup printSetup = spreadSheet.getPrintSetup();
        printSetup.setLandscape(true);
        spreadSheet.setFitToPage(true);
        spreadSheet.setHorizontallyCenter(true);

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel")));
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }
}

From source file:edu.ku.brc.dbsupport.TableModel2Excel.java

License:Open Source License

/**
 * Converts a tableModel to an Excel Spreadsheet.
 * @param toFile the file object to write it to.
 * @param title the title of the spreadsheet.
 * @param tableModel the table model/*  ww  w . ja v  a2 s. c o m*/
 * @return a file to a spreadsheet
 */
public static File convertToExcel(final File toFile, final String title, final TableModel tableModel) {
    if (toFile == null) {
        UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_EXISTS",
                toFile != null ? toFile.getAbsolutePath() : "");
        return null;
    }

    if (tableModel != null && tableModel.getRowCount() > 0) {
        try {
            // create a new file
            FileOutputStream out;
            try {
                out = new FileOutputStream(toFile);

            } catch (FileNotFoundException ex) {
                UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_WRITE",
                        toFile != null ? toFile.getAbsolutePath() : "");
                return null;
            }

            // create a new workbook
            HSSFWorkbook wb = new HSSFWorkbook();

            // create a new sheet
            HSSFSheet sheet = wb.createSheet();
            // declare a row object reference

            // Header Captions
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            // create a style for the header cell
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            headerStyle.setFont(headerFont);
            setBordersOnStyle(headerStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            short numColumns = (short) tableModel.getColumnCount();

            HSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < numColumns; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                headerCell.setCellStyle(headerStyle);

                //add the date to the header cell
                headerCell.setCellValue(tableModel.getColumnName(i));
                sheet.setColumnWidth((short) i, (short) (30 * 256));
            }

            //--------------------------
            // done header
            //--------------------------

            // create 3 cell styles
            HSSFCellStyle oddCellStyle = wb.createCellStyle();
            HSSFCellStyle evenCellStyle = wb.createCellStyle();

            setBordersOnStyle(oddCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);
            setBordersOnStyle(evenCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            // create 2 fonts objects
            HSSFFont cellFont = wb.createFont();
            //set font 1 to 12 point type
            cellFont.setFontHeightInPoints((short) 11);
            oddCellStyle.setFont(cellFont);
            evenCellStyle.setFont(cellFont);

            evenCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            oddCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            oddCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
            evenCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

            oddCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            evenCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            // set the sheet name to HSSF Test
            wb.setSheetName(0, title);

            for (short rownum = 0; rownum < (short) tableModel.getRowCount(); rownum++) {
                // create a row
                HSSFRow row = sheet.createRow(rownum + 1);

                for (short cellnum = (short) 0; cellnum < numColumns; cellnum++) {
                    // create a numeric cell
                    HSSFCell cell = row.createCell(cellnum);

                    Object dataVal = tableModel.getValueAt(rownum, cellnum);
                    cell.setCellValue(dataVal != null ? dataVal.toString() : "");

                    // on every other row
                    cell.setCellStyle((rownum % 2) == 0 ? evenCellStyle : oddCellStyle);
                }
            }

            // write the workbook to the output stream
            // close our file (don't blow out our file handles
            wb.write(out);
            out.close();

        } catch (Exception ex) {
            edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(TableModel2Excel.class, ex);
            log.error("convertToExcel", ex); //$NON-NLS-1$
        }
    }
    return toFile;
}

From source file:edu.swjtu.excel.ExportCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? /* w  ww. j  a  va2s .c o m*/
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    ArrayList<Car> list = null;
    list = new CarDaoImpl().getAllCar(con);

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    //?
    //      FileOutputStream fout = new FileOutputStream("E:/car_data.xls");  
    //      wb.write(fout);  
    //      fout.close();  

    String myexcel = "All_Car";
    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

    wb.write(out);
    out.close();

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:edu.swjtu.excel.ExportConCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? /*from  w w w  . j  a v a2  s  .  c  om*/
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    String condition = request.getParameter("condition");
    String sea_condition = request.getParameter("sea_condition");
    String search_cho = null;

    ArrayList<Car> list = null;
    CarDaoImpl carr = new CarDaoImpl();

    if (condition.equals("0")) {
        list = carr.getAllCar(con);
        search_cho = "All";
    } else if (condition.equals("1")) {
        list = carr.getCarByLicensePlate_V(sea_condition, con);
        search_cho = "LicensePlate";
    } else if (condition.equals("2")) {
        list = carr.getCarByBrand_V(sea_condition, con);
        search_cho = "Brand";
    } else if (condition.equals("3")) {
        list = carr.getCarByDriver_V(sea_condition, con);
        search_cho = "Driver";
    } else if (condition.equals("4")) {
        list = carr.getCarByArrangeId_V(sea_condition, con);
        search_cho = "ArrangeId";
    } else if (condition.equals("5")) {
        list = carr.getCarByNumber_V(sea_condition, con);
        search_cho = "Number";
    } else if (condition.equals("6")) {
        list = carr.getCarByDrivingLicense_V(sea_condition, con);
        search_cho = "DrivingLicenseber";
    } else if (condition.equals("7")) {
        list = carr.getCarByLicense_V(sea_condition, con);
        search_cho = "License";
    } else {
    }

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    String myexcel = search_cho + "_Car";

    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

    wb.write(out);
    out.close();

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /*from   ww w . j av  a  2s.  com*/
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        log.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        HSSFWorkbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
        log.debug("testModifyCellAttribute after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("cell test sheet2");
        //           sheet.setColumnWidth((short) 3, (short) 200);   // column Width

        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeight((short) 16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            HSSFRow row = sheet.createRow(i);
            //              row.setHeight((short)300); // row? height 

            for (int j = 0; j < 5; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString());
        HSSFSheet sheetT = wbT.getSheet("cell test sheet2");
        log.debug("getNumCellStyles : " + wbT.getNumCellStyles());

        HSSFCellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        HSSFFont fontT = cs1.getFont(wbT);
        log.debug("font getFontHeight : " + fontT.getFontHeight());
        log.debug("font getBoldweight : " + fontT.getBoldweight());
        log.debug("font getFontName : " + fontT.getFontName());
        log.debug("getAlignment : " + cs1.getAlignment());
        log.debug("getWrapText : " + cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            HSSFRow row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                HSSFCell cell1 = row1.getCell(j);
                log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue());
                assertEquals(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testModifyCellAttribute end....");
    }
}

From source file:eionet.gdem.conversion.excel.writer.ExcelConversionHandler.java

License:Mozilla Public License

/**
 * Adds style to workbook.//from  w w  w.  java2s .  com
 * @param style Style
 */
private void addStyleToWorkbook(ExcelStyleIF style) {
    HSSFFont font = wb.createFont();
    // Font Size eg.12
    short height = style.getFontSize();
    if (height > 0) {
        font.setFontHeightInPoints(height);
    }
    // Font Name eg.Arial
    String font_name = style.getFontName();
    if (font_name != null) {
        font.setFontName(font_name);
    }
    // Italic
    font.setItalic(style.getItalic());
    // Font Weight eg.bold
    short weight = style.getFontWeight();
    font.setBoldweight(weight);

    // Fonts are set into a style so create a new one to use.
    HSSFCellStyle HSSFStyle = wb.createCellStyle();
    HSSFStyle.setFont(font);
    // Text alignment eg.center
    short align = style.getTextAlign();
    HSSFStyle.setAlignment(align);

    style.setWorkbookIndex(HSSFStyle.getIndex());

}

From source file:eu.scape_project.pc.tika.cli.MergeReports.java

License:Apache License

private static boolean writeXLS(String outputFilePath) throws Exception {

    int rowCounter = 1; //Start in row 1 (which is the 2nd row). Row 0 holds the description.
    FileOutputStream fileOut = new FileOutputStream(outputFilePath);
    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet worksheet = workbook.createSheet("Type Report");

    HSSFCellStyle myCellStyle = workbook.createCellStyle();
    myCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFCellStyle myCellStylePercent = workbook.createCellStyle();
    HSSFDataFormat df = workbook.createDataFormat();
    myCellStylePercent.setDataFormat(df.getFormat("0.00%"));
    myCellStylePercent.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFRow myHeaderRow = worksheet.createRow(0);
    HSSFCell myHeaderCell0 = myHeaderRow.createCell(0);
    HSSFCell myHeaderCell1 = myHeaderRow.createCell(1);
    HSSFCell myHeaderCell2 = myHeaderRow.createCell(2);

    myHeaderCell0.setCellValue("TYPE");
    myHeaderCell1.setCellValue("COUNT");
    myHeaderCell2.setCellValue("PERCENTAGE");
    myHeaderCell0.setCellStyle(myCellStyle);
    myHeaderCell1.setCellStyle(myCellStyle);
    myHeaderCell2.setCellStyle(myCellStyle);

    Iterator it = myCollection.keySet().iterator();
    while (it.hasNext()) {
        String typeKey = it.next().toString();
        float typeValue = myCollection.get(typeKey);
        float myPerc = typeValue / countAllGoodItems;
        //System.out.println("    ***: " + typeKey + "#" + (int) typeValue + "#" + myPerc);

        HSSFRow myRow = worksheet.createRow(rowCounter);
        HSSFCell myCell0 = myRow.createCell(0);
        HSSFCell myCell1 = myRow.createCell(1);
        HSSFCell myCell2 = myRow.createCell(2);

        myCell0.setCellValue(typeKey);//w w w .j a v  a  2  s .  c om
        myCell1.setCellValue(typeValue);
        myCell2.setCellValue(myPerc);

        myCell1.setCellStyle(myCellStyle);
        myCell2.setCellStyle(myCellStyle);

        myCell2.setCellStyle(myCellStylePercent);

        rowCounter++;

    }

    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();

    return true;
}

From source file:excel.FileExcel.java

public File excel_create_katalog_update(ArrayList<UpdKatalog> newKatalogUpdate) {
    if (newKatalogUpdate.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "KatalogDiff_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + "_"
                + time.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
        File ExcelKatalogDiff = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(true);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);// w ww .  j av  a  2 s  .c om

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL PERUBAHAN HARGA");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Update : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(newKatalogUpdate.get(0).update_date_new));

        // create the header
        datastyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG Lama");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Lama");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG Baru");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Baru");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < newKatalogUpdate.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_old);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_old));
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_new);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_new));
        }
        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelKatalogDiff);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        }
        return ExcelKatalogDiff;
    } else
        return null;
}

From source file:excel.FileExcel.java

public File excel_create_diff_upd_stock(ArrayList<DiffUpdStock> diffinput, Date currtime) {
    if (diffinput.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "DailyDiff_" + fmt.format(currtime) + "_" + time.format(currtime) + ".xls";
        File ExcelDailyDiff = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);/*from www  . j  ava2s .co m*/

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL UPDATE DAILY STOK");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Stock : ");
        cell = header.createCell(3);
        cell.setCellValue(currtime);

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Unedited Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Unedited Jumlah Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Kode Barang");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Jumlah Barang");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < diffinput.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriKodeBarang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).edtKodeBarang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);

        }
        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelDailyDiff);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelDailyDiff = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelDailyDiff = null;
        }
        return ExcelDailyDiff;
    }
    return null;
}

From source file:excel.FileExcel.java

public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder,
        Date comingorderdate) {/*from w w  w  .j  a  v a  2 s .  c  o  m*/
    if (comingorder.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "ComingOrder_" + fmt.format(comingorderdate) + "_" + time.format(comingorderdate)
                + ".xls";
        File ExcelComingOrder = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL COMING ORDER");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Stock : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(comingorderdate));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Konter");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        // Sorting
        Collections.sort(comingorder, new Comparator<ComingOrderReportData>() {
            @Override
            public int compare(ComingOrderReportData data2, ComingOrderReportData data1) {
                return data1.nama_barang.compareTo(data2.nama_barang);
            }
        });

        for (int i = 0; i < comingorder.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_konter);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).jumlah_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).nama_barang);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kategori);

        }

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelComingOrder);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        }
        return ExcelComingOrder;
    } else {
        return null;
    }
}