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:com.fufang.bi.controllers.ChainReportController.java

@ApiOperation(value = "Excel", notes = "Excel")
@ApiResponses(value = { @ApiResponse(code = 200, message = RETURNMESSAGE) })
@RequestMapping(value = "/jxctzh/downExcel", method = RequestMethod.POST) //, method = RequestMethod.POST
public void downExcel(HttpServletRequest request, HttpServletResponse response,
        @ApiParam(required = false, value = excels) @RequestParam String query) {
    String path = request.getSession().getServletContext().getRealPath("/");
    try {//from  w w w  .  j  a  va 2s.  co m
        User user = (User) request.getSession().getAttribute("user");
        String role = "";
        role = (String) request.getSession().getAttribute("mark");
        Integer chaintype = user.getChainType();
        logger.debug("chaintype:" + chaintype);

        String data = request.getParameter("query");
        System.out.println("query:" + data);
        JSONObject object = JSONObject.fromObject(data);
        Map<String, Comparable> querymap = new HashMap<String, Comparable>();
        querymap = (Map) object;
        Integer select = (Integer) querymap.get("select");//??
        String way = "";
        if (null != querymap.get("way")) {
            way = (String) querymap.get("way").toString().trim();
        } else {
            return;
        }
        Integer type = (Integer) querymap.get("type");
        String sourcefilePath = path + "/" + "down/taizhang" + select + ".xls";
        List<?> list = new ArrayList();
        querymap.put("mark", role);
        if (type == 0) {
            querymap.put("amount", "t.notaxAmount");//
        } else {
            querymap.put("amount", "t.amount");
        }
        InputStream inputStream;
        inputStream = new FileInputStream(sourcefilePath);
        String fileName = "tmp.xls";

        Object obj = null;

        if (select == 1) {
            fileName = "?_.xls";
            Integer id = user.getPharmacyId();
            querymap.put("id", id);
            list = chainService.findAllCpharmacyExcel(querymap);
            StorageTotal sumData = new StorageTotal();
            sumData = chainService.findAllCpharmacySum(querymap);
            obj = sumData;
        } else if (select == 2) {
            fileName = "?_?.xls";
            if (way.equals("0")) {//
                Integer id = (Integer) querymap.get("id");
                if (id == null) {
                    logger.error("/jxctzh/downExcel id==null error");
                    return;
                }
                querymap.put("needs", 0);
                querymap.put("parent", 8);
            } else if (way.equals("1")) {//
                Integer id = null;
                if (user != null && (querymap.get("id") == null || "".equals(querymap.get("id")))) {
                    id = user.getPharmacyId();
                    querymap.put("id", id);
                }
                String pharmacyCode = "";
                String name = "";
                String pinyin = "";
                pharmacyCode = (String) querymap.get("pharmacyCode");
                name = (String) querymap.get("name");
                pinyin = (String) querymap.get("pinyin");
                if ((pharmacyCode == null && name == null && pinyin == null)
                        || ("".equals(pharmacyCode) && "".equals(name) && "".equals(pinyin))) {
                    querymap.put("parent", 9);// ?
                    if (0 == chaintype) {
                        querymap.put("needs", 2);
                    } else {
                        querymap.put("needs", 3);
                    }
                } else {
                    querymap.put("needs", 1);//?  ?
                }
            }
            StorageMilde sumData = new StorageMilde();
            list = chainService.findAllCpharmacyMildeExcel(querymap);
            sumData = chainService.findAllCpharmacyMildeSum(querymap);
            obj = sumData;
        } else if (select == 3) {
            fileName = "?_.xls";
            if (way.equals("0")) {
                querymap.put("needs", 0);
                querymap.put("parent", 8);
            } else if (way.equals("1")) {
                Integer id = null;
                if (user != null && (querymap.get("id") == null || "".equals(querymap.get("id")))) {
                    id = user.getPharmacyId();
                    querymap.put("pharmacyid", id);
                }
                String pharmacyCode = "";
                String name = "";
                String pinyin = "";
                pharmacyCode = (String) querymap.get("pharmacyCode");
                name = (String) querymap.get("name");
                pinyin = (String) querymap.get("pinyin");
                if ((pharmacyCode == null && name == null && pinyin == null)
                        || ("".equals(pharmacyCode) && "".equals(name) && "".equals(pinyin))) {
                    querymap.put("parent", 9);// ?
                    if (0 == chaintype) {
                        querymap.put("needs", 2);
                    } else {
                        querymap.put("needs", 3);
                    }
                } else {
                    querymap.put("needs", 1);//?  ?
                }
                querymap.put("pharmacyid", id);
            }
            StorageDetail sumData = new StorageDetail();
            list = chainService.findAllCpharmacyDetailExcel(querymap);
            sumData = chainService.findAllCpharmacyDetailSum(querymap);
            obj = sumData;
        }
        fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
        HSSFWorkbook workbook = createUploadSplitExcel(list, inputStream, select, obj);
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        // ?
        ServletOutputStream fOut = response.getOutputStream();
        workbook.write(fOut);
        fOut.flush();
        fOut.close();

    } catch (FileNotFoundException e) {
        logger.error("/jxctzh/downExcel FileNotFoundException error");
        e.printStackTrace();
    } catch (UnsupportedEncodingException e) {
        logger.error("/jxctzh/downExcel UnsupportedEncodingException error");
        e.printStackTrace();
    } catch (IOException e) {
        logger.error("/jxctzh/downExcel IOException error");
        e.printStackTrace();
    }

}

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

License:Apache License

/**
 * Creates a spreadsheet with only the headers as described by the set of
 * payloadClass, but no data. For each payloadClass a sheet will be created.
 * /* ww  w  .  j  ava 2  s .  com*/
 * @param stream
 *            The output which will receive the content of the spreadsheet
 * @param payloadClasses
 *            Java types annotated with {@link SpreadsheetPayload}
 * */
public void toSpreadsheet(final OutputStream stream, final Set<Class<? extends Object>> payloadClasses)
        throws IOException {
    if (payloadClasses.size() < 1) {
        throw new IllegalArgumentException("At least one payload class must be present");
    }

    final HSSFWorkbook wb = new HSSFWorkbook();
    final CreationHelper creationHelper = wb.getCreationHelper();

    for (final Class<? extends Object> clazz : payloadClasses) {
        final Payload<? extends Object> payload = new Payload<>(clazz);

        final Sheet sheet = createSheet(wb, payload.getName());
        final Row row = createRow(sheet);
        createCells(payload, row, creationHelper);
    }

    wb.write(stream);
}

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

License:Open Source License

public void saveSpreadSheet(HSSFWorkbook workbook, File file) {
    try (FileOutputStream out = new FileOutputStream(file)) {
        workbook.write(out);
    } catch (IOException ex) {
        logger.error(null, ex);//  w  ww .  ja va2  s.c o  m
    }
}

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;/*w ww  .j  av a2  s .  c o 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.github.s4ke.worktimegen.Main.java

License:BEER-WARE LICENSE

public static void generateExcelSheet(int year, int month, List<Work> workObjs) throws IOException {
    try (InputStream is = Main.class.getResourceAsStream("/template_urlaub.xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        HSSFSheet sheet = workbook.getSheetAt(0);

        GregorianCalendar calendar = new GregorianCalendar();
        calendar.set(year, month - 1, 1);
        sheet.getRow(7).getCell(2).setCellValue(DATE_FORMAT.format(calendar.getTime()));
        calendar.set(year, month - 1, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        sheet.getRow(7).getCell(4).setCellValue(DATE_FORMAT.format(calendar.getTime()));

        int startRow = 11;
        int endRow = 33;
        if (workObjs.size() > (endRow - startRow)) {
            throw new AssertionError("template has too few rows");
        }/*from  ww  w.j av  a 2s . co m*/
        int curRow = startRow;
        for (Work work : workObjs) {
            Row row = sheet.getRow(curRow);
            row.getCell(0).setCellValue(work.date);
            row.getCell(1).setCellValue(pad(work.startHours) + ":" + pad(work.startMinutes));
            row.getCell(2).setCellValue(pad(work.endHours) + ":" + pad(work.endMinutes));
            ++curRow;
        }

        try (FileOutputStream fos = new FileOutputStream(
                new File("zeiterfassung_braun_" + year + "_" + month + ".xls"))) {
            workbook.write(fos);
        }
    }
}

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

public List getAllItemOutReport() throws FileNotFoundException, IOException {

    ResultSet rs = null;/*w  ww .j av  a  2 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";
        //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.  jav  a  2s  .  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  .ja v  a 2 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;//from   w  ww.j  a v a  2  s.  com
    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 . jav a  2s.  co 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;
}