poi操作Excel相关对象属性介绍及中级应用

之前发布了一篇基于Servlet的poi实现Excel的读取,下载的文章,这里对poi中HSSF接口做详细的介绍;HSSF接口专门处理Excel的,在poi中应该算是比较成熟的一部分了;poi主要接口如下:

1.HSSF － 提供读写Microsoft Excel格式档案的功能。 

2.XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

3.HWPF － 提供读写Microsoft Word格式档案的功能。 4.HSLF － 提供读写Microsoft PowerPoint格式档案的功能。 5.HDGF － 提供读写Microsoft Visio格式档案的功能

反正就是当别人问到poi了就只知道处理excel就行;不说废话了,进入正题

HSSFWorkbook excell 文档对象介绍HSSFSheet excell的表单HSSFRow excell的行HSSFCell excell的格子单元HSSFFont excell字体HSSFName 名称HSSFDataFormat 日期格式HSSFHeader sheet头HSSFFooter sheet尾HSSFCellStyle cell样式HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表

下面是初级应用代码(Java工程),导包就不说了,上篇文章有下载地址链接

PoiTest.java package com.wf.util;import java.io.FileInputStream;import java.io.FileOutputStream;import org.apache.poi.hssf.usermodel.HSSFCell;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.poifs.filesystem.POIFSFileSystem;public class PoiTest {/** * 创建Excel*/public void createExcel(){try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("My First Sheet");//创建工作区,名称 HSSFRow row = sheet.createRow(0);//第一行 HSSFCell cell = row.createCell(0);//第一列 cell.setCellValue("Hello poi");//写入值 FileOutputStream fileOut = new FileOutputStream("d:\test.xls"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } }/** * 读取excel,注意至少要有一行一列,否则无值*/public void readExcel(){try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:\test.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);//第一个工作区 HSSFRow row = sheet.getRow(0);//第一行 HSSFCell cell = row.getCell(0);//第一列 String msg = cell.getStringCellValue();//值 System.out.println("cell'value="+msg); } catch (Exception e) { e.printStackTrace(); } }/** * 修改/写入值*/public void updateCellValue() {try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:\test.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);//第一个工作区 HSSFRow row = sheet.getRow(0); HSSFCell cell = row.getCell(0); cell.setCellValue("a test"); FileOutputStream fileOut = new FileOutputStream("d:\test.xls"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace(); } }}

下面是中级应用:

Test public void test1(){try {// load源文件 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 j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j ++) { HSSFRow row = sheet.getRow(i); if (row != null) { //....操作 } } } //目标文件 FileOutputStream fos = new FileOutputStream("objectPath"); //写文件 wb.write(fos); fos.close(); } catch (Exception e) { e.printStackTrace(); } }

相关操作:

More 1、得到列和单元格HSSFRow row = sheet.getRow(i); HSSFCell cell = row.getCell((short) j); 2、设置sheet名称和单元格内容为中文wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16); cell.setEncoding(1); cell.setCellValue("中文"); 3、单元格内容未公式或数值，可以这样读写cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.getNumericCellValue() 4、设置列宽、行高sheet.setColumnWidth(column,width); row.setHeight(height); 5、添加区域，合并单元格Region region = new Region(rowFrom,columnFrom,rowTo,columnTo); sheet.addMergedRegion(region); //得到所有区域 sheet.getNumMergedRegions() 6、根据单元格不同属性返回字符串数值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; } 7、常用单元格边框格式虚线HSSFCellStyle.BORDER_DOTTED实线HSSFCellStyle.BORDER_THINpublic static HSSFCellStyle getCellStyle( type){ HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(type);//下边框 style.setBorderLeft(type);//左边框 style.setBorderRight(type);//右边框 style.setBorderTop(type);//上边框 return style; } 8、设置字体和内容位置HSSFFont f = wb.createFont(); f.setFontHeightInPoints( 11);//字号 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style.setFont(f); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 style.setRotation( rotation);//单元格内容的旋转的角度 HSSFDataFormat df = wb.createDataFormat(); style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式 cell.setCellFormula(string);//给单元格设公式 style.setRotation( rotation);//单元格内容的旋转的角度 cell.setCellStyle(style); 9、插入图片//先把读进来的图片放到一个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, 0,0,10,10); patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG)); 10、设置列自动换行HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);sheet.setDefaultColumnStyle(0, cellStyle);//设置列的宽度sheet.setColumnWidth((short)0,(short)9000);sheet.setDefaultColumnStyle((short)0, cellStyle);//与sheet.setDefaultColumnWidth((short)70);冲突只会换行 不会设置列宽

单元格复制操作

RowCopy.java package com.wf.test;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;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.Region;import org.apache.poi.poifs.filesystem.POIFSFileSystem;/** * 将某SHEET页中的某几行复制到某SHEET页的某几行中。抱括被合并了的单元格。*/public class RowCopy {/** * @param args * @throws IOException * @throws FileNotFoundException*/ @SuppressWarnings("deprecation")public static void main(String[] args) {try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:\\exlsample.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs);// source为源sheet 页,target为目标sheet页 copyRows(wb, "source", "target", 5, 6, 20); FileOutputStream fileOut = new FileOutputStream("d:\\exlsample.xls"); wb.write(fileOut); fileOut.flush(); fileOut.close(); System.out.println("Operation finished"); } catch (Exception e) { e.printStackTrace(); } }/** * @param wb * HSSFWorkbook * @param pSourceSheetName * 源sheet页名称 * @param pTargetSheetName * 目标sheet页名称 * @param pStartRow * 源sheet页中的起始行 * @param pEndRow * 源sheet页中的结束行 * @param pPosition * 目标sheet页中的开始行*/public static void copyRows(HSSFWorkbook wb, String pSourceSheetName, String pTargetSheetName, int intStartRow, int intEndRow, int intPosition) {// EXECL中的行是从1开始的，而POI中是从0开始的，所以这里要减1. int pStartRow = intStartRow - 1;int pEndRow = intEndRow - 1;int pPosition = intPosition - 1; HSSFRow sourceRow = null; HSSFRow targetRow = null; HSSFCell sourceCell = null; HSSFCell targetCell = null; HSSFSheet sourceSheet = null; HSSFSheet targetSheet = null; Region region = null;int cType;int i;int j;int targetRowFrom;int targetRowTo;if ((pStartRow == -1) || (pEndRow == -1)) {return; } sourceSheet = wb.getSheet(pSourceSheetName); targetSheet = wb.getSheet(pTargetSheetName); System.out.println(sourceSheet.getNumMergedRegions());// 拷贝合并的单元格 for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) { region = sourceSheet.getMergedRegionAt(i);if ((region.getRowFrom() >= pStartRow) && (region.getRowTo() <= pEndRow)) { targetRowFrom = region.getRowFrom() - pStartRow + pPosition; targetRowTo = region.getRowTo() - pStartRow + pPosition; region.setRowFrom(targetRowFrom); region.setRowTo(targetRowTo); targetSheet.addMergedRegion(region); } }// 设置列宽 for (i = pStartRow; i <= pEndRow; i++) { sourceRow = sourceSheet.getRow(i);if (sourceRow != null) {for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) { targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j)); targetSheet.setColumnHidden(j, false); }break; } }// 拷贝行并填充数据 for (; i <= pEndRow; i++) { sourceRow = sourceSheet.getRow(i);if (sourceRow == null) {continue; } targetRow = targetSheet.createRow(i - pStartRow + pPosition); targetRow.setHeight(sourceRow.getHeight());for (j = sourceRow.getFirstCellNum(); j < sourceRow.getPhysicalNumberOfCells(); j++) { sourceCell = sourceRow.getCell(j);if (sourceCell == null) {continue; } targetCell = targetRow.createCell(j); targetCell.setCellStyle(sourceCell.getCellStyle()); cType = sourceCell.getCellType(); targetCell.setCellType(cType);switch (cType) {case HSSFCell.CELL_TYPE_BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_ERROR: targetCell.setCellErrorValue(sourceCell.getErrorCellValue()); System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());break;case HSSFCell.CELL_TYPE_FORMULA:// parseFormula这个函数的用途在后面说明 targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula())); System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());break;case HSSFCell.CELL_TYPE_NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());break;case HSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(sourceCell.getRichStringCellValue()); System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());break; } } } }/** * POI对Excel公式的支持是相当好的，但是有一个问题，如果公式里面的函数不带参数，比如now()或today()， * 那么你通过getCellFormula()取出来的值就是now(ATTR(semiVolatile))和today(ATTR(semiVolatile))， * 这样的值写入Excel是会出错的，这也是我上面copyRow的函数在写入公式前要调用parseFormula的原因， * parseFormula这个函数的功能很简单，就是把ATTR(semiVolatile)删掉。 * @param pPOIFormula * @return*/private static String parseFormula(String pPOIFormula) {final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$ StringBuffer result = null;int index; result = new StringBuffer(); index = pPOIFormula.indexOf(cstReplaceString);if (index >= 0) { result.append(pPOIFormula.substring(0, index)); result.append(pPOIFormula.substring(index + cstReplaceString.length())); } else { result.append(pPOIFormula); }return result.toString(); }}

 

转载于:https://www.cnblogs.com/wufengxyz/archive/2011/12/30/2307960.html

相关资源：数据结构—成绩单生成器