List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:jp.ac.tohoku.ecei.sb.metabolome.lims.ExcelDataLoader.java
License:Open Source License
private static void copyCSV2Excel(Path tempDir, XSSFWorkbook workbook, String one) throws IOException { String sheetName = one;/* w w w . java 2 s. c om*/ if (sheetName.endsWith(".csv")) sheetName = one.substring(0, one.length() - 4); ExcelSheetWriter sheetWriter = new ExcelSheetWriter(workbook.createSheet(sheetName)); sheetWriter.setPrettyTable(true); sheetWriter.setAutoFilter(true); sheetWriter.setAutoResizeColumn(true); try (FileReader reader = new FileReader(new File(tempDir.toFile(), one))) { TableCSVReader csvReader = new TableCSVReader(reader); for (TableRecord record : csvReader) { sheetWriter.printRecord(Stream.of((Object[]) record.getContent()).map(x -> { Matcher matcher = doublePattern.matcher(x.toString()); if (matcher.matches()) { return Double.parseDouble(x.toString()); } return x; }).toArray()); } } try { sheetWriter.close(); } catch (Exception e) { throw new IOException(e); } }
From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryStats.java
License:Apache License
private static void writeexcel(String cb, List<Filebean> list, File outcvs, File outexcel1, boolean bfilter, String gr, long caplength, int minrecurrent, String hg) { // register to memory DB try {/*from w w w .j a v a 2 s .c om*/ XSSFWorkbook wb = new XSSFWorkbook(); CellStyle cs1 = getCS(wb, 3); CellStyle cs2 = getCS(wb, 2); CellStyle cs3 = getCS(wb, 1); CellStyle[] csa = new CellStyle[] { cs1, cs2, cs3 }; SummaryDB sb = new SummaryDB(outcvs); if (gr != null) { sb.loadRef(new File(gr), "generef"); } DataReader dr = new DataReader(list); XSSFSheet statsheet = wb.createSheet("AA_cahnge_mutation_by_gene"); sb.geneStat(dr, statsheet, bfilter, csa, true, wb, caplength, false, minrecurrent, false); XSSFSheet statsheetamphd = wb.createSheet("AA_cahnge_mutation_by_gene(AmpHD)"); sb.geneStat(dr, statsheetamphd, bfilter, csa, true, wb, caplength, true, minrecurrent, false); XSSFSheet statsheet_all = wb.createSheet("all_mutation_by_gene"); sb.geneStat(dr, statsheet_all, bfilter, csa, false, wb, caplength, true, minrecurrent, false); // XSSFSheet mutationstats = wb.createSheet("mutation_type"); int colcnt = sb.mutationStat(mutationstats, 0, true, bfilter); sb.mutationStat(mutationstats, colcnt + 5, false, bfilter); if (hg != null) { XSSFSheet mutationsig = wb.createSheet("mutation_signature"); sb.mutationSig(mutationsig, hg); } // XSSFSheet tumorcontents = wb.createSheet("tumor_contents_ratio"); DataWriter.writeTr(dr, tumorcontents, sb.getSampleL()); ChromBand cband = null; if (cb != null) { cband = new ChromBand(cb); } XSSFSheet statsheet_HD_amp = wb.createSheet("HD_amp"); sb.writeHDAMP(dr, statsheet_HD_amp, cband); // XSSFSheet cnvlist = wb.createSheet("cnvlist"); DataWriter.writeCNV(dr, cnvlist, sb.getSampleL(), 1); XSSFSheet cnvlistal = wb.createSheet("cnvlistAllelic"); DataWriter.writeCNV(dr, cnvlistal, sb.getSampleL(), 2); if (cb != null) { // XSSFSheet cnvcblist = wb.createSheet("cnvchrbandlist"); DataWriter.writeCNVCB(dr, cnvcblist, sb.getSampleL(), cband, csa, false); XSSFSheet cnvcblistlow = wb.createSheet("cnvchrbandlist(low)"); DataWriter.writeCNVCBAL(dr, cnvcblistlow, sb.getSampleL(), cband, csa, false); XSSFSheet cnvcblisthigh = wb.createSheet("cnvchrbandlist(high)"); DataWriter.writeCNVCBAL(dr, cnvcblisthigh, sb.getSampleL(), cband, csa, true); XSSFSheet cnvcblistf = wb.createSheet("focal_cnvchrbandlist"); DataWriter.writeCNVCB(dr, cnvcblistf, sb.getSampleL(), cband, csa, true); cband.close(); } XSSFSheet rstat = wb.createSheet("readstats"); DataWriter.writeReadsStats(dr, rstat, sb.getSampleL()); sb.close(); FileOutputStream out = new FileOutputStream(outexcel1); wb.write(out); out.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryStatsVaridate.java
License:Apache License
private static void writeexcel(String cb, List<Filebean> list, File outcvs, File outexcel1, boolean bfilter, String gr, long caplength, int minrecurrent, String hg) { // register to memory DB try {/*w w w . j a va 2 s .co m*/ XSSFWorkbook wb = new XSSFWorkbook(); CellStyle cs1 = getCS(wb, 3); CellStyle cs2 = getCS(wb, 2); CellStyle cs3 = getCS(wb, 1); CellStyle[] csa = new CellStyle[] { cs1, cs2, cs3 }; SummaryDB sb = new SummaryDB(outcvs); if (gr != null) { sb.loadRef(new File(gr), "generef"); } DataReader dr = new DataReader(list); XSSFSheet statsheet = wb.createSheet("AA_cahnge_mutation_by_gene"); sb.geneStat(dr, statsheet, bfilter, csa, true, wb, caplength, false, minrecurrent, true); XSSFSheet statsheetamphd = wb.createSheet("AA_cahnge_mutation_by_gene(AmpHD)"); sb.geneStat(dr, statsheetamphd, bfilter, csa, true, wb, caplength, true, minrecurrent, true); XSSFSheet statsheet_all = wb.createSheet("all_mutation_by_gene"); sb.geneStat(dr, statsheet_all, bfilter, csa, false, wb, caplength, true, minrecurrent, true); // XSSFSheet mutationstats = wb.createSheet("mutation_type"); int colcnt = sb.mutationStat(mutationstats, 0, true, bfilter); sb.mutationStat(mutationstats, colcnt + 5, false, bfilter); if (hg != null) { XSSFSheet mutationsig = wb.createSheet("mutation_signature"); sb.mutationSig(mutationsig, hg); } // XSSFSheet tumorcontents = wb.createSheet("tumor_contents_ratio"); DataWriter.writeTr(dr, tumorcontents, sb.getSampleL()); ChromBand cband = null; if (cb != null) { cband = new ChromBand(cb); } XSSFSheet statsheet_HD_amp = wb.createSheet("HD_amp"); sb.writeHDAMP(dr, statsheet_HD_amp, cband); // XSSFSheet cnvlist = wb.createSheet("cnvlist"); DataWriter.writeCNV(dr, cnvlist, sb.getSampleL(), 1); XSSFSheet cnvlistal = wb.createSheet("cnvlistAllelic"); DataWriter.writeCNV(dr, cnvlistal, sb.getSampleL(), 2); if (cb != null) { // XSSFSheet cnvcblist = wb.createSheet("cnvchrbandlist"); DataWriter.writeCNVCB(dr, cnvcblist, sb.getSampleL(), cband, csa, false); XSSFSheet cnvcblistlow = wb.createSheet("cnvchrbandlist(low)"); DataWriter.writeCNVCBAL(dr, cnvcblistlow, sb.getSampleL(), cband, csa, false); XSSFSheet cnvcblisthigh = wb.createSheet("cnvchrbandlist(high)"); DataWriter.writeCNVCBAL(dr, cnvcblisthigh, sb.getSampleL(), cband, csa, true); XSSFSheet cnvcblistf = wb.createSheet("focal_cnvchrbandlist"); DataWriter.writeCNVCB(dr, cnvcblistf, sb.getSampleL(), cband, csa, true); cband.close(); } XSSFSheet rstat = wb.createSheet("readstats"); DataWriter.writeReadsStats(dr, rstat, sb.getSampleL()); sb.close(); FileOutputStream out = new FileOutputStream(outexcel1); wb.write(out); out.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:jpgtoxlsx.JPGtoXLSX.java
/** * @param args the command line arguments *//*from www . j a v a2 s . co m*/ public static void main(String[] args) throws Exception { XSSFWorkbook myExcel = new XSSFWorkbook(); XSSFSheet sheet = myExcel.createSheet("Image"); BufferedImage image = null; int width, height; int xlrows; //int pixel; File fimg; //open image try { fimg = new File("C:\\excel\\Test.jpg"); image = ImageIO.read(fimg); } catch (IOException e) { System.out.println(e); } width = image.getWidth(); //System.out.println(width); height = image.getHeight(); //System.out.println(height); xlrows = height * 3; //System.out.println(pixel); int r, g, b; int w = image.getWidth(); int h = image.getHeight(); System.out.println("Width: " + w + "Height: " + h); System.out.println("Generating RGB values.."); XSSFCellStyle style1 = myExcel.createCellStyle(); XSSFCellStyle style2 = myExcel.createCellStyle(); XSSFCellStyle style3 = myExcel.createCellStyle(); for (int i = 1; i < ++h; i++) { if (i == image.getHeight()) { break; } XSSFRow row1 = sheet.createRow((i * 3 - 3)); XSSFRow row2 = sheet.createRow((i * 3 - 2)); XSSFRow row3 = sheet.createRow((i * 3 - 1)); for (int j = 0; j < ++w; j++) { if (j == image.getWidth()) { break; } //System.out.println("I: " + i); //System.out.println("J: " + j + "\n"); int x = i; int y = j; //System.out.println("X: " + x + "Y: " + y + "\r"); //System.out.println("Y: " + y); int pixel = image.getRGB(y, x); r = (pixel >> 16) & 0xff; g = (pixel >> 8) & 0xff; b = (pixel) & 0xff; XSSFCell cell1 = row1.createCell(y); XSSFCell cell2 = row2.createCell(y); XSSFCell cell3 = row3.createCell(y); cell1.setCellValue(Integer.toString(r)); cell2.setCellValue(Integer.toString(g)); cell3.setCellValue(Integer.toString(b)); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(r, 0, 0))); ; style1.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, g, 0))); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style3.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 0, b))); style3.setFillPattern(CellStyle.SOLID_FOREGROUND); cell1.setCellStyle(style1); cell2.setCellStyle(style2); cell3.setCellStyle(style3); //System.out.println("x,y: " + j + ", " + i); //System.out.println("R: " + r + " G: " + g + " B: " + b + "\n"); } } System.out.println("RGB values extracted."); System.out.println("Generating image"); myExcel.write(new FileOutputStream("excel.xlsx")); myExcel.close(); }
From source file:kp.servlet.ExportRpt.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// www.j a v a2s .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:listfiles.ListFiles.java
/** * @param args the command line arguments */// ww w.j av a2 s . c o m public static void main(String[] args) { // TODO code application logic here BufferedReader reader = new BufferedReader(new InputStreamReader(System.in)); String folderPath = ""; String fileName = "DirectoryFiles.xlsx"; try { System.out.println("Folder path :"); folderPath = reader.readLine(); //System.out.println("Output File Name :"); //fileName = reader.readLine(); XSSFWorkbook wb = new XSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream(folderPath + "\\" + fileName); XSSFSheet sheet1 = wb.createSheet("Files"); int row = 0; Stream<Path> stream = Files.walk(Paths.get(folderPath)); Iterator<Path> pathIt = stream.iterator(); String ext = ""; while (pathIt.hasNext()) { Path filePath = pathIt.next(); Cell cell1 = checkRowCellExists(sheet1, row, 0); Cell cell2 = checkRowCellExists(sheet1, row, 1); row++; ext = FilenameUtils.getExtension(filePath.getFileName().toString()); cell1.setCellValue(filePath.getFileName().toString()); cell2.setCellValue(ext); } sheet1.autoSizeColumn(0); sheet1.autoSizeColumn(1); wb.write(fileOut); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Program Finished"); }
From source file:localization.checkURL.java
public static void check(String desktopFile, String serverFile, String inputFolder) { try {/*from w ww .ja v a 2s . co m*/ String desktopFolder = desktopFile.substring(desktopFile.lastIndexOf("\\") + 1, desktopFile.length()); desktop = desktopFolder.substring(0, desktopFolder.indexOf("_")); String serverFolder = serverFile.substring(serverFile.lastIndexOf("\\") + 1, serverFile.length()); server = serverFolder.substring(0, serverFolder.indexOf("_")); pubList = new ArrayList<>(); searchFile(inputFolder); String parFolder = inputFolder.substring(0, inputFolder.lastIndexOf("\\")); HOName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.lastIndexOf("\\") + 5); lang = inputFolder.substring(inputFolder.lastIndexOf("\\") + 1, inputFolder.length()); outputFileName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.length()); outputFilePath = parFolder + "\\" + outputFileName + "_" + lang + ".xlsx"; valueArray = new String[pubList.size() + 1][6]; valueArray[0][0] = "Language"; valueArray[0][1] = "HO#"; valueArray[0][2] = "Publication Name"; valueArray[0][3] = "Type"; valueArray[0][4] = "Topic Name"; valueArray[0][5] = "URL"; for (int i = 0; i < pubList.size(); i++) { String fullPath = pubList.get(i); valueArray[i + 1][0] = lang.toUpperCase().trim(); valueArray[i + 1][1] = HOName.trim(); valueArray[i + 1][4] = fullPath.substring(fullPath.lastIndexOf("\\") + 1, fullPath.length()); if (fullPath.contains("\\topic\\")) { valueArray[i + 1][3] = "topic"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\topic\\")).trim(); } else if (fullPath.contains("\\map\\")) { valueArray[i + 1][3] = "map"; valueArray[i + 1][2] = fullPath .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\map\\")).trim(); } } /* for(int i = 0; i < valueArray.length; i++){ for(int j = 0; j < valueArray[i].length; j++){ System.out.print(valueArray[i][j] + ","); } System.out.println(); }*/ File inputDesktopFile = new File(desktopFile); File inputServerFile = new File(serverFile); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputDesktopFile)); XSSFSheet mysheet = workbook.getSheetAt(0); int desktopRowNum = mysheet.getLastRowNum(); XSSFWorkbook serverWorkbook = new XSSFWorkbook(new FileInputStream(inputServerFile)); XSSFSheet serverSheet = serverWorkbook.getSheetAt(0); int serverRowNum = serverSheet.getLastRowNum(); for (int k = 1; k < valueArray.length; k++) { //System.out.println(valueArray[k][3]); if (valueArray[k][3].equals("topic") && (!valueArray[k][4].trim().startsWith("cfg"))) { //System.out.println(k + " " +valueArray[k][3]); String source = valueArray[k][4].trim(); for (int i = 1; i < desktopRowNum + 1; i++) { Row desktopRow = mysheet.getRow(i); if (desktopRow != null) { String targetString = desktopRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString)) { String desktopURL = desktopRow.getCell(0).getStringCellValue().trim(); desktopURL = desktopURL.replace(".com/en\\", ".com/" + lang.toLowerCase() + "\\"); desktopURL = desktopURL.replace("http://" + desktop, "http://" + desktop + "uat"); valueArray[k][5] = desktopURL; } } } for (int j = 0; j < serverRowNum + 1; j++) { Row serverRow = serverSheet.getRow(j); if (serverRow != null) { String targetString1 = serverRow.getCell(2).getStringCellValue().trim(); if (source.contains(targetString1)) { String serverURL = serverRow.getCell(0).getStringCellValue().trim(); serverURL = serverURL.replace("/en\\", "/" + lang.toLowerCase() + "\\"); serverURL = serverURL.replace("http://" + server, "http://" + server + "uat"); // System.out.println(serverURL); if (valueArray[k][5] != null) { valueArray[k][5] = valueArray[k][5] + "\n" + serverURL; } else { valueArray[k][5] = serverURL; } } } } } XSSFWorkbook outputworkbook = new XSSFWorkbook(); XSSFSheet outputsheet = outputworkbook.createSheet("sheet1"); XSSFCellStyle outputstyle = outputworkbook.createCellStyle(); outputstyle.setWrapText(true); int outputRowNum = 0; int outputCellNum = 0; for (int i = 0; i < valueArray.length; i++) { Row outputRow = outputsheet.createRow(outputRowNum++); for (int j = 0; j < valueArray[1].length; j++) { Cell outputCell = outputRow.createCell(outputCellNum++); if (valueArray[i][j] != null) { outputCell.setCellValue(valueArray[i][j]); } else { outputCell.setCellValue("N/A"); } if (j == 5) { //outputsheet.autoSizeColumn(4); outputCell.setCellStyle(outputstyle); } } outputCellNum = 0; } outputsheet.autoSizeColumn(2); outputsheet.autoSizeColumn(4); outputsheet.autoSizeColumn(5); FileOutputStream out = new FileOutputStream(new File(outputFilePath)); outputworkbook.write(out); out.close(); } } catch (Exception e) { try { File file = new File(userDir + "\\log.txt"); if (!file.exists()) { file.createNewFile(); } FileWriter fw = new FileWriter(file.getAbsoluteFile()); BufferedWriter bw = new BufferedWriter(fw); bw.write(e.getMessage()); bw.write(e.getLocalizedMessage()); bw.close(); fw.close(); } catch (Exception e1) { e1.printStackTrace(); } e.printStackTrace(); } }
From source file:Logic.RStoXL.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {// w w w .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<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 va2s.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:log_compressor.event_checker.java
public static void event_checker() throws IOException, InterruptedException { HashMap<String, List<String>> hm = new HashMap<String, List<String>>(); List<String> file = import_file("D:\\log\\input.txt"); ArrayList<Integer> server_position = new ArrayList<Integer>(); ArrayList<String> server_list = new ArrayList<String>(); File myFile = new File("D:\\log\\log_output.xlsx"); if (!myFile.exists()) { FileOutputStream fos = new FileOutputStream(myFile); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("sheet1"); workbook.write(fos);/*from ww w . j a v a2 s . com*/ } int k = 0; for (String s : file) { if (s.startsWith("Server Name")) { int i = s.lastIndexOf(":"); server_position.add(k); } k++; } for (int j = 0; j < server_position.size(); j++) { ArrayList<String> event = new ArrayList<String>(); String servername = file.get(server_position.get(j)); server_list.add(servername); if (j != server_position.size() - 1) { for (int l = server_position.get(j); l < server_position.get(j + 1); l++) { if (file.get(l).startsWith("Event")) { int eventPosition = file.get(l).lastIndexOf(":"); String eventId = file.get(l).substring(eventPosition + 1); event.add(eventId); } else if (file.get(l).startsWith("No critical")) { String eventId = "Normal"; event.add(eventId); } else if (file.get(l).startsWith("Cannot retrieve EVENT information")) { String eventId = "Need maual check"; event.add(eventId); } } } else { for (int l = server_position.get(server_position.size() - 1); l < file.size(); l++) { if (file.get(l).startsWith("Event")) { int eventPosition = file.get(l).lastIndexOf(":"); String eventId = file.get(l).substring(eventPosition + 1); event.add(eventId); } else if (file.get(l).startsWith("No critical")) { String eventId = "Normal"; event.add(eventId); } else if (file.get(l).startsWith("Cannot retrieve EVENT information")) { String eventId = "Need maual check"; event.add(eventId); } } } Set<String> s = new LinkedHashSet<>(event); List<String> event1 = new ArrayList<String>(); event1.addAll(s); hm.put(servername, event1); } write_xlsx(hm, server_list); for (String server : server_list) { System.out.println(" "); System.out.println(server); System.out.println(hm.get(server).toString().substring(1, hm.get(server).toString().length() - 1)); System.out.println("==============================="); } }