List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setBorderBottom
@Override public void setBorderBottom(BorderStyle border)
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from w ww .j a v a 2s .c om*/ dbConn conn = new dbConn(); ouputyear = outputMonth = ""; if (session.getAttribute("PepfarYear") != null) { year = Integer.parseInt(session.getAttribute("PepfarYear").toString()); quarter = Integer.parseInt(session.getAttribute("period").toString()); // year=2015; // quarter=2; System.out.println("dates are : " + year + " quarter : " + quarter); if (quarter == 4) { reportYear = year - 1; startmonth = "10"; endMonth = "12"; prevReportYear = reportYear; prevReportMonth = "09"; } else { reportYear = year; if (quarter == 1) { startmonth = "01"; endMonth = "03"; prevReportYear = reportYear - 1; prevReportMonth = "12"; } if (quarter == 2) { startmonth = "04"; endMonth = "06"; prevReportYear = reportYear; prevReportMonth = "03"; } if (quarter == 3) { startmonth = "07"; endMonth = "09"; prevReportYear = reportYear; prevReportMonth = "06"; } } startDate = "" + startmonth + "/01/" + reportYear + ""; endDate = "" + endMonth + "/31/" + reportYear + ""; startDate1 = "" + reportYear + "-" + startmonth + "-01"; endDate1 = "" + reportYear + "-" + endMonth + "-31"; endYearMonth = prevReportYear + "" + prevReportMonth; position = 1; // ouputyear=""+reportYear; if (quarter == 1) { outputMonth = "Jan_March_" + reportYear; } if (quarter == 2) { outputMonth = "Apr_Jun_" + reportYear; } if (quarter == 3) { outputMonth = "July_Sept_" + reportYear; } if (quarter == 4) { outputMonth = "Oct_Dec_" + reportYear; } String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " " + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate," + " CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH," + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName," + "CASE " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' " + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + " CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname," + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + " FROM personal_information " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '" + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "") + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + " HAVING YEARMONTH<=" + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable "; System.out.println(query); conn.rs = conn.st.executeQuery(query); while (conn.rs.next()) { countyName = conn.rs.getString(5); partnerName = conn.rs.getString(6); districtName = conn.rs.getString(7); reportDate = conn.rs.getString(2); ageBracket = conn.rs.getString(8); gender = conn.rs.getString(9); fname = conn.rs.getString(10); mname = conn.rs.getString(11); lname = conn.rs.getString(12); if (conn.rs.getString(13) != null) { groupName = conn.rs.getString(13); } else { groupName = "Individual"; } if (conn.rs.getString(14) != null) { groupName = conn.rs.getString(14); } else { dic_name = "NO DIC"; } if (mname.equals(lname)) { mname = ""; } fullName = fname + " " + mname + " " + lname; String dateSplit[] = reportDate.split("-"); if (dateSplit[1].equals("01")) { month = "Jan"; } if (dateSplit[1].equals("02")) { month = "Feb"; } if (dateSplit[1].equals("03")) { month = "Mar"; } if (dateSplit[1].equals("04")) { month = "Apr"; } if (dateSplit[1].equals("05")) { month = "May"; } if (dateSplit[1].equals("06")) { month = "Jun"; } if (dateSplit[1].equals("07")) { month = "Jul"; } if (dateSplit[1].equals("08")) { month = "Aug"; } if (dateSplit[1].equals("09")) { month = "Sept"; } if (dateSplit[1].equals("10")) { month = "Oct"; } if (dateSplit[1].equals("11")) { month = "Nov"; } if (dateSplit[1].equals("12")) { month = "Dec"; } year2 = dateSplit[0]; String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + "," + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (data.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(data[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:ru.jeene.zapretparser.controller.XLSXReportController.java
public void WriteReport(FullReport rep, String timestamp_csv) { try (FileInputStream inp = new FileInputStream(shab_name)) { XSSFWorkbook wb = new XSSFWorkbook(inp); // Declare XSSF WorkBook XSSFSheet sheet = wb.getSheet(" "); XSSFCellStyle cs1 = wb.createCellStyle(); //cs1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cs1.setBorderBottom(XSSFCellStyle.BORDER_THIN); cs1.setBorderTop(XSSFCellStyle.BORDER_THIN); cs1.setBorderRight(XSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(XSSFCellStyle.BORDER_THIN); cs1.setAlignment(XSSFCellStyle.ALIGN_CENTER); cs1.setWrapText(true);// w w w .ja v a2 s. c o m cs1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); XSSFFont f = wb.createFont(); f.setBold(false); f.setFontName("Times New Roman"); f.setFontHeightInPoints((short) 14); cs1.setFont(f); int cnt = 0; XSSFRow row; XSSFCell cell; for (Model_FullReport m : rep.getList()) { int cnt_cell = 0; row = sheet.getRow(t1_start - 1 + cnt); if (row == null) { row = sheet.createRow(t1_start - 1 + cnt); } //? URL cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getUrl()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getOrg()); cell.setCellStyle(cs1); cnt_cell++; //? cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDoc()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getElement().getDate()); cell.setCellStyle(cs1); cnt_cell++; // cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().name()); cell.setCellStyle(cs1); cnt_cell++; // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(m.getResult().getDesc()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(m.getResult().getCode()); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HashMap<ResponseResult, Model_NumberReport> map = rep.reportCountBytype(); // ? sheet = wb.getSheet(""); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTP map = rep.reportCountBytypeHTTP(); // ? sheet = wb.getSheet(" HTTP"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); // HTTPS map = rep.reportCountBytypeHTTPS(); // ? sheet = wb.getSheet(" HTTPS"); // cnt = 0; for (Map.Entry<ResponseResult, Model_NumberReport> entry : map.entrySet()) { ResponseResult key = entry.getKey(); Model_NumberReport value = entry.getValue(); int cnt_cell = 0; row = sheet.getRow(t0_start - 1 + cnt); if (row == null) { row = sheet.createRow(t0_start - 1 + cnt); } /*// cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.name()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(key.getDesc()); cell.setCellStyle(cs1); cnt_cell++; /*// () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(key.getCode()); cell.setCellStyle(cs1); cnt_cell++;*/ // (?) cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(value.getNumber()); cell.setCellStyle(cs1); cnt_cell++; // () cell = row.getCell(cnt_cell); if (cell == null) { cell = row.createCell(cnt_cell); } cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(FormatUtils.FormatDoubleD(value.getPercent())); cell.setCellStyle(cs1); cnt_cell++; cnt++; } // row = sheet.getRow(0); cell = row.getCell(0); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellValue(MAIN_ZAG_TEMPL + timestamp_csv); //? String tmp_out = StringUtils.replaceAll(report_name, "!dt!", DateUtils.DateToString(new Date(System.currentTimeMillis()), "ddMMyyyy_Hms")); try (FileOutputStream out = new FileOutputStream(tmp_out)) { wb.write(out); logger.info("Report file " + tmp_out + " created"); } } catch (Exception ex) { logger.error(ex); } }
From source file:tr.org.liderahenk.liderconsole.core.utils.SWTResourceManager.java
License:Open Source License
private static XSSFWorkbook createWorkbookFromTable(TableViewer tableViewer, String sheetName) { // Create workbook & sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName == null ? "Sheet1" : sheetName); // Shade the background of the header row XSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setAlignment(HorizontalAlignment.CENTER); // Add header row Table table = tableViewer.getTable(); TableColumn[] columns = table.getColumns(); int rowIndex = 0; int cellIndex = 0; XSSFRow header = sheet.createRow((short) rowIndex++); for (TableColumn column : columns) { XSSFCell cell = header.createCell(cellIndex++); cell.setCellValue(column.getText()); cell.setCellStyle(headerStyle);//from w w w. j a v a 2 s.c om } // Add data rows TableItem[] items = tableViewer.getTable().getItems(); for (TableItem item : items) { // create a new row XSSFRow row = sheet.createRow((short) rowIndex++); cellIndex = 0; for (int i = 0; i < columns.length; i++) { // Create a new cell XSSFCell cell = row.createCell(cellIndex++); String text = item.getText(i); // Set the horizontal alignment (default to RIGHT) XSSFCellStyle cellStyle = wb.createCellStyle(); if (LiderCoreUtils.isInteger(text)) { cellStyle.setAlignment(HorizontalAlignment.RIGHT); } else if (LiderCoreUtils.isValidDate(text, ConfigProvider.getInstance().get(LiderConstants.CONFIG.DATE_FORMAT))) { cellStyle.setAlignment(HorizontalAlignment.CENTER); } else { cellStyle.setAlignment(HorizontalAlignment.LEFT); } cell.setCellStyle(cellStyle); // Set the cell's value cell.setCellValue(text); } } // Auto-fit the columns for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn((short) i); } return wb; }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelStyleGenerator.java
License:Apache License
protected void applyBorders(Style style, XSSFCellStyle cellStyle) { if (style.isBorderWidthSet()) { short width = (short) style.getProperty(CssIntegerProperty.BORDER_WIDTH); Color color = style.getProperty(CssColorProperty.BORDER_COLOR) != null ? style.getProperty(CssColorProperty.BORDER_COLOR) : Color.BLACK;/*ww w .ja v a 2s. c o m*/ cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderBottom(width); cellStyle.setBottomBorderColor(new XSSFColor(color)); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderTop(width); cellStyle.setTopBorderColor(new XSSFColor(color)); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderLeft(width); cellStyle.setLeftBorderColor(new XSSFColor(color)); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderRight(width); cellStyle.setRightBorderColor(new XSSFColor(color)); } }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerXUtils.java
License:Open Source License
@Override public void applyBorderStyle(Workbook workbook, CellStyle style, BorderSide side, CSSValue colour, CSSValue borderStyle, CSSValue width) { if ((colour != null) || (borderStyle != null) || (width != null)) { String colourString = colour == null ? "rgb(0,0,0)" : colour.getCssText(); String borderStyleString = borderStyle == null ? "solid" : borderStyle.getCssText(); String widthString = width == null ? "medium" : width.getCssText(); if (style instanceof XSSFCellStyle) { XSSFCellStyle xStyle = (XSSFCellStyle) style; BorderStyle xBorderStyle = poiBorderStyleFromBirt(borderStyleString, widthString); XSSFColor xBorderColour = getXColour(colourString); if (xBorderStyle != BorderStyle.NONE) { switch (side) { case TOP: xStyle.setBorderTop(xBorderStyle); xStyle.setTopBorderColor(xBorderColour); // log.debug( "Top border: " + xStyle.getBorderTop() + " / " + xStyle.getTopBorderXSSFColor().getARGBHex() ); break; case LEFT: xStyle.setBorderLeft(xBorderStyle); xStyle.setLeftBorderColor(xBorderColour); // log.debug( "Left border: " + xStyle.getBorderLeft() + " / " + xStyle.getLeftBorderXSSFColor().getARGBHex() ); break; case RIGHT: xStyle.setBorderRight(xBorderStyle); xStyle.setRightBorderColor(xBorderColour); // log.debug( "Right border: " + xStyle.getBorderRight() + " / " + xStyle.getRightBorderXSSFColor().getARGBHex() ); break; case BOTTOM: xStyle.setBorderBottom(xBorderStyle); xStyle.setBottomBorderColor(xBorderColour); // log.debug( "Bottom border: " + xStyle.getBorderBottom() + " / " + xStyle.getBottomBorderXSSFColor().getARGBHex() ); break; }//from ww w. jav a2s.co m } } } }
From source file:uk.gov.ofwat.RefTest.java
License:Open Source License
public void writeXLS() throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); // create a new sheet Sheet s = wb.createSheet();//from w w w . j a v a 2s . co m // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 2 cell styles XSSFCellStyle cs = wb.createCellStyle(); XSSFCellStyle cs2 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); // Set font 1 to 12 point type, blue and bold f.setFontHeightInPoints((short) 12); f.setColor(IndexedColors.RED.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set font 2 to 10 point type, red and bold f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.RED.getIndex()); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set cell style and formatting cs.setFont(f); cs.setDataFormat(df.getFormat("#,##0.0")); // Set the other cell style and formatting cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); cs2.setFont(f2); // Define a few rows for (int rownum = 0; rownum < 30; rownum++) { r = s.createRow(rownum); for (int cellnum = 0; cellnum < 10; cellnum += 2) { c = r.createCell(cellnum); Cell c2 = r.createCell(cellnum + 1); c.setCellValue((double) rownum + (cellnum / 10)); c2.setCellValue(creationHelper.createRichTextString("Hello! " + cellnum)); } } File file = new File("d:\\out.xls"); FileOutputStream fos = new FileOutputStream(file); wb.write(fos); // fos.write(wb.getBytes()); // fos.flush(); // fos.close(); }
From source file:vd10_workbook.AbilityManagement.java
public static void setThickBorder(XSSFCell cell, XSSFWorkbook workbook) { XSSFCellStyle border = workbook.createCellStyle(); border.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); border.setBottomBorderColor(IndexedColors.BLACK.getIndex()); border.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); border.setRightBorderColor(IndexedColors.BLACK.getIndex()); border.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); border.setTopBorderColor(IndexedColors.BLACK.getIndex()); border.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); border.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(border);/* w ww . j a v a 2s . c om*/ }
From source file:vd10_workbook.AbilityManagement.java
public static void setThinBorder(XSSFCell cell, XSSFWorkbook workbook) { XSSFCellStyle border = workbook.createCellStyle(); border.setBorderBottom(XSSFCellStyle.BORDER_THIN); border.setRightBorderColor(IndexedColors.BLACK.getIndex()); border.setBorderLeft(XSSFCellStyle.BORDER_THIN); border.setTopBorderColor(IndexedColors.BLACK.getIndex()); border.setBorderRight(XSSFCellStyle.BORDER_THIN); border.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cell.setCellStyle(border);// w w w . ja v a 2s . c o m }
From source file:vd7_cellstyle.CellStyle.java
public static void main(String[] args) throws FileNotFoundException, IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("cellstyle"); XSSFRow row = spreadsheet.createRow((short) 1); //row 2 in excel //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 800); XSSFCell cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("test of merging"); //MEARGING CELLS //this statement for merging cells spreadsheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 4 //last column (0-based) ));// www . j a v a 2 s . c o m //CELL Alignment row = spreadsheet.createRow(5); //row 6 (in excel) cell = (XSSFCell) row.createCell(0); row.setHeight((short) 800); // Top Left alignment XSSFCellStyle style1 = workbook.createCellStyle(); spreadsheet.setColumnWidth(0, 8000); style1.setAlignment(XSSFCellStyle.ALIGN_LEFT); style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cell.setCellValue("Top Left"); //apply the style cell.setCellStyle(style1); row = spreadsheet.createRow(6);//row 7 in excel cell = (XSSFCell) row.createCell(1); row.setHeight((short) 800); // Center Align Cell Contents XSSFCellStyle style2 = workbook.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellValue("Center Aligned"); //apply the style cell.setCellStyle(style2); row = spreadsheet.createRow(7); //row 8 in excel cell = (XSSFCell) row.createCell(2); row.setHeight((short) 800); // Bottom Right alignment XSSFCellStyle style3 = workbook.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM); cell.setCellValue("Bottom Right"); //apply the style cell.setCellStyle(style3); row = spreadsheet.createRow(8);//row 9 in excel cell = (XSSFCell) row.createCell(3); // Justified Alignment (cn ?u trong ) XSSFCellStyle style4 = workbook.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); cell.setCellValue("Contents are Justified in Alignment"); cell.setCellStyle(style4); //CELL BORDER row = spreadsheet.createRow((short) 9); //row 10 in excel row.setHeight((short) 800); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("BORDER"); XSSFCellStyle style5 = workbook.createCellStyle(); //set bottom border which is thick line style5.setBorderBottom(XSSFCellStyle.BORDER_THICK); //set color of bottom border style5.setBottomBorderColor(IndexedColors.BLUE.getIndex()); style5.setBorderLeft(XSSFCellStyle.BORDER_DOUBLE); style5.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style5.setBorderRight(XSSFCellStyle.BORDER_HAIR); style5.setRightBorderColor(IndexedColors.RED.getIndex()); style5.setBorderTop(XSSFCellStyle.BIG_SPOTS); style5.setTopBorderColor(IndexedColors.CORAL.getIndex()); cell.setCellStyle(style5); //Fill Colors //background color row = spreadsheet.createRow((short) 10); cell = (XSSFCell) row.createCell((short) 1); XSSFCellStyle style6 = workbook.createCellStyle(); style6.setFillBackgroundColor(HSSFColor.LEMON_CHIFFON.index); style6.setFillPattern(XSSFCellStyle.LESS_DOTS); spreadsheet.setColumnWidth(1, 8000); cell.setCellValue("FILL BACKGROUNG/FILL PATTERN"); cell.setCellStyle(style6); FileOutputStream out = new FileOutputStream(new File("src\\vd7_cellstyle\\cellstyle.xlsx")); workbook.write(out); out.close(); System.out.println("cellstyle.xlsx written successfully"); }