org.apache.poi.hssf.usermodel.HSSFDateUtil Java Examples
The following examples show how to use
org.apache.poi.hssf.usermodel.HSSFDateUtil.
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: ExcelFileReader.java From birt with Eclipse Public License 1.0 | 6 votes |
|
public String getCellValue(Cell cell) {
if (cell == null)
return ExcelODAConstants.EMPTY_STRING;
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return resolveFormula(cell);
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if( HSSFDateUtil.isCellDateFormatted(cell) ){
Date myjavadate = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
return sdf.format( myjavadate );
}
return ((Double) cell.getNumericCellValue()).toString();
}
return cell.toString();
}
Example #2
| Source File: XlsUtils.java From data-prep with Apache License 2.0 | 6 votes |
|
/**
* Return the numeric value.
*
* @param cell the cell to extract the value from.
* @return the numeric value from the cell.
*/
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
// Date is typed as numeric
if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
return sdf.format(cell.getDateCellValue());
}
// Numeric type (use data formatter to get number format right)
DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);
if (cellValue == null) {
return formatter.formatCellValue(cell);
}
return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
Example #3
| Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 6 votes |
|
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
// 初期化
CellRangeAddress region = null;
Row rowFrom = null;
Row rowTo = null;
Cell cellFrom = null;
Cell cellTo = null;
// セル結合のコピー
for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
region = sheetFrom.getMergedRegion(i);
if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
sheetTo.addMergedRegion(region);
}
}
// セルのコピー
for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
rowFrom = sheetFrom.getRow(intRow);
rowTo = sheetTo.createRow(intRow);
if (null == rowFrom) {
continue;
}
rowTo.setHeight(rowFrom.getHeight());
for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
// セル幅のコピー
sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
cellFrom = rowFrom.getCell(intCol);
cellTo = rowTo.createCell(intCol);
if (null == cellFrom) {
continue;
}
// セルスタイルとタイプのコピー
cellTo.setCellStyle(cellFrom.getCellStyle());
cellTo.setCellType(cellFrom.getCellType());
// タイトル内容のコピー
// 不同数据类型处理
int cellFromType = cellFrom.getCellType();
cellTo.setCellType(cellFromType);
if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
cellTo.setCellValue(cellFrom.getDateCellValue());
} else {
cellTo.setCellValue(cellFrom.getNumericCellValue());
}
} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
cellTo.setCellValue(cellFrom.getRichStringCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
cellTo.setCellValue(cellFrom.getBooleanCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
cellTo.setCellFormula(cellFrom.getCellFormula());
} else { // nothing29
}
}
}
// 枠線の設定
sheetTo.setDisplayGridlines(false);
// sheetTo.setDisplayGuts(true);
// sheetTo.setDisplayRowColHeadings(true);
// 剪切
// sheetTo.shiftRows(13, 15, 31, false, false, false);
// Excelのズーム設定
sheetTo.setZoom(85, 100);
// シートを戻る。
return sheetTo;
}
Example #4
| Source File: ExcelUtil.java From javautils with Apache License 2.0 | 5 votes |
|
/**
* 获取cell数据
* @param cell
* @return
*/
private static String getCellValue(Cell cell){
String value = "";
if(cell != null) {
switch (cell.getCellTypeEnum()) {
case FORMULA:
value += cell.getCellFormula();
break;
case NUMERIC:
double cellValue = cell.getNumericCellValue();
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = HSSFDateUtil.getJavaDate(cellValue);
value += DateUtil.format(date, DateUtil.DATE_TIME);
}else{
value += cell.getNumericCellValue();
}
break;
case STRING:
value += cell.getStringCellValue();
break;
case BLANK:
break;
case BOOLEAN:
value += cell.getBooleanCellValue();
break;
case ERROR:
break;
default:break;
}
}
return value;
}
Example #5
| Source File: PoiCell.java From pentaho-kettle with Apache License 2.0 | 5 votes |
|
public KCellType getType() {
int type = cell.getCellType();
if ( type == Cell.CELL_TYPE_BOOLEAN ) {
return KCellType.BOOLEAN;
} else if ( type == Cell.CELL_TYPE_NUMERIC ) {
if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
return KCellType.DATE;
} else {
return KCellType.NUMBER;
}
} else if ( type == Cell.CELL_TYPE_STRING ) {
return KCellType.LABEL;
} else if ( type == Cell.CELL_TYPE_BLANK || type == Cell.CELL_TYPE_ERROR ) {
return KCellType.EMPTY;
} else if ( type == Cell.CELL_TYPE_FORMULA ) {
switch ( cell.getCachedFormulaResultType() ) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
return KCellType.EMPTY;
case Cell.CELL_TYPE_BOOLEAN:
return KCellType.BOOLEAN_FORMULA;
case Cell.CELL_TYPE_STRING:
return KCellType.STRING_FORMULA;
case Cell.CELL_TYPE_NUMERIC:
if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
return KCellType.DATE_FORMULA;
} else {
return KCellType.NUMBER_FORMULA;
}
default:
break;
}
}
return null;
}
Example #6
| Source File: XlsSchemaParser.java From data-prep with Apache License 2.0 | 5 votes |
|
private String getTypeFromNumericCell(Cell cell) {
try {
return HSSFDateUtil.isCellDateFormatted(cell) ? DATE.getName() : NUMERIC.getName();
} catch (IllegalStateException e) {
return ANY.getName();
}
}
Example #7
| Source File: ExcelImportService.java From fredbet with Creative Commons Attribution Share Alike 4.0 International | 5 votes |
|
private Match convertRowToMatch(Row row) {
if (row == null || row.getCell(0) == null) {
return null;
}
String country1 = row.getCell(0).getStringCellValue();
String country2 = row.getCell(1).getStringCellValue();
String group = row.getCell(2).getStringCellValue();
Date kickOffDate = HSSFDateUtil.getJavaDate(row.getCell(3).getNumericCellValue());
String stadium = row.getCell(4).getStringCellValue();
Match match = new Match();
if (Country.fromName(country1) != null) {
match.getTeamOne().setCountry(Country.fromName(country1));
} else {
match.getTeamOne().setName(country1);
}
if (Country.fromName(country2) != null) {
match.getTeamTwo().setCountry(Country.fromName(country2));
} else {
match.getTeamTwo().setName(country2);
}
match.setGroup(Group.valueOf(group));
match.setKickOffDate(DateUtils.toLocalDateTime(kickOffDate));
match.setStadium(stadium);
return match;
}
Example #8
| Source File: Wrapper.java From ExcelReads with Apache License 2.0 | 5 votes |
|
protected String getCellFormatValue(Cell cell) {
String cellValue;
if (cell != null) {
switch (cell.getCellType()) {
case NUMERIC:
cellValue = df.format(cell.getNumericCellValue());
break;
case FORMULA: {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(date);
} else {
cellValue = String.valueOf(cell.getStringCellValue());
}
break;
}
case STRING:
cellValue = cell.getStringCellValue();
break;
case ERROR:
case BLANK:
default:
cellValue = BLANK;
}
} else {
cellValue = BLANK;
}
return cellValue;
}
Example #9
| Source File: ExcelUtil.java From albedo with GNU Lesser General Public License v3.0 | 5 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column) {
if (row == null) {
return row;
}
Object val = "";
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// POI Excel 日期格式转换
val = DateUtil.getJavaDate((Double) val);
} else {
if ((Double) val % 1 > 0) {
val = new DecimalFormat("0.00").format(val);
} else {
val = new DecimalFormat("0").format(val);
}
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellTypeEnum() == CellType.ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
Example #10
| Source File: StyleTest.java From easyexcel with Apache License 2.0 | 5 votes |
|
private void isDate(Cell cell) {
System.out.println(
DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString()));
System.out.println(HSSFDateUtil.isCellDateFormatted(cell));
DataFormatter f = new DataFormatter();
System.out.println(f.formatCellValue(cell));
}
Example #11
| Source File: ExcelUtil.java From RuoYi with Apache License 2.0 | 5 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
private Object getCellValue(Row row, int column) {
if (row == null) {
return null;
}
Object val = "" ;
try {
Cell cell = row.getCell(column);
if (cell != null) {
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// POI Excel 日期格式转换
val = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) val);
} else {
if ((Double) val % 1 > 0) {
val = new DecimalFormat("0.00").format(val);
} else {
val = new DecimalFormat("0").format(val);
}
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
val = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
val = cell.getBooleanCellValue();
} else if (cell.getCellTypeEnum() == CellType.ERROR) {
val = cell.getErrorCellValue();
}
}
} catch (Exception e) {
return val;
}
return val;
}
Example #12
| Source File: PoiCell.java From hop with Apache License 2.0 | 5 votes |
|
public KCellType getType() {
int type = cell.getCellType();
if ( type == Cell.CELL_TYPE_BOOLEAN ) {
return KCellType.BOOLEAN;
} else if ( type == Cell.CELL_TYPE_NUMERIC ) {
if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
return KCellType.DATE;
} else {
return KCellType.NUMBER;
}
} else if ( type == Cell.CELL_TYPE_STRING ) {
return KCellType.LABEL;
} else if ( type == Cell.CELL_TYPE_BLANK || type == Cell.CELL_TYPE_ERROR ) {
return KCellType.EMPTY;
} else if ( type == Cell.CELL_TYPE_FORMULA ) {
switch ( cell.getCachedFormulaResultType() ) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
return KCellType.EMPTY;
case Cell.CELL_TYPE_BOOLEAN:
return KCellType.BOOLEAN_FORMULA;
case Cell.CELL_TYPE_STRING:
return KCellType.STRING_FORMULA;
case Cell.CELL_TYPE_NUMERIC:
if ( HSSFDateUtil.isCellDateFormatted( cell ) ) {
return KCellType.DATE_FORMULA;
} else {
return KCellType.NUMBER_FORMULA;
}
default:
break;
}
}
return null;
}
Example #13
| Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 4 votes |
|
/**
* @方法名 copySheet
* @功能 复制sheet
* @参数 @param sheetFrom
* @参数 @param sheetTo
* @参数 @return
* @返回 HSSFSheet
* @author Administrator
* @throws
*/
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) {
// 初期化
CellRangeAddress region = null;
Row rowFrom = null;
Row rowTo = null;
Cell cellFrom = null;
Cell cellTo = null;
// セル結合のコピー
for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
region = sheetFrom.getMergedRegion(i);
if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
sheetTo.addMergedRegion(region);
}
}
// セルのコピー
for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
rowFrom = sheetFrom.getRow(intRow);
rowTo = sheetTo.createRow(intRow);
if (null == rowFrom) {
continue;
}
rowTo.setHeight(rowFrom.getHeight());
for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
// セル幅のコピー
sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
cellFrom = rowFrom.getCell(intCol);
cellTo = rowTo.createCell(intCol);
if (null == cellFrom) {
continue;
}
// セルスタイルとタイプのコピー
cellTo.setCellStyle(cellFrom.getCellStyle());
cellTo.setCellType(cellFrom.getCellType());
// タイトル内容のコピー
// 不同数据类型处理
int cellFromType = cellFrom.getCellType();
cellTo.setCellType(cellFromType);
if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
cellTo.setCellValue(cellFrom.getDateCellValue());
} else {
cellTo.setCellValue(cellFrom.getNumericCellValue());
}
} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
cellTo.setCellValue(cellFrom.getRichStringCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
cellTo.setCellValue(cellFrom.getBooleanCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
cellTo.setCellFormula(cellFrom.getCellFormula());
} else { // nothing29
}
}
}
// 枠線の設定
sheetTo.setDisplayGridlines(false);
// sheetTo.setDisplayGuts(true);
// sheetTo.setDisplayRowColHeadings(true);
// 剪切
// sheetTo.shiftRows(13, 15, 31, false, false, false);
// Excelのズーム設定
sheetTo.setZoom(85, 100);
// シートを戻る。
return sheetTo;
}
Example #14
| Source File: ExcelUtil.java From LuckyFrameWeb with GNU Affero General Public License v3.0 | 4 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column)
{
if (row == null)
{
return null;
}
Object val = "";
try
{
Cell cell = row.getCell(column);
if (cell != null)
{
if (cell.getCellTypeEnum() == CellType.NUMERIC)
{
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}
else
{
if ((Double) val % 1 > 0)
{
val = new DecimalFormat("0.00").format(val);
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellTypeEnum() == CellType.STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellTypeEnum() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
}
}
catch (Exception e)
{
return val;
}
return val;
}
Example #15
| Source File: ExcelUtil.java From supplierShop with MIT License | 4 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column)
{
if (row == null)
{
return row;
}
Object val = "";
try
{
Cell cell = row.getCell(column);
if (cell != null)
{
if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
{
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}
else
{
if ((Double) val % 1 > 0)
{
val = new DecimalFormat("0.00").format(val);
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellTypeEnum() == CellType.STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellTypeEnum() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
}
}
catch (Exception e)
{
return val;
}
return val;
}
Example #16
| Source File: ExcelHelp.java From hy.common.report with Apache License 2.0 | 4 votes |
|
public final static PartitionMap<String ,RCell> readDatas(Sheet i_Sheet ,Integer i_BeginRow ,Integer i_EndRow)
{
PartitionMap<String ,RCell> v_Ret = new TablePartition<String ,RCell>();
Sheet v_Sheet = i_Sheet;
int v_BeginRow = 0;
int v_EndRow = 0;
if ( i_BeginRow != null )
{
v_BeginRow = i_BeginRow.intValue();
if ( v_BeginRow < 0 )
{
v_BeginRow = 0;
}
}
if ( i_EndRow != null )
{
v_EndRow = i_EndRow.intValue();
}
else
{
v_EndRow = v_Sheet.getLastRowNum();
}
for (int v_RowNo=v_BeginRow; v_RowNo<=v_EndRow; v_RowNo++)
{
Row v_Row = v_Sheet.getRow(v_RowNo);
if ( v_Row == null )
{
continue;
}
short v_CellCount = v_Row.getLastCellNum();
for (int v_ColumnNo=0; v_ColumnNo<v_CellCount; v_ColumnNo++)
{
Cell v_Cell = v_Row.getCell(v_ColumnNo);
if ( v_Cell == null )
{
continue;
}
if ( v_Cell.getCellTypeEnum() == CellType.STRING )
{
String v_Value = v_Cell.getStringCellValue();
if ( !Help.isNull(v_Value) )
{
RCell v_RCell = new RCell(v_RowNo ,v_ColumnNo);
List<String> v_Decimals = StringHelp.getString(v_Cell.getCellStyle().getDataFormatString() ,$Decimal);
if ( !Help.isNull(v_Decimals) )
{
v_RCell.setDecimal(v_Decimals.get(0).split("\\.")[1].length());
}
v_Ret.putRow(v_Value ,v_RCell); // 2018-05-07 修复：不再trim()。因为去空格后，原数据格式会被改变，比如说用户就是想导出空格呢？
// 其二，当为占位符查询时，也是不去空格的查询，这里去空格后，为查询不到匹配的占位符解析信息的。
}
}
else if ( v_Cell.getCellTypeEnum() == CellType.NUMERIC )
{
if ( HSSFDateUtil.isCellDateFormatted(v_Cell) )
{
if ( v_Cell.getDateCellValue() != null )
{
v_Ret.putRow((new Date(v_Cell.getDateCellValue())).getFull() ,new RCell(v_RowNo ,v_ColumnNo));
}
}
else
{
v_Ret.putRow(String.valueOf(v_Cell.getNumericCellValue()) ,new RCell(v_RowNo ,v_ColumnNo));
}
}
}
}
return v_Ret;
}
Example #17
| Source File: JavaToExcel.java From hy.common.report with Apache License 2.0 | 4 votes |
|
/**
* 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
*
* @author ZhengWei(HY)
* @createDate 2017-07-03
* @version v1.0
*
* @param i_RTemplate 模板对象
* @param i_TemplateCell 模板中的单元格对象
* @param i_DataWorkbook 数据工作薄
* @param i_DataCell 数据中的单元格对象
* @param io_RSystemValue 系统变量信息
* @param i_Datas 本行对应的数据
* @param io_RValue 小计循环的迭代器
* @return
*/
public final static void copyCellByBlankSpace(RTemplate i_RTemplate ,Cell i_TemplateCell ,RWorkbook i_DataWorkbook ,Cell i_DataCell ,RSystemValue io_RSystemValue)
{
// 复制样式
i_DataCell.setCellStyle(i_DataWorkbook.getCellStyle(i_RTemplate ,i_TemplateCell.getCellStyle().getIndex()));
// 复制评论
copyComment(i_RTemplate ,i_TemplateCell ,i_DataWorkbook ,i_DataCell);
// 复制数据类型
CellType v_CellType = i_TemplateCell.getCellTypeEnum();
// i_DataCell.setCellType(v_CellType); 不能在此统一设置，原因是：下面代码对类型是有浮动的
if ( v_CellType == CellType.NUMERIC )
{
i_DataCell.setCellType(v_CellType);
if ( HSSFDateUtil.isCellDateFormatted(i_TemplateCell) )
{
i_DataCell.setCellValue(i_TemplateCell.getDateCellValue());
}
else
{
i_DataCell.setCellValue(i_TemplateCell.getNumericCellValue());
}
}
else if ( v_CellType == CellType.STRING )
{
RichTextString v_TemplateRichText = i_TemplateCell.getRichStringCellValue();
String v_ValueName = v_TemplateRichText.toString();
if ( i_RTemplate.isExists(v_ValueName) )
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellValue("");
}
else
{
i_DataCell.setCellType(v_CellType);
copyRichTextStyle(i_RTemplate ,v_TemplateRichText ,i_DataWorkbook ,i_DataCell);
}
}
else if ( v_CellType == CellType.BOOLEAN )
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellValue(i_TemplateCell.getBooleanCellValue());
}
else if ( v_CellType == CellType.FORMULA)
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellFormula(ExcelFormula.calcFormulaOffset(i_TemplateCell ,i_DataCell));
}
else
{
// Nothing.
i_DataCell.setCellType(v_CellType);
}
}
Example #18
| Source File: TypeKit.java From jfinal-ext3 with Apache License 2.0 | 4 votes |
|
public static Object convert(String value, Class<?> type, String format) {
if (StrKit.notBlank(value)) {
if (String.class.equals(type)) {
return value;
}
if (Integer.class.equals(type) || int.class.equals(type)) {
return Integer.parseInt(value);
}
if (Double.class.equals(type) || double.class.equals(type)) {
return Double.parseDouble(value);
}
if (Float.class.equals(type) || float.class.equals(type)) {
return Float.parseFloat(value);
}
if (Boolean.class.equals(type) || boolean.class.equals(type)) {
String valueLower = value.toLowerCase();
if (valueLower.equals("true") || valueLower.equals("false")) {
return Boolean.parseBoolean(value.toLowerCase());
}
Integer integer = Integer.parseInt(value);
if (integer == 0) {
return false;
} else {
return true;
}
}
if (Long.class.equals(type) || long.class.equals(type)) {
return Long.parseLong(value);
}
if (BigInteger.class.equals(type)) {
return (new BigInteger(value));
}
if (byte[].class.equals(type)) {
return value.getBytes();
}
if (Short.class.equals(type)) {
return Short.parseShort(value);
}
if (Byte.class.equals(type)) {
return Byte.parseByte(value);
}
if (Date.class.equals(type)
|| java.sql.Date.class.equals(type)
|| java.sql.Time.class.equals(type)
|| java.sql.Timestamp.class.equals(type)) {
Date date = null;
if (value.contains("-") || value.contains("/") || value.contains(":")) {
date = getSimpleDateFormatDate(value, format);
} else {
Double d = Double.parseDouble(value);
date = HSSFDateUtil.getJavaDate(d, true);
}
if (java.sql.Date.class.equals(type)) {
return new java.sql.Date(date.getTime());
}
if (java.sql.Time.class.equals(type)) {
return new java.sql.Time(date.getTime());
}
if (java.sql.Timestamp.class.equals(type)) {
return new java.sql.Timestamp(date.getTime());
}
return date;
}
if (BigDecimal.class.equals(type)) {
return new BigDecimal(value);
}
}
return null;
}
Example #19
| Source File: ExcelUtil.java From jeewx with Apache License 2.0 | 4 votes |
|
/**
* 读取 Excel文件内容
*
* @param excel_name
* @return
* @throws Exception
*/
public static List<String[]> readExcel(String excel_name) throws Exception {
// 结果集
List<String[]> list = new ArrayList<String[]>();
HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
excel_name));
// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
HSSFRow hssfrow = hssfsheet.getRow(j);
if(hssfrow!=null){
int col = hssfrow.getPhysicalNumberOfCells();
// 单行数据
String[] arrayString = new String[col];
for (int i = 0; i < col; i++) {
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
} else if (cell.getCellType() == 0) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString[i] = formater.format(d);
} else {
arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue()+"";
}
}
} else {// 如果EXCEL表格中的数据类型为字符串型
arrayString[i] = cell.getStringCellValue().trim();
}
}
list.add(arrayString);
}
}
return list;
}
Example #20
| Source File: ExcelToSQLite.java From SQLiteToExcel with Apache License 2.0 | 4 votes |
|
/**
* create table by sheet
*
* @param sheet
*/
private void createTable(Sheet sheet) {
StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
createTableSql.append(sheet.getSheetName());
createTableSql.append("(");
Iterator<Row> rit = sheet.rowIterator();
Row rowHeader = rit.next();
List<String> columns = new ArrayList<>();
for (int i = 0; i < rowHeader.getPhysicalNumberOfCells(); i++) {
createTableSql.append(rowHeader.getCell(i).getStringCellValue());
if (i == rowHeader.getPhysicalNumberOfCells() - 1) {
createTableSql.append(" TEXT");
} else {
createTableSql.append(" TEXT,");
}
columns.add(rowHeader.getCell(i).getStringCellValue());
}
createTableSql.append(")");
database.execSQL(createTableSql.toString());
while (rit.hasNext()) {
Row row = rit.next();
ContentValues values = new ContentValues();
for (int n = 0; n < row.getPhysicalNumberOfCells(); n++) {
if (row.getCell(n) == null) {
continue;
}
if (row.getCell(n).getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(row.getCell(n))) {
if (sdf == null) {
values.put(columns.get(n), DateFormat.getDateTimeInstance().format(row.getCell(n).getDateCellValue()));
} else {
values.put(columns.get(n), sdf.format(row.getCell(n).getDateCellValue()));
}
} else {
String value = getRealStringValueOfDouble(row.getCell(n).getNumericCellValue());
values.put(columns.get(n), value);
}
} else if (row.getCell(n).getCellType() == Cell.CELL_TYPE_STRING) {
values.put(columns.get(n), row.getCell(n).getStringCellValue());
}
}
if (values.size() == 0)
continue;
long result = database.insert(sheet.getSheetName(), null, values);
if (result < 0) {
throw new RuntimeException("Insert value failed!");
}
}
}
Example #21
| Source File: ExcelUtil.java From ruoyiplus with MIT License | 4 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column)
{
if (row == null)
{
return row;
}
Object val = "";
try
{
Cell cell = row.getCell(column);
if (cell != null)
{
if (cell.getCellTypeEnum() == CellType.NUMERIC)
{
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}
else
{
if ((Double) val % 1 > 0)
{
val = new DecimalFormat("0.00").format(val);
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellTypeEnum() == CellType.STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellTypeEnum() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
}
}
catch (Exception e)
{
return val;
}
return val;
}
Example #22
| Source File: ExcelUtil.java From RuoYi-Vue with MIT License | 4 votes |
|
/**
* 获取单元格值
*
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column)
{
if (row == null)
{
return row;
}
Object val = "";
try
{
Cell cell = row.getCell(column);
if (StringUtils.isNotNull(cell))
{
if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
{
val = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell))
{
val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
}
else
{
if ((Double) val % 1 > 0)
{
val = new DecimalFormat("0.00").format(val);
}
else
{
val = new DecimalFormat("0").format(val);
}
}
}
else if (cell.getCellTypeEnum() == CellType.STRING)
{
val = cell.getStringCellValue();
}
else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
{
val = cell.getBooleanCellValue();
}
else if (cell.getCellTypeEnum() == CellType.ERROR)
{
val = cell.getErrorCellValue();
}
}
}
catch (Exception e)
{
return val;
}
return val;
}
Example #23
| Source File: XlsTable.java From Leo with Apache License 2.0 | 4 votes |
|
public Object getValue(int row, String column) throws DataSetException {
if (logger.isDebugEnabled())
logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);
assertValidRowIndex(row);
int columnIndex = getColumnIndex(column);
HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
if (cell == null) {
return null;
}
int type = cell.getCellType();
switch (type) {
case HSSFCell.CELL_TYPE_NUMERIC:
HSSFCellStyle style = cell.getCellStyle();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return getDateValue(cell);
} else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
// The special dbunit date format
return getDateValueFromJavaNumber(cell);
} else {
return getNumericValue(cell);
}
case HSSFCell.CELL_TYPE_STRING:
return cell.getRichStringCellValue().getString();
case HSSFCell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" +
row + ", column=" + column);
case HSSFCell.CELL_TYPE_BLANK:
return null;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case HSSFCell.CELL_TYPE_ERROR:
throw new DataTypeException("Error at row=" + row +
", column=" + column);
default:
throw new DataTypeException("Unsupported type at row=" + row +
", column=" + column);
}
}
Example #24
| 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 #25
| Source File: StreamingCell.java From excel-streaming-reader with Apache License 2.0 | 3 votes |
|
/**
* Get the value of the cell as a date. For strings we throw an exception. For
* blank cells we return a null.
*
* @return the value of the cell as a date
* @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING
* @throws NumberFormatException if the cell value isn't a parsable <code>double</code>.
*/
@Override
public Date getDateCellValue() {
if(getCellType() == CellType.STRING){
throw new IllegalStateException("Cell type cannot be CELL_TYPE_STRING");
}
return rawContents == null ? null : HSSFDateUtil.getJavaDate(getNumericCellValue(), use1904Dates);
}
