java poi 操作excel

1.pom文件

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

2.java代码

写入xls

public static void createXls() {
	HSSFWorkbook wb = new HSSFWorkbook();    
    HSSFSheet sheet = wb.createSheet();    
    HSSFRow row = sheet.createRow(0);    
    row.createCell(0).setCellValue("123");
    row.createCell(1).setCellValue("456");
        
    row = sheet.createRow(1);    
    row.createCell(0).setCellValue("789");
    row.createCell(1).setCellValue("000");

    try{    
        FileOutputStream fout = new FileOutputStream("1.xls");    
        wb.write(fout);    
        fout.close();    
    }catch (IOException e){    
        e.printStackTrace();    
    }   
}

 

写入xlsx

 

public static void createXlsx() {
		XSSFWorkbook hwb = new XSSFWorkbook();
		XSSFSheet sheet = hwb.createSheet();
		XSSFRow row = sheet.createRow(0);
		row.createCell(0).setCellValue("ABC");
		row.createCell(1).setCellValue("DEF");
		row = sheet.createRow(1);
		row.createCell(0).setCellValue("HIJ");
		row.createCell(1).setCellValue("KLM");
		
		OutputStream out;
		try {
			out = new FileOutputStream(xlsxname);
			hwb.write(out);
			out.close();
		}catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

读取xls

public static void readXls() {
		FileInputStream fileInput;
		try {
			fileInput = new FileInputStream(xlsname);
			POIFSFileSystem ts = new POIFSFileSystem(fileInput);
			HSSFWorkbook workbook = new HSSFWorkbook(ts);
			HSSFSheet sh = workbook.getSheetAt(0);
			
			for(int i=0;i<sh.getPhysicalNumberOfRows();i++){
				Row row = sh.getRow(i);
				for(int j=0;j<row.getPhysicalNumberOfCells();j++){
					System.out.print(row.getCell(j).getStringCellValue()+" ");
				}
				System.out.println();
			}
			
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

 

读取xlsx

 

public static void readXlsx() {

		InputStream is;
		try {
			is = new FileInputStream(xlsxname);
			XSSFWorkbook xwb = new XSSFWorkbook(is);
			XSSFSheet sheet = xwb.getSheetAt(0);
			XSSFRow row;

			System.out.println(">>>>>>>>>>>>>>>>>>>>>>>");
			for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
				row = sheet.getRow(i);
				for(int j=0;j<row.getPhysicalNumberOfCells();j++){
					XSSFCell cell=row.getCell(j);
					System.out.print(cell+" ");
				}
				System.out.println();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}