POI基本操作Excel

      Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程式对Microsoft Office格式档案读和写的功能。

所需jar:poi-3.10.1-20140818.jar

1、创建Excel表

@Test
public void testCreateWorkbook(){
	//创建一个Excel表
	Workbook wb = new HSSFWorkbook();
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
		System.out.println("success");
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

 2、创建工作区间  

@Test
public void testCreateSheet(){
	Workbook wb = new HSSFWorkbook();
	//创建一个工作区间
	Sheet sheet = wb.createSheet("one sheet");
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
		System.out.println("success");
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

  3、创建单元格

@Test
public void testCreateCells(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet("one sheet");
	CreationHelper createHelper = wb.getCreationHelper();
	
	//创建第一行
	Row row = sheet.createRow(0);
	
	//创建单元格
	Cell cell = row.createCell(0);
	cell.setCellValue(1);
	
	//或者一下方式创建单元格
	row.createCell(1).setCellValue(1.2);
	row.createCell(2).setCellValue(createHelper.createRichTextString("This is a String"));
	row.createCell(3).setCellValue(true);
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
		System.out.println("success");
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

 4、创建其它格式单元格

@Test
public void testCreateOtherCells(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet("one sheet");
	
	//创建第一行
	Row row = sheet.createRow(0);
	
	//创建单元格
	row.createCell(0).setCellValue(1.1);
    row.createCell(1).setCellValue(new Date());
    row.createCell(2).setCellValue(Calendar.getInstance());
    row.createCell(3).setCellValue("a string");
    row.createCell(4).setCellValue(true);
    row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);
	
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
		System.out.println("success");
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

5、单元格对齐方式

@Test
public void testAlignmentOptions(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	
	//创建第一行
	Row row = sheet.createRow(2);
	row.setHeightInPoints(30);
	
	//创建单元格
	TestUtil.createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
	TestUtil.createCell(wb, row, 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
	TestUtil.createCell(wb, row, 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
	TestUtil.createCell(wb, row, 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
	TestUtil.createCell(wb, row, 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
	TestUtil.createCell(wb, row, 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
	TestUtil.createCell(wb, row, 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);
		
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

public static void createCell(Workbook wb, Row row, int column, short halign, short valign) {
    Cell cell = row.createCell(column);
    cell.setCellValue("Align It");
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(halign);
    cellStyle.setVerticalAlignment(valign);
    cell.setCellStyle(cellStyle);
}

 6、单元格加粗

@Test
public void testBorder(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	//创建行
	Row row = sheet.createRow(1);
	//创建单元格
	Cell cell = row.createCell(1);
	cell.setCellValue(4);
	//创建单元格样式
	CellStyle style = wb.createCellStyle();
	style.setBorderBottom(CellStyle.BORDER_THIN);	//边框类型
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());	//边框颜色
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

 7、单元格加颜色

@Test
public void testColor(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	
	//创建行
	Row row = sheet.createRow(1);
	
	//创建字体样式
	Font font = wb.createFont();
	font.setFontName("宋体");	//字体类型
	font.setFontHeightInPoints((short)12);	//字体大小
	font.setItalic(true);	//倾斜
	font.setBoldweight(Font.BOLDWEIGHT_BOLD);	//加粗
	font.setColor(HSSFColor.RED.index);		//红色

	//创建单元格样式
	CellStyle style = wb.createCellStyle();
	style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());	//背景颜色 
	style.setFillPattern(CellStyle.SOLID_FOREGROUND);	//全部填充  (填充类型)
	style.setFont(font);	//关联字体
	
	//创建单元格
	Cell cell = row.createCell(1);
	cell.setCellValue("呵呵");
	cell.setCellStyle(style);
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
	
}

 8、合并单元格

@Test
public void testMergingCell(){
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	
	//创建行
	Row row = sheet.createRow(1);
	
	Cell cell = row.createCell(1);
	cell.setCellValue("hello");
	
	//合并单元格（开始行数，结束行数,开始列数，结束列数）注意：都是从0开始的
	sheet.addMergedRegion(new CellRangeAddress(1,1,1,2));
	
	
	File file = new File("D:"+File.separator+"first.xls");
	FileOutputStream out = null;
	try {
		out = new FileOutputStream(file);
		wb.write(out);
		out.flush();
		out.close();
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}

 9、迭代读入Excel中的单元格内容(解决int与String的转换问题)

@Test
public void testIterateRowsAndCells(){
	Workbook wb = null;
	try {
		wb = new HSSFWorkbook(new FileInputStream(new File("F:\\test.xls")));
		Sheet sheet = wb.getSheetAt(0);
	    for (Row row : sheet) {
	      for (Cell cell : row) {
	    	  CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
	            System.out.print(cellRef.formatAsString());
	            System.out.print(" - ");

	            switch (cell.getCellType()) {
	                case Cell.CELL_TYPE_STRING:
	                    System.out.println(cell.getRichStringCellValue().getString());
	                    break;
	                case Cell.CELL_TYPE_NUMERIC:
	                    if (DateUtil.isCellDateFormatted(cell)) {
	                        System.out.println(cell.getDateCellValue());
	                    } else {
	                    	cell.setCellType(Cell.CELL_TYPE_STRING);
                            String temp = cell.getStringCellValue();
                            String str = "";
                            if (temp.indexOf(".") > -1) {
                                str = String.valueOf(new Double(temp))
                                        .trim();
                            } else {
                                str = temp.trim();
                            }
	                        System.out.println(str);
	                    }
	                    break;
	                case Cell.CELL_TYPE_BOOLEAN:
	                    System.out.println(cell.getBooleanCellValue());
	                    break;
	                case Cell.CELL_TYPE_FORMULA:
	                    System.out.println(cell.getCellFormula());
	                    break;
	                case Cell.CELL_TYPE_BLANK:
	                	System.out.println("\"\"");
	                	break;
	                default:
	                    System.out.println("未知类型");
	            }
	      }
	    }
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	}
}