Example usage for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows

List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getPhysicalNumberOfRows

Introduction

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

Prototype

int getPhysicalNumberOfRows();

Source Link

Document

Returns the number of physically defined rows (NOT the number of rows in the sheet)

Usage

From source file:com.canoo.webtest.plugins.exceltest.ExcelStructureFilter.java

License:Open Source License

public void doExecute() throws Exception {
    final Workbook excelWorkbook = getExcelWorkbook();
    final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
    final Document doc = builder.newDocument();
    final Element root = doc.createElement("excelWorkbook");
    doc.appendChild(root);/*w w w  .  j  a v  a  2 s  . c o  m*/
    //root.setAttribute("backup", String.valueOf(excelWorkbook.getBackupFlag()));
    root.setAttribute("backup", String.valueOf(Boolean.FALSE));
    root.setAttribute("numberOfFonts", String.valueOf(excelWorkbook.getNumberOfFonts()));
    root.setAttribute("numberOfCellStyles", String.valueOf(excelWorkbook.getNumCellStyles()));
    root.setAttribute("numberOfNames", String.valueOf(excelWorkbook.getNumberOfNames()));
    final Element sheets = doc.createElement("sheets");
    for (int i = 0; i < excelWorkbook.getNumberOfSheets(); i++) {
        final Sheet sheetAt = excelWorkbook.getSheetAt(i);
        final Element sheetElement = doc.createElement("sheet");
        sheetElement.setAttribute("index", String.valueOf(i));
        sheetElement.setAttribute("name", excelWorkbook.getSheetName(i));
        sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum()));
        sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum()));
        sheetElement.setAttribute("physicalRows", String.valueOf(sheetAt.getPhysicalNumberOfRows()));
        sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight()));
        sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth()));
        sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage()));
        sheets.appendChild(sheetElement);
    }
    root.appendChild(sheets);
    final StringWriter sw = new StringWriter();
    writeXmlFile(doc, sw);
    ContextHelper.defineAsCurrentResponse(getContext(), sw.toString(), "text/xml", getClass());
}

From source file:com.cn.controller.CommonController.java

/**
 * ?//from   w  ww.  jav  a2 s. co m
 *
 * @param beanPackage
 * @param className
 * @param fileName
 * @return
 * @throws Exception
 */
public ArrayList<Object> importData(String beanPackage, String className, String fileName) throws Exception {
    //???
    Class objClass = Class.forName(beanPackage + className);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream = null;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        //json = Units.objectToJson(-1, "???, ?, ???", null);
        return null;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        if (fieldDes.indexOf(fieldName) != -1) {
            templateDataIndex[fieldDes.indexOf(fieldName)] = i;
        } else {
            return null;
        }
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, "");
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }

            }
        }

        result.add(object);
    }

    return result;
}

From source file:com.cn.controller.InterfaceController.java

/**
 * Excel?/* w  w  w  .  ja  va2 s.com*/
 * @param beanPackage
 * @param tableName
 * @param fileName
 * @param conn
 * @return
 * @throws Exception 
 */
public String importData(String beanPackage, String tableName, String fileName, String conn) throws Exception {
    String json;
    CommonController commonController = new CommonController();
    //???
    Class objClass = Class.forName(beanPackage + tableName);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream = null;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //        System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        json = Units.objectToJson(-1,
                "???, ?, ???", null);
        return json;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        templateDataIndex[fieldDes.indexOf(fieldName)] = i;
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            //                String fieldType = field.getGenericType().toString();

            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue());
                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue());
                    }
                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getStringCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getStringCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getStringCellValue(cell));
                }
            }
        }

        result.add(object);
    }

    ArrayList<Integer> addResult = commonController.dataBaseOperate(
            JSONObject.toJSONString(result, Units.features), beanPackage, tableName, "add", conn);
    //        System.out.println("import res:" + Arrays.toString(addResult.toArray()));
    if (addResult.get(0) == 0) {
        json = Units.objectToJson(0, "?" + (addResult.size() - 1) + "??!", null);
    } else if (addResult.get(0) == 2) {
        json = Units.objectToJson(2, "?!", null);
    } else {
        int successNum = 0, failedNum = 0;
        String failedMsg = ":";
        for (int i = 1; i < addResult.size(); i++) {
            int res = addResult.get(i);
            if (res == 1) {
                successNum++;
            } else {
                failedNum++;
                failedMsg += (i + 1) + ",";
            }
        }
        json = Units.objectToJson(-1,
                "?" + successNum + "??, " + failedNum + "??!" + failedMsg,
                null);
    }
    return json;
}

From source file:com.cn.controller.OrderPlanController.java

/**
 * ?//  w  ww  .  ja  va2  s .c om
 *
 * @param fileName
 * @param planType
 * @return -1 -- ?, 0 -- ??, -2 -- ?, -3 -- ??
 */
public int importData(String fileName, int planType) {
    InputStream inputStream = null;
    int templateType;//?: 0 -- ??? | 1 -- ??? | 2 -- ?SAP?
    try {
        File file = new File(fileName);
        inputStream = new FileInputStream(file);
        Sheet sheet;
        if (fileName.endsWith(".xls")) {
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            sheet = workbook.getSheetAt(0);
        } else if (fileName.endsWith(".xlsx")) {
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            sheet = workbook.getSheetAt(0);
        } else {
            logger.info("?Excel!");
            return -2;
        }

        Row headRow = sheet.getRow(0);
        if (headRow.getPhysicalNumberOfCells() == 38) {
            templateType = 0;
        } else if (headRow.getPhysicalNumberOfCells() == 8) {
            templateType = 1;
        } else if (headRow.getPhysicalNumberOfCells() == 34) {
            templateType = 2;
        } else {
            logger.info("???!");
            return -3;
        }

        ArrayList<OrderPlan> imports = new ArrayList<>();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            int cellNum = row.getPhysicalNumberOfCells();
            //logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i + ",count cell num is:" + cellNum);
            OrderPlan info = new OrderPlan();
            //
            if (row.getCell(templateDataIndex[templateType][0]).getCellType() == Cell.CELL_TYPE_STRING) {
                info.setFinishTime(row.getCell(templateDataIndex[templateType][0]).getStringCellValue());
            } else {
                if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][0]))) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = DateUtil
                            .getJavaDate(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue());
                    info.setFinishTime(dateFormat.format(date));
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    info.setFinishTime(
                            df.format(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue()));
                }
            }
            //
            if (row.getCell(templateDataIndex[templateType][1]).getCellType() == Cell.CELL_TYPE_STRING) {
                info.setSendTime(row.getCell(templateDataIndex[templateType][1]).getStringCellValue());
            } else {
                if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][1]))) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    Date date = DateUtil
                            .getJavaDate(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue());
                    info.setSendTime(dateFormat.format(date));
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    info.setSendTime(
                            df.format(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue()));
                }
            }
            //?
            row.getCell(templateDataIndex[templateType][2]).setCellType(Cell.CELL_TYPE_STRING);
            info.setCarrierCode(row.getCell(templateDataIndex[templateType][2]).getStringCellValue());
            //??
            row.getCell(templateDataIndex[templateType][3]).setCellType(Cell.CELL_TYPE_STRING);
            info.setCarrierName(row.getCell(templateDataIndex[templateType][3]).getStringCellValue());
            //???
            row.getCell(templateDataIndex[templateType][4]).setCellType(Cell.CELL_TYPE_STRING);
            info.setPinMing(row.getCell(templateDataIndex[templateType][4]).getStringCellValue());
            //?
            row.getCell(templateDataIndex[templateType][5]).setCellType(Cell.CELL_TYPE_STRING);
            info.setJianHao(row.getCell(templateDataIndex[templateType][5]).getStringCellValue());
            //???
            row.getCell(templateDataIndex[templateType][6]).setCellType(Cell.CELL_TYPE_STRING);
            info.setUnit(row.getCell(templateDataIndex[templateType][6]).getStringCellValue());
            //?
            row.getCell(templateDataIndex[templateType][7]).setCellType(Cell.CELL_TYPE_NUMERIC);
            info.setCarCount((int) row.getCell(templateDataIndex[templateType][7]).getNumericCellValue());

            imports.add(info);
            /*
             if (templateType == 0) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(13).getStringCellValue());
             //??
             row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(14).getStringCellValue());
             //???
             row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(16).getStringCellValue());
             //?
             row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(17).getStringCellValue());
             //???
             row.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(21).getStringCellValue());
             //?
             row.getCell(22).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(22).getNumericCellValue());
                    
             imports.add(info);
             } else if (templateType == 1) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(2).getStringCellValue());
             //??
             row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(3).getStringCellValue());
             //???
             row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(4).getStringCellValue());
             //?
             row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(5).getStringCellValue());
             //???
             row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(6).getStringCellValue());
             //?
             row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(7).getNumericCellValue());
                    
             imports.add(info);
             } else if (templateType == 2) {
             OrderPlan info = new OrderPlan();
             //
             if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setFinishTime(row.getCell(0).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(0))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue());
             info.setFinishTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setFinishTime(df.format(row.getCell(0).getNumericCellValue()));
             }
             }
             //
             if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
             info.setSendTime(row.getCell(1).getStringCellValue());
             } else {
             if (DateUtil.isCellDateFormatted(row.getCell(1))) {
             SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
             Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue());
             info.setSendTime(dateFormat.format(date));
             } else {
             DecimalFormat df = new DecimalFormat("0");
             info.setSendTime(df.format(row.getCell(1).getNumericCellValue()));
             }
             }
             //?
             row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierCode(row.getCell(2).getStringCellValue());
             //??
             row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
             info.setCarrierName(row.getCell(3).getStringCellValue());
             //???
             row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
             info.setPinMing(row.getCell(4).getStringCellValue());
             //?
             row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
             info.setJianHao(row.getCell(5).getStringCellValue());
             //???
             row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
             info.setUnit(row.getCell(6).getStringCellValue());
             //?
             row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
             info.setCarCount((int) row.getCell(7).getNumericCellValue());
                    
             imports.add(info);
             }
             */
        }
        return batchAddData(planType, imports);
    } catch (FileNotFoundException ex) {
        logger.error("", ex);
    } catch (IOException ex) {
        logger.error("IO", ex);
    } finally {
        try {
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (IOException ex) {
            logger.error("?", ex);
        }
    }
    return -1;
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToPNG(String excelFilePath, String pngFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???/*from   w w w . java 2  s .c  om*/
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    File file = new File(excelFilePath);
    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    ImageIO.write(image, "png", new File(pngFilePath));
    //        BMPWriter.write(image, new File(pngFilePath));
    System.out.println("Output to png file Success!");
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToBMP(String excelFilePath, String bmpFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???/* w ww  . j  a v  a2s .c  o m*/
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    Workbook wb = WorkbookFactory.create(new File(excelFilePath));
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;
    //        imageWidth = imageWidth;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    BMPWriter.write(image, new File(bmpFilePath));
    System.out.println("Output to png file Success!");
}

From source file:com.cn.servlet.InInterface.java

private ArrayList<Object> importDetailData(String detail, String beanPackage, String tableName, String fileName)
        throws Exception {
    //???// w w w.j a v  a2s. co  m
    Class objClass = Class.forName(beanPackage + tableName);
    Field[] fields = objClass.getDeclaredFields();
    ArrayList<Field> accessFields = new ArrayList<>();
    ArrayList<String> fieldDes = new ArrayList<>();

    for (Field field : fields) {
        if (field.isAnnotationPresent(FieldDescription.class)) {
            FieldDescription description = field.getAnnotation(FieldDescription.class);
            if (description.operate().compareTo("import") == 0) {
                fieldDes.add(description.description());
                accessFields.add(field);
            }
        }
    }

    //?, ?Excel?
    InputStream inputStream;
    File file = new File(fileName);
    inputStream = new FileInputStream(file);
    Sheet sheet;
    if (fileName.endsWith(".xls")) {
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else if (fileName.endsWith(".xlsx")) {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        sheet = workbook.getSheetAt(0);
    } else {
        logger.info("?Excel!");
        return null;
    }

    Row headerRow = sheet.getRow(0);
    //????, ???
    //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size());
    if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) {
        //            json = Units.objectToJson(-1, "???, ?, ???", null);
        return null;
    }

    //?????
    int[] templateDataIndex = new int[fieldDes.size()];
    for (int i = 0; i < fieldDes.size(); i++) {
        Cell cell = headerRow.getCell(i);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String fieldName = cell.getStringCellValue();
        templateDataIndex[fieldDes.indexOf(fieldName)] = i;
    }

    ArrayList<Object> result = new ArrayList<>();
    //??, List
    for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        if (Units.isEmptyRowForExcel(row)) {
            continue;
        }

        Object object = objClass.newInstance();
        //??object(, ??  )
        JSONObject jSONObject = JSONObject.parseObject(detail);
        Iterator<String> keysIterator = jSONObject.keySet().iterator();
        while (keysIterator.hasNext()) {
            String key = keysIterator.next();
            for (Field field : fields) {
                //                    System.out.println("fieldName:" + field.getName() + ",key:" + key);
                if (field.getName().compareToIgnoreCase(key) == 0) {
                    //                        System.out.println("fieldName:" + field.getName() + ",key:" + key + ",key value:" + jSONObject.getString(key));
                    field.setAccessible(true);
                    if (!Units.strIsEmpty(jSONObject.getString(key))) {
                        field.set(object, jSONObject.getString(key));
                    }
                }
            }
        }

        for (int j = 0; j < accessFields.size(); j++) {
            Field field = accessFields.get(j);
            field.setAccessible(true);
            Cell cell = row.getCell(templateDataIndex[j]);
            //                String fieldType = field.getGenericType().toString();

            if (field.getType() == int.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Integer.valueOf(Units.subZeroAndDot(Units.getCellValue(cell))));

                    }
                }
            } else if (field.getType() == float.class) {
                if (cell == null) {
                    field.set(object, 0);
                } else {
                    field.set(object, Float.valueOf(Units.getCellValue(cell)));

                }
            } else if (field.getType() == double.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, 0);
                    } else {
                        field.set(object, Double.valueOf(Units.getCellValue(cell)));

                    }
                }
            } else if (field.getType() == boolean.class) {
                if (cell == null) {
                    field.set(object, false);
                } else {
                    if (Units.strIsEmpty(Units.getCellValue(cell))) {
                        field.set(object, false);
                    } else {
                        field.set(object, Boolean.valueOf(Units.getCellValue(cell)));
                    }
                }
            } else {
                if (cell == null) {
                    field.set(object, null);
                } else {
                    field.set(object, Units.getCellValue(cell));
                }
            }
        }

        result.add(object);
    }
    return result;
}

From source file:com.cpjd.roblu.csv.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file.//  www  .j a v  a  2  s.c  o m
 */
private void convertToCSV() {
    Sheet sheet;
    Row row;
    int lastRowNum;
    this.csvData = new ArrayList<>();

    System.out.println("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.workbook.getNumberOfSheets();

    // and then iterate through them.
    for (int i = 0; i < numSheets; i++) {

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell createInputHeader(Sheet sheet, String label) {
    Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
    Cell cell1 = row.createCell(0);//from  w  w w .  j a v a2 s .  c  o m
    cell1.setCellValue(label);
    cell1.setCellStyle(boldStyle);

    return cell1;
}

From source file:com.dawg6.web.dhcalc.server.ExportExcel.java

License:Open Source License

private Cell createInputCell(Sheet sheet, String label) {
    Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
    Cell cell1 = row.createCell(0);/*from  ww  w.  j  av  a 2s .  com*/
    cell1.setCellValue(label + ":");
    Cell cell2 = row.createCell(1);
    CellUtil.setAlignment(cell2, sheet.getWorkbook(), CellStyle.ALIGN_RIGHT);

    inputCells.put(label, cell2);

    return cell2;
}