POI（2）-----------简单操作Excel表

1、简单创建单元格

/ * HFFSWorkbook读写xls格式
		 * XFFSWorkbook读写xlsx
		 * SXSSFWorkbook低内存占用，超过65536条数据使用
		 */
		//创建一个新的工作薄
		HSSFWorkbook wb = new HSSFWorkbook();
		FileOutputStream fos = new FileOutputStream(new File("1.xls"));
		wb.write(fos);
		fos.close();
		System.out.println("create wb successfully");
		
		//打开现有的工作薄
		File file = new File("1.xls");
		FileInputStream fis = new FileInputStream(file);
		HSSFWorkbook hsf = new HSSFWorkbook(fis);
		if(file.isFile() && file.exists()){
			System.out.println("1.xls存在，并打开");
		}else{
			System.out.println("1.xl打开失败！");		
		}

2、插入数据

                /*
		 * ID     NAME    部门
		 * t1     Tom      01
		 * t2     Jim      02
		 * t3     Lock     03
		 */
		//创建一个sheet工作区
		HSSFWorkbook hwb = new HSSFWorkbook();
		HSSFSheet hs = hwb.createSheet("部门");
		HSSFRow row;
		Map<String,Object[]> map = new TreeMap<String,Object[]>();
		map.put("1",new Object[]{"ID","Name","部门"});
		map.put("2",new Object[]{"t1","Tom","01"});
		map.put("3",new Object[]{"t2","Jim","02"});
		map.put("4",new Object[]{"t3","Lock","03"});
		Set<String> sets = map.keySet();
		int rowId = 0;
		for (String key : sets) {
			row = hs.createRow(rowId++);
			Object[] obj = map.get(key);
			int cellId = 0;
			for(Object o : obj){
				Cell cell = row.createCell(cellId++);
				cell.setCellValue((String)o);
			}
		}
		
		FileOutputStream out = new FileOutputStream(new File("1.xls"));
		hwb.write(out);
		out.close();
		System.out.println("successfully");
		

3、单元格设置

//单元格样式
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("cellStyle");
		HSSFRow row = sheet.createRow((short)2);
		
		row.setHeight((short)800);//设置高度    第2行  ，行高度800 ，下标1单元格添加“苏其昌”“黄志强” 合并区域1行4列
		HSSFCell cell = row.createCell(1);
		
		cell.setCellValue("昌哥");
		cell.setCellValue("强哥");
		sheet.addMergedRegion(new CellRangeAddress(
			1,//first row
			1,//last row
			1,//first column
			4 //last column
				));
		row = sheet.createRow(4);   //第4行，下标0的单元格，高度100，宽度0-8000，左对齐,居上
		cell = row.createCell(0);
		row.setHeight((short)100);
		sheet.setColumnWidth(0, 8000);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		cell.setCellValue("top--->left");
		cell.setCellStyle(style);
		//第6行  下标2的单元格  高度800，样式 居中、
		row = sheet.createRow(6);
		cell = row.createCell(1);
		row.setHeight((short)800);
		HSSFCellStyle style2 = wb.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cell.setCellStyle(style2);
		cell.setCellValue("101");
		
		row = sheet.createRow(10);
		row.setHeight((short)800);
		cell = row.createCell(2);
		HSSFCellStyle style3 = wb.createCellStyle();
		style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
		cell.setCellStyle(style3);
		cell.setCellValue("sanqing");
      row = sheet.createRow((short) 10);
	      row.setHeight((short) 800);
	      cell =  row.createCell( 1);
	      cell.setCellValue("BORDER");
	      HSSFCellStyle style5 = wb.createCellStyle();
	      style5.setBorderBottom(HSSFCellStyle.BORDER_THICK);
	      style5.setBottomBorderColor(
	      IndexedColors.BLUE.getIndex());
	      style5.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
	      style5.setLeftBorderColor( 
	      IndexedColors.GREEN.getIndex());
	      style5.setBorderRight(HSSFCellStyle.BORDER_HAIR);
	      style5.setRightBorderColor( 
	      IndexedColors.RED.getIndex());
	      style5.setBorderTop(HSSFCellStyle.BIG_SPOTS);
	      style5.setTopBorderColor( 
	      IndexedColors.CORAL.getIndex());
	      cell.setCellStyle(style5);
	      
	      FileOutputStream fos = new FileOutputStream("2.xls");
	      wb.write(fos);
	      fos.close();

4、字体设置

                HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("s1");
		HSSFRow row = sheet.createRow(10);
		row.setHeight((short)1500);
		HSSFCell cell = row.createCell(2);
		cell.setCellValue("河南大学");
		//设置字体样式
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.BRIGHT_GREEN.index);
		font.setFontName("IMPACT");
		font.setItalic(true);
		font.setFontHeight((short)500);
		
		//设置边框的样式
		HSSFCellStyle s = wb.createCellStyle();
		s.setBorderBottom((short)100);
		s.setBorderTop(HSSFCellStyle.VERTICAL_TOP);
		//旋转字体
		s.setRotation((short)90);
		//将字体样式加入，样式
		s.setFont(font);
		cell.setCellStyle(s);
		
		
		FileOutputStream fos = new FileOutputStream("3.xls");
		wb.write(fos);
		fos.close();

5、函数计算

                //1、创建一个HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		//2、创建一个工作薄HSSFSheet
		HSSFSheet sheet = wb.createSheet("我的工作薄");
		//3、创建行第5行
		HSSFRow row = sheet.createRow((short)5);
		//4、创建工作单元格
		HSSFCell cell = row.createCell(1);
		cell.setCellValue("A=");
		cell = row.createCell(2);
		cell.setCellValue(2);
		
		//创建行第6行
		row = sheet.createRow((short)6);
		cell = row.createCell(1);
		cell.setCellValue("C=");
		cell = row.createCell(2);
		cell.setCellValue(2);
		//创建行第七行
		row = sheet.createRow((short)7);
		cell = row.createCell(1);
		cell.setCellValue("Total=");
		cell = row.createCell(2);
		//创建求和函数
		cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
		cell.setCellFormula("SUM(c6:c7)");
		cell.setCellValue(2);
		//同理创建 power、max、pact、sqrt等
		
		row = sheet.createRow(10);
		cell = row.createCell(3);
		//设置字体
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.AQUA.index);
		font.setBoldweight((short)500);
		font.setFontHeight((short)500);
		font.setFontName("微软雅黑");
		//设置样式
		HSSFCellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setFillBackgroundColor(HSSFColor.YELLOW.index);
		style.setLocked(true);	
		cell.setCellStyle(style);
		
		cell.setCellValue("https://www.yiibai.com");
		
		OutputStream os = new FileOutputStream("5.xls");
		wb.write(os);
		os.close();
		

6、打印设置

                HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("101");
		wb.setPrintArea(0, 0,5,0,5);
		sheet.getPrintSetup().setPageStart(HSSFPrintSetup.A4_PAPERSIZE);
		//set display grid lines or not
		sheet.setDisplayGridlines(true);
		//set print grid lines or not
		sheet.setPrintGridlines(true);
		
		OutputStream os = new FileOutputStream("6.xls");
		wb.write(os);
		os.close();
		//打印预览查看

7、总结：

1、先创建一个HSSFWorkbook对象

2、创建Sheet工作表

3、创建Row

4、创建单元格Cell

5、创建字体设置HSSFFont对象

6、创建HSSFCellStyle对象

7、将font加入style

8、创建一个OutputStream流对象

9、写入表

10、关闭流对象