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

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

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

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java

License:Apache License

/**
 * Populate columns.//from   w w w  . j a va 2  s.com
 *
 * @param sheet
 *            the sheet
 * @param templateSheet
 *            the template sheet
 * @param columnNames
 *            the column names
 * @throws Exception
 *             the exception
 */
private void populateColumns(Sheet sheet, TemplateSheet templateSheet, List<String> columnNames)
        throws Exception {
    Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap();
    Row headerRow = sheet.getRow(0);

    if (headerRow == null) {
        throw new Exception("No header row found!");
    }

    for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
        Cell headerCell = headerRow.getCell(i);
        if (headerCell == null) {
            throw new Exception("The following header column is empty: " + i);
        }

        String headerName = headerCell.getStringCellValue().trim();
        if (columnNames.contains(headerName)) {
            log.info("Processing user specified column: " + headerName);
            TemplateColumn column = new TemplateColumn();

            column.setColumnPos(i);
            column.setColumnName(headerName);

            columnMap.put(headerName, column);
        }
    }
}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateWriterTest.java

License:Apache License

/**
 * Test column count./*from   www  . j a v  a2  s.  co  m*/
 */
@Test
public void testColumnCount() {
    Sheet sheet = outputBook.getSheet(TEST_SHEET_NAME);
    Row testRow = sheet.getRow(1);

    int cellCount = testRow.getPhysicalNumberOfCells();

    Assert.assertEquals(3, cellCount);

}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateWriterTest.java

License:Apache License

/**
 * Tests to make sure that we wrote out, exactly what we set in the pojo.
 *//*ww  w .  jav  a2  s.c om*/
@Test
public void testColumnValues() {
    Sheet sheet = outputBook.getSheet(TEST_SHEET_NAME);
    Row testRow = sheet.getRow(1);

    Cell cellOne = testRow.getCell(0);
    Cell cellTwo = testRow.getCell(1);
    Cell cellThree = testRow.getCell(2);

    Assert.assertEquals(POJO_TV_1, cellOne.toString());
    Assert.assertEquals(POJO_TV_2, cellTwo.toString());
    Assert.assertEquals(POJO_TV_3, cellThree.toString());
}

From source file:com.bright.json.PGS.java

License:Open Source License

public static void main(String[] args) throws FileNotFoundException {

    String fileBasename = null;/*from   w ww. j  a  va 2s.  co m*/

    JFileChooser chooser = new JFileChooser();
    try {
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel Spreadsheets", "xls", "xlsx");
        chooser.setFileFilter(filter);
        chooser.setCurrentDirectory(new java.io.File(System.getProperty("user.home")));
        chooser.setDialogTitle("Select the Excel file");

        chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        chooser.setAcceptAllFileFilterUsed(false);

        if (chooser.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {
            System.out.println("getCurrentDirectory(): " + chooser.getCurrentDirectory());
            System.out.println("getSelectedFile() : " + chooser.getSelectedFile());

            // String fileBasename =
            // chooser.getSelectedFile().toString().substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator)+1,chooser.getSelectedFile().toString().lastIndexOf("."));
            fileBasename = chooser.getSelectedFile().toString()
                    .substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator) + 1);
            System.out.println("Base name: " + fileBasename);

        } else {
            System.out.println("No Selection ");

        }
    } catch (Exception e) {

        System.out.println(e.toString());

    }
    String fileName = chooser.getSelectedFile().toString();
    InputStream inp = new FileInputStream(fileName);
    Workbook workbook = null;
    if (fileName.toLowerCase().endsWith("xlsx")) {
        try {
            workbook = new XSSFWorkbook(inp);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } else if (fileName.toLowerCase().endsWith("xls")) {
        try {
            workbook = new HSSFWorkbook(inp);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    Sheet nodeSheet = workbook.getSheet("Devices");
    Sheet interfaceSheet = workbook.getSheet("Interfaces");
    System.out.println("Read nodes sheet.");
    // Row nodeRow = nodeSheet.getRow(1);
    // System.out.println(row.getCell(0).toString());
    // System.exit(0);

    JTextField uiHost = new JTextField("demo.brightcomputing.com");
    // TextPrompt puiHost = new
    // TextPrompt("demo.brightcomputing.com",uiHost);
    JTextField uiUser = new JTextField("root");
    // TextPrompt puiUser = new TextPrompt("root", uiUser);
    JTextField uiPass = new JPasswordField("");
    // TextPrompt puiPass = new TextPrompt("x5deix5dei", uiPass);

    JPanel myPanel = new JPanel(new GridLayout(5, 1));
    myPanel.add(new JLabel("Bright HeadNode hostname:"));
    myPanel.add(uiHost);
    // myPanel.add(Box.createHorizontalStrut(1)); // a spacer
    myPanel.add(new JLabel("Username:"));
    myPanel.add(uiUser);
    myPanel.add(new JLabel("Password:"));
    myPanel.add(uiPass);

    int result = JOptionPane.showConfirmDialog(null, myPanel, "Please fill in all the fields.",
            JOptionPane.OK_CANCEL_OPTION);
    if (result == JOptionPane.OK_OPTION) {
        System.out.println("Input received.");

    }

    String rhost = uiHost.getText();
    String ruser = uiUser.getText();
    String rpass = uiPass.getText();

    String cmURL = "https://" + rhost + ":8081/json";
    List<Cookie> cookies = doLogin(ruser, rpass, cmURL);
    chkVersion(cmURL, cookies);

    Map<String, Long> categories = UniqueKeyMap(cmURL, "cmdevice", "getCategories", cookies);
    Map<String, Long> networks = UniqueKeyMap(cmURL, "cmnet", "getNetworks", cookies);
    Map<String, Long> partitions = UniqueKeyMap(cmURL, "cmpart", "getPartitions", cookies);
    Map<String, Long> racks = UniqueKeyMap(cmURL, "cmpart", "getRacks", cookies);
    Map<String, Long> switches = UniqueKeyMap(cmURL, "cmdevice", "getEthernetSwitches", cookies);
    // System.out.println(switches.get("switch01"));
    // System.out.println("Size of the map: "+ switches.size());
    // System.exit(0);
    cmDevice newnode = new cmDevice();
    cmDevice.deviceObject devObj = new cmDevice.deviceObject();
    cmDevice.switchObject switchObj = new cmDevice.switchObject();
    // cmDevice.netObject netObj = new cmDevice.netObject();

    List<String> emptyslist = new ArrayList<String>();

    // Row nodeRow = nodeSheet.getRow(1);
    // Row ifRow = interfaceSheet.getRow(1);
    // System.out.println(nodeRow.getCell(0).toString());
    // nodeRow.getCell(3).getStringCellValue()
    Map<String, ArrayList<cmDevice.netObject>> ifmap = new HashMap<String, ArrayList<cmDevice.netObject>>();
    // Map<String,netObject> helperMap = new HashMap<String,netObject>();
    // Iterator<Row> rows = interfaceSheet.rowIterator ();
    // while (rows. < interfaceSheet.getPhysicalNumberOfRows()) {

    // List<netObject> netList = new ArrayList<netObject>();
    for (int i = 0; i < interfaceSheet.getPhysicalNumberOfRows(); i++) {
        Row ifRow = interfaceSheet.getRow(i);
        if (ifRow.getRowNum() == 0) {
            continue; // just skip the rows if row number is 0
        }

        System.out.println("Row nr: " + ifRow.getRowNum());
        cmDevice.netObject netObj = new cmDevice.netObject();
        ArrayList<cmDevice.netObject> helperList = new ArrayList<cmDevice.netObject>();
        netObj.setBaseType("NetworkInterface");
        netObj.setCardType(ifRow.getCell(3).getStringCellValue());
        netObj.setChildType(ifRow.getCell(4).getStringCellValue());
        netObj.setDhcp((ifRow.getCell(5).getNumericCellValue() > 0.1) ? true : false);
        netObj.setIp(ifRow.getCell(7).getStringCellValue());
        // netObj.setMac(ifRow.getCell(0).toString());
        //netObj.setModified(true);
        netObj.setName(ifRow.getCell(1).getStringCellValue());
        netObj.setNetwork(networks.get(ifRow.getCell(6).getStringCellValue()));
        //netObj.setOldLocalUniqueKey(0L);
        netObj.setRevision("");
        netObj.setSpeed(ifRow.getCell(8, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setStartIf("ALWAYS");
        netObj.setToBeRemoved(false);
        netObj.setUniqueKey((long) ifRow.getCell(2).getNumericCellValue());
        //netObj.setAdditionalHostnames(new ArrayList<String>(Arrays.asList(ifRow.getCell(9, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*"))));
        //netObj.setMac(ifRow.getCell(10, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setMode((int) ifRow.getCell(11, Row.CREATE_NULL_AS_BLANK).getNumericCellValue());
        netObj.setOptions(ifRow.getCell(12, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setMembers(new ArrayList<String>(Arrays
                .asList(ifRow.getCell(13, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*"))));
        // ifmap.put(ifRow.getCell(0).getStringCellValue(), new
        // HashMap<String, cmDevice.netObject>());
        // helperMap.put(ifRow.getCell(1).getStringCellValue(), netObj) ;
        // ifmap.get(ifRow.getCell(0).getStringCellValue()).putIfAbsent(ifRow.getCell(1).getStringCellValue(),
        // netObj);
        // ifmap.put(ifRow.getCell(0).getStringCellValue(), helperMap);

        if (!ifmap.containsKey(ifRow.getCell(0).getStringCellValue())) {
            ifmap.put(ifRow.getCell(0).getStringCellValue(), new ArrayList<cmDevice.netObject>());
        }
        helperList = ifmap.get(ifRow.getCell(0).getStringCellValue());
        helperList.add(netObj);
        ifmap.put(ifRow.getCell(0).getStringCellValue(), helperList);

        continue;
    }

    for (int i = 0; i < nodeSheet.getPhysicalNumberOfRows(); i++) {
        Row nodeRow = nodeSheet.getRow(i);
        if (nodeRow.getRowNum() == 0) {
            continue; // just skip the rows if row number is 0
        }

        newnode.setService("cmdevice");
        newnode.setCall("addDevice");

        Map<String, Long> ifmap2 = new HashMap<String, Long>();
        for (cmDevice.netObject j : ifmap.get(nodeRow.getCell(0).getStringCellValue()))
            ifmap2.put(j.getName(), j.getUniqueKey());

        switchObj.setEthernetSwitch(switches.get(nodeRow.getCell(8).getStringCellValue()));
        System.out.println(nodeRow.getCell(8).getStringCellValue());
        System.out.println(switches.get(nodeRow.getCell(8).getStringCellValue()));
        switchObj.setPrt((int) nodeRow.getCell(9).getNumericCellValue());
        switchObj.setBaseType("SwitchPort");

        devObj.setBaseType("Device");
        // devObj.setCreationTime(0L);
        devObj.setCustomPingScript("");
        devObj.setCustomPingScriptArgument("");
        devObj.setCustomPowerScript("");
        devObj.setCustomPowerScriptArgument("");
        devObj.setCustomRemoteConsoleScript("");
        devObj.setCustomRemoteConsoleScriptArgument("");
        devObj.setDisksetup("");
        devObj.setBmcPowerResetDelay(0L);
        devObj.setBurning(false);
        devObj.setEthernetSwitch(switchObj);
        devObj.setExcludeListFull("");
        devObj.setExcludeListGrab("");
        devObj.setExcludeListGrabnew("");
        devObj.setExcludeListManipulateScript("");
        devObj.setExcludeListSync("");
        devObj.setExcludeListUpdate("");
        devObj.setFinalize("");
        devObj.setRack(racks.get(nodeRow.getCell(10).getStringCellValue()));
        devObj.setRackHeight((long) nodeRow.getCell(11).getNumericCellValue());
        devObj.setRackPosition((long) nodeRow.getCell(12).getNumericCellValue());
        devObj.setIndexInsideContainer(0L);
        devObj.setInitialize("");
        devObj.setInstallBootRecord(false);
        devObj.setInstallMode("");
        devObj.setIoScheduler("");
        devObj.setLastProvisioningNode(0L);
        devObj.setMac(nodeRow.getCell(6).getStringCellValue());

        devObj.setModified(true);
        devObj.setCategory(categories.get(nodeRow.getCell(1).getStringCellValue()));
        devObj.setChildType("PhysicalNode");
        //devObj.setDatanode((nodeRow.getCell(5).getNumericCellValue() > 0.1) ? true
        //      : false);
        devObj.setHostname(nodeRow.getCell(0).getStringCellValue());
        devObj.setModified(true);
        devObj.setPartition(partitions.get(nodeRow.getCell(2).getStringCellValue()));
        devObj.setUseExclusivelyFor("Category");

        devObj.setNextBootInstallMode("");
        devObj.setNotes("");
        devObj.setOldLocalUniqueKey(0L);

        devObj.setPowerControl(nodeRow.getCell(7).getStringCellValue());

        // System.out.println(ifmap.get("excelnode001").size());
        // System.out.println(ifmap.get(nodeRow.getCell(0).getStringCellValue()).get(nodeRow.getCell(3).getStringCellValue()).getUniqueKey());

        devObj.setManagementNetwork(networks.get(nodeRow.getCell(3).getStringCellValue()));

        devObj.setProvisioningNetwork(ifmap2.get(nodeRow.getCell(4).getStringCellValue()));
        devObj.setProvisioningTransport("RSYNCDAEMON");
        devObj.setPxelabel("");
        // "rack": 90194313218,
        // "rackHeight": 1,
        // "rackPosition": 4,
        devObj.setRaidconf("");
        devObj.setRevision("");

        devObj.setSoftwareImageProxy(null);
        devObj.setStartNewBurn(false);

        devObj.setTag("00000000a000");
        devObj.setToBeRemoved(false);
        // devObj.setUcsInfoConfigured(null);
        // devObj.setUniqueKey(12345L);

        devObj.setUserdefined1("");
        devObj.setUserdefined2("");

        ArrayList<Object> mylist = new ArrayList<Object>();

        ArrayList<cmDevice.netObject> mylist2 = new ArrayList<cmDevice.netObject>();
        ArrayList<Object> emptylist = new ArrayList<Object>();

        devObj.setFsexports(emptylist);
        devObj.setFsmounts(emptylist);
        devObj.setGpuSettings(emptylist);
        devObj.setFspartAssociations(emptylist);

        devObj.setPowerDistributionUnits(emptyslist);

        devObj.setRoles(emptylist);
        devObj.setServices(emptylist);
        devObj.setStaticRoutes(emptylist);

        mylist2 = ifmap.get(nodeRow.getCell(0).getStringCellValue());

        devObj.setNetworks(mylist2);
        mylist.add(devObj);
        mylist.add(1);
        newnode.setArgs(mylist);

        GsonBuilder builder = new GsonBuilder();
        builder.enableComplexMapKeySerialization();

        // Gson g = new Gson();
        Gson g = builder.create();

        String json2 = g.toJson(newnode);

        // To be used from a real console and not Eclipse

        String message = JSonRequestor.doRequest(json2, cmURL, cookies);
        continue;
    }

    JOptionPane optionPaneF = new JOptionPane("The nodes have been added!");
    JDialog myDialogF = optionPaneF.createDialog(null, "Complete:  ");
    myDialogF.setModal(false);
    myDialogF.setVisible(true);
    doLogout(cmURL, cookies);
    // System.exit(0);
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream();
    try {// ww w  .  j av a 2  s  . co m
        Workbook book = WorkbookFactory.create(is);
        Sheet sheet = book.getSheet(execBean.getSheetName());
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<String> names = new ArrayList<String>();
        Map<String, String> typeMap = execBean.getTypeMap();
        int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum();
        for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) {
            Row excelRow = sheet.getRow(rowIdx);

            short minColIx = excelRow.getFirstCellNum();
            short maxColIx = excelRow.getLastCellNum();

            Map<String, Object> row = new HashMap<String, Object>();

            for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) {
                Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK);

                if (rowIdx == 0) {
                    names.add(cell.getStringCellValue());
                } else {
                    String type = null;
                    if (names.size() > colIdx) {
                        type = typeMap.get(names.get(colIdx));
                    }
                    if (StringUtils.isNotEmpty(type)) {
                        if (type.equals("string")) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            row.put(names.get(colIdx), cell.getStringCellValue().trim());
                        } else if (type.equals("double")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), cell.getNumericCellValue());
                        } else if (type.equals("int")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), (int) cell.getNumericCellValue());
                        } else if (type.equals("date")) {
                            row.put(names.get(colIdx), cell.getDateCellValue());
                        } else {
                            throw new DataProcessException("??Excel?");
                        }
                    }
                }
            }
            if (rowIdx != 0) {
                result.add(row);
            }
        }
        context.setAttribute(execBean.getResultName(), result);
    } finally {
        if (is != null) {
            is.close();
        }
    }

}

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

/**
 * ?/*from   w w w  .j a  va  2  s . c  o 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?/*from  ww  w .j  av  a 2  s . c o  m*/
 * @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

/**
 * ?/*from w  w  w  .  ja  v a2  s  .  c  o m*/
 *
 * @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  .j a v a  2  s . co m
    //        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 {
    // ???//from   w  w  w.  j av a  2s  . 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!");
}