Java 通过POI 操作excel表格：读取、写入

矫情吗；* 2023-07-13 13:34 49阅读 0赞

> Java操作excel表格是很常见的，在下面的例子中我将展示使用Java如何对excel进行读取和写入操作。（我是在ssm中实现的）

#### 一、导入依赖 ####

(如果是在普通Java项目中需要导入相应**jar**包)  
相应的jar包以及demo测试下载：  
链接: [https://pan.baidu.com/s/1M5ce–CxOKKMuFtpsWXQfg][https_pan.baidu.com_s_1M5ce_CxOKKMuFtpsWXQfg]  
提取码：abcy

我还是建议导入依赖来实现。

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

#### 二、编写工具类，实现读写功能 ####

package com.qcby.test;
    
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    /** * @author TEZ */
    public class ExcelUtil { 
    	//默认单元格内容为数字时格式
    	private static DecimalFormat df = new DecimalFormat("0");
    	// 默认单元格格式化日期字符串 
    	private static SimpleDateFormat sdf = new SimpleDateFormat(  "yyyy-MM-dd HH:mm:ss"); 
    	// 格式化数字
    	private static DecimalFormat nf = new DecimalFormat("0.00");  
    	
    	//向文件中写入
    	public static ArrayList<ArrayList<Object>> readExcel(File file){ 
    		if(file == null){ 
    			return null;
    		}
    		if(file.getName().endsWith("xlsx")){ 
    			//处理ecxel2007
    			return readExcel2007(file);
    		}else{ 
    			//处理ecxel2003
    			return readExcel2003(file);
    		}
    	}
    	/* * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 * lists.get(0).get(0)表示过去Excel中0行0列单元格 */
    	public static ArrayList<ArrayList<Object>> readExcel2003(File file){ 
    		try{ 
    			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
    			ArrayList<Object> colList;
    			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
    			HSSFSheet sheet = wb.getSheetAt(0);
    			HSSFRow row;
    			HSSFCell cell;
    			Object value;
    			for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){ 
    				row = sheet.getRow(i);
    				colList = new ArrayList<Object>();
    				if(row == null){ 
    					//当读取行为空时
    					if(i != sheet.getPhysicalNumberOfRows()){ //判断是否是最后一行
    						rowList.add(colList);
    					}
    					continue;
    				}else{ 
    					rowCount++;
    				}
    				for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){ 
    					cell = row.getCell(j);
    					if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){ 
    						//当该单元格为空
    						if(j != row.getLastCellNum()){ //判断是否是该行中最后一个单元格
    							colList.add("");
    						}
    						continue;
    					}
    					switch(cell.getCellType()){ 
    					 case XSSFCell.CELL_TYPE_STRING:  
    		                    System.out.println(i + "行" + j + " 列 is String type");  
    		                    value = cell.getStringCellValue();  
    		                    break;  
    		                case XSSFCell.CELL_TYPE_NUMERIC:  
    		                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {   
    		                        value = df.format(cell.getNumericCellValue());  
    		                    } else if ("General".equals(cell.getCellStyle()  
    		                            .getDataFormatString())) {   
    		                        value = nf.format(cell.getNumericCellValue());  
    		                    } else {   
    		                        value = sdf.format(HSSFDateUtil.getJavaDate(cell  
    		                                .getNumericCellValue()));  
    		                    }  
    		                    System.out.println(i + "行" + j  
    		                            + " 列 is Number type ; DateFormt:"  
    		                            + value.toString()); 
    		                    break;  
    		                case XSSFCell.CELL_TYPE_BOOLEAN:  
    		                    System.out.println(i + "行" + j + " 列 is Boolean type");  
    		                    value = Boolean.valueOf(cell.getBooleanCellValue());
    		                    break;  
    		                case XSSFCell.CELL_TYPE_BLANK:  
    		                    System.out.println(i + "行" + j + " 列 is Blank type");  
    		                    value = "";  
    		                    break;  
    		                default:  
    		                    System.out.println(i + "行" + j + " 列 is default type");  
    		                    value = cell.toString();  
    					}// end switch
    					colList.add(value);
    				}//end for j
    				rowList.add(colList);
    			}//end for i
    			
    			return rowList;
    		}catch(Exception e){ 
    			return null;
    		}
    	}
    	
    	public static ArrayList<ArrayList<Object>> readExcel2007(File file){ 
    		try{ 
    			ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
    			ArrayList<Object> colList;
    			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
    			XSSFSheet sheet = wb.getSheetAt(0);
    			XSSFRow row;
    			XSSFCell cell;
    			Object value;
    			for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){ 
    				row = sheet.getRow(i);
    				colList = new ArrayList<Object>();
    				if(row == null){ 
    					//当读取行为空时
    					if(i != sheet.getPhysicalNumberOfRows()){ //判断是否是最后一行
    						rowList.add(colList);
    					}
    					continue;
    				}else{ 
    					rowCount++;
    				}
    				for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){ 
    					cell = row.getCell(j);
    					if(cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){ 
    						//当该单元格为空
    						if(j != row.getLastCellNum()){ //判断是否是该行中最后一个单元格
    							colList.add("");
    						}
    						continue;
    					}
    					switch(cell.getCellType()){ 
    					 case XSSFCell.CELL_TYPE_STRING:  
    		                    System.out.println(i + "行" + j + " 列 is String type");  
    		                    value = cell.getStringCellValue();  
    		                    break;  
    		                case XSSFCell.CELL_TYPE_NUMERIC:  
    		                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {   
    		                        value = df.format(cell.getNumericCellValue());  
    		                    } else if ("General".equals(cell.getCellStyle()  
    		                            .getDataFormatString())) {   
    		                        value = nf.format(cell.getNumericCellValue());  
    		                    } else {   
    		                        value = sdf.format(HSSFDateUtil.getJavaDate(cell  
    		                                .getNumericCellValue()));  
    		                    }  
    		                    System.out.println(i + "行" + j  
    		                            + " 列 is Number type ; DateFormt:"  
    		                            + value.toString()); 
    		                    break;  
    		                case XSSFCell.CELL_TYPE_BOOLEAN:  
    		                    System.out.println(i + "行" + j + " 列 is Boolean type");  
    		                    value = Boolean.valueOf(cell.getBooleanCellValue());
    		                    break;  
    		                case XSSFCell.CELL_TYPE_BLANK:  
    		                    System.out.println(i + "行" + j + " 列 is Blank type");  
    		                    value = "";  
    		                    break;  
    		                default:  
    		                    System.out.println(i + "行" + j + " 列 is default type");  
    		                    value = cell.toString();  
    					}// end switch
    					colList.add(value);
    				}//end for j
    				rowList.add(colList);
    			}//end for i
    			
    			return rowList;
    		}catch(Exception e){ 
    			System.out.println("exception");
    			return null;
    		}
    	}
    	
    	//文件读取
    	public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){ 
    		if(result == null){ 
    			System.out.println("未添加内容！");
    			return;
    		}
    		File Cfile = new File(path);
    		Workbook wb = null;
    		HSSFSheet sheet_xls = null;
    		XSSFSheet sheet_xlsx = null;
    		if(Cfile.getName().endsWith("xls")){ 
    			wb = new HSSFWorkbook();
    			sheet_xls = (HSSFSheet) wb.createSheet("sheet1");
    			for(int i = 0 ;i < result.size() ; i++){ 
    				HSSFRow row = sheet_xls.createRow(i + 1);
    				if(result.get(i) != null){ 
    					for(int j = 0; j < result.get(i).size() ; j ++){ 
    						HSSFCell cell = row.createCell(j);
    						cell.setCellValue(result.get(i).get(j).toString());
    					}
    				}
    			}
    		}
    		if(Cfile.getName().endsWith("xlsx")){ 
    			wb = new XSSFWorkbook();
    			sheet_xlsx = (XSSFSheet) wb.createSheet("sheet1");
    			for(int i = 0 ;i < result.size() ; i++){ 
    				XSSFRow row = sheet_xlsx.createRow(i + 1);
    				if(result.get(i) != null){ 
    					for(int j = 0; j < result.get(i).size() ; j ++){ 
    						XSSFCell cell = row.createCell(j);
    						cell.setCellValue(result.get(i).get(j).toString());
    					}
    				}
    			}
    		}
    
    		ByteArrayOutputStream os = new ByteArrayOutputStream();
            try
            { 
                wb.write(os);
            } catch (IOException e){ 
                e.printStackTrace();
            }
            byte[] content = os.toByteArray();
            File file = new File(path);//Excel文件生成后存储的位置。
            OutputStream fos  = null;
            try
            { 
                fos = new FileOutputStream(file);
                fos.write(content);
                os.close();
                fos.close();
            }catch (Exception e){ 
                e.printStackTrace();
            }           
    	}
    	
    	public static DecimalFormat getDf() { 
    		return df;
    	}
    	public static void setDf(DecimalFormat df) { 
    		ExcelUtil.df = df;
    	}
    	public static SimpleDateFormat getSdf() { 
    		return sdf;
    	}
    	public static void setSdf(SimpleDateFormat sdf) { 
    		ExcelUtil.sdf = sdf;
    	}
    	public static DecimalFormat getNf() { 
    		return nf;
    	}
    	public static void setNf(DecimalFormat nf) { 
    		ExcelUtil.nf = nf;
    	}
    }

#### 三、测试 ####

测试读取（写入可自行尝试）：

package com.qcby.test;
    
    
    import java.io.File;
    import java.util.ArrayList;
    
    /** * @author TEZ */
    public class ReadExcel { 
    
    	public static void main(String[] args) { 
    		File file = new File("E:/java.xlsx");
    		ArrayList<ArrayList<Object>> result = ExcelUtil.readExcel(file);
    
    		if (result == null) { 
    			System.out.println("文件不存在：请检查路径是否正确！");
    			return;
    		} else if (result.size() == 0) { 
    			System.out.println("文件内容为空!");
    			return;
    		} else { 
    			for (int i = 0; i < result.size(); i++) { 
    				System.out.println("\n");
    				for (int j = 0; j < result.get(i).size(); j++) { 
    					System.out.print(result.get(i).get(j).toString() + " ");
    				}
    			}
    			System.out.println("\n");
    		}
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTIyMTEz_size_16_color_FFFFFF_t_70]

[https_pan.baidu.com_s_1M5ce_CxOKKMuFtpsWXQfg]: https://pan.baidu.com/s/1M5ce--CxOKKMuFtpsWXQfg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTIyMTEz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200312121801827.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTIyMTEz,size_16,color_FFFFFF,t_70