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

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

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

Introduction

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

Prototype

void setAlignment(HorizontalAlignment align);

Source Link

Document

set the type of horizontal alignment for the cell

Usage

From source file:jgnash.engine.budget.BudgetResultsExport.java

License:Open Source License

public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {

    Resource rb = Resource.get();

    Workbook wb;//from   w  ww.  j a  v a 2 s  .com

    String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {
        wb = new HSSFWorkbook();
    }

    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    Sheet s = wb.createSheet(model.getBudget().getName());

    // create header cell styles
    CellStyle headerStyle = wb.createCellStyle();

    // create 2 fonts objects
    Font amountFont = wb.createFont();
    Font headerFont = wb.createFont();

    amountFont.setFontHeightInPoints((short) 10);
    amountFont.setColor(IndexedColors.BLACK.getIndex());

    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    DataFormat df = wb.createDataFormat();

    // Set the other cell style and formatting
    headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    headerStyle.setDataFormat(df.getFormat("text"));
    headerStyle.setFont(headerFont);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);

    int row = 0;
    Row r = s.createRow(row);

    // create period headers
    for (int i = 0; i < model.getDescriptorList().size(); i++) {
        Cell c = r.createCell(i * 3 + 1);
        c.setCellValue(
                createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
    }

    {
        int col = model.getDescriptorList().size() * 3 + 1;
        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
    }

    // create results header columns
    row++;
    r = s.createRow(row);

    {
        Cell c = r.createCell(0);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
        c.setCellStyle(headerStyle);

        for (int i = 0; i <= model.getDescriptorList().size(); i++) {
            c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 2);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 3);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
            c.setCellStyle(headerStyle);
        }
    }

    // must sort the accounts, otherwise child structure is not correct
    List<Account> accounts = new ArrayList<>(model.getAccounts());
    Collections.sort(accounts);

    // create account rows
    for (Account account : accounts) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);
        cs.setIndention((short) (model.getDepth(account) * 2));

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(account.getName()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

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

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // add group summary rows
    for (AccountGroup group : model.getAccountGroupList()) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);
        amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
        amountStyle.setBorderTop(CellStyle.BORDER_THIN);
        amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
        amountStyle.setBorderRight(CellStyle.BORDER_THIN);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
        String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(group.toString()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

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

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // force evaluation
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateAll();

    short columnCount = s.getRow(1).getLastCellNum();

    // autosize all of the columns + 10 pixels
    for (int i = 0; i <= columnCount; i++) {
        s.autoSizeColumn(i);
        s.setColumnWidth(i, s.getColumnWidth(i) + 10);
    }

    // Save
    String filename = file.getAbsolutePath();

    if (wb instanceof XSSFWorkbook) {
        filename = FileUtils.stripFileExtension(filename) + ".xlsx";
    } else {
        filename = FileUtils.stripFileExtension(filename) + ".xls";
    }

    try (FileOutputStream out = new FileOutputStream(filename)) {
        wb.write(out);
    } catch (Exception e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

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

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();//w  ww.jav  a  2 s  .c o  m
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

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

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    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());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

/**
 * Cell styles used for formatting the sheets
 *
 * @param wb/*from  www. j  ava  2  s .c o m*/
 * @return Map<String, {@link CellStyle}>
 */
public static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();

    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);

    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);

    return styles;
}

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);/*from w  w w. java  2 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();
}

From source file:kp.servlet.ExportRpt.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from  w w w.ja  v  a 2s .  co m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE,
            "accRole :" + request.getParameter("accRole"));
    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "Unit :" + request.getParameter("unit"));
    ArrayList<MocWfTran> Mocstatus = new ArrayList<>();
    TranDao tdao = new TranDao();
    Mocstatus = tdao.getMocStatusList(request.getParameter("accRole"), request.getParameter("unit"),
            request.getParameter("user"));

    //Developing Metadata
    String rptName = "MOC Status Excel Report";
    ArrayList<String> colLabel = new ArrayList<>();
    colLabel.add("Case Id");
    colLabel.add("Moc NO");
    colLabel.add("Moc Title");
    colLabel.add("Moc Status");
    colLabel.add("Creation Date");
    colLabel.add("Owner's Name");
    colLabel.add("Unit");
    colLabel.add("Plant");
    colLabel.add("Current Stage");
    colLabel.add("Pending At");

    //Starting EXCEL Creating
    //XLS Variable
    XSSFSheet spreadsheet;
    XSSFWorkbook workbook;
    XSSFRow row;
    XSSFCell cell;
    XSSFFont xfont = null;
    XSSFCellStyle xstyle = null;

    //2.Create WorkBook and Sheet
    workbook = new XSSFWorkbook();
    spreadsheet = workbook.createSheet(rptName);

    //set header style
    xfont = workbook.createFont();
    xfont.setFontHeight(11);
    xfont.setFontName("Calibri");
    xfont.setBold(true);

    //Set font into style
    CellStyle borderStyle = workbook.createCellStyle();
    borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
    borderStyle.setFont(xfont);
    xstyle = workbook.createCellStyle();
    xstyle.setFont(xfont);

    //header
    row = spreadsheet.createRow(0);
    cell = row.createCell(0);
    cell.setCellValue(rptName);
    cell.setCellStyle(borderStyle);
    spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLabel.size() - 1));

    //3.Get First Row and Set Headers
    row = spreadsheet.createRow(1);

    for (int i = 0; i < colLabel.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(colLabel.get(i));
        cell.setCellStyle(xstyle);
    }

    //Itrate or Database data and write
    int i = 2;
    for (MocWfTran bean : Mocstatus) {
        row = spreadsheet.createRow(i);
        cell = row.createCell(0);
        cell.setCellValue(bean.getCaseId());
        cell = row.createCell(1);
        cell.setCellValue(bean.getMocNo());
        cell = row.createCell(2);
        cell.setCellValue(bean.getCaseName());
        cell = row.createCell(3);
        cell.setCellValue(bean.getMocStatus());
        cell = row.createCell(4);
        cell.setCellValue(bean.getCrDateString());
        cell = row.createCell(5);
        cell.setCellValue(bean.getCaseOwnerName());
        cell = row.createCell(6);
        cell.setCellValue(bean.getUnitId());
        cell = row.createCell(7);
        cell.setCellValue(bean.getPlantId());
        cell = row.createCell(8);
        cell.setCellValue(bean.getStgNname());
        cell = row.createCell(9);
        cell.setCellValue(bean.getUserNname());
        i++;
    }

    //Export to Excel
    String file_name = "MocStatus";
    String path = getServletContext().getRealPath("/");
    String full_path = path + "/report/" + file_name + ".xlsx";
    //        FileOutputStream out = new FileOutputStream(new File("D://" + file_name + ".xlsx"));
    FileOutputStream out = new FileOutputStream(new File(full_path));
    workbook.write(out);

    //Download code 
    // reads input file from an absolute path
    File downloadFile = new File(full_path);
    OutputStream outStream;
    // obtains ServletContext
    try (FileInputStream inStream = new FileInputStream(downloadFile)) {
        //obtains ServletContext
        ServletContext context = getServletContext();
        // gets MIME type of the file
        String mimeType = context.getMimeType(full_path);
        if (mimeType == null) {
            // set to binary type if MIME mapping not found
            mimeType = "application/octet-stream";
        } // modifies response
        response.setContentType(mimeType);
        response.setContentLength((int) downloadFile.length());
        // forces download
        String headerKey = "Content-Disposition";
        String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName());
        response.setHeader(headerKey, headerValue);
        // obtains response's output stream
        outStream = response.getOutputStream();
        byte[] buffer = new byte[4096];
        int bytesRead = -1;
        while ((bytesRead = inStream.read(buffer)) != -1) {
            outStream.write(buffer, 0, bytesRead);
        }
    }
    outStream.close();
    //        response.sendRedirect("mocstatus.jsp");
}

From source file:Logic.RStoXL.java

public void genXLS(ResultSet rs, String Rpt_name, String path) {
    try {/*from   w  w w.j a va2  s . c  o m*/
        //RS METE DATA
        ResultSetMetaData rsmd = rs.getMetaData();
        int col_count = rsmd.getColumnCount();
        ArrayList<String> col_name = new ArrayList<String>();
        for (int i = 1; i <= col_count; i++) {
            col_name.add(rsmd.getColumnLabel(i));
        }

        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet(Rpt_name);

        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);

        //Set font into style
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);
        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue(Rpt_name);
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);

        for (int i = 0; i < col_count; i++) {
            cell = row.createCell(i);
            cell.setCellValue(col_name.get(i));
            cell.setCellStyle(xstyle);
        }

        //Itrate or Database data and write
        int i = 2;
        while (rs.next()) {
            row = spreadsheet.createRow(i);
            for (int j = 1; j <= col_count; j++) {
                cell = row.createCell(j - 1);
                cell.setCellValue(rs.getString(j));
            }
            i++;
        }

        //Export to Excel
        // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx"));
        FileOutputStream out = new FileOutputStream(new File(path));
        workbook.write(out);

        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "");
    } catch (SQLException ex) {
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    }
}

From source file:Logic.Xls.java

public void genXLS(ResultSet rs, String Rpt_name, String path) {
    try {//  www  . j ava  2s  . c  om
        //RS METE DATA
        ResultSetMetaData rsmd = rs.getMetaData();
        int col_count = rsmd.getColumnCount();
        ArrayList<String> col_name = new ArrayList<>();
        for (int i = 1; i <= col_count; i++) {
            col_name.add(rsmd.getColumnLabel(i));
        }

        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet(Rpt_name);

        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);

        //Set font into style
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);
        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue(Rpt_name);
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);

        for (int i = 0; i < col_count; i++) {
            cell = row.createCell(i);
            cell.setCellValue(col_name.get(i));
            cell.setCellStyle(xstyle);
        }

        //Itrate or Database data and write
        int i = 2;
        while (rs.next()) {
            row = spreadsheet.createRow(i);
            for (int j = 1; j <= col_count; j++) {
                cell = row.createCell(j - 1);
                cell.setCellValue(rs.getString(j));
            }
            i++;
        }

        //Export to Excel
        // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx"));
        FileOutputStream out = new FileOutputStream(new File(path));
        workbook.write(out);

        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "");
    } catch (Exception ex) {
        Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "Exception : " + ex);
    }
}

From source file:main.ExcelUtils.java

/**
 * Devuelve el CellStyle indicado para las celdas de cabecera de la tabla
 * @return /*from w  w  w.  ja v  a  2  s.c  om*/
 */
public CellStyle GetTitleStyle() {
    CellStyle title = wb.createCellStyle();
    Font fuenteTitle = wb.createFont();
    fuenteTitle.setBold(true);
    title.setFont(fuenteTitle);
    title.setAlignment(CellStyle.ALIGN_CENTER);
    title.setBorderBottom(CellStyle.BORDER_MEDIUM);

    return title;
}

From source file:main.resources.FileExcel.java

public static void generaXlsx() throws IOException {

    //nombre del archivo de Excel
    String nombreArchivo = "quincena.xlsx";

    String nombreHoja1 = "fecha";//nombre de la hoja1

    Workbook libroTrabajo = new XSSFWorkbook();
    Sheet hoja1 = libroTrabajo.createSheet(nombreHoja1);

    Row row = hoja1.createRow((short) 1);
    //row.setHeightInPoints(10); //alto de celda

    Cell cell = row.createCell((short) 1);
    Cell cell1 = row.createCell((short) 1);
    cell.setCellValue("Asistencia fecha xxxxxx");
    CellStyle cellStyle = libroTrabajo.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    //cellStyle.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
    //cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
    cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());

    hoja1.addMergedRegion(new CellRangeAddress(1, // first row (0-based) primera fila
            1, //lasto row (0-based) ultima fila
            1, //first column (0-based) numero de columna inicial
            5 //last column (0-based) numero de columna final
    ));//from ww  w .  j  ava 2s.  c  om
    cell.setCellStyle(cellStyle);
    cell1.setCellStyle(cellStyle);

    //escribir este libro en un OutputStream.
    try (FileOutputStream fileOut = new FileOutputStream(nombreArchivo)) {
        //escribir este libro en un OutputStream.
        libroTrabajo.write(fileOut);
        fileOut.flush();
    }
}

From source file:main.resources.FileExcel.java

public void excelDia() throws FileNotFoundException, IOException {
    String nombreFile = "quincena.xlsx";
    String nombreHoja = "dia x mes x ao x";

    Workbook libro = new XSSFWorkbook();
    Sheet hoja = libro.createSheet(nombreHoja);

    Font negrita = libro.createFont();
    negrita.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle estilo = libro.createCellStyle();
    estilo.setAlignment(CellStyle.ALIGN_CENTER);
    estilo.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    estilo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    estilo.setBorderBottom(CellStyle.BORDER_THIN);
    estilo.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderLeft(CellStyle.BORDER_THIN);
    estilo.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderRight(CellStyle.BORDER_THIN);
    estilo.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderTop(CellStyle.BORDER_THIN);
    estilo.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setFont(negrita);//ww  w. j av  a 2s .  c om

    CellStyle bordes = libro.createCellStyle();
    bordes.setAlignment(CellStyle.ALIGN_LEFT);
    bordes.setBorderBottom(CellStyle.BORDER_THIN);
    bordes.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderLeft(CellStyle.BORDER_THIN);
    bordes.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderRight(CellStyle.BORDER_THIN);
    bordes.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderTop(CellStyle.BORDER_THIN);
    bordes.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());

    CellStyle estilo2 = libro.createCellStyle();
    estilo2.setAlignment(CellStyle.ALIGN_CENTER);
    estilo2.setBorderBottom(CellStyle.BORDER_THIN);
    estilo2.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderLeft(CellStyle.BORDER_THIN);
    estilo2.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderRight(CellStyle.BORDER_THIN);
    estilo2.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderTop(CellStyle.BORDER_THIN);
    estilo2.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setAlignment(CellStyle.ALIGN_CENTER);
    estilo2.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    estilo2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    estilo2.setFont(negrita);

    CellStyle borderBot = libro.createCellStyle();
    borderBot.setBorderBottom(CellStyle.BORDER_THIN);
    borderBot.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());

    Row row = hoja.createRow(1);

    //Row row1 = hoja.createRow(2);

    //empleados faltas
    Appi app = new Appi();
    Date Fecha = new Date();
    DateFormat formato = new SimpleDateFormat("YYYY-MM-dd");
    String fechaActual = formato.format(Fecha);
    ArrayList<Empleado> faltas = app.faltas(fechaActual);//obtengo listado de empleados
    String grupoBandera = "";
    String maestro = "";
    int pRow = 3;
    if (!faltas.isEmpty()) {
        Cell celda = row.createCell(3);
        Cell celda2 = row.createCell(4);
        Cell celda3 = row.createCell(5);
        Cell celda4 = row.createCell(6);
        Cell celda5 = row.createCell(7);
        combinarceldas(hoja, 1, 1, 3, 7);
        celda.setCellValue("Asistencia " + fechaActual);
        celda.setCellStyle(estilo);
        celda2.setCellStyle(estilo);
        celda3.setCellStyle(estilo);
        celda4.setCellStyle(estilo);
        celda5.setCellStyle(estilo);
        grupoBandera = faltas.get(0).getGrupo();

        //encabezados
        Row row2 = hoja.createRow(pRow);
        Cell cell = row2.createCell(1);
        cell.setCellValue("Nficha");
        cell.setCellStyle(estilo2);

        Cell cell1 = row2.createCell(2);
        cell1.setCellValue("1er Apellido");
        cell1.setCellStyle(estilo2);

        Cell cell2 = row2.createCell(3);
        cell2.setCellValue("2do Apellido");
        cell2.setCellStyle(estilo2);

        Cell cell3 = row2.createCell(4);
        cell3.setCellValue("1er Nombre");
        cell3.setCellStyle(estilo2);

        Cell cell4 = row2.createCell(5);
        cell4.setCellValue("2do Nombre");
        cell4.setCellStyle(estilo2);

        Cell cell5 = row2.createCell(6);
        cell5.setCellValue("Identificacion");
        cell5.setCellStyle(estilo2);

        Cell cell6 = row2.createCell(7);
        cell6.setCellValue("Da");
        cell6.setCellStyle(estilo2);

        Cell cell7 = row2.createCell(8);
        cell7.setCellValue("Cargo");
        cell7.setCellStyle(estilo2);

        Cell cell8 = row2.createCell(9);
        cell8.setCellValue("Grupo");
        cell8.setCellStyle(estilo2);
    }
    Empleado emp = null;
    for (int i = 0; i < faltas.size(); i++) {

        //datos
        emp = (Empleado) faltas.get(i);
        Grupo grupo = app.grupo(emp.getGrupo());

        if (!grupoBandera.equals(emp.getGrupo())) {
            grupoBandera = emp.getGrupo();
            pRow = pRow + 2;
            //frima maestro
            Row row4 = hoja.createRow(pRow);
            Cell celda9 = row4.createCell(1);
            combinarceldas(hoja, pRow, pRow, 1, 3);
            celda9.setCellValue("Maestro Grupo:");
            celda9.setCellStyle(estilo);
            Cell celda10 = row4.createCell(2);
            celda10.setCellStyle(bordes);
            Cell celda11 = row4.createCell(3);
            celda11.setCellStyle(bordes);
            Cell celda12 = row4.createCell(4);
            celda12.setCellStyle(borderBot);
            Cell celda13 = row4.createCell(5);
            celda13.setCellStyle(borderBot);
            Cell celda14 = row4.createCell(6);
            celda14.setCellStyle(borderBot);
            Cell celda15 = row4.createCell(7);
            celda15.setCellStyle(borderBot);
            Cell celda16 = row4.createCell(8);
            celda16.setCellStyle(borderBot);

            pRow++;

            Row row6 = hoja.createRow(pRow);
            Cell celda64 = row6.createCell(4);
            combinarceldas(hoja, pRow, pRow, 4, 8);
            celda64.setCellValue(maestro);

            pRow = pRow + 2;
            //encabexzados
            Row row2 = hoja.createRow(pRow);
            Cell cell = row2.createCell(1);
            cell.setCellValue("Nficha");
            cell.setCellStyle(estilo2);

            Cell cell1 = row2.createCell(2);
            cell1.setCellValue("1er Apellido");
            cell1.setCellStyle(estilo2);

            Cell cell2 = row2.createCell(3);
            cell2.setCellValue("2do Apellido");
            cell2.setCellStyle(estilo2);

            Cell cell3 = row2.createCell(4);
            cell3.setCellValue("1er Nombre");
            cell3.setCellStyle(estilo2);

            Cell cell4 = row2.createCell(5);
            cell4.setCellValue("2do Nombre");
            cell4.setCellStyle(estilo2);

            Cell cell5 = row2.createCell(6);
            cell5.setCellValue("Identificacion");
            cell5.setCellStyle(estilo2);

            Cell cell6 = row2.createCell(7);
            cell6.setCellValue("Da");
            cell6.setCellStyle(estilo2);

            Cell cell7 = row2.createCell(8);
            cell7.setCellValue("Cargo");
            cell7.setCellStyle(estilo2);

            Cell cell8 = row2.createCell(9);
            cell8.setCellValue("Grupo");
            cell8.setCellStyle(estilo2);
        }

        Row row5 = hoja.createRow(pRow + 1);
        Cell celda51 = row5.createCell(1);
        celda51.setCellStyle(bordes);
        celda51.setCellValue(emp.getnFicha());
        Cell celda52 = row5.createCell(2);
        celda52.setCellStyle(bordes);
        celda52.setCellValue(emp.getpApellido());
        Cell celda53 = row5.createCell(3);
        celda53.setCellStyle(bordes);
        celda53.setCellValue(emp.getsApellido());
        Cell celda54 = row5.createCell(4);
        celda54.setCellStyle(bordes);
        celda54.setCellValue(emp.getpNombre());
        Cell celda55 = row5.createCell(5);
        celda55.setCellStyle(bordes);
        celda55.setCellValue(emp.getsNombre());
        Cell celda56 = row5.createCell(6);
        celda56.setCellStyle(bordes);
        celda56.setCellValue(emp.getCedula());
        Cell celda57 = row5.createCell(7);
        celda57.setCellStyle(bordes);
        celda57.setCellValue(fechaActual);
        Cell celda58 = row5.createCell(8);
        celda58.setCellStyle(bordes);
        celda58.setCellValue(emp.getCargo());
        Cell celda59 = row5.createCell(9);
        celda59.setCellStyle(bordes);
        celda59.setCellValue(grupo.getNombre());
        pRow++;
        Empleado supervisor = app.empleado(grupo.getSupervisor());
        if (supervisor != null) {
            maestro = supervisor.getpNombre() + " " + supervisor.getsNombre() + " " + supervisor.getpApellido()
                    + " " + supervisor.getsApellido();
        } else {
            maestro = String.valueOf(grupo.getSupervisor());
        }

        //
        //String cedula = (String) e;
        //Empleado emp = app.empleado(cedula);

        System.out.println(emp.getCedula());

    }
    if (emp != null) {
        pRow = pRow + 2;
        //frima maestro
        Row row4 = hoja.createRow(pRow);
        Cell celda9 = row4.createCell(1);
        combinarceldas(hoja, pRow, pRow, 1, 3);
        celda9.setCellValue("Maestro Grupo:");
        celda9.setCellStyle(estilo);
        Cell celda10 = row4.createCell(2);
        celda10.setCellStyle(bordes);
        Cell celda11 = row4.createCell(3);
        celda11.setCellStyle(bordes);
        Cell celda12 = row4.createCell(4);
        celda12.setCellStyle(borderBot);
        Cell celda13 = row4.createCell(5);
        celda13.setCellStyle(borderBot);
        Cell celda14 = row4.createCell(6);
        celda14.setCellStyle(borderBot);
        Cell celda15 = row4.createCell(7);
        celda15.setCellStyle(borderBot);
        Cell celda16 = row4.createCell(8);
        celda16.setCellStyle(borderBot);

        pRow++;

        Row row6 = hoja.createRow(pRow);
        Cell celda64 = row6.createCell(4);
        combinarceldas(hoja, pRow, pRow, 4, 8);
        celda64.setCellValue(maestro);
    }

    //enmcabezados

    //Row row3 = hoja.createRow(1);
    // debe ejcutarse un loop de acuerdoa consaulta
    //datos

    //datos responsable firma

    //CellS
    //celda64.setCellStyle();

    try (FileOutputStream fileOut = new FileOutputStream(nombreFile)) {
        //escribir este libro en un OutputStream.
        libro.write(fileOut);
        fileOut.flush();
    }

}