Example usage for org.apache.poi.ss.usermodel CellStyle setFont

List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setFont

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel CellStyle setFont.

Prototype

void setFont(Font font);

Source Link

Document

set the font for this style

Usage

From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java

License:Apache License

static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;

    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 14);
    headerFont.setBold(true);/*from w  ww  .  j  ava2s. c o  m*/
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(headerFont);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles.put("header", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.RED.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("red-bold", style);

    String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" };
    for (String fmt : nfmt) {
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat(fmt));
        styles.put(fmt, style);
    }

    return styles;
}

From source file:com.aan.girsang.client.ui.master.barang.BarangPanel.java

private void exportExcel(List<Barang> dataList) throws IOException {
    if (dataList != null && !dataList.isEmpty()) {
        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet sheet = workBook.createSheet();
        HSSFSheet worksheet = workBook.createSheet("Sheet 0");
        // Nama Field
        Row judul = sheet.createRow((short) 0);
        Cell cell = judul.createCell((short) 0);
        cell.setCellValue("This is a test of merging");
        HSSFRow headingRow = sheet.createRow((short) 2);
        headingRow.createCell((short) 0).setCellValue("ID");
        headingRow.createCell((short) 1).setCellValue("BARCODE 1");
        headingRow.createCell((short) 2).setCellValue("BARCODE 2");
        headingRow.createCell((short) 3).setCellValue("NAMA BARANG");
        headingRow.createCell((short) 4).setCellValue("GOLONGAN");
        headingRow.createCell((short) 5).setCellValue("SAT. JUAL");
        headingRow.createCell((short) 6).setCellValue("ST. TOKO");
        headingRow.createCell((short) 7).setCellValue("ST. GUDANG");
        headingRow.createCell((short) 8).setCellValue("SAT. BELI");
        headingRow.createCell((short) 9).setCellValue("ISI PEM.");
        headingRow.createCell((short) 10).setCellValue("HRG PEM.");
        headingRow.createCell((short) 11).setCellValue("HRG NORMAL");
        headingRow.createCell((short) 12).setCellValue("HRG MEMBER");
        headingRow.createCell((short) 13).setCellValue("JUAL");
        int panjang = headingRow.getLastCellNum() - 1;
        short rowNo = 3;

        sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based)
                0, //last row  (0-based)
                0, //first column (0-based)
                panjang //last column  (0-based)
        ));//from   w  ww . j a  v  a2  s  .  c  o m
        CellStyle styleData = workBook.createCellStyle();
        styleData.setBorderBottom(CellStyle.BORDER_THIN);
        styleData.setBorderRight(CellStyle.BORDER_THIN);
        styleData.setBorderLeft(CellStyle.BORDER_THIN);
        for (Barang b : dataList) {
            HSSFRow row = sheet.createRow(rowNo);
            String jual;
            if (b.getJual() == true) {
                jual = "Jual";
            } else {
                jual = "Tidak";
            }
            row.createCell((short) 0).setCellValue(b.getPlu());
            row.createCell((short) 1).setCellValue(b.getBarcode1());
            row.createCell((short) 2).setCellValue(b.getBarcode2());
            row.createCell((short) 3).setCellValue(b.getNamaBarang());
            row.createCell((short) 4).setCellValue(b.getGolonganBarang().getGolonganBarang());
            row.createCell((short) 5).setCellValue(b.getSatuan());
            row.createCell((short) 6).setCellValue(b.getStokToko());
            row.createCell((short) 7).setCellValue(b.getStokGudang());
            row.createCell((short) 8).setCellValue(b.getSatuanPembelian());
            row.createCell((short) 9).setCellValue(b.getIsiPembelian());
            row.createCell((short) 10).setCellValue(TextComponentUtils.formatNumber(b.getHargaBeli()));
            row.createCell((short) 11).setCellValue(TextComponentUtils.formatNumber(b.getHargaNormal()));
            row.createCell((short) 12).setCellValue(TextComponentUtils.formatNumber(b.getHargaMember()));
            row.createCell((short) 13).setCellValue(jual);
            for (int i = 0; i <= 13; i++) {
                row.getCell((short) i).setCellStyle(styleData);
            }
            rowNo++;
        }
        for (int i = 0; i <= 13; i++) {
            sheet.autoSizeColumn(i);
        }
        Font font = workBook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //style judul
        CellStyle styleTitle = workBook.createCellStyle();
        styleTitle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleTitle.setFont(font);
        judul.getCell(0).setCellStyle(styleTitle);

        //judul field
        CellStyle styleHeading = workBook.createCellStyle();
        styleHeading.setFont(font);
        styleHeading.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        styleHeading.setBorderBottom(CellStyle.BORDER_THIN);
        styleHeading.setBorderTop(CellStyle.BORDER_THIN);
        styleHeading.setBorderRight(CellStyle.BORDER_THIN);
        styleHeading.setBorderLeft(CellStyle.BORDER_THIN);
        for (int i = 0; i < headingRow.getLastCellNum(); i++) {//For each cell in the row 
            headingRow.getCell(i).setCellStyle(styleHeading);//Set the style
        }
        String file = "D:/Student_detais.xls";
        try {
            try (FileOutputStream fos = new FileOutputStream(file)) {
                workBook.write(fos);
            }
            JOptionPane.showMessageDialog(null, "Sukses");
        } catch (FileNotFoundException e) {
            System.out.println("Invalid directory or file not found");
        } catch (IOException e) {
            System.out.println("Error occurred while writting excel file to directory");
        }
    }
}

From source file:com.abacus.reports.ExcelBuilder.java

@Override
protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    // get data model which is passed by the Spring container
    List headerlist = (List) map.get("header");
    List<Object[]> data = (List) map.get("data");
    String reportname = String.valueOf(map.get("report_name"));

    // create a new Excel sheet
    HSSFSheet sheet = workbook.createSheet(reportname);
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls");
    OutputStream outStream = response.getOutputStream();
    sheet.setDefaultColumnWidth(30);//w  w w. j ava2 s.  c  om

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    HSSFPalette palette = workbook.getCustomPalette();
    HSSFColor color = palette.findSimilarColor(152, 35, 17);
    short paindex = color.getIndex();
    font.setFontName("Trebuchet MS");
    style.setFillForegroundColor(paindex);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // create header row
    HSSFRow header = sheet.createRow(0);
    int row = 0;
    for (Object headerlist1 : headerlist) {

        header.createCell(row).setCellValue(String.valueOf(headerlist1));
        header.getCell(row).setCellStyle(style);
        row++;
    }

    CellStyle style2 = workbook.createCellStyle();
    HSSFFont font2 = workbook.createFont();
    font2.setFontName("Trebuchet MS");
    style2.setFont(font2);

    System.out.println("data.size(): " + data.size());
    int rownum = 1;
    // create data rows         
    for (int rowCount = 0; rowCount < data.size(); rowCount++) {
        HSSFRow aRow = sheet.createRow(rownum);
        Object[] value = data.get(rowCount);
        int col = 0;
        for (Object value1 : value) {
            HSSFCell cell = aRow.createCell(col++);
            cell.setCellStyle(style2);
            if (value1 instanceof java.lang.String)
                cell.setCellValue(String.valueOf(value1));
            if (value1 instanceof java.lang.Integer)
                cell.setCellValue(Integer.parseInt(String.valueOf(value1)));
            if (value1 instanceof java.lang.Boolean)
                cell.setCellValue(Integer.parseInt(String.valueOf(value1)));
            if (value1 instanceof java.lang.Double)
                cell.setCellValue(Double.parseDouble(String.valueOf(value1)));
            if (value1 instanceof java.lang.Float)
                cell.setCellValue(Float.parseFloat(String.valueOf(value1)));
        }
        rownum++;
    }
    workbook.write(outStream);
    outStream.close();
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

private void initWorkbook() {

    wb = new XSSFWorkbook();
    styles.clear();//from   w  ww.  ja  va2  s . c o  m
    styleWithBordersAbove.clear();
    styleWithBordersUnder.clear();

    CellStyle style;
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 14);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TITLE14, style);

    font = wb.createFont();
    font.setColor(IndexedColors.INDIGO.getIndex());
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 14);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TITLE14BLUE, style);

    font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 12);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TITLE12, style);

    font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_NONE);
    style.setBorderLeft(CellStyle.BORDER_NONE);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_PLAIN, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TH_CENTER, style);

    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TH_LEFT, style);

    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TH_RIGHT, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_LEFT, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_RIGHT, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 8);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_SMALL, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE0, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.INDIGO.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_BLUE, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.0"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE1, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.INDIGO.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.0"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE1_BLUE, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.INDIGO.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.00"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE2_BLUE, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.00"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE2, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.000"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE3, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.MAROON.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0%"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE100_RED, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.MAROON.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.000"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE3_RED, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.INDIGO.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setDataFormat(df.getFormat("0.000"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DOUBLE3_BLUE, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.MAROON.getIndex());
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_RED, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_CENTER, style);

    font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_BOLD_LEFT, style);

    font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_BOLD_CENTER, style);

    font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_BOLD_RIGHT, style);

    font = wb.createFont();
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setWrapText(false);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    styles.put(Style.S_TD_DATE, style);
}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
    Class[] types = getTypes(table);
    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();/*from www  . j  a va  2s.  c om*/
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 15);
    style = wb.createCellStyle();
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 10);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("th", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-border", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-double", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-right", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-bold", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-date", style);

    Sheet sheet = wb.createSheet();
    sheet.setFitToPage(true);

    Cell cell;

    int maxRows = 0;
    for (int r = 0; r < table.length; r++) {
        Row row = sheet.createRow(r);
        if (r == 0) {
            row.setRowStyle(styles.get("th"));
        }

        int rows = 1;
        for (int c = 0; c < table[r].length; c++) {
            cell = row.createCell(c);
            String s = table[r][c];
            if (s == null)
                continue;
            rows = Math.max(rows, s.split("\n").length);
            try {
                if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
                    cell.setCellStyle(styles.get("th"));
                    cell.setCellValue(s);

                } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
                    if (r == 0 && c == 0) {
                        cell.setCellStyle(styles.get("td"));
                    } else {
                        cell.setCellStyle(styles.get("th"));
                    }
                    cell.setCellValue(s);
                } else if (types[c] == Double.class) {
                    cell.setCellStyle(styles.get("td-double"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(s));
                } else if (types[c] == String.class) {
                    cell.setCellStyle(
                            styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(s);
                } else {
                    cell.setCellStyle(styles.get("td-right"));
                    cell.setCellValue(s);
                }
            } catch (Exception e) {
                cell.setCellStyle(styles.get("td"));
                cell.setCellValue(s);
            }
        }
        maxRows = Math.max(maxRows, rows);
        row.setHeightInPoints(rows * 16f);

    }

    // Add footer notes
    if (footerData.size() > 0) {
        Row row = sheet.createRow(table.length);
        row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
        cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
                row.getRowNum(), //last row  (0-based)
                0, //first column (0-based)
                table[0].length - 1 //last column  (0-based)
        ));
        //for ( String data : footerData ) {
        style = wb.createCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
        //}
    }
    footerData.clear();

    autoSizeColumns(sheet);
    if (table.length > 0) {
        for (int c = 0; c < table[0].length; c++) {
            if (sheet.getColumnWidth(c) > 10000)
                sheet.setColumnWidth(c, 3000);
        }
    }

    if (exportMode == ExportMode.HEADERS_TOPLEFT) {
        for (int r = 1; r < table.length; r++) {
            sheet.getRow(r).setHeightInPoints(maxRows * 16f);
        }
    }

    File reportFile = IOUtils.createTempFile("export_", ".xlsx");
    FileOutputStream out = new FileOutputStream(reportFile);
    wb.write(out);
    wb.close();
    out.close();
    Desktop.getDesktop().open(reportFile);
}

From source file:com.aistor.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?// w ww .  ja  v a 2  s.  com
 * @param wb 
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //      style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:com.AllenBarr.CallSheetGenerator.Generator.java

License:Open Source License

public int generateSheet(File file, Contributor contrib) {
    //create workbook file
    final String fileName = file.toString();
    final Workbook wb;
    if (fileName.endsWith(".xlsx")) {
        wb = new XSSFWorkbook();
    } else if (fileName.endsWith(".xls")) {
        wb = new HSSFWorkbook();
    } else {/*from w  w w . j  a  v a 2 s  .c  o m*/
        return 1;
    }
    //create sheet
    final Sheet sheet = wb.createSheet("Call Sheet");
    final Header header = sheet.getHeader();
    header.setCenter("Anderson for Iowa Call Sheet");
    //add empty cells
    final Row[] row = new Row[22 + contrib.getDonationsLength()];
    final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()];
    for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) {
        row[i] = sheet.createRow((short) i);
        for (int j = 0; j < 6; j++) {
            cell[j][i] = row[i].createCell(j);
        }
    }
    //populate cells with data
    //column 1
    cell[0][0].setCellValue(contrib.getName());
    cell[0][3].setCellValue("Sex:");
    cell[0][4].setCellValue("Party:");
    cell[0][5].setCellValue("Phone #:");
    cell[0][6].setCellValue("Home #:");
    cell[0][7].setCellValue("Cell #:");
    cell[0][8].setCellValue("Work #:");
    cell[0][10].setCellValue("Email:");
    cell[0][12].setCellValue("Employer:");
    cell[0][13].setCellValue("Occupation:");
    cell[0][15].setCellValue("Past Contact:");
    cell[0][17].setCellValue("Notes:");
    cell[0][21].setCellValue("Contribution History:");
    //column 2
    cell[1][3].setCellValue(contrib.getSex());
    cell[1][4].setCellValue(contrib.getParty());
    cell[1][5].setCellValue(contrib.getPhone());
    cell[1][6].setCellValue(contrib.getHomePhone());
    cell[1][7].setCellValue(contrib.getCellPhone());
    cell[1][8].setCellValue(contrib.getWorkPhone());
    cell[1][9].setCellValue("x" + contrib.getWorkExtension());
    cell[1][10].setCellValue(contrib.getEmail());
    cell[1][12].setCellValue(contrib.getEmployer());
    cell[1][13].setCellValue(contrib.getOccupation());
    cell[1][17].setCellValue(contrib.getNotes());
    //column 4
    cell[3][3].setCellValue("Salutation:");
    cell[3][4].setCellValue("Age:");
    cell[3][5].setCellValue("Spouse:");
    cell[3][7].setCellValue("Address:");
    cell[3][10].setCellValue("TARGET:");
    //column 5
    cell[4][0].setCellValue("VANID:");
    cell[4][3].setCellValue(contrib.getSalutation());
    cell[4][4].setCellValue(contrib.getAge());
    cell[4][5].setCellValue(contrib.getSpouse());
    cell[4][7].setCellValue(contrib.getStreetAddress());
    cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip());
    //column 6
    cell[5][0].setCellValue(contrib.getVANID());
    //contribution cells
    for (int i = 0; i < contrib.getDonationsLength(); i++) {
        cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate());
        cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient());
        cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount());
    }

    //format cells
    //Name cell
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
    final CellStyle leftBoldUnderline14Style = wb.createCellStyle();
    final Font boldUnderline14Font = wb.createFont();
    boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldUnderline14Font.setUnderline(Font.U_SINGLE);
    boldUnderline14Font.setFontHeightInPoints((short) 14);
    boldUnderline14Font.setFontName("Garamond");
    leftBoldUnderline14Style.setFont(boldUnderline14Font);
    leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT);
    cell[0][0].setCellStyle(leftBoldUnderline14Style);
    //field name cells
    final CellStyle rightBold10Style = wb.createCellStyle();
    final Font bold10Font = wb.createFont();
    bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold10Font.setFontHeightInPoints((short) 10);
    bold10Font.setFontName("Garamond");
    rightBold10Style.setFont(bold10Font);
    rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 3; i < 22; i++) {
        cell[0][i].setCellStyle(rightBold10Style);
    }
    sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1));
    for (int i = 3; i < 11; i++) {
        cell[3][i].setCellStyle(rightBold10Style);
    }
    cell[4][0].setCellStyle(rightBold10Style);
    //field content cells
    final CellStyle left10Style = wb.createCellStyle();
    final Font garamond10Font = wb.createFont();
    garamond10Font.setFontHeightInPoints((short) 10);
    garamond10Font.setFontName("Garamond");
    left10Style.setFont(garamond10Font);
    left10Style.setAlignment(CellStyle.ALIGN_LEFT);
    for (int i = 3; i < 5; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //phone number cells
    final CellStyle phoneStyle = wb.createCellStyle();
    phoneStyle.setFont(garamond10Font);
    phoneStyle.setAlignment(CellStyle.ALIGN_LEFT);
    final CreationHelper createHelper = wb.getCreationHelper();
    phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####"));
    for (int i = 5; i < 9; i++) {
        cell[1][i].setCellStyle(phoneStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2));

    }
    cell[1][9].setCellStyle(left10Style);
    //email through past contact
    for (int i = 10; i < 16; i++) {
        cell[1][i].setCellStyle(left10Style);
    }
    //notes
    CellStyle noteStyle = wb.createCellStyle();
    noteStyle.cloneStyleFrom(left10Style);
    noteStyle.setWrapText(true);
    cell[1][17].setCellStyle(noteStyle);
    //column E
    for (int i = 3; i < 11; i++) {
        cell[4][i].setCellStyle(left10Style);
    }
    //VanID Cell
    final CellStyle right10Style = wb.createCellStyle();
    right10Style.setFont(garamond10Font);
    right10Style.setAlignment(CellStyle.ALIGN_RIGHT);
    cell[5][0].setCellStyle(right10Style);
    //Notes cell
    sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5));
    //contribution cells
    final CellStyle date10Style = wb.createCellStyle();
    date10Style.setFont(garamond10Font);
    date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));
    date10Style.setBorderBottom(CellStyle.BORDER_THIN);
    date10Style.setBorderTop(CellStyle.BORDER_THIN);
    date10Style.setBorderLeft(CellStyle.BORDER_THIN);
    date10Style.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle contributionStyle = wb.createCellStyle();
    contributionStyle.cloneStyleFrom(left10Style);
    contributionStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contributionStyle.setBorderTop(CellStyle.BORDER_THIN);
    contributionStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contributionStyle.setBorderRight(CellStyle.BORDER_THIN);
    final CellStyle money10Style = wb.createCellStyle();
    money10Style.setFont(garamond10Font);
    money10Style.setDataFormat(
            createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
    money10Style.setBorderBottom(CellStyle.BORDER_THIN);
    money10Style.setBorderTop(CellStyle.BORDER_THIN);
    money10Style.setBorderLeft(CellStyle.BORDER_THIN);
    money10Style.setBorderRight(CellStyle.BORDER_THIN);
    for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) {
        cell[0][i].setCellStyle(date10Style);
        cell[1][i].setCellStyle(contributionStyle);
        cell[2][i].setCellStyle(contributionStyle);
        cell[3][i].setCellStyle(contributionStyle);
        cell[4][i].setCellStyle(contributionStyle);
        sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4));
        cell[5][i].setCellStyle(money10Style);

    }
    //resize columns
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    try {
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        return 1;
    } catch (IOException ex) {
        return 1;
    }

    return 0;
}

From source file:com.automaster.autoview.server.servlet.ExcelServlet.java

protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    /*response.setContentType("application/vnd.ms-excel");
     response.setHeader("Content-Disposition", "attachment; filename=filename.xls");
     HSSFWorkbook workbook = new HSSFWorkbook();
     // ...//  ww w  .  j a v  a  2s  .  c  om
     // Now populate workbook the usual way.
     // ...
     OutputStream arqSaida = response.getOutputStream();
     workbook.write(arqSaida); // Write workbook to response.
     arqSaida.flush();
     arqSaida.close();*/
    //getServletContext().getRealPath("/")
    String tempoDecorrido = " 0";
    String url = getServletContext().getRealPath("/");
    String placa = request.getParameter("placa");
    //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC));
    Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio")));
    Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim")));
    //String timeZoneInterface = request.getParameter("timeZone");
    /*String timeZone = "Z";
    if(timeZoneInterface.equalsIgnoreCase("0")){
    timeZone = "Z";
    } else {
    timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60));
    }  */
    TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3"));
    System.out.println("Time zone Cliente : " + timeZonePadrao);
    //System.out.println("timeZoneInterface: "+timeZoneInterface);
    //System.out.println("timeZone: "+timeZone);
    //String ign = request.getParameter("ign");
    ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO();
    ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa,
            dataInicio, dataFim);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx");

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Histrico - " + placa);
    int pictureIdx;
    try ( //add picture data to this workbook.
            InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) {
        byte[] bytes = IOUtils.toByteArray(is);
        pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
    }

    CreationHelper helper = wb.getCreationHelper();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(1);
    anchor.setRow1(0);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    //auto-size picture relative to its top-left corner
    pict.resize(3, 3);
    //pict.resize();
    //sheet.setColumnWidth(0, 200);

    Font fonte = wb.createFont();
    fonte.setFontHeightInPoints((short) 24);
    fonte.setFontName("Arial");
    fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fonte.setItalic(true);
    CellStyle estiloTitulo = wb.createCellStyle();
    estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloTitulo.setFont(fonte);

    Font fonteCabecalho = wb.createFont();
    fonteCabecalho.setFontHeightInPoints((short) 14);
    fonteCabecalho.setFontName("Arial");
    fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //fonteCabecalho.setItalic(true);
    CellStyle estiloCabecalho = wb.createCellStyle();
    estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estiloCabecalho.setFont(fonteCabecalho);

    Font fonteTituloTabela = wb.createFont();
    //fonteTituloTabela.setFontHeightInPoints((short) 14);
    fonteTituloTabela.setFontName("Arial");
    fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    CellStyle estilo = wb.createCellStyle();
    estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    estilo.setFont(fonteTituloTabela);

    CellStyle estiloCorpo = wb.createCellStyle();
    estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index);
    //estiloCorpo.setFont(fonteTituloTabela);

    XSSFRow linha6 = (XSSFRow) sheet.createRow(6);
    XSSFCell cell046 = linha6.createCell(3);
    cell046.setCellValue("Relatrio de Posies");
    cell046.setCellStyle(estiloTitulo);
    //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3));

    XSSFRow linha7 = (XSSFRow) sheet.createRow(7);
    XSSFCell cell047 = linha7.createCell(3);
    cell047.setCellValue("Veculo : " + placa);
    cell047.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3));

    XSSFRow linha8 = (XSSFRow) sheet.createRow(8);
    XSSFCell cell038 = linha8.createCell(3);
    //TimeZone.setDefault(timeZoneMundial);
    //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio")));
    //TimeZone.setDefault(timeZoneCliente);
    //Date dataHoraInicio = new Date(dataHoraInicio0.getTime());
    SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    dataFormatadaCabecalho.setTimeZone(timeZonePadrao);
    cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: "
            + dataFormatadaCabecalho.format(dataFim));
    cell038.setCellStyle(estiloCabecalho);
    //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3));

    sheet.setColumnWidth(0, 5000);
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 3500);
    //sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(3, 30000);
    //sheet.setColumnWidth(4, 4000);
    //sheet.setColumnWidth(4, 30000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 3000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 3000);
    sheet.setColumnWidth(9, 3000);
    //sheet.setColumnWidth(10, 20000);        
    sheet.setColumnWidth(11, 3000);
    XSSFRow linha9 = (XSSFRow) sheet.createRow(10);
    XSSFCell cell0 = linha9.createCell(0);
    cell0.setCellValue("Data e hora");
    cell0.setCellStyle(estilo);
    XSSFCell cell1 = linha9.createCell(1);
    cell1.setCellValue("Velocidade");
    cell1.setCellStyle(estilo);
    XSSFCell cell2 = linha9.createCell(2);
    cell2.setCellValue("Ignio");
    cell2.setCellStyle(estilo);
    //XSSFCell cell3 = linha9.createCell(3);
    //cell3.setCellValue("Latitude");
    //cell3.setCellStyle(estilo);
    //XSSFCell cell4 = linha9.createCell(4);
    //cell4.setCellValue("Longitude");
    //cell4.setCellStyle(estilo);
    //XSSFCell cell5 = linha9.createCell(5);
    //cell5.setCellValue("Satlite");
    //cell5.setCellStyle(estilo);
    //XSSFCell cell6 = linha9.createCell(6);
    //cell6.setCellValue("GPS");
    //cell6.setCellStyle(estilo);
    //XSSFCell cell7 = linha9.createCell(7);
    //cell7.setCellValue("Entrada");
    //cell7.setCellStyle(estilo);
    //XSSFCell cell8 = linha9.createCell(8);
    //cell8.setCellValue("Sada");
    //cell8.setCellStyle(estilo);
    //XSSFCell cell9 = linha9.createCell(9);
    //cell9.setCellValue("Evento");
    //cell9.setCellStyle(estilo);
    XSSFCell cell10 = linha9.createCell(3);
    cell10.setCellValue("Endereo");
    cell10.setCellStyle(estilo);
    //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8));
    //XSSFCell cell11 = linha9.createCell(11);
    //cell11.setCellValue("Direo");
    //cell11.setCellStyle(estilo);
    int linha = 0;
    int j = 11;
    double latAnt = 0;
    double lonAnt = 0;
    double latAtual = 0;
    double lonAtual = 0;
    double distancia = 0;
    double distanciaTotal = 0;
    for (int i = 0; i < posicoes.size(); i++) {
        XSSFRow row = (XSSFRow) sheet.createRow(j);
        if (i == 0) {
            distancia = 0;
            //System.out.println("linha 00 - EXCEL");
        } else {
            //System.out.println("linha 01 - EXCEL");
            latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon"));
            latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
            lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
            //System.out.println("linha 02 - PDF");
            if (latAnt == latAtual && lonAnt == lonAtual) {
                distancia = 0;
            } else {
                distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
                //System.out.println("linha 03 - PDF");
            }

        }
        distanciaTotal = distanciaTotal + distancia;
        /*if(i==0) {
         latAnt = Double.parseDouble(posicoes.get(i).get("lat"));
         lonAnt = Double.parseDouble(posicoes.get(i).get("lon"));
        } else{
            latAnt = Double.parseDouble(posicoes.get(i-1).get("lat"));
            lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon"));
        }
        double latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
        double lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
        double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
        distanciaTotal = distanciaTotal + distancia;*/
        for (int col = 0; col < posicoes.get(linha).size(); col++) {
            XSSFCell cell = row.createCell(col);
            cell.setCellStyle(estiloCorpo);
            switch (col) {
            case 0:
                Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora")));
                Date dataHora = new Date(dataHora0.getTime());
                SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
                dataFormatada.setTimeZone(timeZonePadrao);
                cell.setCellValue(dataFormatada.format(dataHora));
                break;
            case 1:
                cell.setCellValue(posicoes.get(linha).get("vel"));
                break;
            case 2:
                cell.setCellValue(
                        posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada");
                break;
            /*case 3:
                cell.setCellValue(posicoes.get(linha).get("lat"));
                break;
            case 4:
                cell.setCellValue(posicoes.get(linha).get("lon"));
                break;
            case 5:
                cell.setCellValue(posicoes.get(linha).get("sat"));
                break;
            case 6:
                cell.setCellValue(posicoes.get(linha).get("gps"));
                break;
            case 7:
                cell.setCellValue(posicoes.get(linha).get("entrada"));
                break;
            case 8:
                cell.setCellValue(posicoes.get(linha).get("saida"));
                break;
            case 9:
                cell.setCellValue(posicoes.get(linha).get("evento"));
                break;*/
            case 3:
                cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo"
                        : posicoes.get(linha).get("endereco"));
                break;
            /*case 11:
                cell.setCellValue(posicoes.get(linha).get("direcao"));
                break;*/
            }

        }
        j = j + 1;
        linha = linha + 1;

    }
    tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")),
            Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora")));
    int index = 0;
    String kms = "0";
    String m = "";
    double metros = 0;
    if (distanciaTotal > 0) {
        BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN);
        index = String.valueOf(decimalFormatado).indexOf(".");
        kms = String.valueOf(decimalFormatado).substring(0, index);
        m = "0" + (String.valueOf(decimalFormatado).substring(index));
        metros = Double.parseDouble(m) * 1000;
    }
    //String formatted = NumberFormat.getFormat("000.00").format(metros);
    //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros");
    XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12);
    XSSFCell cellX = linhaX.createCell(0);
    sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3));
    cellX.setCellStyle(estilo);
    cellX.setCellValue(
            "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido);
    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);

    byte[] outArray = outByteStream.toByteArray();
    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);
    outStream.flush();

}

From source file:com.b510.excel.client.BusinessPlan.java

License:Apache License

/**
 * create a library of cell styles/*from  w  w  w. j  av  a  2 s  .  co  m*/
 */
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    DataFormat df = wb.createDataFormat();

    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("header_date", style);

    Font font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font1);
    styles.put("cell_b_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_b_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_g", style);

    Font font2 = wb.createFont();
    font2.setColor(IndexedColors.BLUE.getIndex());
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font2);
    styles.put("cell_bb", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_bg", style);

    Font font3 = wb.createFont();
    font3.setFontHeightInPoints((short) 14);
    font3.setColor(IndexedColors.DARK_BLUE.getIndex());
    font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font3);
    style.setWrapText(true);
    styles.put("cell_h", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_normal_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setIndention((short) 1);
    style.setWrapText(true);
    styles.put("cell_indented", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("cell_blue", style);

    return styles;
}

From source file:com.b510.excel.client.CalendarDemo.java

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *//*w  w  w  .j av  a 2s  .  com*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    Font dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_right", style);

    return styles;
}