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

Example 76 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet 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 77 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project drbookings by DrBookings.

the class RunnableImportCSVBooking method process.

@Override
protected List<BookingBean> process(final IProgressMonitor monitor) throws Exception {
    try {
        if (logger.isInfoEnabled()) {
            logger.info("Reading " + file);
        }
        final FileInputStream stream = new FileInputStream(file);
        final HSSFWorkbook workbook = new HSSFWorkbook(stream);
        final HSSFSheet sheet = workbook.getSheetAt(0);
        if (logger.isInfoEnabled()) {
            logger.info("Processing sheet " + sheet.getSheetName());
        }
        final int indexBookingNumber = getColumnIndexBookingNumber(sheet.getRow(0));
        final int indexClientName = getColumnIndexClientName(sheet.getRow(0));
        final int indexBookingCheckIn = getColumnIndexCheckIn(sheet.getRow(0));
        final int indexBookingCheckOut = getColumnIndexCheckOut(sheet.getRow(0));
        final int indexStatus = getColumnIndexStatus(sheet.getRow(0));
        final List<Integer> bookingNumbers = new ArrayList<>();
        final List<String> guestNames = new ArrayList<>();
        final List<String> stati = new ArrayList<>();
        final List<LocalDate> bookingCheckIn = new ArrayList<>();
        final List<LocalDate> bookingCheckOut = new ArrayList<>();
        for (final Row r : sheet) {
            // skip first row
            if (r.getRowNum() == 0) {
                continue;
            }
            bookingNumbers.add(getBookingNumber(r.getCell(indexBookingNumber)));
            guestNames.add(getString(r.getCell(indexClientName)));
            bookingCheckIn.add(getDate(r.getCell(indexBookingCheckIn)));
            bookingCheckOut.add(getDate(r.getCell(indexBookingCheckOut)));
            stati.add(getString(r.getCell(indexStatus)));
        }
        if (logger.isDebugEnabled()) {
            logger.debug("Booking numbers: " + bookingNumbers);
            logger.debug("Guest names: " + guestNames);
            logger.debug("Check-in dates: " + bookingCheckIn);
            logger.debug("Check-out dates: " + bookingCheckOut);
        }
        if (logger.isInfoEnabled()) {
            logger.info("Building bookings.. ");
        }
        final List<BookingBean> bookings = new ArrayList<>();
        for (int i = 0; i < bookingNumbers.size(); i++) {
            final int number = bookingNumbers.get(i);
            final LocalDate checkIn = bookingCheckIn.get(i);
            final LocalDate checkOut = bookingCheckOut.get(i);
            final String names = guestNames.get(i);
            final String status = stati.get(i);
            bookings.add(new BookingBean(Integer.toString(number), checkIn, checkOut, Arrays.asList(names.split(","))).setStatus(status));
        }
        return bookings;
    } finally {
        monitor.done();
    }
}

Example 78 with HSSFSheet

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

the class DbUserExport method createSheet.

public void createSheet() {
    HSSFSheet sheet = book.createSheet(composeUniqueSheetName("db-users-list"));
    sheet.createFreezePane(4, 2);
    // initConditionalFormatting(sheet);
    createHeaders(sheet);
    createDataRows(sheet);
}

Example 79 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet 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 + "','','','');");
            }
        }
    }
}

Example 80 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project financial by greatkendy123.

the class ExportQuotaPayExcel method export.

/*
	 * 导出数据
	 */
public void export() throws Exception {
    try {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建工作表
        HSSFSheet sheet = workbook.createSheet(title);
        // 产生表格标题行
        HSSFRow rowm = sheet.createRow(0);
        HSSFCell cellTiltle = rowm.createCell(0);
        // // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展
        // 获取列头样式对象
        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
        // 单元格样式对象
        HSSFCellStyle style = this.getStyle(workbook);
        /**
         *********************************** 正文 ***************************
         */
        // 定义所需列数
        int columnNum = rowName.length;
        // 在索引2的位置创建行(最顶端的行开始的第二行)
        HSSFRow rowRowName = sheet.createRow(1);
        /**
         *********************************** 标题栏 ***************************
         */
        for (int n = 0; n < columnNum; n++) {
            // 创建列头对应个数的单元格
            HSSFCell cellRowName = rowRowName.createCell(n);
            // 设置列头单元格的数据类型
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            // 设置列头单元格的值
            cellRowName.setCellValue(text);
            // 设置列头单元格样式
            cellRowName.setCellStyle(columnTopStyle);
        }
        /**
         ******将查询出的数据设置到sheet对应的单元格中******************
         */
        for (int i = 0; i < dataList.size(); i++) {
            // 遍历每个对象
            Object[] obj = dataList.get(i);
            // 创建所需的行数
            HSSFRow row = sheet.createRow(i + 2);
            // 设置单元格的数据类型
            HSSFCell cell = null;
            for (int j = 0; j < obj.length; j++) {
                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                    // 设置单元格的值
                    cell.setCellValue(obj[j].toString());
                }
                // 设置单元格样式
                cell.setCellStyle(style);
            }
        }
        // 让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                    currentRow.setHeight((short) 400);
                }
            }
            if (colNum == 0 || colNum == 1) {
                sheet.setColumnWidth(colNum, (columnWidth + 4) * 500);
            } else if (colNum == 5) {
                sheet.setColumnWidth(colNum, (columnWidth + 6) * 500);
            } else if (colNum == 6) {
                sheet.setColumnWidth(colNum, (columnWidth + 10) * 500);
            } else {
                sheet.setColumnWidth(colNum, (columnWidth + 1) * 400);
            }
        }
        if (workbook != null) {
            OutputStream out = null;
            try {
                File file = new File(this.out + ".xls");
                out = new FileOutputStream(file);
                workbook.write(out);
                java.awt.Desktop.getDesktop().open(file);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (out != null)
                    out.close();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}