java之POI操作Excel表

迈不过友情╰ 2022-05-25 05:05 180阅读 0赞

excel 表格的路劲问题
    
    
     package yiche.com.testPol;
    
    import com.sun.xml.internal.messaging.saaj.packaging.mime.util.LineInputStream;
    import javafx.scene.input.RotateEvent;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.Cell;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExcelOperate { 
    
        public static void main(String[] args) throws Exception{
           System.out.println(readExcel());
        }
    
        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;
        }
    
        public static void createExcel(List<Student> list) throws Exception{
    
             // 创建一个Excel文件
    
            HSSFWorkbook workbook=new HSSFWorkbook();
    
            //创建一个工作表
            HSSFSheet sheet=workbook.createSheet("学生表一");
    
            //添加表头
    
            HSSFRow row=sheet.createRow(0);
            //设置单元格居中
            HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
            //添加表头内容 第一列
            HSSFCell 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);
    
    
            //添加数据内容
            for(int i=0;i<list.size();i++){
    
                row=sheet.createRow(i+1);
    
                Student student=list.get(i);
                //创建单元格 并设置值
                HSSFCell cell=row.createCell(0);
                cell.setCellValue(student.getName());
                cell.setCellStyle(cellStyle);
    
    
                cell=row.createCell(1);
                cell.setCellValue(student.getAge());
                cell.setCellStyle(cellStyle);
    
    
                 cell=row.createCell(2);
                 cell.setCellValue(student.getGrade());
                 cell.setCellStyle(cellStyle);
    
    
            }
    
    
            //保存excel文件
            OutputStream outputStream=new FileOutputStream("D:/student.xls");
            workbook.write(outputStream);
            outputStream.close();
        }
    
    
        public static List<Student> readExcel() throws Exception{
           List<Student> list=new ArrayList<Student>();
           HSSFWorkbook workbook=null;
    
           //读取文件
            InputStream inputStream=new FileInputStream("D:/student.xls");
            //输入流作为参数
            workbook=new HSSFWorkbook(inputStream);
    
            //循环工作流
            for(int i=0;i<workbook.getNumberOfSheets();i++){
                //每张工作表
                 HSSFSheet sheet=workbook.getSheetAt(i);
                 if (sheet== null){
                     continue;
                 }
    
                 //循环行 注意从1开始 不要表头
                 for(int row=1;row<=sheet.getLastRowNum();row++){
    
                     HSSFRow row1=sheet.getRow(row);
                     if (row1 == null){
                         continue;
                     }
    
                      Student student=new Student();
                     //得到单元格的值
                     HSSFCell cell=row1.getCell(0);
    
                     if (cell == null){
                         continue;
                     }
                      student.setName(cell.getStringCellValue());
    
                     cell=row1.getCell(1);
                     if (cell == null){
                         continue;
                     }
    
                     student.setAge((int)cell.getNumericCellValue());
    
                     //如果读取的是数值的话 防止错误
    
                     cell=row1.getCell(2);
                     if (cell == null){
                         continue;
    
                     }
    
                     student.setGrade(cell.getStringCellValue());
    
                     list.add(student);
    
                 }
            }
            return  list;
        }
    
    }

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
      </dependency>
      <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
      </dependency>
    </dependencies>

参考：

单独的excel表  
[http://blog.csdn.net/kong\_gu\_you\_lan/article/details/52798383][http_blog.csdn.net_kong_gu_you_lan_article_details_52798383]

批量的excel表  
[http://blog.csdn.net/nayun123/article/details/52126428][http_blog.csdn.net_nayun123_article_details_52126428]

[http_blog.csdn.net_kong_gu_you_lan_article_details_52798383]: http://blog.csdn.net/kong_gu_you_lan/article/details/52798383
[http_blog.csdn.net_nayun123_article_details_52126428]: http://blog.csdn.net/nayun123/article/details/52126428