Java解析Excel内容的方法

时间:2021-11-09 09:27:44

本文实例讲述了Java解析Excel内容的方法。分享给大家供大家参考。具体实现方法如下:

 

复制代码代码如下:

import java.io.File;  
import java.io.FileInputStream;  
import java.io.InputStream;  
import java.util.ArrayList;  
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.xssf.usermodel.XSSFWorkbook;  
public class Test {  
    /** 
     * @param args 
     */  
    public static void main(String[] args) {  
        meetquery("403", "e:\\Excel\\1火灾三级预案处置流程.xlsx");  
    }  
    private static ArrayList<MeetBean> meetquery(String level, String filename) {  
        ArrayList<MeetBean> list = new ArrayList<MeetBean>();  
        try {  
            File file = new File(filename);  
            InputStream is = new FileInputStream(file);  
            Workbook workbook = null;// 一个workbook对象,就是一个Excel文件  
            Sheet sheet = null;// sheet页,因excel总有多个sheet页,需要判断具体取值哪一个  
            Row row1 = null;// Sheet页数中的某一行  
            int colNum = 0;// Sheet总行数  
            Cell cell = null;// 第一列内容  
            Cell cell1 = null;// 第二列内容  
            String meetname = null;// 要点名称  
            String meetid = null;// 要点编号  
            String meethine = null;// 要点提示内容  
            String meettime = null;// 处置时间  
            MeetBean meet = null;  
            // 判断文件是什么格式 2003/2007 根据版本不同 处置对象也不同  
            if (filename.endsWith(".xls")) {  
                workbook = new HSSFWorkbook(is);// Excel 2003  
            } else if (filename.endsWith(".xlsx")) {  
                workbook = new XSSFWorkbook(is);// Excel 2007  
            } else {  
                return null;  
            }  
            // 判断处理那个Sheet页,共有三个用户,分别处置不同的处置要点
            if (level == "401") {  
                // 值班站长的处置要点内容、处置提示  
                sheet = workbook.getSheetAt(0);  
                colNum = sheet.getLastRowNum();// 总行数 不包括标题内容  
                System.out.println("共有:" + colNum + "行");  
                for (int i = 3; i <= colNum; i++) {  
                    meet = new MeetBean();  
                    row1 = sheet.getRow(i);// 要解析的行数  
                    cell = row1.getCell((short) 2);// 要解析要点名称的列数  
                    cell1 = row1.getCell((short) 4);// 要解析要点提示内容的列数  
                    if (cell != null && cell1 != null) {  
                        meetname = cell.getStringCellValue();  
                        meethine = cell1.getStringCellValue();  
                        meetid = "YD" + i;  
                        // 如果处置要点名称为空,则是循环到了最后一个处置要点,则返回。。。  
                        if (!meetname.equals("")) {  
                            String intstr = String  
                                    .valueOf((int) (Math.random() * 10 + 1));// 生成1-10的随机数  
                            // 如果是1-9随机数,则需要自动补零 时间格式为00:00:00  
                            if (intstr.length() < 2) {  
                                String min = "0" + intstr;  
                                meettime = "00:" + min + ":00";  
                            } else {  
                                meettime = "00:" + intstr + ":00";  
                            }  
                            meet.setMeetid(meetid);// 处置要点编号  
                            meet.setMeetname(meetname);// 处置要点名称  
                            meet.setMeethint(meethine);// 处置0要点提示内容  
                            meet.setMeettime(meettime);// 处置时间  
                            meet.setMeetLevel("401");// 处置要点级别  
                            list.add(meet);  
                        } else {  
                            return list;  
                        }  
                    } else {  
                        return list;  
                    }  
                }  
            } else if (level == "402") {  
                sheet = workbook.getSheetAt(1);// OCC调度员的处置要点内容、处置提示  
                colNum = sheet.getLastRowNum();// 总行数 不包括标题内容  
                System.out.println("共有:" + colNum + "行");  
                for (int i = 3; i <= colNum; i++) {  
                    meet = new MeetBean();  
                    row1 = sheet.getRow(i);// 要解析的行数  
                    cell = row1.getCell((short) 2);// 要解析要点名称的列数  
                    cell1 = row1.getCell((short) 4);// 要解析要点提示内容的列数  
                    if (cell != null && cell1 != null) {  
                        meetname = cell.getStringCellValue();  
                        meethine = cell1.getStringCellValue();  
                        meetid = "YD" + i;  
                        // 如果处置要点名称为空,则是循环到了最后一个处置要点,则返回。。。  
                        if (!meetname.equals("")) {  
                            String intstr = String  
                                    .valueOf((int) (Math.random() * 10 + 1));// 生成1-10的随机数  
                            // 如果是1-9随机数,则需要自动补零 时间格式为00:00:00  
                            if (intstr.length() < 2) {  
                                String min = "0" + intstr;  
                                meettime = "00:" + min + ":00";  
                            } else {  
                                meettime = "00:" + intstr + ":00";  
                            }  
                            meet.setMeetid(meetid);// 处置要点编号  
                            meet.setMeetname(meetname);// 处置要点名称  
                            meet.setMeethint(meethine);// 处置要点提示内容  
                            meet.setMeettime(meettime);// 处置时间  
                            meet.setMeetLevel("402");// 处置要点级别  
                            list.add(meet);  
                        } else {  
                            return list;  
                        }  
                    } else {  
                        return list;  
                    }  
                }  
            } else if (level == "403") {  
                sheet = workbook.getSheetAt(2);// 控制中心的处置要点内容、处置提示  
                colNum = sheet.getLastRowNum();// 总行数 不包括标题内容  
                System.out.println("共有:" + colNum + "行");  
                int nameInt = 0;  
                int hineInt = 0;  
                for (int j = 0; j <= colNum; j++) {  
                    row1 = sheet.getRow(3);// 要解析的行数 只有第三行中存在处置要点名称,提示内容  
                    cell = row1.getCell((short) 1);// 要解析要点名称的列数  
                    cell1 = row1.getCell((short) 2);// 要解析要点提示内容的列数  
                }  
                for (int i = 3; i <= colNum; i++) {  
                    meet = new MeetBean();  
                    row1 = sheet.getRow(i);// 要解析的行数  
                    cell = row1.getCell((short) 1);// 要解析要点名称的列数  
                    cell1 = row1.getCell((short) 2);// 要解析要点提示内容的列数  
                    if (cell != null && cell1 != null) {  
                        meetname = cell.getStringCellValue();  
                        meethine = cell1.getStringCellValue();  
                        meetid = "YD" + i;  
                        // 如果处置要点名称为空,则是循环到了最后一个处置要点,则返回。。。  
                        if (!meetname.equals("")) {  
                            String intstr = String  
                                    .valueOf((int) (Math.random() * 10 + 1));// 生成1-10的随机数  
                            // 如果是1-9随机数,则需要自动补零 时间格式为00:00:00  
                            if (intstr.length() < 2) {  
                                String min = "0" + intstr;  
                                meettime = "00:" + min + ":00";  
                            } else {  
                                meettime = "00:" + intstr + ":00";  
                            }  
                            meet.setMeetid(meetid);// 处置要点编号  
                            meet.setMeetname(meetname);// 处置要点名称  
                            meet.setMeethint(meethine);// 处置要点提示内容  
                            meet.setMeettime(meettime);// 处置时间  
                            meet.setMeetLevel("403");// 处置要点级别  
                            list.add(meet);  
                        } else {  
                            return list;  
                        }  
                    } else {  
                        return list;  
                    }  
                }  
            }    
            is.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return list;  
    }  
}

 

希望本文所述对大家的java程序设计有所帮助。