Excel表格生成：Java中将List集合数据转换成Excel表格，包含表头
Ⅰ：先上相关依赖，包含解析Excel和生成Excel，缺一不可
<!-- Excel Xls格式解析 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- Excel Xlsx格式解析 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- 解析Excel相关依赖 -->
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
<classifier>jdk15</classifier>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
Ⅱ：关键代码代码
private final String outPutSheetName="学生预约系统教师信息";//Excel文件的标题
public List<Teacher> getFromExcel( String filePath){
List<Teacher> teachers=null;
try{
Workbook wb =null;
Sheet sheet = null;
Row row = null;
teachers =new ArrayList<Teacher>();
wb = readExcel(filePath);//指定Excel对象
if(wb != null){
//获取第一个表
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取最大列数
String Email,Major,Name,Password;
for (int i = 2; i<rownum; i++) {//这里从第三行开始读取数据
row = sheet.getRow(i);//获得制定行数
Cell cell0=row.getCell(0);//分别从每个格子中获取内容
Cell cell1=row.getCell(1);
Cell cell2=row.getCell(2);
Cell cell3=row.getCell(3);
cell0.setCellType(CellType.STRING);
cell1.setCellType(CellType.STRING);
cell2.setCellType(CellType.STRING);
cell3.setCellType(CellType.STRING);
//设置Excel内容为文本类型 ,不加执行不了
Email=cell0.toString();
Major=cell1.toString();
Name=cell2.toString();
Password=cell3.toString();//toString获得内容
teachers.add(new Teacher(Email, Password, Name, Major));
}
}
}catch (Exception e) {
System.out.print("文件格式不正确！");
e.printStackTrace();
}
return teachers;
}
//读取excel
public Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));//获取文件格式
InputStream is = null;
try {
is = new FileInputStream(filePath);
if(".xls".equals(extString)){//判断Excel文件格式
return new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return new XSSFWorkbook(is);
}else{
return null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**导出xlsx文件
* https://www.2cto.com/kf/201605/510933.html
* https://www.cnblogs.com/f-anything/p/5996380.html
*/
public void outPutToAExcel(List<Teacher> teachers,String filePath) throws IOException {
XSSFWorkbook wb=new XSSFWorkbook();
XSSFCellStyle style=createCellStyle(wb);
XSSFSheet sheet=wb.createSheet(outPutSheetName);
//这里的是表格内像素的转换公式,例如第列宽24像素,第二列宽20像素等等..
sheet.setColumnWidth(0, 256*24+184);//sheet.setColumnWidth(0, 256*width+184);http://blog.csdn.net/duqian42707/article/details/51491312
sheet.setColumnWidth(1, 256*20+184);
sheet.setColumnWidth(2, 256*10+184);
sheet.setColumnWidth(3, 256*10+184);
//合并第一行
CellRangeAddress region1 = new CellRangeAddress(0, 0, (short) 0, (short) 3); //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(region1);//合并单元格
XSSFCell cell_=sheet.createRow(0).createCell(0);
cell_.setCellStyle(style);
cell_.setCellValue(" 学生预约系统教师账号信息");//手动居中~
//输出表头,即第一行
XSSFRow row=null;
XSSFCell cell0_,cell1_,cell2_,cell3_;
XSSFRow row0=sheet.createRow((int)1);
XSSFCell cell0=row0.createCell(0);
XSSFCell cell1=row0.createCell(1);
XSSFCell cell2=row0.createCell(2);
XSSFCell cell3=row0.createCell(3);
//表格样式
cell0.setCellStyle(style);
cell1.setCellStyle(style);
cell2.setCellStyle(style);
cell3.setCellStyle(style);
cell0.setCellValue("邮箱账号");
cell1.setCellValue("学科专业");
cell2.setCellValue("教师姓名");
cell3.setCellValue("账号密码");
for(int i=0;i<teachers.size();i++){
row=sheet.createRow(i+2);
cell0_=row.createCell(0);
cell1_=row.createCell(1);
cell2_=row.createCell(2);
cell3_=row.createCell(3);
cell0_.setCellStyle(style);
cell1_.setCellStyle(style);
cell2_.setCellStyle(style);
cell3_.setCellStyle(style);
cell0_.setCellValue(teachers.get(i).getEmail());
cell1_.setCellValue(teachers.get(i).getMajor());
cell2_.setCellValue(teachers.get(i).getName());
cell3_.setCellValue(teachers.get(i).getPassword());
}
try{
FileOutputStream outputStream=new FileOutputStream(filePath);
wb.write(outputStream);
wb.close();
outputStream.flush();
outputStream.close();
} catch (FileNotFoundException e){
System.err.println("获取不到位置");
e.printStackTrace();
} catch (IOException e){
e.printStackTrace();
}
}
/**
* 设置导出Excel表格样式
* @param workbook 表格
* @return 样式
*/
private XSSFCellStyle createCellStyle(XSSFWorkbook workbook){
XSSFFont font=workbook.createFont();
//在对应的workbook中新建字体
font.setFontName("微软雅黑");
//字体微软雅黑
font.setFontHeightInPoints((short)11);
//设置字体大小
XSSFCellStyle style=workbook.createCellStyle();
//新建Cell字体
style.setFont(font);
return style;
}
Ⅲ：用于测试的Teacher实体类
@Data
public class Teacher {
private String Email;
private String Name;
private String Password;
private String Major;
public Teacher(){}
public Teacher(String Email,String Password,String Name,String Major) {
this.Email=Email;
this.Password=Password;
this.Name=Name;
this.Major=Major;
}
}
Ⅳ：Test测试方法
@Test
public void outPutToAExcel() throws IOException {
//导出Excel测试
List<Teacher> teachers=new ArrayList<Teacher>();
Teacher a=new Teacher("[email protected]", "123456", "Eason", "Computer Science");
Teacher a1=new Teacher("[email protected]", "123456", "czwon", "Computer Science");
teachers.add(a);
teachers.add(a1);
excelOperator.outPutToAExcel(teachers,"C:\\Users\\Administrator\\Desktop\\teacher.xlsx");
//导入Excel测试
// TeacherTableEnhanced one=new TeacherTableEnhanced("C:/Users/XPS/Desktop/教师表格.xlsx");//这里需要输入导入的地址
// List<Teacher> teachers=one.getFromExcel();//从Excel文件中获取List
// for(Teacher a:teachers){
// System.out.println(a);
// }
}
Ⅵ：生成的Excel表格
Ⅶ：遗留问题：此处为硬编码，类中的属性全部固定，后期会根据类动态生成响应列的表格，敬请关注。
参考文章点我*_*
来生还长，切勿惆怅；创作不易，点赞再走。