JPK_V7M - Import danych CSV z systemu BOS BP
Generowanie JPK_V7M z tekstowego pliku CSV pochodzącego z systemu BOS BP.
Arkusz Kalkulacyjny aplikacji Program-JPK PRO, posiada wbudowane mechanizmy dodawania i wykonywania (ad-hoc) rozszerzeń podobnych do technologii MS Office: VSTO, VBA czy najnowszych Add-ins. W naszych rozszerzeniach posługujemy się popularnym językiem programowania C#, a środowisko dostępne jest natychmiast po uruchomieniu Arkusza bez instalowania dodatkowego oprogramowania.
Poprzez rozszerzenia możemy dodawać kolejne przyciski do Menu oraz cztery niezależne panele. Całą konfigurację własnego macro zapisujemy do pliku. Domyślną nazwą i lokalizacją jest plik .\DATA\Extensions.bin. Po otwarciu Arkusza, program automatycznie ładuje rozszerzenia z tego pliku, a wykonywanie zdefiniowanych tam funkcji od razu gotowe jest do pracy.
Można utworzyć wiele różnych konfiguracji ładowanych na życzenie (naprzemiennie). Kolejny załadowany plik udostępnia własne rozszerzenia po usunięciu poprzednio załadowanych.
Obsługa rozszerzeń znajduje się w Menu/Plik/Developer.
Pierwsze użycie tej funkcji otwiera pusty panel „Extensions”. Proszę wykonać kroki 1,2,3.
- Ribbon page – dodajemy nową zakładkę do Menu
- Ribbon group – dodajemy pierwszą grupę (sekcję) do zakładki
- Button – dodanie przycisku
Widok po wykonaniu powyższych 3 czynności. Każda z nich powoduje natychmiastowe wykonanie i dostępność nowego dodatku w programie.
Domyślne nazwy można zmienić za pomocą przycisku [Edycja]. W naszym przykładzie zmieniamy podpis przycisku na [Załaduj CSV]. Następnie zmieniamy nazwy dla Ribbon page/group odpowiednio na [BOS BP] oraz [JPK_V7M]. Ostatecznie zapisujemy przyciskiem [Zapisz jako] do pliku .\DATA\ Extensions.bin.
Zamykamy/otwieramy Arkusz. Program ładuje domyślny projekt i nasz widok prezentuje się tak:
Po załadowaniu projektu, wszystkie domyślne nazwy są już zmienione.
Definiowanie rozszerzenia odbędzie się po wybraniu przycisku [Projektuj], ale o tym w dalszej części opisu.
W przykładzie posłużymy się rzeczywistym problemem przetworzenia tekstowego pliku CSV pochodzącego z systemu BOS BP i wypełnienia tabeli Excel, która będzie użyta do wygenerowania pliku JPK_V7M.
Poniżej przykład takiego pliku. UWAGA plik nie posiada „rekordu” nagłówkowego z nazwami kolumn i dodatkowo posiada puste wiersze pomiędzy liniami z danymi.
FHUDemo|BOS|JPK_VAT|SprzedazWiersze|2020-04-01|2020-04-30||7351010648--- |TGSoft|UL. Pd Dębami 3, Kraków, 30-389|001501-2020/101|2020-04-01||0,00|||0,00||0,00|0,00|0,00|0,00|81,30|18,70|||||||||||||||||||
FHUDemo|BOS|JPK_VAT|SprzedazWiersze|2020-04-01|2020-04-30||735-001-06-69 |SHIFT SP. J.|UL. Kopernika 28, NOWY TARG, 34-400|001502-2020/101|2020-04-02||0,00|||0,00||0,00|0,00|0,00|0,00|16,26|3,74|||||||||||||||||||
FHUDemo|BOS|JPK_VAT|SprzedazWiersze|2020-04-01|2020-04-30||1111111111--- |DEMO Sp. z o.o.|UL. Zakopiańska 62, Kraków, 30-380|001503-2020/101|2020-04-03||0,00|||0,00||0,00|0,00|-500,00|-40,00|100,00|23,00|||||||||||||||||||
FHUDemo|BOS|JPK_VAT|SprzedazWiersze|2020-04-01|2020-04-30||2222222222--- |Rośliny Doniczkowe s.c.|Krupówki 1, ZAKOPANE, 34-500|001504-2020/101|2020-04-14|2020-04-01|0,00|||0,00||0,00|0,00|0,00|0,00|235,03|54,06|||||||||||||||||||
FHUDemo|BOS|JPK_VAT|SprzedazWiersze|2020-04-01|2020-04-30||333-333-33-33 |Kowalski JAN|Rynek 12, NOWY SĄCZ, 34-100|001505-2020/101|2020-04-15||0,00|||0,00||0,00|0,00|0,00|0,00|1276,55|293,60|||||||||||||||||||
Widok pliku z danymi testowymi po załadowaniu do Arkusza za pomocą standardowej funkcji [Otwórz] z zakładki Menu/Plik.
Plik tworzy układ tabelaryczny, ale bez nagłówka z tytułami kolumn i dodatkowymi pustymi wierszami.
W pierwszym przykładzie zaprezentujemy metodę do załadowania takiego pliku, a następnie uzupełnienie arkusza o nazwy kolumn, które wykorzystamy do pobierania danych z kolejnych rekordów za pomocą ich nazw, a nie adresów poszczególnych komórek arkusza. Będzie to dużo łatwiejsze do obsługi i analizowania kodu.
Widok wbudowanego środowiska projektowania po wybraniu [Projektuj] dla naszego przycisku [Załaduj CSV].
W przykładzie nie potrzebujemy rozszerzeń graficznych, dlatego od razu przechodzimy do zakładki [C# Code].
Poniżej pełny kod tego rozszerzenia.
namespace Szark
{
using System;
using System.Windows.Forms;
using DevExpress.Spreadsheet;
using TGSoft.UI.XLS;
public partial class Form1 : DevExpress.XtraEditors.XtraForm
{
private SSController sc;
public Form1(SSController sender)
{
this.InitializeComponent();
sc = sender;
}
public void Main()
{
LoadCSV();
}
private bool LoadCSV()
{
using (var dlg = new OpenFileDialog())
{
dlg.Filter = "Pliki tekstowe *.txt; *.csv | *.txt; *.csv";
if (dlg.ShowDialog() == DialogResult.OK)
{
if (!sc.LoadDocument(dlg.FileName))
return false;
}
else
{
return false;
}
}
try
{
sc.BeginUpdate();
Worksheet ws = sc.ActiveWorksheet;
ws.Rows.Insert(0, 1);
ws[0, 0].Value = "Podmiot";
ws[0, 1].Value = "System";
ws[0, 2].Value = "KodFormularza";
ws[0, 3].Value = "SekcjaFormularza";
ws[0, 4].Value = "DataOd";
ws[0, 5].Value = "DataDo";
ws[0, 6].Value = "LpSprzedazy";
ws[0, 7].Value = "NrKontrahenta";
ws[0, 8].Value = "NazwaKontrahenta";
ws[0, 9].Value = "AdresKontrahenta";
ws[0, 10].Value = "DowodSprzedazy";
ws[0, 11].Value = "DataWystawienia";
ws[0, 12].Value = "DataSprzedazy";
for (int i = 10; i <= 39; i++)
{
ws[0, i + 3].Value = "K_" + i.ToString();
}
}
finally
{
sc.EndUpdate();
}
return true;
}
}
}
Widok załadowanego pliku z automatycznie dodanym rekordem nagłówkowym zawierającym tytuły kolumn.
Wracamy do zakładki Menu/Plik/Developer i dodajemy drugi przycisk [V7M Template]. Będzie służył do załadowania pustego szablonu XLSX dla potrzeb kreatora JPK_V7M. W dalszej części szablon zostanie wypełniony danymi z załadowanego pliku CSV.
Widok po dodaniu drugiego przycisku przycisk [V7M Template].
Skrypt projektu wykonywanego po naciśnięciu drugiego przycisku:
namespace Szark
{
using System;
using TGSoft.UI.XLS;
public partial class Form1 : DevExpress.XtraEditors.XtraForm
{
private SSController ssc;
public Form1(SSController sender)
{
this.InitializeComponent();
ssc = sender;
}
public void Main()
{
ssc.NewJpkV7();
}
}
}
Najistotniejszym elementem jest tutaj metoda NewJpkV7() z klasy SSController.
Widok po dodaniu trzeciego przycisku [Import JPK_V7M].
W rzeczywistym projekcie będzie potrzebny tylko ten ostatni przycisk i jeden projekt, który wykona wszystkie czynności:
- Odczyta plik źródłowy CSV z systemu BOS BP,
- Załaduje pusty wzór XLSX dla kreatora importu JPK_V7M,
- Wypełni wzór danymi.
Na potrzeby naszego opisu zostało to podzielone na części, dla przejrzystości kodu poszczególnych czynności oraz prezentacji możliwości programu w zakresie wykonywania różnych zadań po dodaniu kilku dodatkowych przycisków do Menu Arkusza Kalkulacyjnego.
Poniżej cały skrypt wykonujący wszystkie zadania jednocześnie.
namespace Szark
{
using System;
using System.Windows.Forms;
using DevExpress.Spreadsheet;
using TGSoft.UI.XLS;
using TGSoft.Utils;
public partial class Form1 : DevExpress.XtraEditors.XtraForm
{
private SSController sc, dc;
public Form1(SSController sender)
{
this.InitializeComponent();
sc = new SSController();
dc = sender;
}
public void Main()
{
if (LoadCSV())
{
Fill();
}
}
private bool LoadCSV()
{
using (var dlg = new OpenFileDialog())
{
dlg.Filter = "Pliki tekstowe *.txt; *.csv | *.txt; *.csv";
if (dlg.ShowDialog() == DialogResult.OK)
{
if (!sc.LoadDocument(dlg.FileName))
return false;
}
else
{
return false;
}
}
try
{
sc.BeginUpdate();
Worksheet ws = sc.ActiveWorksheet;
ws.Rows.Insert(0, 1);
ws[0, 0].Value = "Podmiot";
ws[0, 1].Value = "System";
ws[0, 2].Value = "KodFormularza";
ws[0, 3].Value = "SekcjaFormularza";
ws[0, 4].Value = "DataOd";
ws[0, 5].Value = "DataDo";
ws[0, 6].Value = "LpSprzedazy";
ws[0, 7].Value = "NrKontrahenta";
ws[0, 8].Value = "NazwaKontrahenta";
ws[0, 9].Value = "AdresKontrahenta";
ws[0, 10].Value = "DowodSprzedazy";
ws[0, 11].Value = "DataWystawienia";
ws[0, 12].Value = "DataSprzedazy";
for (int i = 10; i <= 39; i++)
{
ws[0, i + 3].Value = "K_" + i.ToString();
}
}
finally
{
sc.EndUpdate();
}
return true;
}
private void Fill()
{
try
{
dc.BeginUpdate();
if (!dc.NewJpkV7())
return;
var xs = new WsTable(sc);
var xt = new WbTable(dc.GetWorksheet(1));
int lp = xt.GetValue(xt.LastRow, "Lp").ToInt();
DateTime dataSp, dataWyst;
string nip;
for (int i = 1; i <= xs.LastRow; i++)
{
dataWyst = xs.GetValue(i, "DataWystawienia").ToDateTime();
if (dataWyst.Year < 2000)
{
continue;
}
lp++;
xt.NewRow();
xt.SetField("Lp", lp);
xt.SetField("DataWystawienia", dataWyst);
dataSp = xs.GetValue(i, "DataSprzedazy").ToDateTime();
if (dataSp.Year > 2015)
xt.SetField("DataSprzedazy", dataSp);
nip = xs.GetValue(i, "NrKontrahenta").ToStr().Replace("-", "").Replace(" ", "");
if (nip == "")
nip = "BRAK";
xt.SetField("NrFaktury", xs.GetValue(i, "DowodSprzedazy").ToStr());
xt.SetField("NIP", nip);
xt.SetField("Nazwa", xs.GetValue(i, "NazwaKontrahenta").ToStr());
string fieldName;
for (int j = 10; j <= 36; j++)
{
fieldName = "K_" + j.ToString();
xt.SetField(fieldName, xs.GetValue(i, fieldName).ToDecimal());
}
xt.AppendRow();
}
}
finally
{
dc.EndUpdate();
}
}
}
}
Zapoznaj się z innymi dokumentami opisującymi definiowaniu dodatków do Arkusza Kalkulacyjnego: