static void Main(string[] args)
{
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("test.xls");
ExcelExtractor extractor = new ExcelExtractor(workbook);
Console.Write(extractor.Text);
Console.Read();
}
public override string Parse()
{
if (!File.Exists(Context.Path))
throw new FileNotFoundException("File " + Context.Path + " is not found");
IWorkbook workbook = WorkbookFactory.Create(Context.Path);
bool extractHeaderFooter = false;
if (Context.Properties.ContainsKey("IncludeHeaderFooter"))
{
extractHeaderFooter = Utility.IsTrue(Context.Properties["IncludeHeaderFooter"]);
}
bool showCalculatedResult = false;
if (Context.Properties.ContainsKey("ShowCalculatedResult"))
{
showCalculatedResult = Utility.IsTrue(Context.Properties["ShowCalculatedResult"]);
}
bool includeSheetNames = true;
if (Context.Properties.ContainsKey("IncludeSheetNames"))
{
includeSheetNames = Utility.IsTrue(Context.Properties["IncludeSheetNames"]);
}
bool includeComment = true;
if (Context.Properties.ContainsKey("IncludeComments"))
{
includeComment = Utility.IsTrue(Context.Properties["IncludeComments"]);
}
if (workbook is XSSFWorkbook)
{
XSSFExcelExtractor extractor = new XSSFExcelExtractor((XSSFWorkbook)workbook);
extractor.SetIncludeHeadersFooters(extractHeaderFooter);
extractor.SetIncludeCellComments(includeComment);
extractor.SetIncludeSheetNames(includeSheetNames);
extractor.SetFormulasNotResults(!showCalculatedResult);
return extractor.Text;
}
else //if (workbook is HSSFWorkbook)
{
ExcelExtractor extractor = new ExcelExtractor((HSSFWorkbook)workbook);
extractor.IncludeHeaderFooter = extractHeaderFooter;
extractor.IncludeCellComments= includeComment;
extractor.IncludeSheetNames = includeSheetNames;
extractor.FormulasNotResults = !showCalculatedResult;
return extractor.Text;
}
}
public void TestComparedToOLE2()
{
// A fairly simple file - ooxml
XSSFExcelExtractor ooxmlExtractor = GetExtractor("SampleSS.xlsx");
ExcelExtractor ole2Extractor =
new ExcelExtractor(HSSFTestDataSamples.OpenSampleWorkbook("SampleSS.xls"));
POITextExtractor[] extractors =
new POITextExtractor[] { ooxmlExtractor, ole2Extractor };
for (int i = 0; i < extractors.Length; i++)
{
POITextExtractor extractor = extractors[i];
String text = Regex.Replace(extractor.Text,"[\r\t]", "");
Assert.IsTrue(text.StartsWith("First Sheet\nTest spreadsheet\n2nd row2nd row 2nd column\n"));
Regex pattern = new Regex(".*13(\\.0+)?\\s+Sheet3.*",RegexOptions.Compiled);
Assert.IsTrue(pattern.IsMatch(text));
}
}
public void Test51535()
{
byte[] data = HSSFITestDataProvider.Instance.GetTestDataFileContent("51535.xls");
HSSFWorkbook wbPOIFS = new HSSFWorkbook(new POIFSFileSystem(
new MemoryStream(data)).Root, false);
HSSFWorkbook wbNPOIFS = new HSSFWorkbook(new NPOIFSFileSystem(
new MemoryStream(data)).Root, false);
foreach (HSSFWorkbook wb in new HSSFWorkbook[] { wbPOIFS, wbNPOIFS })
{
Assert.AreEqual(3, wb.NumberOfSheets);
// Check directly
ISheet s = wb.GetSheetAt(0);
Assert.AreEqual("Top Left Cell", s.GetRow(0).GetCell(0).StringCellValue);
Assert.AreEqual("Top Right Cell", s.GetRow(0).GetCell(255).StringCellValue);
Assert.AreEqual("Bottom Left Cell", s.GetRow(65535).GetCell(0).StringCellValue);
Assert.AreEqual("Bottom Right Cell", s.GetRow(65535).GetCell(255).StringCellValue);
// Extract and check
ExcelExtractor ex = new ExcelExtractor(wb);
String text = ex.Text;
Assert.IsTrue(text.Contains("Top Left Cell"));
Assert.IsTrue(text.Contains("Top Right Cell"));
Assert.IsTrue(text.Contains("Bottom Left Cell"));
Assert.IsTrue(text.Contains("Bottom Right Cell"));
}
}
public void TestConstructors()
{
POIFSFileSystem fs;
HSSFWorkbook wb;
try
{
fs = new POIFSFileSystem(_samples.OpenResourceAsStream("TestUnicode.xls"));
wb = new HSSFWorkbook(fs);
}
catch (IOException e)
{
throw new Exception("TestConstructors", e);
}
ExcelExtractor excelExt = new ExcelExtractor(wb);
String fsText = (new HPSFPropertiesExtractor(fs)).Text;
String hwText = (new HPSFPropertiesExtractor(wb)).Text;
String eeText = (new HPSFPropertiesExtractor(excelExt)).Text;
Assert.AreEqual(fsText, hwText);
Assert.AreEqual(fsText, eeText);
Assert.IsTrue(fsText.IndexOf("AUTHOR = marshall") > -1);
Assert.IsTrue(fsText.IndexOf("TITLE = Titel: \u00c4h") > -1);
}
/// <summary>
/// 测试NPOI组件方法得到*.xls文件第一张‘Sheet’的内容；未被用到的方法。
/// </summary>
/// <param name="FileName">文件完整的路径名，如D:\\test.xls</param>
/// <returns>返回FileName第一张‘Sheet’的内容</returns>
public static string npoiExtractStringTesting(string FileName)
{
try
{
FileInfo files = new FileInfo(FileName);
using (FileStream fs = files.Open(FileMode.Open))
{
HSSFWorkbook HBook = new HSSFWorkbook(fs);
ExcelExtractor extractor = new ExcelExtractor(HBook);
return extractor.Text;
}
}
catch
{
FormMain.isSqlLangCreatedSuccessful = false;
return null;
}
}
public void TestWithEmbededInOwn()
{
POIFSFileSystem fs = new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("excel_with_embeded.xls")
);
DirectoryNode dirA = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B5");
DirectoryNode dirB = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B4");
HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
ExcelExtractor exA = new ExcelExtractor(wbA);
ExcelExtractor exB = new ExcelExtractor(wbB);
Assert.AreEqual("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
exA.Text);
Assert.AreEqual("Sample Excel", exA.SummaryInformation.Title);
Assert.AreEqual("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
exB.Text);
Assert.AreEqual("Sample Excel 2", exB.SummaryInformation.Title);
// And the base file too
ExcelExtractor ex = new ExcelExtractor(fs);
Assert.AreEqual("Sheet1\nI have lots of embeded files in me\nSheet2\nSheet3\n",
ex.Text);
Assert.AreEqual("Excel With Embeded", ex.SummaryInformation.Title);
}
public void TestWithEmbeded()
{
POIFSFileSystem fs = new POIFSFileSystem(
POIDataSamples.GetDocumentInstance().OpenResourceAsStream("word_with_embeded.doc")
);
DirectoryNode objPool = (DirectoryNode)fs.Root.GetEntry("ObjectPool");
DirectoryNode dirA = (DirectoryNode)objPool.GetEntry("_1269427460");
DirectoryNode dirB = (DirectoryNode)objPool.GetEntry("_1269427461");
HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
ExcelExtractor exA = new ExcelExtractor(wbA);
ExcelExtractor exB = new ExcelExtractor(wbB);
Assert.AreEqual("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
exA.Text);
Assert.AreEqual("Sample Excel", exA.SummaryInformation.Title);
Assert.AreEqual("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
exB.Text);
Assert.AreEqual("Sample Excel 2", exB.SummaryInformation.Title);
}