List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFont
@Override public void setFont(Font font)
From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java
private static void CreaateHeaderOfErrorList(XSSFWorkbook ErrorWorkbook, Cell column, String text) { XSSFCellStyle headerStyleOfreference = ErrorWorkbook.createCellStyle(); headerStyleOfreference.setAlignment(XSSFCellStyle.ALIGN_CENTER); headerStyleOfreference.setFillPattern(FillPatternType.SOLID_FOREGROUND); headerStyleOfreference.setFillForegroundColor(new XSSFColor(new java.awt.Color(217, 217, 217))); headerStyleOfreference.setBorderBottom((short) 1); headerStyleOfreference.setBorderTop((short) 1); headerStyleOfreference.setBorderLeft((short) 1); headerStyleOfreference.setBorderRight((short) 1); //create font XSSFFont fontOfCellFirst = ErrorWorkbook.createFont(); fontOfCellFirst.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fontOfCellFirst.setFontHeightInPoints((short) 12); fontOfCellFirst.setFontName("Calibri"); fontOfCellFirst.setColor(new XSSFColor(new java.awt.Color(0, 0, 0))); headerStyleOfreference.setFont(fontOfCellFirst); column.setCellValue(text);//from ww w. j a v a2 s . com column.setCellStyle(headerStyleOfreference); }
From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java
private static void CreaateStyleOfErrorList(XSSFWorkbook ErrorWorkbook, XSSFRow row, String cell_ref, String sheet_name, String error_desc, String error_level) { XSSFCellStyle StyleOfCell = ErrorWorkbook.createCellStyle(); StyleOfCell.setAlignment(XSSFCellStyle.ALIGN_CENTER); StyleOfCell.setFillPattern(FillPatternType.SOLID_FOREGROUND); if (error_level.equalsIgnoreCase("Warning")) { StyleOfCell.setFillForegroundColor(new XSSFColor(new java.awt.Color(155, 194, 230))); } else {//from w ww . j a va 2 s . c o m StyleOfCell.setFillForegroundColor(new XSSFColor(new java.awt.Color(225, 171, 171))); } StyleOfCell.setBorderLeft((short) 1); StyleOfCell.setBorderRight((short) 1); StyleOfCell.setBorderTop((short) 1); StyleOfCell.setBorderBottom((short) 1); StyleOfCell.setWrapText(true); //create font XSSFFont fontOfCell = ErrorWorkbook.createFont(); fontOfCell.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fontOfCell.setFontHeightInPoints((short) 10); fontOfCell.setFontName("Calibri"); fontOfCell.setColor(new XSSFColor(new java.awt.Color(0, 0, 0))); StyleOfCell.setFont(fontOfCell); Cell Rowcell_0 = row.createCell(0); Cell Rowcell_1 = row.createCell(1); Cell Rowcell_2 = row.createCell(2); Cell Rowcell_3 = row.createCell(3); Rowcell_0.setCellValue(cell_ref); Rowcell_1.setCellValue(sheet_name); Rowcell_2.setCellValue(error_desc); Rowcell_3.setCellValue(error_level); Rowcell_0.setCellStyle(StyleOfCell); Rowcell_1.setCellStyle(StyleOfCell); Rowcell_2.setCellStyle(StyleOfCell); Rowcell_3.setCellStyle(StyleOfCell); }
From source file:jdbreport.model.io.xls.poi.Excel2007Writer.java
License:Apache License
protected CellStyle createStyle(jdbreport.model.CellStyle style, Workbook wb) { XSSFCellStyle newStyle = (XSSFCellStyle) wb.createCellStyle(); newStyle.setAlignment(convertHorizontalAlign(style.getHorizontalAlignment())); newStyle.setVerticalAlignment(convertVerticalAlign(style.getVerticalAlignment())); Border border = style.getBorders(Border.LINE_BOTTOM); if (border != null) { newStyle.setBorderBottom(getBorder(border)); newStyle.setBottomBorderColor(new XSSFColor(border.getColor())); }//from w ww .j a v a2 s . c o m border = style.getBorders(Border.LINE_TOP); if (border != null) { newStyle.setBorderTop(getBorder(border)); newStyle.setTopBorderColor(new XSSFColor(border.getColor())); } border = style.getBorders(Border.LINE_LEFT); if (border != null) { newStyle.setBorderLeft(getBorder(border)); newStyle.setLeftBorderColor(new XSSFColor(border.getColor())); } border = style.getBorders(Border.LINE_RIGHT); if (border != null) { newStyle.setBorderRight(getBorder(border)); newStyle.setRightBorderColor(new XSSFColor(border.getColor())); } Font font = wb.createFont(); font.setFontName(style.getFamily()); if (style.isBold()) { font.setBold(true); } font.setItalic(style.isItalic()); if (style.isUnderline()) { font.setUnderline(Font.U_SINGLE); } if (style.isStrikethrough()) { font.setStrikeout(true); } font.setFontHeightInPoints((short) style.getSize()); if (style.getForegroundColor() != null && !style.getForegroundColor().equals(Color.black)) { font.setColor(colorToIndex(wb, style.getForegroundColor())); } newStyle.setFont(font); if (style.getBackground() != null && !style.getBackground().equals(Color.white)) { newStyle.setFillForegroundColor(new XSSFColor(style.getBackground())); newStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); } if (style.getAngle() != 0) { int angle = style.getAngle(); if (angle > 90 && angle <= 180) { angle = 90; } else if (angle > 180 && angle <= 270) { angle = -90; } else if (angle > 270) { angle = -(360 - angle); } newStyle.setRotation((short) angle); } newStyle.setWrapText(style.isWrapLine()); return newStyle; }
From source file:kp.servlet.ExportRpt.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w w w. j av a 2 s . c o 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 ww. j a va 2s . co 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 {/*from w ww . j a v a 2s . c o m*/ //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:lospolloshermanos.SalesTablePan.java
public void PrintSales() { if (items != null && categories != null) { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet itemssheet = workbook.createSheet("Item-wise"); XSSFSheet categorysheet = workbook.createSheet("Cateogry-wise"); XSSFRow row;/*from w w w.ja v a 2s . c o m*/ XSSFFont font = workbook.createFont(); font.setBold(true); XSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); Cell cell; row = itemssheet.createRow(0); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("Meal Name"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Quantity"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Sub Total"); font.setBold(false); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); for (int i = 2; i <= no_of_items + 1; i++) { try { row = itemssheet.createRow(i); String QtyTot = items.getString("QtyTot"); String SubTot = items.getString("SubTot"); cell = row.createCell(0); cell.setCellValue(items.getString("MName")); cell = row.createCell(1); if (QtyTot != null) cell.setCellValue(QtyTot); else cell.setCellValue("0"); cell = row.createCell(2); if (SubTot != null) cell.setCellValue(SubTot); else cell.setCellValue("0.00"); items.next(); } catch (SQLException ex) { Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex); } } row = itemssheet.createRow(no_of_items + 3); font.setBold(true); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Grand Total"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(GrandTot + ""); row = categorysheet.createRow(0); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("Category Name"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("No of items sold"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Sub Total"); font.setBold(false); for (int i = 2; i <= no_of_cats + 1; i++) { try { row = categorysheet.createRow(i); cell = row.createCell(0); cell.setCellValue(categories.getString("CName")); cell = row.createCell(1); String QtyTot = categories.getString("QtyTot"); String SubTot = categories.getString("SubTot"); if (QtyTot != null) cell.setCellValue(QtyTot); else cell.setCellValue("0"); cell = row.createCell(2); if (SubTot != null) cell.setCellValue(SubTot); else cell.setCellValue("0.00"); categories.next(); } catch (SQLException ex) { Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex); } } row = categorysheet.createRow(no_of_cats + 3); font.setBold(true); cell = row.createCell(0); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Grand Total"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(GrandTot + ""); font.setBold(false); itemssheet.autoSizeColumn(0); itemssheet.autoSizeColumn(1); itemssheet.autoSizeColumn(2); categorysheet.autoSizeColumn(0); categorysheet.autoSizeColumn(1); categorysheet.autoSizeColumn(2); try { items.first(); categories.first(); FileOutputStream out = new FileOutputStream( new File("C:/Program Files/RMS/Sales_from_" + Date1 + "_to_" + Date2 + ".xlsx")); workbook.write(out); out.close(); } catch (Exception e) { } } }
From source file:mvjce.Excel_operations.java
public static void fill_exceldata(XSSFWorkbook workbook, XSSFSheet spreadsheet) { try {/*from w ww . j a va 2 s . co m*/ Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet detail = st.executeQuery( "select attendance.USN,Student_info.Name,attendance.sub1_class,attendance.sub2_class," + " attendance.sub3_class,attendance.sub4_class,attendance.sub5_class,attendance.sub6_class,attendance.sub7_class,attendance.sub8_class from attendance inner join " + " Student_info on attendance.USN = Student_info.USN where class='" + Writesheet.sec + "' and semester=" + Writesheet.sem_string + " ;"); int i = 1, j = 8; XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true); XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); XSSFCell cell; while (detail.next()) { XSSFRow row = spreadsheet.createRow((short) j); row.setHeight((short) 900); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue(i); i++; cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue(detail.getString(1)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue(detail.getString(2)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue(detail.getString(3)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue(detail.getString(4)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 7); cell.setCellValue(detail.getString(5)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 9); cell.setCellValue(detail.getString(6)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 11); cell.setCellValue(detail.getString(7)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 13); cell.setCellValue(detail.getString(8)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 15); cell.setCellValue(detail.getString(9)); cell.setCellStyle(style); cell = (XSSFCell) row.createCell((short) 17); cell.setCellValue(detail.getString(10)); cell.setCellStyle(style); j++; } } catch (Exception e) { System.out.println(e); } }
From source file:mvjce.Excel_operations.java
public static void insert_internals(XSSFWorkbook workbook, XSSFSheet spreadsheet) { try {//from w ww . ja v a2 s . co m Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/Sample_data", "root", "root"); Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet detail = st.executeQuery( "select s.USN,s.Name,i.sub1_int1,i.sub1_int2,i.sub1_int3,i.sub2_int1,i.sub2_int2,i.sub2_int3,i.sub3_int1,i.sub3_int2,i.sub3_int3,i.sub4_int1,i.sub4_int2,i.sub4_int3,\n" + "i2.sub5_int1,i2.sub5_int2,i2.sub5_int3,i2.sub6_int1,i2.sub6_int2,i2.sub6_int3,i2.sub7_int1,i2.sub7_int2,i2.sub7_int3,i2.sub8_int1,i2.sub8_int2,i2.sub8_int3\n" + "from internals as i\n" + "join Student_info as s\n" + "on i.USN=s.USN\n" + "join internals2 as i2\n" + "on i2.USN=s.USN\n" + "where s.Class='" + Writesheet.sec + "' and s.semester=" + Writesheet.sem_string + ";"); int i = 1, j = 6; XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true); XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_LEFT); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); XSSFCell cell; while (detail.next()) { XSSFRow row = spreadsheet.createRow((short) j); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue(i); cell.setCellStyle(style); for (int k = 1; k <= 26; k++) { cell = (XSSFCell) row.createCell((short) k); cell.setCellValue(detail.getString(k)); cell.setCellStyle(style); } i++; j++; } spreadsheet.autoSizeColumn(2); spreadsheet.autoSizeColumn(1); } catch (Exception e) { System.out.println(e); } }
From source file:mvjce.internal_sheet.java
public static void internal_details(XSSFWorkbook workbook) { XSSFSheet spreadsheet = workbook.createSheet("test_excel_internal"); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);//from ww w.j a v a2 s . c om XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(3); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(4); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT NAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 5, i, i)); } XSSFRow row2 = spreadsheet.createRow((short) 5); for (int i = 0, j = 3; j <= 23; j += 4) { cell = row.createCell((short) j); cell.setCellValue(sub[i]); i++; cell.setCellStyle(style); cell = row2.createCell(j); cell.setCellValue("T1"); cell.setCellStyle(style); cell = row2.createCell(j + 1); cell.setCellValue("T2"); cell.setCellStyle(style); cell = row2.createCell(j + 2); cell.setCellValue("T3"); cell.setCellStyle(style); cell = row2.createCell(j + 3); cell.setCellValue("Avg"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 4, j, j + 3)); Excel_operations.insert_internals(workbook, spreadsheet); } }