reading large texts from Excel

xavigepe · 2010-09-21

Hello. I have an excel spreadsheet where one specific cell contains large text (more than 1024 characters). I'm trying to use excel buffer table and readsheet function to read this cell, but as it's more than 250 characters long, it returns an error. Does anybody know a solution for this?. I guess I could use streams or something like this, but I'm not very used to them and any help would be appreciated.

Thanks,

rmv_RU · 2010-09-22

You can save the workbook in XML format and read worksheet from it.
Sample Code

Name	DataType	Subtype	Length
XlWorkBook	Automation	Unknown Automation Server.Workbook	
xmlExcelBook	Automation	'Microsoft XML, v3.0'.DOMDocument30	
xmlSheetNode	Automation	'Microsoft XML, v3.0'.IXMLDOMNode	
xmlSheetNodes	Automation	'Microsoft XML, v3.0'.IXMLDOMNodeList	
xmlRowNode	Automation	'Microsoft XML, v3.0'.IXMLDOMNode	
xmlRowNodes	Automation	'Microsoft XML, v3.0'.IXMLDOMNodeList	
xmlCellNode	Automation	'Microsoft XML, v3.0'.IXMLDOMNode	
xmlCellNodes	Automation	'Microsoft XML, v3.0'.IXMLDOMNodeList	
i	Integer		
j	Integer		
k	Integer		




XlWorkBook.SaveAs(lxmlFileName, 46);
CREATE(xmlExcelBook);
xmlExcelBook.load(lxmlFileName);

xmlSheetNode:=xmlExcelBook.documentElement.selectSingleNode('./Worksheet[@ss:Name="' + lSheetName + '"]');
IF NOT ISCLEAR(xmlSheetNode) THEN BEGIN
  xmlRowNodes:=xmlSheetNode.selectNodes('./Table/Row');
  FOR j:=0 TO xmlRowNodes.length-1 DO BEGIN
    xmlRowNode:=xmlRowNodes.item(j);
    xmlCellNodes:=xmlRowNode.selectNodes('./Cell');
    FOR k:=0 TO xmlCellNodes.length-1 DO BEGIN
      xmlCellNode:=xmlCellNodes.item(k);
      .................................................
    end;
end;

XML Text Node ('Microsoft XML, v3.0'.IXMLDOMText) has SubstringData method, you can use it for reading large texts.

Howdy, Stranger!

Quick Links

Categories

reading large texts from Excel

Comments