工具类返回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);
}
}
}