poi 抽取execl表面数据源代码工具

signheart
关注文章
TinyMind专栏频道

开发中 ,导入导出execl避免不了数据类型格式的校验，在使用poi要使用抽取表面数据，poi暂时不支持单元格抽取，查询poi源码抽取工具类如下，如使用jxl就不必使用，jxl取出的单元格数据已是抽取后的表面数据
注：poi版本 - 3.10

接口ExeclExtractor 
  1. package com.dadi.oa.util.poi;
  3. import org.apache.poi.ss.usermodel.Cell;
  5. /**
  6.  * poi execl文本抽取接口
  7.  * @author ao.ouyang
  8.  *
  9.  */
  10. public interface ExeclExtractor {
  12. 	/**
  13. 	 * 抽取单元格文本
  14. 	 * @param cell
  15. 	 * @return
  16. 	 */
  17. 	public String getText(Cell cell);
  18. 	
  19. 	/**
  20. 	 * 公式结果
  21. 	 * @param formulasNotResults
  22. 	 */
  23. 	public void setFormulasNotResults(boolean formulasNotResults);
  24. 	
  25. 	/**
  26. 	 * 是否抽取注释
  27. 	 * @param includeCellComments
  28. 	 */
  29. 	public void setIncludeCellComments(boolean includeCellComments);
  30. }

03版抽取方法HSSFExeclExtractor 
  1. package com.dadi.oa.util.poi;
  2. import org.apache.poi.POIOLE2TextExtractor;
  3. import org.apache.poi.hssf.usermodel.HSSFCell;
  4. import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  5. import org.apache.poi.hssf.usermodel.HSSFComment;
  6. import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
  7. import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  8. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  9. import org.apache.poi.ss.formula.eval.ErrorEval;
  10. import org.apache.poi.ss.usermodel.Cell;
  11. import org.apache.poi.xssf.usermodel.XSSFCell;
  13. /**
  14.  * Poi操作工具类 03版<br/>
  15.  * 功能1：获取execl单元格显示的文本<br/>
  16.  * @author ao.ouyang
  17.  *
  18.  */
  19. public class HSSFExeclExtractor extends POIOLE2TextExtractor implements ExeclExtractor {
  21. 	private HSSFDataFormatter _formatter;
  22. 	private boolean _shouldEvaluateFormulas = true;
  23. 	private boolean _includeCellComments = false;
  25. 	public HSSFExeclExtractor(HSSFWorkbook wb) {
  26. 		super(wb);
  27. 		_formatter = new HSSFDataFormatter();
  28. 	}
  30. 	public void setFormulasNotResults(boolean formulasNotResults) {
  31. 		_shouldEvaluateFormulas = !formulasNotResults;
  32. 	}
  33. 	@Override
  34. 	public void setIncludeCellComments(boolean includeCellComments) {
  35. 		_includeCellComments = includeCellComments;
  36. 		
  37. 	}
  38. 	/**
  39. 	 * 获取单元格格式内容
  40. 	 * @param cell
  41. 	 * @return
  42. 	 */
  43. 	@Override
  44. 	public String getText(Cell cell) {
  45. 		HSSFCell hssfCell = (HSSFCell) cell;
  46. 		StringBuffer text = new StringBuffer();
  47. 		if(hssfCell != null) {
  48. 			switch(hssfCell.getCellType()) {
  49. 				case HSSFCell.CELL_TYPE_STRING:
  50. 					text.append(hssfCell.getRichStringCellValue().getString());
  51. 					break;
  52. 				case HSSFCell.CELL_TYPE_NUMERIC:
  53. 					text.append(
  54. 					      _formatter.formatCellValue(hssfCell)
  55. 					);
  56. 					break;
  57. 				case HSSFCell.CELL_TYPE_BOOLEAN:
  58. 					text.append(hssfCell.getBooleanCellValue());
  59. 					break;
  60. 				case HSSFCell.CELL_TYPE_ERROR:
  61. 					text.append(ErrorEval.getText(hssfCell.getErrorCellValue()));
  62. 					break;
  63. 				case HSSFCell.CELL_TYPE_FORMULA:
  64. 					if(!_shouldEvaluateFormulas) {
  65. 						text.append(hssfCell.getCellFormula());
  66. 					} else {
  67. 						switch(cell.getCachedFormulaResultType()) {
  68. 							case HSSFCell.CELL_TYPE_STRING:
  69. 								HSSFRichTextString str = hssfCell.getRichStringCellValue();
  70. 								if(str != null && str.length() > 0) {
  71. 									text.append(str.toString());
  72. 								}
  73. 								break;
  74. 							case HSSFCell.CELL_TYPE_NUMERIC:
  75. 							   HSSFCellStyle style = hssfCell.getCellStyle();
  76. 							   if(style == null) {
  77. 							      text.append( cell.getNumericCellValue() );
  78. 							   } else {
  79.                          text.append(
  80.                                _formatter.formatRawCellContents(
  81.                                      cell.getNumericCellValue(),
  82.                                      style.getDataFormat(),
  83.                                      style.getDataFormatString()
  84.                                )
  85.                          );
  86. 							   }
  87. 								break;
  88. 							case HSSFCell.CELL_TYPE_BOOLEAN:
  89. 								text.append(cell.getBooleanCellValue());
  90. 								break;
  91. 							case HSSFCell.CELL_TYPE_ERROR:
  92. 								text.append(ErrorEval.getText(cell.getErrorCellValue()));
  93. 								break;
  95. 						}
  96. 					}
  97. 					break;
  98. 			}
  100. 			// Output the comment, if requested and exists
  101. 			HSSFComment comment = hssfCell.getCellComment();
  102. 			if(_includeCellComments && comment != null) {
  103. 				// Replace any newlines with spaces, otherwise it
  104. 				//  breaks the output
  105. 				String commentText = comment.getString().getString().replace('\n', ' ');
  106. 				text.append(" Comment by "+comment.getAuthor()+": "+commentText);
  107. 			}
  108. 		}
  111. 		return text.toString();
  112. 	}
  114. 	@Override
  115. 	public String getText() {
  116. 		// TODO Auto-generated method stub
  117. 		return null;
  118. 	}
  120. 	
  122. }


07 版抽取方法XSSFExeclExtractor 
  1. package com.dadi.oa.util.poi;
  4. import java.io.IOException;
  5. import java.util.Iterator;
  6. import java.util.Locale;
  8. import org.apache.poi.POIXMLTextExtractor;
  9. import org.apache.poi.ss.usermodel.Cell;
  10. import org.apache.poi.ss.usermodel.CellStyle;
  11. import org.apache.poi.ss.usermodel.Comment;
  12. import org.apache.poi.ss.usermodel.DataFormatter;
  13. import org.apache.poi.xssf.usermodel.XSSFCell;
  14. import org.apache.poi.xssf.usermodel.XSSFSheet;
  15. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  18. /**
  19.  * Poi操作工具类 07版<br/>
  20.  * 功能1：获取execl单元格显示的文本<br/>
  21.  * @author ao.ouyang
  22.  *
  23.  */
  24. public class XSSFExeclExtractor extends POIXMLTextExtractor implements ExeclExtractor {
  25. 	 private Locale locale;
  26.     private boolean formulasNotResults = false;
  27.     private boolean includeCellComments = false;
  29.     public XSSFExeclExtractor(XSSFWorkbook workbook) {
  30.         super(workbook);
  31.     }
  32.     
  33.     
  34.     /**
  35. 	 * 获取单元格格式内容
  36. 	 * @param cell
  37. 	 * @return
  38. 	 */
  39.     public String getText(Cell cell) {
  40.     	XSSFCell xssfCell = (XSSFCell) cell;
  41.         DataFormatter formatter;
  42.         if(locale == null) {
  43.             formatter = new DataFormatter();
  44.         } else  {
  45.             formatter = new DataFormatter(locale);
  46.         }
  48.         StringBuffer text = new StringBuffer();
  50.         // Is it a formula one?
  51.         if(xssfCell!=null){
  52.         if(xssfCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
  53.             if (formulasNotResults) {
  54.                 text.append(xssfCell.getCellFormula());
  55.             } else {
  56.                 if (xssfCell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING) {
  57.                     handleStringCell(text, xssfCell);
  58.                 } else {
  59.                     handleNonStringCell(text, xssfCell, formatter);
  60.                 }
  61.             }
  62.         } else if(xssfCell.getCellType() == Cell.CELL_TYPE_STRING) {
  63.             handleStringCell(text, xssfCell);
  64.         } else {
  65.             handleNonStringCell(text, xssfCell, formatter);
  66.         }
  68.         // Output the comment, if requested and exists
  69.         Comment comment = xssfCell.getCellComment();
  70.         if(includeCellComments && comment != null) {
  71.             // Replace any newlines with spaces, otherwise it
  72.             //  breaks the output
  73.             String commentText = comment.getString().getString().replace('\n', ' ');
  74.             text.append(" Comment by ").append(comment.getAuthor()).append(": ").append(commentText);
  75.         }
  76.             
  77.         }
  78.         return text.toString();
  79.     }
  81.     private void handleStringCell(StringBuffer text, Cell cell) {
  82.         text.append(cell.getRichStringCellValue().getString());
  83.     }
  84.     private void handleNonStringCell(StringBuffer text, Cell cell, DataFormatter formatter) {
  85.         int type = cell.getCellType();
  86.         if (type == Cell.CELL_TYPE_FORMULA) {
  87.             type = cell.getCachedFormulaResultType();
  88.         }
  90.         if (type == Cell.CELL_TYPE_NUMERIC) {
  91.             CellStyle cs = cell.getCellStyle();
  93.             if (cs.getDataFormatString() != null) {
  94.                 text.append(formatter.formatRawCellContents(
  95.                         cell.getNumericCellValue(), cs.getDataFormat(), cs.getDataFormatString()
  96.                         ));
  97.                 return;
  98.             }
  99.         }
  101.     }
  103. 	@Override
  104. 	public void setFormulasNotResults(boolean formulasNotResults) {
  105. 		 this.formulasNotResults = formulasNotResults;
  106. 		
  107. 	}
  108. 	
  110. 	@Override
  111. 	public String getText() {
  112. 		// TODO Auto-generated method stub
  113. 		return null;
  114. 	}
  118. 	@Override
  119. 	public void setIncludeCellComments(boolean formulasNotResults) {
  120. 		 this.includeCellComments = includeCellComments;
  121. 	}
  123. }


优质内容筛选与推荐>>
1、数据类型之基础类型
2、R.java的不能识别控件的问题
3、dplyr 数据操作 常用函数（2）
4、java框架之springmvc
5、在 ASP.NET 网页中不经过回发而实现客户端回调(需要实现ICallbackEventHandler接口)


长按二维码向我转账

受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。

    阅读
    好看
    已推荐到看一看
    你的朋友可以在“发现”-“看一看”看到你认为好看的文章。
    已取消，“好看”想法已同步删除
    已推荐到看一看 和朋友分享想法
    最多200字，当前共字 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字，当前共字

    发送中

    网络异常，请稍后重试

    微信扫一扫
    关注该公众号

    0 │ 收藏 │ 举报