List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from ww w. ja va 2 s .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:se.inera.intyg.rehabstod.service.export.xlsx.XlsxExportServiceImpl.java
License:Open Source License
private void addDataRows(XSSFSheet sheet, int rowIndex, List<SjukfallEnhet> sjukfallList, Urval urval, boolean showPatientId, boolean showSrs) { for (int a = 0; a < sjukfallList.size(); a++) { XSSFRow row = sheet.createRow(rowIndex + a); SjukfallEnhet sf = sjukfallList.get(a); int colIndex = 0; createDataCell(row, colIndex++, Integer.toString(a + 1)); if (showPatientId) { createRichTextDataCell(row, colIndex++, buildPersonnummerRichText(sf.getPatient())); }// w ww.j a v a 2s . c o m createDataCell(row, colIndex++, Integer.toString(sf.getPatient().getAlder())); if (showPatientId) { createDataCell(row, colIndex++, sf.getPatient().getNamn()); } createDataCell(row, colIndex++, sf.getPatient().getKon().getDescription()); createDataCell(row, colIndex++, getCompoundDiagnoseText(sf)); createDataCell(row, colIndex++, YearMonthDateFormatter.print(sf.getStart())); createDataCell(row, colIndex++, YearMonthDateFormatter.print(sf.getSlut())); createDataCell(row, colIndex++, String.format(FORMAT_ANTAL_DAGAR, sf.getDagar())); createDataCell(row, colIndex++, Integer.toString(sf.getIntyg())); createRichTextDataCell(row, colIndex++, buildGraderRichText(sf)); if (urval != Urval.ISSUED_BY_ME) { createDataCell(row, colIndex++, sf.getLakare().getNamn()); } if (showSrs) { createDataCell(row, colIndex++, getRiskKategoriDesc(sf.getRiskSignal())); } } for (int a = 0; a < headers.length; a++) { sheet.autoSizeColumn(a); } // Makes sure the "namn" column isn't excessively wide due to the filter. sheet.setColumnWidth(2, 7000); }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//from w ww. ja va2s. c o m continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }
From source file:vd10_workbook.AbilityManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("kha_nang"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Kh nng lm vic"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) );/*from w w w . j a va 2 s . co m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border this.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //ID_NHAN_VIEN sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("ID Nhn vin"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //ID_NGOAI_NGU sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("ID Ngoi ng"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); this.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); this.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getEmployeeID()); cell = (XSSFCell) row.createCell((short) 2); this.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getLanguageID()); } }
From source file:vd10_workbook.AssignedVoteManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("phieu_phan_cong"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Phiu phn cng"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 4 //last column (0-based) );/* w w w. ja v a2 s. co m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Ngy bt u sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Ngy bt u"); AbilityManagement.setThickBorder(cell, workbook); //S ngy sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("S ngy"); AbilityManagement.setThickBorder(cell, workbook); //ID nhn vin sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("ID nhn vin"); AbilityManagement.setThickBorder(cell, workbook); //ID loi cng vic sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("ID loi cng vic"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getStartDate().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getNumberOfdate()); cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getEmployeeID()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getWorkTypeID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Tng"); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula("SUM(C" + (startRow + 1 + 2) + ":C" + (startRow + 2 + this.list.size()) + ")"); AbilityManagement.setThickBorder(cell, workbook); }
From source file:vd10_workbook.Company.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("cong_ty"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Thng tin cng ty"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) );//from w ww . j ava2 s . c o m sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border // AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn cng ty sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Tn"); AbilityManagement.setThickBorder(cell, workbook); //?in thoi sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("?in thoi"); AbilityManagement.setThickBorder(cell, workbook); //?a ch sheet.setColumnWidth(3, 10000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("?a ch"); AbilityManagement.setThickBorder(cell, workbook); //Tui ti thiu sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("Tui ti thiu"); AbilityManagement.setThickBorder(cell, workbook); //Tui ti a sheet.setColumnWidth(5, 5000); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue("Tui ti a"); AbilityManagement.setThickBorder(cell, workbook); //== fill out the information ==// row = sheet.createRow((short) startRow + 2); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(1); //== content ==// //Tn cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.name); //?in thoi cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.phoneNumber); //?a ch cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.address); //Tui ti thiu cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.minAge); //Tui ti a cell = (XSSFCell) row.createCell((short) 5); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.maxAge); }
From source file:vd10_workbook.DepartmentManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("don_vi"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("?n v"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) );//from w w w . j a va2s. c o m sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn n v sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Tn n v"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); } }
From source file:vd10_workbook.EmployeeManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("ds_nhan_vien"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Danh sch nhn vin"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 7 //last column (0-based) );// ww w . j a v a 2 s. co m sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //H? tn sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("H? tn"); AbilityManagement.setThickBorder(cell, workbook); //Gii tnh sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("Gii tnh"); AbilityManagement.setThickBorder(cell, workbook); //Ngy sinh sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("Ngy sinh"); AbilityManagement.setThickBorder(cell, workbook); //CMND sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("CMND"); AbilityManagement.setThickBorder(cell, workbook); //Mc lng sheet.setColumnWidth(5, 5000); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue("Mc lng"); AbilityManagement.setThickBorder(cell, workbook); //?a ch sheet.setColumnWidth(6, 10000); cell = (XSSFCell) row.createCell((short) 6); cell.setCellValue("?a ch"); AbilityManagement.setThickBorder(cell, workbook); //?n v sheet.setColumnWidth(7, 2000); cell = (XSSFCell) row.createCell((short) 7); cell.setCellValue("?n v"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); if (this.list.get(i).getSex() == 1) { cell.setCellValue("Nam"); } else { cell.setCellValue("N"); } cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDateOfbirth().toString()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getIdentityNumber().toString()); cell = (XSSFCell) row.createCell((short) 5); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getSalary()); cell = (XSSFCell) row.createCell((short) 6); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getAddress()); cell = (XSSFCell) row.createCell((short) 7); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDepartmentID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("S nhn vin c lng >= 6tr "); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula( "COUNTIF(F" + (startRow + 1 + 2) + ":F" + (startRow + 2 + this.list.size()) + ",\">= 6000000\")"); AbilityManagement.setThickBorder(cell, workbook); }
From source file:vd10_workbook.ForeignLanguageManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("ngoai_ngu"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Ngoi ng"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) );/* w w w . j av a 2 s .co m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn ngn ng sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Ngn ng"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); } }
From source file:vd10_workbook.RequirementManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("yeu_cau"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Bng yu cu"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) );//w ww . j a va 2s . co m sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); AbilityManagement.setBackGroundColor(cell, workbook); //ID loi cng vic sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("ID Loi cng vic"); AbilityManagement.setThickBorder(cell, workbook); AbilityManagement.setBackGroundColor(cell, workbook); //ID ngoi ng sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("ID Ngoi ng"); AbilityManagement.setThickBorder(cell, workbook); AbilityManagement.setBackGroundColor(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getWorktypeID()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getForeignLanguageID()); } }