1, 对于类似电话号码或手机一类的大数值读取问题
- // 取值后会带一个E的问题
- double cellValue = row.getCell(k).getNumericCellValue();
- new DecimalFormat("#").format(cellValue);
- //如874349394后台获取时会变成科学计数法的数字
- 自己的代码:private String getCellStrValue2(HSSFCell cell) {
if (cell == null) {
return "";
}
try {
// 按照字符串格式读取
log.debug("getCellStrValue:= |" + cell.getStringCellValue());
return cell.getStringCellValue();
} catch (Exception e) {
// 读取出错,说明是数字格式
String s = new DecimalFormat("#.##").format(cell.getNumericCellValue());
log.debug("getCellStrValue:=" + s);
return s;
}
}
DecimalFormat用法
DecimalFormat 是 NumberFormat 的一个具体子类,用于格式化十进制数字。
DecimalFormat 包含一个模式 和一组符号
符号含义:
0 一个数字
# 一个数字,不包括 0
. 小数的分隔符的占位符
, 分组分隔符的占位符
; 分隔格式。
- 缺省负数前缀。
% 乘以 100 和作为百分比显示
? 乘以 1000 和作为千进制货币符显示;用货币符号代替;如果双写,用
国际货币符号代替。如果出现在一个模式中,用货币十进制分隔符代
替十进制分隔符。
X 前缀或后缀中使用的任何其它字符,用来引用前缀或后缀中的特殊字符。
例子:
DecimalFormat df1 = new DecimalFormat("0.0");
DecimalFormat df2 = new DecimalFormat("#.#");
DecimalFormat df3 = new DecimalFormat("000.000");
DecimalFormat df4 = new DecimalFormat("###.###");
System.out.println(df1.format(12.34));
System.out.println(df2.format(12.34));
System.out.println(df3.format(12.34));
System.out.println(df4.format(12.34));
结果:
12.3
12.3
012.340
12.34
2, 对于数值型单元的纯数值和日期型的处理
- case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- // 如果是date类型则 ,获取该cell的date值
- value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
- } else { // 纯数字
- value = String.valueOf(cell.getNumericCellValue());
- }
转: POI读取Excel浅谈
先看代码,挨句解释:
一般遍历使用两种方式,1:得到总的行数和每行的列数,然后循环。2:使用迭代
先看第一种:
- Java代码
- package com.golden.test;
- import java.io.File;
- import java.io.FileInputStream;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- /**
- *
- * @author 崔素强
- *
- */
- public class PoiReadXls2 {
- public static void main(String[] args) {
- File f = new File("c:\\a.xls");
- try {
- FileInputStream is = new FileInputStream(f);
- HSSFWorkbook wbs = new HSSFWorkbook(is);
- HSSFSheet childSheet = wbs.getSheetAt(0);
- // System.out.println(childSheet.getPhysicalNumberOfRows());
- System.out.println("有行数" + childSheet.getLastRowNum());
- for (int j = 0; j < childSheet.getLastRowNum(); j++) {
- HSSFRow row = childSheet.getRow(j);
- // System.out.println(row.getPhysicalNumberOfCells());
- // System.out.println("有列数" + row.getLastCellNum());
- if (null != row) {
- for (int k = 0; k < row.getLastCellNum(); k++) {
- HSSFCell cell = row.getCell(k);
- if (null != cell) {
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_NUMERIC: // 数字
- System.out.print(cell.getNumericCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_STRING: // 字符串
- System.out.print(cell.getStringCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
- System.out.println(cell.getBooleanCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_FORMULA: // 公式
- System.out.print(cell.getCellFormula() + " ");
- break;
- case HSSFCell.CELL_TYPE_BLANK: // 空值
- System.out.println(" ");
- break;
- case HSSFCell.CELL_TYPE_ERROR: // 故障
- System.out.println(" ");
- break;
- default:
- System.out.print("未知类型 ");
- break;
- }
- } else {
- System.out.print("- ");
- }
- }
- }
- System.out.println();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- package com.golden.test;
- import java.io.File;
- import java.io.FileInputStream;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- /**
- *
- * @author 崔素强
- *
- */
- public class PoiReadXls2 {
- public static void main(String[] args) {
- File f = new File("c:\\a.xls");
- try {
- FileInputStream is = new FileInputStream(f);
- HSSFWorkbook wbs = new HSSFWorkbook(is);
- HSSFSheet childSheet = wbs.getSheetAt(0);
- // System.out.println(childSheet.getPhysicalNumberOfRows());
- System.out.println("有行数" + childSheet.getLastRowNum());
- for (int j = 0; j < childSheet.getLastRowNum(); j++) {
- HSSFRow row = childSheet.getRow(j);
- // System.out.println(row.getPhysicalNumberOfCells());
- // System.out.println("有列数" + row.getLastCellNum());
- if (null != row) {
- for (int k = 0; k < row.getLastCellNum(); k++) {
- HSSFCell cell = row.getCell(k);
- if (null != cell) {
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_NUMERIC: // 数字
- System.out.print(cell.getNumericCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_STRING: // 字符串
- System.out.print(cell.getStringCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
- System.out.println(cell.getBooleanCellValue()
- + " ");
- break;
- case HSSFCell.CELL_TYPE_FORMULA: // 公式
- System.out.print(cell.getCellFormula() + " ");
- break;
- case HSSFCell.CELL_TYPE_BLANK: // 空值
- System.out.println(" ");
- break;
- case HSSFCell.CELL_TYPE_ERROR: // 故障
- System.out.println(" ");
- break;
- default:
- System.out.print("未知类型 ");
- break;
- }
- } else {
- System.out.print("- ");
- }
- }
- }
- System.out.println();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
System.out.println("有行数" + childSheet.getLastRowNum());
System.out.println(childSheet.getPhysicalNumberOfRows());
System.out.println("有列数" + row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
如果人都拷贝代码进行使用了,不知道有什么区别。太多的区别不知道,但是有一点我发现了,那就是如果中间各行或者隔列的话getPhysicalNumberOfRows和getPhysicalNumberOfCells就不能读取到所有的行和列了。
再者,一定要对单元格的格式进行判断switch (cell.getCellType()),不同的单元格格式使用不同的方法。最后加上为止类型,以防万一。
而且在数字类型里,又分为了纯数字和时间格式:
- case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- // 如果是date类型则 ,获取该cell的date值
- value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
- } else { // 纯数字
- value = String.valueOf(cell.getNumericCellValue());
- }
- case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- // 如果是date类型则 ,获取该cell的date值
- value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
- } else { // 纯数字
- value = String.valueOf(cell.getNumericCellValue());
- }
还有一种迭代的方法:
- package com.golden.test;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.Iterator;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- /**
- *
- * @author 崔素强
- *
- */
- public class PoiReadXls {
- @SuppressWarnings( { "unchecked", "deprecation" })
- public static void main(String[] args) {
- File f = new File("c:\\a.xls");
- try {
- InputStream input = new FileInputStream(f);
- POIFSFileSystem fs = new POIFSFileSystem(input);
- HSSFWorkbook wb = new HSSFWorkbook(fs);
- HSSFSheet sheet = wb.getSheetAt(0);
- Iterator rows = sheet.rowIterator();
- while (rows.hasNext()) {
- HSSFRow row = (HSSFRow) rows.next();
- // System.out.print("行:" + row.getRowNum() + " ");
- Iterator cells = row.cellIterator();
- while (cells.hasNext()) {
- HSSFCell cell = (HSSFCell) cells.next();
- // System.out.println("列:" + cell.getCellNum());
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_NUMERIC: // 数字
- System.out.print(cell.getNumericCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_STRING: // 字符串
- System.out.print(cell.getStringCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
- System.out.println(cell.getBooleanCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_FORMULA: // 公式
- System.out.print(cell.getCellFormula() + " ");
- break;
- case HSSFCell.CELL_TYPE_BLANK: // 空值
- System.out.println(" ");
- break;
- case HSSFCell.CELL_TYPE_ERROR: // 故障
- System.out.println(" ");
- break;
- default:
- System.out.print("未知类型 ");
- break;
- }
- }
- System.out.println();
- }
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- }
- }
- package com.golden.test;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.Iterator;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- /**
- *
- * @author 崔素强
- *
- */
- public class PoiReadXls {
- @SuppressWarnings( { "unchecked", "deprecation" })
- public static void main(String[] args) {
- File f = new File("c:\\a.xls");
- try {
- InputStream input = new FileInputStream(f);
- POIFSFileSystem fs = new POIFSFileSystem(input);
- HSSFWorkbook wb = new HSSFWorkbook(fs);
- HSSFSheet sheet = wb.getSheetAt(0);
- Iterator rows = sheet.rowIterator();
- while (rows.hasNext()) {
- HSSFRow row = (HSSFRow) rows.next();
- // System.out.print("行:" + row.getRowNum() + " ");
- Iterator cells = row.cellIterator();
- while (cells.hasNext()) {
- HSSFCell cell = (HSSFCell) cells.next();
- // System.out.println("列:" + cell.getCellNum());
- switch (cell.getCellType()) {
- case HSSFCell.CELL_TYPE_NUMERIC: // 数字
- System.out.print(cell.getNumericCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_STRING: // 字符串
- System.out.print(cell.getStringCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
- System.out.println(cell.getBooleanCellValue() + " ");
- break;
- case HSSFCell.CELL_TYPE_FORMULA: // 公式
- System.out.print(cell.getCellFormula() + " ");
- break;
- case HSSFCell.CELL_TYPE_BLANK: // 空值
- System.out.println(" ");
- break;
- case HSSFCell.CELL_TYPE_ERROR: // 故障
- System.out.println(" ");
- break;
- default:
- System.out.print("未知类型 ");
- break;
- }
- }
- System.out.println();
- }
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- }
- }
这种方法,如果数据的紧凑的,使用还是方便的,但是我发现,如果是空行或者是空列,他就会隔过去。具体的自己试试就知道了。
另外,也能看到这里得到Excel文件的方式是通过File,如果要引用到Struts2里,这是很简单的,因为Struts2上传时Action里定义的就是File或者File数组。