List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell
@Override public XSSFCell createCell(int columnIndex)
From source file:Logic.Xls.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {// w ww .jav a2 s .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; XSSFFont font = workbook.createFont(); font.setBold(true);/*from ww w . ja v a 2s . co m*/ 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:Main.Database.java
/** * @param dbTable - the database table which contains job results from a * particular job board./*from w w w. j a v a2 s . c o m*/ * @param excelSheetIndex - the sheet index on the Excel File which will * house the results from the database. * * This method appends results from the specific database table onto the * index of the excel sheet. */ /* public void appendDBTableToExcel(String dbTable, int excelSheetIndex) { try { connect = connectDatabase(); String sqlQuery = "Select *from " + dbTable; // create the java statement selectStmt = connect.prepareStatement(sqlQuery); resultSet = selectStmt.executeQuery(); //call upon the excel file. excelFile = new File("C:\\Users\\jason\\Desktop\\Job Scraper\\JobScraper\\src\\main\\java\\Main\\links.xlsx"); excelInputStream = new FileInputStream(excelFile); Workbook workbook = create(excelInputStream); CreationHelper createHelper = workbook.getCreationHelper(); Sheet sheet = workbook.getSheetAt(excelSheetIndex); excelOutputStream = new FileOutputStream(excelFile); //set link style to blue CellStyle hlinkstyle = workbook.createCellStyle(); Font hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); // iterate through the java database,grabbing the details of the job. System.out.println("I can see contents of database"); while (resultSet.next()) { this.title = resultSet.getString("title"); this.link = resultSet.getString("link"); this.date = resultSet.getString("date"); System.out.println(this.title + " / " + this.link + " / " + this.date ); //append database Information onto Excel Row row = sheet.createRow(rowNumber); Cell titleCell = row.createCell(0); titleCell.setCellValue(title); Cell linkCell = row.createCell(1); linkCell.setCellValue(link); Cell dateCell = row.createCell(2); dateCell.setCellValue(date); //Make the link a clickable blue url. XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); hyperLink.setAddress(link); linkCell.setHyperlink(hyperLink); linkCell.setCellStyle(hlinkstyle); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i+=2) { sheet.autoSizeColumn(i); } selectStmt.close(); excelOutputStream = new FileOutputStream(excelFile); workbook.write(excelOutputStream); excelOutputStream.close(); System.out.println("Printed out " + dbTable); } catch (SQLException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex); } } */ public void appendDBTableToExcel(String dbTable) { rowNumber = 0; try { connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); if (file.exists()) { closeDBSession(); callExistingExcel(dbTable); } else { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx")); workbook.write(out); out.close(); System.out.println("exceldatabase.xlsx written successfully"); } } catch (SQLException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Main.Database.java
private void callExistingExcel(String dbTable) throws FileNotFoundException, IOException, SQLException { rowNumber = 0;//from w ww. ja v a 2 s. c o m connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); FileInputStream fIP = new FileInputStream(file); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); try { if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream("exceldatabase.xlsx"); workbook.write(out); out.close(); System.out.println("Successfully written"); } else { System.out.println("Error to open openworkbook.xlsx file."); } } catch (IllegalArgumentException ex) { } }
From source file:minor.Bill.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();//from w ww .j a v a 2 s . c o m //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4) }); } //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/bill.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.dbook.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();/*from w ww.j a v a 2 s . c o m*/ //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4) }); } //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/daybook.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.UpdateMenu.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();//ww w . j ava 2s . co m //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2) }); } //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/menurecords.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:minor.UpdateRecords.java
private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();/*from w ww.j a v a2 s . c om*/ //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4), model.getColumnName(5), model.getColumnName(6), model.getColumnName(7), model.getColumnName(8), model.getColumnName(9) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4), getcellvalue(i, 5), getcellvalue(i, 6), getcellvalue(i, 7), getcellvalue(i, 8), getcellvalue(i, 9) }); } //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/employeerecords.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }
From source file:mvjce.Excel_operations.java
public static void fill_exceldata(XSSFWorkbook workbook, XSSFSheet spreadsheet) { try {//from ww w . j ava2 s .c om 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 www . j a va2 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 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); } }