Excel Import
Posted: 6 Feb 2018 15:44
I am using CE 7.4. In order to import content of excel spreadsheet cells into client application I am looking for simple routine that works.
Visual Prolog Discussion forums
https://discuss.visual-prolog.com/
Code: Select all
class predicates
readFileToSSS : ( string QFN ) -> string_list_list determ. /* QFN = fully Qualified File Name */
clauses
readFileToSSS(QFN) = _ :-
not( file::existExactFile(QFN) ),
!,
stdio::write("\nNo such file: ", QFN ),
fail.
readFileToSSS( QFN ) = DataSSS :-
FileString = file::readString(QFN),
DataRows = string::split( FileString, "\n" ),
DataSSS = [ SS || S in DataRows,
SS = string::split(S,"\t"),
SS <> [""] /* skip empty rows */ ].
Code: Select all
% Create some custom predicate to access the file strings;
% you could also read the file a line at a time and process it.
class predicates
readSepDelimFileToSSS : ( string QFN ) -> string_list_list determ.
clauses
readSepDelimFileToSSS(QFN) = _ :-
not( file::existExactFile(QFN) ),
!,
stdio::write("\nNo such file: ", QFN ),
fail.
readSepDelimFileToSSS(QFN) = DataSSS :-
FileString = file::readString(QFN),
DataRows = string::split( FileString, "\n" ),
Buff = outputStream_string::new(),
DataSSS = [ DataSS || RowStr in DataRows, DataSS = splitStringSepDelim( Buff, RowStr, '\t', '"' ) ],
Buff:close().
% This predicate does the parsing using specified separator and delimiter characters.
% Note that it uses a string buffer created and closed by the calling predicate.
constants
splitChar : char = '|'. % This may be any character that is NOT in the string to be parsed
class predicates
splitStringSepDelim : ( outputStream_string Buffer, string InStr, char Separator, char Delimiter ) -> string_list.
% NOTE: Does NOT handle embedded escaped characters such as '/t'
clauses
splitStringSepDelim( Buff, S, Separ, Delim ) = SS :-
Buff:resetString(),
InDelimitedSubstr = varM::new(false),
foreach X = std::fromTo(0,string::length(S)-1) do
C = string::subChar(S,X),
CurrState = InDelimitedSubstr:value,
NowState = writeToBuffer( Buff, CurrState, C, Separ, Delim ),
if NowState <> CurrState then InDelimitedSubstr:value := NowState end if
end foreach,
SS = string::split( Buff:getString(), toString(splitChar) ).
class predicates
writeToBuffer : ( outputStream_string Buffer, boolean InAQuoteTF, char CurrChar, char Separ, char Delim ) -> boolean InQuoteNow.
clauses
%-- NOT IN a delimited part of the string
writeToBuffer( Buff, false, SEPAR, SEPAR, _Delim ) = false :- Buff:write(splitChar), !.
writeToBuffer( _Buff, false, DELIM, _Separ, DELIM ) = true :- !.
writeToBuffer( Buff, false, Char, _Separ, _Delim ) = false :- Buff:write(Char), !.
%-- IN a delimited part of the string
writeToBuffer( Buff, true, SEPAR, SEPAR, _Delim ) = true :- Buff:write(SEPAR), !.
writeToBuffer( _Buff, true, DELIM, _Separ, DELIM ) = false :- !.
writeToBuffer( Buff, true, Char, _Separ, _Delim ) = true :- Buff:write(Char).