Poi操作Excel

大家注意，请用最新的POI开发包进行操作，版本至少要不低于3.2，这样才能顺利完成以下所有操作。POI3.2下载地址是：http://apache.freelamp.com/poi/release/bin/poi-bin-3.2-FINAL-20081019.tar.gz

创建一个新的Workbook

     HSSFWorkbook wb = new HSSFWorkbook();

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

创建一个新的 Sheet

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet1 = wb.createSheet("new sheet");

     HSSFSheet sheet2 = wb.createSheet("second sheet");

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

创建单元格 Cells

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

// 创建一个行row同时在上面设置一些单元格，注意，行是从0开始,这里创建第一行。

HSSFRow row = sheet.createRow((short)0);

     // 创建单元格并为他设置一个值，注意，单元格也是从0开始

     HSSFCell cell = row.createCell((short)0);

     cell.setCellValue(1);

     // 可以在同一行创建多个单元格.

     row.createCell((short)1).setCellValue(1.2);

     row.createCell((short)2).setCellValue("This is a string");

     row.createCell((short)3).setCellValue(true);

     // 将这些数据输出为Excel

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

创建关于时间的单元格 Cells

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     //创建一个行row同时在上面设置一些单元格，注意，行是从0开始,这里创建第一行

     HSSFRow row = sheet.createRow((short)0);

     // 创建一个单元格并为其设置时间值，第一个单元格是原始时间值Double类型，没有格式

     HSSFCell cell = row.createCell((short)0);

     cell.setCellValue(new Date());

     // 现在将第二个单元格格式化为日期+时间.

// 通过workbook创建一个新的单元格风格（cell style）是很重要的  

// 否则你在修改这个单元格的风格的时候可能会影响到其他单元格的风格。

HSSFCellStyle cellStyle = wb.createCellStyle();

     cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

     cell = row.createCell((short)1);

     cell.setCellValue(new Date());

     cell.setCellStyle(cellStyle);

     // 写出文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

操作不同类型的单元格

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     HSSFRow row = sheet.createRow((short)2);

     row.createCell((short) 0).setCellValue(1.1);

     row.createCell((short) 1).setCellValue(new Date());

     row.createCell((short) 2).setCellValue("a string");

     row.createCell((short) 3).setCellValue(true);

     row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR);

     // 写出文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

fileOut.close();

//一点废话：由此可见，在上面的关于日期的格式化我们不一定要通过poi来做，我们可以将数据都格式化好了以后，转换成String类型来写入Excel，这样从一定程度上统一写入单元格的代码，实现代码复用。

示范不同的队列选项（也就是单元格内容居左、居右等等）

     public static void main(String[] args)

             throws IOException

     {

         HSSFWorkbook wb = new HSSFWorkbook();

         HSSFSheet sheet = wb.createSheet("new sheet");

         HSSFRow row = sheet.createRow((short) 2);

         createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);

         createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);

         createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);

         createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);

         createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);

         createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);

         createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

         // 写入文件

         FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

         wb.write(fileOut);

         fileOut.close();

     }

     

     private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)

     {

         HSSFCell cell = row.createCell(column);

         cell.setCellValue("Align It");

         HSSFCellStyle cellStyle = wb.createCellStyle();

         cellStyle.setAlignment(align);

         cell.setCellStyle(cellStyle);

     }

操作边框

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     // 创建一行（row）并为其设置单元格，行从0开始.

     HSSFRow row = sheet.createRow((short) 1);

     // 创建单元格并为其设置数值.

     HSSFCell cell = row.createCell((short) 1);

     cell.setCellValue(4);

     // 在单元格周围设置边框.

     HSSFCellStyle style = wb.createCellStyle();

     style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

     style.setBottomBorderColor(HSSFColor.BLACK.index);

     style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

     style.setLeftBorderColor(HSSFColor.GREEN.index);

     style.setBorderRight(HSSFCellStyle.BORDER_THIN);

     style.setRightBorderColor(HSSFColor.BLUE.index);

     style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);

     style.setTopBorderColor(HSSFColor.BLACK.index);

     cell.setCellStyle(style);

     // 写入文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

迭代遍历行及其单元格

有时，我们喜欢遍历一个Sheet的所有行，或者一行的所有单元格，这时可以通过循环来遍历还是比较简单的。

幸运的是，我们这有个非常简单的方法。HSSFRow定义了一个CellIterator的内部类来迭代遍历所有的单元格（通过调用row.celIterator获取），而且HSSFSheet也提供了一个rowIterator方法给出了遍历所有行的迭代器。

(Unfortunately, due to the broken and backwards-incompatible way that Java 5 foreach loops were implemented, it isn't possible to use them on a codebase that supports Java 1.4, as POI does)

          HSSFSheet sheet = wb.getSheetAt(0);

          for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {

                  HSSFRow row = (HSSFRow)rit.next();

                  for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {

                           HSSFCell cell = (HSSFCell)cit.next();

                           // Do something here

                  }

          }

          HSSFSheet sheet = wb.getSheetAt(0);

          for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {

                  HSSFRow row = rit.next();

                  for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {

                           HSSFCell cell = cit.next();

                           // Do something here

                  }

          }

使用java5的特有的前端循环（foreach loops）遍历行和单元格- OOXML Branch Only

有时候我们需要遍历一个Sheet的所有行或者一行的所有列，这时如果使用java5或者更高版本的话，可以采用这种新的循环方式的处理。

幸运的是，这也非常简单。HSSFSheet和HSSFRow都实现了java.lang.Iterator接口来允许新的循环方式的运行。 HSSFRow允许通过CellIterator内部类来处理单元格的循环，HSSFSheet给出了rowIterator方法来遍历所有的行。

          HSSFSheet sheet = wb.getSheetAt(0);

          for (HSSFRow row : sheet.rowIterator()) {

                  for (HSSFCell cell : row.cellIterator()) {

                           // Do something here

                  }

          }

内容提取

对于大多数的内容提取需求，标准ExcelExtractor类应该能满足您所有的需求。

          InputStream inp = new FileInputStream("workbook.xls");

          HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));

          ExcelExtractor extractor = new ExcelExtractor(wb);

          extractor.setFormulasNotResults(true);

          extractor.setIncludeSheetNames(false);

          String text = extractor.getText();

填充和颜色

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     // 创建一行，并为其设置单元格，行从0开始 .

     HSSFRow row = sheet.createRow((short) 1);

     // Aqua 背景色

     HSSFCellStyle style = wb.createCellStyle();

     style.setFillBackgroundColor(HSSFColor.AQUA.index);

     style.setFillPattern(HSSFCellStyle.BIG_SPOTS);

     HSSFCell cell = row.createCell((short) 1);

     cell.setCellValue("X");

     cell.setCellStyle(style);

     // Orange "前景色".

     style = wb.createCellStyle();

     style.setFillForegroundColor(HSSFColor.ORANGE.index);

     style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

     cell = row.createCell((short) 2);

     cell.setCellValue("X");

     cell.setCellStyle(style);

     // 写入文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

合并单元格

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     HSSFRow row = sheet.createRow((short) 1);

     HSSFCell cell = row.createCell((short) 1);

     cell.setCellValue("This is a test of merging");

     sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

     // 写入文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

一点废话：关于new Region(1,(short)1,1,(short)2)：四个参数分别为起始行、起始列、终止行、终止列，如果要在合并的单元格中写入数据，首先在起始行列的单元格中写入内容，之后从起始行列处开始拉伸合并单元格到终止行列。

字体设置

     HSSFWorkbook wb = new HSSFWorkbook();

     HSSFSheet sheet = wb.createSheet("new sheet");

     //创建行

     HSSFRow row = sheet.createRow((short) 1);

     // 创建一个系的呢字体并设置其属性.

     HSSFFont font = wb.createFont();

     font.setFontHeightInPoints((short)24);

     font.setFontName("Courier New");

     font.setItalic(true);

     font.setStrikeout(true);

     // 字体设置给一个HSSFCellStyle对象.

     HSSFCellStyle style = wb.createCellStyle();

     style.setFont(font);

     // 创建一个单元格并为其设置值

     HSSFCell cell = row.createCell((short) 1);

     cell.setCellValue("This is a test of fonts");

     cell.setCellStyle(style);

     //写入文件

     FileOutputStream fileOut = new FileOutputStream("c://workbook.xls");

     wb.write(fileOut);

     fileOut.close();

注意：一个workbook中字体数目被限制在32767个以内。你应该在程序中重用字体来代替为每个单元格创建新字体

下面写法是错误的:

         for (int i = 0; i < 10000; i++) {

             HSSFRow row = sheet.createRow(i);

             HSSFCell cell = row.createCell((short) 0);

             HSSFCellStyle style = workbook.createCellStyle();

             HSSFFont font = workbook.createFont();

             font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

             style.setFont(font);

             cell.setCellStyle(style);

         }

修正后:

         HSSFCellStyle style = workbook.createCellStyle();

         HSSFFont font = workbook.createFont();

         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

         style.setFont(font);

         for (int i = 0; i < 10000; i++) {

             HSSFRow row = sheet.createRow(i);

             HSSFCell cell = row.createCell((short) 0);

             cell.setCellStyle(style);

         }

一点废话：将创建字体的代码移出循环体。

如何读取超链接

     HSSFSheet sheet = workbook.getSheetAt(0);

     HSSFCell cell = sheet.getRow(0).getCell((short)0);

     HSSFHyperlink link = cell.getHyperlink();

     if(link != null){

         System.out.println(link.getAddress());

     }

如何设置超链接

     HSSFWorkbook wb = new HSSFWorkbook();

     //超链接的单元格风格

     //超链接默认的是蓝色底边框

     HSSFCellStyle hlink_style = wb.createCellStyle();

     HSSFFont hlink_font = wb.createFont();

     hlink_font.setUnderline(HSSFFont.U_SINGLE);

     hlink_font.setColor(HSSFColor.BLUE.index);

     hlink_style.setFont(hlink_font);

     HSSFCell cell;

     HSSFSheet sheet = wb.createSheet("Hyperlinks");

     //URL

     cell = sheet.createRow(0).createCell((short)0);

     cell.setCellValue("URL Link");

     HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);

     link.setAddress("http://poi.apache.org/");

     cell.setHyperlink(link);

     cell.setCellStyle(hlink_style);

     //链接到当前路径的一个文件

     cell = sheet.createRow(1).createCell((short)0);

     cell.setCellValue("File Link");

     link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE);

     link.setAddress("link1.xls");

     cell.setHyperlink(link);

     cell.setCellStyle(hlink_style);

     //链接到e-mail

     cell = sheet.createRow(2).createCell((short)0);

     cell.setCellValue("Email Link");

     link = new HSSFHyperlink(HSSFHyperlink.LINK_EMAIL);

     //note, if subject contains white spaces, make sure they are url-encoded

     link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");

     cell.setHyperlink(link);

     cell.setCellStyle(hlink_style);

     //链接到 workbook的某个地方

     //创建一个目标Sheet和单元格

     HSSFSheet sheet2 = wb.createSheet("Target Sheet");

     sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");

     cell = sheet.createRow(3).createCell((short)0);

     cell.setCellValue("Worksheet Link");

     link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);

     link.setAddress("'Target Sheet'!A1");

     cell.setHyperlink(link);

     cell.setCellStyle(hlink_style);

     FileOutputStream out = new FileOutputStream("c://hssf-links.xls");

     wb.write(out);

     out.close();