Java实现Excel读取到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;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
public class MyExcelUtil {
public static List<List<String>> readExcel(String path, int sheetIndex) {
List<List<String>> excelDataList = new ArrayList<List<String>>();
try {
InputStream inputStream = new FileInputStream(path);
File file;
Workbook workbook = WorkbookFactory.create(inputStream);
if (inputStream == null || workbook == null) {
return excelDataList;
}
Sheet sheet = workbook.getSheetAt(sheetIndex);
if (sheet == null) {
return excelDataList;
}
int rows = sheet.getPhysicalNumberOfRows();
int minCells = 0;
int maxCells = 0;
if (rows >= 1) {
minCells = sheet.getRow(0).getFirstCellNum();
maxCells = sheet.getRow(0).getLastCellNum();
}
for (int i = 0; i < rows; i++) {
Row row = sheet.getRow(i);
List<String> rowList = new ArrayList<String>();
for (int j = minCells; j < maxCells; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
rowList.add("");
} else {
rowList.add(cell.toString());
}
}
excelDataList.add(rowList);
}
inputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return excelDataList;
}
public static boolean writeExcel(List<String> title, List<List<String>> data, String sheetName, String filePath){
if (filePath == null || !filePath.contains(".")) {
return false;
}
String suffix = filePath.substring(filePath.lastIndexOf(".") + 1);
Workbook workbook = null;
switch (suffix) {
case "xls":
workbook = new HSSFWorkbook();
break;
case "xlsx":
workbook = new XSSFWorkbook();
break;
default:
return false;
}
Sheet sheet = workbook.createSheet(sheetName);
Row row = sheet.createRow(0);
int titleSize = title.size();
for (int i = 0; i < titleSize; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(title.get(i));
}
int dataSize = data.size();
for (int i = 0; i < dataSize; i++) {
Row row1 = sheet.createRow(i + 1);
List<String> rowData = data.get(i);
for (int i1 = 0; i1 < rowData.size(); i1++) {
row1.createCell(i1).setCellValue(rowData.get(i1));
}
}
File file = new File(filePath);
if (file.exists()) {
file.delete();
}
try {
file.createNewFile();
workbook.write(new FileOutputStream(file));
workbook.close();
return true;
} catch (IOException e) {
e.printStackTrace();
}
return false;
}
public static void main(String[] args) {
List<List<String>> lists = readExcel("E:\\testRead.xlsx", 1);
List<List<String>> dataList = new ArrayList<>();
for (int i = 1; i < lists.size(); i++) {
dataList.add(lists.get(i));
}
System.out.println(writeExcel(lists.get(0), dataList, "testSheet", "E:\\testWrite.xlsx"));
}
}