POI简介
Apache POI是Apache软件基金会的开放源码函数库,POI提供API让Java程序对Microsoft Office格式的文档进行读写操作。
HSSF概況
HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取,写入,修改Excel文件。HSSF 为读取操作提供了两个API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。
POI EXCEL文档结构类
序号 | 类名 | 用途 |
---|---|---|
1 | HSSFWorkbook | excel文档对象 |
2 | HSSFSheet | excel的sheet |
3 | HSSFRow | excel的行 |
4 | HSSFCell | excel的单元格 |
5 | HSSFFont | excel字体 |
6 | HSSFName | 名称 |
7 | HSSFDataFormat | 日期格式 |
8 | HSSFHeader | sheet头 |
9 | HSSFFooter | sheet尾 |
10 | HSSFCellStyle | cell样式 |
11 | HSSFDateUtil | 日期 |
12 | HSSFPrintSetup | 打印 |
13 | HSSFErrorConstants | 错误信息表 |
导入jar包
要使用一个框架,首先要导入相应的jar包,poi的jar包去网上搜索就可以了,因为本人是使用Maven构建的项目,所以将pom文件中的引用贴出:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
EXCEL常用操作方法
1. 得到Excel常用对象
POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); //得到POI文件系統
HSSFWorkbook wb = new HSSFWorkbook(fs); //得到Excel工作簿对象
HSSFSheet sheet = wb.getSheetAt(0); //得到Excel工作表对象
HSSFRow row = sheet.getRow(i); //得到Excel工作表的行
HSSFCell cell = row.getCell((short) j); //得到Excel工作表指定行的单元格
cellStyle = cell.getCellStyle(); //得到单元格格式
2. 建立Excel常用对象
HSSFWorkbook wb = new HSSFWorkbook(); //创建Excel工作簿对象
HSSFSheet sheet = wb.createSheet("new sheet"); //创建Excel工作表对象
HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行
cellStyle = wb.createCellStyle(); //创建单元格样式
row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格
row.createCell((short)0).setCellValue(1); //设置Excel工作表的值
3. 设置sheet名称和单元格内容
wb.setSheetName(1, "第一張工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("單元格內容");
4. 获取数值
wb.getNumberOfSheets(); // 获取sheet的数目
HSSFSheet sheet = wb.getSheetAt(0); // 根据index取的sheet对象
int rowcount = sheet.getLastRowNum(); // 取的有效的行数
row.getLastCellNum(); // 取的一行的有效单元格个数
5. 单元格值类型读写
//设置单元格为STRING类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//读取是数值类型的单元格內容
cell.getNumericCellValue();
5. 设置列宽和行高
sheet.setColumnWidth((short)column,(short)width);
row.setHeight((short)height);
6. 添加区域,合并单元格
//合并从第rowFrom行columnFrom列
Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);
// 到rowTo行columnTo的区域
sheet.addMergedRegion(region);
//得到所有区域
sheet.getNumMergedRegions()
7. 保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
8. 根据单元格不同属性返回字符串数值
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;
}
9.常用单元格边框格式
HSSFCellStyle style = wb.createCellStyle();style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
10. 设置字体和内容位置
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);//单元格内容的旋转的角度
11. 插入图片
//先把读进来的图片放到一个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));
12. 调整工作表位置
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);
13. 设置打印区域
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");
14. 标注脚注
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
15. 在工作单中清空行数据,调整行位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
16. 将电子表格上的第6-11行上移5行,即移到顶部(第0-5行)
sheet.shiftRows(5, 10, -5);
17. 选中指定的工作表
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);
18. 工作表的放大缩小
HSSFSheet sheet1 = wb.createSheet("new sheet");
//放大倍数为50%
sheet1.setZoom(1,2);
19. 头注和脚注
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");
20. 自定义颜色
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);
21. 填充和颜色设置
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
22. 强行刷新单元格公式
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
private static void updateFormula(Workbook wb,Sheet s,int row){
Row r=s.getRow(row);
Cell c=null;
FormulaEcaluator eval=null;
if(wb instanceof HSSFWorkbook) {
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
}else if(wb instanceof XSSFWorkbook){
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
}
for(int i=r.getFirstCellNum();i<r.getLastCellNum();i++){
c=r.getCell(i);
if(c.getCellType()==Cell.CELL_TYPE_FORMULA){
eval.evaluateFormulaCell(c);
}
}
}
说明:FormulaEvaluator提供了evaluateFormulaCell(Cell cell)方法,计算公式保存结果,但不改变公式。而evaluateInCell(Cell cell) 方法是计算公式,并将原公式替换为计算结果,也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了静态方法evaluateAllFormulaCells(HSSFWorkbook wb) ,计算一个Excel文件的所有公式,用起来很方便。