Author: John D7279
|
1 2 |
//// class function ExcelToJson(cells: TRect; excel_filename, sheet_name: String):TJSONObject; |
It is a code for excel to JSON.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
//// 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; |
Now you get the Excel data.
|
1 2 3 4 |
//// 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 ); |
It inserts this acquired JSON data further into the DB table with FireDAC.
I made the above table.This is MySQL.
Insert from TJSONObject into MySQL table.
It is a code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//// 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; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
//// 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
Call this method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
//// 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; |
Insertion to the database succeeded.
[I also write in Japanese]
http://qiita.com/mojeld/items/50ccdea274a17f9d9bbd
[If you use C++Builder, please use the following URL]
https://community.embarcadero.com/blogs/entry/use-the-excel-application-in-c-builder-bcc64
Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Free Delphi Community Edition Free C++Builder Community Edition
