Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

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

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:HSSFReadWrite.java

License:Apache License

/**
 * given a filename this outputs a sample sheet with just a set of
 * rows/cells.// w  ww . j ava  2 s  . c o  m
 */
private static void testCreateSampleSheet(String outputFilename) throws IOException {
    int rownum;
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = 0; rownum < 300; rownum++) {
        HSSFRow r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            HSSFCell c = r.createCell(cellnum);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell(cellnum + 1);
            c.setCellValue(new HSSFRichTextString("TEST"));
            // 50 characters divided by 1/20th of a point
            s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        }
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    HSSFRow r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        HSSFCell c = r.createCell(cellnum);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3));
    s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

    // end deleted sheet
    FileOutputStream out = new FileOutputStream(outputFilename);
    wb.write(out);
    out.close();
}

From source file:Main3.java

/**
 * @param args the command line arguments
 *//*from w ww. ja  v  a2 s .c o  m*/
public static void main(String[] args) throws FileNotFoundException, IOException {

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("columA.xls"));
    HSSFSheet sheet = wb.getSheetAt(1);

    removeRow(sheet, 3);

    for (int i = 3; i < 4000; i++) {
        HSSFRow origRow = sheet.getRow(i);
        if (rowIsEmpty(origRow)) {
            System.out.println(i);
            //removeRow(sheet, i);
        }
    }

    FileOutputStream out = new FileOutputStream("okay4.xls");
    wb.write(out);
    out.close();
}

From source file:adminDown.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    try {// w  ww. ja v a2s  .  co m
        FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=CRSTrainingDetails.xls");
        workbook.write(response.getOutputStream());
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:NewMain.java

/**
 * @param args the command line arguments
 */// w  w  w.  j a v  a2  s .  c  om
public static void main(String[] args) throws FileNotFoundException, IOException {

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook.xls"));
    HSSFSheet sheet = wb.getSheetAt(0);

    int written = 2;

    //2997
    for (int r = 2; r < 2997; r += 6) {
        HSSFCell cell1 = sheet.getRow(r).getCell(1);
        HSSFCell cell2 = sheet.getRow(r + 1).getCell(1);
        HSSFCell cell3 = sheet.getRow(r + 2).getCell(1);

        Contact c = new Contact(cell1, cell2, cell3);
        System.out.println(c);

        HSSFRow row = sheet.getRow(written);
        if (row == null)
            row = sheet.createRow(written);

        HSSFCell cellComp = row.createCell(4);
        cellComp.setCellValue(c.getCompany());

        HSSFCell cellAdd = row.createCell(5);
        cellAdd.setCellValue(c.getAddress());

        HSSFCell cellCity = row.createCell(6);
        cellCity.setCellValue(c.getCity());

        HSSFCell cellState = row.createCell(7);
        cellState.setCellValue(c.getState());

        HSSFCell cellZip = row.createCell(8);
        cellZip.setCellValue(c.getZip());

        HSSFCell cellPhone = row.createCell(9);
        cellPhone.setCellValue(c.getPhone());

        HSSFCell cellSite = row.createCell(10);
        cellSite.setCellValue(c.getSite());

        written++;

    }

    FileOutputStream out = new FileOutputStream("okay.xls");
    wb.write(out);
    out.close();

}

From source file:erp_frame.java

 private void btnExportMouseClicked(java.awt.event.MouseEvent evt) {
   String outputFile = "D:/ERPOutputFile/" + path + ".xls";
   switch (path) {
   case "":
      fields = employeeFields;//from   w ww. jav  a  2 s  . co m
      break;
   case "":
      fields = attendanceFields;
      break;
   case "":
      fields = achivevmentFields;
      break;
   case "":
      fields = payRollFields;
      break;
   case "":
      fields = materialFields;
      break;
   case "?":
      fields = productFields;
      break;
   case "":
      fields = memberFields;
      break;
   case "":
      fields = orderListFields;
      break;
   case "":
      fields = orderItemFields;
      break;
   case "":
      fields = issueFields;
      break;
   case "":
      fields = vendorFields;
      break;
   case "?":
      fields = adminFields;
      break;
   case "":
      fields = purchaseFields;
      break;
   case "?":
      fields = payableFields;
      break;
   case "?":
      fields = assetFields;
      break;
   case "?":
      fields = billboardFields;
      break;
   case "?":
      fields = departFields;
      break;

   default:
      JOptionPane.showMessageDialog(JToolBar, "?");
      break;
   }
   try {
      // Create a excel file
      HSSFWorkbook workbook = new HSSFWorkbook();
      // Create a sheet with name
      HSSFSheet sheet = workbook.createSheet(path);
      HSSFRow row = null;
      HSSFCell cell = null;
      // set the sheet row count and set the first row data with title
      for (int i = 0; i < table_firmData.getRowCount() + 1; i++) {
         if (i == 0) {
            row = sheet.createRow((short) i);
            for (int k = 0; k < fields.length; k++) {
               cell = row.createCell((short) k);
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               cell.setCellValue(fields[k]);
            }
         } else {
            // Insert table data in next row
            row = sheet.createRow((short) i);
            for (int k = 0; k < fields.length; k++) {
               cell = row.createCell((short) k);
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               cell.setCellValue((String) table_firmData.getValueAt(i - 1, k));
            }

         }
      }
      FileOutputStream fOut = new FileOutputStream(outputFile);
      workbook.write(fOut);
      fOut.flush();
      fOut.close();
      JOptionPane.showMessageDialog(JToolBar, "?!\n ? :" + outputFile);
   } catch (Exception ee) {
      JOptionPane.showMessageDialog(JToolBar, " : " + ee.getMessage());
      System.out.println(ee.toString());
   }
}

From source file:Main2.java

/**
 * @param args the command line arguments
 *//*from  w w  w  .j  a  v a2  s.  co m*/
public static void main(String[] args) throws FileNotFoundException, IOException {

    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook2.xls"));
    HSSFSheet sheet = wb.getSheetAt(1);

    //Advanced Imaging of Port Charlotte 2625 Tamiami Trail, Unit 1 Port Charlotte, FL 33952 941-235-4646 
    for (int r = 0; r < 3815; r++) {
        HSSFRow row = sheet.getRow(r);
        if (row == null)
            continue;

        HSSFCell cell = row.getCell(0);
        if (cell == null)
            continue;

        String parse = cell.getStringCellValue().trim();

        if (parse.length() == 0)
            continue;

        //Check if the last character is a digit
        if (Character.isDigit(parse.charAt(parse.length() - 1)) || parse.charAt(parse.length() - 1) == ')') {
            int stop = parse.length() - 1;
            char stopChar = parse.charAt(stop);

            while (stopChar != ' ') {
                stop--;

                if (stop == -1)
                    break;

                stopChar = parse.charAt(stop);
            }
            String number = parse.substring(stop + 1);

            //If it's a phone number
            if (number.length() > 11) {
                //HSSFCell cellPhone = row.createCell(8);
                //cellPhone.setCellValue(number);

                //Now search for the zip
                int stopZip = stop - 1;
                char stopCharZip = parse.charAt(stopZip);

                if (Character.isDigit(stopCharZip)) {
                    //Cycle through the string backwards until you find a space
                    while (stopCharZip != ' ') {
                        stopZip--;
                        if (stopZip == -1)
                            continue;
                        stopCharZip = parse.charAt(stopZip);
                    }

                    //Write down the zip in the correct spot
                    String zipNumber = parse.substring(stopZip + 1, stop);
                    HSSFCell cellZip = row.createCell(6);
                    cellZip.setCellValue(zipNumber);
                    System.out.println((cellZip == null) + " " + zipNumber);

                }

                String state = parse.substring(stopZip - 2, stopZip);
                //HSSFCell cellState = row.createCell(6);
                //cellState.setCellValue(state);
            }

        }

        //Find the string before the first dash
        int firstDash = parse.indexOf('-');

        if (firstDash != -1) {
            String preDash = parse.substring(0, firstDash).trim();
            boolean noDigits = true;

            for (int i = 0; i < 10; i++) {
                if (preDash.contains(Integer.toString(i))) {
                    noDigits = false;
                }
            }

            if (noDigits && preDash.contains(" ")) {
                HSSFCell cellComp = row.createCell(2);
                //cellComp.setCellValue(preDash);
            }

        } else {
            int stopNum = 0;
            char stopCharNum = parse.charAt(stopNum);

            while (stopNum < parse.length() - 1 && !Character.isDigit(stopCharNum)) {
                stopNum++;
                stopCharNum = parse.charAt(stopNum);
            }

            String possTitle = parse.substring(0, stopNum);

            if (!possTitle.contains(",") && possTitle.length() >= 8) {
                HSSFCell cellComp = row.createCell(2);
                //cellComp.setCellValue(possTitle);
            }
        }

    }

    FileOutputStream out = new FileOutputStream("okay3.xls");
    wb.write(out);
    out.close();

    //        2997
    //        for (int r = 2; r < 2997 ; r += 6)
    //        {
    //            HSSFCell cell1 = sheet.getRow(r).getCell(1);
    //            HSSFCell cell2 = sheet.getRow(r + 1).getCell(1);
    //            HSSFCell cell3 = sheet.getRow(r + 2).getCell(1);
    //            
    //            Contact c = new Contact(cell1, cell2, cell3);
    //            System.out.println(c);
    //            
    //            HSSFRow row = sheet.getRow(written);
    //            if (row == null)
    //                row = sheet.createRow(written);
    //            
    //            HSSFCell cellComp = row.createCell(4);
    //            cellComp.setCellValue(c.getCompany());
    //            
    //            HSSFCell cellAdd = row.createCell(5);
    //            cellAdd.setCellValue(c.getAddress());
    //            
    //            HSSFCell cellCity = row.createCell(6);
    //            cellCity.setCellValue(c.getCity());
    //            
    //            HSSFCell cellState = row.createCell(7);
    //            cellState.setCellValue(c.getState());
    //            
    //            HSSFCell cellZip = row.createCell(8);
    //            cellZip.setCellValue(c.getZip());
    //            
    //            HSSFCell cellPhone = row.createCell(9);
    //            cellPhone.setCellValue(c.getPhone());
    //            
    //            HSSFCell cellSite = row.createCell(10);
    //            cellSite.setCellValue(c.getSite());
    //            
    //            written++;
    //            
    //        }
    //        
    //        FileOutputStream out = new FileOutputStream("okay.xls");
    //        wb.write(out);
    //        out.close();

}

From source file:TestUtil.java

License:BSD License

public void extractToExcel(File _xcelFile, Document doc) {
    try {/*from w w  w.ja  v  a 2 s . c o m*/
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(_xcelFile));

        NodeList cdeIds = doc.getElementsByTagName("ns1:Question");
        List<Node[]> cdeNodes = new ArrayList<Node[]>();
        List<Node[]> decNodes = new ArrayList<Node[]>();
        List<Node[]> vdNodes = new ArrayList<Node[]>();
        List<Node[]> cdNodes = new ArrayList<Node[]>();
        List<Node[]> conNodes = new ArrayList<Node[]>();

        for (int i = 0; i < cdeIds.getLength(); i++) {
            Node node = cdeIds.item(i);
            NodeList quesNodes = node.getChildNodes();
            Node[] cdeNodeArr = null;
            Node[] decNodeArr = null;
            Node[] vdNodeArr = null;
            Node[] cdNodeArr = null;
            Node[] conNodeArr = null;

            for (int j = 0; j < quesNodes.getLength(); j++) {
                Node quesNode = quesNodes.item(j);
                String quesNodeName = quesNode.getNodeName();

                if (quesNodeName.equalsIgnoreCase("ns1:cdeId")) {
                    if (cdeNodeArr == null) {
                        cdeNodeArr = new Node[4];
                        cdeNodes.add(cdeNodeArr);
                    }
                    cdeNodeArr[0] = quesNode;
                } else if (quesNodeName.equalsIgnoreCase("ns1:cdeLongName") && cdeNodeArr != null) {
                    cdeNodeArr[1] = quesNode;
                } else if (quesNodeName.equalsIgnoreCase("ns1:decId")) {
                    if (decNodeArr == null) {
                        decNodeArr = new Node[2];
                        decNodes.add(decNodeArr);
                    }
                    decNodeArr[0] = quesNode;

                    if (cdeNodeArr == null) {
                        cdeNodeArr = new Node[4];
                        cdeNodes.add(cdeNodeArr);
                    }
                    cdeNodeArr[2] = quesNode;
                }

                else if (quesNodeName.equalsIgnoreCase("ns1:vdId")) {
                    if (vdNodeArr == null) {
                        vdNodeArr = new Node[4];
                        vdNodes.add(vdNodeArr);
                    }
                    vdNodeArr[0] = quesNode;

                    if (cdeNodeArr == null) {
                        cdeNodeArr = new Node[4];
                        cdeNodes.add(cdeNodeArr);
                    }
                    cdeNodeArr[3] = quesNode;
                }

                else if (quesNodeName.equalsIgnoreCase("ns1:dataType") && vdNodeArr != null) {

                    vdNodeArr[1] = quesNode;
                }

                else if (quesNodeName.equalsIgnoreCase("ns1:enumerated") && vdNodeArr != null) {
                    vdNodeArr[2] = quesNode;
                }

                else if (quesNodeName.equalsIgnoreCase("ns1:decConceptualDomainId")
                        || quesNodeName.equalsIgnoreCase("ns1:vdConceptualDomainId")) {
                    cdNodeArr = new Node[1];
                    cdNodes.add(cdNodeArr);

                    cdNodeArr[0] = quesNode;

                    if (quesNodeName.equalsIgnoreCase("ns1:decConceptualDomainId")) {
                        if (decNodeArr == null) {
                            decNodeArr = new Node[2];
                            decNodes.add(decNodeArr);
                        }
                        decNodeArr[1] = quesNode;
                    } else if (quesNodeName.equalsIgnoreCase("ns1:vdConceptualDomainId")) {
                        if (vdNodeArr == null) {
                            vdNodeArr = new Node[4];
                            vdNodes.add(vdNodeArr);
                        }
                        vdNodeArr[3] = quesNode;
                    }
                }

                else if (quesNodeName.equalsIgnoreCase("ns1:ocPrimConcepts")
                        || quesNodeName.equalsIgnoreCase("ns1:propPrimConcepts")
                        || quesNodeName.equalsIgnoreCase("ns1:repTermQualConcepts")
                        || quesNodeName.equalsIgnoreCase("ns1:repTermPrimConcepts")
                        || quesNodeName.equalsIgnoreCase("ns1:vmConcepts")) {

                    conNodeArr = new Node[1];
                    conNodes.add(conNodeArr);
                    conNodeArr[0] = quesNode;
                }
            }

        }
        writeCon(conNodes, wb);
        writeCD(cdNodes, wb);
        writeVD(vdNodes, wb);
        writeDEC(decNodes, wb);
        writeDE(cdeNodes, wb);

        NodeList csNodes = doc.getElementsByTagName("ns1:cs");
        if (csNodes.getLength() > 0) {
            writeCS(csNodes.item(0), wb);
        } else {
            throw new Exception("No CS defined!!");
        }

        NodeList csiNodes = doc.getElementsByTagName("ns1:csi");
        if (csiNodes.getLength() > 0) {
            writeCSI(csiNodes.item(0), wb);
        } else {
            throw new Exception("No CSI defined!!");
        }
        wb.write(new FileOutputStream(_xcelFile));
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:Console.java

static public void exportToExcel(String sheetName, ArrayList headers, ArrayList data, File outputFile)
        throws HPSFException {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);

    int rowIdx = 0;
    short cellIdx = 0;

    // Header//from  w w w .  java2 s.  co  m
    HSSFRow hssfHeader = sheet.createRow(rowIdx);
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    for (Iterator cells = headers.iterator(); cells.hasNext();) {
        HSSFCell hssfCell = hssfHeader.createCell(cellIdx++);
        hssfCell.setCellStyle(cellStyle);
        hssfCell.setCellValue((String) cells.next());
    }
    // Data
    rowIdx = 1;
    for (Iterator rows = data.iterator(); rows.hasNext();) {
        ArrayList row = (ArrayList) rows.next();
        HSSFRow hssfRow = sheet.createRow(rowIdx++);
        cellIdx = 0;
        for (Iterator cells = row.iterator(); cells.hasNext();) {
            HSSFCell hssfCell = hssfRow.createCell(cellIdx++);
            Object o = cells.next();
            if ("class java.lang.Double".equals(o.getClass().toString())) {
                hssfCell.setCellValue((Double) o);
            } else {
                hssfCell.setCellValue((String) o);
            }

        }
    }

    wb.setSheetName(0, sheetName);
    try {
        FileOutputStream outs = new FileOutputStream(outputFile);
        wb.write(outs);
        outs.close();
        //            System.out.println("Archivo creado correctamente en " + outputFile.getAbsolutePath());
    } catch (IOException e) {
        e.printStackTrace();
        throw new HPSFException(e.getMessage());
    }
}

From source file:action.AdminAction.java

public String viewCustomerReport() throws IOException {
    LoginDao loginDao = new LoginDao();

    List<Customer> customerList = new ArrayList<Customer>();

    HttpServletRequest request = ServletActionContext.getRequest();
    HttpServletResponse response = ServletActionContext.getResponse();
    customerList = loginDao.findAll();//  w w  w . ja va 2 s  . c  o m

    HttpSession session = request.getSession();
    HSSFWorkbook workBook = new HSSFWorkbook();
    AdminReport customerReport = new AdminReport();
    /** Date Time Format for Time Stamp */
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd_HH_mm");
    String dateFormat = simpleDateFormat.format(new Date());
    /**File Name for XLS File */
    String fileName = "Customer_" + dateFormat;

    // excelWriter.inventoryExport(lst, workBook, request);
    customerReport.customerExport(customerList, workBook, request);
    response.setContentType("application/vnd.ms-excel");
    /** Change attachment into inline, inorder to open the excel file in Readonly mode */
    response.setHeader("Content-disposition", "inline;filename=" + fileName + ".xls");

    workBook.write(response.getOutputStream());
    response.getOutputStream().close();

    return SUCCESS;

}

From source file:action.AdminAction.java

public String viewOrdersReport() throws IOException {
    TransactionDao transactionDao = new TransactionDao();
    List<CustomerOrder> orderList = new ArrayList<CustomerOrder>();
    CustomerOrder order;//from   w  w  w .ja v a  2 s. c om
    HttpServletRequest request = ServletActionContext.getRequest();
    HttpServletResponse response = ServletActionContext.getResponse();
    orderList = transactionDao.findAll();
    HSSFWorkbook workBook = new HSSFWorkbook();
    AdminReport customerReport = new AdminReport();
    /** Date Time Format for Time Stamp */
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd_HH_mm");
    String dateFormat = simpleDateFormat.format(new Date());
    /**File Name for XLS File */
    String fileName = "Order_" + dateFormat;

    // excelWriter.inventoryExport(lst, workBook, request);
    customerReport.orderExport(orderList, workBook, request);
    response.setContentType("application/vnd.ms-excel");
    /** Change attachment into inline, inorder to open the excel file in Readonly mode */
    response.setHeader("Content-disposition", "inline;filename=" + fileName + ".xls");

    workBook.write(response.getOutputStream());
    response.getOutputStream().close();

    return SUCCESS;
}