使用POI分割纯文本Excel文件 支持XLSX\XLS

为什么80%的码农都做不了架构师？>>>   

使用POI3.12分割纯文本Excel文件 支持XLSX\XLS文件类型，支持分割成定文件长度和定文件数量两种。

PS:生成XLS文件打开时显示文件可能已损坏 不知是何原因 求解....

package com.sgmder.java;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.util.LinkedList;
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;

/**
 * 分割Excel文件
 * 
 * @author sgmder
 * @version 0.1
 *
 */
public class SplitExcelOne2More {

	private String fileName;
	private Workbook workBook;
	private String title;

	public static void main(String[] arg) throws Exception {
		SplitExcelOne2More rw = new SplitExcelOne2More("C:\\test.xlsx");
		// System.out.println(rw.getExcelContent());
		// 文件长度固定
		// List<Workbook> list = rw.splitExcelByLength(1);
		// 文件个数固定
		List<Workbook> list = rw.splitExcelByNum(10);
		int x = 1;
		for (Workbook workBook : list) {
			FileOutputStream fOut = new FileOutputStream("C:\\test\\SplitFile"
					+ x++ + ".xlsx");
			workBook.write(fOut);
			fOut.flush();
			fOut.close();
		}
	}

	public SplitExcelOne2More(String fileName) {
		this.fileName = fileName;
	}

	/**
	 * 获取工作簿
	 * 
	 * @return
	 * @throws Exception
	 */
	private Workbook getWorkbook() {
		if (workBook == null) {
			try {
				if (fileName.substring(fileName.lastIndexOf('.') + 1)
						.equalsIgnoreCase("xlsx")) {
					workBook = new XSSFWorkbook(new FileInputStream(fileName));
				} else if (fileName.substring(fileName.lastIndexOf('.') + 1)
						.equalsIgnoreCase("xls")) {
					workBook = new HSSFWorkbook(new FileInputStream(fileName));
				} else {
					throw new IllegalArgumentException("非EXCEL文件类型！");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return workBook;
	}

	/**
	 * 文件长度固定 分割EXCEL文件
	 * 
	 * @param length
	 * @return
	 */
	public List<Workbook> splitExcelByLength(int length) {
		List<Workbook> bookList = new LinkedList<Workbook>();
		int rows = getWorkbook().getSheetAt(0).getLastRowNum();
		int count = rows / length;
		return NewWorkbook(length, bookList, rows, count);
	}

	/**
	 * 文件个数固定 分割EXCEL文件
	 * 
	 * @param count
	 * @return
	 */
	public List<Workbook> splitExcelByNum(int count) {
		List<Workbook> bookList = new LinkedList<Workbook>();
		int rows = getWorkbook().getSheetAt(0).getLastRowNum();
		count = rows % count != 0 ? count - 1 : count;
		int length = rows / count;
		return NewWorkbook(length, bookList, rows, count);
	}

	/**
	 * 获取WorkbookList
	 * 
	 * @param length
	 * @param bookList
	 * @param rows
	 * @param count
	 * @return
	 */
	private List<Workbook> NewWorkbook(int length, List<Workbook> bookList,
			int rows, int count) {
		for (int x = 1; x <= count; x++) {
			bookList.add(getNewWorkbook(getTitle()
					+ getExcelContent((x - 1) * length + 1, x * length)));
		}
		if (rows % length != 0) {
			bookList.add(getNewWorkbook(getTitle()
					+ getExcelContent(count * length + 1, rows)));
		}
		return bookList;
	}

	/**
	 * 获取Excel内容
	 * 
	 * @param startNum
	 * @return
	 */

	public String getExcelContent(int... startNum) {
		int start = startNum.length > 0 ? startNum[0] : 0;
		int end = startNum.length > 1 ? startNum[1] > getWorkbook().getSheetAt(
				0).getLastRowNum() ? getWorkbook().getSheetAt(0)
				.getLastRowNum() : startNum[1] : getWorkbook().getSheetAt(0)
				.getLastRowNum();
		StringBuffer sb = new StringBuffer();
		while (start <= end) {
			sb.append(getRowValue(start++));
		}
		return sb.toString();
	}

	/**
	 * 获取一行数据
	 * 
	 * @param rowNum
	 * @return
	 */
	public String getRowValue(int rowNum) {
		Sheet sheet = getWorkbook().getSheetAt(0);
		Row row = sheet.getRow(rowNum);
		int cellNum = row.getLastCellNum();

		StringBuffer result = new StringBuffer();

		for (int j = 0; j < cellNum; j++) {
			if (row.getCell((short) j) != null) {
				if (row.getCell((short) j).getCellType() == 1) {
					result.append(row.getCell((short) j).getStringCellValue());
				} else if (row.getCell((short) j).getCellType() == 0) {
					DecimalFormat df = new DecimalFormat("0");
					String strCell = df.format(row.getCell((short) j)
							.getNumericCellValue());
					result.append(strCell);
				}
			}
			result.append("\t");
		}
		result.setLength(result.length() - 1);
		result.append("\n");

		return result.toString();
	}

	/**
	 * 获取新的Workbook
	 * 
	 * @param ExcelContent
	 * @return
	 */
	public Workbook getNewWorkbook(String ExcelContent) {
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		String[] rows = ExcelContent.split("\n");
		for (int i = 0; i < rows.length; i++) {
			Row row = sheet.createRow((short) i);
			String[] cells = rows[i].split("\t");
			for (int j = 0; j < cells.length; j++) {
				Cell cell = row.createCell((short) j);
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue(cells[j]);
			}
		}
		return workbook;
	}

	/**
	 * 获取列头
	 * 
	 * @return
	 */
	public String getTitle() {
		return title == null ? getRowValue(0) : title;
	}
}

 

转载于:https://my.oschina.net/sgmder/blog/677020