（二）JAVA使用POI操作excel

快来打我* 2022-01-31 02:55 224阅读 0赞

链接：[https://www.cnblogs.com/wishwzp/p/5494038.html][https_www.cnblogs.com_wishwzp_p_5494038.html]  
出现版本错误，链接：[https://www.cnblogs.com/xx0405/p/5305566.html][https_www.cnblogs.com_xx0405_p_5305566.html]

# 1. 创建一个时间格式的单元格 #

Demo4.java

package com.wajpzywj.exceldemo.main;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    
    import java.io.FileOutputStream;
    import java.util.Calendar;
    import java.util.Date;
    
    public class Demo4 { 
        public static void main(String[] args) throws Exception{ 
            Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
            Sheet sheet=wb.createSheet("第一个Sheet页");  // 创建第一个Sheet页
    
            Row row=sheet.createRow(0); // 创建一个行
    
            Cell cell=row.createCell(0); // 创建一个单元格 第1列
            cell.setCellValue(new Date());  // 给单元格设置值
    
            CreationHelper createHelper=wb.getCreationHelper();
            CellStyle cellStyle=wb.createCellStyle(); //单元格样式类
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy-mm-dd hh:mm:ss"));
            cell=row.createCell(1); // 第二列
            cell.setCellValue(new Date());
            cell.setCellStyle(cellStyle);
    
            cell=row.createCell(2);  // 第三列
            cell.setCellValue(Calendar.getInstance());
            cell.setCellStyle(cellStyle);
    
            FileOutputStream fileOut=new FileOutputStream("d:\\工作簿.xls");
            wb.write(fileOut);
            fileOut.close();
        }
    }

![在这里插入图片描述][20190515134934546.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]

# 2，处理不同内容格式的单元格 #

Demo5.java

package com.wajpzywj.exceldemo.main;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    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 java.io.FileOutputStream;
    import java.util.Date;
    
    public class Demo5 { 
        public static void main(String[] args) throws Exception{ 
            Workbook wb=new HSSFWorkbook(); // 定义一个新的工作簿
            Sheet sheet=wb.createSheet("第一个Sheet页");  // 创建第一个Sheet页
    
            Row row=sheet.createRow(0); // 创建一个行
    
            Cell cell=row.createCell(0); // 创建一个单元格 第1列
            cell.setCellValue(new Date());  // 给单元格设置值
    
            row.createCell(1).setCellValue(1);
            row.createCell(2).setCellValue("一个字符串");
            row.createCell(3).setCellValue(true);
            row.createCell(4).setCellValue(HSSFCell.CELL_TYPE_NUMERIC);
            row.createCell(5).setCellValue(false);
    
            FileOutputStream fileOut=new FileOutputStream("d:\\工作簿.xls");
            wb.write(fileOut);
            fileOut.close();
        }
    }

![在这里插入图片描述][20190515135212733.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 1]

# 3，遍历工作簿的行和列并获取单格内容 #

![在这里插入图片描述][20190515135501968.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 2]  
我们遍历这个excel。

Demo6.java

package com.wajpzywj.exceldemo.main;
    
    import com.wajpzywj.exceldemo.utils.WorkbookFactory;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    
    import java.io.FileInputStream;
    
    public class Demo6 { 
        public static void main(String[] args) throws Exception{ 
    // InputStream is=new FileInputStream("d:\\个人名单.xlsx");
    // POIFSFileSystem fs=new POIFSFileSystem(is);
    // HSSFWorkbook wb=new HSSFWorkbook(fs);
            Workbook wb = WorkbookFactory.create(new FileInputStream("d:\\个人名单.xlsx"));
    
            XSSFSheet xssfSheet= (XSSFSheet) wb.getSheetAt(0); // 获取第一个Sheet页
            if(xssfSheet==null){ 
                return;
            }
            // 遍历行Row
            for(int rowNum=0;rowNum<=xssfSheet.getLastRowNum();rowNum++){ 
                XSSFRow xssfRow=xssfSheet.getRow(rowNum);
                if(xssfRow==null){ 
                    continue;
                }
                // 遍历列Cell
                for(int cellNum=0;cellNum<=xssfRow.getLastCellNum();cellNum++){ 
                    XSSFCell xssfCell=xssfRow.getCell(cellNum);
                    if(xssfCell==null){ 
                        continue;
                    }
                    System.out.print(" "+getValue(xssfCell));
                }
                System.out.println();
            }
        }
    
        private static String getValue(XSSFCell xssfCell){ 
            if(xssfCell.getCellType()==XSSFCell.CELL_TYPE_BOOLEAN){ 
                return String.valueOf(xssfCell.getBooleanCellValue());
            }else if(xssfCell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){ 
                return String.valueOf(xssfCell.getNumericCellValue());
            }else{ 
                return String.valueOf(xssfCell.getStringCellValue());
            }
        }
    }

![在这里插入图片描述][20190515142801266.png]

# 4，文本提取 #

我们文本提取上面个人名单.xls表格。。。

Demo7.java

package com.wajpzywj.exceldemo.main;
    
    import com.wajpzywj.exceldemo.utils.WorkbookFactory;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import org.apache.poi.xssf.extractor.XSSFExcelExtractor;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.FileInputStream;
    
    public class Demo7 { 
        public static void main(String[] args) throws Exception{ 
            Workbook wb = WorkbookFactory.create(new FileInputStream("d:\\个人名单.xlsx"));
    
            XSSFExcelExtractor excelExtractor=new XSSFExcelExtractor((XSSFWorkbook) wb);
            excelExtractor.setIncludeSheetNames(false);// 我们不需要Sheet页的名字
            System.out.println(excelExtractor.getText());
        }
    }

控制台显示：  
![在这里插入图片描述][20190515143606576.png]  
excelExtractor.setIncludeSheetNames(false);这个如果为true的话就会显示Sheet页了

excelExtractor.setIncludeSheetNames(true)：

控制台输出：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 3]

[https_www.cnblogs.com_wishwzp_p_5494038.html]: https://www.cnblogs.com/wishwzp/p/5494038.html
[https_www.cnblogs.com_xx0405_p_5305566.html]: https://www.cnblogs.com/xx0405/p/5305566.html
[20190515134934546.png]: /images/20220131/a75ec552783943a7a696c0061c68f988.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]: /images/20220131/b2dc88134c6c440981a6e83bf81cc5a9.png
[20190515135212733.png]: /images/20220131/c4b0cc584a514e2692b91936ed85ea35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 1]: /images/20220131/7ed01da4d607473d9a0bce7ad0bd911e.png
[20190515135501968.png]: /images/20220131/7236ad893e954746846c614f79ea15ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 2]: /images/20220131/1197d294fa9d4fceb956b7d83f0487d2.png
[20190515142801266.png]: /images/20220131/950b94abf3f64edbb8fd80bdf76bcaf7.png
[20190515143606576.png]: /images/20220131/43d57b9c5ef648f09a01a67e62499a8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 3]: /images/20220131/f35a3e9083a947cc9cefaf1515b49e78.png