Java 操作Excel poi读写excel

た入场券 2022-05-11 13:22 309阅读 0赞

# Java 操作Excel poi读写excel，excel导入导出（精简版） #

1.所需jar包

下载地址：[https://download.csdn.net/download/u014646662/10734734][https_download.csdn.net_download_u014646662_10734734]

![70][]

2.源码

写操作（excel导出）

package cn.com.tengen.excel;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    import java.util.Set;
    
    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;
    /**
     * excel写操作
     * @author lucky
     *
     */
    public class ExcelWrite {
        private static final String EXCEL_XLS = "xls";
        private static final String EXCEL_XLSX = "xlsx";
        
        public static void main(String[] args) throws IOException {
        	ExcelWrite ew = new ExcelWrite();
        	//excel数据, key是sheet的名字
        	Map<String, List<List<String>>> dataSheets = new HashMap<>();
        	//excel中一个sheet的数据
        	List<List<String>> dataSheet = new ArrayList<>();
        	//excel的一行数据
        	List<String> row = new ArrayList<>();
        	//excel中一个单元的数据
        	row.add("aaaa");
        	row.add("aaaa");
        	row.add("aaaa");
        	dataSheet.add(row);
        	row = new ArrayList<>();
        	row.add("bbbbb");
        	row.add("bbbbb");
        	row.add("bbbbb");
        	dataSheet.add(row);
        	row = new ArrayList<>();
        	row.add("ccccc");       
        	row.add("ccccc");       
        	row.add("ccccc"); 
        	dataSheet.add(row);
        	dataSheets.put("aaa", dataSheet);
        	dataSheets.put("aab", dataSheet);
        	dataSheets.put("aac", dataSheet);
        	ew.writeExcel(dataSheets,"qqqq.xlsx");
        }
    
        /**
         * 创建一个excel <br>
         * @param dataSheets
         * @param file
         * @throws IOException
         */
        public void writeExcel(Map<String, List<List<String>>> dataSheets,String path) throws IOException {
        	
        	Workbook workBook = null;
        	OutputStream out = null;
        	File file = new File(path);
        	try {
        	workBook = getWriteWorkbok(file);
        	Set<Entry<String, List<List<String>>>> entrySet = dataSheets.entrySet();
        	for (Entry<String, List<List<String>>> dataSheet : entrySet) {
        		//创建sheet
        		Sheet sheet = workBook.createSheet(dataSheet.getKey());
        		List<List<String>> list = dataSheet.getValue();
        		if(list != null) {
        			for (int i = 0; i < list.size(); i++) {
        				//创建行
        				Row row = sheet.createRow(i);
        				List<String> dataRow = list.get(i);
        				if(dataRow != null) {
        					for (int j = 0; j < dataRow.size(); j++) {
        						//创建单元格
        						Cell cell = row.createCell(j);
        						cell.setCellValue(dataRow.get(j));
        					}
        				}
        			}
        		}
        		
    		}
        	out =  new FileOutputStream(file);
            workBook.write(out);
        	}finally {
        		if(workBook != null) {
        			workBook.close();
        		}
        		if(out != null) {
        			out.close();
        		}
        	}
        }
        
        
        /**
         * 判断Excel的版本,获取Workbook
         * @param file
         * @return
         * @throws IOException
         */
        private  Workbook getWriteWorkbok(File file) throws IOException{
        	
            Workbook wb = null;
            if(file.getName().endsWith(EXCEL_XLS)){     
                wb = new HSSFWorkbook();
            }else if(file.getName().endsWith(EXCEL_XLSX)){    
                wb = new XSSFWorkbook();
            }
            return wb;
        }
    
    }

读excel（excel导入）

package cn.com.tengen.excel;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    import java.util.Map.Entry;
    import java.util.Set;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    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 ExcelRead {
    	private static final String EXCEL_XLS = "xls";
    	private static final String EXCEL_XLSX = "xlsx";
    
    
    
    	/**
    	 * 读取Excel
    	 * @param path
    	 * @return
    	 * @throws IOException
    	 */
    	public Map<String, List<List<String>>> readExcel(String path) throws IOException {
    
    		Workbook workbook = getWriteWorkbok(path);
    		//excel数据, key是sheet的名字
        	Map<String, List<List<String>>> dataSheets = new HashMap<>();
    		// Read the Sheet
    		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
    			Sheet sheet = workbook.getSheetAt(numSheet);
    			if (sheet == null) {
    				continue;
    			}
    			//excel中一个sheet的数据
    	    	List<List<String>> dataSheet = new ArrayList<>();
    	    	dataSheets.put(sheet.getSheetName(), dataSheet);
    			
    			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
    				Row row = sheet.getRow(rowNum);
    				if (row != null) {
    					//excel的一行数据
    			    	List<String> dataRow = new ArrayList<>();
    			    	dataSheet.add(dataRow);
    					Iterator<Cell> it = row.cellIterator();
    					while(it.hasNext()) {
    						dataRow.add(getValue(it.next()));
    					}
    				}
    			}
    		}
    		return dataSheets;
    	}
    
    	/**
    	 * 获取单元格的值
    	 * @param row
    	 * @return
    	 */
    	private String getValue(Cell row) {
    		if (row.getCellType() == CellType.BOOLEAN) {
    			return String.valueOf(row.getBooleanCellValue());
    		} else if (row.getCellType() == CellType.NUMERIC) {
    			return String.valueOf(row.getNumericCellValue());
    		} else {
    			return String.valueOf(row.getStringCellValue());
    		}
    	}
    
    	/**
    	 * 判断Excel的版本,获取Workbook
    	 * 
    	 * @param file
    	 * @return
    	 * @throws IOException
    	 */
    	private Workbook getWriteWorkbok(String path) throws IOException {
    		if (path == null) {
    			throw new IOException("找不到文件，文件路径可能是null");
    		}
    		InputStream is = new FileInputStream(path);
    
    		if (path.endsWith(EXCEL_XLS)) {
    			return new HSSFWorkbook(is);
    		}
    		if (path.endsWith(EXCEL_XLSX)) {
    			return new XSSFWorkbook(is);
    		}
    		
    		is.close();
    		throw new IOException(path + " 不是Excel文件");
    	}
    	
    	
    	public static void main(String[] args) throws IOException {
    		ExcelRead er = new ExcelRead();
    		Map<String, List<List<String>>> readExcel = er.readExcel("qqqq.xlsx");
    		Set<Entry<String, List<List<String>>>> entrySet = readExcel.entrySet();
    		for (Entry<String, List<List<String>>> sheetsMap : entrySet) {
    			System.out.println("sheet: "+sheetsMap.getKey());
    			for(List<String> sheet:sheetsMap.getValue()) {
    				for (String value : sheet) {
    					System.out.print(value);
    					System.out.print("\t\t");
    				}
    				System.out.println();
    			}
    			System.out.println("---------------------------------------------------------");
    		}
    	}
    }

对人工智能感兴趣的同学，可以点击以下链接：

[现在人工智能非常火爆，很多朋友都想学，但是一般的教程都是为博硕生准备的，太难看懂了。最近发现了一个非常适合小白入门的教程，不仅通俗易懂而且还很风趣幽默。所以忍不住分享一下给大家。点这里可以跳转到教程。][Link 1]

[https://www.cbedai.net/u014646662][Link 1]

[https_download.csdn.net_download_u014646662_10734734]: https://download.csdn.net/download/u014646662/10734734
[70]: /images/20220505/9e3ca656558d4317b12953bef968f43c.png
[Link 1]: https://www.cbedai.net/u014646662