excel学习笔记之一
说明该读取支持97到2003
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import org.apache.poi.ss.usermodel.CellStyle;
//excle 读取
public class Demo1 {
public static void main(String[] args) throws Exception {
InputStream is = new FileInputStream("E:\\data.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);//创建一个新的工作簿
HSSFSheet hssfSheet = wb.getSheetAt(0);
if (hssfSheet == null)
{
return;
}
//遍历行row
for(int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++)
{
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if(hssfRow == null)
{
continue;
}
//遍历列
for(int cellNum = 0;cellNum <= hssfRow.getLastCellNum() ; cellNum++)
{
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if(hssfCell == null)
{
continue;
}
System.out.print(""+getValue(hssfCell));
}
System.out.println();
}
}
/*1）CELL_TYPE_BLANK ：空值
2）CELL_TYPE_BOOLEAN ：布尔型
3）CELL_TYPE_ERROR ： 错误
4）CELL_TYPE_FORMULA ：公式型
5）CELL_TYPE_STRING：字符串型
6）CELL_TYPE_NUMERIC：数值型
*
* */
//类型判断方法
private static String getValue(HSSFCell hssfCell)
{
if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) // 布尔类型
{
return String.valueOf(hssfCell.getBooleanCellValue());
}
else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) // 数字类型
{
return String.valueOf(hssfCell.getNumericCellValue());
}
else if(hssfCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
{
return String.valueOf(hssfCell.getDateCellValue()); //公式型
}
else
{
return String.valueOf(hssfCell.getStringCellValue()); //字符串
}
}
//poi读取excel文档判断日期格式
public static String readCellValues(HSSFCell cell) throws Exception {
// 用于返回结果
String result = new String();
try {
// 如果单元格为空，返回null
if (cell == null) {
result = "null";
}
else {
// 判断单元格类型
switch (cell.getCellType()) {
// 数字类型
case HSSFCell.CELL_TYPE_NUMERIC:
// 处理日期格式、时间格式
if (HSSFDateUtil.isCellDateFormatted(cell))
{
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:// String类型
result = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
}
}
catch(Exception e) {
e.printStackTrace();
}
return result;
}
}