Example usage for org.apache.poi.ss.usermodel Workbook close

List of usage examples for org.apache.poi.ss.usermodel Workbook close

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. close

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook close.

Prototype

@Override
void close() throws IOException;

Source Link

Document

Close the underlying input resource (File or Stream), from which the Workbook was read.

Usage

From source file:com.linus.excel.poi.AligningCells.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    Sheet sheet = wb.createSheet();/*from www  .j a  v a  2  s  . c  o  m*/
    Row row = sheet.createRow((short) 2);
    row.setHeightInPoints(30);
    for (int i = 0; i < 8; i++) {
        //column width is set in units of 1/256th of a character width
        sheet.setColumnWidth(i, 256 * 15);
    }

    createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("excel/ss-example-align.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

From source file:com.lwr.software.reporter.restservices.ReportExportService.java

License:Open Source License

public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) {
    Workbook wb = new XSSFWorkbook();

    Font boldFont = wb.createFont();
    boldFont.setBold(true);/*  ww  w  .  j  a v  a  2 s  . c  o  m*/

    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setBorderBottom(BorderStyle.THIN);
    headerStyle.setBorderTop(BorderStyle.THIN);
    headerStyle.setBorderRight(BorderStyle.THIN);
    headerStyle.setBorderLeft(BorderStyle.THIN);
    headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
    headerStyle.setFont(boldFont);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);

    CellStyle titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(BorderStyle.THIN);
    titleStyle.setBorderTop(BorderStyle.THIN);
    titleStyle.setBorderRight(BorderStyle.THIN);
    titleStyle.setBorderLeft(BorderStyle.THIN);

    List<RowElement> rows = toExport.getRows();
    int sheetIndex = 0;
    for (RowElement rowElement : rows) {
        List<Element> elements = rowElement.getElements();
        for (Element element : elements) {
            try {
                element.setParams(reportParams);
                element.init();
            } catch (Exception e) {
                logger.error("Unable to init '" + element.getTitle() + "' element of report '"
                        + toExport.getTitle() + "' Error " + e.getMessage(), e);
                return Response.serverError().entity("Unable to init '" + element.getTitle()
                        + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build();
            }
            String sheetName = element.getTitle().substring(0,
                    element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++);
            Sheet sheet = wb.createSheet(sheetName);

            Row reportTitleRow = sheet.createRow(0);
            Cell reportTitleHeader = reportTitleRow.createCell(0);
            reportTitleHeader.setCellStyle(headerStyle);
            reportTitleHeader.setCellValue("Report Title:");

            Cell reportTitleCell = reportTitleRow.createCell(1);
            reportTitleCell.setCellStyle(titleStyle);
            reportTitleCell.setCellValue(toExport.getTitle());

            Row elementTitleRow = sheet.createRow(1);
            Cell elementTitleHeader = elementTitleRow.createCell(0);
            elementTitleHeader.setCellStyle(headerStyle);
            elementTitleHeader.setCellValue("Element Title:");

            Cell elementTitleCell = elementTitleRow.createCell(1);
            elementTitleCell.setCellStyle(titleStyle);
            elementTitleCell.setCellValue(element.getTitle());

            List<List<Object>> dataToExport = element.getData();

            int rowIndex = 3;
            Row headerRow = sheet.createRow(rowIndex++);
            List<Object> unifiedHeaderRow = element.getHeader();
            for (int i = 0; i < unifiedHeaderRow.size(); i++) {
                Cell headerCell = headerRow.createCell(i);
                String headerCellValue = unifiedHeaderRow.get(i).toString();
                headerCell.setCellValue(headerCellValue);
                headerCell.setCellStyle(headerStyle);
            }
            for (int i = 0; i < dataToExport.size(); i++) {
                Row row = sheet.createRow(rowIndex++);
                List<Object> unifiedRow = dataToExport.get(i);
                int cellIndex = 0;
                for (Object cellValue : unifiedRow) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);
                    try {
                        double val = Double.parseDouble(cellValue.toString());
                        cell.setCellValue(val);
                    } catch (NumberFormatException e) {
                        cell.setCellValue(cellValue.toString());
                    }
                    cellIndex++;
                }
            }
        }
    }
    try {
        File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime());
        logger.info("Export CSV temp file path is " + file.getAbsoluteFile());
        wb.write(new FileOutputStream(file));
        wb.close();
        ResponseBuilder responseBuilder = Response.ok((Object) file);
        responseBuilder.header("Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        responseBuilder.header("Content-Transfer-Encoding", "binary");
        responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName());
        responseBuilder.header("Content-Length", file.length());
        Response responseToSend = responseBuilder.build();
        file.deleteOnExit();
        return responseToSend;
    } catch (Exception e1) {
        return Response.serverError()
                .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build();
    }

}

From source file:com.movielabs.availslib.AvailSS.java

License:Open Source License

/**
 * Add a sheet from an Excel spreadsheet to a spreadsheet object
 * @param sheetName name of the sheet to add
 * @return created sheet object/*  w  w w . j  a v  a2s .  c  o  m*/
 * @throws IllegalArgumentException if the sheet does not exist in the Excel spreadsheet
 * @throws Exception other error conditions may also throw exceptions
 */
public AvailsSheet addSheet(String sheetName) throws Exception {
    Workbook wb = new XSSFWorkbook(new FileInputStream(file));
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet == null) {
        wb.close();
        throw new IllegalArgumentException(file + ":" + sheetName + " not found");
    }
    AvailsSheet as = addSheetHelper(wb, sheet);
    wb.close();
    return as;
}

From source file:com.movielabs.availslib.AvailSS.java

License:Open Source License

/**
 * Add a sheet from an Excel spreadsheet to a spreadsheet object
 * @param sheetNumber zero-based index of sheet to add
 * @return created sheet object/*ww  w.jav  a 2  s  .  c  o  m*/
 * @throws IllegalArgumentException if the sheet does not exist in the Excel spreadsheet
 * @throws Exception other error conditions may also throw exceptions
 */
public AvailsSheet addSheet(int sheetNumber) throws Exception {
    Workbook wb = new XSSFWorkbook(new FileInputStream(file));

    Sheet sheet;
    try {
        sheet = wb.getSheetAt(sheetNumber);
    } catch (IllegalArgumentException e) {
        wb.close();
        throw new IllegalArgumentException(file + ": sheet number " + sheetNumber + " not found");
    }
    AvailsSheet as = addSheetHelper(wb, sheet);
    wb.close();
    return as;
}

From source file:com.movielabs.availslib.AvailSS.java

License:Open Source License

/**
 * Dump the contents (sheet-by-sheet) of an Excel spreadsheet
 * @param file name of the Excel .xlsx spreadsheet
 * @throws Exception if any error is encountered (e.g. non-existant or corrupt file)
 *//* w ww. ja va2  s  . com*/
public static void dumpFile(String file) throws Exception {
    Workbook wb = new XSSFWorkbook(new FileInputStream(file));
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("Sheet <" + wb.getSheetName(i) + ">");
        for (Row row : sheet) {
            System.out.println("rownum: " + row.getRowNum());
            for (Cell cell : row) {
                System.out.println("   | " + cell.toString());
            }
        }
    }
    wb.close();
}

From source file:com.mycompany.modelinglab2.helperModel.ExcelHelper.ExcelHelper.java

/**
 * distance = x higth = y// w w w . jav a2 s .  co  m
 */
public void formExcelFile(List distance, List higth) {

    File file = new File("C:\\Users\\Roman\\Desktop\\Experiment.xls");
    //   
    Workbook wb = new HSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet("Laboratory 2 (Modeling balls)");
        // ? 
        Font font = wb.createFont();
        font.setBoldweight(font.ANSI_CHARSET);

        // ? ?  ?
        Row row = sheet.createRow(0);

        row.createCell(0).setCellValue("???");
        row.createCell(1).setCellValue("?");

        for (int i = 0; i < distance.size(); i++) {
            row = sheet.createRow(i + 1);
            Cell distCell = row.createCell(0);
            Cell higthCell = row.createCell(1);
            distCell.setCellValue(distance.get(i).toString().replace('.', ','));
            higthCell.setCellValue(higth.get(i).toString().replace('.', ','));
        }
        //   
        sheet.autoSizeColumn(0);
        // ?  ??
        wb.write(new FileOutputStream(file));
        wb.close();
    } catch (IOException ex) {
        Logger.getLogger(ExcelHelper.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/*from  w  ww  . jav  a 2  s. c  o m*/
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? )
 * </pre>
 *
 * @method Name : readExcel
 * @param strFullFilePath, serviceType
 * @return List<Map<String, Object>>
 * @throws Exception
 * 
 */
@SuppressWarnings("deprecation")
public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Map<String, Object> map = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */

        /* Sheet  ? ,  ?  ??  */
        /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */
        for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1  ? */

            Sheet sheet = wb.getSheetAt(sheetIdx);

            /*   ?? ?    ?,   */

            /* row  ?  */
            int cellCount = 0;
            for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

                Row row = sheet.getRow(rowIdx);
                cellCount = row.getLastCellNum();
                map = new HashMap<String, Object>();

                if (row != null) {
                    // cell  ? 
                    for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                        Cell cell = row.getCell(cellIdx);
                        if (cell != null) {

                            int cellType = cell.getCellType();
                            String value = "";

                            //  WBS  ? ? ??
                            if (serviceType.equals("order")) {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                        value = formatter.format(cell.getDateCellValue());
                                    } else {
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                    ;
                                }
                            } else {
                                switch (cellType) {
                                case HSSFCell.CELL_TYPE_FORMULA: //??
                                    value = cell.getStringCellValue();//cell.getCellFormula();
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC://?
                                    value = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: //?
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: //
                                    value = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: //BYTE
                                    value = cell.getErrorCellValue() + "";
                                    break;

                                default:
                                }
                            }
                            map.put("colName" + cellIdx, value);
                        } else {
                            map.put("colName" + cellIdx, "");
                        }
                    }

                    list.add(map);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>//from   w  w w  .j a  va  2s  .  c o m
 * 1.  : POI Util
 * 2.  : Excel ? ?(*.xls, *.xlsx ? ), sheet  ?
 * </pre>
 *
 * @method Name : readExcelMulti
 * @param strFullFilePath, serviceType, sheetNo
 * @return 
 * @throws
 * 
 */
@SuppressWarnings("deprecation")
public static List<LinkedHashMap<String, Object>> readExcelMulti(String strFullFilePath, String serviceType,
        int sheetNo) throws Exception {
    String tmpFile = strFullFilePath;
    File wbfile = new File(tmpFile);

    Workbook wb = null;
    FileInputStream file = null;

    List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
    LinkedHashMap<String, Object> map = null;

    try {
        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= file path : {} =", strFullFilePath);
            log.debug("= tmp file  : {} =", tmpFile);
            log.debug(
                    "==========================================================================================");
        }

        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */

        Sheet sheet = wb.getSheetAt(sheetNo);

        /* row  ?  */
        int cellCount = 0;
        for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {

            Row row = sheet.getRow(rowIdx);
            cellCount = row.getLastCellNum();
            map = new LinkedHashMap<String, Object>();

            if (rowIdx == 0) {
                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= sheet no  : {} =", sheetNo);
                    log.debug("= row count : {} =", sheet.getLastRowNum());
                    log.debug("= col count : {} =", cellCount);
                    log.debug(
                            "==================================================================================");
                }
            }

            if (row != null) {
                // cell  ? 
                for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) {

                    Cell cell = row.getCell(cellIdx);
                    if (cell != null) {

                        int cellType = cell.getCellType();
                        String value = "";

                        switch (cellType) {
                        case HSSFCell.CELL_TYPE_FORMULA: //??
                            value = cell.getStringCellValue();//cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC://?
                            value = cell.getNumericCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_STRING: //?
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: //
                            value = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: //BYTE
                            value = cell.getErrorCellValue() + "";
                            break;

                        default:
                        }

                        map.put("item" + String.valueOf(cellIdx), value);
                    } else {
                        map.put("item" + String.valueOf(cellIdx), "");
                    }
                }

                if (log.isDebugEnabled()) {
                    log.debug(
                            "==================================================================================");
                    log.debug("= map : {} =", map);
                    log.debug(
                            "==================================================================================");
                }

                list.add(map);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readExcel ?");
    } finally {

        /* ? ??  */
        file.close();
        wb.close();
    }

    return list;
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/* ww w .  j a  v a  2s  . c  o m*/
 * 1.  : POI Util
 * 2.  : Excel ? ? (? ?), ?    ?? ?
 * </pre>
 *
 * @method Name : readWriteExcel
 * @param response, codeList, serviceType, templateFile
 * @return none
 * @throws Exception
 * 
 */
public static void readWriteExcel(HttpServletResponse response, List<Map<String, Object>> codeList,
        String serviceType, String templateFile) throws Exception {
    OutputStream fileOut = null;

    /* ?   ?   */

    File wbfile = new File(templateFile);
    Workbook wb = null;
    FileInputStream file = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); */

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            wb.removeSheetAt(wb.getNumberOfSheets() - 1);
        }

        /* Sheet ?, ? , ?   ?  ?  */
        /*   ?*/
        /* ?  : wb = createWorkBook(wb, "sheet Name", "dataList", "header column List");*/

    } catch (Exception e) {
        e.printStackTrace();

        if (log.isDebugEnabled()) {
            log.debug(
                    "==========================================================================================");
            log.debug("= Excel File Reading ... Error : [{}] =", e);
            log.debug(
                    "==========================================================================================");
        }

        throw new NCException("ExcelUtil > readWriteExcel ?");
    } finally {
        /* ? ??  */
        file.close();
        wb.close();
    }

    String fileName = serviceType + ".xlsm";

    /* ?  */
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

    /* , to file */
    fileOut = response.getOutputStream();
    wb.write(fileOut);
    fileOut.close();
    fileOut.flush();
}

From source file:com.nc.common.utils.ExcelUtil.java

License:Open Source License

/**
 * <pre>/*  w ww  .  ja v  a 2s  .c  o  m*/
 * 1.  : POI UTIL 
 * 2.  : Excel ? ? (? ?)
 * </pre>
 *
 * @method Name : readWriteWbsExcel
 * @param response, codeList, serviceType, templateFile
 * @return none 
 * @throws Exception
 * 
 */
public static void readWriteWbsExcel(HttpServletResponse response, List<Map<String, Object>> codeList,
        String serviceType, String templateFile) throws Exception {
    OutputStream fileOut = null;

    templateFile += ".xlsx";

    File wbfile = new File(templateFile);
    Workbook wb = null;
    FileInputStream file = null;

    try {
        file = new FileInputStream(wbfile);

        wb = new HSSFWorkbook(file);

        /* Sheet , ? , ?   ?    */
        /*   ?*/
        /* ?  : wb.removeSheetAt(wb.getSheetIndex("?id")); */

        /* Sheet ?, ? , ?   ?  ?  */
        /*   ?*/
        /* ?  : wb = createWorkBook(wb, "sheet Name", "dataList", "header column List");*/

    } catch (Exception e) {
        e.printStackTrace();
        throw new NCException("ExcelUtil > readWriteExcel ?");
    } finally {
        /* ? ??  */
        file.close();
        wb.close();
    }

    String fileName = serviceType + ".xlsx";

    /* ?  */
    response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

    /*  */
    fileOut = response.getOutputStream();
    wb.write(fileOut);
    fileOut.close();
    fileOut.flush();
}