POI学习笔记(二) 转载

7.  设置单元格的边框

 

Java代码

1. public void createBorder() throws Exception {   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.   
5.         // Create a row and put some cells in it. Rows are 0 based.   
6.         Row row = sheet.createRow(1);   
7.   
8.         // Create a cell and put a value in it.   
9.         Cell cell = row.createCell(1);   
10.         cell.setCellValue(4);   
11.   
12.         // Style the cell with borders all around.   
13.         CellStyle style = wb.createCellStyle();   
14.         style.setBorderBottom(CellStyle.BORDER_THIN);   
15.         style.setBottomBorderColor(IndexedColors.BLACK.getIndex());   
16.         style.setBorderLeft(CellStyle.BORDER_THIN);   
17.         style.setLeftBorderColor(IndexedColors.GREEN.getIndex());   
18.         style.setBorderRight(CellStyle.BORDER_THIN);   
19.         style.setRightBorderColor(IndexedColors.BLUE.getIndex());   
20.         style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);   
21.         style.setTopBorderColor(IndexedColors.BLACK.getIndex());   
22.         cell.setCellStyle(style);   
23.   
24.         // Write the output to a file   
25.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
26.         wb.write(fileOut);   
27.         fileOut.close();   
28.   
29.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">public void createBorder() throws Exception {   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.   
5.         // Create a row and put some cells in it. Rows are 0 based.   
6.         Row row = sheet.createRow(1);   
7.   
8.         // Create a cell and put a value in it.   
9.         Cell cell = row.createCell(1);   
10.         cell.setCellValue(4);   
11.   
12.         // Style the cell with borders all around.   
13.         CellStyle style = wb.createCellStyle();   
14.         style.setBorderBottom(CellStyle.BORDER_THIN);   
15.         style.setBottomBorderColor(IndexedColors.BLACK.getIndex());   
16.         style.setBorderLeft(CellStyle.BORDER_THIN);   
17.         style.setLeftBorderColor(IndexedColors.GREEN.getIndex());   
18.         style.setBorderRight(CellStyle.BORDER_THIN);   
19.         style.setRightBorderColor(IndexedColors.BLUE.getIndex());   
20.         style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);   
21.         style.setTopBorderColor(IndexedColors.BLACK.getIndex());   
22.         cell.setCellStyle(style);   
23.   
24.         // Write the output to a file   
25.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
26.         wb.write(fileOut);   
27.         fileOut.close();   
28.   
29.     }</SPAN>  

public void createBorder() throws Exception {
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("new sheet");

		// Create a row and put some cells in it. Rows are 0 based.
		Row row = sheet.createRow(1);

		// Create a cell and put a value in it.
		Cell cell = row.createCell(1);
		cell.setCellValue(4);

		// Style the cell with borders all around.
		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);

		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream("workbook.xls");
		wb.write(fileOut);
		fileOut.close();

	}

 

8. 迭代行和单元格

 

    有时需要迭代一个页中的所有行，或者一个行中所有的单元格。一个简单的方法是循环。

    幸运的是，poi知道我们所需。页可以通过sheet.rowIterator()迭代出所有的行，行可以通过row.cellIterator()迭代出所有的单元格。总之，Sheet和Row实现了java.lang.Iterable，如果你用的是jdk1.5以上的版本，你可以使用java高级for循环。

 

Java代码

1. Sheet sheet = wb.getSheetAt(0);   
2.     for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {   
3.         Row row = (Row)rit.next();   
4.         for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {   
5.             Cell cell = (Cell)cit.next();   
6.             // Do something here   
7.         }   
8.     }   
9.                     HSSFSheet sheet = wb.getSheetAt(0);   
10.     for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {   
11.         HSSFRow row = rit.next();   
12.         for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {   
13.             HSSFCell cell = cit.next();   
14.             // Do something here   
15.         }   
16.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">Sheet sheet = wb.getSheetAt(0);   
2.     for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {   
3.         Row row = (Row)rit.next();   
4.         for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {   
5.             Cell cell = (Cell)cit.next();   
6.             // Do something here   
7.         }   
8.     }   
9.                     HSSFSheet sheet = wb.getSheetAt(0);   
10.     for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {   
11.         HSSFRow row = rit.next();   
12.         for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {   
13.             HSSFCell cell = cit.next();   
14.             // Do something here   
15.         }   
16.     }   
17. </SPAN>  

Sheet sheet = wb.getSheetAt(0);
	for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {
		Row row = (Row)rit.next();
		for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {
			Cell cell = (Cell)cit.next();
			// Do something here
		}
	}
					HSSFSheet sheet = wb.getSheetAt(0);
	for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {
		HSSFRow row = rit.next();
		for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {
			HSSFCell cell = cit.next();
			// Do something here
		}
	}

 

java高级for循环迭代行和单元格

 

Java代码

1. Sheet sheet = wb.getSheetAt(0);   
2.     for (Row row : sheet) {   
3.         for (Cell cell : row) {   
4.             // Do something here   
5.         }   
6.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">Sheet sheet = wb.getSheetAt(0);   
2.     for (Row row : sheet) {   
3.         for (Cell cell : row) {   
4.             // Do something here   
5.         }   
6.     }   
7. </SPAN>  

Sheet sheet = wb.getSheetAt(0);
	for (Row row : sheet) {
		for (Cell cell : row) {
			// Do something here
		}
	}

 

9.  得到单元格的内容

 

     想得到单元格的内容之前，首先要知道单元格的类型，因此你要先判断单元格的类型之后选择合适的方法得到单元格的值。下面的代码，循环得到一个Sheet所有的单元格。

 

Java代码

1. public void getCellValue() throws Exception {   
2.         InputStream inp = new FileInputStream("D:\\hjn.xls");   
3.         HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));   
4.         Sheet sheet1 = wb.getSheetAt(0);   
5.         for (Row row : sheet1) {   
6.             for (Cell cell : row) {   
7.                 CellReference cellRef = new CellReference(row.getRowNum(), cell   
8.                         .getColumnIndex());   
9.                 System.out.print(cellRef.formatAsString());   
10.                 System.out.print(" - ");   
11.   
12.                 switch (cell.getCellType()) {   
13.                 case Cell.CELL_TYPE_STRING:   
14.                     System.out.println(cell.getRichStringCellValue()   
15.                             .getString());   
16.                     break;   
17.                 case Cell.CELL_TYPE_NUMERIC:   
18.                     if (DateUtil.isCellDateFormatted(cell)) {   
19.                         System.out.println(cell.getDateCellValue());   
20.                     } else {   
21.                         System.out.println(cell.getNumericCellValue());   
22.                     }   
23.                     break;   
24.                 case Cell.CELL_TYPE_BOOLEAN:   
25.                     System.out.println(cell.getBooleanCellValue());   
26.                     break;   
27.                 case Cell.CELL_TYPE_FORMULA:   
28.                     System.out.println(cell.getCellFormula());   
29.                     break;   
30.                 default:   
31.                     System.out.println();   
32.                 }   
33.             }   
34.         }   
35.   
36.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">public void getCellValue() throws Exception {   
2.         InputStream inp = new FileInputStream("D:\\hjn.xls");   
3.         HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));   
4.         Sheet sheet1 = wb.getSheetAt(0);   
5.         for (Row row : sheet1) {   
6.             for (Cell cell : row) {   
7.                 CellReference cellRef = new CellReference(row.getRowNum(), cell   
8.                         .getColumnIndex());   
9.                 System.out.print(cellRef.formatAsString());   
10.                 System.out.print(" - ");   
11.   
12.                 switch (cell.getCellType()) {   
13.                 case Cell.CELL_TYPE_STRING:   
14.                     System.out.println(cell.getRichStringCellValue()   
15.                             .getString());   
16.                     break;   
17.                 case Cell.CELL_TYPE_NUMERIC:   
18.                     if (DateUtil.isCellDateFormatted(cell)) {   
19.                         System.out.println(cell.getDateCellValue());   
20.                     } else {   
21.                         System.out.println(cell.getNumericCellValue());   
22.                     }   
23.                     break;   
24.                 case Cell.CELL_TYPE_BOOLEAN:   
25.                     System.out.println(cell.getBooleanCellValue());   
26.                     break;   
27.                 case Cell.CELL_TYPE_FORMULA:   
28.                     System.out.println(cell.getCellFormula());   
29.                     break;   
30.                 default:   
31.                     System.out.println();   
32.                 }   
33.             }   
34.         }   
35.   
36.     }</SPAN>  

public void getCellValue() throws Exception {
		InputStream inp = new FileInputStream("D:\\hjn.xls");
		HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		Sheet sheet1 = wb.getSheetAt(0);
		for (Row row : sheet1) {
			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 {
						System.out.println(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					System.out.println(cell.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					System.out.println(cell.getCellFormula());
					break;
				default:
					System.out.println();
				}
			}
		}

	}

 

10. 文本提取

 

poi的ExcelExtractor可以抽取Cell中的值。org.apache.poi.ss.extractor 为抽取类的接口，ExcelExtractor, XSSFExcelExtractor实现了该接口。

 

Java代码

1. InputStream inp = new FileInputStream("D:\\hjn.xls");   
2.         HSSFWorkbook  wb = new HSSFWorkbook(new POIFSFileSystem(inp));   
3.            
4.         ExcelExtractor extractor = new ExcelExtractor(wb);   
5.   
6.         extractor.setFormulasNotResults(true);   
7.         extractor.setIncludeSheetNames(true);   
8.         String text = extractor.getText();   
9.         System.out.println(text);  

Java代码

1. <SPAN style="FONT-SIZE: medium">InputStream inp = new FileInputStream("D:\\hjn.xls");   
2.         HSSFWorkbook  wb = new HSSFWorkbook(new POIFSFileSystem(inp));   
3.            
4.         ExcelExtractor extractor = new ExcelExtractor(wb);   
5.   
6.         extractor.setFormulasNotResults(true);   
7.         extractor.setIncludeSheetNames(true);   
8.         String text = extractor.getText();   
9.         System.out.println(text);</SPAN>  

InputStream inp = new FileInputStream("D:\\hjn.xls");
		HSSFWorkbook  wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		
		ExcelExtractor extractor = new ExcelExtractor(wb);

		extractor.setFormulasNotResults(true);
		extractor.setIncludeSheetNames(true);
		String text = extractor.getText();
		System.out.println(text);

 

 11. 填充和颜色

 

Java代码

1. public void fillAndColors() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.   
5.         // Create a row and put some cells in it. Rows are 0 based.   
6.         Row row = sheet.createRow((short) 1);   
7.   
8.         // Aqua background   
9.         CellStyle style = wb.createCellStyle();   
10.         style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());   
11.         style.setFillPattern(CellStyle.ALIGN_FILL);   
12.         Cell cell = row.createCell((short) 1);   
13.         cell.setCellValue("X");   
14.         cell.setCellStyle(style);   
15.   
16.         // Orange "foreground", foreground being the fill foreground not the font color.   
17.         style = wb.createCellStyle();   
18.         style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());   
19.         style.setFillPattern(CellStyle.SOLID_FOREGROUND);   
20.         cell = row.createCell((short) 2);   
21.         cell.setCellValue("X");   
22.         cell.setCellStyle(style);   
23.   
24.         // Write the output to a file   
25.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
26.         wb.write(fileOut);   
27.         fileOut.close();   
28.   
29.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">public void fillAndColors() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.   
5.         // Create a row and put some cells in it. Rows are 0 based.   
6.         Row row = sheet.createRow((short) 1);   
7.   
8.         // Aqua background   
9.         CellStyle style = wb.createCellStyle();   
10.         style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());   
11.         style.setFillPattern(CellStyle.ALIGN_FILL);   
12.         Cell cell = row.createCell((short) 1);   
13.         cell.setCellValue("X");   
14.         cell.setCellStyle(style);   
15.   
16.         // Orange "foreground", foreground being the fill foreground not the font color.   
17.         style = wb.createCellStyle();   
18.         style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());   
19.         style.setFillPattern(CellStyle.SOLID_FOREGROUND);   
20.         cell = row.createCell((short) 2);   
21.         cell.setCellValue("X");   
22.         cell.setCellStyle(style);   
23.   
24.         // Write the output to a file   
25.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
26.         wb.write(fileOut);   
27.         fileOut.close();   
28.   
29.     }</SPAN>  

public void fillAndColors() throws Exception{
		Workbook wb = new HSSFWorkbook();
	    Sheet sheet = wb.createSheet("new sheet");

	    // Create a row and put some cells in it. Rows are 0 based.
	    Row row = sheet.createRow((short) 1);

	    // Aqua background
	    CellStyle style = wb.createCellStyle();
	    style.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
	    style.setFillPattern(CellStyle.ALIGN_FILL);
	    Cell cell = row.createCell((short) 1);
	    cell.setCellValue("X");
	    cell.setCellStyle(style);

	    // Orange "foreground", foreground being the fill foreground not the font color.
	    style = wb.createCellStyle();
	    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
	    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    cell = row.createCell((short) 2);
	    cell.setCellValue("X");
	    cell.setCellStyle(style);

	    // Write the output to a file
	    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
	    wb.write(fileOut);
	    fileOut.close();

	}

 

12. 合并单元格

 

Java代码

1. public void mergingCell() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.         Row row = sheet.createRow((short) 1);   
5.         Cell cell = row.createCell((short) 1);   
6.         cell.setCellValue("This is a test of merging");   
7.   
8.         sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)   
9.                 4, // last row (0-based)   
10.                 1, // first column (0-based)   
11.                 6 // last column (0-based)   
12.                 ));   
13.   
14.         // Write the output to a file   
15.         FileOutputStream fileOut = new FileOutputStream("workbook.xls");   
16.         wb.write(fileOut);   
17.         fileOut.close();   
18.   
19.     }  

Java代码

1. <SPAN style="FONT-SIZE: medium">public void mergingCell() throws Exception{   
2.         Workbook wb = new HSSFWorkbook();   
3.         Sheet sheet = wb.createSheet("new sheet");   
4.         Row row = sheet.createRow((short) 1);   
5.         Cell cell = row.createCell((short) 1);   
6.         cell.setCellValue("This is a test of merging");   
7.   
8.         sheet.addMergedRegion(new CellRangeAddress(1, // first row (0-based)   
9.                 4, // last row (0-based)   
10.                 1, // first column (0-based)   
11.                 6 // last column (0-based)   
12.                 ));   
13.   
14.         /