Examples with HSSFCell org.apache.poi.hssf.usermodel.HSSFCell used on opensource projects

Example 46 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project TeachingInSimulation by ScOrPiOzzy.

the class ExcelUtil method readExcelCell03.

private static Object[][] readExcelCell03(Workbook wb, String sheetName, int rowNum, int columnNum) throws Exception {
    Object[][] result = null;
    int cellType = 0;
    HSSFRow row = null;
    HSSFCell cell = null;
    result = new Object[rowNum][columnNum];
    HSSFSheet sheet = (HSSFSheet) wb.getSheet(sheetName);
    // 开始循环遍历单元格，取出数据放入result的二维数组中
    for (int i = START_ROW; i < rowNum; i++) {
        row = sheet.getRow(i);
        cellType = -1;
        Object cellValue = null;
        // 确保此行有数据
        if (row == null) {
            result[i] = null;
            continue;
        }
        boolean rowEmptyFlg = true;
        for (int j = 0; j < columnNum; j++) {
            cell = row.getCell(j);
            if (cell != null) {
                rowEmptyFlg = false;
                // 判断单元格内数据类型，
                try {
                    // 數字型必須要先檢測，他既不會走if也不會走catch
                    cellType = cell.getCellType();
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 日期格式需要这样来判断，下面的方法判断不了
                        cellType = CELL_TYPE_DATE;
                    }
                } catch (IllegalStateException e) {
                    cellType = cell.getCellType();
                }
                if (cellType == CELL_TYPE_NULL) {
                    // 空值型
                    result[i][j] = null;
                    continue;
                } else if (cellType == CELL_TYPE_NUM) {
                    // 数值型，避免科学计数法
                    DecimalFormat df = new DecimalFormat("0");
                    cellValue = df.format(cell.getNumericCellValue());
                } else if (cellType == CEll_TYPE_STRING) {
                    // 字符串型
                    cellValue = cell.getStringCellValue();
                } else if (cellType == CELL_TYPE_BOOLEAN) {
                    // boolean型
                    cellValue = new Boolean(cell.getBooleanCellValue());
                } else if (cellType == CELL_TYPE_DATE) {
                    // 日期类型
                    double value = cell.getNumericCellValue();
                    cellValue = DateUtil.getJavaDate(value);
                } else if (cellType == CEll_TYPE_EXPRESSION) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    if ("NaN".equals(cellValue)) {
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                }
                result[i][j] = cellValue;
            } else {
                result[i][j] = null;
            }
        }
        // 如何该行每一列都没有数据，则该行为空
        if (rowEmptyFlg) {
            result[i] = null;
        }
    }
    return result;
}

Example 47 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project processdash by dtuma.

the class WBSExcelWriter method createHeaderRow.

private void createHeaderRow(HSSFSheet sheet, TableColumnModel columns) {
    HSSFRow row = sheet.createRow(0);
    StyleKey style = new StyleKey();
    style.bold = true;
    for (int i = 0; i < columns.getColumnCount(); i++) {
        TableColumn col = columns.getColumn(i);
        String columnName = data.getColumnName(col.getModelIndex());
        HSSFCell cell = row.createCell(s(i + 1));
        cell.setCellValue(new HSSFRichTextString(columnName));
        styleCache.applyStyle(cell, style);
    }
}

Example 48 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project portal by ixinportal.

the class ExcelFileGenerator method createWorkbook.

/**
 * 创建HSSFWorkbook对象
 *
 * @return HSSFWorkbook
 */
public HSSFWorkbook createWorkbook() {
    // 创建workbook对象
    workBook = new HSSFWorkbook();
    int rows = fieldData.size();
    int sheetNum = 1;
    for (int i = 1; i <= sheetNum; i++) {
        // 使用wookbook对象创建sheet对象
        HSSFSheet sheet = workBook.createSheet("Page " + i);
        // 使用HSSFSheet对象创建row，row的下标从0开始
        HSSFRow headRow = sheet.createRow(0);
        for (int j = 0; j < fieldName.size(); j++) {
            // 循环excel的标题
            // 使用HSSFRow创建cell，cell的下标从0开始
            HSSFCell cell = headRow.createCell(j);
            // 添加样式
            // 设置每一列的宽度
            sheet.setColumnWidth(j, 6000);
            // 创建样式
            HSSFCellStyle cellStyle = workBook.createCellStyle();
            // 设置字体
            // 创建字体对象
            HSSFFont font = workBook.createFont();
            // 将字体变为粗体
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 将字体颜色变红色
            short color = HSSFColor.RED.index;
            font.setColor(color);
            // 设置之后的字体
            cellStyle.setFont(font);
            // 添加样式
            // 设置单元格的类型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (fieldName.get(j) != null) {
                cell.setCellStyle(cellStyle);
                // 赋值
                cell.setCellValue((String) fieldName.get(j));
            } else {
                cell.setCellStyle(cellStyle);
                cell.setCellValue("-");
            }
        }
        for (int k = 0; k < rows; k++) {
            // 分页显示数据
            // 使用HSSFSheet对象创建row，row的下标从0开始
            HSSFRow row = sheet.createRow((k + 1));
            // 将数据内容放入excel单元格
            // 循环数据集
            ArrayList rowList = (ArrayList) fieldData.get((i - 1) + k);
            for (int n = 0; n < rowList.size(); n++) {
                // 使用HSSFRow创建cell，cell的下标从0开始
                HSSFCell cell = row.createCell(n);
                if (rowList.get(n) != null) {
                    cell.setCellValue((String) rowList.get(n).toString());
                } else {
                    cell.setCellValue("");
                }
            }
        }
    }
    return workBook;
}

Example 49 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project activityinfo by bedatadriven.

the class ExportUiTest method largeDatabase.

@Test
public void largeDatabase() throws Exception {
    driver.login();
    driver.setup().createDatabase(name(WASH_DATABASE));
    driver.setup().addPartner("ACF", WASH_DATABASE);
    driver.setup().createForm(name(WASH_SITE_FORM), property("database", WASH_DATABASE), property("reportingFrequency", "monthly"));
    driver.setup().createField(name(INDICATOR_NAME), property("form", WASH_SITE_FORM), property("type", "quantity"));
    // Submit 200 sites with 6 months worth of data each
    double expectedTotal = 0;
    for (int i = 0; i < 200; i++) {
        List<MonthlyFieldValue> fieldValues = new ArrayList<>();
        for (int month = 1; month < 6; ++month) {
            int count = month * 10;
            expectedTotal += count;
            MonthlyFieldValue fieldValue = new MonthlyFieldValue();
            fieldValue.setYear(2015);
            fieldValue.setMonth(month);
            fieldValue.setField(INDICATOR_NAME);
            fieldValue.setValue(count);
            fieldValues.add(fieldValue);
        }
        driver.setup().submitForm(WASH_SITE_FORM, "ACF", fieldValues);
    }
    File file = driver.setup().exportForm(WASH_SITE_FORM);
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
    HSSFSheet worksheet = workbook.getSheetAt(0);
    // Find indicator column
    double exportedTotal = 0;
    int indicatorColumn = findColumn(worksheet);
    for (int rowIndex = 2; rowIndex <= worksheet.getLastRowNum(); ++rowIndex) {
        HSSFRow row = worksheet.getRow(rowIndex);
        HSSFCell cell = row.getCell(indicatorColumn);
        exportedTotal += cell.getNumericCellValue();
    }
    assertThat(exportedTotal, equalTo(expectedTotal));
}

Example 50 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project JFramework by gugumall.

the class ExcelUtilViaPOI method main.

public static void main(String[] args) throws Exception {
    JUtilTextWriter log = new JUtilTextWriter(new File("E:\\jstudio\\jframework\\doc\\regions.sql"), "UTF-8");
    log.addLine("use jframework;");
    log.addLine("delete from j_province;");
    log.addLine("delete from j_city;");
    log.addLine("delete from j_county;");
    log.addLine("delete from j_zone;");
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("E:\\JStudio\\JFramework\\doc\\2013最新全国街道乡镇级以上行政区划代码表.xls"));
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("records");
    String provinceId = null;
    String provinceName = null;
    String cityId = null;
    String cityName = null;
    String countyId = null;
    String countyName = null;
    int lr = sheet.getLastRowNum();
    System.out.println("total:" + lr);
    for (int i = 1; i < lr; i++) {
        HSSFRow row = sheet.getRow(i);
        String code = "";
        String pcode = "";
        String name = "";
        String level = "";
        HSSFCell c0 = row.getCell(0);
        if (c0 == null)
            break;
        if (c0.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            code = "" + (int) c0.getNumericCellValue();
        } else {
            code = c0.getStringCellValue();
        }
        HSSFCell c1 = row.getCell(1);
        if (c1.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            pcode = "" + (int) c1.getNumericCellValue();
        } else {
            pcode = c1.getStringCellValue();
        }
        HSSFCell c2 = row.getCell(2);
        if (c2.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            name = "" + (int) c2.getNumericCellValue();
        } else {
            name = c2.getStringCellValue();
        }
        HSSFCell c3 = row.getCell(3);
        if (c3.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            level = "" + (int) c3.getNumericCellValue();
        } else {
            level = c3.getStringCellValue();
        }
        if (level.equals("1")) {
            provinceId = code;
            provinceName = name;
            log.addLine("insert into j_province values ('" + code + "','" + name + "','" + name + "');");
            System.out.println("insert into j_province values ('" + code + "','" + name + "','" + name + "');");
        } else if (level.equals("2")) {
            cityId = code;
            cityName = name;
            if (!name.equals("省直辖行政单位") && !name.equals("市辖区") && !name.equals("县")) {
                log.addLine(" insert into j_city values ('" + code + "','" + provinceId + "','" + name + "','','','');");
                System.out.println(" insert into j_city values ('" + code + "','" + provinceId + "','" + name + "','','','');");
            }
        } else if (level.equals("3")) {
            countyId = code;
            countyName = name;
            if (!cityName.equals("省直辖行政单位") && !cityName.equals("市辖区") && !cityName.equals("县")) {
                if (!name.equals("市辖区")) {
                    log.addLine("  insert into j_county values ('" + code + "','" + cityId + "','" + name + "','','','');");
                    System.out.println("  insert into j_county values ('" + code + "','" + cityId + "','" + name + "','','','');");
                }
            } else {
                if (!name.equals("市辖区")) {
                    log.addLine(" insert into j_city values ('" + code + "','" + provinceId + "','" + name + "','','','');");
                    System.out.println(" insert into j_city values ('" + code + "','" + provinceId + "','" + name + "','','','');");
                }
            }
        } else if (level.equals("4")) {
            if (!cityName.equals("省直辖行政单位") && !cityName.equals("市辖区") && !cityName.equals("县")) {
                log.addLine("   insert into j_zone values ('" + code + "','" + countyId + "','" + name + "','','','');");
                System.out.println("   insert into j_zone values ('" + code + "','" + countyId + "','" + name + "','','','');");
            } else {
                log.addLine("  insert into j_county values ('" + code + "','" + countyId + "','" + name + "','','','');");
                System.out.println("  insert into j_county values ('" + code + "','" + countyId + "','" + name + "','','','');");
            }
        }
    }
}