Java操作Excel之Poi

首先是需要导入poi包

1.poi创建Sheet页

public static void main(String[] args) throws Exception {
		Workbook wb = new HSSFWorkbook();
		wb.createSheet("第一个Sheet页");
		wb.createSheet("第二个Sheet页");
		FileOutputStream fileOut = new FileOutputStream("c:\\E\\用poi搞出来的Sheet页.xls");
		wb.write(fileOut);
		fileOut.close();
	}

执行后会在c盘下的文件夹E下创建出一个 用poi搞出来的Sheet页.xls （Excle文件）

2.poi创建单元格和设置值

public static void main(String[] args) throws Exception {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("第一个Sheet页");
		Row row = sheet.createRow(0);//创建第一行
		Cell cell = row.createCell(0);//创建第一行的第一个的单元格
		cell.setCellValue(1);//给单元格设置值
		
		row.createCell(1).setCellValue(1.2);
		row.createCell(2).setCellValue("这是一个字符串");
		
		FileOutputStream fileOut = new FileOutputStream("c:\\E\\用poi搞出来的Cell.xls");
		wb.write(fileOut);
		fileOut.close();
	}

3.设置时间值

public static void main(String[] args) throws Exception {	
		Workbook wb = new HSSFWorkbook();//定义工作簿
		Sheet sheet = wb.createSheet("第一个sheet页");
		Row row = sheet.createRow(0);//创建一个行
		Cell cell = row.createCell(0);//创建一个单元格，第一列
		cell.setCellValue(new Date());//给单元格设置值
		
		CreationHelper createHelper = wb.getCreationHelper();
		CellStyle cellStyle = wb.createCellStyle();//单元格样式
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-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("c:\\E\\工作簿.xls");
		wb.write(fileOut);
		fileOut.close();
	}

4.各种类型的值设置

	public static void main(String[] args) throws Exception {
		Workbook wb = new HSSFWorkbook();//定义工作簿
		Sheet sheet = wb.createSheet("第一个sheet页");
		Row row = sheet.createRow(0);//创建一个行
		Cell cell = row.createCell(0);//创建一个单元格，第一列
		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("c:\\E\\工作簿.xls");
		wb.write(fileOut);
		fileOut.close();
	}

5.遍历

	public static void main(String[] args) throws Exception {
		
		InputStream is = new FileInputStream("c:\\E\\二货名单.xls");
		POIFSFileSystem fs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet hssfSheet = wb.getSheetAt(0);//获取第一个sheet页
		if(hssfSheet==null) {
			return;
		}
		//遍历行Row
		for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);//获取行
			if(hssfRow==null) {
				continue;
			}
			//遍历列Cell
			for(int cellNum=0;cellNum<=hssfRow.getLastCellNum();cellNum++) {
				HSSFCell hssfCell = hssfRow.getCell(cellNum);//获取单元格
				if(hssfCell==null) {
					continue;
				}
				System.out.println(getValue(hssfCell));
			}
			System.out.println();
		}
	}
	
	private static String getValue(HSSFCell hssfCell) {
		if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		}else{
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

6.抽取文本

public class Demo7 {

	public static void main(String[] args) throws Exception {
		
		InputStream is = new FileInputStream("c:\\E\\二货名单.xls");
		POIFSFileSystem fs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		
		ExcelExtractor excelExtractor = new ExcelExtractor(wb);
		System.out.println(excelExtractor.getText());
	}
	

}
<