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;
}
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);
}
}
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;
}
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));
}
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 + "','','','');");
}
}
}
}
Aggregations