Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) {
    try {//from  w  w  w  .ja  va 2s  . c  o m
        String formatV = "";
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
            if (format.length >= col) {
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col]));
                //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellA1.setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRow(String sheetName, int row, String[] value) {
    try {/*from  w  ww  .  j a  va2  s .c  om*/
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addHeaderRow(String sheetName, String[] columnNames) {
    try {//from  w ww .ja  va2s . c o m
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        HSSFCellStyle cellStyle = setHeaderStyle(workbook);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(0);

        for (int col = 0; col < columnNames.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(columnNames[col]);
            cellA1.setCellStyle(cellStyle);
        }

        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.timesheet.export.ExcelExportView.java

@Override
protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook hssfw, HttpServletRequest hsr,
        HttpServletResponse hsr1) throws Exception {
    hsr1.setHeader("Pragma", "public");
    hsr1.setHeader("Expires", "0");
    hsr1.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
    hsr1.setHeader("Content-type", "application-download");
    hsr1.setHeader("Content-Disposition", "attachment; filename=bookings.xls");
    hsr1.setHeader("Content-Transfer-Encoding", "binary");

    HSSFSheet excelSheet = hssfw.createSheet("Time sheet report");

    HSSFRow excelHeader = excelSheet.createRow(0);
    excelHeader.createCell(0).setCellValue("Project");
    excelHeader.createCell(1).setCellValue("Option");
    excelHeader.createCell(2).setCellValue("Date");
    excelHeader.createCell(3).setCellValue("Duration");
    excelHeader.createCell(4).setCellValue("Description");

    List<Booking> bookings = (List<Booking>) map.get("bookings");
    int record = 1;

    for (Booking booking : bookings) {
        HSSFRow excelRow = excelSheet.createRow(record++);
        excelRow.createCell(0)/* w w w.  ja  va 2  s  .  c  om*/
                .setCellValue(booking.getProject().getProjectId() + "-" + booking.getProject().getName());
        excelRow.createCell(1).setCellValue(booking.getBookingOption().getOptionLabel());
        excelRow.createCell(2).setCellValue(booking.getBookingDate().toString());
        excelRow.createCell(3).setCellValue(booking.getDuration().toString());
        excelRow.createCell(4).setCellValue(booking.getDescription());
    }

}

From source file:com.tm.hiber.service.util.DatabaseUtil.java

/**
 *
 * @param objFile/*from w ww.j a va2 s.  c  o m*/
 * @param objJTable
 * @return number of records exported
 */
public int exportData(File objFile, JTable objJTable) throws FileNotFoundException, IOException {
    int response = 0;

    if (objFile == null) {
        return response;
    }

    DefaultTableModel tm = (DefaultTableModel) objJTable.getModel();
    Object[] rows = tm.getDataVector().toArray();

    JTableHeader columnNames = objJTable.getTableHeader();
    TableColumnModel columnModel = columnNames.getColumnModel();
    int columnCount = columnModel.getColumnCount();
    Vector<String> vecColumnNames = new Vector<String>();
    for (int c = 0; c < columnCount; c++) {
        vecColumnNames.add(columnModel.getColumn(c).getHeaderValue().toString());
    }

    HSSFWorkbook exportReadyWorkbook = new HSSFWorkbook();
    HSSFSheet dataSheet = exportReadyWorkbook.createSheet(getExportSheetName());

    if (vecColumnNames.size() > 0) {
        int columnCounter = 0;
        Row objHSSFColumnName = dataSheet.createRow(0);

        for (String strColumnName : vecColumnNames) {
            /* Set Header CSS */

            Cell objHSSFCell = objHSSFColumnName.createCell(columnCounter);
            objHSSFCell.setCellValue(strColumnName);
            CellStyle csll = exportReadyWorkbook.createCellStyle();
            Font objFont = exportReadyWorkbook.createFont();
            objFont.setFontName("Calibri");
            objFont.setColor(IndexedColors.BLACK.index);
            objFont.setBold(true);
            csll.setFont(objFont);
            csll.setFillBackgroundColor(HSSFColor.YELLOW.index);
            csll.setFillForegroundColor(HSSFColor.YELLOW.index);
            csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            csll.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //csll.setWrapText(true);
            objHSSFCell.setCellStyle(csll);
            columnCounter++;
        }
    }

    if (rows != null && rows.length > 0) {
        /* Set Data into Sheet */
        for (int i = 0; i < rows.length; i++) {
            Vector objCellsData = (Vector) rows[i];
            Row objHSSFRow = dataSheet.createRow(i + 1);
            if (objCellsData != null && objCellsData.size() > 0) {
                for (int j = 0; j < objCellsData.size(); j++) {

                    /* Set Cell Data CSS */

                    Cell objHSSFCell = objHSSFRow.createCell(j);
                    CellStyle csll = exportReadyWorkbook.createCellStyle();
                    Font objFont = exportReadyWorkbook.createFont();
                    objFont.setColor(IndexedColors.BLACK.index);
                    objFont.setBold(false);
                    objFont.setFontName("Calibri");
                    csll.setFont(objFont);
                    csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    csll.setFillBackgroundColor(IndexedColors.WHITE.index);
                    csll.setFillForegroundColor(IndexedColors.WHITE.index);

                    csll.setAlignment(HSSFCellStyle.ALIGN_CENTER);

                    //csll.setWrapText(true);
                    csll.setBorderBottom(CellStyle.BORDER_THIN);
                    csll.setBorderTop(CellStyle.BORDER_THIN);

                    csll.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index);
                    csll.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index);

                    objHSSFCell.setCellStyle(csll);
                    Object cellData = objCellsData.get(j);
                    objHSSFCell.setCellValue((String) cellData);
                }
            }
        }

        for (int i = 0; i < columnCount; i++) {
            if (i == 2) {
                dataSheet.setColumnWidth(i, 30 * 256);
            } else {
                dataSheet.autoSizeColumn(i);
            }
        }

        /* Write File */
        FileOutputStream objFileOutputStream = new FileOutputStream(objFile);
        exportReadyWorkbook.write(objFileOutputStream);
        objFileOutputStream.flush();
        objFileOutputStream.close();
        response = rows.length;
    }

    return response;
}

From source file:com.top10bestdatingsites.csv.XlsGeneration.java

public void generateXLS() {

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("Monster Details");
    HSSFRow rowhead = sheet.createRow((int) 0);
    rowhead.createCell((int) 0).setCellValue("S.No.");
    rowhead.createCell((int) 1).setCellValue("URL");
    rowhead.createCell((int) 2).setCellValue("HEADLINE");
    rowhead.createCell((int) 3).setCellValue("RATING");
    rowhead.createCell((int) 4).setCellValue("FULL_HTML");

    try {//from  w  ww  .ja  v a 2 s  . c o  m
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection con = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/top10bestdatingsites", "root", "");
        String sql = "SELECT * FROM  alldata;";
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int k = 0;
        while (rs.next()) {

            HSSFRow row = sheet.createRow((int) k + 2);
            try {
                row.createCell((int) 0).setCellValue(k + 1);
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 1).setCellValue(rs.getString("URL") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 2).setCellValue(rs.getString("HEADLINE") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 3).setCellValue(rs.getString("RATING") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 4).setCellValue(rs.getString("FULL_HTML") + "");
            } catch (Exception sd) {
            }

            k++;
        }

    } catch (Exception aaa) {
    }

    try {

        String filename = "data.csv";
        System.out.println("Directory is created!");
        FileOutputStream fileOut = new FileOutputStream(filename);
        hwb.write(fileOut);
        fileOut.close();
        System.out.println("Your excel file has been generated!");
    } catch (IOException iOException) {
    }

}

From source file:com.top10bestdatingsites.csv.XlsGenerationAdvice.java

public void generateXLS() {

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("Monster Details");
    HSSFRow rowhead = sheet.createRow((int) 0);
    rowhead.createCell((int) 0).setCellValue("S.No.");
    rowhead.createCell((int) 1).setCellValue("URL");
    rowhead.createCell((int) 2).setCellValue("HEADLINE");
    rowhead.createCell((int) 3).setCellValue("FULL_HTML");

    try {//from  w w  w . j ava 2 s . c om
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection con = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/top10bestdatingsites", "root", "");
        String sql = "SELECT * FROM  advices;";
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int k = 0;
        while (rs.next()) {

            HSSFRow row = sheet.createRow((int) k + 2);
            try {
                row.createCell((int) 0).setCellValue(k + 1);
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 1).setCellValue(rs.getString("URL") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 2).setCellValue(rs.getString("HEADING") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 3).setCellValue(rs.getString("FULL_HTML") + "");
            } catch (Exception sd) {
            }

            k++;
        }

    } catch (Exception aaa) {
    }

    try {

        String filename = "data_advice.csv";
        System.out.println("Directory is created!");
        FileOutputStream fileOut = new FileOutputStream(filename);
        hwb.write(fileOut);
        fileOut.close();
        System.out.println("Your excel file has been generated!");
    } catch (IOException iOException) {
    }

}

From source file:com.topsec.tsm.sim.asset.web.AssetListController.java

/**
 * /*w  w w.j av a  2 s .  c  o m*/
 * @param request
 * @param response
 */
@RequestMapping("exportAssetExcel")
public void exportAssetExcel(SID sid, HttpServletRequest request, HttpServletResponse response) {
    //?
    List<AssetObject> assetList;
    SID.setCurrentUser(sid);
    try {
        assetList = AssetFacade.getInstance().getAll();
        Collections.sort(assetList, IpComparator.getInstance());
    } finally {
        SID.removeCurrentUser();
    }
    String[] column = { "??(*)", "IP(*)", "??(*)",
            "(*)", "?(*)", "?(*)",
            "??", "?", "", null, null, null, null, "?", "??" };

    // Excel
    HSSFWorkbook workbook = new HSSFWorkbook();

    // Excel??Asset??
    HSSFSheet sheet = workbook.createSheet("?");
    //?
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    //
    HSSFRow row = sheet.createRow(0);
    for (int j = 0; j < column.length; j++) {
        HSSFCell cell = row.createCell(j);
        if (column[j] == null) {
            sheet.setColumnHidden(j, true);
            continue;
        }
        sheet.setColumnWidth(j, 22 * 256);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(column[j]);
    }
    NodeMgrFacade nodeMgrFacade = (NodeMgrFacade) SpringContextServlet.springCtx.getBean("nodeMgrFacade");
    //??
    for (int i = 0; i < assetList.size(); i++) {
        AssetObject ao = assetList.get(i);
        row = sheet.createRow(i + 1);
        for (int j = 0; j < column.length; j++) {
            HSSFCell cell = row.createCell(j);
            switch (j) {
            case 0:
                cell.setCellValue(i + 1);
                cell.setCellStyle(cellStyle);
                continue;
            case 1:
                cell.setCellValue(ao.getIp());
                continue;
            case 2:
                cell.setCellValue(ao.getName());
                continue;
            case 3:
                String deviceType = ao.getDeviceType().split("/")[0];
                cell.setCellValue(
                        DeviceTypeShortKeyUtil.getInstance().getShortZhCN(deviceType) + "_" + deviceType);
                continue;
            case 4:
                String vendor = ao.getDeviceType().split("/")[1];
                cell.setCellValue(DeviceTypeShortKeyUtil.getInstance().getShortZhCN(vendor) + "_" + vendor);
                continue;
            case 5:
                Node node = nodeMgrFacade.getNodeByNodeId(ao.getScanNodeId());
                cell.setCellValue(node != null ? node.getIp() : "");
                continue;
            case 6:
                cell.setCellValue(ao.getHostName());
                continue;
            case 7:
                cell.setCellValue(ao.getOs() != null ? ao.getOs().getOsName() : "");
                continue;
            case 8:
                cell.setCellValue(ao.getSafeRank());
                continue;
            case 9:
                cell.setCellValue(ao.getAssGroup().getGroupName());
                continue;
            case 10:
            case 11:
            case 12:
                continue;
            case 13:
                cell.setCellValue(StringUtil.nvl(ao.getLinkman()));
                continue;
            case 14:
                cell.setCellValue("");
                continue;
            default:
                cell.setCellValue("");
                continue;
            }
        }
    }

    //?excel?
    //response.setContentType("application/vnd.ms-excel");
    String userAgent = request.getHeader("User-Agent");
    String fileName = "?.xls";
    if (userAgent.indexOf("Firefox") > 0) {
        response.setHeader("Content-Disposition",
                "attachment; filename*=\"utf8' '" + StringUtil.encode(fileName, "UTF-8") + "\"");
    } else {
        response.addHeader("Content-Disposition",
                "attachment; filename=\"" + StringUtil.encode(fileName, "UTF-8") + "\"");
    }
    try {
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.ts.excelservlet.UDR_Driver_Excel.java

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 *//*from  w ww .  j  ava2 s  . c  om*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    // TODO Auto-generated method stub

    DBTransaction dbtranobj = new DBTransaction();
    String vehicle_number = request.getParameter("vehicle_number");
    HttpSession session = request.getSession(true);
    String[] select = (String[]) session.getAttribute("id");
    String driver_name = request.getParameter("driver_name");

    //System.out.println("IMEI : " +imeinumber);

    //String vehicle_number="";
    int index = 2;
    System.out.println("************** doGet ************");
    OutputStream out = null;
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=Driver_UDR.xls");
        Connection con = dbtranobj.connect();

        // Class.forName("org.postgresql.Driver").newInstance();
        //conn = DriverManager.getConnection("jdbc:postgresql://182.72.167.34:5432/master_database","postgres", "postgres");
        ResultSet rs = null;
        Statement st = null;
        st = con.createStatement();
        rs = st.executeQuery(
                "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name");

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Driver  Sheet");

        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1));
        sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2));
        /* sheet.addMergedRegion(new Region(0,(short)0,0,(short)3));
         sheet.addMergedRegion(new Region(0,(short)0,0,(short)4));
         sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));*/

        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.setHeight((short) 500);
        /*rowhead.createCell((short) 0).setCellValue("Fuel Information For " +vehicle_number);
         * */
        HSSFCell cell2B = rowhead.createCell(0);
        cell2B.setCellValue(new HSSFRichTextString("Report For Driver : " + driver_name));

        // Style Font in Cell 2B  
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle = wb.createCellStyle();
        HSSFFont hSSFFont = wb.createFont();
        hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
        hSSFFont.setFontHeightInPoints((short) 14);
        hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        hSSFFont.setColor(HSSFColor.BLUE.index);
        cellStyle.setFont(hSSFFont);
        cell2B.setCellStyle(cellStyle);
        HSSFRow rowhead1 = sheet.createRow((short) 1);
        rowhead1.setHeight((short) 600);
        Cell cell = rowhead1.createCell((short) 0);
        HSSFCellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1 = wb.createCellStyle();
        HSSFFont hSSFFont1 = wb.createFont();
        hSSFFont1.setFontName(HSSFFont.FONT_ARIAL);
        hSSFFont1.setFontHeightInPoints((short) 12);
        hSSFFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        hSSFFont1.setColor(HSSFColor.BLACK.index);
        cellStyle1.setFont(hSSFFont1);
        cell.setCellStyle(cellStyle1);
        // cell.setCellValue("SNO");
        sheet.setColumnWidth(0, 7000);
        // rowhead.createCell((short) 0).setCellValue("Date");

        if (select != null && select.length != 0) {

            for (int i = 0; i < select.length; i++) {

                if (select[i].equalsIgnoreCase("doe")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("LICENCE EXPIRY");
                    sheet.setColumnWidth(i, 7000);

                } else if (select[i].equalsIgnoreCase("doj")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("JOINED DATE");
                    sheet.setColumnWidth(i, 7000);

                }

                else if (select[i].equalsIgnoreCase("license_number")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("LICENCE NUMBER ");
                    sheet.setColumnWidth(i, 7000);

                }

                else if (select[i].equalsIgnoreCase("vehicle_number")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("VEHICLE NUMBER");
                    sheet.setColumnWidth(i, 7000);

                }

                else if (select[i].equalsIgnoreCase("shift_number")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("SHIFT NUMBER");
                    sheet.setColumnWidth(i, 7000);

                }

                else if (select[i].equalsIgnoreCase("route_number")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("ROUTE NUMBER");
                    sheet.setColumnWidth(i, 7000);

                }

                else if (select[i].equalsIgnoreCase("address")) {

                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue("ADDRESS");
                    sheet.setColumnWidth(i, 7000);

                }

                else {
                    Cell cell1 = rowhead1.createCell((short) i);
                    cell1.setCellStyle(cellStyle1);
                    cell1.setCellValue(select[i]);
                    sheet.setColumnWidth(i, 7000);
                }

            }
        }

        rs = st.executeQuery(
                "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name");

        while (rs.next()) {

            HSSFRow row = sheet.createRow((short) index);
            row.setHeight((short) 500);

            if (select != null && select.length != 0) {

                for (int i = 0; i < select.length; i++) {

                    row.createCell((short) i).setCellValue(rs.getString(select[i]));

                }
            }

            index++;
        }

        out = response.getOutputStream();
        wb.write(out);

    } catch (Exception e) {
        throw new ServletException("Exception in Excel Sample Servlet", e);
    } finally {
        if (out != null)
            out.close();
    }

}

From source file:com.util.poi.ExcelView.java

License:Open Source License

/**
 * ?Excel//ww w.  j ava  2 s.  c  o m
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                //comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    /*   Map<String, Object> map=toHashMap(item);
                       cell.setCellValue(map.get(properties[i]).toString());*/
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    /*Map<String, Object> map=toHashMap(item);
                    cell.setCellValue(map.get(properties[i]).toString());*/
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}