做项目时要用到Excel批量导入导出数据，网上搜了以下大部分都是.xls格式Excel文件的教程。
导入.xlsx跟.xls格式的过程差不了多少，就是导出的时候好像有点不同，而且网上也没教程，于是硬着头皮写了一个（并没有看官方Api文档(￣▽￣)"）。
首先是导入Jar包，在上传的项目里面已经将需要用到的Jar文件放在lib文件夹里面了，我们只需要在Eclipse里面设置一下：
这里表格信息用Teacher这个类封装：
package JavaBean;public class Teacher {private String Email;private String Name;private String Password;private String Major;public Teacher(String Email,String Password,String Name,String Major) {this.Email=Email;this.Password=Password;this.Name=Name;this.Major=Major;}public String getEmail() {return Email;}public String getName() {return Name;}public String getPassword() {return Password;}public String getMajor() {return Major;}public String toString() {String information=String.format("Email:%s Major:%s Name:%s Password:%s", this.Email,this.Major,this.Name,this.Password);return information;}}
字体设置的类：（由于表格的每个格子都需要设置一下，单独建个类方便些）
package Excel;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/** 该类用于对表格主题样式的设置*/public class Style {public static 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;}}
最后是导入导出：
package Excel;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.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.ss.util.CellRangeAddress;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 JavaBean.Teacher;/*** * 该类用于教师于学生对应关系表格的读取.xlsx和输出.xlsx表格文件,同时也支持xls格式的Excel表格文件* 参考/hhhshct/p/7255915.html*/public class TeacherTableEnhanced {private String filePath;//文件位置private final String outPutSheetName="学生预约系统教师信息";//Excel文件的标题public TeacherTableEnhanced(String filePath){//构造方法,Excel文件位置为参数this.filePath=filePath;}public List<Teacher> getFromExcel(){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(Cell.CELL_TYPE_STRING);cell1.setCellType(Cell.CELL_TYPE_STRING);cell2.setCellType(Cell.CELL_TYPE_STRING);cell3.setCellType(Cell.CELL_TYPE_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;}//读取excelpublic 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 wb = new HSSFWorkbook(is);}else if(".xlsx".equals(extString)){return wb = new XSSFWorkbook(is);}else{return wb = null;}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return wb; }/**导出xlsx文件* /kf/05/510933.html* /f-anything/p/5996380.html*/public void outPutToAExcel(List<Teacher> teachers) throws IOException {XSSFWorkbook wb=new XSSFWorkbook();XSSFCellStyle style=Style.createCellStyle(wb);XSSFSheet sheet=wb.createSheet(outPutSheetName);//这里的是表格内像素的转换公式,例如第列宽24像素,第二列宽20像素等等..sheet.setColumnWidth(0, 256*24+184);//sheet.setColumnWidth(0, 256*width+184);/duqian42707/article/details/51491312sheet.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();}}public static void main(String[] args) throws IOException {//导出Excel测试TeacherTableEnhanced one=new TeacherTableEnhanced("C:/Users/XPS/Desktop/教师表格.xlsx");//这里需要输入导出的地址List<Teacher> teachers=new ArrayList<Teacher>();Teacher a=new Teacher("111@", "123456", "Eason", "Computer Science");teachers.add(a);one.outPutToAExcel(teachers);//导入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);// } }}
源代码╰(￣ω￣ｏ)：/Ruukita/Java-Excel
