推荐使用easyexcel来操作excel,方便且内存优化较好。但是由于本次操作是没有类的,完全使用excel的二位属性,所以使用POI。
工具方法
1、建立Excel
public void createExcel(String filePath) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
FileOutputStream fileOutputStream = new FileOutputStream(filePath);
wb.write(fileOutputStream);
fileOutputStream.close();
}
2、得到一个已有的Excel工作簿的对象
private XSSFWorkbook returnWorkBookGivenFileHandle(String filePath) {
XSSFWorkbook wb = null;
FileInputStream fis = null;
File f = new File(filePath);
try {
if (f != null) {
fis = new FileInputStream(f);
wb = new XSSFWorkbook(fis);
}
} catch (Exception e) {
return null;
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return wb;
}
3、保存Excel
private void saveExcel(XSSFWorkbook wb, String filePath) {
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(filePath);
wb.write(fileOutputStream);
fileOutputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
使用方法
1、通过ArrayList横排注入Excel
public void setHead(String classMajor, String type, ArrayList<String> nameList) throws IOException {
String filePath = excelPath + classMajor + type + ".xlsx";
createExcel(filePath);
XSSFWorkbook wb = returnWorkBookGivenFileHandle(filePath);
XSSFSheet sheet = wb.createSheet("Sheet1");
XSSFRow row = sheet.createRow(0);
for (int i = 1; i <= nameList.size(); ++i) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(nameList.get(i - 1));
}
saveExcel(wb, filePath);
}
2、通过ArrayList纵列注入Excel
public void setTest(String classMajor, String type, ArrayList<String> testList) {
String filePath = excelPath + classMajor + type + ".xlsx";
XSSFWorkbook wb = returnWorkBookGivenFileHandle(filePath);
XSSFSheet sheet = wb.getSheet("Sheet1");
for (int i = 0; i < testList.size(); ++i) {
XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
XSSFCell cell = row.createCell(0);
cell.setCellValue(testList.get(i));
}
saveExcel(wb, filePath);
}
3、查询第一纵列注入ArrayList
public void inputScore(String classMajor, String name, String type, ArrayList<Integer> scoreList) {
String filePath = excelPath + classMajor + type + ".xlsx";
XSSFWorkbook wb = returnWorkBookGivenFileHandle(filePath);
XSSFSheet sheet = wb.getSheet("Sheet1");
for (int i = 1; i <= sheet.getLastRowNum(); ++i) {
XSSFRow row = sheet.getRow(i);
XSSFCell cell = row.getCell(0);
if (cell.getStringCellValue().equals(name)) {
for (int j = 1; j <= scoreList.size(); ++j) {
cell = row.createCell(j);
cell.setCellValue(scoreList.get(j - 1));
}
}
}
saveExcel(wb, filePath);
}
4、对Excel纵列统计
public ArrayList<Double> Calculate(String classMajor, String type) {
String filePath = excelPath + classMajor + type + ".xlsx";
XSSFWorkbook wb = returnWorkBookGivenFileHandle(filePath);
XSSFSheet sheet = wb.getSheet("Sheet1");
ArrayList<Double> scoreList = new ArrayList<Double>();
for (int i = 1; i <= sheet.getRow(0).getPhysicalNumberOfCells(); ++i) { // 列
double sum = 0;
double max = 0;
double min = 100;
for (int j = 1; j <= sheet.getLastRowNum(); ++j) { // 遍历行 getLastRowNum()->最后一行行标
XSSFRow row = sheet.getRow(j);
XSSFCell cell = row.getCell(i);
double score = cell.getNumericCellValue();
sum += score;
if (max < score)
max = score;
if (min > score)
min = score;
}
sum -= (max + min);
double average = sum / (sheet.getLastRowNum() - 2);
scoreList.add(average);
}
return scoreList;
}
九九乘法表注入
public void test() {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheets = wb.createSheet("九九乘法表");
for (int i = 1; i <= 9; i++) {
XSSFRow row = sheets.createRow(i - 1); // 行
for (int j = 1; j <= 9; j++) {
XSSFCell cell = row.createCell(j - 1); // 列
cell.setCellValue(i + "*" + j + "=" + i * j);
}
}
try {
FileOutputStream fileOutputStream = new FileOutputStream("");
try {
wb.write(fileOutputStream);
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}