-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathImport excel.xpp
123 lines (101 loc) · 4.17 KB
/
Import excel.xpp
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class CustomerPaymentJob
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = “_args”>The specified arguments.</param>
public static void main(Args _args)
{
System.IO.Stream stream;
LedgerJournalTable lederJournalTable;
LedgerJournalTrans ledgerJournalTrans;
NumberSequenceTable numberSequenceTable;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
Dialog dialog = new Dialog(“Import the Customer payment Journal data from Excel”);
dlgUploadGroup = dialog.addGroup(“@SYS54759”);
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), ‘Upload’);
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted(‘.xlsx’);
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId(‘Upload’));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row – worksheet.Dimension.Start.Row + 1;
for (i = 2; i<= rowCount; i++)
{
DimensionDisplayValue offsetValue = range.get_Item(i, 11).value;
DimensionDisplayValue accountValue = range.get_Item(i, 4).value;
lederJournalTable.JournalName = range.get_Item(i, 1).value;
lederJournalTable.insert();
ledgerJournalTrans.JournalNum = lederJournalTable.JournalNum;
ledgerJournalTrans.TransDate = DateTimeUtil::date(range.get_Item(i, 2).value);
ledgerJournalTrans.Company = range.get_Item(i, 3).value;
ledgerJournalTrans.AccountType = LedgerJournalACType::Cust;
ledgerJournalTrans.LedgerDimension = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(range.get_Item(i, 4).value, LedgerJournalACType::Cust);
if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
{
ledgerJournalTrans.DefaultDimension = CustTable::find(accountValue).DefaultDimension;
}
ledgerJournalTrans.MarkedInvoice = range.get_Item(i, 5).value;
ledgerJournalTrans.Txt = range.get_Item(i, 6).value;
ledgerJournalTrans.AmountCurDebit = any2Real(range.get_Item(i, 7).value);
ledgerJournalTrans.AmountCurCredit = any2real(range.get_Item(i, 8).value);
ledgerJournalTrans.CurrencyCode = range.get_Item(i, 9).value;
LedgerJournalACType ljcType;
ledgerJournalTrans.OffsetAccountType = str2Enum(ljcType, range.get_Item(i, 10).value);
DimensionAttributeValueCombination dimAttrValComb;
select RecId from dimAttrValComb
where dimAttrValComb.DisplayValue == offsetValue;
if(dimAttrValComb)
{
ledgerJournalTrans.OffsetLedgerDimension = dimAttrValComb.RecId;
}
if (ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Bank)
{
ledgerJournalTrans.OffsetDefaultDimension = BankAccountTable::find(offsetValue).DefaultDimension;
}
else if (ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
{
ledgerJournalTrans.OffsetDefaultDimension = CustTable::find(offsetValue).DefaultDimension;
}
else if (ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Project)
{
ledgerJournalTrans.OffsetDefaultDimension = ProjTable::find(offsetValue).DefaultDimension;
}
else if (ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::FixedAssets)
{
ledgerJournalTrans.OffsetDefaultDimension = AssetTable::find(offsetValue).DefaultDimension;
}
ledgerJournalTrans.PaymMode = range.get_Item(i, 12).value;
ledgerJournalTrans.PaymReference = range.get_Item(i, 13).value;
numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find (lederJournalTable.JournalName).NumberSequenceTable);
ledgerJournalTrans.Voucher = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();
ledgerJournalTrans.insert();
}
}
}
else
{
error(“Error here”);
}
}
}
}