📄 tmsutextdelim.pas
字号:
/// Utilities to load or save text delimited files.
unit tmsUTextDelim;
{$INCLUDE ..\FLXCOMPILER.INC}
interface
uses Classes, SysUtils, tmsUExcelAdapter, tmsUFlxNumberFormat, tmsUFlxMessages;
type
/// <summary>
/// Handles how to convert a column from text when importing a text file.
/// </summary>
XLSColumnImportTypes=(
/// <summary>
/// Try to convert it to a number, a date, etc.
/// </summary>
xct_general,
/// <summary>
/// Keep the column as text, even if it can be converted to a number or other things.
/// </summary>
xct_text,
/// <summary>
/// Do not import this column.
/// </summary>
xct_skip);
//***********************************************************************************
/// <summary>
/// Saves the Active sheet of an Excel file as a text delimited file.
/// </summary>
/// <remarks>
/// You will normally want to use <see cref="TFlexCelImport.SaveAsText@TFileName@Char" text="TFlexCelImport.SaveAsText" />
/// instead of this method to save whole files, or SaveRangeAsTextDelim to save a range of the active
/// sheet.
/// </remarks>
/// <param name="OutStream">Stream where we are going to save the file.</param>
/// <param name="Workbook">Workbook we want to save.</param>
/// <param name="Delim">Delimiter used in the file ("," or ";" for csv, #9
/// for tab delimited)</param>
procedure SaveAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char); {$IFDEF DELPHI2008UP}overload;{$ENDIF}
/// <summary>
/// Saves a range of cells in a text delimited file.
/// </summary>
/// <remarks>
/// To save the full sheet use SaveAsTextDelim or <see cref="TFlexCelImport.SaveAsText@TFileName@Char" text="TFlexCelImport.SaveAsText" />.
/// </remarks>
/// <param name="OutStream">Stream where we are going to save the file.</param>
/// <param name="Workbook">Workbook we want to save.</param>
/// <param name="Delim">Delimiter used in the file ("," or ";" for csv, #9
/// for tab delimited)</param>
/// <param name="Range">Encoding for the generated file.</param>
procedure SaveRangeAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char; const Range: TXlsCellRange); {$IFDEF DELPHI2008UP}overload;{$ENDIF}
/// <summary>
/// Imports a text file into an Excel file.
/// </summary>
/// <remarks>
/// Normally you can just use <see cref="TFlexCelImport.OpenText@TFileName@Char@array of XLSColumnImportTypes" text="TFlexCelImport.OpenFile Method" />
/// to import csv files, but this method gives you a little more control, like the row and column where
/// to import the data.<para></para>
/// Actually OpenText internally calls this method.
/// </remarks>
/// <param name="InStream">Stream with the text delimited file you want to import.</param>
/// <param name="Workbook">Excel file where there data will be imported.</param>
/// <param name="aDelim">Delimiter used in the file. This is normally ",", ";"
/// in csv files or #9 (tab) in tab delimited files.</param>
/// <param name="FirstRow">First row where the data will be imported. (1 based)</param>
/// <param name="FirstCol">First column where the data will be imported (1 based)</param>
/// <param name="ColumnFormats">Array of import types specifying how to import each column.</param>
procedure LoadFromTextDelim(const InStream: TStream; const Workbook: TExcelFile; const aDelim: Char; const FirstRow, FirstCol: integer; const ColumnFormats: array of XLSColumnImportTypes); {$IFDEF DELPHI2008UP}overload;{$ENDIF}
{$IFDEF DELPHI2008UP}
/// <summary>
/// Saves the Active sheet of an Excel file as a text delimited file.
/// </summary>
/// <remarks>
/// You will normally want to use <see cref="TFlexCelImport.SaveAsText@TFileName@Char" text="TFlexCelImport.SaveAsText" />
/// instead of this method to save whole files, or SaveRangeAsTextDelim to save a range of the active
/// sheet.<para></para>
/// <para></para>
/// <b>This method only works in Delphi 2009 or newer.</b>
/// </remarks>
/// <param name="OutStream">Stream where we are going to save the file.</param>
/// <param name="Workbook">Workbook we want to save.</param>
/// <param name="Delim">Delimiter used in the file ("," or ";" for csv, #9
/// for tab delimited)</param>
/// <param name="Encoding">Encoding for the saved file.</param>
procedure SaveAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char; const Encoding: TEncoding); overload;
/// <summary>
/// Saves a range of cells in a text delimited file.
/// </summary>
/// <remarks>
/// To save the full sheet use SaveAsTextDelim or <see cref="TFlexCelImport.SaveAsText@TFileName@Char" text="TFlexCelImport.SaveAsText" />.<para></para>
/// <para></para>
/// <b>This method only works in Delphi 2009 or newer.</b>
/// </remarks>
/// <param name="OutStream">Stream where we are going to save the file.</param>
/// <param name="Workbook">Workbook we want to save.</param>
/// <param name="Delim">Delimiter used in the file ("," or ";" for csv, #9
/// for tab delimited)</param>
/// <param name="Range">Range of cells we want to export.</param>
/// <param name="Encoding">Encoding for the generated file.</param>
procedure SaveRangeAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char; const Range: TXlsCellRange; const Encoding: TEncoding); overload;
/// <summary>
/// Imports a text file into an Excel file.
/// </summary>
/// <remarks>
/// Normally you can just use <see cref="TFlexCelImport.OpenText@TFileName@Char@array of XLSColumnImportTypes" text="TFlexCelImport.OpenFile Method" />
/// to import csv files, but this method gives you a little more control, like the row and column where
/// to import the data.<para></para>
/// <para></para>
/// <b>This overload of the method is only available in Delphi 2009 or newer.</b>
/// </remarks>
/// <param name="InStream">Stream with the text delimited file you want to import.</param>
/// <param name="Workbook">Excel file where there data will be imported.</param>
/// <param name="Delim">Delimiter used in the file. This is normally ",", ";"
/// in csv files or #9 (tab) in tab delimited files.</param>
/// <param name="FirstRow">First row where the data will be imported. (1 based)</param>
/// <param name="FirstCol">First column where the data will be imported (1 based)</param>
/// <param name="ColumnFormats">Array of import types specifying how to import each column.</param>
/// <param name="Encoding">Encoding used in the text file. (UTF8, Unicode, etc).</param>
/// <param name="DetectBOM">If true, FlexCel will try to detect the encoding from the BOM (Byte
/// order mark) in the file. Set it to true if the files have BOM.</param>
procedure LoadFromTextDelim(const InStream: TStream; const Workbook: TExcelFile; const Delim: Char; const FirstRow, FirstCol: integer; const ColumnFormats: array of XLSColumnImportTypes;
const Encoding: TEncoding; const DetectBOM: Boolean = false); overload;
/// <summary>
/// Imports a text file into an Excel file.
/// </summary>
/// <remarks>
/// Normally you can just use <see cref="TFlexCelImport.OpenText@TFileName@Char@array of XLSColumnImportTypes" text="TFlexCelImport.OpenFile Method" />
/// to import csv files, but this method gives you a little more control, like the row and column where
/// to import the data.<para></para>
/// <para></para>
/// <b>This overload of the method is only available in Delphi 2009 or newer.</b>
/// </remarks>
/// <param name="Sr">StreamReader with the text delimited file you want to import.</param>
/// <param name="Workbook">Excel file where there data will be imported.</param>
/// <param name="Delim">Delimiter used in the file. This is normally ",", ";"
/// in csv files or #9 (tab) in tab delimited files.</param>
/// <param name="FirstRow">First row where the data will be imported. (1 based)</param>
/// <param name="FirstCol">First column where the data will be imported (1 based)</param>
/// <param name="ColumnFormats">Array of import types specifying how to import each column.</param>
procedure LoadFromTextDelim(const Sr: TStreamReader; const Workbook: TExcelFile; const Delim: Char; const FirstRow, FirstCol: integer; const ColumnFormats: array of XLSColumnImportTypes); overload;
{$ENDIF}
//***********************************************************************************
implementation
function FlxQuotedStr(const S: string): string;
var
I: Integer;
begin
Result := S;
for I := Length(Result) downto 1 do
if Result[I] = '"' then Insert('"', Result, I);
Result := '"' + Result + '"';
end;
procedure SaveRangeAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char; const Range: TXlsCellRange
{$IFDEF DELPHI2008UP}; const Encoding: TEncoding {$ENDIF});
var
r,c: integer;
s: String; //UTF16 in D2009, AnsiString otherwise.
Color: integer;
{$IFDEF DELPHI2008UP}
Buff : TBytes;
{$ENDIF}
begin
for r:=Range.Top to Range.Bottom do
begin
for c:=Range.Left to Range.Right do
begin
Color := -1;
s:=XlsFormatValue1904(Workbook.CellValue[r,c],Workbook.FormatList[Workbook.CellFormat[r,c]].Format, Workbook.Options1904Dates, Color);
if (pos(Delim, s)>0) or (pos('"', s)>0) or (pos(#10,s)>0) or (pos(#13,s)>0) then
begin
s:=FlxQuotedStr(s);
end;
if c<Range.Right then s:=s+Delim else s:=s+#13#10;
{$IFDEF DELPHI2008UP}
Buff := Encoding.GetBytes(s);
OutStream.Write(Buff, Length(Buff));
{$ELSE}
OutStream.Write(s[1], Length(s));
{$ENDIF}
end;
end;
end;
procedure SaveAsTextDelim(const OutStream: TStream; const Workbook: TExcelFile; const Delim: Char
{$IFDEF DELPHI2008UP}; const Encoding: TEncoding {$ENDIF});
var
Range:TXlsCellRange;
begin
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -