import
java.io.FileInputStream;
import
java.io.FileOutputStream;
import
java.io.InputStream;
import
java.io.OutputStream;
import
java.util.ArrayList;
import
java.util.List;
?
import
org.apache.poi.hssf.usermodel.HSSFCell;
import
org.apache.poi.hssf.usermodel.HSSFCellStyle;
import
org.apache.poi.hssf.usermodel.HSSFRow;
import
org.apache.poi.hssf.usermodel.HSSFSheet;
import
org.apache.poi.hssf.usermodel.HSSFWorkbook;
?
public
class
ExcelOperate {
?
??
public
static
void
main(String[] args) {
????
????
createExcel(getStudent());
?
????
????
List<Student> list = readExcel();
????
System.out.println(list.toString());
??
}
?
??
/**
???
* 初始化数据
???
*
???
* @return 数据
???
*/
??
private
static
List<Student> getStudent() {
????
List<Student> list =
new
ArrayList<Student>();
????
Student student1 =
new
Student(
"小明"
,
8
,
"二年级"
);
????
Student student2 =
new
Student(
"小光"
,
9
,
"三年级"
);
????
Student student3 =
new
Student(
"小花"
,
10
,
"四年级"
);
????
list.add(student1);
????
list.add(student2);
????
list.add(student3);
????
return
list;
??
}
?
??
/**
???
* 创建Excel
???
*
???
* @param list
???
*????? 数据
???
*/
??
private
static
void
createExcel(List<Student> list) {
????
????
HSSFWorkbook workbook =
new
HSSFWorkbook();
????
????
HSSFSheet sheet = workbook.createSheet(
"学生表一"
);
????
????
HSSFRow hssfRow = sheet.createRow(
0
);
????
????
HSSFCellStyle cellStyle = workbook.createCellStyle();
????
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
?
????
????
HSSFCell headCell = hssfRow.createCell(
0
);
????
headCell.setCellValue(
"姓名"
);
????
headCell.setCellStyle(cellStyle);
?
????
headCell = hssfRow.createCell(
1
);
????
headCell.setCellValue(
"年龄"
);
????
headCell.setCellStyle(cellStyle);
?
????
headCell = hssfRow.createCell(
2
);
????
headCell.setCellValue(
"年级"
);
????
headCell.setCellStyle(cellStyle);
?
????
????
for
(
int
i =
0
; i < list.size(); i++) {
??????
hssfRow = sheet.createRow((
int
) i +
1
);
??????
Student student = list.get(i);
?
??????
??????
HSSFCell cell = hssfRow.createCell(
0
);
??????
cell.setCellValue(student.getName());
??????
cell.setCellStyle(cellStyle);
?
??????
cell = hssfRow.createCell(
1
);
??????
cell.setCellValue(student.getAge());
??????
cell.setCellStyle(cellStyle);
?
??????
cell = hssfRow.createCell(
2
);
??????
cell.setCellValue(student.getGrade());
??????
cell.setCellStyle(cellStyle);
????
}
?
????
????
try
{
??????
OutputStream outputStream =
new
FileOutputStream(
"D:/students.xls"
);
??????
workbook.write(outputStream);
??????
outputStream.close();
????
}
catch
(Exception e) {
??????
e.printStackTrace();
????
}
??
}
?
??
/**
???
* 读取Excel
???
*
???
* @return 数据集合
???
*/
??
private
static
List<Student> readExcel() {
????
List<Student> list =
new
ArrayList<Student>();
????
HSSFWorkbook workbook =
null
;
?
????
try
{
??????
??????
InputStream inputStream =
new
FileInputStream(
"D:/students.xls"
);
??????
workbook =
new
HSSFWorkbook(inputStream);
??????
inputStream.close();
????
}
catch
(Exception e) {
??????
e.printStackTrace();
????
}
?
????
????
for
(
int
numSheet =
0
; numSheet < workbook.getNumberOfSheets(); numSheet++) {
??????
HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
??????
if
(hssfSheet ==
null
) {
????????
continue
;
??????
}
??????
??????
for
(
int
rowNum =
1
; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
????????
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
????????
if
(hssfRow ==
null
) {
??????????
continue
;
????????
}
?
????????
????????
Student student =
new
Student();
?
????????
HSSFCell cell = hssfRow.getCell(
0
);
????????
if
(cell ==
null
) {
??????????
continue
;
????????
}
????????
student.setName(cell.getStringCellValue());
?
????????
cell = hssfRow.getCell(
1
);
????????
if
(cell ==
null
) {
??????????
continue
;
????????
}
????????
student.setAge((
int
) cell.getNumericCellValue());
?
????????
cell = hssfRow.getCell(
2
);
????????
if
(cell ==
null
) {
??????????
continue
;
????????
}
????????
student.setGrade(cell.getStringCellValue());
?
????????
list.add(student);
??????
}
????
}
????
return
list;
??
}
}