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

xiaowei8782088

我用的版本是 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();
}
}