文档章节

TXLSReadWriteII5 单元格读写

vga
 vga
发布于 2016/10/12 06:46
字数 1050
阅读 179
收藏 0

TXLSReadWriteII5 单元格读写

复制代码

unit Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, XLSReadWriteII5, Grids, IniFiles, Xc12Utils5,
  XLSSheetData5, XPMan;

type TDoubleArray = array of double;

type
  TfrmMain = class(TForm)
    Panel1: TPanel;
    btnRead: TButton;
    btnWrite: TButton;
    edReadFilename: TEdit;
    edWriteFilename: TEdit;
    btnDlgOpen: TButton;
    btnDlgSave: TButton;
    dlgSave: TSaveDialog;
    dlgOpen: TOpenDialog;
    Button1: TButton;
    Grid: TStringGrid;
    btnAddCells: TButton;
    XLS: TXLSReadWriteII5;
    XPManifest1: TXPManifest;
    procedure btnCloseClick(Sender: TObject);
    procedure btnReadClick(Sender: TObject);
    procedure btnWriteClick(Sender: TObject);
    procedure btnDlgOpenClick(Sender: TObject);
    procedure btnDlgSaveClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btnAddCellsClick(Sender: TObject);
  private
    procedure AddCells;

    procedure ReadCells;
  public
    { Public declarations }
  end;

var
  frmMain: TfrmMain;

implementation

{$R *.dfm}

procedure TfrmMain.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmMain.btnReadClick(Sender: TObject);
begin
  XLS.Filename := edReadFilename.Text;
  XLS.Read;

  ReadCells;
end;

procedure TfrmMain.btnWriteClick(Sender: TObject);
begin
  XLS.Filename := edWriteFilename.Text;
  XLS.Write;
end;

procedure TfrmMain.btnDlgOpenClick(Sender: TObject);
begin
  dlgOpen.FileName := edReadFilename.Text;
  if dlgOpen.Execute then
    edReadFilename.Text := dlgOpen.FileName;
end;

procedure TfrmMain.btnDlgSaveClick(Sender: TObject);
begin
  dlgSave.FileName := edWriteFilename.Text;
  if dlgSave.Execute then
    edWriteFilename.Text := dlgSave.FileName;
end;

procedure TfrmMain.Button1Click(Sender: TObject);
begin
  Close;
end;

procedure TfrmMain.ReadCells;
var
  C,R: integer;
  Ref: string;
  Cnt: integer;
  vError: TXc12CellError;
  CellType: TXLSCellType;
begin
  Cnt := 0;
  XLS[0].CalcDimensions;
  for R := XLS[0].FirstRow to XLS[0].LastRow do begin
    for C := XLS[0].FirstCol to XLS[0].LastCol do begin
      CellType := XLS[0].CellType[C,R];
      if CellType <> xctNone then begin
        Ref := ColRowToRefStr(C,R);

        Grid.Cells[0,Cnt + 1] := Ref;

        case CellType of
          xctBlank         : begin
            Grid.Cells[1,Cnt + 1] := 'Blank';
          end;
          xctBoolean       : begin
            Grid.Cells[1,Cnt + 1] := 'Boolean';
            if XLS[0].AsBoolean[C,R] then
              Grid.Cells[2,Cnt + 1] := 'TRUE'
            else
              Grid.Cells[2,Cnt + 1] := 'FALSE';
          end;
          xctError         : begin
            Grid.Cells[1,Cnt + 1] := 'Error';
            vError := XLS[0].AsError[C,R];
            Grid.Cells[2,Cnt + 1] := Xc12CellErrorNames[vError];
          end;
          xctString        : begin
            Grid.Cells[1,Cnt + 1] := 'String';
            Grid.Cells[2,Cnt + 1] := XLS[0].AsString[C,R];
          end;
          xctFloat         : begin
            Grid.Cells[1,Cnt + 1] := 'Float';
            Grid.Cells[2,Cnt + 1] := FloatToStr(XLS[0].AsFloat[C,R]);
          end;
          xctFloatFormula  : begin
            Grid.Cells[1,Cnt + 1] := 'Formula, float';
            Grid.Cells[2,Cnt + 1] := FloatToStr(XLS[0].AsFloat[C,R]);
            Grid.Cells[3,Cnt + 1] := XLS[0].AsFormula[C,R]
          end;
          xctStringFormula : begin
            Grid.Cells[1,Cnt + 1] := 'Formula, string';
            Grid.Cells[2,Cnt + 1] := XLS[0].AsString[C,R];
            Grid.Cells[3,Cnt + 1] := XLS[0].AsFormula[C,R]
          end;
          xctBooleanFormula: begin
            Grid.Cells[1,Cnt + 1] := 'Formula, boolean';
            if XLS[0].AsBoolean[C,R] then
              Grid.Cells[2,Cnt + 1] := 'TRUE'
            else
              Grid.Cells[2,Cnt + 1] := 'FALSE';
            Grid.Cells[3,Cnt + 1] := XLS[0].AsFormula[C,R]
          end;
          xctErrorFormula  : begin
            Grid.Cells[1,Cnt + 1] := 'Formula, error';
            vError := XLS[0].AsError[C,R];
            Grid.Cells[2,Cnt + 1] := Xc12CellErrorNames[vError];
            Grid.Cells[3,Cnt + 1] := XLS[0].AsFormula[C,R]
          end;
        end;

        Inc(Cnt);
        if Cnt > Grid.RowCount then
          Exit;
      end;
    end;
  end;
end;

procedure TfrmMain.FormCreate(Sender: TObject);
var
  S: string;
  Ini: TIniFile;
begin
  S := ChangeFileExt(Application.ExeName,'.ini');
  Ini := TIniFile.Create(S);
  try
    edReadFilename.Text := Ini.ReadString('Files','Read','');
    edWriteFilename.Text := Ini.ReadString('Files','Write','');
  finally
    Ini.Free;
  end;

  Grid.Cells[0,0] := 'Refrence';
  Grid.Cells[1,0] := 'Cell type';
  Grid.Cells[2,0] := 'Value';
  Grid.Cells[3,0] := 'Formula';
end;

procedure TfrmMain.FormDestroy(Sender: TObject);
var
  S: string;
  Ini: TIniFile;
begin
  S := ChangeFileExt(Application.ExeName,'.ini');
  Ini := TIniFile.Create(S);
  try
    Ini.WriteString('Files','Read',edReadFilename.Text);
    Ini.WriteString('Files','Write',edWriteFilename.Text);
  finally
    Ini.Free;
  end;
end;

procedure TfrmMain.btnAddCellsClick(Sender: TObject);
begin
  AddCells;

  ReadCells;
end;

procedure TfrmMain.AddCells;
begin
  // Column and row references are zero-relative. Cell A1 have column 0 and row 0.

  // Add a float value in cell A1
  XLS[0].AsFloat[0,0] := 125;
  // Cell references can also be given as a string when using the AsXXXRef properties.
  XLS[0].AsFloatRef['A2'] := 250;

  // Inserting a float values in column B, starting at row 4 and continuing to row 6.
  // The array argument can be of any size.
  XLS[0].InsertFloatColValues(0,3,[10,20,30]);

  XLS[0].AsString[1,0] := 'Hello, world';
  XLS[0].AsStringRef['B2'] := 'Hello again';

  XLS[0].InsertStringColValues(1,3,['One','Two','Three']);

  XLS[0].AsBoolean[2,0] := True;
  XLS[0].AsBooleanRef['C2'] := False;

  // Adding Excel error values.
  XLS[0].AsError[2,0] := errDiv0;
  XLS[0].AsErrorRef['C2'] := errNA;

  // Adding values as variants.
  XLS[0].AsVariant[3,0] := 'Oink!';
  XLS[0].AsVariantRef['D2'] := 750.25;

  // Inserting a float values in Row 11, starting at col A and continuing to col C.
  // The array argument can be of any size.
  XLS[0].InsertFloatRowValues(0,10,[100,200,300]);

  XLS[0].InsertFloatRowValues(5,10,[1000,2000,3000]);

  // Inserting values as a variant array. The values can be numeric, string or boolean.
  // Error values are not possible as delphi will translate the error type to
  // an integer value.
  XLS[0].InsertRowValues(0,11,[1,'Two',True]);

  XLS[0].InsertColValues(6,0,[1,'Two',True]);

  // Add formulas. Formuas are entered as text strings in the same syntax as
  // in Excel.
  XLS[0].AsFormula[4,0] := 'SUM(A10:H11)';
  // Set the result of the formula (this value is wrong).
  XLS[0].AsNumFormulaValue[4,0] := 5000;
  XLS[0].AsFormulaRef['E2'] := 'MAX(A10:H11)*100';


  // Calculate the workbook. This will replace the above wrong formula result
  // with the correct value.
  // This os only of importance when working with the file in the component,
  // or possible if the file is exported to another software that don't calculate
  // formulas.
  // When the file is opened in Excel, the formulas will be recalculated.
  XLS.Calculate;

  XLS[0].CalcDimensions;
end;

end.

复制代码

 例子1、

复制代码

TXLSReadWriteII2版本导出Excel文件:

 

procedure TForm1.N1Click(Sender: TObject);

var

  i: Integer;

  aSaveDialog: TSaveDialog;

  aFileName, aStampTime: AnsiString;

  aXlsObj: TXLSReadWriteII2;

  p: PDataRec;

begin

  aSaveDialog := TSaveDialog.Create(Self);

  try

    aSaveDialog.InitialDir := ExtractFilePath(ParamStr(0));

    aSaveDialog.DefaultExt := 'xls';

    aSaveDialog.Filter := 'Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*';

    aStampTime := FormatDateTime('yyyymmddhhnnss', Now);

    aSaveDialog.FileName := aStampTime;

 

    if not aSaveDialog.Execute then

      Exit;

 

    aFileName := aSaveDialog.FileName;

    if aFileName = '' then

      Exit;

  finally

    aSaveDialog.Free;

  end;

 

  aXlsObj := TXLSReadWriteII2.Create(nil);

  try

    aXlsObj.Sheets[0].AsWideString[0, 0] := 'id';

    aXlsObj.Sheets[0].AsWideString[1, 0] := 'table';

    aXlsObj.Sheets[0].AsWideString[2, 0] := 'kind';

    aXlsObj.Sheets[0].AsWideString[3, 0] := 'rows';

    aXlsObj.Sheets[0].AsWideString[4, 0] := 'times';

    aXlsObj.Sheets[0].AsWideString[5, 0] := 'desc';

 

    for i:=1 to FDataHash.Count - 1 do

    begin

      p := FDataHash[i];

      aXlsObj.Sheets[0].AsWideString[0, i] := Format('%d', [p.id]);

      aXlsObj.Sheets[0].AsWideString[1, i] := p.table;

      aXlsObj.Sheets[0].AsWideString[2, i] := p.kind;

      aXlsObj.Sheets[0].AsWideString[3, i] := Format('%d', [p.rows]);

      aXlsObj.Sheets[0].AsWideString[4, i] := Format('%d', [p.times]);

      aXlsObj.Sheets[0].AsWideString[5, i] := p.desc;

    end;

 

    aXlsObj.Filename := aFileName;

    aXlsObj.Write;

    ShowMessage(Format('导出文件'+#13#10+'%s'+#13#10 +'成功!', [aFileName]));

  finally

    aXlsObj.Free;

  end;

end;

复制代码

本文转载自:http://www.cnblogs.com/chenmfly/p/4858869.html

vga

vga

粉丝 23
博文 366
码字总数 26645
作品 0
佳木斯
私信 提问
C#读写EXCEL单元格的问题

最近, 我在用C#开发一个EXCEL Add-In的时候,发现了一些害人不浅的坑,特来总结列举如下: 这里我读写EXCEL引用的是using Excel = Microsoft.Office.Interop.Excel; 问题一 如何判断一个单元...

微时空
04/25
0
0
Java使用Apache POI导出Excel

1.POI简单介绍 Apache POI 是用Java 编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对 Microsoft Office 格式档案读和写的功能 HSSF 提供读写Microsoft Excel XLS格式档案...

Mr_欢先生
2017/09/27
0
0
支持.NET和移动设备的XLS读写控件XLSReadWriteII控件介绍

XLSReadWriteII 相关文章 在Delphi中使用XlsReadWrite xlsreadwriteII对excel进行读取操作的几点提示 解决 XLSReadWriteII 解析Excel95格式的文件乱码的问题 Excel读写方案XLSReadWriteII使用...

yidongkaifa
2014/06/04
71
0
牌Exel文件xls读写和生成控件Bytescout Spreadsheet SDK for.NET

Bytescout Spreadsheet SDK for.NET能在Delphi和.NET下访问Excel文件的完美解决方案。 读写任何单元值 数字型、字符型、布尔型以及错误型。但是你了解日期和时间型单元吗?在Excel中没有这样...

netkongjian
2014/06/03
0
0
XLSMini控件是XLSReadWriteII的简版可读写Excel单元格的值

XLSMini是XLSReadWriteII的简版,使用它你可以读写单元格的值,包括公式。它和XLSReadWriteII是一样的概念,但是它仅限于操作单元格的值。当你使用XLSMini编辑Excel文件时,其他的所有数据都...

netkongjian
2014/06/03
629
0

没有更多内容

加载失败,请刷新页面

加载更多

最简单的获取相机拍照的图片

  import android.content.Intent;import android.graphics.Bitmap;import android.os.Bundle;import android.os.Environment;import android.provider.MediaStore;import andr......

MrLins
29分钟前
4
0
说好不哭!数据可视化深度干货,前端开发下一个涨薪点在这里~

随着互联网在各行各业的影响不断深入,数据规模越来越大,各企业也越来越重视数据的价值。作为一家专业的数据智能公司,个推从消息推送服务起家,经过多年的持续耕耘,积累沉淀了海量数据,在...

个推
31分钟前
7
0
第三方支付-返回与回调注意事项

不管是支付宝,微信,还是其它第三方支付,第四方支付,支付机构服务商只要涉及到钱的交易都要进行如下校验,全部成功了才视为成功订单 1.http请求是否成功 2.校验商户号 3.校验订单号及状态...

Shingfi
33分钟前
4
0
简述Java内存分配和回收策略以及Minor GC 和 Major GC(Full GC)

内存分配: 1. 栈区:栈可分为Java虚拟机和本地方法栈 2. 堆区:堆被所有线程共享,在虚拟机启动时创建,是唯一的目的是存放对象实例,是gc的主要区域。通常可分为两个区块年轻代和年老代。更...

DustinChan
39分钟前
6
0
Excel插入批注:可在批注插入文字、形状、图片

1.批注一直显示:审阅选项卡-------->勾选显示批注选项: 2.插入批注快捷键:Shift+F2 组合键 3.在批注中插入图片:鼠标右键点击批注框的小圆点【重点不可以在批注文本框内点击】----->调出批...

东方墨天
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部