使用POI分割纯文本Excel文件 并保留文件的首行格式
POI 3.12 支持XLS和XLSX
PS:怎么才能复制一个workbook对象呢...求解
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.2
*
*/
public class SplitExcelOne2MoreSaveFormat {
private String fileName;
private Workbook workBook;
private int i = 1;
private String preName = "split";
private String path = "C:\\test";
private String extend;
public static void main(String[] arg) throws Exception {
SplitExcelOne2MoreSaveFormat rw = new SplitExcelOne2MoreSaveFormat("C:\\test.xls");
//path + preName+ i++ + entend
rw.setPath("C:\\test\\");
rw.setPreName("splitFile_");
// 文件长度固定
rw.splitExcelByLength(1);
// 文件个数固定
//rw.splitExcelByNum(1);
}
/**
* 写文件
* @throws Exception
*/
private void writeFile() throws Exception{
FileOutputStream fOut = new FileOutputStream(path+
preName +i++ +extend);
workBook.write(fOut);
fOut.flush();
fOut.close();
}
public SplitExcelOne2MoreSaveFormat(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));
extend = ".xlsx";
} else if (fileName.substring(fileName.lastIndexOf('.') + 1)
.equalsIgnoreCase("xls")) {
workBook = new HSSFWorkbook(new FileInputStream(fileName));
extend = ".xls";
} else {
throw new IllegalArgumentException("非EXCEL文件类型!");
}
} catch (Exception e) {
e.printStackTrace();
}
}
return workBook;
}
/**
* 文件长度固定 分割EXCEL文件
*
* @param length
* @return
*/
public List<Workbook> splitExcelByLength(int length) throws Exception{
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) throws Exception{
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) throws Exception{
List<String> list = new LinkedList<String>();
for (int x = 1; x <= count; x++) {
list.add(
getExcelContent((x - 1) * length + 1, x * length));
}
if (rows % length != 0) {
list.add(
getExcelContent(count * length + 1, rows));
}
for(String str:list)
getNewWorkbook(str);
return bookList;
}
/**
* 获取Excel内容
*
* @param startNum
* @return
*/
private 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
*/
private 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 workbook
*
* @param ExcelContent
* @return
*/
private void getNewWorkbook(String ExcelContent) throws Exception{
clear();
Workbook workbook = getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
String[] rows = ExcelContent.split("\n");
for (int i = 0; i < rows.length; i++) {
Row row = sheet.createRow((short) (i+1));
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]);
}
}
writeFile();
}
/**
* 清除除了title之外的行
*/
private void clear(){
Sheet sheet = getWorkbook().getSheetAt(0);
while( sheet.getLastRowNum() > 0)
sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
}
public void setPath(String path){
this.path = path;
}
public void setPreName(String preName){
this.preName = preName;
}
}