POI 操作excel_wd总结
POI操作Excel常用方法总结
POI操作Excel常用方法总结POI操作Excel常用方法总结一、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常用操作方法1、得到Excel常用对象view plaincopy to clipboardprint?1.POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));2.//得到Excel工作簿对象3.HSSFWorkbook wb = new HSSFWorkbook(fs);4.//得到Excel工作表对象5.HSSFSheet sheet = wb.getSheetAt(0);6.//得到Excel工作表的行7.HSSFRow row = sheet.getRow(i);8.//得到Excel工作表指定行的单元格9.HSSFCell cell = row.getCell((short) j);10.cellStyle = cell.getCellStyle();//得到单元格样式1.HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象2.HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象3.HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行4.cellStyle = wb.createCellStyle();//创建单元格样式5.row.createCell((short)0).setCellStyle(cellStyle); //创建Excel 工作表指定行的单元格6.row.createCell((short)0).setCellValue(1); //设置Excel工作表的值3、设置sheet名称和单元格内容view plaincopy to clipboardprint?1.wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);2.cell.setEncoding((short) 1);3.cell.setCellValue("单元格内容");1.wb.getNumberOfSheets()1.HSSFSheet sheet = wb.getSheetAt(0);1.int rowcount = sheet.getLastRowNum();view plaincopy to clipboardprint?1.row.getLastCellNum();1.cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型2.cell.getNumericCellValue();//读取为数值类型的单元格内容1.sheet.setColumnWidth((short)column,(short)width);2.row.setHeight((short)height);1.Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowT o2.,(short)columnT o);//合并从第rowFrom行columnFrom列3.sheet.addMergedRegion(region);// 到rowTo行columnT o 的区域4.//得到所有区域5.sheet.getNumMergedRegions()1.FileOutputStream fileOut = new FileOutputStream(path);2.wb.write(fileOut);1.public String getCellStringValue(HSSFCell cell) {2. String cellValue = "";3. switch (cell.getCellType()) {4. case HSSFCell.CELL_TYPE_STRING://字符串类型5. cellValue = cell.getStringCellValue();6. if(cellValue.trim().equals("")||cellValue.trim().length()<=0)7. cellValue=" ";8. break;9. case HSSFCell.CELL_TYPE_NUMERIC: //数值类型10. cellValue = String.valueOf(cell.getNumericCellValue());11. break;12. case HSSFCell.CELL_TYPE_FORMULA: //公式13. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);14. cellValue = String.valueOf(cell.getNumericCellValue());15. break;16. case HSSFCell.CELL_TYPE_BLANK:17. cellValue=" ";18. break;19. case HSSFCell.CELL_TYPE_BOOLEAN:20. break;21. case HSSFCell.CELL_TYPE_ERROR:22. break;23. default:24. break;25. }26. return cellValue;27. }13、常用单元格边框格式view plaincopy to clipboardprint?1.HSSFCellStyle style = wb.createCellStyle();2.style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框3.style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框4.style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框5.style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框1.HSSFFont f = wb.createFont();2. f.setFontHeightInPoints((short) 11);//字号3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗4.style.setFont(f);5.style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中6.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTE R);//上下居中7.style.setRotation(short rotation);//单元格内容的旋转的角度8.HSSFDataFormat df = wb.createDataFormat();9.style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式10.cell.setCellFormula(string);//给单元格设公式11.style.setRotation(short rotation);//单元格内容的旋转的角度1.//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray2. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();3. BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));4. ImageIO.write(bufferImg,"jpg",byteArrayOut);5.//读进一个excel模版6.FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");7.fs = new POIFSFileSystem(fos);8.//创建一个工作薄9.HSSFWorkbook wb = new HSSFWorkbook(fs);10.HSSFSheet sheet = wb.getSheetAt(0);11.HSSFPatriarch patriarch = sheet.createDrawingPatriarch();12.HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);13.patriarch.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));1.HSSFWorkbook wb = new HSSFWorkbook();2.HSSFSheet sheet = wb.createSheet("format sheet");3.HSSFPrintSetup ps = sheet.getPrintSetup();4.sheet.setAutobreaks(true);5.ps.setFitHeight((short)1);6.ps.setFitWidth((short)1);1.HSSFSheet sheet = wb.createSheet("Sheet1");2.wb.setPrintArea(0, "$A$1:$C$2");1.HSSFSheet sheet = wb.createSheet("format sheet");2.HSSFFooter footer = sheet.getFooter()3.footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );1.HSSFWorkbook wb = new HSSFWorkbook();2.HSSFSheet sheet = wb.createSheet("row sheet");3.// Create various cells and rows for spreadsheet.4.// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 -5)5.sheet.shiftRows(5, 10, -5);20、选中指定的工作表view plaincopy to clipboardprint?1.HSSFSheet sheet = wb.createSheet("row sheet");2.heet.setSelected(true);1.HSSFSheet sheet1 = wb.createSheet("new sheet");2.sheet1.setZoom(1,2); // 50 percent magnification1.HSSFSheet sheet = wb.createSheet("new sheet");2.HSSFHeader header = sheet.getHeader();3.header.setCenter("Center Header");4.header.setLeft("Left Header");5.header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +6.HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");1.HSSFCellStyle style = wb.createCellStyle();2.style.setFillForegroundColor(HSSFColor.LIME.index);3.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);4.HSSFFont font = wb.createFont();5.font.setColor(HSSFColor.RED.index);6.style.setFont(font);7.cell.setCellStyle(style);1.HSSFCellStyle style = wb.createCellStyle();2.style.setFillBackgroundColor(HSSFColor.AQUA.index);3.style.setFillPattern(HSSFCellStyle.BIG_SPOTS);4.HSSFCell cell = row.createCell((short) 1);5.cell.setCellValue("X");6.style = wb.createCellStyle();7.style.setFillForegroundColor(HSSFColor.ORANGE.index);8.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);9.cell.setCellStyle(style);1.HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);2.private static void updateFormula(Workbook wb,Sheet s,int row){3. Row r=s.getRow(row);4. Cell c=null;5. FormulaEcaluator eval=null;6. if(wb instanceof HSSFWorkbook)7. eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);8. else if(wb instanceof XSSFWorkbook)9. eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);10. for(int i=r.getFirstCellNum();i<r.getlastcellnum();i++){< p="">11. c=r.getCell(i);12. if(c.getCellType()==Cell.CELL_TYPE_FORMULA)13. eval.evaluateFormulaCell(c);14. }15. }laCells(HSSFWorkbook wb) ，计算一个Excel文件的所有公式，用起来很方便。
POI操作excel大全
POI操作excel大全POI操作excel大全第一讲：基本的Excel读写本文主要演示一下POI的基本操作，例如怎样读取和创建一个具体的Excel文件。
按照惯例，拿HelloWorld说事儿。
说明：本文主要内容皆包含于官方帮助手册，之所以要拿出来，出于两个原因，手册是英文的+手册是对应2.5.1的。
核心代码如下，注释部分为讲解。
这里只挑干的讲，完整的代码请参考(下载代码)。
//创建一个空白的WorkBook HSSFWorkbook wb = new HSSFWorkbook(); //基于上面的WorkBook创建属于此WorkBook 的Sheet， //3.0.1版在使用全角Sheet名的时候不必再setEncdoing了，个人感觉方便了许多。
H SSFSheet st = wb.createSheet(“测试页“); //创建属于上面Sheet的Row，参数0可以是0～65535之间的任何一个，//注意，尽管参数是Int类型，但是Excel最多支持65536行HSSFRow row = st.createRow(0); //创建属于上面Row的Cell，参数0可以是0～255之间的任何一个， //同样，是因为Excel最大支持的列数为256列 HSSFCell cell = row.createCell((short)0); //设置此单元格的格式为文本，此句可以省略，Excel会自动识别。
//其他还有几种常用的格式，请参考本文底部的补充部分。
c ell.setCellType(HSSFCell.CELL_TYPE_STRING); //此处是3.0.1版的改进之处，上一版可以直接setCellValue(“Hello, World!“)， //但是在3.0.1里，被deprecated了。
c ell.setCellValue(new HSSFRichTextString(“Hello, World!“)); //创建一个文件输出流，指定到C盘根目录下（C盘都有吧？）//xls是Excel97-2003的标准扩展名，xx是xlsx，目前的POI能直接生产的还是xls格式，//如果此处把扩展名改成xlsx，在用Excelxx打开此文件时会报错。
apachePOI之excel操作
apachePOI之excel操作Apache POI是的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能1.下载开发包解压：2.引⼊POI的依赖：<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.11</version></dependency>3.POI使⽤：public String exportXls() throws IOException{List<Subarea> list = subareaService.findAll();HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("分区数据");HSSFRow row0 = sheet.createRow(0);row0.createCell(0).setCellValue("分区编号");row0.createCell(1).setCellValue("开始编号");row0.createCell(2).setCellValue("结束编号");row0.createCell(3).setCellValue("位置信息");row0.createCell(4).setCellValue("省市区");for (Subarea subarea : list) {HSSFRow row = sheet.createRow(sheet.getLastRowNum()+1);row.createCell(0).setCellValue(subarea.getId());row.createCell(1).setCellValue(subarea.getStartnum());row.createCell(2).setCellValue(subarea.getEndnum());row.createCell(3).setCellValue(subarea.getPosition());row.createCell(4).setCellValue(subarea.getRegion().getName());}String filename = "分区数据.xls";String mimeType = ServletActionContext.getServletContext().getMimeType(filename);ServletActionContext.getResponse().setContentType(mimeType);String agent = ServletActionContext.getRequest().getHeader("User-Agent");filename = FileUtils.encodeDownloadFilename(filename, agent);ServletActionContext.getResponse().setHeader("content-disposition","attchment;filename="+filename);ServletOutputStream out = ServletActionContext.getResponse().getOutputStream();workbook.write(out);return NONE;}@Testpublic void test1() throws FileNotFoundException, IOException{String filePath = "C:\\Users\\zhaoqx\\Desktop\\资料\\区域导⼊测试数据.xls";//包装⼀个Excel⽂件对象HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));//读取⽂件中第⼀个Sheet标签页HSSFSheet hssfSheet = workbook.getSheetAt(0);//遍历标签页中所有的⾏for (Row row : hssfSheet) {System.out.println();for (Cell cell : row) {String value = cell.getStringCellValue();System.out.print(value + " ");}}}。
POI设置EXCEL单元格格式的操作小结
POI中可能会用到一些需要设置EXCEL单元格格式的操作小结：先获取工作薄对象:Java代码HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet();HSSFCellStyle setBorder = wb.createCellStyle();HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet();HSSFCellStyle setBorder = wb.createCellStyle();一、设置背景色：Java代码setBorder.setFillForegroundColor((short) 13);// 设置背景色setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); setBorder.setFillForegroundColor((short) 13);// 设置背景色setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);二、设置边框:Java代码setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框三、设置居中:Java代码setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中四、设置字体:Java代码HSSFFont font = wb.createFont();font.setFontName("黑体");font.setFontHeightInPoints((short) 16);//设置字体大小HSSFFont font2 = wb.createFont();font2.setFontName("仿宋_GB2312");font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示font2.setFontHeightInPoints((short) 12);setBorder.setFont(font);//选择需要用到的字体格式HSSFFont font = wb.createFont();font.setFontName("黑体");font.setFontHeightInPoints((short) 16);//设置字体大小HSSFFont font2 = wb.createFont();font2.setFontName("仿宋_GB2312");font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示font2.setFontHeightInPoints((short) 12);setBorder.setFont(font);//选择需要用到的字体格式五、设置列宽:Java代码sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值六、设置自动换行:Java代码setBorder.setWrapText(true);//设置自动换行setBorder.setWrapText(true);//设置自动换行七、合并单元格:Java代码Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1：行号参数2：起始列号参数3：行号参数4：终止列号sheet.addMergedRegion(region1);Region region1 = new Region(0, (short) 0, 0, (short) 6);//参数1：行号参数2：起始列号参数3：行号参数4：终止列号sheet.addMergedRegion(region1);附一个完整的例子：Java代码package .util;import ermodel.HSSFCellStyle;import ermodel.HSSFFont;import ermodel.HSSFRichTextString;import ermodel.HSSFWorkbook;import ermodel.HSSFSheet;import ermodel.HSSFRow;import ermodel.HSSFCell;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.Region;import ermodel.CellStyle;import java.io.FileOutputStream;import javax.servlet.http.HttpServlet;public class CreateXL extends HttpServlet {/** Excel 文件要存放的位置，假定在D盘下*/public static String outputFile = "c:\\test.xls";private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) { HSSFCell cell = row.createCell(col);// cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellV alue(val);HSSFCellStyle cellstyle = wb.createCellStyle();cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);cell.setCellStyle(cellstyle);}public static void main(String argv[]) {try {// 创建新的Excel 工作簿HSSFWorkbook workbook = new HSSFWorkbook();// 设置字体HSSFFont font = workbook.createFont();// font.setColor(HSSFFont.COLOR_RED);font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontHeightInPoints((short) 14);// HSSFFont font2 = workbook.createFont();// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// font.setFontHeightInPoints((short)14);// 设置样式HSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// HSSFCellStyle cellStyle2= workbook.createCellStyle();// cellStyle.setFont(font2);// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 在Excel工作簿中建一工作表，其名为缺省值// 如要新建一名为"月报表"的工作表，其语句为：HSSFSheet sheet = workbook.createSheet("月报表");CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,11);sheet.addMergedRegion(cellRangeAddress);//第一行// 在索引0的位置创建行（最顶端的行）HSSFRow row = sheet.createRow(0);// 在索引0的位置创建单元格（左上端）HSSFCell cell = row.createCell(0);// 定义单元格为字符串类型cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellStyle(cellStyle);// 在单元格中输入一些内容cell.setCellV alue(new HSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));//第二行cellRangeAddress = new CellRangeAddress(1, 1, 3, 6);sheet.addMergedRegion(cellRangeAddress);row = sheet.createRow(1);HSSFCell datecell = row.createCell(3);datecell.setCellType(HSSFCell.CELL_TYPE_STRING); datecell.setCellStyle(cellStyle);datecell.setCellV alue("时间间隔xxxxx");cellRangeAddress = new CellRangeAddress(1, 1, 9, 10);sheet.addMergedRegion(cellRangeAddress);row.createCell(9).setCellV alue("单位：元");//第三行row=sheet.createRow(2);row.createCell(0).setCellV alue("一、");row.createCell(1).setCellV alue("基本资料");//第4行row=sheet.createRow(3);row.createCell(1).setCellV alue("小区名称："); cellRangeAddress=new CellRangeAddress(3,3,2,11); sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellV alue("xxxxx");//第5行row=sheet.createRow(4);row.createCell(1).setCellV alue("座落地点："); cellRangeAddress=new CellRangeAddress(4,4,2,11); sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellV alue("xxxxx");//第6行row=sheet.createRow(5);row.createCell(1).setCellV alue("建成年月："); cellRangeAddress=new CellRangeAddress(5,5,2,4); sheet.addMergedRegion(cellRangeAddress);row.createCell(2).setCellV alue("年月日：xxxxx"); row.createCell(5).setCellV alue("联系人"); cellRangeAddress=new CellRangeAddress(5,5,6,8); sheet.addMergedRegion(cellRangeAddress);row.createCell(6).setCellV alue("XXX");row.createCell(9).setCellV alue("电话"); cellRangeAddress=new CellRangeAddress(5,5,10,11); sheet.addMergedRegion(cellRangeAddress);row.createCell(10).setCellV alue("XXX");//第7行row=sheet.createRow(6);row.createCell(1).setCellV alue("住户：");row.createCell(2).setCellV alue("(XX)");row.createCell(3).setCellV alue("(户)"); cellRangeAddress=new CellRangeAddress(6,6,4,5); sheet.addMergedRegion(cellRangeAddress);row.createCell(4).setCellV alue("共计（）"); row.createCell(6).setCellV alue("幢"); cellRangeAddress=new CellRangeAddress(6,6,7,8); sheet.addMergedRegion(cellRangeAddress);row.createCell(7).setCellV alue("发卡张数"); cellRangeAddress=new CellRangeAddress(6,6,9,10); sheet.addMergedRegion(cellRangeAddress);row.createCell(9).setCellV alue("xxxx");//第9行row=sheet.createRow(8);row.createCell(0).setCellV alue("二、"); cellRangeAddress=new CellRangeAddress(8,8,1,2); sheet.addMergedRegion(cellRangeAddress);row.createCell(1).setCellV alue("维修用材料台账"); row.createCell(6).setCellV alue("三、"); cellRangeAddress=new CellRangeAddress(8,8,7,9); sheet.addMergedRegion(cellRangeAddress);row.createCell(7).setCellV alue("维修工时记录");//第10行row=sheet.createRow(9);row.createCell(0).setCellV alue("日期");row.createCell(1).setCellV alue("维修事项");row.createCell(2).setCellV alue("材料清单");row.createCell(3).setCellV alue("数量");row.createCell(4).setCellV alue("单价");row.createCell(5).setCellV alue("材料金额");row.createCell(7).setCellV alue("日期");row.createCell(8).setCellV alue("技工");row.createCell(9).setCellV alue("工时数");row.createCell(10).setCellV alue("单价");row.createCell(11).setCellV alue("工时金额");//填充数据for (int i = 0; i < 10; i++) {row=sheet.createRow(9+i+1);row.createCell(0).setCellV alue("日期");row.createCell(1).setCellV alue("维修事项");row.createCell(2).setCellV alue("材料清单");row.createCell(3).setCellV alue("数量");row.createCell(4).setCellV alue("单价");row.createCell(5).setCellV alue("材料金额");row.createCell(7).setCellV alue("日期");row.createCell(8).setCellV alue("技工");row.createCell(9).setCellV alue("工时数");row.createCell(10).setCellV alue("单价");row.createCell(11).setCellV alue("工时金额");}//第n+10行row=sheet.createRow(9+10+1);//cellRangeAddress=new CellRangeAddress(19,19,0,4);//sheet.addMergedRegion(cellRangeAddress);row.createCell(0).setCellV alue("累计:");row.createCell(1).setCellV alue("xxx");row.createCell(7).setCellV alue("累计:");row.createCell(8).setCellV alue("xxx");// 新建一输出文件流FileOutputStream fOut = new FileOutputStream(outputFile); // 把相应的Excel 工作簿存盘workbook.write(fOut);fOut.flush();// 操作结束，关闭文件fOut.close();System.out.println("文件生成...");} catch (Exception e) {System.out.println("已运行xlCreate() : " + e);}}}。
POI操作Excel
POI操作ExcelPOI操作Excel----------（转载）java当初把核⼼处理设成Unicode，带来的好处是另代码适应了多语⾔环境。
然⽽由于⽼外的英语只有26个字母，有些情况下，⼀些程序员⽤8 位的byte处理，⼀不⼩⼼就去掉了CJK的⾼位。
或者是由于习惯在程序中采⽤硬编码，还有多种原因，使得许多java应⽤在CJK的处理上很烦恼。
还好在POI HSSF中考虑到这个问题，可以设置encoding为双字节。
POI可以到/doc/c8e5a30127d3240c8447eff0.html 下载到。
编译好的jar主要有这样4个：poi包,poi Browser包,poi hdf包,poi hssf例程包。
实际运⾏时，需要有poi包就可以了。
如果⽤Jakarta ant编译和运⾏，下载apache Jakarta POI的release中的src 包，它⾥⾯已经为你⽣成好了build⽂件了。
只要运⾏ant就可以了(ant 的安装和使⽤在此不说了)。
如果是⽤Jbuilder 运⾏，请在新建的项⽬中加⼊poi 包。
以Jbuilder6为例，选择Tools菜单项的config libraries...选项，新建⼀个lib。
在弹出的菜单中选择poi包，如这个jakarta-poi-1.5.1-final- 20020820.jar，把poi添加到jbuilder中。
然后，右键点击你的项⽬，在project 的properties菜单中path的 required Libraries中，点add，添加刚才加⼊到jbuilder中的poi到你现在的项⽬中。
如果你仅仅是为了熟悉POI hssf的使⽤，可以直接看POI的samples包中的源代码，并且运⾏它。
hssf的各种对象都有例程的介绍。
hssf提供的例程在/doc/c8e5a30127d3240c8447eff0.html ermodel.examples包中，共有14个，⽣成的⽬标xls 都是workbook.xls。
JavaPOI操作word文档内容、表格
JavaPOI操作word⽂档内容、表格⼀、pom<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.0.0</version></dependency>⼆、直接上代码word模板中${content} 注意我只有在.docx⽤XWPFDocument才有效2.1/*** 获取document**/XWPFDocument document = null;try {document = new XWPFDocument(inputStream);} catch (IOException ioException) {ioException.printStackTrace();}/*** 替换段落⾥⾯的变量** @param doc 要替换的⽂档* @param params 参数*/private void replaceInPara(XWPFDocument doc, Map<String, String> params) {for (XWPFParagraph para : doc.getParagraphs()) {replaceInPara(para, params);}}/*** 替换段落⾥⾯的变量** @param para 要替换的段落* @param params 参数*/private void replaceInPara(XWPFParagraph para, Map<String, String> params) {List<XWPFRun> runs;Matcher matcher;replaceText(para);//如果para拆分的不对，则⽤这个⽅法修改成正确的if (matcher(para.getParagraphText()).find()) {runs = para.getRuns();for (int i = 0; i < runs.size(); i++) {XWPFRun run = runs.get(i);String runText = run.toString();matcher = matcher(runText);if (matcher.find()) {while ((matcher = matcher(runText)).find()) {runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));}//直接调⽤XWPFRun的setText()⽅法设置⽂本时，在底层会重新创建⼀个XWPFRun，把⽂本附加在当前⽂本后⾯， para.removeRun(i);para.insertNewRun(i).setText(runText);}}}}/*** 替换⽂本内容* @param para* @return*/private List<XWPFRun> replaceText(XWPFParagraph para) {List<XWPFRun> runs = para.getRuns();String str = "";boolean flag = false;for (int i = 0; i < runs.size(); i++) {XWPFRun run = runs.get(i);String runText = run.toString();if (flag || runText.equals("${")) {str = str + runText;flag = true;para.removeRun(i);if (runText.equals("}")) {flag = false;para.insertNewRun(i).setText(str);str = "";}i--;}}return runs;}2.22.2.1XWPFTable table = document.getTableArray(0);//获取当前表格XWPFTableRow twoRow = table.getRow(2);//获取某⼀⾏XWPFTableRow nextRow = table.insertNewTableRow(3);//插⼊⼀⾏XWPFTableCell firstRowCellOne = firstRow.getCell(0);firstRowCellOne.removeParagraph(0);//删除默认段落，要不然表格内第⼀条为空⾏XWPFParagraph pIO2 =firstRowCellOne.addParagraph();XWPFRun rIO2 = pIO2.createRun();rIO2.setFontFamily("宋体");//字体rIO2.setFontSize(8);//字体⼤⼩rIO2.setBold(true);//是否加粗rIO2.setColor("FF0000");//字体颜⾊rIO2.setText("这是写⼊的内容");//rIO2.addBreak(BreakType.TEXT_WRAPPING);//软换⾏，亲测有效/*** 复制单元格和样式** @param targetRow 要复制的⾏* @param sourceRow 被复制的⾏*/public void createCellsAndCopyStyles(XWPFTableRow targetRow, XWPFTableRow sourceRow) {targetRow.getCtRow().setTrPr(sourceRow.getCtRow().getTrPr());List<XWPFTableCell> tableCells = sourceRow.getTableCells();if (CollectionUtils.isEmpty(tableCells)) {return;}for (XWPFTableCell sourceCell : tableCells) {XWPFTableCell newCell = targetRow.addNewTableCell();newCell.getCTTc().setTcPr(sourceCell.getCTTc().getTcPr());List sourceParagraphs = sourceCell.getParagraphs();if (CollectionUtils.isEmpty(sourceParagraphs)) {continue;}XWPFParagraph sourceParagraph = (XWPFParagraph) sourceParagraphs.get(0);List targetParagraphs = newCell.getParagraphs();if (CollectionUtils.isEmpty(targetParagraphs)) {XWPFParagraph p = newCell.addParagraph();p.getCTP().setPPr(sourceParagraph.getCTP().getPPr());XWPFRun run = p.getRuns().isEmpty() ? p.createRun() : p.getRuns().get(0);run.setFontFamily(sourceParagraph.getRuns().get(0).getFontFamily());} else {XWPFParagraph p = (XWPFParagraph) targetParagraphs.get(0);p.getCTP().setPPr(sourceParagraph.getCTP().getPPr());XWPFRun run = p.getRuns().isEmpty() ? p.createRun() : p.getRuns().get(0);if (sourceParagraph.getRuns().size() > 0) {run.setFontFamily(sourceParagraph.getRuns().get(0).getFontFamily());}}}}#### 2.2.3/*** 合并单元格** @param table 表格对象* @param beginRowIndex 开始⾏索引* @param endRowIndex 结束⾏索引* @param colIndex 合并列索引*/public void mergeCell(XWPFTable table, int beginRowIndex, int endRowIndex, int colIndex) { if (beginRowIndex == endRowIndex || beginRowIndex > endRowIndex) {return;}//合并⾏单元格的第⼀个单元格CTVMerge startMerge = CTVMerge.Factory.newInstance();startMerge.setVal(STMerge.RESTART);//合并⾏单元格的第⼀个单元格之后的单元格CTVMerge endMerge = CTVMerge.Factory.newInstance();endMerge.setVal(STMerge.CONTINUE);table.getRow(beginRowIndex).getCell(colIndex).getCTTc().getTcPr().setVMerge(startMerge); for (int i = beginRowIndex + 1; i <= endRowIndex; i++) {table.getRow(i).getCell(colIndex).getCTTc().getTcPr().setVMerge(endMerge);}}/*** insertRow 在word表格中指定位置插⼊⼀⾏，并将某⼀⾏的样式复制到新增⾏* @param copyrowIndex 需要复制的⾏位置* @param newrowIndex 需要新增⼀⾏的位置* */public static void insertRow(XWPFTable table, int copyrowIndex, int newrowIndex) {// 在表格中指定的位置新增⼀⾏XWPFTableRow targetRow = table.insertNewTableRow(newrowIndex);// 获取需要复制⾏对象XWPFTableRow copyRow = table.getRow(copyrowIndex);//复制⾏对象targetRow.getCtRow().setTrPr(copyRow.getCtRow().getTrPr());//或许需要复制的⾏的列List<XWPFTableCell> copyCells = copyRow.getTableCells();//复制列对象XWPFTableCell targetCell = null;for (int i = 0; i < copyCells.size(); i++) {XWPFTableCell copyCell = copyCells.get(i);targetCell = targetRow.addNewTableCell();targetCell.getCTTc().setTcPr(copyCell.getCTTc().getTcPr());if (copyCell.getParagraphs() != null && copyCell.getParagraphs().size() > 0) {targetCell.getParagraphs().get(0).getCTP().setPPr(copyCell.getParagraphs().get(0).getCTP().getPPr()); if (copyCell.getParagraphs().get(0).getRuns() != null&& copyCell.getParagraphs().get(0).getRuns().size() > 0) {XWPFRun cellR = targetCell.getParagraphs().get(0).createRun();cellR.setBold(copyCell.getParagraphs().get(0).getRuns().get(0).isBold());}}}}/*** 正则匹配字符串** @param str* @return*/private Matcher matcher(String str) {Pattern pattern = pile("\\$\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);Matcher matcher = pattern.matcher(str);return matcher;}。
最直观的poi的使用帮助（告诉你怎么使用poi的官网），操作word，excel，ppt
最直观的poi的使⽤帮助（告诉你怎么使⽤poi的官⽹），操作
word，excel，ppt
最直观的poi的使⽤帮助（告诉你怎么使⽤poi的官⽹），poi操作word，excel，ppt
写在最前⾯
其实poi的官⽹上⾯有poi的各种类和接⼝的使⽤说明，还有⾮常详细的样例，所以照着这些样例来进⾏poi的开发会变得很容易。
就是excel的帮助⽂档，每⼀个链接都是⼀个实例。
说明
官⽹的Conponents APIs就告诉我们操作office的每⼀种⽂件需要的组件。
⽐如说word就需要HWPF（⽤来操作word2003,后缀为.doc）和XWPF（⽤来操作word2007,后缀为.docx）
Conponent Map⾥⾯就有每⼀个版块所属的jar包，⽐如HWPF组件的jar包就是poi-scratchpad。
我们来看下载好的（如何下载后⾯会说）poi-3.17包，根据上⾯的所属关系，我们可以轻松知道HWPF组件要⽤到poi-3.17.jar包，⽽XHPF 要⽤到poi-ooxml-3.17.jar包。
下载
现在最新的poi的版本为poi-bin-3.17-20170915，我们就⽤这个说明
把我们要的poi-bin-3.17-20170915.zip jar包下载下来就好。
实例
下⾯我们来到官⽹找到操作word的两个组件HWPF和XWPF的操作样例
我们以HWPF的Quick Guide页⾯来做说明
从这个页⾯的最右下⾓箭头指向的位置点下去，就会得到样例界⾯
为了⽅便读者，直接把这个链接的传送门弄下来：
到这个界⾯就可以⾃⼰去找⾃⼰喜欢的实例了。
POI操作Excel常用方法总结
POI操作Excel常用方法总结一、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常用操作方法1、得到Excel常用对象view plaincopy to clipboardprint?1.POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));2.//得到Excel工作簿对象3.HSSFWorkbook wb = new HSSFWorkbook(fs);4.//得到Excel工作表对象5.HSSFSheet sheet = wb.getSheetAt(0);6.//得到Excel工作表的行7.HSSFRow row = sheet.getRow(i);8.//得到Excel工作表指定行的单元格9.HSSFCell cell = row.getCell((short) j);10.cellStyle = cell.getCellStyle();//得到单元格样式1.HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象2.HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象3.HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行4.cellStyle = wb.createCellStyle();//创建单元格样式5.row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格6.row.createCell((short)0).setCellValue(1); //设置Excel工作表的值3、设置sheet名称和单元格内容view plaincopy to clipboardprint?1.wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);2.cell.setEncoding((short) 1);3.cell.setCellValue("单元格内容");1.wb.getNumberOfSheets()1.HSSFSheet sheet = wb.getSheetAt(0);1.int rowcount = sheet.getLastRowNum();view plaincopy to clipboardprint?1.row.getLastCellNum();1.cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型2.cell.getNumericCellValue();//读取为数值类型的单元格内容1.sheet.setColumnWidth((short)column,(short)width);2.row.setHeight((short)height);1.Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo2.,(short)columnTo);//合并从第rowFrom行columnFrom列3.sheet.addMergedRegion(region);// 到rowTo行columnTo的区域4.//得到所有区域5.sheet.getNumMergedRegions()1.FileOutputStream fileOut = new FileOutputStream(path);2.wb.write(fileOut);1.public String getCellStringValue(HSSFCell cell) {2. String cellValue = "";3. switch (cell.getCellType()) {4. case HSSFCell.CELL_TYPE_STRING://字符串类型5. cellValue = cell.getStringCellValue();6. if(cellValue.trim().equals("")||cellValue.trim().length()<=0)7. cellValue=" ";8. break;9. case HSSFCell.CELL_TYPE_NUMERIC: //数值类型10. cellValue = String.valueOf(cell.getNumericCellValue());11. break;12. case HSSFCell.CELL_TYPE_FORMULA: //公式13. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);14. cellValue = String.valueOf(cell.getNumericCellValue());15. break;16. case HSSFCell.CELL_TYPE_BLANK:17. cellValue=" ";18. break;19. case HSSFCell.CELL_TYPE_BOOLEAN:20. break;21. case HSSFCell.CELL_TYPE_ERROR:22. break;23. default:24. break;25. }26. return cellValue;27. }13、常用单元格边框格式view plaincopy to clipboardprint?1.HSSFCellStyle style = wb.createCellStyle();2.style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框3.style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框4.style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框5.style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框1.HSSFFont f = wb.createFont();2. f.setFontHeightInPoints((short) 11);//字号3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗4.style.setFont(f);5.style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中6.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中7.style.setRotation(short rotation);//单元格内容的旋转的角度8.HSSFDataFormat df = wb.createDataFormat();9.style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式10.cell.setCellFormula(string);//给单元格设公式11.style.setRotation(short rotation);//单元格内容的旋转的角度1.//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray2. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();3. BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));4. ImageIO.write(bufferImg,"jpg",byteArrayOut);5.//读进一个excel模版6.FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");7.fs = new POIFSFileSystem(fos);8.//创建一个工作薄9.HSSFWorkbook wb = new HSSFWorkbook(fs);10.HSSFSheet sheet = wb.getSheetAt(0);11.HSSFPatriarch patriarch = sheet.createDrawingPatriarch();12.HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);13.patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));1.HSSFWorkbook wb = new HSSFWorkbook();2.HSSFSheet sheet = wb.createSheet("format sheet");3.HSSFPrintSetup ps = sheet.getPrintSetup();4.sheet.setAutobreaks(true);5.ps.setFitHeight((short)1);6.ps.setFitWidth((short)1);1.HSSFSheet sheet = wb.createSheet("Sheet1");2.wb.setPrintArea(0, "$A$1:$C$2");1.HSSFSheet sheet = wb.createSheet("format sheet");2.HSSFFooter footer = sheet.getFooter()3.footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );1.HSSFWorkbook wb = new HSSFWorkbook();2.HSSFSheet sheet = wb.createSheet("row sheet");3.// Create various cells and rows for spreadsheet.4.// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)5.sheet.shiftRows(5, 10, -5);20、选中指定的工作表view plaincopy to clipboardprint?1.HSSFSheet sheet = wb.createSheet("row sheet");2.heet.setSelected(true);1.HSSFSheet sheet1 = wb.createSheet("new sheet");2.sheet1.setZoom(1,2); // 50 percent magnification1.HSSFSheet sheet = wb.createSheet("new sheet");2.HSSFHeader header = sheet.getHeader();3.header.setCenter("Center Header");4.header.setLeft("Left Header");5.header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +6.HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");1.HSSFCellStyle style = wb.createCellStyle();2.style.setFillForegroundColor(HSSFColor.LIME.index);3.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);4.HSSFFont font = wb.createFont();5.font.setColor(HSSFColor.RED.index);6.style.setFont(font);7.cell.setCellStyle(style);1.HSSFCellStyle style = wb.createCellStyle();2.style.setFillBackgroundColor(HSSFColor.AQUA.index);3.style.setFillPattern(HSSFCellStyle.BIG_SPOTS);4.HSSFCell cell = row.createCell((short) 1);5.cell.setCellValue("X");6.style = wb.createCellStyle();7.style.setFillForegroundColor(HSSFColor.ORANGE.index);8.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);9.cell.setCellStyle(style);1.HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);2.private static void updateFormula(Workbook wb,Sheet s,int row){3. Row r=s.getRow(row);4. Cell c=null;5. FormulaEcaluator eval=null;6. if(wb instanceof HSSFWorkbook)7. eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);8. else if(wb instanceof XSSFWorkbook)9. eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);10. for(int i=r.getFirstCellNum();i<r.getLastCellNum();i++){11. c=r.getCell(i);12. if(c.getCellType()==Cell.CELL_TYPE_FORMULA)13. eval.evaluateFormulaCell(c);14. }15. }laCells(HSSFWorkbook wb) ，计算一个Excel文件的所有公式，用起来很方便。
poi操作word 2007 常用方法总结
poi操作word 2007 常用方法总结import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.math.BigInteger;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import ng3.StringUtils;import org.apache.poi.POIXMLDocument;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy;import ermodel.BodyElementType;import ermodel.Borders;import ermodel.BreakType;import ermodel.Document;import ermodel.IBodyElement;import ermodel.ParagraphAlignment;import ermodel.TextAlignment;import ermodel.VerticalAlign;import ermodel.XWPFDocument;import ermodel.XWPFParagraph;import ermodel.XWPFRelation;import ermodel.XWPFRun;import ermodel.XWPFTable;import ermodel.XWPFTableCell;import ermodel.XWPFTableRow;import org.apache.xmlbeans.XmlException;import org.apache.xmlbeans.XmlToken;import org.apache.xmlbeans.impl.xb.xmlschema.SpaceAttribute;import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps; import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D;import org.openxmlformats.schemas.drawingml.x2006.wordprocessingDrawing.CTInline; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTBackground; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTBookmark;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTColor;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTDecimalNumber; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTEm;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTFldChar; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTFonts;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTHMerge;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTHeight;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTHighlight; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTHpsMeasure; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTHyperlink; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTInd;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTJc;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTMarkupRange; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTOnOff;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTP;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPageBorders; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTPageMar;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTParaRPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTR;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTRPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTRow;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSectPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTShd;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSignedTwipsMeasure; import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSpacing;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblBorders;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblGrid;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblGridCol;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTText;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTextScale;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTUnderline;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTVMerge;import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTVerticalJc; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STBorder;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STEm;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STFldCharType; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STHdrFtr;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STHeightRule; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STHighlightColor; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STJc;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STLineSpacingRule; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STOnOff;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STPageOrientation; import org.openxmlformats.schemas.wordprocessingml.x2006.main.STShd;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STUnderline;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STVerticalAlignRun;import org.openxmlformats.schemas.wordprocessingml.x2006.main.STVerticalJc;public class POI_Word2007_工具类_S3_Test {// 替换复制表格段落/*------------------------------------Word 插入书签--------------------------------------------------- *//*** @Description: 添加书签*/public void addParagraphContentBookmarkBasicStyle(XWPFParagraph p,String content, BigInteger markId, String bookMarkName,boolean isInsert, boolean isNewLine, String fontFamily,String fontSize, String colorVal, boolean isBlod,boolean isUnderLine, String underLineColor,STUnderline.Enum underStyle, boolean isItalic, boolean isStrike) {CTBookmark bookStart = p.getCTP().addNewBookmarkStart();bookStart.setId(markId);bookStart.setName(bookMarkName);XWPFRun pRun = getOrAddParagraphFirstRun(p, isInsert, isNewLine);setParagraphRunFontInfo(p, pRun, content, fontFamily, fontSize);setParagraphTextStyleInfo(p, pRun, colorVal, isBlod, isUnderLine,underLineColor, underStyle, isItalic, isStrike, false, false, false, false, false, false, false, null, false, null, false,null, null, null, 0, 0, 0);CTMarkupRange bookEnd = p.getCTP().addNewBookmarkEnd();bookEnd.setId(markId);}/*** @Description: 添加书签*/public void addParagraphContentBookmark(XWPFParagraph p, String content, BigInteger markId, String bookMarkName, boolean isInsert, boolean isNewLine, String fontFamily, String fontSize,String colorVal, boolean isBlod, boolean isUnderLine,String underLineColor, STUnderline.Enum underStyle,boolean isItalic, boolean isStrike, boolean isDStrike,boolean isShadow, boolean isVanish, boolean isEmboss,boolean isImprint, boolean isOutline, boolean isEm,STEm.Enum emType, boolean isHightLight,STHighlightColor.Enum hightStyle, boolean isShd,STShd.Enum shdStyle, String shdColor, VerticalAlign verticalAlign, int position, int spacingValue, int indent) {CTBookmark bookStart = p.getCTP().addNewBookmarkStart();bookStart.setId(markId);bookStart.setName(bookMarkName);XWPFRun pRun = getOrAddParagraphFirstRun(p, isInsert, isNewLine);setParagraphRunFontInfo(p, pRun, content, fontFamily, fontSize);setParagraphTextStyleInfo(p, pRun, colorVal, isBlod, isUnderLine,underLineColor, underStyle, isItalic, isStrike, isDStrike,isShadow, isVanish, isEmboss, isImprint, isOutline, isEm,emType, isHightLight, hightStyle, isShd, shdStyle, shdColor,verticalAlign, position, spacingValue, indent);CTMarkupRange bookEnd = p.getCTP().addNewBookmarkEnd();bookEnd.setId(markId);}/*------------------------------------Word 插入超链接--------------------------------------------------- *//*** @Description: 默认的超链接样式*/public void addParagraphTextHyperlinkBasicStyle(XWPFParagraph paragraph,String url, String text, String fontFamily, String fontSize,String colorVal, boolean isBlod, boolean isItalic, boolean isStrike) {addParagraphTextHyperlink(paragraph, url, text, fontFamily, fontSize,colorVal, isBlod, true, "0000FF", STUnderline.SINGLE, isItalic,isStrike, false, false, false, false, false, false, false,null, false, null, false, null, null, null, 0, 0, 0);}/*** @Description: 设置超链接样式*/public void addParagraphTextHyperlink(XWPFParagraph paragraph, String url, String text, String fontFamily, String fontSize, String colorVal, boolean isBlod, boolean isUnderLine, String underLineColor,STUnderline.Enum underStyle, boolean isItalic, boolean isStrike, boolean isDStrike, boolean isShadow, boolean isVanish,boolean isEmboss, boolean isImprint, boolean isOutline,boolean isEm, STEm.Enum emType, boolean isHightLight,STHighlightColor.Enum hightStyle, boolean isShd,STShd.Enum shdStyle, String shdColor,STVerticalAlignRun.Enum verticalAlign, int position,int spacingValue, int indent) {// Add the link as External relationshipString id = paragraph.getDocument().getPackagePart().addExternalRelationship(url,XWPFRelation.HYPERLINK.getRelation()).getId();// Append the link and bind it to the relationshipCTHyperlink cLink = paragraph.getCTP().addNewHyperlink(); cLink.setId(id);// Create the linked textCTText ctText = CTText.Factory.newInstance();ctText.setStringValue(text);CTR ctr = CTR.Factory.newInstance();CTRPr rpr = ctr.addNewRPr();if (StringUtils.isNotBlank(fontFamily)) {// 设置字体CTFonts fonts = rpr.isSetRFonts() ? rpr.getRFonts() : rpr .addNewRFonts();fonts.setAscii(fontFamily);fonts.setEastAsia(fontFamily);fonts.setHAnsi(fontFamily);}if (StringUtils.isNotBlank(fontSize)) {// 设置字体大小CTHpsMeasure sz = rpr.isSetSz() ? rpr.getSz() : rpr.addNewSz(); sz.setVal(new BigInteger(fontSize));CTHpsMeasure szCs = rpr.isSetSzCs() ? rpr.getSzCs() : rpr.addNewSzCs();szCs.setVal(new BigInteger(fontSize));}// 设置超链接样式// 字体颜色if (StringUtils.isNotBlank(colorVal)) {CTColor color = CTColor.Factory.newInstance();color.setVal(colorVal);rpr.setColor(color);}// 加粗if (isBlod) {CTOnOff bCtOnOff = rpr.addNewB();bCtOnOff.setVal(STOnOff.TRUE);}// 下划线if (isUnderLine) {CTUnderline udLine = rpr.addNewU();udLine.setVal(underStyle);udLine.setColor(underLineColor);}// 倾斜if (isItalic) {CTOnOff iCtOnOff = rpr.addNewI();iCtOnOff.setVal(STOnOff.TRUE);}// 删除线if (isStrike) {CTOnOff sCtOnOff = rpr.addNewStrike(); sCtOnOff.setVal(STOnOff.TRUE);}// 双删除线if (isDStrike) {CTOnOff dsCtOnOff = rpr.addNewDstrike();dsCtOnOff.setVal(STOnOff.TRUE);}// 阴影if (isShadow) {CTOnOff shadowCtOnOff = rpr.addNewShadow(); shadowCtOnOff.setVal(STOnOff.TRUE);}// 隐藏if (isVanish) {CTOnOff vanishCtOnOff = rpr.addNewVanish(); vanishCtOnOff.setVal(STOnOff.TRUE);}// 阳文if (isEmboss) {CTOnOff embossCtOnOff = rpr.addNewEmboss(); embossCtOnOff.setVal(STOnOff.TRUE);}// 阴文if (isImprint) {CTOnOff isImprintCtOnOff = rpr.addNewImprint(); isImprintCtOnOff.setVal(STOnOff.TRUE);}// 空心if (isOutline) {CTOnOff isOutlineCtOnOff = rpr.addNewOutline(); isOutlineCtOnOff.setVal(STOnOff.TRUE);}// 着重号if (isEm) {CTEm em = rpr.addNewEm();em.setVal(emType);}// 突出显示文本if (isHightLight) {if (hightStyle != null) {CTHighlight hightLight = rpr.addNewHighlight();hightLight.setVal(hightStyle);}}if (isShd) {// 设置底纹CTShd shd = rpr.addNewShd();if (shdStyle != null) {shd.setVal(shdStyle);}if (shdColor != null) {shd.setColor(shdColor);}}// 上标下标if (verticalAlign != null) {rpr.addNewVertAlign().setVal(verticalAlign);}// 设置文本位置rpr.addNewPosition().setVal(new BigInteger(String.valueOf(position)));if (spacingValue != 0) {// 设置字符间距信息CTSignedTwipsMeasure ctSTwipsMeasure = rpr.addNewSpacing();ctSTwipsMeasure.setVal(new BigInteger(String.valueOf(spacingValue)));}// 设置字符间距缩进if (indent > 0) {CTTextScale paramCTTextScale = rpr.addNewW();paramCTTextScale.setVal(indent);}ctr.setTArray(new CTText[] { ctText });cLink.setRArray(new CTR[] { ctr });}/*------------------------------------Word 页眉页脚相关--------------------------------------------------- *//*** @Description: 页脚:显示页码信息*/public void simpleNumberFooter(XWPFDocument document) throws Exception { CTP ctp = CTP.Factory.newInstance();XWPFParagraph codePara = new XWPFParagraph(ctp, document);XWPFRun r1 = codePara.createRun();r1.setText("第");setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");r1 = codePara.createRun();CTFldChar fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.BEGIN);r1 = codePara.createRun();CTText ctText = r1.getCTR().addNewInstrText();ctText.setStringValue("PAGE \\* MERGEFORMAT");ctText.setSpace(SpaceAttribute.Space.PRESERVE);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.END);r1 = codePara.createRun();r1.setText("页总共");setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");r1 = codePara.createRun();fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.BEGIN);r1 = codePara.createRun();ctText = r1.getCTR().addNewInstrText();ctText.setStringValue("NUMPAGES \\* MERGEFORMAT ");ctText.setSpace(SpaceAttribute.Space.PRESERVE);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.END);r1 = codePara.createRun();r1.setText("页");setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");setParagraphAlignInfo(codePara, ParagraphAlignment.CENTER,TextAlignment.CENTER);codePara.setBorderTop(Borders.THICK);XWPFParagraph[] newparagraphs = new XWPFParagraph[1];newparagraphs[0] = codePara;CTSectPr sectPr = document.getDocument().getBody().addNewSectPr();XWPFHeaderFooterPolicy headerFooterPolicy = new XWPFHeaderFooterPolicy( document, sectPr);headerFooterPolicy.createFooter(STHdrFtr.DEFAULT, newparagraphs);}/*** @Description: 页眉:显示时间信息*/public void simpleDateHeader(XWPFDocument document) throws Exception {CTP ctp = CTP.Factory.newInstance();XWPFParagraph codePara = new XWPFParagraph(ctp, document);XWPFRun r1 = codePara.createRun();CTFldChar fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.BEGIN);r1 = codePara.createRun();CTText ctText = r1.getCTR().addNewInstrText();ctText.setStringValue("TIME \\@ \"EEEE\"");ctText.setSpace(SpaceAttribute.Space.PRESERVE);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.END);r1 = codePara.createRun();r1.setText("年");setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");r1 = codePara.createRun();fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.BEGIN);r1 = codePara.createRun();ctText = r1.getCTR().addNewInstrText();ctText.setStringValue("TIME \\@ \"O\"");ctText.setSpace(SpaceAttribute.Space.PRESERVE);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.END);r1 = codePara.createRun();r1.setText("月");r1.setFontSize(11);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22"); r1 = codePara.createRun();fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.BEGIN);r1 = codePara.createRun();ctText = r1.getCTR().addNewInstrText();ctText.setStringValue("TIME \\@ \"A\"");ctText.setSpace(SpaceAttribute.Space.PRESERVE);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");fldChar = r1.getCTR().addNewFldChar();fldChar.setFldCharType(STFldCharType.END);r1 = codePara.createRun();r1.setText("日");r1.setFontSize(11);setParagraphRunFontInfo(codePara, r1, null, "微软雅黑", "22");setParagraphAlignInfo(codePara, ParagraphAlignment.CENTER,TextAlignment.CENTER);codePara.setBorderBottom(Borders.THICK);XWPFParagraph[] newparagraphs = new XWPFParagraph[1];newparagraphs[0] = codePara;CTSectPr sectPr = document.getDocument().getBody().addNewSectPr();XWPFHeaderFooterPolicy headerFooterPolicy = new XWPFHeaderFooterPolicy(document, sectPr);headerFooterPolicy.createHeader(STHdrFtr.DEFAULT, newparagraphs);}/*------------------------------------Word 段落相关--------------------------------------------------- *//*** @Description: 得到段落CTPPr*/public CTPPr getParagraphCTPPr(XWPFParagraph p) {CTPPr pPPr = null;if (p.getCTP() != null) {if (p.getCTP().getPPr() != null) {pPPr = p.getCTP().getPPr();} else {pPPr = p.getCTP().addNewPPr();}}return pPPr;}/*** @Description: 得到XWPFRun的CTRPr*/public CTRPr getRunCTRPr(XWPFParagraph p, XWPFRun pRun) { CTRPr pRpr = null;if (pRun.getCTR() != null) {pRpr = pRun.getCTR().getRPr();if (pRpr == null) {pRpr = pRun.getCTR().addNewRPr();}} else {pRpr = p.getCTP().addNewR().addNewRPr();}return pRpr;}public XWPFRun getOrAddParagraphFirstRun(XWPFParagraph p, boolean isInsert, boolean isNewLine) {XWPFRun pRun = null;if (isInsert) {pRun = p.createRun();} else {if (p.getRuns() != null && p.getRuns().size() > 0) {pRun = p.getRuns().get(0);} else {pRun = p.createRun();}}if (isNewLine) {pRun.addBreak();}return pRun;}public void setParagraphTextFontInfo(XWPFParagraph p, boolean isInsert, boolean isNewLine, String content, String fontFamily,String fontSize) {XWPFRun pRun = getOrAddParagraphFirstRun(p, isInsert, isNewLine);setParagraphRunFontInfo(p, pRun, content, fontFamily, fontSize);}/*** @Description 设置字体信息*/public void setParagraphRunFontInfo(XWPFParagraph p, XWPFRun pRun,String content, String fontFamily, String fontSize) {CTRPr pRpr = getRunCTRPr(p, pRun);if (StringUtils.isNotBlank(content)) {pRun.setText(content);}// 设置字体CTFonts fonts = pRpr.isSetRFonts() ? pRpr.getRFonts() : pRpr.addNewRFonts();fonts.setAscii(fontFamily);fonts.setEastAsia(fontFamily);fonts.setHAnsi(fontFamily);// 设置字体大小CTHpsMeasure sz = pRpr.isSetSz() ? pRpr.getSz() : pRpr.addNewSz();sz.setVal(new BigInteger(fontSize));CTHpsMeasure szCs = pRpr.isSetSzCs() ? pRpr.getSzCs() : pRpr.addNewSzCs();szCs.setVal(new BigInteger(fontSize));}/*** @Description: 设置段落基本样式*/public void setParagraphTextBasicStyleInfo(XWPFParagraph p, XWPFRun pRun,String colorVal, boolean isBlod, boolean isUnderLine,String underLineColor, STUnderline.Enum underStyle,boolean isItalic, boolean isStrike, boolean isHightLight,STHighlightColor.Enum hightStyle, boolean isShd,STShd.Enum shdStyle, String shdColor) {setParagraphTextStyleInfo(p, pRun, colorVal, isBlod, isUnderLine,underLineColor, underStyle, isItalic, isStrike, false, false,false, false, false, false, false, null, isHightLight,hightStyle, isShd, shdStyle, shdColor, null, 0, 0, 0);}/*** @Description: 设置段落文本样式(高亮与底纹显示效果不同)设置字符间距信息(CTSignedTwips Measure)* @param verticalAlign* : SUPERSCRIPT上标 SUBSCRIPT下标* @param position* :字符间距位置：>0提升 <0降低=磅值*2 如3磅=6* @param spacingValue* :字符间距间距 >0加宽 <0紧缩 =磅值*20 如2磅=40* @param indent* :字符间距缩进 <100 缩*/public void setParagraphTextSimpleStyleInfo(XWPFParagraph p, XWPFRun pRun,String colorVal, boolean isBlod, boolean isUnderLine,String underLineColor, STUnderline.Enum underStyle,boolean isItalic, boolean isStrike, boolean isHightLight,STHighlightColor.Enum hightStyle, boolean isShd,STShd.Enum shdStyle, String shdColor, VerticalAlign verticalAlign, int position, int spacingValue, int indent) {setParagraphTextStyleInfo(p, pRun, colorVal, isBlod, isUnderLine,underLineColor, underStyle, isItalic, isStrike, false, false,false, false, false, false, false, null, isHightLight,hightStyle, isShd, shdStyle, shdColor, verticalAlign, position,spacingValue, indent);}/*** @Description: 设置段落文本样式(高亮与底纹显示效果不同)设置字符间距信息(CTSignedTwips Measure)* @param verticalAlign* : SUPERSCRIPT上标 SUBSCRIPT下标* @param position* :字符间距位置：>0提升 <0降低=磅值*2 如3磅=6* @param spacingValue* :字符间距间距 >0加宽 <0紧缩 =磅值*20 如2磅=40* @param indent* :字符间距缩进 <100 缩*/public void setParagraphTextStyleInfo(XWPFParagraph p, XWPFRun pRun, String colorVal, boolean isBlod, boolean isUnderLine,String underLineColor, STUnderline.Enum underStyle,boolean isItalic, boolean isStrike, boolean isDStrike,boolean isShadow, boolean isVanish, boolean isEmboss,boolean isImprint, boolean isOutline, boolean isEm,STEm.Enum emType, boolean isHightLight,STHighlightColor.Enum hightStyle, boolean isShd,STShd.Enum shdStyle, String shdColor, VerticalAlign verticalAlign, int position, int spacingValue, int indent) {if (pRun == null) {return;}CTRPr pRpr = getRunCTRPr(p, pRun);if (colorVal != null) {pRun.setColor(colorVal);}// 设置字体样式// 加粗if (isBlod) {pRun.setBold(isBlod);}// 倾斜if (isItalic) {pRun.setItalic(isItalic);}// 删除线if (isStrike) {pRun.setStrike(isStrike);}// 双删除线if (isDStrike) {CTOnOff dsCtOnOff = pRpr.isSetDstrike() ? pRpr.getDstrike() : pRpr .addNewDstrike();dsCtOnOff.setVal(STOnOff.TRUE);}// 阴影if (isShadow) {CTOnOff shadowCtOnOff = pRpr.isSetShadow() ? pRpr.getShadow(): pRpr.addNewShadow();shadowCtOnOff.setVal(STOnOff.TRUE);}// 隐藏if (isVanish) {CTOnOff vanishCtOnOff = pRpr.isSetVanish() ? pRpr.getVanish(): pRpr.addNewVanish();vanishCtOnOff.setVal(STOnOff.TRUE);}// 阳文if (isEmboss) {CTOnOff embossCtOnOff = pRpr.isSetEmboss() ? pRpr.getEmboss(): pRpr.addNewEmboss();embossCtOnOff.setVal(STOnOff.TRUE);}// 阴文if (isImprint) {CTOnOff isImprintCtOnOff = pRpr.isSetImprint() ? pRpr.getImprint() : pRpr.addNewImprint();isImprintCtOnOff.setVal(STOnOff.TRUE);}// 空心if (isOutline) {CTOnOff isOutlineCtOnOff = pRpr.isSetOutline() ? pRpr.getOutline() : pRpr.addNewOutline();isOutlineCtOnOff.setVal(STOnOff.TRUE);}// 着重号if (isEm) {CTEm em = pRpr.isSetEm() ? pRpr.getEm() : pRpr.addNewEm();em.setVal(emType);}// 设置下划线样式if (isUnderLine) {CTUnderline u = pRpr.isSetU() ? pRpr.getU() : pRpr.addNewU();if (underStyle != null) {u.setVal(underStyle);}if (underLineColor != null) {u.setColor(underLineColor);}}// 设置突出显示文本if (isHightLight) {if (hightStyle != null) {CTHighlight hightLight = pRpr.isSetHighlight() ? pRpr.getHighlight() : pRpr.addNewHighlight();hightLight.setVal(hightStyle);}}if (isShd) {// 设置底纹CTShd shd = pRpr.isSetShd() ? pRpr.getShd() : pRpr.addNewShd();if (shdStyle != null) {shd.setVal(shdStyle);}if (shdColor != null) {shd.setColor(shdColor);}}// 上标下标if (verticalAlign != null) {pRun.setSubscript(verticalAlign);}// 设置文本位置pRun.setTextPosition(position);if (spacingValue > 0) {// 设置字符间距信息CTSignedTwipsMeasure ctSTwipsMeasure = pRpr.isSetSpacing() ? pRpr .getSpacing() : pRpr.addNewSpacing();ctSTwipsMeasure.setVal(new BigInteger(String.valueOf(spacingValue)));}if (indent > 0) {CTTextScale paramCTTextScale = pRpr.isSetW() ? pRpr.getW() : pRpr .addNewW();paramCTTextScale.setVal(indent);}}/*** @Description: 设置段落底纹(对整段文字起作用)*/public void setParagraphShdStyle(XWPFParagraph p, boolean isShd,STShd.Enum shdStyle, String shdColor) {CTPPr pPpr = getParagraphCTPPr(p);CTShd shd = pPpr.isSetShd() ? pPpr.getShd() : pPpr.addNewShd();if (shdStyle != null) {shd.setVal(shdStyle);}if (shdColor != null) {shd.setColor(shdColor);}}/*** @Description: 设置段落间距信息,一行=100 一磅=20*/public void setParagraphSpacingInfo(XWPFParagraph p, boolean isSpace, String before, String after, String beforeLines, String afterLines, boolean isLine, String line, STLineSpacingRule.Enum lineValue) {CTPPr pPPr = getParagraphCTPPr(p);CTSpacing pSpacing = pPPr.getSpacing() != null ? pPPr.getSpacing(): pPPr.addNewSpacing();// 段前磅数if (before != null) {pSpacing.setBefore(new BigInteger(before));}// 段后磅数if (after != null) {pSpacing.setAfter(new BigInteger(after));}// 段前行数if (beforeLines != null) {pSpacing.setBeforeLines(new BigInteger(beforeLines)); }// 段后行数if (afterLines != null) {pSpacing.setAfterLines(new BigInteger(afterLines)); }}// 间距if (line != null) {pSpacing.setLine(new BigInteger(line));}if (lineValue != null) {pSpacing.setLineRule(lineValue);}}}// 设置段落缩进信息 1厘米≈567public void setParagraphIndInfo(XWPFParagraph p, String firstLine,String firstLineChar, String hanging, String hangingChar,String right, String rigthChar, String left, String leftChar) {CTPPr pPPr = getParagraphCTPPr(p);CTInd pInd = pPPr.getInd() != null ? pPPr.getInd() : pPPr.addNewInd();if (firstLine != null) {pInd.setFirstLine(new BigInteger(firstLine));}if (firstLineChar != null) {pInd.setFirstLineChars(new BigInteger(firstLineChar)); }if (hanging != null) {pInd.setHanging(new BigInteger(hanging));}if (hangingChar != null) {pInd.setHangingChars(new BigInteger(hangingChar));}if (left != null) {pInd.setLeft(new BigInteger(left));}if (leftChar != null) {pInd.setLeftChars(new BigInteger(leftChar));}if (right != null) {pInd.setRight(new BigInteger(right));}if (rigthChar != null) {pInd.setRightChars(new BigInteger(rigthChar));}}// 设置段落边框public void setParagraphBorders(XWPFParagraph p, Borders lborder, Borders tBorders, Borders rBorders, Borders bBorders,Borders btborders) {if (lborder != null) {p.setBorderLeft(lborder);}if (tBorders != null) {p.setBorderTop(tBorders);}if (rBorders != null) {p.setBorderRight(rBorders);}if (bBorders != null) {p.setBorderBottom(bBorders);}if (btborders != null) {p.setBorderBetween(btborders);}}/*** @Description: 设置段落对齐*/public void setParagraphAlignInfo(XWPFParagraph p,ParagraphAlignment pAlign, TextAlignment valign) { if (pAlign != null) {p.setAlignment(pAlign);}if (valign != null) {p.setVerticalAlignment(valign);}}/*------------------------------------Word 表格相关--------------------------------------------------- *//*** @Description:删除指定位置的表格,被删除表格后的索引位置--*/public void deleteTableByIndex(XWPFDocument xdoc, int pos) {Iterator<IBodyElement> bodyElement = xdoc.getBodyElementsIterator();int eIndex = 0, tableIndex = -1;while (bodyElement.hasNext()) {IBodyElement element = bodyElement.next();BodyElementType elementType = element.getElementType();if (elementType == BodyElementType.TABLE) {tableIndex++;if (tableIndex == pos) {break;}}eIndex++;}xdoc.removeBodyElement(eIndex);。
POI操作大全(动态合并单元格为单元格生成一个自定义的数据显示格式自定义公式计算结果生
POI操作大全（动态合并单元格为单元格生成一个自定义的数据显示格式自定义公式计算结果生POI是一个用于操作Microsoft Office格式文件的Java库。
通过POI库，可以实现对Excel、Word和PowerPoint文件的读写操作。
本文将介绍POI库的一些常用操作，包括动态合并单元格、为单元格生成自定义的数据显示格式、自定义公式计算结果等。
一、动态合并单元格在Excel中，可以将多个相邻的单元格合并成一个单元格，以便显示更大的数据块。
POI库可以很方便地实现对合并单元格的操作。
1.创建合并区域：```javaCellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);sheet.addMergedRegion(region);```其中，rowStart和rowEnd表示合并区域的起始行和结束行，colStart和colEnd表示合并区域的起始列和结束列。
2.设置合并单元格的值：```javaCell cell = sheet.getRow(rowStart).createCell(colStart);cell.setCellValue("合并单元格的值");```这里，我们取合并区域的起始行和起始列，设置单元格的值。
二、为单元格生成一个自定义的数据显示格式在Excel中，可以对单元格的值设置格式，以便以不同的方式展示数据。
POI库提供了设置单元格格式的方法。
1.创建格式对象：```javaCellStyle cellStyle = workbook.createCellStyle(;```2.设置格式：```javaDataFormat dataFormat = workbook.createDataFormat(;short format = dataFormat.getFormat("自定义格式");cellStyle.setDataFormat(format);```其中，"自定义格式"是Excel中的一种格式化字符串。
POI操作Excel
POI操作Excel⼀、POI概述 Apache POI是Apache软件基⾦会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
结构：HSSF －提供读写Microsoft Excel格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML格式档案的功能。
HWPF －提供读写Microsoft Word格式档案的功能。
HSLF －提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读写Microsoft Visio格式档案的功能。
使⽤必须引⼊依赖<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency> 注：3.17版本是⽀持jdk6的最后版本⼆、HSSF概况 HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以⽤纯Java代码来读取、写⼊、修改Excel⽂件。
HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“⽤户模型”和“事件-⽤户模型”。
三、 POI EXCEL⽂档结构类HSSFWorkbook excel⽂档对象HSSFSheet excel的sheetHSSFRow excel的⾏HSSFCell excel的单元格HSSFFont excel字体HSSFName 名称HSSFDataFormat ⽇期格式HSSFHeader sheet头HSSFFooter sheet尾HSSFCellStyle cell样式HSSFDateUtil ⽇期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表四、EXCEL的读写操作1、读取“区域数据.xls”并储存于list集合中，“区域数据.xls”如下图1public List<Area> importXLS(){23 ArrayList<Area> list = new ArrayList<>();4try {5 //1、获取⽂件输⼊流6 InputStream inputStream = new FileInputStream("/Users/Shared/区域数据.xls");7 //2、获取Excel⼯作簿对象8 HSSFWorkbook workbook = new HSSFWorkbook(inputStream);9 //3、得到Excel⼯作表对象10 HSSFSheet sheetAt = workbook.getSheetAt(0);11//4、循环读取表格数据12 for (Row row : sheetAt) {13 //⾸⾏（即表头）不读取14if (row.getRowNum() == 0) {15continue;16 }17//读取当前⾏中单元格数据，索引从0开始18 String areaNum = row.getCell(0).getStringCellValue();19 String province = row.getCell(1).getStringCellValue();20 String city = row.getCell(2).getStringCellValue();21 String district = row.getCell(3).getStringCellValue();22 String postcode = row.getCell(4).getStringCellValue();2324 Area area = new Area();25 area.setCity(city);26 area.setDistrict(district);27 area.setProvince(province);28 area.setPostCode(postcode);29 list.add(area);30 }31 //5、关闭流32 workbook.close();33 } catch (IOException e) {34 e.printStackTrace();35 }36 return list;37 }2、导出数据到“区域数据.xls”⽂件中，页⾯数据如下图：1public void exportExcel() throws IOException {23 Page<Area> page = areaService.pageQuery(null);4 List<Area> list = page.getContent();56//1.在内存中创建⼀个excel⽂件7 HSSFWorkbook hssfWorkbook = new HSSFWorkbook();8//2.创建⼯作簿9 HSSFSheet sheet = hssfWorkbook.createSheet();10//3.创建标题⾏11 HSSFRow titlerRow = sheet.createRow(0);12 titlerRow.createCell(0).setCellValue("省");13 titlerRow.createCell(1).setCellValue("市");14 titlerRow.createCell(2).setCellValue("区");15 titlerRow.createCell(3).setCellValue("邮编");16 titlerRow.createCell(4).setCellValue("简码");17 titlerRow.createCell(5).setCellValue("城市编码");1819//4.遍历数据,创建数据⾏20for (Area area : list) {21//获取最后⼀⾏的⾏号22int lastRowNum = sheet.getLastRowNum();23 HSSFRow dataRow = sheet.createRow(lastRowNum + 1);24 dataRow.createCell(0).setCellValue(area.getProvince());25 dataRow.createCell(1).setCellValue(area.getCity());26 dataRow.createCell(2).setCellValue(area.getDistrict());27 dataRow.createCell(3).setCellValue(area.getPostcode());28 dataRow.createCell(4).setCellValue(area.getShortcode());29 dataRow.createCell(5).setCellValue(area.getCitycode());30 }31//5.创建⽂件名32 String fileName = "区域数据统计.xls";33//6.获取输出流对象34 HttpServletResponse response = ServletActionContext.getResponse();35 ServletOutputStream outputStream = response.getOutputStream();3637//7.获取mimeType38 ServletContext servletContext = ServletActionContext.getServletContext();39 String mimeType = servletContext.getMimeType(fileName);40//8.获取浏览器信息,对⽂件名进⾏重新编码41 HttpServletRequest request = ServletActionContext.getRequest();42 fileName = FileUtils.filenameEncoding(fileName, request);4344//9.设置信息头45 response.setContentType(mimeType);46 response.setHeader("Content-Disposition","attachment;filename="+fileName);47//10.写出⽂件,关闭流48 hssfWorkbook.write(outputStream);49 hssfWorkbook.close();50 }⼯具类1public class FileUtils {23public static String filenameEncoding(String filename, HttpServletRequest request) throws IOException { 4 String agent = request.getHeader("User-Agent"); //获取浏览器5if (agent.contains("Firefox")) {6 BASE64Encoder base64Encoder = new BASE64Encoder();7 filename = "=?utf-8?B?"8 + base64Encoder.encode(filename.getBytes("utf-8"))9 + "?=";10 } else if(agent.contains("MSIE")) {11 filename = URLEncoder.encode(filename, "utf-8");12 } else if(agent.contains ("Safari")) {13 filename = new String (filename.getBytes ("utf-8"),"ISO8859-1");14 } else {15 filename = URLEncoder.encode(filename, "utf-8");16 }17return filename;18 }19 }写出xls⽂件：五、 EXCEL常⽤操作⽅法1、得到Excel常⽤对象1 POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); 2//得到Excel⼯作簿对象3 HSSFWorkbook wb = new HSSFWorkbook(fs);4//得到Excel⼯作表对象5 HSSFSheet sheet = wb.getSheetAt(0);6//得到Excel⼯作表的⾏7 HSSFRow row = sheet.getRow(i);8//得到Excel⼯作表指定⾏的单元格9 HSSFCell cell = row.getCell((short) j);10 cellStyle = cell.getCellStyle();//得到单元格样式2、建⽴Excel常⽤对象1 HSSFWorkbook wb = new HSSFWorkbook();//创建Excel⼯作簿对象2 HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel⼯作表对象3 HSSFRow row = sheet.createRow((short)0); //创建Excel⼯作表的⾏4 cellStyle = wb.createCellStyle();//创建单元格样式5 row.createCell((short)0).setCellStyle(cellStyle); //创建Excel⼯作表指定⾏的单元格6 row.createCell((short)0).setCellValue(1); //设置Excel⼯作表的值3、设置sheet名称和单元格内容1 wb.setSheetName(1, "第⼀张⼯作表",HSSFCell.ENCODING_UTF_16);2 cell.setEncoding((short) 1);3 cell.setCellValue("单元格内容");4、取得sheet的数⽬1 wb.getNumberOfSheets()5、根据index取得sheet对象1 HSSFSheet sheet = wb.getSheetAt(0);6、取得有效的⾏数1int rowcount = sheet.getLastRowNum();7、取得⼀⾏的有效单元格个数1 row.getLastCellNum();8、单元格值类型读写1 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型2 cell.getNumericCellValue();//读取为数值类型的单元格内容9、设置列宽、⾏⾼1 sheet.setColumnWidth((short)column,(short)width);2 row.setHeight((short)height);10、添加区域，合并单元格1 Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo2 ,(short)columnTo);//合并从第rowFrom⾏columnFrom列3 sheet.addMergedRegion(region);// 到rowTo⾏columnTo的区域4//得到所有区域5 sheet.getNumMergedRegions()11、保存Excel⽂件1 FileOutputStream fileOut = new FileOutputStream(path);2 wb.write(fileOut);12、根据单元格不同属性返回字符串数值1public String getCellStringValue(HSSFCell cell) {2 String cellValue = "";3 switch (cell.getCellType()) {4 case HSSFCell.CELL_TYPE_STRING://字符串类型5 cellValue = cell.getStringCellValue();6 if(cellValue.trim().equals("")||cellValue.trim().length()<=0)7 cellValue=" ";8 break;9 case HSSFCell.CELL_TYPE_NUMERIC: //数值类型10 cellValue = String.valueOf(cell.getNumericCellValue());11 break;12 case HSSFCell.CELL_TYPE_FORMULA: //公式13 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);14 cellValue = String.valueOf(cell.getNumericCellValue());15 break;16 case HSSFCell.CELL_TYPE_BLANK:17 cellValue=" ";18 break;19 case HSSFCell.CELL_TYPE_BOOLEAN:20 break;21 case HSSFCell.CELL_TYPE_ERROR:22 break;23 default:24 break;25 }26 return cellValue;27 }13、常⽤单元格边框格式1 HSSFCellStyle style = wb.createCellStyle();2 style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框3 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框4 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框5 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框14、设置字体和内容位置1 HSSFFont f = wb.createFont();2 f.setFontHeightInPoints((short) 11);//字号3 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗4 style.setFont(f);5 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中6 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中7 style.setRotation(short rotation);//单元格内容的旋转的⾓度8 HSSFDataFormat df = wb.createDataFormat();9 style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式10 cell.setCellFormula(string);//给单元格设公式11 style.setRotation(short rotation);//单元格内容的旋转的⾓度15、插⼊图⽚1//先把读进来的图⽚放到⼀个ByteArrayOutputStream中，以便产⽣ByteArray2 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();3 BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));4 ImageIO.write(bufferImg,"jpg",byteArrayOut);5//读进⼀个excel模版6 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");7 fs = new POIFSFileSystem(fos);8//创建⼀个⼯作薄9 HSSFWorkbook wb = new HSSFWorkbook(fs);10 HSSFSheet sheet = wb.getSheetAt(0);11 HSSFPatriarch patriarch = sheet.createDrawingPatriarch();12 HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);13 patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));16、调整⼯作表位置1 HSSFWorkbook wb = new HSSFWorkbook();2 HSSFSheet sheet = wb.createSheet("format sheet");3 HSSFPrintSetup ps = sheet.getPrintSetup();4 sheet.setAutobreaks(true);5 ps.setFitHeight((short)1);6 ps.setFitWidth((short)1);。
POI对Excel表格操作研
1
只需要用工作表调用合并单元格的方法： 只 需 sheet 表 调 用 合 并 单 元 格 的 方 法
sheet.addMergedRegion(new
合 并
CellRangeAddress(
单
1, //开始行号
元 格Βιβλιοθήκη 1, //终止行号1, //开始列号
WritableSheet.mergeCells( int m, //开始列号 int n, //开始行号 int p, //终止列号 int q//终止行号
wb.addPicture(bytes,
Workbook.PICTURE_TYPE_JPEG); File file=new
CreationHelper helper = File("D:\\1.png");//仅支持png格
wb.getCreationHelper();
式图片
插
Drawing drawing = sheet.
XSSFWorkbook(f);
Sheet
读 操
XSSFSheet sheet =
sheet=book.getSheet(0);
作
workbook.getSheet("Sheet");// Cell cell1=sheet.getCell(0,0);
XSSFRow row =
String result=cell1.getContents();
import jxl.*;
写
XSSFRow row
WritableWorkbook book=
操 作
=sheet.createRow(65536);
Workbook.createWorkbook(new
使用POI来处理Excel和Word文件格式
// 文件二进制输入流private InputStream is = null;// 当前的Sheetprivate int currSheet;// 当前位置private int currPosition;// Sheet数量private int numOfSheets;// HSSFWorkbookHSSFWorkbook workbook = null;// 设置Cell之间以空格分割private static String EXCEL_LINE_DELIMITER = " ";// 设置最大列数private static int MAX_EXCEL_COLUMNS = 64;// 构造函数创建一个ExcelReaderpublic ExcelReader(String inputfile) throws IOException, Exception { // 判断参数是否为空或没有意义if (inputfile == null || inputfile.trim().equals("")) {throw new IOException("no input file specified");}// 取得文件名的后缀名赋值给filetypethis.filetype = inputfile.substring(stIndexOf(".") + 1);// 设置开始行为0currPosition = 0;// 设置当前位置为0currSheet = 0;// 创建文件输入流is = new FileInputStream(inputfile);// 判断文件格式if (filetype.equalsIgnoreCase("txt")) {// 如果是txt则直接创建BufferedReader读取reader = new BufferedReader(new InputStreamReader(is));}else if (filetype.equalsIgnoreCase("xls")) {// 如果是Excel文件则创建HSSFWorkbook读取workbook = new HSSFWorkbook(is);// 设置Sheet数numOfSheets = workbook.getNumberOfSheets();}else {throw new Exception("File Type Not Supported");}}// 函数readLine读取文件的一行public String readLine() throws IOException {// 如果是txt文件则通过reader读取if (filetype.equalsIgnoreCase("txt")) {String str = reader.readLine();// 空行则略去，直接读取下一行while (str.trim().equals("")) {str = reader.readLine();}return str;}// 如果是XLS文件则通过POI提供的API读取文件else if (filetype.equalsIgnoreCase("xls")) {// 根据currSheet值获得当前的sheetHSSFSheet sheet = workbook.getSheetAt(currSheet);// 判断当前行是否到但前Sheet的结尾if (currPosition > sheet.getLastRowNum()) {// 当前行位置清零currPosition = 0;// 判断是否还有Sheetwhile (currSheet != numOfSheets - 1) {// 得到下一张Sheetsheet = workbook.getSheetAt(currSheet + 1);// 当前行数是否已经到达文件末尾if (currPosition == sheet.getLastRowNum()) {// 当前Sheet指向下一张SheetcurrSheet++;continue;} else {// 获取当前行数int row = currPosition;currPosition++;// 读取当前行数据return getLine(sheet, row);}}return null;}// 获取当前行数int row = currPosition;currPosition++;// 读取当前行数据return getLine(sheet, row);}return null;}// 函数getLine返回Sheet的一行数据private String getLine(HSSFSheet sheet, int row) {// 根据行数取得Sheet的一行HSSFRow rowline = sheet.getRow(row);// 创建字符创缓冲区StringBuffer buffer = new StringBuffer();// 获取当前行的列数int filledColumns = rowline.getLastCellNum(); HSSFCell cell = null;// 循环遍历所有列for (int i = 0; i < filledColumns; i++) {// 取得当前Cellcell = rowline.getCell((short) i);String cellvalue = null;if (cell != null) {// 判断当前Cell的Typeswitch (cell.getCellType()) {// 如果当前Cell的Type为NUMERICcase HSSFCell.CELL_TYPE_NUMERIC: {// 判断当前的cell是否为Dateif (HSSFDateUtil.isCellDateFormatted(cell)) {// 如果是Date类型则，取得该Cell的Date值Date date = cell.getDateCellValue();// 把Date转换成本地格式的字符串cellvalue = cell.getDateCellValue().toLocaleString();}// 如果是纯数字else {// 取得当前Cell的数值Integer num = new Integer((int) cell.getNumericCellValue());cellvalue = String.valueOf(num);}break;}// 如果当前Cell的Type为STRINcase HSSFCell.CELL_TYPE_STRING:// 取得当前的Cell字符串cellvalue = cell.getStringCellValue().replaceAll("'", "''");break;// 默认的Cell值default:cellvalue = " ";}} else {cellvalue = "";}// 在每个字段之间插入分割符buffer.append(cellvalue).append(EXCEL_LINE_DELIMITER);}// 以字符串返回该行的数据return buffer.toString();}// close函数执行流的关闭操作public void close() {// 如果is不为空，则关闭InputSteam文件输入流if (is != null) {try {is.close();} catch (IOException e) {is = null;}}// 如果reader不为空则关闭BufferedReader文件输入流if (reader != null) {try {reader.close();} catch (IOException e) {reader = null;}}}}7.3.2 ExcelReader的运行效果下面创建一个main函数，用来测试上面的ExcelReader类，代码如下。
利用POI读取word、Excel文件的最佳实践教程
利⽤POI读取word、Excel⽂件的最佳实践教程前⾔是 Apache 旗下⼀款读写微软家⽂档声名显赫的类库。
应该很多⼈在做报表的导出，或者创建 word ⽂档以及读取之类的都是⽤过 POI。
POI 也的确对于这些操作带来很⼤的便利性。
我最近做的⼀个⼯具就是读取计算机中的 word 以及 excel ⽂件。
POI结构说明包名称说明HSSF提供读写Microsoft Excel XLS格式档案的功能。
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF提供读写Microsoft Word DOC格式档案的功能。
HSLF提供读写Microsoft PowerPoint格式档案的功能。
HDGF提供读Microsoft Visio格式档案的功能。
HPBF提供读Microsoft Publisher格式档案的功能。
HSMF提供读Microsoft Outlook格式档案的功能。
下⾯就word和excel两⽅⾯讲解以下遇到的⼀些坑：word 篇对于 word ⽂件，我需要的就是提取⽂件中正⽂的⽂字。
所以可以创建⼀个⽅法来读取 doc 或者 docx ⽂件：private static String readDoc(String filePath, InputStream is) {String text= "";try {if (filePath.endsWith("doc")) {WordExtractor ex = new WordExtractor(is);text = ex.getText();ex.close();is.close();} else if(filePath.endsWith("docx")) {XWPFDocument doc = new XWPFDocument(is);XWPFWordExtractor extractor = new XWPFWordExtractor(doc);text = extractor.getText();extractor.close();is.close();}} catch (Exception e) {logger.error(filePath, e);} finally {if (is != null) {is.close();}}return text;}理论上来说，这段代码应该对于读取⼤多数 doc 或者 docx ⽂件都是有效的。
使用POI进行Excel操作的总结一——创建Workbook，Sheet，Row以及Cell
使⽤POI进⾏Excel操作的总结⼀——创建Workbook，Sheet，Row以及Cell前段时间，看在其他的⽹站上给出Excel⽂档的导⼊与导出操作，感觉很酷的样⼦，所以就学习了⼀下如何使⽤POI进⾏Excel的操作，现在对之前的学习过程进⾏⼀个总结。
⼀、现在普遍使⽤的Excel⽂档有xls以及xlsx这两种Excel⽂档，其中xls格式的Excel⽂档⼜分为5.0/95⼯作簿以及97-2003⼯作簿这两种。
需要注意的是，由于5.0/95⼯作簿的版本太低，现在的POI⽂档不再⽀持这种类型的Excel⽂档的读取⼯作，当试图读取这种类型的Excel⽂档的时候，POI会抛出⼀个异常（OldExcelFormatException）。
我现在使⽤的POI是3.14版本。
⼆、Workbook的创建1、由于Excel中存在xls以及xlsx这两种格式，所以创建⽅式也有所不同。
其中对于xls格式的⽂档，需要使⽤HSSFWorkbook来创建⼯作簿对象，⽽对于xlsx格式的Excel⽂档，则需要使⽤XSSFWrokbook来创建⼯作簿。
有⼀点需要注意的是，HSSFWorkbook与XSSFWorkbook 这两个类其实都是Workbook接⼝的⼀个实现类。
好了，下⾯就是创建⼯作簿对象的代码：//创建⼀个不存在的excel⽂件private static Workbook createWorkbookIfNotExist(String fileName) throws Exception {Workbook wb = null;if(fileName.endsWith(".xls")) {wb = new HSSFWorkbook();} else if(fileName.endsWith(".xlsx")) {wb = new XSSFWorkbook();} else {throw new Exception("⽂件类型错误！");}try{OutputStream output = new FileOutputStream(fileName);wb.write(output);}catch(FileNotFoundException e) {System.out.println("⽂件创建失败，失败原因为：" + e.getMessage());throw new FileNotFoundException();}System.out.println(fileName + "⽂件创建成功！");return wb;}//创建⼀个新的或者已存在的Excel⽂档的Workbookpublic static Workbook createWorkbook(String fileName) throws Exception {InputStream input = null;Workbook wb = null;try{input = new FileInputStream(fileName);wb = WorkbookFactory.create(input);} catch(FileNotFoundException e) {System.out.println("要打开的⽂件不存在，正试图创建该⽂件，请稍后……！");wb = createWorkbookIfNotExist(fileName);} catch(OldExcelFormatException e) {System.out.println("⽂件打开失败，原因：要打开的Excel⽂件版本过低！");throw new OldExcelFormatException("⽂件版本过低");} finally {if(input != null) {input.close();}}return wb;}2、创建Sheet的时候，同样的也存在HSSFSheet以及XSSHSheet这两种类型。
POI操作常用方法
POI操作常用方法POI（Poor Obfuscation Implementation）是一种用于操作Microsoft Office文件的Java库。
它提供了对Excel、Word和PowerPoint等文件的创建、读写和修改的功能。
POI操作常用的方法包括文件的创建、读取和写入、单元格的操作、格式设置、图表的创建和修改等。
1.文件的创建与读取：- 创建一个Excel文件：```Workbook workbook = new XSSFWorkbook(; // 创建一个新的Excel 文件Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个名为Sheet1的工作表```- 读取一个Excel文件：```InputStream inputStream = newFileInputStream("path/to/file.xlsx");Workbook workbook = new XSSFWorkbook(inputStream); // 读取Excel文件Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表```2.单元格的操作：-创建一个单元格并设置值：```Row row = sheet.createRow(0); // 创建第一行Cell cell = row.createCell(0); // 创建第一列cell.setCellValue("Hello World"); // 设置单元格的值```-获取单元格的值：```Row row = sheet.getRow(0); // 获取第一行Cell cell = row.getCell(0); // 获取第一列String value = cell.getStringCellValue(; // 获取单元格的值```3.格式设置：-设置单元格的样式：```CellStyle style = workbook.createCellStyle(; // 创建样式对象Font font = workbook.createFont(; // 创建字体对象font.setBold(true); // 设置字体为粗体style.setFont(font); // 将字体应用于样式cell.setCellStyle(style); // 将样式应用于单元格```-设置单元格的格式：```DataFormat format = workbook.createDataFormat(; // 创建数据格式对象CellStyle style = workbook.createCellStyle(; // 创建样式对象style.setDataFormat(format.getFormat("#,#.00")); // 设置数据的格式，如数字格式为千分位cell.setCellStyle(style); // 将样式应用于单元格```4.图表的创建与修改：-创建一个柱状图：```Drawing<?> drawing = sheet.createDrawingPatriarch(; // 创建图纸对象ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); // 定义图表的位置和大小Chart chart = drawing.createChart(anchor); // 创建图表对象ChartAxis bottomAxis =chart.getChartAxisFactory(.createCategoryAxis(AxisPosition.BOTTO M); // 创建X轴对象ChartAxis leftAxis =chart.getChartAxisFactory(.createValueAxis(AxisPosition.LEFT); // 创建Y轴对象。
POI技术使用说明
POI技术使⽤说明POI技术使⽤说明⽬录⼀、⽂档总体说明 3⼆、 POI技术基础准备 32.1常⽤jar包 32.2资源下载⽹址 3三、 POI对EXCEL的操作 33.1创建⼀个Excel 33.1.1常⽤⽅法以及实例对象的创建 33.1.2完整例⼦ 43.2 解析Excel 53.2.1 常⽤⽅法整理 53.2.2 完整实例 5四、 POI对word的操作 64.1 关于POI 对word操作的简介 64.2 POI 对word的解析 74.2.1 常⽤⽅法整理 74.2.2 对word中图⽚的解析例⼦ 7五、 POI对ppt的操作 85.1 POI 创建PPT⽂件 85.1.1 常⽤⽅法整理 85.1.2 完整例⼦ 8⼀、⽂档总体说明该⽂档的主要内容是对POI技术的使⽤说明，POI 技术主要是对office⽂件的读写控制，通过程序解析⽂件中的内容，或者将数据内容⽣成⽂件。
操作的对象主要有：excel，doc，ppt等⽂件。
⼆、POI技术基础准备2.1常⽤jar包主要jar包：poi-3.8-beta4-20110826.jarpoi-excelant-3.8-beta4-20110826.jarpoi-ooxml-3.8-beta4-20110826.jarpoi-scratchpad-3.8-beta4-20110826.jar⼀些辅助的jar 包有：xmlbeans-2.3.0.jarstax-api-1.0.1.jarooxml-schemas-1.0.jarlog4j-1.2.13.jardom4j-1.6.1.jarcommons-logging-1.1.jarcommons-codec-1.3.jar2.2资源下载⽹址三、POI对EXCEL的操作3.1创建⼀个Excel3.1.1常⽤⽅法以及实例对象的创建(1)HSSFWorkbook demoWorkBook = new HSSFWorkbook();// 创建⼀个excel对象(2)HSSFSheet demoSheet = demoWorkBook.createSheet("sheet名称");// 创建⼀个sheet对象，可创建多个(3)HSSFRow row = demoSheet.createRow((short) index); //创建excel⾏，index为第⼏⾏，从0开始(4)HSSFCell cell = row.createCell(i);// 创建第row⾏的第i个单元格，i从0开始(5)单元格内容的设置：HSSFCellStyle cellStyle2 = demoWorkBook.createCellStyle();//创建单元格样式HSSFDataFormat format = demoWorkBook.createDataFormat();//创建数据格式cellStyle2.setDataFormat(format.getFormat("@"));//字符串类型所对应的是数据格式为"@"cell.setCellStyle(cellStyle2);//给cell赋样式cell.setCellValue(cells.get(i));//给单元格赋值3.1.2完整例⼦package com.poi.excel;import java.io.FileOutputStream;import ermodel.HSSFCell;import ermodel.HSSFCellStyle;import ermodel.HSSFDataFormat;import ermodel.HSSFRow;import ermodel.HSSFSheet;import ermodel.HSSFWorkbook;public class Test {public static void main(String[] args) throws Exception {String fileName = "f:\\我创建的Excel.xls";FileOutputStream fos = null;String[] tableHeader = { "姓名", "民族", "性别","年龄"};//表头名字HSSFWorkbook demoWorkBook = new HSSFWorkbook();// 创建excel HSSFSheet demoSheet = demoWorkBook.createSheet("个⼈信息");// 创建sheetint cellNumber = tableHeader.length;// 表头数⽬HSSFRow headerRow = demoSheet.createRow((short) 0); //第⼀⾏for (int i = 0; i < cellNumber; i++) {HSSFCell headerCell = headerRow.createCell(i);//创建第⼀⾏第i个单元格,从0开始headerCell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置内容格式为字符串型headerCell.setCellValue(tableHeader[i]);//设置内容}for (int i = 1; i < 10; i++) {HSSFRow row = demoSheet.createRow(i);// 创建第rowIndex⾏for (int j = 0; j < cellNumber; j++) {HSSFCell cell = row.createCell(j);// 创建第rowIndex⾏的第i个单元格if (cell.getCellType() != 1) {cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置数据类型}// 设置CELL格式为⽂本格式HSSFCellStyle cellStyle2 = demoWorkBook.createCellStyle();//创建单元格样式HSSFDataFormat format = demoWorkBook.createDataFormat();//创建数据格式cellStyle2.setDataFormat(format.getFormat("@"));//字符串类型所对应的是数据格式为"@"cell.setCellStyle(cellStyle2);//给cell赋样式cell.setCellValue("第"+i+"⾏第"+j+"列");//给单元格赋值}}//写⼊⽂件fos = new FileOutputStream(fileName);//⽂件输出流demoWorkBook.write(fos);System.out.println("表格已成功导出到 : " + fileName);}}3.2 解析Excel3.2.1 常⽤⽅法整理(1)HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);//xls⽂件转换成Excel实体类(2)wb.getNumberOfSheets()//获取Excel实体类的sheet数⽬(3)HSSFSheet sheet = wb.getSheetAt(k);//获取sheet对象(4)int rows = sheet.getPhysicalNumberOfRows();//获取sheet⾏数(5)HSSFRow row = sheet.getRow(r);//获取单⾏对象(6)int cells = row.getPhysicalNumberOfCells();//该数据⾏的列数⽬(7)HSSFCell cell = row.getCell(c);//获取单元格对象(8)得到单元格内容：value = "FORMULA value=" + cell.getCellFormula();//公式型数据value = "NUMERIC value=" + cell.getNumericCellValue();//数字型value = "STRING value=" + cell.getStringCellValue();//字符串型3.2.2 完整实例String fileName = "e:\\test.xls";HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);//xls⽂件转换成Excel实体类for (int k = 0; k < wb.getNumberOfSheets(); k++) {//wb.getNumberOfSheets()为sheet数⽬//遍历HSSFSheet sheet = wb.getSheetAt(k);//获取sheet对象int rows = sheet.getPhysicalNumberOfRows();//获取sheet⾏数//wb.getSheetName(k)为sheet名字System.out.println("Sheet " + k + " "" + wb.getSheetName(k) + "" has " + rows+ " row(s).");for (int r = 0; r < rows; r++) {//遍历⾏HSSFRow row = sheet.getRow(r);//获取单⾏对象if (row == null) {continue;}int cells = row.getPhysicalNumberOfCells();//该数据⾏的列数⽬//row.getRowNum()为⾏号,从0开始System.out.println("\nROW " + row.getRowNum() + " has " + cells+ " cell(s).");for (int c = 0; c < cells; c++) {HSSFCell cell = row.getCell(c);//获取单元格对象String value = null;switch (cell.getCellType()) {//判断单元格数据类型case HSSFCell.CELL_TYPE_FORMULA://公式型value = "FORMULA value=" + cell.getCellFormula();break;case HSSFCell.CELL_TYPE_NUMERIC://数值型value = "NUMERIC value=" + cell.getNumericCellValue();break;case HSSFCell.CELL_TYPE_STRING://字符型value = "STRING value=" + cell.getStringCellValue();break;default:}System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE="+ value);}}}四、POI对word的操作4.1 关于POI 对word操作的简介POI 对word操作，其中解析word还⾏，但是把数据⽣成⼀个word就没那么好⽤了。
poi操作wrod技巧
1.表格或单元格宽度：默认TblW的type属性为STTblWidth.AUTO,即自动伸缩。
所以要调整为指定类型：STTblWidth.DXA 1）表格宽：CTTblPr tblPr = xtab2.getCTTbl().getTblPr();tblPr.getTblW().setType(STTblWidth.DXA);tblPr.getTblW().setW(new BigInteger("7000"));2.单元格宽：CTTcPr tcpr = cell.getCTTc().addNewTcPr();CTTblWidth cellw = tcpr.addNewTcW();cellw.setType(STTblWidth.DXA);cellw.setW(BigInteger.valueOf(360*5));2.表格风格注：如果不设置风格，将采用默认的Normal风格CTTblPr tblPr = xtab2.getCTTbl().getTblPr();CTString styleStr = tblPr.addNewTblStyle();styleStr.setVal("StyledTable");2.表格行高：获取表格行的CTTrPr.增加CTHeight属性List<XWPFTableRow> rows = xtab2.getRows();for (XWPFTableRow row : rows) {CTTrPr trPr = row.getCtRow().addNewTrPr();CTHeight ht = trPr.addNewTrHeight();ht.setVal(BigInteger.valueOf(360));......}3.表格行内容垂直居中：CTVerticalJc va = tcpr.addNewVAlign();va.setVal(STVerticalJc.CENTER);3.表格单元格颜色例如下面的标题行与奇偶行颜色设置CTShd ctshd = tcpr.addNewShd();ctshd.setColor("auto");ctshd.setVal(STShd.CLEAR);if (rowCt == 0) {// 标题行ctshd.setFill("A7BFDE");}else if (rowCt % 2 == 0) {// even rowctshd.setFill("D3DFEE");}else {// odd rowctshd.setFill("EDF2F8");}5.获取某指定位置对象并生成新的光标位置注:这个更新或插入操作比较有用,比如更新文档目录.XmlCursor cursor = doc.getDocument().getBody().getPArray(0).newCursor();XWPFParagraph cP = doc.insertNewParagraph(cursor);6.插入图片：XWPFParagraph parapictest = document.createParagraph();XWPFRun runtest = parapictest.createRun();runtest.setText("图片:");XWPFRun pictest = document.createParagraph().createRun();XWPFPicture picture = pictest.addPicture(new FileInputStream("D://563.jpg"), Document.PICTURE_T YPE_JPEG, "D://563.jpg", 1000*360*10,1000*360*10);。
POI操作Excel
POI操作Excelpackage excel;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.regex.Matcher;import java.util.regex.Pattern;import ermodel.HSSFWorkbook;import ermodel.HSSFSheet;import ermodel.HSSFRow;import ermodel.HSSFCell;import org.apache.poi.hssf.util.Region;import org.apache.poi.ss.util.CellRangeAddress;import ermodel.HSSFCellStyle;public class CopyExcelSheetToAnotherExcelSheet {public static void main(String[] args) throws FileNotFoundException,IOException {String strPath = "D:\\excelFrom";//excel存放路径File file = new File(strPath);for (File excel : file.listFiles()) {ReadExcel(strPath, excel.getName());}}/*** strPath 文件夹路径名* name excel文件名*/private static void ReadExcel(String strPath, String name)throws IOException {// 打开已有的excelString strExcelPath = strPath + "\\" + name;InputStream in = new FileInputStream(strExcelPath);HSSFWorkbook wb = new HSSFWorkbook(in);for (int i = 0; i < wb.getNumberOfSheets(); i++) {HSSFSheet sheet = wb.getSheetAt(i);CreatNewExcel(sheet,wb);}}/**** @param sheet excel表sheet* @throws IOException*/private static void CreatNewExcel(HSSFSheet sheet,HSSFWorkbook wb) throws IOException {// 新的excel 文件名String excelName = "新的excel 文件名";// 创建新的excelHSSFWorkbook wbCreat = new HSSFWorkbook();HSSFSheet sheetCreat = wbCreat.createSheet("new sheet");// 复制源表中的合并单元格MergerRegion(sheetCreat, sheet);int firstRow = sheet.getFirstRowNum();int lastRow = sheet.getLastRowNum();for (int i = firstRow; i <= lastRow; i++) {// 创建新建excel Sheet的行HSSFRow rowCreat = sheetCreat.createRow(i);// 取得源有excel Sheet的行HSSFRow row = sheet.getRow(i);// 单元格式样HSSFCellStyle cellStyle = null;int firstCell = row.getFirstCellNum();int lastCell = row.getLastCellNum();for (int j = firstCell; j < lastCell; j++) {// 自动适应列宽貌似不起作用sheetCreat.autoSizeColumn(j);// new一个式样cellStyle = wbCreat.createCellStyle();// 设置边框线型cellStyle.setBorderTop(row.getCell(j).getCellStyle().getBorde rTop());cellStyle.setBorderBottom(row.getCell(j).getCellStyle().getBo rderBottom());cellStyle.setBorderLeft(row.getCell(j).getCellStyle().getBorde rLeft());cellStyle.setBorderRight(row.getCell(j).getCellStyle().getBord erRight());// 设置内容位置:例水平居中,居右，居工cellStyle.setAlignment(row.getCell(j).getCellStyle().getAlign ment());// 设置内容位置:例垂直居中,居上，居下cellStyle.setVerticalAlignment(row.getCell(j).getCellStyle().ge tVerticalAlignment());// 自动换行cellStyle.setWrapT ext(row.getCell(j).getCellStyle().getWrapTe xt());rowCreat.createCell(j).setCellStyle(cellStyle);// 设置单元格高度rowCreat.getCell(j).getRow().setHeight(row.getCell(j).getRo w().getHeight());// 单元格类型switch (row.getCell(j).getCellType()) {case HSSFCell.CELL_TYPE_STRING:String strVal = removeInternalBlank(row.getCell(j).getStringCellValue());rowCreat.getCell(j).setCellValue(strVal);break;case HSSFCell.CELL_TYPE_NUMERIC:rowCreat.getCell(j).setCellValue(row.getCell(j).getNumericC ellValue());break;case HSSFCell.CELL_TYPE_FORMULA:try {rowCreat.getCell(j).setCellValue(String.valueOf(row.getCell(j ).getNumericCellValue()));} catch (IllegalStateException e) {try {rowCreat.getCell(j).setCellValue(String.valueOf(row.getCell(j ).getRichStringCellValue()));} catch (Exception ex) {rowCreat.getCell(j).setCellValue("公式出错");}}break;}}}String strPath = "D:\\excelT o\\";//保存新EXCEL路径//检查同名excelName = checkFileName(strPath,excelName);FileOutputStream fileOut = new FileOutputStream(strPath + excelName +".xls");wbCreat.write(fileOut);fileOut.close();}/*** 检查此文件夹下有无同名，若有返回新文件名“文件名_重名”* @param strPath "D:\\excelTo\\"* @param checkFilename 文件名* @return 文件名*/private static String checkFileName(String strPath ,String checkFilename){File file = new File (strPath);for(File f : file.listFiles()){if(f.getName().equals(checkFilename)){checkFilename+=checkFilename+"_重名";checkFileName(strPath,checkFilename);break;}}return checkFilename;}/*** 复制原有sheet的合并单元格到新创建的sheet** @param sheetCreat 新创建sheet* @param sheet 原有的sheet*/private static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {int sheetMergerCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergerCount; i++) {Region mergedRegionAt = sheet.getMergedRegionAt(i);sheetCreat.addMergedRegion(mergedRegionAt);}}/*** 判断单元格在不在合并单元格范围内** @param sheet* @param intCellRow 被判断的单元格的行号* @param intCellCol 被判断的单元格的列号* @return TRUE 表示在，反之不在* @throws IOException*/private static boolean isInMergerCellRegion(HSSFSheetsheet,int intCellRow, int intCellCol) throws IOException {boolean retVal = false;int sheetMergerCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergerCount; i++) {CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);// 合并单元格CELL起始行int firstRow = cra.getFirstRow();// 合并单元格CELL起始列int firstCol = cra.getFirstColumn();// 合并单元格CELL结束行int lastRow = cra.getFirstColumn();// 合并单元格CELL结束列int lastCol = cra.getLastColumn();if (intCellRow >= firstRow && intCellRow <= lastRow) {if (intCellCol >= firstCol && intCellCol <= lastCol) {retVal = true;break;}}}return retVal;}/*** 去除字符串内部空格*/public static String removeInternalBlank(String s) {// System.out.println("bb:" + s);Pattern p = pile("\\s*|\t|\r|\n");Matcher m = p.matcher(s);char str[] = s.toCharArray(); StringBuffer sb = new StringBuffer(); for (int i = 0; i < str.length; i++) {if (str[i] == ' ') {sb.append(' ');} else {break;}}String after = m.replaceAll(""); return sb.toString() + after;}}。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
使用POI操作EXCEL
注：action中，首先应该设置字符集：
//设置字符集
response.setContentType("application/vnd.ms-excel;charset=GB2312");
一、生成excel文件：
OutputStream outStream=response.getOutputStream();//获取文件输出流
HSSFWorkbook wb = new HSSFWorkbook(); //声明一个工作薄
HSSFSheet sheet = wb.createSheet(); //生成一个工作表
wb.setSheetName(0, "第1页"); //为第一个工作表取名字
HSSFCellStyle normalStyle = 样式方法名(wb);// 设置样式
HSSFRow row = sheet.createRow(0); //创建第一行
row.setHeightInPoints(20); //设置行高
HSSFCell cell = row.createCell(0); //创建第一行的第一列
cell.setCellValue("第一列的值"); //为第一列赋值
cell.setCellStyle(normalStyle); //设置单元格的样式
cell.setCellFormula(string);//给单元格设公式
sheet.addMergedRegion(new CellRangeAddress(1,3,(short)1,
(short)1)); //跨行(列)：firstRow,lastRow,firstCol,lastCol
wb.write(outStream); //将文档对象写入文件输出流
outStream.close(); //关闭文件输出流
二、设置各种样式：
A.基本样式：
//生成一个样式
HSSFCellStyle style = wb.createCellStyle();
//设置背景颜色
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置边框样式
style.setBorderBottom(HSSFCellStyle.BORDER_double);//双线
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.RED.index);//设置为红色
//设置自动换行
style.setWrapText(true);
//设置对齐方式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直//设置列的style为锁定
style.setLocked(true);
//单元格内容的旋转的角度
style.setRotation(short_rotation);
//格式化日期
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//设置单元格数据格式
HSSFDataFormat df = wb.createDataFormat();
style.setDataFormat(df.getFormat("0.00%"));
//生成字体设置
HSSFFont font = wb.createFont(); // 生成另一个字体font.setColor(HSSFColor.VIOLET.index); //设置字体颜色font.setFontHeightInPoints((short)9); // 设置字体大小font.setFontName("宋体"); //设置字体样式
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
style.setFont(font); // 把字体应用到当前的样式
B.特殊样式：
//设置页边距
sheet.setMargin(HSSFSheet.BottomMargin,1.0);// 页边距（下）sheet.setMargin(HSSFSheet.LeftMargin,0.5);// 页边距（左）
sheet.setMargin(HSSFSheet.RightMargin,0.5);// 页边距（右）
sheet.setMargin(HSSFSheet.TopMargin,1.0);// 页边距（上）
//页面设置：横向打印 true为横向
sheet.getPrintSetup().setLandscape(true);
//设置列宽
sheet.setColumnWidth(0,1800); //第一个单元格的宽度是1800
//在页脚上添加页码
HSSFFooter footer = sheet.getFooter();
footer.setRight( "第" + HSSFFooter.page() + "页，共" +
HSSFFooter.numPages()+"页");
//页面设置的纸张大小是：A4纸
HSSFPrintSetup printSetup = sheet.getPrintSetup();
printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);。