poi Excel根据列名生成多文件工具类

工具类返回WorkBook对象集合list

传入文件全路径，要根据的列名称，表头开始行。（数据结束行默认为lastRow） 如果有需求请修改传值lastRow。

package com.poi.excel;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
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.xssf.usermodel.XSSFWorkbook;

/** 
* @date 2017年10月19日 上午9:32:36 
*  
*/
public class SplitExcelUtils {

	public static List<Workbook> SplitExcel(String filePath,String columnName,Integer headerIndex){
		String fileType = filePath.substring(filePath.lastIndexOf(".") + 1);
		List<Workbook> workbooks = new ArrayList<Workbook>();
		InputStream stream = null;
		Workbook wb = null;
		Sheet sheet1 = null;
		Integer colIndex = -1;
		try {
			stream = new FileInputStream(filePath);
			if ("xls".equals(fileType)) {
				wb = new HSSFWorkbook(stream);
			}else if("xlsx".equals(fileType)){
				wb = new XSSFWorkbook(stream);
			}else{
				System.out.println("您的文档格式不正确");
			}
			sheet1 = wb.getSheetAt(0);
			//查询指点列名位置
			Row row = sheet1.getRow(headerIndex);
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				String colName = getCellVallue(cell);
				if (columnName.equalsIgnoreCase(colName)) {
					colIndex = cell.getColumnIndex();
					break;
				}
			}
			
			List<String> colunms = new ArrayList<String>();
			//获取到指定列  读取指定列数组集合
			Integer LastRowNum = sheet1.getLastRowNum();
			if (colIndex!=-1) {
				for (int i = headerIndex+1; i <=LastRowNum; i++) {
					//循环数据获取指定列不同数据集合 去重复
					Cell celldata = sheet1.getRow(i).getCell(colIndex);
					String cellvalue = getCellVallue(celldata);
					if (!colunms.contains(cellvalue)) {
						colunms.add(cellvalue);
					}
				}
			}
			//封装Workbook对象
			if (colunms!=null && colunms.size()>0) {
				workbooks= getWorkbooks(colunms, sheet1, colIndex, filePath,headerIndex);
			}
			
		} catch (Exception e) {
			System.out.println("异常");
		}
		return workbooks;
	}
	
	
	public static String getCellVallue(Cell cell)
	{	
		if (cell == null)
			return "";
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
		{
			return String.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue());
		} else {
			return cell.getStringCellValue();
		}		
	}
	
	public static Workbook cloneWorkBook(String filePath)
	{	
		String fileType = filePath.substring(filePath.lastIndexOf(".") + 1);
		InputStream stream = null;
		Workbook wb = null;
		try {
			stream = new FileInputStream(filePath);
			if ("xls".equals(fileType)) {
				wb = new HSSFWorkbook(stream);
			}else if("xlsx".equals(fileType)){
				wb = new XSSFWorkbook(stream);
			}else{
				System.out.println("您的文档格式不正确");
			}
		} catch (Exception e) {
			System.out.println("您的文档格式不正确");
		}
		return 	wb;
	}
	
	public static List<Workbook> getWorkbooks(List<String> colunms,Sheet sheet1,Integer targetIndex,String filePath,Integer headerIndex){
		List<Workbook> Workbooks = new ArrayList<Workbook>();
		Integer lastRowNum = sheet1.getLastRowNum();
		//封装workbook
		for (String coluVale : colunms) {
			Workbook workNew = cloneWorkBook(filePath);
			Sheet dataSheet = workNew.getSheetAt(0);
			for (int i = headerIndex+1; i<=lastRowNum;i++) {
				Cell cell = dataSheet.getRow(i).getCell(targetIndex);
				String cellVallue = getCellVallue(cell);
				if (!coluVale.equalsIgnoreCase(cellVallue)) {
					Row row = dataSheet.getRow(i);
					dataSheet.removeRow(row);
					/*dataSheet.shiftRows(i, i+1, -1);*/
				}
			}
			delNullRow(dataSheet,headerIndex);
			Workbooks.add(workNew);
		}
		return Workbooks;
	}
	
	public static void delNullRow(Sheet sheet,Integer headerIndex){
		int i = sheet.getLastRowNum();
		Row tempRow;
			while(i > headerIndex){
			i--;
			tempRow = sheet.getRow(i);
			if(tempRow == null){
				sheet.shiftRows(i+1, sheet.getLastRowNum(), -1);
			}
		}
	}
	
	public static void main(String[] args) throws Exception {
		String path = "C:\\Users\\Administrator\\Desktop\\123\\ceshi.xls";
		String colName = "分组";
		String root = "C:\\Users\\Administrator\\Desktop\\123\\";
		List<Workbook> splitExcel = SplitExcel(path, colName,6);
		for (int i =0 ; i<splitExcel.size();i++) {
			String newPath = root+i+".xls";
			FileOutputStream out = new FileOutputStream(newPath);
			splitExcel.get(i).write(out);
		}
	}
}