Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook write

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook write.

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

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 {//from   w  w  w.ja  v a2s .  c  o 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 w  w w .java  2 s  .com*/
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:ke.co.tawi.babblesms.server.servlet.export.excel.inbox.ExportExcel.java

License:Open Source License

/**
 * Returns MS Excel file of the data specified for exporting.
 * @param List<IncomingLog>//from  w  w w  .j  a  v  a2 s .  c  om
 * Method create excelSheets and sends them
 ****/
public void createExcelSheets(List<IncomingLog> InLog) throws IOException {
    List<Phone> phoneList;
    //String cont = null;

    XSSFWorkbook xf = new XSSFWorkbook();
    XSSFCreationHelper ch = xf.getCreationHelper();

    XSSFSheet s = xf.createSheet();
    //create the first row
    XSSFRow r1 = s.createRow(0);
    XSSFCell c11 = r1.createCell(0);
    c11.setCellValue(ch.createRichTextString("*"));
    XSSFCell c12 = r1.createCell(1);
    c12.setCellValue(ch.createRichTextString("Message"));
    XSSFCell c13 = r1.createCell(2);
    c13.setCellValue(ch.createRichTextString("Source"));
    XSSFCell c14 = r1.createCell(3);
    c14.setCellValue(ch.createRichTextString("Destination"));
    XSSFCell c15 = r1.createCell(4);
    c15.setCellValue(ch.createRichTextString("Network"));
    XSSFCell c16 = r1.createCell(5);
    c16.setCellValue(ch.createRichTextString("Time (" + timezoneFormatter.format(new Date()) + ") Time Zone"));
    XSSFCell c17 = r1.createCell(6);
    c17.setCellValue(ch.createRichTextString("Message Id"));

    int i = 1;
    //create other rows
    for (IncomingLog log : InLog) {
        phoneList = phnDAO.getPhones(log.getOrigin());

        XSSFRow r = s.createRow(i);
        //row number
        XSSFCell c1 = r.createCell(0);
        c1.setCellValue(i + pageno);

        //get message  
        XSSFCell c2 = r.createCell(1);
        c2.setCellValue(ch.createRichTextString(log.getMessage()));

        //get phone numbers
        XSSFCell c3 = r.createCell(2);
        if (phoneList.size() > 0) {
            for (Phone phone : phoneList) {
                Contact contacts = ctDAO.getContact(phone.getContactUuid());
                c3.setCellValue(ch.createRichTextString(contacts.getName()));
            }
        } else {
            c3.setCellValue(ch.createRichTextString(log.getOrigin()));
        }

        //get destination   
        XSSFCell c4 = r.createCell(3);
        c4.setCellValue(ch.createRichTextString(log.getDestination()));

        //get network name    
        XSSFCell c5 = r.createCell(4);
        c5.setCellValue(ch.createRichTextString(networkHash.get(log.getNetworkUuid())));

        //get date 
        XSSFCell c6 = r.createCell(5);
        c6.setCellValue(ch.createRichTextString("" + dateFormatter.format(log.getLogTime())));

        //get message id
        XSSFCell c7 = r.createCell(6);
        c7.setCellValue(ch.createRichTextString(log.getUuid()));
        i++;

    }
    xf.write(out);
    out.flush();
    out.close();
}

From source file:kp.servlet.ExportRpt.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from   w  w  w .j av  a  2  s  .  com
 *
 * @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
 *//*  w ww .j  av  a2 s  . c  om*/
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 w w  . ja v  a 2s  .  c om*/
        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:localization.excel.java

public static void convert(String filePath) {
    Vector<String> zFile;
    if (filePath.endsWith(".zip")) {
        zFile = readzipfile(filePath);/*from   w w w. j  av  a 2s.  com*/
        for (String s : zFile) {
            if (s.endsWith(".xlsx")) {
                //System.out.println(s);
                convert(s);
            }
        }
    } else if (!filePath.endsWith(".xlsx")) {
        return;
    } else {
        try {
            FileInputStream file = new FileInputStream(new File(filePath));
            System.out.println(filePath);
            //Get the workbook instance for XLS file 
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            rowNumber = sheet.getPhysicalNumberOfRows();
            try {
                for (int i = 0; i < rowNumber; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        int columnNum = row.getPhysicalNumberOfCells();
                        //System.out.println(columnNum);
                        for (int j = 0; j < columnNum; j++) {
                            cell = row.getCell(j);

                            if (j == 0) {
                                String name = cell.getRichStringCellValue().getString();
                                if (name.equalsIgnoreCase("Esri")) {
                                    langNumber++;
                                }
                                //System.out.println(name);
                            }
                        }
                        if (i == 3) {
                            cell = row.getCell(30);
                            XSSFCellStyle cs = cell.getCellStyle();
                            cell = row.createCell(32);
                            cell.setCellValue("Additional Charge per language");
                            cell.setCellStyle(cs);
                        }
                    }
                }
            } catch (Exception e) {

            }
            System.out.println(langNumber);
            double total = Double.parseDouble(sheet.getRow(langNumber + 3).getCell(29).getRawValue());

            double subTotal = total / langNumber;
            DecimalFormat df = new DecimalFormat("#.000");
            for (int i = 0; i < langNumber; i++) {
                cell = sheet.getRow(i + 4).createCell(32);
                cell.setCellValue("$" + df.format(subTotal));
            }

            file.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            workbook.write(outFile);
            outFile.close();
            rowNumber = 0;
            langNumber = 0;
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

From source file:logic.Export.java

public boolean convertXls()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;/*from ww w  . j av  a 2  s  .  c o m*/
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        //formate sheets
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        //setPrintArea 
        workbook.setPrintArea(i + 1, //sheet index
                0, //start column Spalte
                6, //end column
                0, //start row zeile
                convert.size() + 8 //end row
        );
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell);
                if (Cell == 3) {
                    if ("true".equals(convert.get(Row)[Cell])) {
                        XSSFCellStyle style1 = workbook.createCellStyle();
                        style1 = (XSSFCellStyle) cell.getCellStyle();
                        style1 = (XSSFCellStyle) style1.clone();
                        style1.setFillBackgroundColor(HSSFColor.RED.index);
                        style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                        XSSFColor myColor = new XSSFColor(Color.RED);
                        style1.setFillForegroundColor(myColor);
                        sheet.getRow(9 + Row).getCell(6).setCellStyle(style1);
                    }
                } else {
                    cell.setCellValue(convert.get(Row)[Cell]);
                }
            }
        }
    }

    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:logic.Export.java

public boolean convertXls2()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;/* ww  w .  java  2s. c o m*/
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell + 1);
                String name;
                switch (Cell) {
                case 3:
                    name = convert.get(Row)[Cell];
                    int parseInt = Integer.parseInt(name);
                    cell.setCellValue(parseInt);
                    cell.setCellType(CellType.NUMERIC);
                    break;
                case 4:
                    number = format.parse(convert.get(Row)[Cell]);
                    cell.setCellValue(number.doubleValue());
                    //  cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.NUMERIC);

                    break;
                default:
                    cell.setCellValue(convert.get(Row)[Cell]);
                    break;
                }
            }
        }

        for (String[] sa : convert) {
            number = format.parse(sa[4]);
            hours = hours + number.doubleValue();
        }
        cell = sheet.getRow(6).getCell(5);
        cell.setCellValue(hours);
        cell = sheet.getRow(2).getCell(8);
        XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8);
        double numericCellValue = cellHourlyRate.getNumericCellValue();
        cell.setCellValue(hours * numericCellValue);
    }
    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:Logic.RStoXL.java

public void genXLS(ResultSet rs, String Rpt_name, String path) {
    try {/*from   ww  w.  ja v  a 2s .  com*/
        //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);
    }
}