Apache POI 简单使用示例代码

Apache POI 使用详解

参考文档：https://www.cnblogs.com/love540376/p/5787022.html

 

示例代码：

/**
 * poi 操作excel实例
 *
 */
public class PoiTest {

	public static void main(String args[]) throws IOException {
		
		PoiTest test = new PoiTest();
		
		//创建表
		//test.create();
		
		//读取表
		test.read();
	}
	
	/**
	 * 创建excel文件，写操作
	 * @throws IOException
	 */
	public void create() throws IOException{
		
		String filePath="d:\\sample.xls";//文件路径
		HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
		HSSFSheet sheet = workbook.createSheet("Test2");//创建工作表(Sheet)
		HSSFRow row = sheet.createRow(0);// 创建行,从0开始
		HSSFCell cell = row.createCell(0);// 创建行的单元格,也是从0开始
		cell.setCellValue("ocean");// 设置单元格内容
		row.createCell(1).setCellValue("kwkw");
		row.createCell(2).setCellValue("dsfdfdf");
		
		//设置日期格式--使用Excel内嵌的格式
		HSSFCell cell3=row.createCell(3);
		cell3.setCellValue(new Date());
		HSSFCellStyle style3=workbook.createCellStyle();
		style3.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		cell3.setCellStyle(style3);
		
		//设置保留2位小数--使用Excel内嵌的格式
		HSSFCell cell4=row.createCell(4);
		cell4.setCellValue(12.3456789);
		HSSFCellStyle style4=workbook.createCellStyle();
		style4.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		cell4.setCellStyle(style4);
		
		//设置中文大写格式--使用自定义的格式
		HSSFCell cell5=row.createCell(5);
		cell5.setCellValue(12345);
		HSSFCellStyle style5=workbook.createCellStyle();
		style5.setDataFormat(workbook.createDataFormat().getFormat("[DbNum2][$-804]0"));
		cell5.setCellStyle(style5);
		
		//和并列
		CellRangeAddress region=new CellRangeAddress(0, 0, 1, 2);//CellRangeAddress(firstRow, lastRow, firstCol, lastCol)
		sheet.addMergedRegion(region);
		
		//合并行
		HSSFCell cell6=row.createCell(6);
		cell6.setCellValue("合并行");
		CellRangeAddress region2=new CellRangeAddress(0, 1, 6, 6);//CellRangeAddress(firstRow, lastRow, firstCol, lastCol)
		sheet.addMergedRegion(region2);
		
		//单元格对其方式
		HSSFCellStyle style6=workbook.createCellStyle();
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		cell6.setCellStyle(style6);
		
		//使用边框
		cell=row.createCell(7);
		cell.setCellValue("设置边框");
		HSSFCellStyle style7=workbook.createCellStyle();
		style7.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框
		style7.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框
		style7.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框
		style7.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框
		style7.setTopBorderColor(HSSFColor.RED.index);//上边框颜色
		style7.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色
		style7.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色
		style7.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色
		cell.setCellStyle(style7);
		
		//设置宽度和高度
		sheet.setColumnWidth(0, 5*256);//设置第0列的宽度是15个字符宽度
		row.setHeightInPoints(50);//设置第0行的高度是50个点
		
		//输出表
		FileOutputStream out = new FileOutputStream(filePath);
		workbook.write(out);//保存Excel文件
		out.close();//关闭文件流
		System.out.println("OK!");
	}
	
	/**
	 * 读取excel文件，读操作
	 * @throws IOException
	 */
	public void read() throws IOException{
		
		String filePath="d:\\sample.xls";//文件路径
		FileInputStream stream = new FileInputStream(filePath);
		HSSFWorkbook workbook = new HSSFWorkbook(stream);//读取现有的Excel		
		HSSFSheet sheet = workbook.getSheetAt(0);//得到第一个sheet
		
		for(Row row : sheet){
			for(org.apache.poi.ss.usermodel.Cell cell: row){
				System.out.println(cell+"\t");
			}
			System.out.println();
		}
	}
		
}