1、createExcel这是一个生成excel文件的类具体代码如下:
package utils;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import ben.Student;
public class createExcel {
public void create(List<Student> list) {
//创建一个excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
//创建一个工作表
XSSFSheet sheet = workbook.createSheet("学生信息");
//添加表头行
XSSFRow row = sheet.createRow(0);
//设置单元格格式居中
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);;
//添加表头内容
XSSFCell headCell = row.createCell(0);
headCell.setCellValue("学号");
headCell.setCellStyle(cellStyle);
headCell = row.createCell(1);
headCell.setCellValue("姓名");
headCell.setCellStyle(cellStyle);
headCell = row.createCell(2);
headCell.setCellValue("年龄");
headCell.setCellStyle(cellStyle);
headCell = row.createCell(3);
headCell.setCellValue("年级");
headCell.setCellStyle(cellStyle);
headCell = row.createCell(4);
headCell.setCellValue("班主任");
headCell.setCellStyle(cellStyle);
//添加数据内容
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow((int)i+1);
Student student = list.get(i);
//创建单元格,并设置值
XSSFCell cell = row.createCell(0);
cell.setCellValue(student.getId());
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue(student.getName());
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(student.getAge());
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue(student.getClasss());
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue(student.getcName());
cell.setCellStyle(cellStyle);
}
//保存Excel文件
try {
OutputStream os = new FileOutputStream("D:/students.xls");
workbook.write(os);
os.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2、readExcel是读取excel文件的类具体代码如下:
package utils;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import ben.Student;
public class readExcel {
public List<Student> read() {
List<Student> list = new ArrayList<Student>();
XSSFWorkbook workbook = null;
try {
// 读取Excel文件
InputStream inputStream = new FileInputStream("D:/students.xls");
workbook = new XSSFWorkbook(inputStream);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
// 循环工作表
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = workbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环行
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 将单元格中的内容存入集合
Student student = new Student();
XSSFCell cell = xssfRow.getCell(0);
if (cell == null) {
continue;
}
student.setId((int) cell.getNumericCellValue());
cell = xssfRow.getCell(1);
if (cell == null) {
continue;
}
student.setName(cell.getStringCellValue());
cell = xssfRow.getCell(2);
if (cell == null) {
continue;
}
student.setAge((int) cell.getNumericCellValue());
cell = xssfRow.getCell(3);
if (cell == null) {
continue;
}
student.setClasss(cell.getStringCellValue());
cell = xssfRow.getCell(4);
if (cell == null) {
continue;
}
student.setcName(cell.getStringCellValue());;
list.add(student);
}
}
return list;
}
}