POI常用方法

点击(此处)折叠或打开

1. public static void main(String[] args) throws IOException {
   
2.         Workbook workbook = new HSSFWorkbook();
   
3.         /*==========获取常用对象======begin===========
   
4.         //读取文件
   
5.         POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("C:/a.xls"));
   
6.         //得到Excel工作簿对象
   
7.         Workbook workbook = new HSSFWorkbook(fs);
   
8.         //得到工作表对象
   
9.         Sheet sheet = workbook.getSheetAt(0);
   
10.         //得到工作表的行
    
11.         Row row = sheet.getRow(0);
    
12.         //得到指定行的单元格
    
13.         Cell cell = row.getCell(0);
    
14.         //得到单元格样式
    
15.         CellType cellType = CellType.forInt(cell.getCellType());
    
16.         =========获取常用对象=========end===========*/
    
17. 
    
18.         /*=============建立常用对象=======begin=======
    
19.         //创建工作簿
    
20.         Workbook workbook = new HSSFWorkbook();
    
21.         //创建工作表
    
22.         Sheet sheet= workbook.createSheet("new Sheet");
    
23.         //创建行
    
24.         Row row = sheet.getRow(0);
    
25.         //创建单元格样式
    
26.         CellStyle cellStyle = workbook.createCellStyle();
    
27.         //在指定行创建指定样式的单元格
    
28.         row.createCell(0).setCellStyle(cellStyle);
    
29.         //在指定行创建含有指定值的单元格
    
30.         row.createCell(0).setCellValue(1);
    
31.         ===============建立常用对象=======end========*/
    
32. 
    
33.         /*============设置sheet名称和单元格内容======begin======
    
34.         Workbook workbook = new HSSFWorkbook();
    
35.         workbook.setSheetName(1, "第一张工作表");
    
36.         Cell cell = workbook.createSheet().createRow(0).createCell(0);
    
37.         cell.setCellValue("单元格内容");
    
38.         ============设置sheet名称和单元格内容=======end======*/
    
39. 
    
40.         /*============取得sheet的数量========begin====
    
41.         Workbook workbook = new HSSFWorkbook();
    
42.         workbook.getNumberOfSheets();
    
43.         ============取得sheet的数量========end====*/
    
44. 
    
45.         //根据index取得sheet对象
    
46.         Sheet sheet = workbook.getSheetAt(0);
    
47.         //取得有效的行数
    
48.         int count = sheet.getLastRowNum();
    
49.         //取得一行有效的单元格个数
    
50.         Row row = sheet.getRow(0);
    
51.         row.getLastCellNum();
    
52.         //单元格类型的读写
    
53.         Cell cell = row.getCell(0);
    
54.         cell.setCellType(CellType.STRING);//设置单元格为String类型
    
55.         cell.getNumericCellValue();//读取数值类型的单元格内容
    
56. 
    
57.         //设置列宽 行高
    
58.         sheet.setColumnWidth(100, 100);
    
59.         row.setHeight((short)100);
    
60. 
    
61.         //添加区域 合并单元格
    
62.         sheet.addMergedRegion(new CellRangeAddress(0,3,0,0));
    
63.         Row row1 = sheet.createRow(0);
    
64.         row1.createCell(0).setCellValue("0");
    
65.         row1.createCell(1).setCellValue("1");
    
66.         row1.createCell(2).setCellValue("2");
    
67.         row1.createCell(3).setCellValue("3");
    
68. 
    
69.         //保存Excel文件
    
70.         String path = "C:/a.xls";
    
71.         FileOutputStream fileOutputStream = new FileOutputStream(path);
    
72.         workbook.write(fileOutputStream);
    
73. 
    
74.         //常用单元格边框格式
    
75.         CellStyle style = workbook.createCellStyle();
    
76.         style.setBorderTop(CellStyle.BORDER_DOTTED);//上边框
    
77.         style.setBorderBottom(CellStyle.BORDER_DOTTED);//下边框
    
78.         style.setBorderLeft(CellStyle.BORDER_THIN);//左边框
    
79.         style.setBorderRight(CellStyle.BORDER_DOTTED);//右边框
    
80. 
    
81.         //设置字体
    
82.         Font font = workbook.createFont();
    
83.         font.setFontHeightInPoints((short)11);//设置字体
    
84.         font.setBoldweight(Font.BOLDWEIGHT_NORMAL);//加粗
    
85. 
    
86.         //设置位置
    
87.         style.setFont(font);
    
88.         style.setAlignment(CellStyle.ALIGN_CENTER);//左右居中
    
89.         style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//上下居中
    
90.         style.setRotation((short)10);//单元格内容的旋转的角度
    
91. 
    
92.         //设置
    
93.         DataFormat dataFormat = workbook.createDataFormat();
    
94.         style.setDataFormat(dataFormat.getFormat("0.00%"));//设置单元格数据格式
    
95.         cell.setCellFormula("");//给单元格设置公式
    
96. 
    
97.         //插入图片
    
98.         ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
    
99.         BufferedImage bufferedImage = ImageIO.read(new File("a.jpg"));
    
100.         ImageIO.write(bufferedImage, "jpg", byteArrayOutputStream);
     
101. 
     
102.         //自定义颜色
     
103.         Font font1 = workbook.createFont();
     
104.         font1.setColor(HSSFColor.RED.index);
     
105.         style.setFont(font1);
     
106.         cell.setCellStyle(style);
     
107. 
     
108.     }

点击(此处)折叠或打开

1. /*=================根据单元不同属性返回字符串数值========begin============*/
   
2.     public String getCellStringValue(Cell cell){
   
3.         String cellValue = "";
   
4.         switch (CellType.forInt(cell.getCellType())){
   
5.             case _NONE:
   
6.                 break;
   
7.             case NUMERIC:
   
8.                 cellValue = String.valueOf(cell.getNumericCellValue());
   
9.                 break;
   
10.             case STRING:
    
11.                 cellValue = cell.getStringCellValue();
    
12.                 if (cellValue.trim().equals("") || cellValue.trim().length() <= 0){
    
13.                     cellValue = "";
    
14.                 }
    
15.                 break;
    
16.             case FORMULA:
    
17.                 cell.setCellType(CellType.NUMERIC);
    
18.                 cellValue = String.valueOf(cell.getNumericCellValue());
    
19.                 break;
    
20.             case BLANK:
    
21.                 cellValue = "";
    
22.                 break;
    
23.             case BOOLEAN:
    
24.                 break;
    
25.             case ERROR:
    
26.                 break;
    
27.         }
    
28.         return cellValue;
    
29.     }
    
30.     /*=================根据单元不同属性返回字符串数值========end============*/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30046312/viewspace-2150663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30046312/viewspace-2150663/