Example usage for org.apache.poi.hssf.usermodel HSSFSheet setHorizontallyCenter

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setHorizontallyCenter

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

Introduction

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

Prototype

@Override
public void setHorizontallyCenter(boolean value)

Source Link

Document

determines whether the output is horizontally centered on the page.

Usage

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) {
    if (src == null || dest == null)
        return;//from w  w w.j  a v a2  s . c o m

    dest.setAlternativeExpression(src.getAlternateExpression());
    dest.setAlternativeFormula(src.getAlternateFormula());
    dest.setAutobreaks(src.getAutobreaks());
    dest.setDialog(src.getDialog());
    if (src.getColumnBreaks() != null) {
        for (int col : src.getColumnBreaks()) {
            dest.setColumnBreak(col);
        }
    }
    dest.setDefaultColumnWidth(src.getDefaultColumnWidth());
    dest.setDefaultRowHeight(src.getDefaultRowHeight());
    dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints());
    dest.setDisplayGuts(src.getDisplayGuts());
    dest.setFitToPage(src.getFitToPage());
    dest.setHorizontallyCenter(src.getHorizontallyCenter());
    dest.setDisplayFormulas(src.isDisplayFormulas());
    dest.setDisplayGridlines(src.isDisplayGridlines());
    dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings());
    dest.setGridsPrinted(src.isGridsPrinted());
    dest.setPrintGridlines(src.isPrintGridlines());

    for (int i = 0; i < src.getNumMergedRegions(); i++) {
        CellRangeAddress r = src.getMergedRegion(i);
        dest.addMergedRegion(r);
    }

    if (src.getRowBreaks() != null) {
        for (int row : src.getRowBreaks()) {
            dest.setRowBreak(row);
        }
    }
    dest.setRowSumsBelow(src.getRowSumsBelow());
    dest.setRowSumsRight(src.getRowSumsRight());

    int maxcol = 0;
    for (int i = 0; i <= src.getLastRowNum(); i++) {
        HSSFRow row = src.getRow(i);
        if (row != null) {
            if (maxcol < row.getLastCellNum())
                maxcol = row.getLastCellNum();
        }
    }
    for (int col = 0; col <= maxcol; col++) {
        if (src.getColumnWidth(col) != src.getDefaultColumnWidth())
            dest.setColumnWidth(col, src.getColumnWidth(col));
        dest.setColumnHidden(col, src.isColumnHidden(col));
    }
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java

License:Open Source License

/**
 * /*from  ww w.  ja va 2s.  c o  m*/
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    HSSFWorkbook wb;

    wb = new HSSFWorkbook();

    Map<String, HSSFCellStyle> styles = createStyles(wb);
    HSSFSheet sheet = wb.createSheet(sheetName);

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    HSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    HSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

    for (int i = 0; i < titles.length; i++) {
        HSSFCell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));

        width[i][0] = titles[i].length();
    }

    HSSFRow row;
    HSSFCell cell;
    int rownum = 1;

    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (int i = 0; i < titles.length; i++) {
        int widthShort = (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell(position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

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

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java

License:Open Source License

/**
 * //from w  w  w  .ja  v a2 s. com
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    HSSFWorkbook wb = new HSSFWorkbook();
    Map<String, HSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        HSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        HSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        HSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        //                sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        HSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth((i * 2), (5 * 256)); //the column is 5 characters wide
            sheet.setColumnWidth((i * 2 + 1), (13 * 256)); //the column is 13 characters wide
            //sheet.addMergedRegion(new Region(1, (short) 1, i*2, (short) (i * 2 + 1)));
            sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test
            HSSFCell monthCell = monthRow.createCell((i * 2));
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            HSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                HSSFCell dayCell_1 = row.createCell((i * 2));
                HSSFCell dayCell_2 = row.createCell((i * 2 + 1));

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file        

    FileOutputStream out = new FileOutputStream(this.filename);
    wb.write(out);
    out.close();
}

From source file:demo.admin.controller.UserController.java

@RequestMapping(value = "/user/downloadData")
@VerifyAuthentication(Trader = true, Admin = true, Operation = true)
public HttpEntity<byte[]> downloadUserData(String status, String securephone,
        @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
        @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate)
        throws IOException, DocumentException {
    if (securephone != null && securephone != "") {
        securephone = Where.$like$(securephone);
    }/* w  w w .j av  a  2 s .c  o m*/
    List<Map<String, Object>> users = userMapper.userExport(status, securephone, startDate, endDate);
    String type = status + "?";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(type);
    HSSFRow row = sheet.createRow(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    sheet.setVerticallyCenter(true);
    sheet.setHorizontallyCenter(true);
    String[] excelHeader = { "??", "???", "??", "", "??" };
    for (int i = 0; i < excelHeader.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(excelHeader[i]);
        cell.setCellStyle(cellStyle);
        sheet.autoSizeColumn(i, true);
    }
    for (int i = 0; i < users.size(); i++) {
        Map<String, Object> resultSet = users.get(i);
        sheet.autoSizeColumn(i, true);
        row = sheet.createRow(i + 1);
        row.setRowStyle(cellStyle);
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(String.valueOf(resultSet.get("name")));
        row.createCell(2).setCellValue(String.valueOf(resultSet.get("address")));
        row.createCell(3).setCellValue(String.valueOf(resultSet.get("registertime")));
        row.createCell(4).setCellValue(String.valueOf(resultSet.get("securephone")));

    }
    File file = File.createTempFile(".xls", ".xls");
    OutputStream out = new FileOutputStream(file);
    wb.write(out);
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attachment",
            URLEncoder.encode(type, "UTF-8") + LocalDate.now() + ".xls");
    return new HttpEntity<byte[]>(FileUtils.readFileToByteArray(file), headers);
}

From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java

License:BSD License

public void testGenerateEXCEL() throws Exception {
    HealthcareSite hcs = healthcareSiteDao.getById(1100);

    DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
    Date startDate = (Date) format.parse("01/11/1990");
    Date endDate = (Date) format.parse("01/01/2007");
    String grantNumber = "GRANT-NO 1232";

    Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate);
    String reportingSource = healthcareSiteDao.getById(1000).getName();
    summary3Report.setReportingSource(reportingSource);

    summaryReportService.buildSummary3Report(summary3Report);

    String xmlString = summaryReportService.generateXML(summary3Report);
    File file = new File("testReport.xml");

    FileWriter fileWriter = new FileWriter(file);
    fileWriter.write(xmlString);//from  w w  w .ja  v a  2 s  .  c om
    fileWriter.flush();
    fileWriter.close();
    System.out.println(xmlString);

    // creating the workbook and the spreadsheet

    try {

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

        InputStream inputStream = new FileInputStream(file);
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        // creating table header 2nd & 3rd cells

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

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

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

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

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

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

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

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

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

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

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

            cell.setCellValue("");

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

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls");
        FileOutputStream output = new FileOutputStream(outputFile);
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }

}

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

License:BSD License

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

    // creating the workbook and the spreadsheet
    try {/*from ww w. j a  v  a2 s .  co m*/

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

        String fileName = "";

        fileName = file;

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

        HSSFWorkbook wb = new HSSFWorkbook();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        // creating table header 2nd & 3rd cells

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

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

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

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

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

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

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

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

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

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

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

            cell.setCellValue("");

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

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

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

/**
 * Ralise l'export des donnes de contenu et de la ligne d'en-tte.
 *
 * @param parameters Paramtre de cet export
 * @param workbook Document excel/*  w  w  w .jav  a  2 s  .com*/
 * @param sheet Feuille Excel
 * @param forceLandscape Indique si le parametrage force un affichage en paysage
 */
private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet,
        final boolean forceLandscape) {
    initHssfStyle(workbook);

    // Column width
    final Map<Integer, Double> maxWidthPerColumn = new HashMap<>();
    if (parameters.hasDtObject()) {
        exportObject(parameters, workbook, sheet, maxWidthPerColumn);
    } else {
        exportList(parameters, workbook, sheet, maxWidthPerColumn);
    }
    // On definit la largeur des colonnes:
    double totalWidth = 0;
    int cellIndex;
    for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) {
        cellIndex = entry.getKey();
        final Double maxLength = entry.getValue();
        final double usesMaxLength = Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH);
        sheet.setColumnWidth(cellIndex, Double.valueOf(usesMaxLength * 256).intValue());
        totalWidth += usesMaxLength;
    }
    /**
     * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ?
     */

    // note: il ne semble pas simple de mettre title et author dans les
    // proprits du document
    final String title = parameters.getTitle();
    if (title != null) {
        final HSSFHeader header = sheet.getHeader();
        header.setLeft(title);
    }
    sheet.setHorizontallyCenter(true);
    sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE);
    if (forceLandscape || totalWidth > 85) {
        sheet.getPrintSetup().setLandscape(true);
    }

    // On dfinit le footer
    final HSSFFooter footer = sheet.getFooter();
    footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages());
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

/**
 * Ralise l'export des donnes de contenu et de la ligne d'en-tte.
 *
 * @param parameters Paramtre de cet export
 * @param workbook Document excel/*w ww  .ja  v  a2 s  . com*/
 * @param sheet Feuille Excel
 * @param forceLandscape Indique si le parametrage force un affichage en paysage
 */
private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet,
        final boolean forceLandscape) {
    // Column width
    final Map<Integer, Double> maxWidthPerColumn = new HashMap<>();
    if (parameters.hasDtObject()) {
        exportObject(parameters, workbook, sheet, maxWidthPerColumn);
    } else {
        exportList(parameters, workbook, sheet, maxWidthPerColumn);
    }
    // On definit la largeur des colonnes:
    double totalWidth = 0;
    int cellIndex;
    for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) {
        cellIndex = entry.getKey();
        final Double maxLength = entry.getValue();
        final int usesMaxLength = Double.valueOf(Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH))
                .intValue();
        sheet.setColumnWidth(cellIndex, usesMaxLength * 256);
        totalWidth += usesMaxLength;
    }
    /**
     * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ?
     */

    // note: il ne semble pas simple de mettre title et author dans les proprits du document
    final String title = parameters.getTitle();
    if (title != null) {
        final HSSFHeader header = sheet.getHeader();
        header.setLeft(title);
    }
    sheet.setHorizontallyCenter(true);
    sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE);
    if (forceLandscape || totalWidth > 85) {
        sheet.getPrintSetup().setLandscape(true);
    }

    // On dfinit le footer
    final HSSFFooter footer = sheet.getFooter();
    footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages());
}

From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java

@Override
public void generateXSL(String user, String startDate, String endDate) {
    try {/*  ww w.  j  av  a2  s.  c o  m*/

        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(wb);
        HSSFSheet sheet = wb.createSheet("Users_Sheet1");

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

        //title row
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(45);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("SMS OUT REPORT");
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

        String[] titles = { "Mobile", "Source Address", "Message", "Time Spent", "Last Update", "User",
                "Status", "No. of SMS" };

        HSSFRow row = sheet.createRow(1);
        row.setHeightInPoints(40);

        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = row.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }

        List<SMSOut> exportSMSOutReport = (List) userSMSOutReport(user, startDate, endDate).get("result");
        int rowNum = 2;

        for (SMSOut anSMS : exportSMSOutReport) {
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(anSMS.getDestinationAddr());
            row.createCell(1).setCellValue(anSMS.getSourceAddr());
            row.createCell(2).setCellValue(anSMS.getMessagePayload());
            row.createCell(3).setCellValue(anSMS.getTimeSubmitted());
            row.createCell(4).setCellValue(anSMS.getTimeProcessed());

            row.createCell(5).setCellValue(anSMS.getUser());
            row.createCell(6).setCellValue(anSMS.getRealStatus());
            row.createCell(7).setCellValue(anSMS.getSmsCount());
            rowNum++;
        }

        sheet.setColumnWidth(0, 20 * 256); //30 characters wide
        sheet.setColumnWidth(1, 15 * 256);
        for (int i = 2; i < 5; i++) {
            sheet.setColumnWidth(i, 20 * 256); //6 characters wide
        }
        sheet.setColumnWidth(5, 10 * 256);

        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 10 * 256); //10 characters wide

        FacesContext context = FacesContext.getCurrentInstance();
        HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse();
        res.setContentType("application/vnd.ms-excel");
        res.setHeader("Content-disposition", "attachment;filename=mydata.xlsx");

        ServletOutputStream out = res.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
        FacesContext.getCurrentInstance().responseComplete();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:kitt.admin.controller.UserController.java

@RequestMapping(value = "/downloadData")
@Authority(role = AuthenticationRole.Service)
@Authority(role = AuthenticationRole.TraderAssistant)
@Authority(role = AuthenticationRole.LegalPersonnel)
@Authority(role = AuthenticationRole.Admin)
@Authority(role = AuthenticationRole.Operation)
public void downloadUserData(String status,
        @RequestParam(value = "securephone", required = false, defaultValue = "") String securephone,
        @RequestParam(value = "clienttype", required = false, defaultValue = "0") int clienttype,
        @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
        @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate,
        HttpServletRequest request, HttpServletResponse response) throws IOException, DocumentException {
    List<Map<String, Object>> users = userMapper.userExport(status, Where.$like$(securephone), clienttype,
            startDate, endDate);/* w  w w.  j  a v a2  s.  c o m*/
    String filename = status + "?";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(filename);
    HSSFRow row = sheet.createRow(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    sheet.setVerticallyCenter(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 1200);
    sheet.setColumnWidth(1, 3600);
    sheet.setColumnWidth(2, 8000);
    sheet.setColumnWidth(3, 4500);
    sheet.setColumnWidth(4, 4500);
    String[] excelHeader = { "??", "", "??", "??",
            "" };
    for (int i = 0; i < excelHeader.length; i++) {
        sheet.autoSizeColumn(i, true);
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(excelHeader[i]);
        cell.setCellStyle(cellStyle);
    }
    for (int i = 0; i < users.size(); i++) {
        Map<String, Object> resultSet = users.get(i);
        sheet.autoSizeColumn(i, true);
        row = sheet.createRow(i + 1);
        row.setRowStyle(cellStyle);
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(String.valueOf(resultSet.get("tradername")).equals("null") ? ""
                : String.valueOf(resultSet.get("tradername")));
        row.createCell(2).setCellValue(String.valueOf(resultSet.get("companyname")));
        row.createCell(3).setCellValue(String.valueOf(resultSet.get("securephone")));
        row.createCell(4).setCellValue(String.valueOf(resultSet.get("verifytime")));
    }
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-download");
    filename += LocalDate.now() + ".xls";
    if (request.getHeader("user-agent").toLowerCase().contains("firefox")) {
        filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
    } else {
        filename = URLEncoder.encode(filename, "UTF-8");
    }
    response.addHeader("Content-Disposition", "attachment; filename=" + filename);
    OutputStream out = response.getOutputStream();
    wb.write(out);
    out.close();
}