package test.util;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcel {
public List<String []> readExcel(String filePath)
{
List<String []> list =new ArrayList<String []>();
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat df = new DecimalFormat("0");
try {
//同时支持Excel 2003、2007
File excelFile = new File(filePath); //创建文件对象
FileInputStream is = new FileInputStream(excelFile); //文件流
Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量
//遍历每个Sheet
for (int s = 0; s < sheetCount; s++) {
Sheet sheet = workbook.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
//遍历每一行
for (int r = 0; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
//遍历每一列
String[] datas = new String[cellCount];
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
int cellType = cell.getCellType();
String cellValue = null;
switch(cellType) {
case Cell.CELL_TYPE_STRING: //文本
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: //数字、日期
if(DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
}
else {
cellValue=df.format(cell.getNumericCellValue());
//cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: //错误
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = "错误";
break;
default:
cellValue = "错误";
}
datas[c] = cellValue;
System.out.print(cellValue + " ");
}
list.add(datas);
System.out.println();
}
}
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}
public static void main(String[] args) {
String filePath ="f:/templete.xls";
ReadExcel readExcel =new ReadExcel();
List<String []> list =new ArrayList<String []>();
list=readExcel.readExcel(filePath);
System.out.println("总记录"+list.size());
}
} 点击打开链接
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcel {
public List<String []> readExcel(String filePath)
{
List<String []> list =new ArrayList<String []>();
SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
DecimalFormat df = new DecimalFormat("0");
try {
//同时支持Excel 2003、2007
File excelFile = new File(filePath); //创建文件对象
FileInputStream is = new FileInputStream(excelFile); //文件流
Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量
//遍历每个Sheet
for (int s = 0; s < sheetCount; s++) {
Sheet sheet = workbook.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
//遍历每一行
for (int r = 0; r < rowCount; r++) {
Row row = sheet.getRow(r);
int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
//遍历每一列
String[] datas = new String[cellCount];
for (int c = 0; c < cellCount; c++) {
Cell cell = row.getCell(c);
int cellType = cell.getCellType();
String cellValue = null;
switch(cellType) {
case Cell.CELL_TYPE_STRING: //文本
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: //数字、日期
if(DateUtil.isCellDateFormatted(cell)) {
cellValue = fmt.format(cell.getDateCellValue()); //日期型
}
else {
cellValue=df.format(cell.getNumericCellValue());
//cellValue = String.valueOf(cell.getNumericCellValue()); //数字
}
break;
case Cell.CELL_TYPE_BOOLEAN: //布尔型
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: //空白
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_ERROR: //错误
cellValue = "错误";
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = "错误";
break;
default:
cellValue = "错误";
}
datas[c] = cellValue;
System.out.print(cellValue + " ");
}
list.add(datas);
System.out.println();
}
}
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}
public static void main(String[] args) {
String filePath ="f:/templete.xls";
ReadExcel readExcel =new ReadExcel();
List<String []> list =new ArrayList<String []>();
list=readExcel.readExcel(filePath);
System.out.println("总记录"+list.size());
}
} 点击打开链接