Delphiで エクセルデータを取得しJSONにしてからMySQLにインサート

  • 3
    いいね
  • 0
    コメント

Delphiを使ってExcelデータをJSONに入れてそのデータをMySQLにインサートします。

元ネタは下記URLです
https://community.embarcadero.com/blogs/entry/using-delphi-from-excel-to-json-then-firedac-japan

Excelから直接インサートすればいいのですが
一旦JSONに入れてみました。

ExcelからTJSONObject

class functionで作りました。

uExcelToFireDAC.pas
class function ExcelToJson(cells: TRect; excel_filename, sheet_name: String):TJSONObject;

TRectでExcelのセル領域を指定しています。
ファイル名とシートを指定すると TJSONObjectで返ってくる仕組みです

実装部

uExcelToFireDAC.pas
class function TExcelToFireDAC.ExcelToJson(cells: TRect; excel_filename, sheet_name: String): TJSONObject;
const
  def_excel_application = 'Excel.Application';
var
  jres:     TJSONObject;
  jline:    TJSONObject;
  ExcelApp,               //Excel.Application
  excel_book,             //Excel Book
  excel_sheet: Variant;   //Excel Sheet
  s:  String;
  iCol: Integer;
  iRow: Integer;
begin
  ExcelApp  := CreateOleObject(def_excel_application);
  jres      := TJSONObject.Create;
  try
    jres.AddPair('sheet', TJSONArray.Create);
    try
      excel_book  := ExcelApp.Workbooks.Open(excel_filename);
      excel_sheet := excel_book.Worksheets.item[sheet_name];
      for iRow := cells.Top to cells.Bottom do
      begin
        jline := TJSONObject.Create;
        jres.GetValue<TJSONArray>('sheet').Add(jline);
        for iCol := cells.Left to cells.Right do
        begin
          s := excel_sheet.Cells[iRow,iCol];
          jline.AddPair(Format('column_%d', [iCol]),s);
        end;
      end;
    finally
      excel_sheet := Unassigned();
      excel_book  := Unassigned();
    end;
  finally
    ExcelApp  := Unassigned();
  end;
  Result  := jres;
end;

CreateOleObject()を使ってExcelを呼び出しています。

TJSONObjectからFireDACを使ってインサート

ここれはクラスとインターフェイスを使っています。

uExcelToFireDAC.pas
type
  IExcelToFireDAC = interface(IInterface)
    procedure json_to_fdinsert(cells: TRect; sql_string: String; json_data: TJSONObject);
    procedure OnMessage_(const pro: TGetStrProc);
  end;
  TExcelToFireDAC = class(TInterfacedObject, IExcelToFireDAC)
  private
    FFDConn1:   TFDConnection;
    FFDQuery_:  TFDQuery;
    FOnMessage: TGetStrProc;
    FJsonData:  TJSONObject;
    procedure message_log(const msg_: String);
  public
    constructor Create(const connne: TFDConnection);
    destructor Destroy; override;
    class function ExcelToJson(cells: TRect; excel_filename, sheet_name: String):TJSONObject;
    procedure json_to_fdinsert(cells: TRect; sql_string: String; json_data: TJSONObject);
    property  OnMessage: TGetStrProc  read FOnMessage write FOnMessage;
    procedure OnMessage_(const pro: TGetStrProc);
  end;

実装部

これも同じく引数にはExcelで指定したTRectを使っています。
あとはインサート文(SQL)を入れるためのStringと先ほどのTJSONObjectです。

uExcelToFireDAC.pas
procedure TExcelToFireDAC.json_to_fdinsert(cells: TRect; sql_string: String;
  json_data: TJSONObject);
var
  iRow,
  iCol:   Integer;
  jlines: TJSONArray;
  value_:     TJSONValue;
  value_str:  String;
  sql_line:   String;
  stSQL:      String;
begin
  FFDQuery_.Connection  := FFDConn1;
  FJsonData := json_data;
  jlines    := json_data.GetValue<TJSONArray>('sheet');
  for iRow := 0 to jlines.Count-1 do
  begin
    sql_line  := '';
    for iCol := cells.Left-1 to cells.Right-1 do
    begin
      try
        value_ := jlines.Items[iRow].GetValue<TJSONValue>(Format('column_%d', [iCol+1]));
        if SameText(value_.Value, 'null') then
          value_str := 'null,'
        else
          value_str := Format('''%s'',', [value_.Value]);
        sql_line  := sql_line + value_str;
      except

      end;
    end;
    try
      Delete(sql_line, sql_line.Length, 1);
      stSQL   := Format(sql_string, [sql_line]);
      message_log(stSQL);
      FFDQuery_.SQL.Text  := stSQL;
      FFDQuery_.ExecSQL;
    except
      on e: Exception do
        message_log(e.Message);
    end;
  end;
end;

https://gist.github.com/mojeld/66acc0959e9f7a09c1ee4dfddb57b413

動かしてみます。

下絵のようなExcelブックを作りました。
20170208957.gif

MySQL側にt_MotoGPと言うテーブルを作成します。

CREATE TABLE `t_MotoGP` (
  `position` int(11) NOT NULL,
  `racer_name` varchar(100) NOT NULL,
  `team_name` varchar(200) NOT NULL,
  `point_sum` int(11) NOT NULL,
  PRIMARY KEY (`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

実装

Unit1.pas
procedure TForm1.Button1Click(Sender: TObject);
var
  jbase:    TJSONObject;
  excetofd: IExcelToFireDAC;
  rect:     TRect;
begin
  rect  := TRect.Create(2,2,5,6);//Excel Position(Top, Left, Right, Bottom)
  jbase :=  TExcelToFireDAC.ExcelToJson(rect,'Book1.xlsx','Sheet1');
  Memo1.Lines.Append(  jbase.ToString );

  excetofd  := TExcelToFireDAC.Create(FDConnection1);
  excetofd.OnMessage_(msg_log);
  excetofd.json_to_fdinsert(rect,
    'INSERT INTO `t_MotoGP` (`position`,`racer_name`,`team_name`,`point_sum`) '
      + 'VALUES (%s)',jbase);
end;