Excel解析

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>

代码如下：

package ExcelProject.ExcelProject;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel解析解析
 * @author JAVA3
 */
public class ExcelAnalysis {
	
	public static void analysis(InputStream inputStream,String fileName) {
		boolean isE2007 = false;
		if(fileName.endsWith("xlsx")) {
			isE2007=true;
		}
		int rowIndex = 0;//行
    	int columnIndex = 0;//列
    	List<List<Object>> allDateList=new ArrayList<List<Object>>();
    	try {
    		InputStream input=inputStream;
   		 	Workbook wb  = null;  
	   		if(isE2007){ 
	              wb = new XSSFWorkbook(input);
	        }else{  
	              wb = new HSSFWorkbook(input);
	        }
	   		Sheet sheet = wb.getSheetAt(0);    //获得第一个表单  
	   		System.out.println("总行数:"+sheet.getLastRowNum());//总行数
	   	 	int totalRow=sheet.getLastRowNum();
		   	List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();  
		   	int count=sheet.getNumMergedRegions();  //所有合并单元格的数量
			for(int i = 0; i<count;i++) {  
				//获得合并单元格保存进list中  
				CellRangeAddress ca = sheet.getMergedRegion(i);  
				list.add(ca);  //获取所有合并单元格的信息
			}  	
			for(int rowNum=3;rowNum<=totalRow;rowNum++) {//行数
				List<Object> rowList=new ArrayList<Object>();
				for(int cloNum=0;cloNum<=7;cloNum++) {//列数
					Row row = sheet.getRow(rowNum);//得到第rowNum的数据
					rowList.add(getMergedRegionValue(sheet,rowNum,cloNum));
				}
				allDateList.add(rowList);
			}
			writeFile(allDateList);
    	}catch(Exception e) {
    		e.printStackTrace();
    	}
	}
	
	public static void writeFile(List<List<Object>> list) {
		File file=new File("C:\\ABCDE\\新加装烟感统计表.txt");
		try {
			if(!file.getParentFile().exists()) {
				file.getParentFile().mkdirs();
				file.createNewFile();
			}
			FileWriter fileWrite=new FileWriter(file,true);
			BufferedWriter buw=new BufferedWriter(fileWrite);
			for(List<Object> listObj:list) {
				String str="";
				for(Object obj:listObj) {
					if(obj instanceof String) {
						str+=(String)obj+"#";
					}
				}
				buw.write(str);
				buw.flush();
				buw.newLine();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		
		
		
		
	}
	
	
	public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        Row rowInfo=sheet.getRow(row);
        Cell fCell = rowInfo.getCell(column);
        if(fCell==null) {
        	return "null";
        }
        return getCellValue(fCell);
    }

	
	public static String getCellValue(Cell cell) {
        if (cell == null)
            return "null";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }else {
        	return "null";
        }
    }
	public static void main(String[] args) {
		try {
			File file=new File("C:\\美礼实\\新加装烟感统计表.xlsx");
			if(file.exists()) {
				System.out.println("文件存在");
			}else {
				System.out.println("文件不存在");
				return ;
			}
			InputStream input=new FileInputStream(file);
			String fileName="test.xlsx";
			analysis(input,fileName);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}