|
1、遍歷workbook
代碼
-
- POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
- HSSFWorkbook wb = new HSSFWorkbook(fs);
- for (int i = 0; i < wb.getNumberOfSheets(); i++) {
- HSSFSheet sheet = wb.getSheetAt(i);
- for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i ++) {
- HSSFRow row = sheet.getRow(i);
- if (row != null) {
- 。。。操作}
- }
- }
-
- FileOutputStream fos = new FileOutputStream(objectPath);
-
- swb.write(fos);
- fos.close();
2、得到列和單元格
代碼
- HSSFRow row = sheet.getRow(i);
- HSSFCell cell = row.getCell((short) j);
3、設(shè)置sheet名稱和單元格內(nèi)容為中文
代碼
- wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16);
- cell.setEncoding((short) 1);
- cell.setCellValue("中文");
4、單元格內(nèi)容未公式或數(shù)值，可以這樣讀寫
代碼
- cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
- cell.getNumericCellValue()
5、設(shè)置列寬、行高
代碼
- sheet.setColumnWidth((short)column,(short)width);
- row.setHeight((short)height);
6、添加區(qū)域，合并單元格
代碼
- Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);
- sheet.addMergedRegion(region);
-
- sheet.getNumMergedRegions()
7、常用方法
根據(jù)單元格不同屬性返回字符串數(shù)值
代碼
- 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;
- }
8、常用單元格邊框格式
虛線HSSFCellStyle.BORDER_DOTTED
實線HSSFCellStyle.BORDER_THIN
代碼
- public static HSSFCellStyle getCellStyle(short type)
- {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFCellStyle style = wb.createCellStyle();
- style.setBorderBottom(type);
- style.setBorderLeft(type);
- style.setBorderRight(type);
- style.setBorderTop(type);
- return style;
- }
9、設(shè)置字體和內(nèi)容位置
代碼
- 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);
- cell.setCellStyle(style);
10、插入圖片
論壇里看到的
代碼
-
- ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
- BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
- ImageIO.write(bufferImg,"jpg",byteArrayOut);
-
- 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));
|