解析xls:
解析xlsx:
- package xls;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- 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;
- public class XlsMain {
- public static void main( String[] args) throws IOException {
- XlsMain xlsMain = new XlsMain();
- xlsMain.readXls();
- }
- private void readXls() throws IOException{
- InputStream is = new FileInputStream( "D:\\excel\\xls_test2.xls");
- HSSFWorkbook hssfWorkbook = new HSSFWorkbook( is);
- // 循环工作表Sheet
- for(int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++){
- HSSFSheet hssfSheet = hssfWorkbook.getSheetAt( numSheet);
- if(hssfSheet == null){
- continue;
- }
- // 循环行Row
- for(int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++){
- HSSFRow hssfRow = hssfSheet.getRow( rowNum);
- if(hssfRow == null){
- continue;
- }
- // 循环列Cell
- 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();
- }
- }
- }
- @SuppressWarnings("static-access")
- private 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{
- return String.valueOf( hssfCell.getStringCellValue());
- }
- }
- }
解析xlsx:
- package xlsx;
- import java.io.IOException;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- public class XlsxMain {
- public static void main( String[] args) throws IOException {
- XlsxMain xlsxMain = new XlsxMain();
- xlsxMain.readXlsx();
- }
- private void readXlsx() throws IOException{
- String fileName = "D:\\excel\\xlsx_test.xlsx";
- XSSFWorkbook xssfWorkbook = new XSSFWorkbook( fileName);
- // 循环工作表Sheet
- for(int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++){
- XSSFSheet xssfSheet = xssfWorkbook.getSheetAt( numSheet);
- if(xssfSheet == null){
- continue;
- }
- // 循环行Row
- for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++ ){
- XSSFRow xssfRow = xssfSheet.getRow( rowNum);
- if(xssfRow == null){
- continue;
- }
- // 循环列Cell
- for(int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++){
- XSSFCell xssfCell = xssfRow.getCell( cellNum);
- if(xssfCell == null){
- continue;
- }
- System.out.print(" "+getValue(xssfCell));
- }
- System.out.println();
- }
- }
- }
- @SuppressWarnings("static-access")
- private String getValue(XSSFCell xssfCell){
- if(xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN){
- return String.valueOf( xssfCell.getBooleanCellValue());
- }else if(xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC){
- return String.valueOf( xssfCell.getNumericCellValue());
- }else{
- return String.valueOf( xssfCell.getStringCellValue());
- }
- }
- }
由于豆丁网不能复制,只能一行代码一行代码的敲。
- package com.s2sh.test;
- import java.io.BufferedReader;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.InputStreamReader;
- import java.util.Date;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- 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;
- public class ExcelReader {
- //创建文件输入流
- private BufferedReader reader = null;
- //文件类型
- private String fileType;
- //文件二进制输入流
- private InputStream is = null;
- //当前sheet
- private int currSheet;
- //当前位置
- private int currPosition;
- //sheet数量
- private int numOfSheets;
- //HSSFWorkbook
- HSSFWorkbook workbook = null;
- //设置cell之间以空格分隔
- private static String EXCEL_LINE_DELIMITER = "";
- //设置最大列数
- private static int MAX_EXCEL_COLUMNS = 64;
- //构造函数创建一个ExcelReader
- public ExcelReader(String inputfile) throws IOException,Exception {
- //判断参数是否为空或者没有意义
- if(null == inputfile && "".equals(inputfile.trim())) {
- throw new IOException("no input file specified");
- }
- //取得文件名后缀赋值给fileType
- this.fileType = inputfile.substring(inputfile.lastIndexOf(".")+1);
- //设置开始行为0
- currPosition = 0;
- //设置当前位置为0
- currSheet = 0;
- //创建输入流
- is = new FileInputStream(inputfile);
- //判断文件格式
- if(fileType.equalsIgnoreCase("txt")) {
- //如果是txt则直接创建BufferReader读取
- reader = new BufferedReader(new InputStreamReader(is));
- }
- else if(fileType.equalsIgnoreCase("xls")) {
- //如果是Excel文件则创建HSSFWorkbook读取
- workbook = new HSSFWorkbook(is);
- //设置sheet数
- numOfSheets = workbook.getNumberOfSheets();
- }else {
- throw new Exception("File Type not Supported");
- }
- }
- //函数readLine读取文本的一行
- public String readLine() throws IOException {
- //如果是txt则通过reader读取
- if(fileType.equalsIgnoreCase("txt")) {
- String str = reader.readLine();
- //空行则略去,直接读取下一行
- while(str.trim().equals("")) {
- str = reader.readLine();
- }
- return str;
- }
- //如果是xls文件则通过POI提供给的API读取文件
- else if(fileType.equalsIgnoreCase("xls")) {
- //根据currSheet值获得当前的sheet
- HSSFSheet sheet = workbook.getSheetAt(currSheet);
- //判断当前行是否到当前sheet的结尾
- if(currPosition > sheet.getLastRowNum()) {
- //当前行位置清零
- currPosition = 0;
- //判断是否还有Sheet
- while(currSheet != numOfSheets -1){
- //得到下一个sheet
- sheet = workbook.getSheetAt(currSheet+1);
- //判断当前行是否到当前sheet的结尾
- if(currPosition == sheet.getLastRowNum()) {
- currSheet++;
- continue;
- }else {
- //获取当前行数
- int row = currPosition;
- currPosition++;
- //读取当前行数据
- return getLine(sheet,row);
- }
- }
- return null;
- }
- //获取当前行数
- int row = currPosition;
- currPosition++;
- //读取当前行数据
- return getLine(sheet,row);
- }
- return null;
- }
- //函数getLine返回sheet的一行数据
- private String getLine (HSSFSheet sheet,int row) {
- //根据行数取得sheet的一行
- HSSFRow rowLine = sheet.getRow(row);
- //创建字符串缓冲区
- StringBuffer buffer = new StringBuffer();
- //获取挡前行的列数
- int filledColumns = rowLine.getLastCellNum();
- HSSFCell cell = null;
- //循环遍历所有列
- for(int i=0;i<filledColumns;i++) {
- //取得当前cell
- cell = rowLine.getCell(i);
- String cellValue = null;
- if(null != cell) {
- //判断当前cell的type
- switch(cell.getCellType()) {
- //如果当前cell的type为NUMERIC
- case HSSFCell.CELL_TYPE_NUMERIC : {
- //判断当前cell是否为Date
- if(HSSFDateUtil.isCellDateFormatted(cell)){
- //如果是Date类型,取得该Cell的Date值
- Date date = cell.getDateCellValue();
- //把Date转换成本地格式的字符串
- cellValue = new java.text.SimpleDateFormat( " yyyy-MM-dd HH:mm " ).format(cell.getDateCellValue());
- }
- //如果是纯数字
- else {
- //取得当前cell的数值
- Integer num = new Integer((int)cell.getNumericCellValue());//默认返回时double类型
- cellValue = String.valueOf(num);
- }
- break;
- }
- //如果当前cell的type为String
- case HSSFCell.CELL_TYPE_STRING :
- //取得当前shell的字符串
- cellValue = cell.getStringCellValue().replaceAll("/'", "/"");
- break;
- //默认的cell值
- default:
- cellValue = "";
- }
- }else {
- cellValue = "";
- }
- //在每个字段之间插入分隔符
- buffer.append(cellValue).append(EXCEL_LINE_DELIMITER);
- }
- //以字符串返回该行的数据
- return buffer.toString();
- }
- //close函数执行流的关闭操作
- public void close() {
- //如果id不为空,则关闭InputStream文件输入流
- if(is != null) {
- try {
- is.close();
- }catch(IOException e) {
- is = null;
- }
- }
- //如果reader不为空,则关闭BufferedReader文件输入流
- if(reader != null) {
- try {
- reader.close();
- }catch(IOException e) {
- reader = null;
- }
- }
- }
测试类:
- public class ExcelReaderTest {
- /**
- * @param args
- */
- public static void main(String[] args) {
- try {
- ExcelReader er = new ExcelReader("D://SAPI//task//CBS00087558//records.xls");
- String line = er.readLine();
- while(line != null) {
- System.out.println(line);
- line = er.readLine();
- }
- er.close();
- }catch(Exception e) {
- e.printStackTrace();
- }
- }
- }