Example usage for org.apache.poi.hssf.usermodel HSSFClientAnchor HSSFClientAnchor

List of usage examples for org.apache.poi.hssf.usermodel HSSFClientAnchor HSSFClientAnchor

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

Introduction

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

Prototype

public HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)

Source Link

Document

Creates a new client anchor and sets the top-left and bottom-right coordinates of the anchor.

Usage

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell/*from  w  w w  . j  av a 2  s .  c  om*/
 */
public void createImageCell(Cell cell, double height, String imagePath, byte[] data) throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell//from   w  w w . ja  v a  2s.  c o m
 */
public void createImageCell(Cell cell, double height, int rowspan, int colspan, String imagePath, byte[] data)
        throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.bzvs.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 * Cell/*from   ww w .j av a 2s . c  o m*/
 *
 * @param patriarch
 * @param entity
 * @param row
 * @param i
 * @param imagePath
 * @param obj
 * @throws Exception
 */
public void createImageCell(Drawing patriarch, ExcelExportEntity entity, Row row, int i, String imagePath,
        Object obj) throws Exception {
    row.setHeight((short) (50 * entity.getHeight()));
    row.createCell(i);
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1),
                row.getRowNum() + 1);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1),
                row.getRowNum() + 1);
    }

    byte[] value = null;
    if (entity.getExportImageType() == 1) {
        if (StringUtils.isNotEmpty(imagePath)) {
            value = ImageCache.getImage(imagePath);
        }
    } else {
        value = (byte[]) (entity.getMethods() != null ? getFieldBySomeMethod(entity.getMethods(), obj)
                : entity.getMethod().invoke(obj, new Object[] {}));
    }
    if (value != null) {
        patriarch.createPicture(anchor, row.getSheet().getWorkbook().addPicture(value, getImageType(value)));
    }

}

From source file:cn.mario256.blog.ExcelView.java

License:Open Source License

/**
 * ?Excel//  w  w  w .  ja va  2  s . com
 * 
 * @param model
 *            ?
 * @param workbook
 *            HSSFWorkbook
 * @param request
 *            HttpServletRequest
 * @param response
 *            HttpServletResponse
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);

    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:com.app.ExcelView.java

License:Open Source License

/**
 * ?Excel//from   ww  w.  j  a v a2  s .c  o  m
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

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

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("new sheet");
    byte data[] = new byte[8000];
    try {/*from w w  w  .j a v a 2  s. com*/
        new DataInputStream(new FileInputStream(
                "D:\\Users\\Adriano\\Documents\\NetBeansProjects\\JRGWT\\web\\imagens\\logo.jpg")).read(data);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    int index = wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG);

    HSSFClientAnchor ancora = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 5, (short) 10, 10);
    ancora.setAnchorType(2);
    sheet.createDrawingPatriarch().createPicture(ancora, index);

    //Write the Excel file
    FileOutputStream fileOut = null;
    fileOut = new FileOutputStream("C://myFile.xls");
    wb.write(fileOut);
    fileOut.close();

}

From source file:com.dp2345.ExcelView.java

License:Open Source License

/**
 * ?Excel//from   w  w  w  .  jav a2 s  .  co  m
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "D" + "P" + "2" + "3" + "4" + "5"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:com.kcs.action.ForwardContractAction.java

@Override
@SuppressWarnings("empty-statement")
public String export() throws Exception {
    list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate()));

    getLogger().debug("exportExcel : begin...");
    setFileName("Forward contract Report.xls");
    DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy");
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    HSSFCellStyle style = myWorkBook.createCellStyle();
    HSSFCellStyle styleFont = myWorkBook.createCellStyle();
    HSSFFont fontB = myWorkBook.createFont();
    fontB.setBoldweight(Font.BOLDWEIGHT_BOLD);
    styleFont.setFont(fontB);/*w ww  . jav  a2  s.  c om*/

    list = sortSheet(list);
    List<String> listSheet = countSheet(list);

    for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) {
        List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet));
        HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet));
        List<String> currencyList = getCurrency(sheetObject);

        //------------------------- ROW 1 -----------------------------//
        Row FW1_row_0 = FW1.createRow(0);

        FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT");
        CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER);

        HSSFCellStyle styleRow1 = myWorkBook.createCellStyle();

        styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleRow1.setFont(fontB);

        FW1_row_0.getCell(0).setCellStyle(styleRow1);

        FW1_row_0.createCell(1)
                .setCellValue(" ? ?()");
        FW1_row_0.getCell(1).setCellStyle(styleRow1);

        // FW1_row_0.getCell(8).setCellStyle(styleFont);

        //------------------------- ROW 2 -----------------------------//
        Row FW1_row_1 = FW1.createRow(1);

        FW1_row_1.createCell(0).setCellValue("FW");
        //        FW1_row_1.getCell(0).setCellStyle(styleBR);

        //     FW1.addMergedRegion(new CellRangeAddress(1,1,7,8));

        FW1.autoSizeColumn(0);
        FW1.autoSizeColumn(7);
        FW1.autoSizeColumn(8);

        Row FW1_row_2 = FW1.createRow(2);
        FW1_row_2.createCell(0).setCellValue("?");
        //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT);
        HSSFCellStyle style2 = myWorkBook.createCellStyle();

        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        FW1_row_2.getCell(0).setCellStyle(style2);

        Row FW1_row_3 = FW1.createRow(3);
        FW1_row_3.createCell(0).setCellValue("?");

        HSSFCellStyle style3 = myWorkBook.createCellStyle();
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        FW1_row_3.getCell(0).setCellStyle(style3);

        FW1.createRow(4).createCell(0).setCellValue("?");
        FW1.createRow(5).createCell(0).setCellValue("? (129027)");
        FW1.createRow(6).createCell(0).setCellValue(
                "???");
        FW1.createRow(7).createCell(0).setCellValue("  ?");
        FW1.createRow(8).createCell(0).setCellValue("  - ?? (129030)");
        FW1.createRow(9).createCell(0)
                .setCellValue("  - ??? (129031)");
        FW1.createRow(10).createCell(0).setCellValue("  ??");
        FW1.createRow(11).createCell(0).setCellValue("  ");
        FW1.createRow(12).createCell(0).setCellValue("  - ?? (129034)");
        FW1.createRow(13).createCell(0)
                .setCellValue("  - ??? (129035)");
        FW1.createRow(14).createCell(0).setCellValue("  ???");
        FW1.createRow(15).createCell(0).setCellValue("  - ?? (129037)");
        FW1.createRow(16).createCell(0)
                .setCellValue("  - ??? (129038) ");
        FW1.createRow(17).createCell(0)
                .setCellValue("  ?");
        FW1.createRow(18).createCell(0).setCellValue("  - ?? (129040)");
        FW1.createRow(19).createCell(0)
                .setCellValue("  - ??? (129041) ");
        FW1.createRow(20).createCell(0)
                .setCellValue("  ??");
        FW1.createRow(21).createCell(0).setCellValue("  - ?? (129043)");
        FW1.createRow(22).createCell(0)
                .setCellValue("  - ??? (129044)");
        FW1.createRow(23).createCell(0)
                .setCellValue("  ?");
        FW1.createRow(24).createCell(0).setCellValue("  - ?? (129046)");
        FW1.createRow(25).createCell(0)
                .setCellValue("  - ??? (129047) ");
        FW1.createRow(26).createCell(0).setCellValue("????");
        FW1.createRow(27).createCell(0).setCellValue("  ??");
        FW1.createRow(28).createCell(0).setCellValue("  - ?? (129050)");
        FW1.createRow(29).createCell(0)
                .setCellValue("  - ??? (129051)");
        FW1.createRow(30).createCell(0)
                .setCellValue("  ");
        FW1.createRow(31).createCell(0).setCellValue("  - ?? (129053)");
        FW1.createRow(32).createCell(0)
                .setCellValue("  - ??? (129054)");
        FW1.createRow(33).createCell(0).setCellValue("  ?  ");
        FW1.createRow(34).createCell(0).setCellValue("  - ?? (129056) ");
        FW1.createRow(35).createCell(0)
                .setCellValue("  - ??? (129057)  ");
        FW1.createRow(36).createCell(0).setCellValue("   ");
        FW1.createRow(37).createCell(0).setCellValue("  - ?? (129059) ");
        FW1.createRow(38).createCell(0)
                .setCellValue("  - ??? (129060)  ");
        FW1.createRow(39).createCell(0).setCellValue("? (129061) ");
        FW1.createRow(40).createCell(0)
                .setCellValue("? (129062) ");
        //    FW1.getRow(40).getCell(0).setCellStyle(styleBR);

        FW1.getRow(4).getCell(0).setCellStyle(styleFont);
        FW1.getRow(5).getCell(0).setCellStyle(styleFont);
        FW1.getRow(6).getCell(0).setCellStyle(styleFont);
        FW1.getRow(7).getCell(0).setCellStyle(styleFont);
        FW1.getRow(11).getCell(0).setCellStyle(styleFont);
        FW1.getRow(14).getCell(0).setCellStyle(styleFont);
        FW1.getRow(17).getCell(0).setCellStyle(styleFont);
        FW1.getRow(20).getCell(0).setCellStyle(styleFont);
        FW1.getRow(23).getCell(0).setCellStyle(styleFont);
        FW1.getRow(26).getCell(0).setCellStyle(styleFont);
        FW1.getRow(30).getCell(0).setCellStyle(styleFont);
        FW1.getRow(33).getCell(0).setCellStyle(styleFont);
        FW1.getRow(36).getCell(0).setCellStyle(styleFont);
        FW1.getRow(39).getCell(0).setCellStyle(styleFont);
        FW1.getRow(40).getCell(0).setCellStyle(styleFont);

        int cellCurrency = 1;

        for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) {

            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);

            String currencyName = currencyList.get(iCurrencyList);
            FW1_row_2.createCell(cellCurrency).setCellValue(currencyName);
            FW1_row_3.createCell(cellCurrency).setCellValue("");
            FW1_row_2.getCell(cellCurrency).setCellStyle(style);
            FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style);

            FW1_row_3.getCell(cellCurrency).setCellStyle(style);

            List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName);

            for (ForwardContract objCCY : tmpGroupCCY) {
                FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + "");

                if ("????"
                        .equals(objCCY.getTRANS_TYPE())) {
                    if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) {
                        FW1.getRow(8).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true));
                        FW1.getRow(12).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true));
                        FW1.getRow(15).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true));
                        FW1.getRow(18).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true));
                        FW1.getRow(21).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true));
                        FW1.getRow(24).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true));

                        FW1.getRow(8).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true));
                        FW1.getRow(12).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true));
                        FW1.getRow(15).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true));
                        FW1.getRow(18).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true));
                        FW1.getRow(21).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true));
                        FW1.getRow(24).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true));
                    } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) {

                        FW1.getRow(9).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false));
                        FW1.getRow(13).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false));
                        FW1.getRow(16).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false));
                        FW1.getRow(19).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false));
                        FW1.getRow(22).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false));
                        FW1.getRow(25).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false));

                        FW1.getRow(9).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false));
                        FW1.getRow(13).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false));
                        FW1.getRow(16).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false));
                        FW1.getRow(19).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false));
                        FW1.getRow(22).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false));
                        FW1.getRow(25).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false));

                    }
                } else if ("????"
                        .equals(objCCY.getTRANS_TYPE().trim())) {
                    if ("New Forward".equals(objCCY.getLIST_ITEM())) {
                        FW1.getRow(28).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true));
                        FW1.getRow(31).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true));
                        FW1.getRow(34).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true));
                        FW1.getRow(37).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true));

                        FW1.getRow(28).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true));
                        FW1.getRow(31).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true));
                        FW1.getRow(34).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true));
                        FW1.getRow(37).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true));

                    } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) {

                        FW1.getRow(29).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false));
                        FW1.getRow(32).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false));
                        FW1.getRow(35).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false));
                        FW1.getRow(38).createCell(cellCurrency)
                                .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false));

                        FW1.getRow(29).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false));
                        FW1.getRow(32).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false));
                        FW1.getRow(35).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false));
                        FW1.getRow(38).createCell(cellCurrency + 1)
                                .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false));

                    }
                } else if ("?".equals(objCCY.getTRANS_TYPE())) {

                }

                FW1.getRow(39).createCell(cellCurrency)
                        .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false));
                FW1.getRow(40).createCell(cellCurrency)
                        .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false));

                FW1.getRow(39).createCell(cellCurrency + 1)
                        .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false));
                FW1.getRow(40).createCell(cellCurrency + 1)
                        .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false));

            }
            //   
            // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+"");

            CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER);
            FW1_row_2.getCell(cellCurrency).setCellStyle(style);
            FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency));

            FW1_row_3.createCell(cellCurrency).setCellValue("");
            HSSFCellStyle styleTop = style;
            styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN);
            FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop);

            cellCurrency++;
        }
        System.out.println("cellCurrency ++++ " + cellCurrency);
        int finishM = cellCurrency - 3;

        int noOfColumns = FW1.getRow(2).getLastCellNum();
        int noOfRow = FW1.getLastRowNum();

        //          HSSFPalette palette = myWorkBook.getCustomPalette();
        //          HSSFColor hssfColor = null;
        //
        //
        //    palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255);
        //    hssfColor = palette.getColor(HSSFColor.WHITE.index);
        //    
        //    
        //
        //    HSSFCellStyle styleBG = myWorkBook.createCellStyle();
        //    styleBG.setFillForegroundColor(hssfColor.getIndex());
        //    styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND);  

        for (int i = 0; i < noOfRow; i++) {

            for (int j = 0; j < noOfColumns; j++) {
                FW1.autoSizeColumn(i);
                //  row.getCell(j).setCellStyle(styleBG);
            }
        }

        for (int i = 4; i < noOfRow + 1; i++) {

            for (int j = 1; j < noOfColumns; j++) {
                HSSFCellStyle style6 = myWorkBook.createCellStyle();
                try {

                    if (j == 1) {
                        style6.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                        style6.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                    } else {
                        style6.setBorderRight(HSSFCellStyle.BORDER_THIN);
                        style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                    }

                    if (i == noOfRow) {
                        style6.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    }

                    FW1.getRow(i).getCell(j).setCellStyle(style6);
                } catch (Exception e) {

                    FW1.getRow(i).createCell(j).setCellStyle(style6);
                }
            }
        }

        HSSFFont fontBold = myWorkBook.createFont();
        fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);

        HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle();

        borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN);
        borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        borderRightCenterFontBold.setFont(fontBold);

        HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold;
        borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle styleR = myWorkBook.createCellStyle();
        styleR.setBorderRight(HSSFCellStyle.BORDER_THIN);

        //  Forward contract
        // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold);
        // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold);
        FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM));

        FW1_row_0.createCell(finishM + 1)
                .setCellValue(" " + dateFormat.format(new Date()) + "");

        HSSFCellStyle style4 = myWorkBook.createCellStyle();

        style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        FW1_row_0.getCell(finishM + 1).setCellStyle(style4);
        FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont);

        FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2));
        FW1_row_1.createCell(finishM + 1).setCellValue(
                ":");

        FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2));

        FW1_row_1.getCell(finishM + 1).setCellStyle(style4);
        FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont);

        FW1_row_0.createCell(finishM + 2).setCellStyle(styleR);
        FW1_row_1.createCell(finishM + 2).setCellStyle(styleR);

        FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold);
        //        FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold);
        HSSFCellStyle borderBottom = myWorkBook.createCellStyle();
        borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN);
        borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        borderBottom.setFont(fontBold);

        FW1.getRow(40).getCell(0).setCellStyle(borderBottom);

        HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch();

        /* Here is the thing: the line will go from top left in cell (0,0) to down left 
        of cell (0,1) */
        //  int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3);

        HSSFSimpleShape shape = patriarch.createSimpleShape(anchor);
        shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
        shape.setLineStyleColor(10, 10, 10);
        shape.setFillColor(90, 10, 200);
        shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT);
        shape.setLineStyle(HSSFShape.LINESTYLE_SOLID);

        //  createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow);    
        //  FW1.createFreezePane(0,4);

    }

    ByteArrayOutputStream boas = new ByteArrayOutputStream();
    myWorkBook.write(boas);
    setExcelStream(new ByteArrayInputStream(boas.toByteArray()));

    getLogger().debug("exportExcel : end...");
    return "excel";
}

From source file:com.krawler.esp.servlets.exportExcel.java

License:Open Source License

public void addimage(String imagepath, int pictype, HSSFWorkbook wb, HSSFSheet sheet, int dx1, int dy1, int dx2,
        int dy2, int col1, int row1, int col2, int row2) throws IOException {
    FileInputStream fimage = null;
    ByteArrayOutputStream bos = null;
    try {//from   w w  w  .j av  a2  s. c  o  m
        fimage = new FileInputStream(imagepath);
        bos = new ByteArrayOutputStream();
        int c;
        while ((c = fimage.read()) != -1)
            bos.write(c);
    } catch (IOException e) {
        e.printStackTrace();
        System.out.println(e);
    } finally {
        fimage.close();
    }

    int imgindex = wb.addPicture(bos.toByteArray(), pictype);
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFClientAnchor anchor;
    anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, (short) col1, row1, (short) col2, row2);
    anchor.setAnchorType(2);
    patriarch.createPicture(anchor, imgindex);
}

From source file:com.lingxiang2014.ExcelView.java

License:Open Source License

public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);/*from  w ww  .j a  v  a2  s  .  co m*/
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}