Apache POI SpreadSheet的一些简单应用（一）

我用的版本是 poi-3.5,您可以去官方上下载。
希望对大家能有些帮组。

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class XZou {
	
	/**
	 * 生成一个简单的excel，
	 * @throws Exception
	 */
	public static void writeSimpleExcel() throws Exception {

		HSSFWorkbook wb = new HSSFWorkbook();
		FileOutputStream out = new FileOutputStream("c:/aa.xls");
		wb.write(out);
		out.close();
	}
	/**
	 * 生成一个简单的excel,并且有两个工作表 
	 * @throws Exception
	 */
	public static void writeSheetExcel() throws Exception {

		HSSFWorkbook wb = new HSSFWorkbook();
		wb.createSheet("a sheet");
		wb.createSheet("b sheet");
		FileOutputStream out = new FileOutputStream("c:/bb.xls");

		wb.write(out);
		out.close();
	}
	
	/**
	 * 生成Excel文件和一个工作表，其中工作表中去设置一些内容
	 * @throws Exception
	 */
	public static void writeSheetCellExcel() throws Exception {

		HSSFWorkbook wb = new HSSFWorkbook();

		HSSFSheet sheet = wb.createSheet("a sheet");

		HSSFRow row = sheet.createRow(0);

		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue(new HSSFRichTextString("ID"));
		row.createCell((short) 1).setCellValue(new HSSFRichTextString("姓名"));
		row.createCell((short) 2).setCellValue(new HSSFRichTextString("年龄"));
		row.createCell((short) 3).setCellValue(new HSSFRichTextString("薪水"));
		row.createCell((short) 4).setCellValue(new HSSFRichTextString("生日"));
		row.createCell((short) 5).setCellValue(new HSSFRichTextString("是否"));
		row.createCell((short) 6).setCellValue(new HSSFRichTextString("工作时间"));

		row = sheet.createRow(1);
		row.createCell((short) 0).setCellValue(1);
		row.createCell((short) 1).setCellValue(new HSSFRichTextString("XZou"));
		row.createCell((short) 2).setCellValue(22);
		row.createCell((short) 3).setCellValue(100.25);
		row.createCell((short) 4).setCellValue(new java.util.Date());
		row.createCell((short) 5).setCellValue(true);
		// row.createCell((short)6).setCellValue(new HSSFRichTextString("生日"));
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		HSSFCell cellr = row.createCell((short) 6);
		cellr.setCellValue(new Date());
		cellr.setCellStyle(cellStyle);

		row.createCell((short) 7).setCellType(HSSFCell.CELL_TYPE_ERROR);

		HSSFCell cella = row.createCell((short) 8);
		cellStyle = wb.createCellStyle();//利用workbook生成一个样式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//样式基中
		cella.setCellValue(new HSSFRichTextString("中"));//cella单元格内容设置

		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.GREEN.index);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setRightBorderColor(HSSFColor.BLUE.index);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		cella.setCellStyle(cellStyle);

		cella = row.createCell((short) 9);
		HSSFCellStyle hlink_style = wb.createCellStyle();
		HSSFFont hlink_font = wb.createFont();
		hlink_font.setUnderline(HSSFFont.U_SINGLE);
		hlink_font.setColor(HSSFColor.BLUE.index);
		hlink_style.setFont(hlink_font);

		cella.setCellValue(new HSSFRichTextString("URL LINK"));

		FileOutputStream out = new FileOutputStream("c:/bb.xls");

		wb.write(out);
		out.close();
	}
	
	/**
	 * 读取
	 * @throws Exception
	 */
	public static void getSheet() throws Exception {
		InputStream in = new FileInputStream("c:/bb.xls");
		HSSFWorkbook wb = new HSSFWorkbook(in);
		HSSFSheet sheet = wb.getSheetAt(0);
		for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
			HSSFRow row = (HSSFRow) rit.next();
			for (Iterator cit = row.cellIterator(); cit.hasNext();) {
				HSSFCell cell = (HSSFCell) cit.next();
				if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
					System.out.print(cell.getRichStringCellValue() + ",");
				}
				if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
					System.out.print(cell.getErrorCellValue() + ",");
				}
				if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						System.out.print(HSSFDateUtil.getJavaDate(
								cell.getNumericCellValue()).toLocaleString()
								+ ",");
					}
					System.out.print(cell.getNumericCellValue() + ",");
				}
				if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
					System.out.print(cell.getBooleanCellValue() + ",");
				}
			}
			System.out.println();
		}
		/*
		 * for (HSSFRow row : sheet.rowIterator()) { for (HSSFCell cell :
		 * row.cellIterator()) { // Do something here } }
		 */
	}
	/**
	 * 生成Excel,并且格式化单元格的数字和时间
	 * @throws Exception
	 */
	public static void writeFormat() throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("format sheet");
		HSSFCellStyle style;
		HSSFDataFormat format = wb.createDataFormat();
		HSSFRow row;
		HSSFCell cell;
		short rowNum = 0;
		short colNum = 0;

		row = sheet.createRow(rowNum++);
		cell = row.createCell(colNum);
		cell.setCellValue(11111.25);
		style = wb.createCellStyle();
		style.setDataFormat(format.getFormat("0.0"));
		cell.setCellStyle(style);

		row = sheet.createRow(rowNum++);
		cell = row.createCell(colNum);
		cell.setCellValue(11111.25);
		style = wb.createCellStyle();
		style.setDataFormat(format.getFormat("#,##0.0000"));
		cell.setCellStyle(style);

		FileOutputStream fileOut = new FileOutputStream("c:/cc.xls");
		wb.write(fileOut);
		fileOut.close();

	}

	public static void main(String[] args)  throws Exception{
		
		// writeSimpleExcel();
		//writeSheetExcel();
		writeSheetCellExcel();
		//getSheet();
		//writeFormat();
		
	}

}