List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createFont
@Override
public XSSFFont createFont()
From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java
License:Open Source License
public void runXSSF(String inputfile) { int size = checkSpecialCharacters(inputfile); int n = inputfile.lastIndexOf("."); //String outputfile = inputfile.substring(0, n) + ".xlsx"; String outputfile = getOutputFile(inputfile, ".xlsx"); try {/* w w w .j a va 2 s. co m*/ XSSFWorkbook wb = new XSSFWorkbook(); XSSFCreationHelper helper = null; XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); XSSFCellStyle linkCellStyle = wb.createCellStyle(); XSSFFont linkfont = wb.createFont(); //XSSFColor color = new XSSFColor(Color.LIGHT_BLUE); XSSFColor color = new XSSFColor(Color.BLUE); linkfont.setColor(color); //linkfont.setColor(XSSFColor.LIGHT_BLUE.index); linkCellStyle.setFont(linkfont); CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file String[] line; int r = 0; Cell cell = null; XSSFHyperlink url_link = null; XSSFSheet sheet = null; int page_num = 1; Row row = null; int lcv = 0; int row_count = 0; try { while ((line = reader.readNext()) != null) { if (lcv % PAGE_SIZE == 0) { r = 0; String sheetLabel = SHEET_LABEL; if (size > PAGE_SIZE) { sheetLabel = sheetLabel + " (Page " + page_num + ")"; } //System.out.println("Creating " + sheetLabel); sheet = wb.createSheet(sheetLabel); helper = sheet.getWorkbook().getCreationHelper(); url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); row = sheet.createRow((short) r); for (int i = 0; i < HEADINGS.length; i++) { String heading = HEADINGS[i]; cell = row.createCell(i); cell.setCellValue(heading); cell.setCellStyle(cellStyle); } page_num++; } else { String s4 = (String) line[4]; s4 = s4.trim(); r++; row = sheet.createRow((short) r); row_count++; cell = row.createCell(0); String ncit_code = line[0]; cell.setCellValue(ncit_code); if (NCIT_LINK) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCItHyperlink(ncit_code)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } cell = row.createCell(1); String name = line[1]; cell.setCellValue(line[1]); cell = row.createCell(2); cell.setCellValue(line[2]); if (NCIM_LINK) { String s2 = line[2]; s2 = s2.trim(); if (s2.length() > 0) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); url_link.setAddress(getNCImHyperlink(s2)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } cell = row.createCell(3); String ncim_name = line[3]; cell.setCellValue(line[3]); cell = row.createCell(4); cell.setCellValue(line[4]); cell = row.createCell(5); String atom_name = (String) line[5]; cell.setCellValue(line[5]); cell = row.createCell(6); cell.setCellValue(line[6]); if (SOURCE_LINK) { if (s4.length() > 0) { String s6 = (String) line[6]; if (localNameMap.containsKey(s4)) { url_link = helper.createHyperlink(XSSFHyperlink.LINK_URL); s4 = (String) localNameMap.get(s4); url_link.setAddress(getSourceHyperlink(s4, s6)); cell.setHyperlink(url_link); cell.setCellStyle(linkCellStyle); } } } cell = row.createCell(7); cell.setCellValue(line[7]); } lcv++; } } catch (Exception ex) { ex.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(outputfile); wb.write(fileOut); fileOut.close(); System.out.println("Output file " + outputfile + " generated."); System.out.println("row_count: " + row_count); } catch (Exception ex) { ex.printStackTrace(); } }
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);/*www .ja v a 2s .c o m*/ 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 {/* ww w. j a v a 2 s . c om*/ 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:javaapp.generateAnomolies.java
public static void generateAnomolyReport() throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); HashMap<String, List<String>> hmd = new HashMap<String, List<String>>(); HashMap<String, List<String>> hmc = new HashMap<String, List<String>>(); HashMap<String, List<String>> uhmd = new HashMap<String, List<String>>(); HashMap<String, List<String>> uhmc = new HashMap<String, List<String>>(); hmd = getAnomolies("transrec", "Debtors", 0); hmc = getAnomolies("ctransrec", "Creditors", 1); uhmd = getAnomoliesUninv("uninv", "Uninv_Debtors", 2); uhmc = getAnomoliesUninv("cuninv", "Uninv_Creditors", 3); List ls;//from w ww. j a v a2 s . c om List ls2, ls3, ls4; Font font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); Font hfont = workbook.createFont(); hfont.setFontHeightInPoints((short) 12); hfont.setFontName("Cambria"); CellStyle style = workbook.createCellStyle(); CellStyle hstyle = workbook.createCellStyle(); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); hstyle.setFont(font); hstyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); hstyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //System.out.println(hm); XSSFSheet sheet = workbook.createSheet("Debtors"); XSSFSheet sheet2 = workbook.createSheet("Creditors"); XSSFSheet sheet3 = workbook.createSheet("Uninv_Debtors"); XSSFSheet sheet4 = workbook.createSheet("Uninv_Creditors"); Iterator iterator = hmd.keySet().iterator(); int rowCount = 0; Row row = sheet.createRow(0); Cell hcell = row.createCell(1); hcell.setCellValue((String) "RPPS"); hcell.setCellStyle(hstyle); hcell = row.createCell(2); hcell.setCellValue((String) "SVC"); hcell.setCellStyle(hstyle); hcell = row.createCell(3); hcell.setCellValue((String) "OPEN"); hcell.setCellStyle(hstyle); hcell = row.createCell(4); hcell.setCellValue((String) "RINVOICE"); hcell.setCellStyle(hstyle); hcell = row.createCell(5); hcell.setCellValue((String) "CORRECTION"); hcell.setCellStyle(hstyle); hcell = row.createCell(6); hcell.setCellValue((String) "ADJUST"); hcell.setCellStyle(hstyle); hcell = row.createCell(7); hcell.setCellValue((String) "O1C"); hcell.setCellStyle(hstyle); hcell = row.createCell(8); hcell.setCellValue((String) "SETTLED"); hcell.setCellStyle(hstyle); hcell = row.createCell(9); hcell.setCellValue((String) "ALLOC"); hcell.setCellStyle(hstyle); hcell = row.createCell(10); hcell.setCellValue((String) "WRITEOFF"); hcell.setCellStyle(hstyle); hcell = row.createCell(11); hcell.setCellValue((String) "CLOSE"); hcell.setCellStyle(hstyle); hcell = row.createCell(12); hcell.setCellValue((String) "RECDIFF"); hcell.setCellStyle(hstyle); while (iterator.hasNext()) { row = sheet.createRow(++rowCount); int columnCount = 0; String field = ""; String key = iterator.next().toString(); field = key; Cell cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } //System.out.println(hm.get(key)); ls = (List<String>) hmd.get(key); System.out.println(ls); Iterator<String> ite = ls.iterator(); while (ite.hasNext()) { field = ite.next(); //System.out.println( ite.next() ); cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); } cell.setCellStyle(style); } } Iterator iterator2 = hmc.keySet().iterator(); rowCount = 0; while (iterator2.hasNext()) { row = sheet2.createRow(++rowCount); int columnCount = 0; String field = ""; String key = iterator2.next().toString(); field = key; Cell cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } //System.out.println(hm.get(key)); ls2 = (List<String>) hmc.get(key); System.out.println(ls2); Iterator<String> ite = ls2.iterator(); while (ite.hasNext()) { field = ite.next(); //System.out.println( ite.next() ); cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } } } // uninv debtors Iterator iterator3 = uhmd.keySet().iterator(); rowCount = 0; while (iterator3.hasNext()) { row = sheet3.createRow(++rowCount); int columnCount = 0; String field = ""; String key = iterator3.next().toString(); field = key; Cell cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } //System.out.println(hm.get(key)); ls3 = (List<String>) hmc.get(key); System.out.println(ls3); Iterator<String> ite = ls3.iterator(); while (ite.hasNext()) { field = ite.next(); //System.out.println( ite.next() ); cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } } } // uninv creditors Iterator iterator4 = uhmc.keySet().iterator(); rowCount = 0; while (iterator4.hasNext()) { row = sheet4.createRow(++rowCount); int columnCount = 0; String field = ""; String key = iterator4.next().toString(); field = key; Cell cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } //System.out.println(hm.get(key)); ls4 = (List<String>) uhmc.get(key); System.out.println(ls4); Iterator<String> ite = ls4.iterator(); while (ite.hasNext()) { field = ite.next(); //System.out.println( ite.next() ); cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); cell.setCellStyle(style); } } } /* Object[][] bookData = { {"Head First Java", "Kathy Serria", 79}, {"Effective Java", "Joshua Bloch", 36}, {"Clean Code", "Robert martin", 42}, {"Thinking in Java", "Bruce Eckel", 35}, }; //int rowCount = 0; for (Object[] aBook : bookData) { Row row = sheet.createRow(++rowCount); int columnCount = 0; for (Object field : aBook) { Cell cell = row.createCell(++columnCount); if (field instanceof String) { cell.setCellValue((String) field); } else if (field instanceof Integer) { cell.setCellValue((Integer) field); } } } */ // workbook.setSheetName(n, WorkbookUtil.createSafeSheetName("Debtors")); try (FileOutputStream outputStream = new FileOutputStream("anomolies/GBRCN_Anomolies_27April.xlsx")) { workbook.write(outputStream); } }
From source file:kp.servlet.ExportRpt.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww . ja v a 2 s .c om*/ * * @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 av a2 s . c om*/ //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 {/* w ww .j a v a2 s . 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: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. j ava 2 s . 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 {/* ww w . j av a2 s. c o 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 {// ww w . j a va 2 s . com 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); } }