做项目时要用到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表格文件 * 参考https://www.cnblogs.com/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; } //读取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 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文件 * https://www.2cto.com/kf/201605/510933.html * https://www.cnblogs.com/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);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(); } } 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@qq.com", "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); // } } }
源代码╰(￣ω￣ｏ)：https://github.com/Ruukita/Java-Excel
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
