List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue
@SuppressWarnings("fallthrough") public void setCellValue(boolean value)
From source file:facebook.metrics.FacebookMetricsGroup.java
public static void main(String[] args) { try {//from w w w. j a v a 2 s . co m // Se crea el libro HSSFWorkbook libro = new HSSFWorkbook(); // Se crea una hoja dentro del libro HSSFSheet hoja = libro.createSheet(); // Se crea una fila dentro de la hoja HSSFRow fila = hoja.createRow(0); // Se crea una celda dentro de la fila HSSFCell celda = fila.createCell(1); // Se crea el contenido de la celda y se mete en ella. HSSFRichTextString texto = new HSSFRichTextString("Metricas de Grupo Cinepolitos"); celda.setCellValue(texto); /************************************/ /*Mongo DB Conection*/ MongoClient mongoClient = new MongoClient("localhost", 27017); DB db = mongoClient.getDB("JavaMongoFacebookGroup"); DBCollection datos = db.getCollection("Datos"); /********************/ Facebook facebook = new FacebookFactory().getInstance(); facebook.setOAuthAppId("603320016402931", "202694064e7a4e77f0c0042b1a16ebd4"); System.out.println(facebook.getOAuthAppAccessToken().getToken()); ResponseList<Post> feedX = facebook.getGroupFeed("17761155026", new Reading().limit(1135)); List<ResponseList<Post>> X = new ArrayList<ResponseList<Post>>(); do { X.add(feedX); Paging<Post> pag1 = feedX.getPaging(); feedX = facebook.fetchNext(pag1); } while (feedX.getPaging() != null); fila = hoja.createRow(2); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString("Id Post:")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString("Fecha del Post:")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString("Usuario que posteo:")); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString("Post:")); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString("# de Likes del Post:")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString("# de Comentarios del Post:")); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString("Comentarios del Post:")); int filasX = 3; for (int j = 0; j < X.size(); j++) { ResponseList<Post> feed = X.get(j); System.out.println(feed.size()); for (int i = 0; i < feed.size(); i++) { System.out.println("Feed: " + i); BasicDBObject obj = new BasicDBObject(); ResponseList<Like> likes = facebook.getPostLikes(feed.get(i).getId(), new Reading().limit(1135)); ResponseList<Comment> comments = facebook.getPostComments(feed.get(i).getId(), new Reading().limit(1135)); obj.append("idPost", feed.get(i).getId() + ""); if (feed.get(i).getMessage() == null) { obj.append("Post", " "); //System.out.println("Null"); } else { obj.append("Post", feed.get(i).getMessage()); } obj.append("Likes", likes.size() + ""); obj.append("Comments", comments.size() + ""); datos.insert(obj); fila = hoja.createRow(filasX); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString(feed.get(i).getId() + "")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString(feed.get(i).getCreatedTime() + "")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString(feed.get(i).getFrom().getName())); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString(feed.get(i).getMessage())); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString(likes.size() + "")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString(comments.size() + "")); filasX++; for (int y = 0; y < comments.size(); y++) { fila = hoja.createRow(filasX); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString(comments.get(y).getMessage() + "")); filasX++; } } System.out.println(); } FileOutputStream elFichero = new FileOutputStream("Metricas_Cinepolitos.xls"); libro.write(elFichero); elFichero.close(); } catch (Exception e) { System.err.println("Fatal Error: " + e); } }
From source file:facebook.metrics.FacebookMetricsPage.java
public static void main(String[] args) throws IOException { int varFeed = 0; int filasX = 0; HSSFRow fila = null;//from www .j a va2 s. c om HSSFRow filam = null; HSSFCell celda = null; List<ResponseList<Post>> X = null; int j = 0; int i = 0; HSSFSheet hoja = null; Facebook facebook = null; HSSFWorkbook libro = null; try { // Se crea el libro libro = new HSSFWorkbook(); hoja = libro.createSheet(); fila = hoja.createRow(0); celda = fila.createCell(1); HSSFRichTextString texto = new HSSFRichTextString("Metricas de Infinitum"); celda.setCellValue(texto); /************************************/ /*Mongo DB Conection*/ MongoClient mongoClient = new MongoClient("localhost", 27017); DB db = mongoClient.getDB("JavaMongoFacebookGroup"); DBCollection datos = db.getCollection("Datos"); /********************/ facebook = new FacebookFactory().getInstance(); facebook.setOAuthAppId("603320016402931", "202694064e7a4e77f0c0042b1a16ebd4"); System.out.println(facebook.getOAuthAppAccessToken().getToken()); ResponseList<Post> feedX = facebook.getFeed("216786388331757", new Reading().limit(1000)); X = new ArrayList<ResponseList<Post>>(); int ui = 0; do { X.add(feedX); Paging<Post> pag1 = feedX.getPaging(); feedX = facebook.fetchNext(pag1); System.out.println("" + ui); ui++; } while (feedX.getPaging() != null && ui < 25); fila = hoja.createRow(2); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString("Id Post:")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString("Fecha del Post:")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString("Usuario que posteo:")); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString("Post:")); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString("# de Likes del Post:")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString("# de Comentarios del Post:")); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString("Comentarios del Post:")); celda = fila.createCell(8); celda.setCellValue(new HSSFRichTextString("Tipo de Post:")); filasX = 4; for (j = 0; j < X.size(); j++) { ResponseList<Post> feed = X.get(j); System.out.println(feed.size()); for (i = 0; i < feed.size(); i++) { System.out.println("Feed: " + i); BasicDBObject obj = new BasicDBObject(); ResponseList<Like> likes = facebook.getPostLikes(feed.get(i).getId(), new Reading().limit(1135)); ResponseList<Comment> comments = facebook.getPostComments(feed.get(i).getId(), new Reading().limit(1135)); obj.append("idPost", feed.get(i).getId() + ""); if (feed.get(i).getMessage() == null) { obj.append("Post", " "); //System.out.println("Null"); } else { obj.append("Post", feed.get(i).getMessage()); } obj.append("Likes", likes.size() + ""); obj.append("Comments", comments.size() + ""); datos.insert(obj); filam = hoja.createRow(filasX); celda = filam.createCell(1); celda.setCellValue(new HSSFRichTextString(feed.get(i).getId() + "")); celda = filam.createCell(2); celda.setCellValue(new HSSFRichTextString(feed.get(i).getCreatedTime() + "")); celda = filam.createCell(3); celda.setCellValue(new HSSFRichTextString(feed.get(i).getFrom().getName())); celda = filam.createCell(4); celda.setCellValue(new HSSFRichTextString(feed.get(i).getMessage())); celda = filam.createCell(5); celda.setCellValue(new HSSFRichTextString(likes.size() + "")); celda = filam.createCell(6); celda.setCellValue(new HSSFRichTextString(comments.size() + "")); celda = filam.createCell(8); celda.setCellValue(new HSSFRichTextString(feed.get(i).getType() + "")); filasX++; for (int y = 0; y < comments.size(); y++) { filam = hoja.createRow(filasX); celda = filam.createCell(7); celda.setCellValue(new HSSFRichTextString(comments.get(y).getMessage() + "")); filasX++; } } } FileOutputStream elFichero = new FileOutputStream("Metricas_Infinitum.xls"); libro.write(elFichero); elFichero.close(); } catch (Exception e) { System.err.println("Fatal Error: " + e); FacebookMetricsPage s = new FacebookMetricsPage(); s.porsi(i, j, filasX + 1, filam, celda, facebook, X, hoja, libro); } }
From source file:facebook.metrics.FacebookMetricsPage.java
public void porsi(int ii, int jj, int filasX, HSSFRow fila, HSSFCell celda, Facebook facebook, List<ResponseList<Post>> X, HSSFSheet hoja, HSSFWorkbook libro) throws IOException { try {/*ww w . java 2 s. c om*/ for (int j = jj; j < X.size(); j++) { ResponseList<Post> feed = X.get(j); System.out.println(feed.size()); for (int i = ii; i < feed.size(); i++) { System.out.println("Feed: " + i); BasicDBObject obj = new BasicDBObject(); ResponseList<Like> likes = facebook.getPostLikes(feed.get(i).getId(), new Reading().limit(1135)); ResponseList<Comment> comments = facebook.getPostComments(feed.get(i).getId(), new Reading().limit(1135)); // obj.append("idPost",feed.get(i).getId()+""); // // if(feed.get(i).getMessage()==null){ // obj.append("Post"," "); // //System.out.println("Null"); // }else{ // obj.append("Post",feed.get(i).getMessage()); // } // // obj.append("Likes",likes.size()+""); // obj.append("Comments",comments.size()+""); // // datos.insert(obj); fila = hoja.createRow(filasX); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString(feed.get(i).getId() + "")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString(feed.get(i).getCreatedTime() + "")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString(feed.get(i).getFrom().getName())); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString(feed.get(i).getMessage())); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString(likes.size() + "")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString(comments.size() + "")); celda = fila.createCell(8); celda.setCellValue(new HSSFRichTextString(feed.get(i).getType() + "")); filasX++; for (int y = 0; y < comments.size(); y++) { fila = hoja.createRow(filasX); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString(comments.get(y).getMessage() + "")); filasX++; } } } FileOutputStream elFichero = new FileOutputStream("Metricas_Infinitum.xls"); libro.write(elFichero); elFichero.close(); } catch (Exception e) { System.err.println("Fatal Error 2: " + e); FileOutputStream elFichero; try { elFichero = new FileOutputStream("Metricas_Infinitum.xls"); libro.write(elFichero); elFichero.close(); } catch (FileNotFoundException ex) { Logger.getLogger(FacebookMetricsPage.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:facebook.metrics.FacebookMetricsPage2.java
public static void main(String[] args) throws IOException { int varFeed = 0; int filasX = 0; HSSFRow fila = null;/*from w w w .j a v a 2 s. c om*/ HSSFCell celda = null; List<ResponseList<Post>> X = null; int j = 0; int i = 0; HSSFSheet hoja = null; Facebook facebook = null; HSSFWorkbook libro = null; try { // Se crea el libro libro = new HSSFWorkbook(); // Se crea una hoja dentro del libro hoja = libro.createSheet(); // Se crea una fila dentro de la hoja fila = hoja.createRow(0); // Se crea una celda dentro de la fila celda = fila.createCell(1); // Se crea el contenido de la celda y se mete en ella. HSSFRichTextString texto = new HSSFRichTextString("Metricas de Sony Futbol"); celda.setCellValue(texto); /************************************/ /*Mongo DB Conection*/ MongoClient mongoClient = new MongoClient("localhost", 27017); DB db = mongoClient.getDB("JavaMongoFacebookGroup"); DBCollection datos = db.getCollection("Datos"); /********************/ facebook = new FacebookFactory().getInstance(); facebook.setOAuthAppId("603320016402931", "202694064e7a4e77f0c0042b1a16ebd4"); System.out.println(facebook.getOAuthAppAccessToken().getToken()); ResponseList<Post> feedX = facebook.getFeed("120472297979101", new Reading().limit(1000)); X = new ArrayList<ResponseList<Post>>(); int ui = 0; do { X.add(feedX); Paging<Post> pag1 = feedX.getPaging(); feedX = facebook.fetchNext(pag1); System.out.println("" + ui); ui++; } while (feedX.getPaging() != null && ui < 5); fila = hoja.createRow(2); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString("Id Post:")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString("Fecha del Post:")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString("Usuario que posteo:")); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString("Post:")); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString("# de Likes del Post:")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString("# de Comentarios del Post:")); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString("Comentarios del Post:")); celda = fila.createCell(8); celda.setCellValue(new HSSFRichTextString("Tipo de Post:")); filasX = 3; for (j = 0; j < X.size(); j++) { ResponseList<Post> feed = X.get(j); System.out.println(feed.size()); for (i = 0; i < feed.size(); i++) { System.out.println("Feed: " + i); BasicDBObject obj = new BasicDBObject(); ResponseList<Like> likes = facebook.getPostLikes(feed.get(i).getId(), new Reading().limit(1135)); ResponseList<Comment> comments = facebook.getPostComments(feed.get(i).getId(), new Reading().limit(1135)); obj.append("idPost", feed.get(i).getId() + ""); if (feed.get(i).getMessage() == null) { obj.append("Post", " "); //System.out.println("Null"); } else { obj.append("Post", feed.get(i).getMessage()); } obj.append("Likes", likes.size() + ""); obj.append("Comments", comments.size() + ""); datos.insert(obj); fila = hoja.createRow(filasX); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString(feed.get(i).getId() + "")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString(feed.get(i).getCreatedTime() + "")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString(feed.get(i).getFrom().getName())); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString(feed.get(i).getMessage())); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString(likes.size() + "")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString(comments.size() + "")); celda = fila.createCell(8); celda.setCellValue(new HSSFRichTextString(feed.get(i).getType() + "")); filasX++; for (int y = 0; y < comments.size(); y++) { fila = hoja.createRow(filasX); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString(comments.get(y).getMessage() + "")); filasX++; } } } FileOutputStream elFichero = new FileOutputStream("Metricas_SonyFutbol.xls"); libro.write(elFichero); elFichero.close(); } catch (Exception e) { System.err.println("Fatal Error: " + e); FacebookMetricsPage s = new FacebookMetricsPage(); s.porsi(i, j, filasX, fila, celda, facebook, X, hoja, libro); } }
From source file:facebook.metrics.FacebookMetricsPage2.java
public void porsi(int ii, int jj, int filasX, HSSFRow fila, HSSFCell celda, Facebook facebook, List<ResponseList<Post>> X, HSSFSheet hoja, HSSFWorkbook libro) throws IOException { try {/* www . j a v a2s .c o m*/ for (int j = jj; j < X.size(); j++) { ResponseList<Post> feed = X.get(j); System.out.println(feed.size()); for (int i = ii; i < feed.size(); i++) { System.out.println("Feed: " + i); BasicDBObject obj = new BasicDBObject(); ResponseList<Like> likes = facebook.getPostLikes(feed.get(i).getId(), new Reading().limit(1135)); ResponseList<Comment> comments = facebook.getPostComments(feed.get(i).getId(), new Reading().limit(1135)); // obj.append("idPost",feed.get(i).getId()+""); // // if(feed.get(i).getMessage()==null){ // obj.append("Post"," "); // //System.out.println("Null"); // }else{ // obj.append("Post",feed.get(i).getMessage()); // } // // obj.append("Likes",likes.size()+""); // obj.append("Comments",comments.size()+""); // // datos.insert(obj); fila = hoja.createRow(filasX); celda = fila.createCell(1); celda.setCellValue(new HSSFRichTextString(feed.get(i).getId() + "")); celda = fila.createCell(2); celda.setCellValue(new HSSFRichTextString(feed.get(i).getCreatedTime() + "")); celda = fila.createCell(3); celda.setCellValue(new HSSFRichTextString(feed.get(i).getFrom().getName())); celda = fila.createCell(4); celda.setCellValue(new HSSFRichTextString(feed.get(i).getMessage())); celda = fila.createCell(5); celda.setCellValue(new HSSFRichTextString(likes.size() + "")); celda = fila.createCell(6); celda.setCellValue(new HSSFRichTextString(comments.size() + "")); celda = fila.createCell(8); celda.setCellValue(new HSSFRichTextString(feed.get(i).getType() + "")); filasX++; for (int y = 0; y < comments.size(); y++) { fila = hoja.createRow(filasX); celda = fila.createCell(7); celda.setCellValue(new HSSFRichTextString(comments.get(y).getMessage() + "")); filasX++; } } } FileOutputStream elFichero = new FileOutputStream("Metricas_SonyFutbol.xls"); libro.write(elFichero); elFichero.close(); } catch (Exception e) { System.err.println("Fatal Error 2: " + e); FileOutputStream elFichero; try { elFichero = new FileOutputStream("Metricas_SonyFutbol.xls"); libro.write(elFichero); elFichero.close(); } catch (FileNotFoundException ex) { Logger.getLogger(FacebookMetricsPage.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:featurescomparison.workingwithformattingfeatures.cellalignment.java.ApacheCellAlignment.java
License:Apache License
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook//from w ww.j a va2 s. co m * @param row the row to create the cell in * @param column the column number to create the cell in * @param align the alignment for the cell. */ private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, int align) { HSSFCell cell = row.createCell(column); cell.setCellValue("Align It"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment((short) align); cell.setCellStyle(cellStyle); }
From source file:fileXLS.MakeFileXLS.java
public void resources(int id, int stock_id) throws IOException, Exception { DBEntry[] params;//from w w w . ja v a2 s. c om if (stock_id == -1 && id != -1) { params = new DBEntry[1]; params[0] = new DBEntry("resource_id", EntryType.Int, id); } else if (stock_id != -1 && id != -1) { params = new DBEntry[2]; AvailableResource avRes = AvailableResource.getOne(id); params[0] = new DBEntry("resource_id", EntryType.Int, avRes.getResourceId()); params[1] = new DBEntry("stock_id", EntryType.Int, stock_id); } else if (stock_id != -1 && id == -1) { params = new DBEntry[1]; params[0] = new DBEntry("stock_id", EntryType.Int, stock_id); } else { params = null; } History[] history = History.getAll(params); String[] columnNames = { ", --", "??", "-", "", "" }; Object[][] data = new Object[history.length][5]; int len = Resource.getAll(null)[Resource.getAll(null).length - 1].getId(); int[] total = new int[len]; for (int i = 0; i < len; i++) total[i] = 0; for (int i = 0; i < history.length; i++) { data[i][0] = history[i].getDateString(); Resource res = Resource.getOne(history[i].getResourceId()); data[i][1] = res.getName(); total[history[i].getResourceId() - 1] += history[i].getNumber(); data[i][2] = total[history[i].getResourceId() - 1] + " " + res.getMeasureName(); if (history[i].getNumber() > 0) data[i][3] = "+" + history[i].getNumber() + " " + res.getMeasureName(); else data[i][3] = history[i].getNumber() + " " + res.getMeasureName(); data[i][4] = "" + history[i].getStockId(); } JTable jtable = new JTable(data, columnNames); HSSFWorkbook table = new HSSFWorkbook(); HSSFSheet fSheet = table.createSheet("??"); String link1 = link; if (stock_id == -1 && id != -1) link1 += "resourceId" + id + ".xls"; else if (stock_id != -1 && id != -1) link1 += "resourceId" + id + "&stockId" + stock_id + ".xls"; else if (stock_id != -1 && id == -1) link1 += "stockId" + stock_id + ".xls"; else link1 += "history.xls"; File file = new File(link1); HSSFCellStyle cellStyle = table.createCellStyle(); TableModel model = jtable.getModel(); HSSFRow fRow = fSheet.createRow(0); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(jtable.getColumnName(j)); } for (int i = 0; i < model.getRowCount(); i++) { fRow = fSheet.createRow(i + 1); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(model.getValueAt(i, j).toString()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(file); BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream); table.write(bos); bos.close(); fileOutputStream.close(); }
From source file:fileXLS.MakeFileXLS.java
public void applications(int status) throws Exception { DBEntry[] params = { new DBEntry("request_type_id", EntryType.Int, 1), new DBEntry("status", EntryType.Int, status) }; DBEntry[] params1 = { new DBEntry("request_type_id", EntryType.Int, 2), new DBEntry("status", EntryType.Int, status) }; Request[] req = Request.getAll(params); Request[] req1 = Request.getAll(params1); Vector<Integer> ind = new Vector(); Vector<Integer> numb = new Vector(); for (int i = 0; i < req.length; i++) { if (ind.size() > 0) { boolean f = false; for (int j = 0; j < ind.size(); j++) if (ind.elementAt(j) == req[i].getResourceId()) { numb.set(j, numb.elementAt(j) + req[i].getNumber()); f = true;/*from www . j av a 2s .co m*/ } if (!f) { numb.add(req[i].getNumber()); ind.add(req[i].getResourceId()); } } else { numb.add(req[i].getNumber()); ind.add(req[i].getResourceId()); } } Vector<Integer> ind1 = new Vector(); Vector<Integer> numb1 = new Vector(); for (int i = 0; i < req1.length; i++) { if (ind1.size() > 0) { boolean f = false; for (int j = 0; j < ind1.size(); j++) if (ind1.elementAt(j) == req1[i].getResourceId()) { numb1.set(j, numb1.elementAt(j) + req1[i].getNumber()); f = true; } if (!f) { numb1.add(req1[i].getNumber()); ind1.add(req1[i].getResourceId()); } } else { numb1.add(req1[i].getNumber()); ind1.add(req1[i].getResourceId()); } } String[] columnNames = { "??", "-" }; Object[][] data = new Object[numb.size()][2]; Object[][] data1 = new Object[numb1.size()][2]; for (int i = 0; i < numb.size(); i++) { data[i][0] = Resource.getOne(ind.elementAt(i)).getName(); data[i][1] = numb.elementAt(i) + " " + Resource.getOne(ind.elementAt(i)).getMeasureName(); } for (int i = 0; i < numb1.size(); i++) { data1[i][0] = Resource.getOne(ind1.elementAt(i)).getName(); data1[i][1] = numb1.elementAt(i) + " " + Resource.getOne(ind1.elementAt(i)).getMeasureName(); } JTable jtable = new JTable(data, columnNames); JTable jtable1 = new JTable(data1, columnNames); HSSFWorkbook table = new HSSFWorkbook(); HSSFSheet fSheet = table.createSheet("??? ??"); HSSFSheet fSheet1 = table.createSheet(" ??"); String link1 = link; if (status == 0) link1 += "resources_ready.xls"; else link1 += "resources_done.xls"; File file = new File(link1); HSSFCellStyle cellStyle = table.createCellStyle(); TableModel model = jtable.getModel(); TableModel model1 = jtable1.getModel(); HSSFRow fRow = fSheet.createRow(0); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(jtable.getColumnName(j)); } for (int i = 0; i < model.getRowCount(); i++) { fRow = fSheet.createRow(i + 1); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(model.getValueAt(i, j).toString()); cell.setCellStyle(cellStyle); } } HSSFRow fRow1 = fSheet1.createRow(0); for (int j = 0; j < model1.getColumnCount(); j++) { HSSFCell cell = fRow1.createCell(j); cell.setCellValue(jtable1.getColumnName(j)); } for (int i = 0; i < model1.getRowCount(); i++) { fRow1 = fSheet1.createRow(i + 1); for (int j = 0; j < model1.getColumnCount(); j++) { HSSFCell cell = fRow1.createCell(j); cell.setCellValue(model1.getValueAt(i, j).toString()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(file); BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream); table.write(bos); bos.close(); fileOutputStream.close(); }
From source file:fileXLS.MakeFileXLS.java
public void FullApplication(int status) throws Exception { DBEntry[] params = { new DBEntry("request_type_id", EntryType.Int, 1), new DBEntry("status", EntryType.Int, status) }; DBEntry[] params1 = { new DBEntry("request_type_id", EntryType.Int, 2), new DBEntry("status", EntryType.Int, status) }; Request[] req = Request.getAll(params); Request[] req1 = Request.getAll(params1); String[] columnNames = { "??", "-", ", --", "", "?", "", "Email" }; Object[][] data = new Object[req.length][7]; Object[][] data1 = new Object[req1.length][7]; for (int i = 0; i < req.length; i++) { data[i][0] = req[i].getResourceName(); data[i][1] = req[i].getNumber() + " " + req[i].getMeasureName(); data[i][2] = req[i].getDateString(); data[i][3] = Application.getOne(req[i].getApplicationId()).getFullName(); data[i][4] = Location.getOne(req[i].getLocationId()).getAddress(); data[i][5] = Application.getOne(req[i].getApplicationId()).getPhone(); data[i][6] = Application.getOne(req[i].getApplicationId()).getEmail(); }//w ww .j a va2 s . com for (int i = 0; i < req1.length; i++) { data1[i][0] = req1[i].getResourceName(); data1[i][1] = req1[i].getNumber() + " " + req1[i].getMeasureName(); data1[i][2] = req1[i].getDateString(); data1[i][3] = Application.getOne(req1[i].getApplicationId()).getFullName(); data1[i][4] = Location.getOne(req1[i].getLocationId()).getAddress(); data1[i][5] = Application.getOne(req1[i].getApplicationId()).getPhone(); data1[i][6] = Application.getOne(req1[i].getApplicationId()).getEmail(); } JTable jtable = new JTable(data, columnNames); JTable jtable1 = new JTable(data1, columnNames); HSSFWorkbook table = new HSSFWorkbook(); HSSFSheet fSheet = table.createSheet("??? ??"); HSSFSheet fSheet1 = table.createSheet(" ??"); String link1 = link; if (status == 0) link1 += "full_resources_ready.xls"; else link1 += "full_resources_done.xls"; File file = new File(link1); HSSFCellStyle cellStyle = table.createCellStyle(); TableModel model = jtable.getModel(); TableModel model1 = jtable1.getModel(); HSSFRow fRow = fSheet.createRow(0); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(jtable.getColumnName(j)); } for (int i = 0; i < model.getRowCount(); i++) { fRow = fSheet.createRow(i + 1); for (int j = 0; j < model.getColumnCount(); j++) { HSSFCell cell = fRow.createCell(j); cell.setCellValue(model.getValueAt(i, j).toString()); cell.setCellStyle(cellStyle); } } HSSFRow fRow1 = fSheet1.createRow(0); for (int j = 0; j < model1.getColumnCount(); j++) { HSSFCell cell = fRow1.createCell(j); cell.setCellValue(jtable1.getColumnName(j)); } for (int i = 0; i < model1.getRowCount(); i++) { fRow1 = fSheet1.createRow(i + 1); for (int j = 0; j < model1.getColumnCount(); j++) { HSSFCell cell = fRow1.createCell(j); cell.setCellValue(model1.getValueAt(i, j).toString()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(file); BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream); table.write(bos); bos.close(); fileOutputStream.close(); }
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww . j ava2 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, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District[]; String Year = ""; District = request.getParameterValues("District"); Year = request.getParameter("Year"); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String distval = ""; int activeOVC = 0; int activeHH = 0; float activeovc = 0; float activehh = 0; float percent = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("CBO FILING TRACKER REPORT" + "( " + Year + ")"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 4000); shet1.setColumnWidth(18, 4000); shet1.setColumnWidth(19, 4200); shet1.setColumnWidth(20, 4200); shet1.setColumnWidth(21, 4200); shet1.setColumnWidth(22, 4200); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("DISTRICT"); cell1.setCellStyle(style); cell2.setCellValue("CBO"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); ArrayList docidarray = new ArrayList(); String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { docidarray.add(conn.rs3.getString(1)); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); cell6 = rw6.createCell(doccounter1); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); doccounter1++; System.out.println("mergecounter b4" + mergecounter); // shet1.addMergedRegion(new CellRangeAddress(2,2,2,3)); // shet1.addMergedRegion(new CellRangeAddress(2,2,4,5)); // shet1.addMergedRegion(new CellRangeAddress(2,2,6,7)); // shet1.addMergedRegion(new CellRangeAddress(2,2,8,9)); // shet1.addMergedRegion(new CellRangeAddress(2,2,10,11)); // shet1.addMergedRegion(new CellRangeAddress(2,2,12,13)); // shet1.addMergedRegion(new CellRangeAddress(2,2,14,15)); // shet1.addMergedRegion(new CellRangeAddress(2,2,16,17)); // shet1.addMergedRegion(new CellRangeAddress(2,2,18,19)); // shet1.addMergedRegion(new CellRangeAddress(2,2,20,21)); System.out.println("mergecounter after" + mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 4; HSSFRow rw5 = null; for (int j = 0; j < District.length; j++) { String getcboCOUNT = "select * from CBO where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from District where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("District"); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); } doccounter = 4; System.out.println("doccount " + doccounter); String getdocname1 = "select * from ovcdocuments"; // conn.rs3 = conn.state.executeQuery(getdocname1); // while(conn.rs3.next()){ String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District," + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District[j] + "' and " + "Clientdetails.Cbo='" + conn.rs2.getString(1) + "' and Clientdetails.Exited='1' and " + " (Year='" + Year + "' OR Year='') AND Clientdetails.OVCID = ovcfiling.ovcid " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District Order by ovcfiling.ovcdocid,District "; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); int a = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(Math.round(percent) + "%"); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } //} // end of while loop if (distval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); distval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); if (counter > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + distval); } // cell1.setCellValue(districtname); if (!distval.equals(districtname) && !distval.equals("")) { distval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(distval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(distval + "@@@@ " + districtname); } // end of for loop // int totalvalue=countercopy+counter; // System.out.println(counter+" hhhh "+countercopy); // if(counter>countercopy){ // shet1.addMergedRegion(new CellRangeAddress(countercopy,totalvalue-1,0,0)); // // countercopy=counter; // } // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // write it as an excel attachment 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=CBO_FILING_TRACKER_REPORT_FOR_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }