资源描述：
《poi操作excel技巧》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
POI操作EXCEL技巧Java代码1.1.创建工作簿 (WORKBOOK) 2. 3. HSSFWorkbook wb = new HSSFWorkbook(); 4. 5. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 6. 7. wb.write(fileOut); 8. 9. fileOut.close(); 10. 11.2.创建工作表（SHEET） 12. 13. HSSFWorkbook wb = new HSSFWorkbook(); 14. 15. HSSFSheet sheet1 = wb.createSheet("new sheet"); 16. 17. HSSFSheet sheet2 = wb.createSheet("second sheet"); 18. 19. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 20. 21. wb.write(fileOut); 22. 23. fileOut.close(); 24. 25.3.创建单元格(CELL) 26. 27. HSSFWorkbook wb = new HSSFWorkbook(); 28. 29. HSSFSheet sheet = wb.createSheet("new sheet"); 30. 31. // Create a row and put some cells in it. Rows are 0 based. 32. 33. HSSFRow row = sheet.createRow((short)0); 34. 35. // Create a cell and put a value in it. 36. 37. HSSFCell cell = row.createCell((short)0); 38. 1. cell.setCellValue(1); 2. 3. // Or do it on one line. 4. 5. row.createCell((short)1).setCellValue(1.2); 6. 7. row.createCell((short)2).setCellValue("This is a string"); 8. 9. row.createCell((short)3).setCellValue(true); 10. 11. // Write the output to a file 12. 13. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 14. 15. wb.write(fileOut); 16. 17. fileOut.close(); 18. 19.4.创建指定单元格式的单元格 20. 21. HSSFWorkbook wb = new HSSFWorkbook(); 22. 23. HSSFSheet sheet = wb.createSheet("new sheet"); 24. 25. // Create a row and put some cells in it. Rows are 0 based. 26. 27. HSSFRow row = sheet.createRow((short)0); 28. 29. // Create a cell and put a date value in it. The first cell is not styled 30. 31. // as a date. 32. 33. HSSFCell cell = row.createCell((short)0); 34. 35. cell.setCellValue(new Date()); 36. 37. // we style the second cell as a date (and time). It is important to 38. 39. // create a new cell style from the workbook otherwise you can end up 40. 41. // modifying the built in style and effecting not only this cell but other cells. 42. 43. HSSFCellStyle cellStyle = wb.createCellStyle(); 44. 1. cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 2. 3. cell = row.createCell((short)1); 4. 5. cell.setCellValue(new Date()); 6. 7. cell.setCellStyle(cellStyle); 8. 9. // Write the output to a file 10. 11. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 12. 13. wb.write(fileOut); 14. 15. fileOut.close(); 16. 17.5. 单元格的不同格式 18. 19. HSSFWorkbook wb = new HSSFWorkbook(); 20. 21. HSSFSheet sheet = wb.createSheet("new sheet"); 22. 23. HSSFRow row = sheet.createRow((short)2); 24. 25. row.createCell((short) 0).setCellValue(1.1); 26. 27. row.createCell((short) 1).setCellValue(new Date()); 28. 29. row.createCell((short) 2).setCellValue("a string"); 30. 31. row.createCell((short) 3).setCellValue(true); 32. 33. row.createCell((short) 4).setCellType(HSSFCell.CELL_TYPE_ERROR); 34. 35. // Write the output to a file 36. 37. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 38. 39. wb.write(fileOut); 40. 41. fileOut.close(); 42. 43.6.单元格的不通对齐方式 44. 1. public static void main(String[] args) 2. 3. throws IOException 4. 5. { 6. 7. HSSFWorkbook wb = new HSSFWorkbook(); 8. 9. HSSFSheet sheet = wb.createSheet("new sheet"); 10. 11. HSSFRow row = sheet.createRow((short) 2); 12. 13. createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER); 14. 15. createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION); 16. 17. createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL); 18. 19. createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL); 20. 21. createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY); 22. 23. createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT); 24. 25. createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT); 26. 27. // Write the output to a file 28. 29. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 30. 31. wb.write(fileOut); 32. 33. fileOut.close(); 34. 35. } 36. 37. /** 38. 39. * Creates a cell and aligns it a certain way. 40. 41. * 42. 43. * @param wb the workbook 1. 2. * @param row the row to create the cell in 3. 4. * @param column the column number to create the cell in 5. 6. * @param align the alignment for the cell. 7. 8. */ 9. 10. private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) 11. 12. { 13. 14. HSSFCell cell = row.createCell(column); 15. 16. cell.setCellValue("Align It"); 17. 18. HSSFCellStyle cellStyle = wb.createCellStyle(); 19. 20. cellStyle.setAlignment(align); 21. 22. cell.setCellStyle(cellStyle); 23. 24. } 25. 26.7.单元格的边框设置 27. 28.Working with borders 29. 30. HSSFWorkbook wb = new HSSFWorkbook(); 31. 32. HSSFSheet sheet = wb.createSheet("new sheet"); 33. 34. // Create a row and put some cells in it. Rows are 0 based. 35. 36. HSSFRow row = sheet.createRow((short) 1); 37. 38. // Create a cell and put a value in it. 39. 40. HSSFCell cell = row.createCell((short) 1); 41. 42. cell.setCellValue(4); 43. 1. // Style the cell with borders all around. 2. 3. HSSFCellStyle style = wb.createCellStyle(); 4. 5. style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 6. 7. style.setBottomBorderColor(HSSFColor.BLACK.index); 8. 9. style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 10. 11. style.setLeftBorderColor(HSSFColor.GREEN.index); 12. 13. style.setBorderRight(HSSFCellStyle.BORDER_THIN); 14. 15. style.setRightBorderColor(HSSFColor.BLUE.index); 16. 17. style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED); 18. 19. style.setTopBorderColor(HSSFColor.BLACK.index); 20. 21. cell.setCellStyle(style); 22. 23. // Write the output to a file 24. 25. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 26. 27. wb.write(fileOut); 28. 29. fileOut.close(); 30. 31.8.填充和颜色设置 32. 33. HSSFWorkbook wb = new HSSFWorkbook(); 34. 35. HSSFSheet sheet = wb.createSheet("new sheet"); 36. 37. // Create a row and put some cells in it. Rows are 0 based. 38. 39. HSSFRow row = sheet.createRow((short) 1); 40. 41. // Aqua background 42. 43. HSSFCellStyle style = wb.createCellStyle(); 44. 1. style.setFillBackgroundColor(HSSFColor.AQUA.index); 2. 3. style.setFillPattern(HSSFCellStyle.BIG_SPOTS); 4. 5. HSSFCell cell = row.createCell((short) 1); 6. 7. cell.setCellValue("X"); 8. 9. cell.setCellStyle(style); 10. 11. // Orange "foreground", foreground being the fill foreground not the font color. 12. 13. style = wb.createCellStyle(); 14. 15. style.setFillForegroundColor(HSSFColor.ORANGE.index); 16. 17. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 18. 19. cell = row.createCell((short) 2); 20. 21. cell.setCellValue("X"); 22. 23. cell.setCellStyle(style); 24. 25. // Write the output to a file 26. 27. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 28. 29. wb.write(fileOut); 30. 31. fileOut.close(); 32. 33.9.合并单元格操作 34. 35. HSSFWorkbook wb = new HSSFWorkbook(); 36. 37. HSSFSheet sheet = wb.createSheet("new sheet"); 38. 39. HSSFRow row = sheet.createRow((short) 1); 40. 41. HSSFCell cell = row.createCell((short) 1); 42. 43. cell.setCellValue("This is a test of merging"); 44. 1. sheet.addMergedRegion(new Region(1,(short)1,1,(short)2)); 2. 3. // Write the output to a file 4. 5. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 6. 7. wb.write(fileOut); 8. 9. fileOut.close(); 10. 11.10.字体设置 12. 13. HSSFWorkbook wb = new HSSFWorkbook(); 14. 15. HSSFSheet sheet = wb.createSheet("new sheet"); 16. 17. // Create a row and put some cells in it. Rows are 0 based. 18. 19. HSSFRow row = sheet.createRow((short) 1); 20. 21. // Create a new font and alter it. 22. 23. HSSFFont font = wb.createFont(); 24. 25. font.setFontHeightInPoints((short)24); 26. 27. font.setFontName("Courier New"); 28. 29. font.setItalic(true); 30. 31. font.setStrikeout(true); 32. 33. // Fonts are set into a style so create a new one to use. 34. 35. HSSFCellStyle style = wb.createCellStyle(); 36. 37. style.setFont(font); 38. 39. // Create a cell and put a value in it. 40. 41. HSSFCell cell = row.createCell((short) 1); 42. 43. cell.setCellValue("This is a test of fonts"); 44. 1. cell.setCellStyle(style); 2. 3. // Write the output to a file 4. 5. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 6. 7. wb.write(fileOut); 8. 9. fileOut.close(); 10. 11.11.自定义颜色 12. 13. HSSFWorkbook wb = new HSSFWorkbook(); 14. 15. HSSFSheet sheet = wb.createSheet(); 16. 17. HSSFRow row = sheet.createRow((short) 0); 18. 19. HSSFCell cell = row.createCell((short) 0); 20. 21. cell.setCellValue("Default Palette"); 22. 23. //apply some colors from the standard palette, 24. 25. // as in the previous examples. 26. 27. //we'll use red text on a lime background 28. 29. HSSFCellStyle style = wb.createCellStyle(); 30. 31. style.setFillForegroundColor(HSSFColor.LIME.index); 32. 33. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 34. 35. HSSFFont font = wb.createFont(); 36. 37. font.setColor(HSSFColor.RED.index); 38. 39. style.setFont(font); 40. 41. cell.setCellStyle(style); 42. 43. //save with the default palette 44. 1. FileOutputStream out = new FileOutputStream("default_palette.xls"); 2. 3. wb.write(out); 4. 5. out.close(); 6. 7. //now, let's replace RED and LIME in the palette 8. 9. // with a more attractive combination 10. 11. // (lovingly borrowed from freebsd.org) 12. 13. cell.setCellValue("Modified Palette"); 14. 15. //creating a custom palette for the workbook 16. 17. HSSFPalette palette = wb.getCustomPalette(); 18. 19. //replacing the standard red with freebsd.org red 20. 21. palette.setColorAtIndex(HSSFColor.RED.index, 22. 23. (byte) 153, //RGB red (0-255) 24. 25. (byte) 0, //RGB green 26. 27. (byte) 0 //RGB blue 28. 29. ); 30. 31. //replacing lime with freebsd.org gold 32. 33. palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102); 34. 35. //save with the modified palette 36. 37. // note that wherever we have previously used RED or LIME, the 38. 39. // new colors magically appear 40. 41. out = new FileOutputStream("modified_palette.xls"); 42. 43. wb.write(out); 1. 2. out.close(); 3. 4.12.读和重写EXCEL文件 5. 6. POIFSFileSystem fs = 7. 8. new POIFSFileSystem(new FileInputStream("workbook.xls")); 9. 10. HSSFWorkbook wb = new HSSFWorkbook(fs); 11. 12. HSSFSheet sheet = wb.getSheetAt(0); 13. 14. HSSFRow row = sheet.getRow(2); 15. 16. HSSFCell cell = row.getCell((short)3); 17. 18. if (cell == null) 19. 20. cell = row.createCell((short)3); 21. 22. cell.setCellType(HSSFCell.CELL_TYPE_STRING); 23. 24. cell.setCellValue("a test"); 25. 26. // Write the output to a file 27. 28. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 29. 30. wb.write(fileOut); 31. 32. fileOut.close(); 33. 34.13.在EXCEL单元格中使用自动换行 35. 36. HSSFWorkbook wb = new HSSFWorkbook(); 37. 38. HSSFSheet s = wb.createSheet(); 39. 40. HSSFRow r = null; 41. 42. HSSFCell c = null; 43. 44. HSSFCellStyle cs = wb.createCellStyle(); 1. 2. HSSFFont f = wb.createFont(); 3. 4. HSSFFont f2 = wb.createFont(); 5. 6. cs = wb.createCellStyle(); 7. 8. cs.setFont( f2 ); 9. 10. //Word Wrap MUST be turned on 11. 12. cs.setWrapText( true ); 13. 14. r = s.createRow( (short) 2 ); 15. 16. r.setHeight( (short) 0x349 ); 17. 18. c = r.createCell( (short) 2 ); 19. 20. c.setCellType( HSSFCell.CELL_TYPE_STRING ); 21. 22. c.setCellValue( "Use with word wrap on to create a new line" ); 23. 24. c.setCellStyle( cs ); 25. 26. s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) ); 27. 28. FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); 29. 30. wb.write( fileOut ); 31. 32. fileOut.close(); 33. 34.14.数字格式自定义 35. 36. HSSFWorkbook wb = new HSSFWorkbook(); 37. 38. HSSFSheet sheet = wb.createSheet("format sheet"); 39. 40. HSSFCellStyle style; 41. 42. HSSFDataFormat format = wb.createDataFormat(); 43. 44. HSSFRow row; 1. 2. HSSFCell cell; 3. 4. short rowNum = 0; 5. 6. short colNum = 0; 7. 8. row = sheet.createRow(rowNum++); 9. 10. cell = row.createCell(colNum); 11. 12. cell.setCellValue(11111.25); 13. 14. style = wb.createCellStyle(); 15. 16. style.setDataFormat(format.getFormat("0.0")); 17. 18. cell.setCellStyle(style); 19. 20. row = sheet.createRow(rowNum++); 21. 22. cell = row.createCell(colNum); 23. 24. cell.setCellValue(11111.25); 25. 26. style = wb.createCellStyle(); 27. 28. style.setDataFormat(format.getFormat("#,##0.0000")); 29. 30. cell.setCellStyle(style); 31. 32. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 33. 34. wb.write(fileOut); 35. 36. fileOut.close(); 37. 38.15.调整工作单位置 39. 40. HSSFWorkbook wb = new HSSFWorkbook(); 41. 42. HSSFSheet sheet = wb.createSheet("format sheet"); 43. 44. HSSFPrintSetup ps = sheet.getPrintSetup(); 1. 2. sheet.setAutobreaks(true); 3. 4. ps.setFitHeight((short)1); 5. 6. ps.setFitWidth((short)1); 7. 8. 9. 10. // Create various cells and rows for spreadsheet. 11. 12. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 13. 14. wb.write(fileOut); 15. 16. fileOut.close(); 17. 18.16.设置打印区域 19. 20. HSSFWorkbook wb = new HSSFWorkbook(); 21. 22. HSSFSheet sheet = wb.createSheet("Sheet1"); 23. 24. wb.setPrintArea(0, "$A$1:$C$2"); 25. 26. //sets the print area for the first sheet 27. 28. //Alternatively: 29. 30. //wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details) 31. 32. // Create various cells and rows for spreadsheet. 33. 34. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 35. 36. wb.write(fileOut); 37. 38. fileOut.close(); 39. 40.17.标注脚注 41. 42. HSSFWorkbook wb = new HSSFWorkbook(); 43. 1. HSSFSheet sheet = wb.createSheet("format sheet"); 2. 3. HSSFFooter footer = sheet.getFooter() 4. 5. footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() ); 6. 7. 8. 9. // Create various cells and rows for spreadsheet. 10. 11. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 12. 13. wb.write(fileOut); 14. 15. fileOut.close(); 16. 17.18.使用方便的内部提供的函数 18. 19. HSSFWorkbook wb = new HSSFWorkbook(); 20. 21. HSSFSheet sheet1 = wb.createSheet( "new sheet" ); 22. 23. // Create a merged region 24. 25. HSSFRow row = sheet1.createRow( (short) 1 ); 26. 27. HSSFRow row2 = sheet1.createRow( (short) 2 ); 28. 29. HSSFCell cell = row.createCell( (short) 1 ); 30. 31. cell.setCellValue( "This is a test of merging" ); 32. 33. Region region = new Region( 1, (short) 1, 4, (short) 4 ); 34. 35. sheet1.addMergedRegion( region ); 36. 37. // Set the border and border colors. 38. 39. final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED; 40. 41. HSSFRegionUtil.setBorderBottom( borderMediumDashed, 42. 1. region, sheet1, wb ); 2. 3. HSSFRegionUtil.setBorderTop( borderMediumDashed, 4. 5. region, sheet1, wb ); 6. 7. HSSFRegionUtil.setBorderLeft( borderMediumDashed, 8. 9. region, sheet1, wb ); 10. 11. HSSFRegionUtil.setBorderRight( borderMediumDashed, 12. 13. region, sheet1, wb ); 14. 15. HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 16. 17. HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 18. 19. HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 20. 21. HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb); 22. 23. // Shows some usages of HSSFCellUtil 24. 25. HSSFCellStyle style = wb.createCellStyle(); 26. 27. style.setIndention((short)4); 28. 29. HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style); 30. 31. HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell"); 32. 33. HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER); 34. 35. // Write out the workbook 36. 37. FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); 38. 39. wb.write( fileOut ); 1. 2. fileOut.close(); 3. 4.19.在工作单中移动行，调整行的上下位置 5. 6. HSSFWorkbook wb = new HSSFWorkbook(); 7. 8. HSSFSheet sheet = wb.createSheet("row sheet"); 9. 10. // Create various cells and rows for spreadsheet. 11. 12. // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5) 13. 14. sheet.shiftRows(5, 10, -5); 15. 16. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 17. 18. wb.write(fileOut); 19. 20. fileOut.close(); 21. 22.20.选种指定的工作单 23. 24. HSSFWorkbook wb = new HSSFWorkbook(); 25. 26. HSSFSheet sheet = wb.createSheet("row sheet"); 27. 28. sheet.setSelected(true); 29. 30. // Create various cells and rows for spreadsheet. 31. 32. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 33. 34. wb.write(fileOut); 35. 36. fileOut.close(); 37. 38.21.工作单的放大缩小 39. 40. HSSFWorkbook wb = new HSSFWorkbook(); 41. 42. HSSFSheet sheet1 = wb.createSheet("new sheet"); 43. 44. sheet1.setZoom(3,4); // 75 percent magnification 1. 2. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 3. 4. wb.write(fileOut); 5. 6. fileOut.close(); 7. 8.22.头注和脚注 9. 10. HSSFWorkbook wb = new HSSFWorkbook(); 11. 12. HSSFSheet sheet = wb.createSheet("new sheet"); 13. 14. HSSFHeader header = sheet.getHeader(); 15. 16. header.setCenter("Center Header"); 17. 18. header.setLeft("Left Header"); 19. 20. header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + 21. 22. HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); 23. 24. FileOutputStream fileOut = new FileOutputStream("workbook.xls"); 25. 26. wb.write(fileOut); 27. 28. fileOut.close(); 29. 30.//-------------------------------以上实例代码均来自官方网站 31. 32.//-------------------------------POI中使用的颜色是用颜色索引来实现，如下： 33. 34. /* 35. 36. * 颜色对照表 数字代表颜色索引 37. 38. 8： BLACK 39. 40. 60： BROWN 41. 42. 59： OLIVE_GREEN 43. 1. 58： DARK_GREEN 2. 3. 56： DARK_TEAL 4. 5. 18： DARK_BLUE 6. 7. 32： DARK_BLUE 8. 9. 62： INDIGO 10. 11. 63： GREY_80_PERCENT 12. 13. 53： ORANGE 14. 15. 19： DARK_YELLOW 16. 17. 17： GREEN 18. 19. 21： TEAL 20. 21. 38： TEAL 22. 23. 12： BLUE 24. 25. 39： BLUE 26. 27. 54： BLUE_GREY 28. 29. 23： GREY_50_PERCENT 30. 31. 10： RED 32. 33. 52： LIGHT_ORANGE 34. 35. 50： LIME 36. 37. 57： SEA_GREEN 38. 39. 49： AQUA 40. 41. 48： LIGHT_BLUE 42. 43. 20： VIOLET 44. 1. 36： VIOLET 2. 3. 55： GREY_40_PERCENT 4. 5. 14： PINK 6. 7. 33： PINK 8. 9. 51： GOLD 10. 11. 13： YELLOW 12. 13. 34： YELLOW 14. 15. 11： BRIGHT_GREEN 16. 17. 35： BRIGHT_GREEN 18. 19. 15： TURQUOISE 20. 21. 35： TURQUOISE 22. 23. 16： DARK_RED 24. 25. 37： DARK_RED 26. 27. 40： SKY_BLUE 28. 29. 61： PLUM 30. 31. 25： PLUM 32. 33. 22： GREY_25_PERCENT 34. 35. 45： ROSE 36. 37. 43： LIGHT_YELLOW 38. 39. 42： LIGHT_GREEN 40. 41. 41： LIGHT_TURQUOISE 42. 43. 27：LIGHT_TURQUOISE 44. 1. 44： PALE_BLUE 2. 3. 46： LAVENDER 4. 5. 9： WHITE 6. 7. 24： CORNFLOWER_BLUE 8. 9. 26： LEMON_CHIFFON 10. 11. 25： MAROON 12. 13. 28： ORCHID 14. 15. 29： CORAL 16. 17. 30： ROYAL_BLUE 18. 19. 31： LIGHT_CORNFLOWER_BLUE 20. 21. */ 22. 23.//----------------------------------------------------你可以按上面的方法来自定义颜色 24. 25. /* 26. 27. * 自定义颜色，去掉注释，贴加，其他则查看颜色对照表 28. 29. HSSFPalette palette = this.getCustomPalette(); 30. 31. palette.setColorAtIndex(idx, 32. 33. i, //RGB red (0-255) 34. 35. j, //RGB green 36. 37. k //RGB blue 38. 39. ); 40. 41. */ 11月30日POI操作Excel文档－基础篇关键字: poi 一．POI简介JakartaPOI是apache的子项目，目标是处理ole2对象。它提供了一组操纵Windows文档的JavaAPI目前比较成熟的是HSSF接口，处理MSExcel（97-2002）对象。它不象我们仅仅是用csv生成的没有格式的可以由Excel转换的东西，而是真正的Excel对象，你可以控制一些属性如sheet,cell等等。二．HSSF概况HSSF是HorribleSpreadSheetFormat的缩写，也即“讨厌的电子表格格式”。也许HSSF的名字有点滑稽，就本质而言它是一个非常严肃、正规的API。通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。前者很好理解，后者比较抽象，但操作效率要高得多。三．开始编码1．准备工作要求:JDK1.4+POI开发包可以到http://www.apache.org/dyn/closer.cgi/jakarta/poi/最新的POI工具包2．EXCEL结构HSSFWorkbookexcell文档对象介绍HSSFSheetexcell的表单HSSFRowexcell的行HSSFCellexcell的格子单元HSSFFontexcell字体HSSFName名称HSSFDataFormat日期格式在poi1.7中才有以下2项：HSSFHeadersheet头 HSSFFootersheet尾和这个样式HSSFCellStylecell样式辅助操作包括HSSFDateUtil日期HSSFPrintSetup打印HSSFErrorConstants错误信息表3．具体用法实例（采用usermodel）如何读Excel读取Excel文件时，首先生成一个POIFSFileSystem对象，由POIFSFileSystem对象构造一个HSSFWorkbook，该HSSFWorkbook对象就代表了Excel文档。下面代码读取上面生成的Excel文件写入的消息字串：代码1.POIFSFileSystemfs=newPOIFSFileSystem(newFileInputStream("d:test.xls")); 2.HSSFWorkbook wb=newHSSFWorkbook(fs); 3. }catch(IOExceptione){ 4. e.printStackTrace(); 5. } 6. HSSFSheetsheet=wb.getSheetAt(0); 7. HSSFRowrow=sheet.getRow(0); 8. HSSFCellcell=row.getCell((short)0); 9. Stringmsg=cell.getStringCellValue(); 如何写excel，将excel的第一个表单第一行的第一个单元格的值写成“atest”。代码1.POIFSFileSystemfs=newPOIFSFileSystem(newFileInputStream("workbook.xls")); 2.3. HSSFWorkbookwb=newHSSFWorkbook(fs); 4.5. HSSFSheetsheet=wb.getSheetAt(0); 6.7. HSSFRowrow=sheet.getRow(0); 8. 1. HSSFCellcell=row.getCell((short)0); 2.3. cell.setCellValue("atest"); 4.5.//Writetheoutputtoafile 6.7. FileOutputStreamfileOut=newFileOutputStream("workbook.xls"); 8.9. wb.write(fileOut); 10.11.fileOut.close(); 4．可参考文档POI主页：http://jakarta.apache.org/poi/，初学者如何快速上手使用POIHSSFhttp://jakarta.apache.org/poi/hssf/quick-guide.html。代码例子http://blog.java-cn.com/user1/6749/archives/2005/18347.html里面有很多例子代码，可以很方便上手。四．使用心得POIHSSF的usermodel包把Excel文件映射成我们熟悉的结构，诸如Workbook、Sheet、Row、Cell等，它把整个结构以一组对象的形式保存在内存之中，便于理解，操作方便，基本上能够满足我们的要求，所以说这个一个不错的选择。11月30日POI操作Excel文档－中级篇1、遍历workbook代码1.//load源文件2.POIFSFileSystemfs=newPOIFSFileSystem(newFileInputStream(filePath)); 3.HSSFWorkbookwb=newHSSFWorkbook(fs); 4.for(inti=0;i
