Java Code Examples for org.apache.poi.ss.usermodel.Cell#CELL_TYPE_BOOLEAN
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#CELL_TYPE_BOOLEAN .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: ExcelUtils.java From onetwo with Apache License 2.0 | 6 votes |
|
public static Object getCellValue(Cell cell){
if(cell==null)
return null;
int type = cell.getCellType();
Object value = null;
if(Cell.CELL_TYPE_STRING==type){
// value = StringUtils.cleanInvisibleUnicode(cell.getStringCellValue().trim());
value = cell.getStringCellValue().trim();
}else if(Cell.CELL_TYPE_NUMERIC==type){
value = cell.getNumericCellValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
value = cell.getCellFormula();
}else if(Cell.CELL_TYPE_BOOLEAN==type){
value = cell.getBooleanCellValue();
}else if(Cell.CELL_TYPE_BLANK==type){
value = "";
}
return value;
}
Example 2
| Source File: DataImporterController.java From curly with Apache License 2.0 | 6 votes |
|
private String getStringValueFromCell(Cell cell) {
if (cell == null) {
return null;
}
int cellType = cell.getCellType();
if (cellType == Cell.CELL_TYPE_FORMULA) {
cellType = cell.getCachedFormulaResultType();
}
switch (cellType) {
case Cell.CELL_TYPE_BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case Cell.CELL_TYPE_BLANK:
return null;
case Cell.CELL_TYPE_NUMERIC:
double num = cell.getNumericCellValue();
if (num == Math.floor(num)) {
return Integer.toString((int) num);
} else {
return Double.toString(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "???";
}
}
Example 3
| Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0 | 6 votes |
|
private String getCellFormulaContents(Cell cell) {
String contents = null;
switch (cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
contents = getNumericContents(cell);
break;
case Cell.CELL_TYPE_STRING:
contents = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
contents = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
contents = String.valueOf(cell.getCellFormula());
break;
default:
contents = "";
}
return contents;
}
Example 4
| Source File: ExcelImportServer.java From autopoi with Apache License 2.0 | 6 votes |
|
/**
* 获取key的值,针对不同类型获取不同的值
*
* @Author JEECG
* @date 2013-11-21
* @param cell
* @return
*/
private String getKeyValue(Cell cell) {
if(cell==null){
return null;
}
Object obj = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
obj = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
obj = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
obj = cell.getCellFormula();
break;
}
return obj == null ? null : obj.toString().trim();
}
Example 5
| Source File: TestController.java From Mario with Apache License 2.0 | 6 votes |
|
private Object setCellValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString();
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
default:
System.out.println();
return "";
}
}
Example 6
| Source File: ExcelDataReader.java From micro-integrator with Apache License 2.0 | 5 votes |
|
/**
* Extracts the value of a particular cell depending on its type
*
* @param cell A populated Cell instance
* @return Value of the cell
*/
private Object extractCellValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
return cell.getNumericCellValue();
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_FORMULA:
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
default:
return cell.getStringCellValue();
}
}
Example 7
| Source File: PoiSheetUtility.java From easypoi with Apache License 2.0 | 5 votes |
|
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case Cell.CELL_TYPE_ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
}
}
Example 8
| Source File: SheetReaderAbs.java From xcelite with Apache License 2.0 | 5 votes |
|
protected Object readValueFromCell(Cell cell) {
if (cell == null) return null;
Object cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue = cell.getNumericCellValue();
break;
default:
cellValue = cell.getStringCellValue();
}
return cellValue;
}
Example 9
| Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0 | 5 votes |
|
public String getContents() {
String contents = null;
// IllegalStateException occurs , if illegal type defined...
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
contents = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
contents = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
contents = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_FORMULA:
contents = getCellFormulaContents(cell);
break;
case Cell.CELL_TYPE_NUMERIC:
contents = getNumericContents(cell);
break;
case Cell.CELL_TYPE_STRING:
contents = String.valueOf(cell.getStringCellValue());
break;
default:
contents = "";
break;
}
return contents;
}
Example 10
| Source File: CellValueHelper.java From jeasypoi with Apache License 2.0 | 5 votes |
|
public String getHtmlValue(Cell cell) {
if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType() || Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
if (cell.getRichStringCellValue().numFormattingRuns() == 0) {
return XmlEscapers.xmlContentEscaper().escape(cell.getStringCellValue());
} else if (is07) {
return getXSSFRichString((XSSFRichTextString) cell.getRichStringCellValue());
} else {
return getHSSFRichString((HSSFRichTextString) cell.getRichStringCellValue());
}
}
return "";
}
Example 11
| Source File: AbstractExcelExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
protected String getCellValueAsString(Cell cell, int type) {
if(cell != null) {
switch(type) {
case Cell.CELL_TYPE_ERROR: {
return "ERROR"+cell.getErrorCellValue();
}
case Cell.CELL_TYPE_BOOLEAN: {
return ""+cell.getBooleanCellValue();
}
case Cell.CELL_TYPE_NUMERIC: {
if(DateUtil.isCellDateFormatted(cell)) {
return dateFormat.format(cell.getDateCellValue());
}
else {
double value = cell.getNumericCellValue();
String formatString = cell.getCellStyle().getDataFormatString();
int formatIndex = cell.getCellStyle().getDataFormat();
return formatter.formatRawCellContents(value, formatIndex, formatString);
}
}
case Cell.CELL_TYPE_STRING: {
return cell.getRichStringCellValue().getString();
}
}
}
return null;
}
Example 12
| Source File: ExcelUtils.java From onetwo with Apache License 2.0 | 4 votes |
|
public static void copyRow(Sheet worksheet, Row newRow, Row sourceRow) {
Workbook workbook = worksheet.getWorkbook();
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
Cell oldCell = sourceRow.getCell(i);
Cell newCell = newRow.createCell(i);
if (oldCell == null) {
newCell = null;
continue;
}
CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);
if (oldCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
if (oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
}
newCell.setCellType(oldCell.getCellType());
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
}
}
for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
(newRow.getRowNum() +
(cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
)),
cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
worksheet.addMergedRegion(newCellRangeAddress);
}
}
}
Example 13
| Source File: XLSXDataParser.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 4 votes |
|
@Override
public DataRecordMetadata createMetadata() {
if (workbook == null) {
return null;
}
String sheetName = workbook.getSheetName(sheetCounter);
DataRecordMetadata xlsMetadata = new DataRecordMetadata(DataRecordMetadata.EMPTY_NAME, DataRecordMetadata.DELIMITED_RECORD);
xlsMetadata.setLabel(sheetName);
xlsMetadata.setFieldDelimiter(DEFAULT_FIELD_DELIMITER);
xlsMetadata.setRecordDelimiter(DEFAULT_RECORD_DELIMITER);
Row namesRow = null;
if((metadataRow > -1)) {
namesRow = sheet.getRow(metadataRow);
} else {
namesRow = sheet.getRow(firstRow);
}
Row dataRow = sheet.getRow(firstRow);
if(dataRow == null) {
for(int i = 0 ; i < 100; i++) {
dataRow = sheet.getRow(i);
if(dataRow != null) break;
}
}
int maxNumberOfColumns = Math.max(namesRow.getLastCellNum(), dataRow.getLastCellNum());
for (int i = 0; i < maxNumberOfColumns; i++) {
Cell nameCell = (i < namesRow.getLastCellNum()) ? namesRow.getCell(i) : null;
Cell dataCell = (i < dataRow.getLastCellNum()) ? dataRow.getCell(i) : null;
int cellType = (dataCell != null) ? dataCell.getCellType() : Cell.CELL_TYPE_STRING;
if (namesRow != dataRow
&& (nameCell == null || nameCell.getCellType() == Cell.CELL_TYPE_BLANK)
&& (dataCell == null || cellType == Cell.CELL_TYPE_BLANK)) {
continue;
}
String cellName = (metadataRow > -1 && nameCell != null) ?
dataFormatter.formatCellValue(nameCell) : XLSFormatter.getCellCode(i);
DataFieldMetadata dataField = null;
if (cellType == Cell.CELL_TYPE_BOOLEAN) {
dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DataFieldMetadata.BOOLEAN_FIELD, null);
} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DateUtil.isCellDateFormatted(dataCell)
? DataFieldMetadata.DATE_FIELD : DataFieldMetadata.NUMERIC_FIELD, null);
String formatString = dataCell.getCellStyle().getDataFormatString();
if (formatString != null && !formatString.equals(XLSXDataFormatter.GENERAL_FORMAT_STRING)) {
dataField.setFormatStr(formatString);
}
} else {
dataField = new DataFieldMetadata(DataFieldMetadata.EMPTY_NAME, DataFieldMetadata.STRING_FIELD, null);
}
dataField.setLabel(cellName);
xlsMetadata.addField(dataField);
}
xlsMetadata.normalize();
return xlsMetadata;
}
Example 14
| Source File: ExcelUtil.java From game-server with MIT License | 4 votes |
|
/**
* 获取属性值
*
* @param cell
* @param type
* @return
*/
public static Object getCellValue(Cell cell, String type) {
String cellValue = "";
type = type.toLowerCase();
if (cell == null) {
//表格未填数据设置默认值
switch (type) {
case "int":
case "short":
case "byte":
case "long":
return 0;
case "float":
case "double":
return 0.0;
case "array":
return new ArrayList<Document>();
case "object":
return new Document();
case "boolean":
return false;
default:
return cellValue;
}
}
//把数字当成String来读，避免出现1读成1.0的情况
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: //数字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
if ("int".equalsIgnoreCase(type)) {
return Integer.parseInt(cellValue);
} else if ("long".equalsIgnoreCase(type)) {
return Long.parseLong(cellValue);
} else if ("byte".equalsIgnoreCase(type)) {
return Byte.parseByte(cellValue);
} else if ("short".equalsIgnoreCase(type)) {
return Short.parseShort(cellValue);
} else if ("Date".equalsIgnoreCase(type)) {
return new Date(cellValue);
} else if ("boolean".equalsIgnoreCase(type)) {
return Boolean.parseBoolean(cellValue);
} else if ("float".equalsIgnoreCase(type)) {
return Float.parseFloat(cellValue);
} else if ("double".equalsIgnoreCase(type)) {
return Double.parseDouble(cellValue);
} else if ("array".equalsIgnoreCase(type)) {
return MongoUtil.getDocuments(cellValue);
} else if ("object".equalsIgnoreCase(type)) {
return MongoUtil.getDocument(cellValue);
}
return cellValue;
}
Example 15
| Source File: ExcelParser.java From ExcelRecordReaderMapReduce with Apache License 2.0 | 4 votes |
|
public String parseExcelData(InputStream is) {
try {
HSSFWorkbook workbook = new HSSFWorkbook(is);
// Taking first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
currentString = new StringBuilder();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
bytesRead++;
currentString.append(cell.getBooleanCellValue() + "\t");
break;
case Cell.CELL_TYPE_NUMERIC:
bytesRead++;
currentString.append(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
bytesRead++;
currentString.append(cell.getStringCellValue() + "\t");
break;
}
}
currentString.append("\n");
}
is.close();
} catch (IOException e) {
LOG.error("IO Exception : File not found " + e);
}
return currentString.toString();
}
Example 16
| Source File: XSSFSheetXMLHandler.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 4 votes |
|
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
String thisStr = null;
int cellType;
// v => contents of a cell
if (isTextTag(localName)) {
vIsOpen = false;
// Process the value contents as required, now we have it all
switch (nextDataType) {
case BOOLEAN:
char first = value.charAt(0);
thisStr = Character.toString(first);
cellType = Cell.CELL_TYPE_BOOLEAN;
break;
case ERROR:
thisStr = "ERROR:" + value.toString();
cellType = Cell.CELL_TYPE_ERROR;
break;
case FORMULA:
if(formulasNotResults) {
thisStr = formula.toString();
} else {
thisStr = value.toString();
}
cellType = Cell.CELL_TYPE_FORMULA;
break;
case INLINE_STRING:
// TODO: Can these ever have formatting on them?
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = rtsi.toString();
cellType = Cell.CELL_TYPE_STRING;
break;
case SST_STRING:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = rtss.toString();
} catch (NumberFormatException ex) {
System.err.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
}
cellType = Cell.CELL_TYPE_STRING;
break;
case NUMBER:
String n = value.toString();
thisStr = n;
cellType = Cell.CELL_TYPE_NUMERIC;
break;
default:
thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
cellType = -1;
break;
}
// Output
output.cell(cellRef, cellType, formulaType, thisStr, styleIndex);
formulaType = -1;
} else if ("is".equals(localName)) {
isIsOpen = false;
} else if ("row".equals(localName)) {
output.endRow();
} else if ("oddHeader".equals(localName) || "evenHeader".equals(localName) || "firstHeader".equals(localName)) {
hfIsOpen = false;
output.headerFooter(headerFooter.toString(), true, localName);
} else if ("oddFooter".equals(localName) || "evenFooter".equals(localName) || "firstFooter".equals(localName)) {
hfIsOpen = false;
output.headerFooter(headerFooter.toString(), false, localName);
}
}
Example 17
| Source File: SpreadsheetGetCellValue.java From openbd-core with GNU General Public License v3.0 | 4 votes |
|
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
cfSpreadSheetData spreadsheet = null;
int rowNo, columnNo;
/*
* Collect up the parameters
*/
spreadsheet = (cfSpreadSheetData)parameters.get(2);
rowNo = parameters.get(1).getInt() - 1;
columnNo = parameters.get(0).getInt() - 1;
if ( rowNo < 0 )
throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
throwException(_session, "column must be 1 or greater (" + columnNo + ")");
/*
* Find the cell in question
*/
Sheet sheet = spreadsheet.getActiveSheet();
Row row = sheet.getRow( rowNo );
if ( row == null )
row = sheet.createRow( rowNo );
Cell cell = row.getCell( columnNo );
if ( cell == null )
cell = row.createCell( columnNo );
FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();
if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
return new cfNumberData(cellValue.getNumberValue());
case Cell.CELL_TYPE_STRING:
return new cfStringData(cellValue.getStringValue());
default:
return cfStringData.EMPTY_STRING;
}
}else
return cfStringData.EMPTY_STRING;
}
Example 18
| Source File: ExcelUtils.java From ssm-Online_Examination with Apache License 2.0 | 4 votes |
|
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
//解决excel类型问题，获得数值
public String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ，获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}
Example 19
| Source File: POIUtils.java From ermasterr with Apache License 2.0 | 4 votes |
|
public static void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldRowNum, final int newRowNum) {
final HSSFRow oldRow = oldSheet.getRow(oldRowNum);
final HSSFRow newRow = newSheet.createRow(newRowNum);
if (oldRow == null) {
return;
}
newRow.setHeight(oldRow.getHeight());
if (oldRow.getFirstCellNum() == -1) {
return;
}
for (int colNum = oldRow.getFirstCellNum(); colNum <= oldRow.getLastCellNum(); colNum++) {
final HSSFCell oldCell = oldRow.getCell(colNum);
final HSSFCell newCell = newRow.createCell(colNum);
if (oldCell != null) {
final HSSFCellStyle style = oldCell.getCellStyle();
newCell.setCellStyle(style);
final int cellType = oldCell.getCellType();
newCell.setCellType(cellType);
if (cellType == Cell.CELL_TYPE_BOOLEAN) {
newCell.setCellValue(oldCell.getBooleanCellValue());
} else if (cellType == Cell.CELL_TYPE_FORMULA) {
newCell.setCellFormula(oldCell.getCellFormula());
} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
newCell.setCellValue(oldCell.getNumericCellValue());
} else if (cellType == Cell.CELL_TYPE_STRING) {
newCell.setCellValue(oldCell.getRichStringCellValue());
}
}
}
POIUtils.copyMergedRegion(newSheet, getMergedRegionList(oldSheet, oldRowNum), newRowNum);
}
Example 20
| Source File: ExcelUtil.java From util with Apache License 2.0 | 4 votes |
|
/**
* 读取某个工作簿上的所有单元格的值。
* 作者：朱志杰
* July 30, 2013 3:27:08 PM
* @param sheetOrder 工作簿序号，从0开始。
* @return List<Object[]> 所有单元格的值。
* @throws IOException 加载excel文件IO异常。
* @throws FileNotFoundException excel文件没有找到异常。
* @throws InvalidFormatException
*/
public List<Object[]> read(int sheetOrder) throws FileNotFoundException, IOException, InvalidFormatException{
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if(fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
//用来记录excel值
List<Object[]> valueList=new LinkedList<Object[]>();
//循环遍历每一行、每一列。
for(Row row : sheet){
//每一行
Object[] rowObject=null;
for(Cell cell : row){
//cell.getCellType是获得cell里面保存的值的type
switch(cell.getCellType()){
case Cell.CELL_TYPE_BOOLEAN:
//得到Boolean对象的方法
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
//先看是否是日期格式
if(DateUtil.isCellDateFormatted(cell)){
//读取日期格式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getDateCellValue());
}else{
DecimalFormat df = new DecimalFormat();
//单元格的值,替换掉,
String value=df.format(cell.getNumericCellValue()).replace(",","");
//读取数字
rowObject=CollectionUtil.addObjectToArray(rowObject, value);
}
break;
case Cell.CELL_TYPE_FORMULA:
//读取公式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getCellFormula());
break;
case Cell.CELL_TYPE_STRING:
//读取String
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getRichStringCellValue().toString());
break;
}
}
//将这行添加到list。
valueList.add(rowObject);
}
return valueList;
}
