POI简介
- Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF概况
- HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。
POI EXCEL文档结构类
- HSSFWorkbook excel文档对象
- HSSFSheet excel的sheet HSSFRow excel的行
- HSSFCell excel的单元格 HSSFFont excel字体
- HSSFName 名称 HSSFDataFormat 日期格式
- HSSFHeader sheet头
- HSSFFooter sheet尾
- HSSFCellStyle cell样式
- HSSFDateUtil 日期
- HSSFPrintSetup 打印
- HSSFErrorConstants 错误信息表
EXCEL常用操作方法
- 建立Excel常用对象
POIFSFileSystem fs = newPOIFSFileSystem(new FileInputStream("d:/test.xls"));
//得到Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到Excel工作表对象
HSSFSheet sheet = wb.getSheetAt(0);
//得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
//得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell((short) j);
//得到单元格样式
cellStyle = cell.getCellStyle();
- 建立Excel常用对象
//创建Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建Excel工作表对象
HSSFSheet sheet = wb.createSheet("new sheet");
//创建Excel工作表的行
HSSFRow row = sheet.createRow((short)0);
//创建单元格样式
cellStyle = wb.createCellStyle();
//创建Excel工作表指定行的单元格
row.createCell((short)0).setCellStyle(cellStyle);
//设置Excel工作表的值
row.createCell((short)0).setCellValue(1);
- 设置sheet名称和单元格内容
wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("单元格内容");
- 取得sheet的数目
wb.getNumberOfSheets();
- 根据index取得sheet对象
HSSFSheet sheet = wb.getSheetAt(0);
- 取得有效的行数
int rowcount = sheet.getLastRowNum();
- 取得一行的有效单元格个数
row.getLastCellNum();
- 单元格值类型读写
//设置单元格为STRING类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//读取为数值类型的单元格内容
cell.getNumericCellValue();
- 设置列宽、行高
sheet.setColumnWidth((short)column,(short)width);
row.setHeight((short)height);
- 添加区域，合并单元格
//合并从第rowFrom行columnFrom列
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo
(short)columnTo);
//到rowTo行columnTo的区域
sheet.addMergedRegion(region);
//得到所有区域
sheet.getNumMergedRegions()
- 保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
- 根据单元格不同属性返回字符串数值
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://字符串类型
cellValue = cell.getStringCellValue();
if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
cellValue = "";
break;
case HSSFCell.CELL_TYPE_NUMERIC://数值类型
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}
- 保存Excel文件
HSSFCellStyle style = wb.createCellStyle();
//下边框
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);
//左边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);
//右边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//上边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- 设置字体和内容位置
HSSFFont f = wb.createFont();
//字号
f.setFontHeightInPoints((short) 11);
//加粗
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style.setFont(f);
//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//上下居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//单元格内容的旋转的角度
style.setRotation(short rotation);
HSSFDataFormat df = wb.createDataFormat();
//设置单元格数据格式
style1.setDataFormat(df.getFormat("0.00%"));
//给单元格设公式
cell.setCellFormula(string);
//单元格内容的旋转的角度
style.setRotation(short rotation);
- 插入图片
//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//读进一个excel模版
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
- 调整工作表位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);
- 设置打印区域
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");
- 标注脚注
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
- 在工作单中清空行数据，调整行位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
sheet.shiftRows(5, 10, -5);
- 选中指定的工作表
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);
- 工作表的放大缩小
HSSFSheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(1,2); // 50 percent magnification
- 头注和脚注
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
- 自定义颜色
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);
cell.setCellStyle(style);
小结
- 设置不显示excel网格线
sheet.setDisplayGridlines(false);
其中sheet是Sheet对象
- 设置excel单元格中的内容换行
cellStyle.setWrapText(true);
其中cellStyle是WorkBook创建的CellStyle对象,然后将cellStyle设置到要换行的Cell对象,最后在要换行的对象(一般为字符串)加入"/r/n"。如
topTile.append("/r/n" +"cellContent");
- 单元格的合并
sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));
本示例为合并4行2列
- 设置页眉和页脚的页数
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
HSSFFooter footer = (HSSFFooter )sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
- 使得一个Sheet适合一页
sheet.setAutobreaks(true);
- 设置放大属性
sheet.setZoom(3,4);
Zoom被明确为一个分数，例如下面的75%使用3作为分子，4作为分母
- 设置打印
HSSFPrintSetup print = (HSSFPrintSetup) sheet.getPrintSetup();
print.setLandscape(true);//设置横向打印
print.setScale((short) 70);//设置打印缩放70%
print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//设置为A4纸张
print.setLeftToRight(true);//設置打印顺序先行后列,默认为先列行
print.setFitHeight((short) 10);设置缩放调整为10页高
print.setFitWidth((short) 10);设置缩放调整为宽高
sheet.setAutobreaks(false);
if (i != 0 && i % 30 == 0) {
sheet.setRowBreak(i);//设置每30行分页打印
}
- 反复的行和列
HSSFWorkbook wb = new HSSFWorkbook();
wb.setRepeatingRowsAndColumns(0, 0, 12, 1, 6);
//设置1到12列,行1到6每一页重复打印
- 调整单元格宽度
sheet.setAutobreaks(true);
sheet.setColumnWidth((short)i,colsWidth[i]);
//设定单元格长度
sheet.autoSizeColumn((short) i);
//自动根据长度调整单元格长度
