用java写一个降大文件excel分成若干份文件的工具类
时间: 2023-05-13 22:07:21 浏览: 80
好的,以下是一个用 Java 写的将大文件 Excel 分成若干份文件的工具类:
```java
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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;
public class ExcelSplitter {
private static final int DEFAULT_ROW_LIMIT = 10000;
public static void split(File inputFile, int rowLimit) throws IOException {
FileInputStream fis = new FileInputStream(inputFile);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getLastRowNum() + 1;
int sheetCount = (int) Math.ceil((double) rowCount / rowLimit);
List<Sheet> sheets = new ArrayList<>(sheetCount);
for (int i = 0; i < sheetCount; i++) {
Sheet newSheet = workbook.createSheet("Sheet" + (i + 1));
sheets.add(newSheet);
}
int currentSheetIndex = 0;
int currentRowIndex = 0;
for (int i = 0; i < rowCount; i++) {
Row row = sheet.getRow(i);
if (currentRowIndex == rowLimit) {
currentSheetIndex++;
currentRowIndex = 0;
}
Row newRow = sheets.get(currentSheetIndex).createRow(currentRowIndex++);
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
Cell newCell = newRow.createCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case BOOLEAN:
newCell.setCellValue(cell.getBooleanCellValue());
break;
case NUMERIC:
newCell.setCellValue(cell.getNumericCellValue());
break;
case STRING:
newCell.setCellValue(cell.getStringCellValue());
break;
case FORMULA:
newCell.setCellFormula(cell.getCellFormula());
break;
case BLANK:
newCell.setBlank();
break;
case ERROR:
newCell.setCellErrorValue(cell.getErrorCellValue());
break;
default:
break;
}
}
}
}
fis.close();
File outputDir = new File(inputFile.getParentFile(), "output");
if (!outputDir.exists()) {
outputDir.mkdir();
}
for (int i = 0; i < sheets.size(); i++) {
Sheet sheetToWrite = sheets.get(i);
File outputFile = new File(outputDir, "output" + (i + 1) + ".xlsx");
FileOutputStream fos = new FileOutputStream(outputFile);
workbook.write(fos);
fos.close();
}
workbook.close();
}
public static void split(File inputFile) throws IOException {
split(inputFile, DEFAULT_ROW_LIMIT);
}
}
```
这个工具类可以将一个 Excel 文件分成若干个文件,每个文件最多包含指定的行数。你可以使用以下代码来调用这个工具类:
```java
File inputFile = new File("input.xlsx");
ExcelSplitter.split(inputFile, 5000);
```
这个例子将会把 `input.xlsx` 文件分成若干个文件,每个文件最多包含 5000 行。分割后的文件将会保存在 `input.xlsx` 文件所在目录下的 `output` 文件夹中。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)