i have one csv/txt file i want that file to convert into excel using csv buffer and excel buffer.Firstly i want that file to import into csv buffer after that i want that file to be converted into excel using excel buffer i am able to import file into csv buffer but not able to export it into excel. Export should be according to condition for multiple sheet and also want some hard code for first three cell. The sheet examples and code is given below that i have used for import.I tried export using report but not able to do that if you can help ,e it will be greatfull.thanks in advance.
page 51111 "import data" { ApplicationArea = All; Caption = 'Import data'; PageType = Card; SourceTable = "CSV Buffer"; SourceTableTemporary = true; UsageCategory = Documents; layout { area(content) { repeater(General) { field("Line No."; Rec."Line No.") { ApplicationArea = All; } field("Field No."; Rec."Field No.") { ApplicationArea = All; } field(Value; Rec.Value) { ApplicationArea = All; } } } } actions { area(Processing) { action(Import) { Caption = 'Import'; Image = Import; ToolTip = 'Import'; ApplicationArea = all; Promoted = true; PromotedCategory = Process; PromotedIsBig = true; PromotedOnly = true; trigger OnAction() var begin Rec.Init(); SelectFileFromFileShare(Rec); Message('check import %1 ', Rec.GetValue(3, 5)); end; } action(Excel) { Image = Excel; ToolTip = 'excel file'; ApplicationArea = all; trigger OnAction() var excel: Report "Export sheet"; begin excel.Run(); end; } } } procedure SelectFileFromFileShare(Var TempCSVBUffer: record "CSV Buffer" temporary) var FileMgt: Codeunit "File Management"; WindowTitle: text; FileName: text; InS: InStream; begin WindowTitle := 'Select file salba'; Filename := FileMgt.OpenFileDialog(WindowTitle, '', ''); Rec.Init(); Rec.LoadData(FileName, ','); end; trigger OnClosePage() begin Rec.Reset(); Rec.DeleteAll(); end; }
report 51103 "Export sheet" { UsageCategory = ReportsAndAnalysis; ApplicationArea = All; ProcessingOnly = true; Caption = 'Export To Excel'; dataset { dataitem("CSV Buffer"; "CSV Buffer") { DataItemTableView = SORTING("Field No.") ORDER(Ascending); trigger OnPreDataItem() begin ; CreateExcelHeader(); end; trigger OnAfterGetRecord() begin CreateExcelBody(); end; } } trigger OnInitReport() begin TempExcelBuffer.DeleteAll(); end; trigger OnPostReport() begin CreateExcelBook(); end; local procedure CreateExcelHeader() begin TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption("Line No."), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Number); TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption("Field No."), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Number); TempExcelBuffer.AddColumn("CSV Buffer".FieldCaption(Value), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Text); // TempExcelBuffer.AddColumn(csvbuffer.FieldCaption(Inventory), False, '', True, false, false, '', TempExcelBuffer."Cell Type"::Text); end; local procedure CreateExcelBody() begin TempExcelBuffer.NewRow(); TempExcelBuffer.AddColumn("CSV Buffer"."Line No.", False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number); TempExcelBuffer.AddColumn("CSV Buffer"."Field No.", False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number); TempExcelBuffer.AddColumn("CSV Buffer".Value, False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text); // TempExcelBuffer.AddColumn(csvbuffer.GetValue(1, 5), False, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number); // TempExcelBuffer.EnterCell(); end; local procedure CreateExcelBook() begin TempExcelBuffer.CreateNewBook('List'); TempExcelBuffer.WriteSheet('List', CompanyName, UserId); TempExcelBuffer.CloseBook(); TempExcelBuffer.SetFriendlyFilename('Export'); TempExcelBuffer.OpenExcel(); end; var TempExcelBuffer: Record "Excel Buffer" temporary; }
and output excel file will be like that below:
there are 13 column and 763 rows in txt file.
thanks in advance