excel导入是经常使用到的功能，如果文件数据量大的话还是建议分割后导入，java常用的API是poi和jxl，我采用的是jxl,那么让我们来看下怎么用jxl来实现分割。
- 需要在pom中导入jxl的包
<!--excel-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
- 我们需要两个实体类 ,分别是excel信息和文件信息
public class FileInfoModel {
/**
* 文件在数据库中的Id
*/
private Long fileId;
/**
* 是否上传
*/
private boolean isUpload;
/**
* 存储循环过程中的索引值
*/
private int forIndex;
/**
* 文件全名 xxx.xls
*/
private String fileFullName;
/**
* 文件名 xxx
*/
private String fileName;
/**
* 文件后缀 .xls
*/
private String fileSuffix;
/**
* 文件保存路径 e:\\xx\xx
*/
private String filePath;
/**
* 文件的起始行号
*/
private int beginRow;
/**
* 文件的末尾行号
*/
private int endRow;
public FileInfoModel() {
super();
}
public FileInfoModel(String fileFullName) {
super();
this.fileFullName = fileFullName;
}
public FileInfoModel(String fileName, String fileFullName) {
super();
this.fileName = fileName;
this.fileFullName = fileFullName;
}
public FileInfoModel(String fileFullName, String fileName, String fileSuffix) {
super();
this.fileFullName = fileFullName;
this.fileName = fileName;
this.fileSuffix = fileSuffix;
}
public FileInfoModel(String fileFullName, String fileName, String fileSuffix, String filePath) {
super();
this.fileFullName = fileFullName;
this.fileName = fileName;
this.fileSuffix = fileSuffix;
this.filePath = filePath;
}
public Long getFileId() {
return fileId;
}
public void setFileId(Long fileId) {
this.fileId = fileId;
}
public boolean isUpload() {
return isUpload;
}
public void setUpload(boolean isUpload) {
this.isUpload = isUpload;
}
public int getForIndex() {
return forIndex;
}
public void setForIndex(int forIndex) {
this.forIndex = forIndex;
}
public String getFileFullName() {
return fileFullName;
}
public void setFileFullName(String fileFullName) {
this.fileFullName = fileFullName;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getFileSuffix() {
return fileSuffix;
}
public void setFileSuffix(String fileSuffix) {
this.fileSuffix = fileSuffix;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public int getBeginRow() {
return beginRow;
}
public void setBeginRow(int beginRow) {
this.beginRow = beginRow;
}
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
}
public class ExcelModel {
/*excel 文件路径*/
private String excelFilePath;
/*分割后的文件目录*/
private String excelChildFileDir;
/*excel 名称*/
private String excelName;
/*sheet 名称*/
private String sheetName = "Sheet1";
/*excel 总记录数*/
private int totalRows;
/*分割文件后每个文件的记录数*/
private int fileSize = 3000;
/*分割后的文件数*/
private int fileCount;
/*分割后的文件集合*/
private List<FileInfoModel> files;
/*计算总行数时是否去表头*/
private boolean isRemoveHeader = true;
/*是否去除重复数据*/
private boolean isCleareRepeat = false;
/*导入文件过程中是否出错*/
private boolean isError;
public boolean isError() {
return isError;
}
public void setError(boolean isError) {
this.isError = isError;
}
public String getExcelFilePath() {
return excelFilePath;
}
public void setExcelFilePath(String excelFilePath) {
this.excelFilePath = excelFilePath;
}
public boolean isCleareRepeat() {
return isCleareRepeat;
}
public void setCleareRepeat(boolean isCleareRepeat) {
this.isCleareRepeat = isCleareRepeat;
}
public String getExcelChildFileDir() {
return excelChildFileDir;
}
public void setExcelChildFileDir(String excelChildFileDir) {
this.excelChildFileDir = excelChildFileDir;
}
public boolean isRemoveHeader() {
return isRemoveHeader;
}
public void setRemoveHeader(boolean isRemoveHeader) {
this.isRemoveHeader = isRemoveHeader;
}
public int getFileCount() {
//根据总记录数及分割文件的行数计算文件数量
fileCount = (int) Math.ceil(this.totalRows / this.fileSize) + 1;
return fileCount;
}
public String getExcelName() {
return excelName;
}
public void setExcelName(String excelName) {
this.excelName = excelName;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public int getTotalRows() {
if (this.isRemoveHeader) {
return totalRows - 1;
} else {
return totalRows;
}
}
public void setTotalRows(int totalRows) {
this.totalRows = totalRows;
}
public int getFileSize() {
return fileSize;
}
public void setFileSize(int fileSize) {
this.fileSize = fileSize;
}
public List<FileInfoModel> getFiles() {
return files;
}
public void setFiles(List<FileInfoModel> files) {
this.files = files;
}
}
- 添加接口IExcelOperate
public interface IExcelOperate {
/**
* 加载excel文件
* @param fileName
*/
void load(String fileName) throws Exception;
/**
* 读取excel文件数据
*
* @throws Exception
*/
public <T> List<T> readExcel() throws Exception;
/**
* 分割excel
*
* @throws Exception
*/
public void splitExcel() throws Exception;
/**
* 多线程分割excel
*/
public void splitExcelThread() throws Exception;
/**
* 关闭文件
*/
void close();
}
- 让我们来实现接口的方法吧
public class ExcelUtil<T> implements IExcelOperate {
protected final Log log = LogFactory.getLog(getClass());
private ExcelModel excelModel;
private Class tClass;
private Cell[] titleCell;
private jxl.Workbook workBook;
private Sheet sheet;
public int getRows() {
if (this.sheet != null) {
return this.sheet.getRows();
}
return 0;
}
public ExcelUtil() {
super();
}
public ExcelUtil(ExcelModel excelModel, Class tClass) {
this.tClass = tClass;
this.excelModel = excelModel;
}
public ExcelModel getExcelModel() {
return excelModel;
}
public void setExcelModel(ExcelModel excelModel) {
this.excelModel = excelModel;
}
@Override
public void load(String fileName) throws Exception {
try {
File file = new File(fileName);
workBook = Workbook.getWorkbook(file);
this.excelModel.setFiles(new ArrayList<FileInfoModel>());
//默认读取文件的路径
String[] ary = file.getName().split("\\.");
String suffix = ary[1];
this.excelModel.getFiles().add(new FileInfoModel(file.getName(), fileName, "." + suffix, file.getParent()));
if (this.excelModel.isCleareRepeat()) {
this.clearRepeat(file);
workBook = Workbook.getWorkbook(file);
}
if (workBook == null) {
throw new Exception("读取excel文件出错!");
}
sheet = this.excelModel.getSheetName().trim() == "" ? workBook.getSheet(0) : workBook.getSheet(this.excelModel.getSheetName());
if (sheet == null) {
sheet = workBook.getSheet(0);
}
if (sheet == null) {
throw new Exception("读取sheet出错!");
}
this.excelModel.setTotalRows(sheet.getRows());
// 用于存储列标题
titleCell = new Cell[sheet.getColumns()];
// 将列标题存储存到一个一维数组中
for (int i = 0; i < titleCell.length; i++) {
titleCell[i] = sheet.getCell(i, 0);
}
} catch (IOException e) {
workBook.close();
}
}
@Override
public void splitExcel() throws Exception {
//根据文件数分割 excel，重置文件集合
this.excelModel.setFiles(new ArrayList<FileInfoModel>());
//生成Guid作为文件前缀
UUID uuid = UUID.randomUUID();
FileInfoModel fileInfoModel = new FileInfoModel();
for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
fileInfoModel = getFileInfoModelBySplit(i, uuid);
this.excelModel.getFiles().add(this.split(fileInfoModel));
}
}
@Override
public void splitExcelThread() throws Exception {
//根据文件数分割 excel，重置文件集合
this.excelModel.setFiles(new ArrayList<FileInfoModel>());
//生成Guid作为文件前缀
UUID uuid = UUID.randomUUID();
//根据文件数分割 excel，重置文件集合
this.excelModel.setFiles(new ArrayList<FileInfoModel>());
//工作线程
ExecutorService executorService = Executors.newFixedThreadPool(this.excelModel.getFileCount());
FileInfoModel fileInfoModel = new FileInfoModel();
for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
fileInfoModel = getFileInfoModelBySplit(i, uuid);
ExcelThread thread = new ExcelThread(fileInfoModel);
executorService.execute(thread);
this.excelModel.getFiles().add(thread.getReturnFile());
}
executorService.shutdown();
while (!executorService.isTerminated()) {
//检查所有线程都执行完成
}
}
/**
* 分割excel时获取文件信息
*
* @param i
* @return
*/
private FileInfoModel getFileInfoModelBySplit(int i, UUID uuid) {
/*结束行*/
int endRow = i * this.excelModel.getFileSize();
/*起始行*/
int beginRow = (endRow - this.excelModel.getFileSize()) + 1;
/*如果结束行超出总记录数，结束行就等于总记录数*/
if (endRow >= this.excelModel.getTotalRows()) {
endRow = this.excelModel.getTotalRows();
}
//获取文件路径
String filePath = MessageFormat.format("{0}/{1}_{2}.xls", this.excelModel.getExcelChildFileDir(), uuid, i);
FileInfoModel fileInfoModel = new FileInfoModel();
fileInfoModel.setFilePath(filePath);
fileInfoModel.setBeginRow(beginRow);
fileInfoModel.setEndRow(endRow);
fileInfoModel.setForIndex(i - 1);
return fileInfoModel;
}
/**
* 分割excel
*
* @param fileInfoModel
* @return
*/
private FileInfoModel split(FileInfoModel fileInfoModel) {
File file = new File(fileInfoModel.getFilePath());
try {
jxl.write.WritableWorkbook ww = Workbook.createWorkbook(file);
WritableSheet ws = ww.createSheet(this.excelModel.getSheetName(), 0);
//添加表头
for (int iColumn = 0; iColumn < this.titleCell.length; iColumn++) {
ws.addCell(new Label(iColumn, 0, this.titleCell[iColumn].getContents()));
}
//添加数据到excel中
int rowIndex = 1;
for (int iRow = fileInfoModel.getBeginRow(); iRow <= fileInfoModel.getEndRow(); iRow++, rowIndex++) {
Cell[] cells = this.sheet.getRow(iRow);
for (int iCell = 0; iCell < cells.length; iCell++) {
Cell cell = cells[iCell];
//excel 行的索引需要计算
ws.addCell(new Label(iCell, rowIndex, cell.getContents()));
}
}
ww.write();
ww.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String[] ary = file.getName().split("\\.");
String fileName = ary[0];
String suffix = ary[1];
fileInfoModel.setFileFullName(file.getName());
fileInfoModel.setFileName(fileName);
fileInfoModel.setFileSuffix("." + suffix);
return fileInfoModel;
}
class ExcelThread implements Runnable {// 任务接口
FileInfoModel returnFile;
public FileInfoModel getReturnFile() {
return returnFile;
}
ExcelThread(FileInfoModel fileInfoModel) {
this.returnFile = fileInfoModel;
}
public void run() {
long beginTime = System.currentTimeMillis();
this.returnFile = split(this.returnFile);
System.out.println(MessageFormat.format("分割文件{0},执行耗时{1}秒", this.returnFile.getForIndex(), (System.currentTimeMillis() - beginTime) / 1000f));
}
}
}