【Apache POI】Java 读取Excel文件

àì夳堔傛蜴生んèń 2022-09-30 11:48 367阅读 0赞

Excel内容如下：

Center

封装Excel行列字段的Bean：

  1. public class Employee {
  3.     private String name;
  4.     private String gender;
  5.     private int age;
  6.     private String department;
  7.     private double salary;
  8.     /**
  9.      * 注意：读取日期操作要将Excel单元格设为文本格式，然后按字符串读取；写入操作时，直接按字符串写入
  10.      */
  11.     private String date;
  12.     public Employee() {
  13.         super();
  14.     }
  15.     public Employee(String name, String gender, int age, String department,
  16.             double salary, String date) {
  17.         super();
  18.         this.name = name;
  19.         this.gender = gender;
  20.         this.age = age;
  21.         this.department = department;
  22.         this.salary = salary;
  23.         this.date = date;
  24.     }
  25.     //省略getter/setter方法
  26.     @Override
  27.     public String toString() {
  28.         return "Employee [name=" + name + ", gender=" + gender + ", age=" + age
  29.                 + ", department=" + department + ", salary=" + salary
  30.                 + ", date=" + date + "]";
  31.     }
  32. }

读取Excel文件的方法：

  1. import java.io.File;
  2. import java.io.FileInputStream;
  3. import java.io.FileNotFoundException;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.util.ArrayList;
  7. import java.util.Iterator;
  8. import java.util.List;
  9. import org.apache.poi.hssf.usermodel.HSSFCell;
  10. import org.apache.poi.hssf.usermodel.HSSFRow;
  11. import org.apache.poi.hssf.usermodel.HSSFSheet;
  12. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  13. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  14. import org.apache.poi.ss.usermodel.Cell;
  15. import org.apache.poi.ss.usermodel.Row;
  16. import org.apache.poi.ss.usermodel.Sheet;
  17. import org.apache.poi.ss.usermodel.Workbook;
  18. import org.apache.poi.ss.usermodel.WorkbookFactory;
  19. import com.lmb.excel.bean.Employee;
  21. /**
  22.  * 读取Excel文件的方法
  23.  * @author lmb
  24.  * @date 2017-3-15
  25.  *
  26.  */
  27. public class ReadExcel {
  29.     private static String xls2003 = "D:\\employee.xls";
  30.     private static String xlsx2007 = "D:\\employee.xlsx";
  32.     /** 
  33.      * 读取Excel2003的主表数据 （单个sheet）
  34.      * @param filePath 
  35.      * @return 
  36.      */  
  37.     private static List<Employee> readFromXLS2003(String filePath) {  
  38.         File excelFile = null;// Excel文件对象  
  39.         InputStream is = null;// 输入流对象  
  40.         String cellStr = null;// 单元格，最终按字符串处理  
  41.         List<Employee> employeeList = new ArrayList<Employee>();// 返回封装数据的List  
  42.         Employee employee = null;// 每一个雇员信息对象  
  43.         try {  
  44.             excelFile = new File(filePath);  
  45.             is = new FileInputStream(excelFile);// 获取文件输入流  
  46.             HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象  
  47.             HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0  
  48.             // 开始循环遍历行，表头不处理，从1开始  
  49.             for (int i = 1; i <= sheet.getLastRowNum(); i++) {  
  50.                 HSSFRow row = sheet.getRow(i);// 获取行对象 
  51.                 employee = new Employee();// 实例化Student对象  
  52.                 if (row == null) {// 如果为空，不处理  
  53.                     continue;  
  54.                 }  
  55.                 // 循环遍历单元格  
  56.                 for (int j = 0; j < row.getLastCellNum(); j++) {  
  57.                     HSSFCell cell = row.getCell(j);// 获取单元格对象  
  58.                     if (cell == null) {// 单元格为空设置cellStr为空串  
  59.                         cellStr = "";  
  60.                     } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理  
  61.                         cellStr = String.valueOf(cell.getBooleanCellValue());  
  62.                     } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理  
  63.                         cellStr = cell.getNumericCellValue() + "";
  64.                     } else {// 其余按照字符串处理  
  65.                         cellStr = cell.getStringCellValue();  
  66.                     }  
  67.                     // 下面按照数据出现位置封装到bean中  
  68.                     if (j == 0) {  
  69.                         employee.setName(cellStr);  
  70.                     } else if (j == 1) {  
  71.                         employee.setGender(cellStr);  
  72.                     } else if (j == 2) {  
  73.                         employee.setAge(new Double(cellStr).intValue());  
  74.                     } else if (j == 3) {  
  75.                         employee.setDepartment(cellStr);  
  76.                     } else if(j == 4){  
  77.                         employee.setSalary(new Double(cellStr).intValue());  
  78.                     }else {  
  79.                         employee.setDate(cellStr);  
  80.                     }  
  81.                 }  
  82.                 employeeList.add(employee);// 数据装入List  
  83.             }  
  84.         } catch (IOException e) {  
  85.             e.printStackTrace();  
  86.         } finally {// 关闭文件流  
  87.             if (is != null) {  
  88.                 try {  
  89.                     is.close();  
  90.                 } catch (IOException e) {  
  91.                     e.printStackTrace();  
  92.                 }  
  93.             }  
  94.         }  
  95.         return employeeList;  
  96.     }  
  98.     /**
  99.      * 读取Excel2003的表头
  100.      * @param filePath 需要读取的文件路径
  101.      * @return
  102.      */
  103.     public static String[] readHeaderFromXLS2003(String filePath){
  104.         String[] excelTitle = null;
  105.         FileInputStream is = null;
  106.         try{
  107.             File excelFile = new File(filePath);
  108.             is = new FileInputStream(excelFile);
  109.             HSSFWorkbook workbook2003 = new HSSFWorkbook(is);
  110.             //循环读取工作表
  111.             for (int i = 0; i < workbook2003.getNumberOfSheets(); i++) {
  112.                  HSSFSheet hssfSheet = workbook2003.getSheetAt(i);             
  113.                 //*************获取表头是start*************
  114.                     HSSFRow sheetRow = hssfSheet.getRow(i);  
  115.                     excelTitle = new String[sheetRow.getLastCellNum()];
  116.                     for (int k = 0; k < sheetRow.getLastCellNum(); k++) {
  117.                         HSSFCell hssfCell = sheetRow.getCell(k);
  118.                         excelTitle[k] = hssfCell.getStringCellValue();
  119. //                        System.out.println(excelTitle[k] + " ");
  120.                     }
  121.                     //*************获取表头end*************
  122.             }
  123.         }catch (IOException e) {  
  124.             e.printStackTrace();  
  125.         } finally {// 关闭文件流  
  126.             if (is != null) {  
  127.                 try {  
  128.                     is.close();  
  129.                 } catch (IOException e) {  
  130.                     e.printStackTrace();  
  131.                 }  
  132.             }  
  133.         }          
  134.         return excelTitle;
  135.     }
  137.     /**
  138.      * 读取Excel2007的示例方法 （单个sheet）
  139.      * @param filePath
  140.      * @return
  141.      */
  142.     public static List<Employee> readFromXLSX2007(String filePath) {  
  143.         File excelFile = null;// Excel文件对象  
  144.         InputStream is = null;// 输入流对象  
  145.         String cellStr = null;// 单元格，最终按字符串处理  
  146.         List<Employee> studentList = new ArrayList<Employee>();// 返回封装数据的List  
  147.         Employee employee = null;// 每一个雇员信息对象  
  148.         try {  
  149.             excelFile = new File(filePath);  
  150.             is = new FileInputStream(excelFile);// 获取文件输入流  
  151. //            XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
  152.             org.apache.poi.ss.usermodel.Workbook workbook2007 = WorkbookFactory.create(is);
  153. //            XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0 
  154.             org.apache.poi.ss.usermodel.Sheet sheet = workbook2007.getSheetAt(0);
  155.             // 开始循环遍历行，表头不处理，从1开始  
  156.             for (int i = 1; i <= sheet.getLastRowNum(); i++) {  
  157.                 employee = new Employee();// 实例化Student对象  
  158. //                HSSFRow row = sheet.getRow(i);// 获取行对象 
  159.                 Row row = sheet.getRow(i);// 获取行对象  
  160.                 if (row == null) {// 如果为空，不处理
  161.                     continue;  
  162.                 }  
  163.                 // 循环遍历单元格  
  164.                 for (int j = 0; j < row.getLastCellNum(); j++) {  
  165. //                    XSSFCell cell = row.getCell(j);// 获取单元格对象 
  166.                     Cell cell = row.getCell(j);// 获取单元格对象 
  167.                     if (cell == null) {// 单元格为空设置cellStr为空串  
  168.                         cellStr = "";  
  169.                     } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理  
  170.                         cellStr = String.valueOf(cell.getBooleanCellValue());  
  171.                     } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理  
  172.                         cellStr = cell.getNumericCellValue() + "";  
  173.                     } else {// 其余按照字符串处理  
  174.                         cellStr = cell.getStringCellValue();  
  175.                     }  
  176.                     // 下面按照数据出现位置封装到bean中  
  177.                     if (j == 0) {  
  178.                         employee.setName(cellStr);  
  179.                     } else if (j == 1) {  
  180.                         employee.setGender(cellStr);  
  181.                     } else if (j == 2) {  
  182.                         employee.setAge(new Double(cellStr).intValue());  
  183.                     } else if (j == 3) {  
  184.                         employee.setDepartment(cellStr);  
  185.                     } else if(j == 4){  
  186.                         employee.setSalary(new Double(cellStr).intValue());  
  187.                     } else {  
  188.                         employee.setDate(cellStr);  
  189.                     }  
  190.                 }  
  191.                 studentList.add(employee);// 数据装入List  
  192.             }  
  193.         } catch (IOException e) {  
  194.             e.printStackTrace();  
  195.         } catch (InvalidFormatException e) {
  196.             // TODO Auto-generated catch block
  197.         }finally {// 关闭文件流  
  198.             if (is != null) {  
  199.                 try {  
  200.                     is.close();  
  201.                 } catch (IOException e) {  
  202.                     e.printStackTrace();  
  203.                 }  
  204.             }  
  205.         }  
  206.         return studentList;  
  207.     }  
  209.     /**
  210.      * 读取Excel的示例方法 （多个sheet）
  211.      * @param filePath
  212.      * @return
  213.      */
  214.     public List<Employee> readMoreSheetFromXLS(String filePath){
  215.         List<Employee> employeeList = new ArrayList<Employee>();
  216.         String cellStr = null;//单元格，最终按字符串处理
  217.         //创建来自excel文件的输入流
  218.         try {
  219.             FileInputStream is = new FileInputStream(filePath);
  220.             //创建WorkBook实例
  221.             Workbook workbook = null;
  222.             if (filePath.toLowerCase().endsWith("xls")) {//2003
  223.                 workbook = new HSSFWorkbook(is);
  224.             }else if(filePath.toLowerCase().endsWith("xlsx")){//2007
  225.                 workbook = WorkbookFactory.create(is);
  226.             }
  227.             //获取excel文件的sheet数量
  228.             int numOfSheets = workbook.getNumberOfSheets();
  229.             //挨个遍历sheet
  230.             for (int i = 0; i < numOfSheets; i++) {
  231.                 Sheet sheet = workbook.getSheetAt(i);
  232.                 //挨个遍历sheet的每一行
  233.                 for (Iterator<Row> iterRow = sheet.iterator();iterRow.hasNext();) {
  234.                     Row row = iterRow.next();
  235.                     Employee employee = new Employee();
  236.                     int j = 0;//标识位，用于标识第几列
  237.                     //挨个遍历每一行的每一列
  238.                     for (Iterator<Cell> cellIter = row.cellIterator();cellIter.hasNext();) {
  239.                         Cell cell = cellIter.next();//获取单元格对象
  240.                         if (j == 0) {
  241.                             if (cell == null) {// 单元格为空设置cellStr为空串  
  242.                                 cellStr = "";  
  243.                             } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理  
  244.                                 cellStr = String.valueOf(cell.getBooleanCellValue());  
  245.                             } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理  
  246.                                 cellStr = cell.getNumericCellValue() + "";  
  247.                             } else {// 其余按照字符串处理  
  248.                                 cellStr = cell.getStringCellValue();  
  249.                             }  
  250.                             employee.setName(cellStr); 
  251.                             j ++;
  252.                         }
  253. //                        employee.setGender(cellStr); j == 1
  254. //                        employee.setAge(new Double(cellStr).intValue()); j == 2
  255. //                        employee.setDepartment(cellStr); j == 3
  256. //                        employee.setSalary(new Double(cellStr).intValue());  j == 4
  257. //                        employee.setDate(cellStr); j == 5
  258.                         employeeList.add(employee);
  259.                     }
  260.                 }
  261.             }
  262.         } catch (FileNotFoundException e) {
  263.             e.printStackTrace();
  264.         } catch (IOException e) {
  265.             e.printStackTrace();
  266.         } catch (InvalidFormatException e) {
  267.             e.printStackTrace();
  268.         }
  269.         return employeeList;
  270.     }
  272.     /** 
  273.      * 主函数 
  274.      *  
  275.      * @param args 
  276.      */  
  277.     public static void main(String[] args) {  
  278.         System.out.println("************读取Excel2003的主表数据（将Excel单元格设为文本格式）******************");
  279.         long start2003 = System.currentTimeMillis();  
  280.         List<Employee> list2003 = readFromXLS2003(xls2003);  
  281.         for (Employee employee : list2003) {  
  282.             System.out.println(employee);  
  283.         }  
  284.         long end2003 = System.currentTimeMillis();  
  285.         System.out.println((end2003 - start2003) + " ms done!");  
  286.         System.out.println("***********读取Excel2007的主表数据（未将Excel单元格设为文本格式） *********************");
  287.         long start2007 = System.currentTimeMillis();  
  288.         List<Employee> list2007 = readFromXLSX2007(xlsx2007);  
  289.         for (Employee employee : list2007) {  
  290.             System.out.println(employee);  
  291.         }
  292.         long end2007 = System.currentTimeMillis();  
  293.         System.out.println((end2007 - start2007) + " ms done!");  
  294.         System.out.println("************读取Excel2003的表头**********************");
  295.         String[] excelTitle = readHeaderFromXLS2003(xls2003);
  296.         StringBuffer title = new StringBuffer(); 
  297.         for (int i = 0; i < excelTitle.length; i++) {
  298.             title.append(excelTitle[i] + " ");
  299.         }
  300.         System.out.println(title.toString());
  301.     }  
  303.     /*    运行结果：http://stackoverflow.com/questions/21992071/org-apache-poi-poixmlexception-org-apache-poi-openxml4j-exceptions-invalidforma
  304.         ************读取Excel2003的主表数据（将Excel单元格设为文本格式）******************
  305.         Employee [name=Tom, gender=男, age=29, department=信息技术部, salary=6800.0, date=20170316]
  306.         Employee [name=Jack, gender=男, age=25, department=质量保障部, salary=6800.0, date=20170316]
  307.         Employee [name=May, gender=女, age=26, department=公共关系部, salary=6800.0, date=20170316]
  308.         Employee [name=Mary, gender=女, age=27, department=财务部, salary=6800.0, date=20170316]
  309.         Employee [name=Sarah, gender=女, age=28, department=电销部, salary=6800.0, date=20170316]
  310.         268 ms done!
  311.          ***********读取Excel2007的主表数据 （未将Excel单元格设为文本格式）*********************
  312.         Employee [name=Tom, gender=男, age=29, department=信息技术部, salary=6800.0, date=42810.0]
  313.         Employee [name=Jack, gender=男, age=25, department=质量保障部, salary=6800.0, date=42810.0]
  314.         Employee [name=May, gender=女, age=26, department=公共关系部, salary=6800.0, date=42810.0]
  315.         Employee [name=Mary, gender=女, age=27, department=财务部, salary=6800.0, date=42810.0]
  316.         Employee [name=Sarah, gender=女, age=28, department=电销部, salary=6800.0, date=42810.0]
  317.         924 ms done!
  318.          ************读取Excel2003的表头**********************
  319.         姓名 性别 年龄 部门 薪资 日期 
  320.     */
  321. }

以上方法分为对Excel正文的读取和对Excel表头的读取，注释写的很详细，我就不在多说什么，大家参看具体实现。

需要特别说明一点：

读取日期操作要将Excel单元格设为文本格式，然后程序中按字符串读取；写入操作时，程序内也直接按字符串写入。否则读取到的日期内容是有问题的，参看上面的日志打印结果。
将Excel单元格设为文本格式的方法如下：

Center 1

Center 2

Center 3

发表评论

表情：
评论列表 （有 0 条评论，367人围观）

还没有评论，来说两句吧...

相关阅读