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

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

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

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname)

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:com.fota.statMgt.controller.StatFotaExcelDown.java

License:Open Source License

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String date = DateTimeUtil.getCurrentDate();
    String formNm = "FOTA";
    String fileName = date + "_" + formNm;
    String sheetName = "sheet1";

    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setHeader("Content-disposition",
            "attachent; filename=" + new String((fileName).getBytes("KSC5601"), "8859_1") + ".xls");

    @SuppressWarnings("unchecked")
    List<StatFotaSearchVO> resultData = (List<StatFotaSearchVO>) model.get("resultData");

    HSSFSheet sheet = workbook.createSheet(sheetName);

    //  ?/*w ww .  jav a 2  s.  co m*/
    HSSFRow header = sheet.createRow(0);
    header.createCell(0).setCellValue("??");
    header.createCell(1).setCellValue("FOTA TYPE");
    header.createCell(2).setCellValue("");
    header.createCell(3).setCellValue("");
    header.createCell(4).setCellValue("?");
    header.createCell(5).setCellValue("??");
    header.createCell(6).setCellValue("");
    header.createCell(7).setCellValue("?");
    header.createCell(8).setCellValue("");
    header.createCell(9).setCellValue("?");
    header.createCell(10).setCellValue("");
    header.createCell(11).setCellValue("");
    header.createCell(12).setCellValue("");
    header.createCell(13).setCellValue("");
    header.createCell(14).setCellValue("(%)");

    int index = 0;
    for (int i = 0; i < resultData.size(); i++) {
        StatFotaSearchVO fvo = resultData.get(i);
        HSSFRow row = sheet.createRow(++index);
        row.createCell(0).setCellValue(fvo.getCretDt());
        row.createCell(1).setCellValue(fvo.getFotaType());
        row.createCell(2).setCellValue(fvo.getBizNm());
        row.createCell(3).setCellValue(fvo.getSvcNm());
        row.createCell(4).setCellValue(fvo.getDevMakerNm());
        row.createCell(5).setCellValue(fvo.getDevModelNm());
        row.createCell(6).setCellValue(fvo.getFirmwareMakerVer());
        row.createCell(7).setCellValue(fvo.getEarlyCnt());
        row.createCell(8).setCellValue(fvo.getWaitCnt());
        row.createCell(9).setCellValue(fvo.getVersionChkCnt());
        row.createCell(10).setCellValue(fvo.getProcessCnt());
        row.createCell(11).setCellValue(fvo.getSuccessCnt());
        row.createCell(12).setCellValue(fvo.getFailCnt());
        row.createCell(13).setCellValue(fvo.getTotal());
        row.createCell(14).setCellValue(fvo.getSuccessRate());
    }

    workbook.write(response.getOutputStream());
    response.getOutputStream().close();
}

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private Sheet createSheet(final HSSFWorkbook wb, final String payloadName) {
    String sheetName = WorkbookUtil.createSafeSheetName(payloadName);
    final Sheet sheet = wb.createSheet(sheetName);
    return sheet;
}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private HSSFWorkbook createSpreadSheet() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("GrantMaster exported data");
    int rowId = 0;
    rowId = addExcelRow(workbook, sheet, rowId, getTableViewHeader());
    for (Object tableRowEntity : getTableItems()) {
        rowId = addExcelRow(workbook, sheet, rowId, getTableViewRow(tableRowEntity));
    }/*w  ww  . ja va  2  s  . co  m*/
    return workbook;
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;/*from   w w  w. ja  v a  2s .  co m*/
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

From source file:com.grant.data.ItemDAO.java

public List getAllItemOutReport() throws FileNotFoundException, IOException {

    ResultSet rs = null;/*from   w  w  w . j a  va2 s.com*/
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out";
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell((short) 0).setCellValue("CellHeadName1");
        rowhead.createCell((short) 1).setCellValue("CellHeadName2");
        rowhead.createCell((short) 2).setCellValue("CellHeadName3");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            // row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("column1")));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/test.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public boolean getStockINReport(ReportICatogory rrc) throws FileNotFoundException, IOException {

    ResultSet rs = null;/*from   w  w  w  .j a v  a2s .c  o m*/
    Connection dbConn = null;
    boolean ss = false;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();
        String query = "SELECT * FROM grant_item_in WHERE d_in_date BETWEEN '" + rrc.getItemIDateStart()
                + "' AND '" + rrc.getItemInDateEnd() + "'";
        System.out.println(query);

        rs = stmt.executeQuery(query);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell((short) 1).setCellValue("Item ID");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_descrip");
        rowhead.createCell((short) 5).setCellValue("i_inwards");
        rowhead.createCell((short) 6).setCellValue("i_balance");
        rowhead.createCell((short) 7).setCellValue("v_type");
        rowhead.createCell((short) 8).setCellValue("d_in_date");
        rowhead.createCell((short) 9).setCellValue("i_unit_price");

        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));

            i++;
        }

        FileDateTime fileDateTime = new FileDateTime();
        FileOutputStream fileOut = new FileOutputStream(fileDateTime.getFileName("Stock_In"));
        workbook.write(fileOut);
        ss = true;
    } catch (SQLException sQLException) {
        ss = false;
        System.out.println(sQLException + "-----------Insert query failed-------");
        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public boolean getStockOutReport(ReportICatogory ric) throws FileNotFoundException, IOException {

    ResultSet rs = null;/*from w  w  w  .  j ava2 s.  c  o m*/
    Connection dbConn = null;
    boolean ss = false;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_print WHERE d_in_date BETWEEN '" + ric.getItemIDateStart()
                + "' AND '" + ric.getItemInDateEnd() + "'";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");

        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");

        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }

        FileDateTime fileDateTime = new FileDateTime();
        FileOutputStream fileOut = new FileOutputStream(fileDateTime.getFileName("Stock_Out"));
        workbook.write(fileOut);
        fileOut.close();
        ss = true;

    } catch (SQLException sQLException) {
        ss = false;
        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public List getItemCatogaryReport(ReportICatogory ric) throws FileNotFoundException, IOException {

    ResultSet rs = null;// w  w  w .  j a  v  a2s . c  o m
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '" + ric.getItemIDateStart()
                + "' AND '" + ric.getItemInDateEnd() + "' AND v_item_name = '" + ric.getItemName() + "'";
        //SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '2016-01-07' AND '2016-01-08' AND v_item_name = 'Item 1 chm'
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");
        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/test.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.grant.data.ItemDAO.java

public List getRefPerfReport(ReportRefCode rrc) throws FileNotFoundException, IOException {

    ResultSet rs = null;//from   www .j a v a2  s. c o m
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '" + rrc.getItemIDateStart()
                + "' AND '" + rrc.getItemInDateEnd() + "' AND v_ref_code = '" + rrc.getRefCode() + "'";
        //SELECT * FROM grant_item_out WHERE d_in_date BETWEEN '2016-01-07' AND '2016-01-08' AND v_item_name = 'Item 1 chm'
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        //rowhead.createCell((short) 0).setCellValue("Item ID");
        rowhead.createCell((short) 1).setCellValue("i_itin_id");
        rowhead.createCell((short) 2).setCellValue("v_item_name");
        rowhead.createCell((short) 3).setCellValue("v_item_no");
        rowhead.createCell((short) 4).setCellValue("v_ref_code");
        rowhead.createCell((short) 5).setCellValue("v_descrip");
        rowhead.createCell((short) 6).setCellValue("v_invo_no");
        rowhead.createCell((short) 7).setCellValue("i_outwards");
        rowhead.createCell((short) 8).setCellValue("v_outtype");
        rowhead.createCell((short) 9).setCellValue("i_balance");
        rowhead.createCell((short) 10).setCellValue("i_unit_price");
        rowhead.createCell((short) 11).setCellValue("i_amount");
        rowhead.createCell((short) 12).setCellValue("i_sub_total");
        rowhead.createCell((short) 13).setCellValue("i_discount");
        rowhead.createCell((short) 14).setCellValue("i_total");
        rowhead.createCell((short) 15).setCellValue("d_in_date");
        rowhead.createCell((short) 16).setCellValue("v_cus_name");
        rowhead.createCell((short) 17).setCellValue("v_address");
        rowhead.createCell((short) 18).setCellValue("v_payType");
        rowhead.createCell((short) 19).setCellValue("v_no");
        rowhead.createCell((short) 20).setCellValue("v_order_no");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            //row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt(0)));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            row.createCell((short) 3).setCellValue(rs.getString(3));
            row.createCell((short) 4).setCellValue(rs.getString(4));
            row.createCell((short) 5).setCellValue(rs.getString(5));
            row.createCell((short) 6).setCellValue(rs.getString(6));
            row.createCell((short) 7).setCellValue(rs.getString(7));
            row.createCell((short) 8).setCellValue(rs.getString(8));
            row.createCell((short) 9).setCellValue(rs.getString(9));
            row.createCell((short) 10).setCellValue(rs.getString(10));
            row.createCell((short) 11).setCellValue(rs.getString(11));
            row.createCell((short) 12).setCellValue(rs.getString(12));
            row.createCell((short) 13).setCellValue(rs.getString(13));
            row.createCell((short) 14).setCellValue(rs.getString(14));
            row.createCell((short) 15).setCellValue(rs.getString(15));
            row.createCell((short) 16).setCellValue(rs.getString(16));
            row.createCell((short) 17).setCellValue(rs.getString(17));
            row.createCell((short) 18).setCellValue(rs.getString(18));
            row.createCell((short) 19).setCellValue(rs.getString(19));
            row.createCell((short) 20).setCellValue(rs.getString(20));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/ref.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}

From source file:com.haulmont.mp2xls.writer.LocalizationBatchExcelWriter.java

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

    HSSFCellStyle systemStyle = workbook.createCellStyle();
    systemStyle.setFillForegroundColor(HSSFColor.RED.index);
    systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }/*from   w w  w.jav a  2  s  .  c  om*/
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}