Import to Excel

[Former Member]

Hi

using System;

using System.Data;

using System.Windows.Forms;

using System.Collections;

using System.Configuration;

using ICR_SBOClassLibrary;

using CrystalDecisions.CrystalReports;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Shared;

using CrystalDecisions.CrystalReports.Engine;

using System.Threading;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.Diagnostics;

namespace ICreon

{

class ClsUploadJE : ICR_clsSBOForm

{

SAPbouiCOM.Form oForm;

SAPbouiCOM.Matrix oMatrix1;

SAPbouiCOM.EditText oEditText;

SAPbobsCOM.UserTable oUDT;

SAPbouiCOM.ChooseFromListCollection oCFLCollection;

SAPbouiCOM.DBDataSource oDBDataSource;

SAPbouiCOM.Column oColumn;

SAPbobsCOM.JournalEntries oJE;

public string FileName = "";

DateTime dtFromDate, dtToDate, dtFromValidDate, dtToValidDate;

SAPbouiCOM.Columns oColumns;

public override bool CreateSBOForm()

{

try

{

string strPath = Application.StartupPath + "
" + ConfigurationManager.AppSettings["JEUPLOAD"];

base.createXMLForm(strPath);

oEditText = (SAPbouiCOM.EditText)oForm.Items.Item("6").Specific;

oEditText.DataBind.SetBound(true, "", "POSTDATE");

oEditText = (SAPbouiCOM.EditText)oForm.Items.Item("2").Specific;

oEditText.DataBind.SetBound(true, "", "PATH");

oForm.EnableMenu("1281", false);

//BindHeader();

return true;

}

catch (Exception EX)

{

oForm.Freeze(false);

ICreon_SBOAddOn.SetStatusMsg(EX);

return false;

}

}

public class WindowWrapper : System.Windows.Forms.IWin32Window

{

private IntPtr _hwnd;

public WindowWrapper(IntPtr handle)

{

_hwnd = handle;

}

public System.IntPtr Handle

{

get { return _hwnd; }

}

}

protected override void OnLoadAfter(ref SAPbouiCOM.ItemEvent pVal, out bool BubbleEvent)

{

BubbleEvent = true;

try

{

oForm = ICreon_SBOAddOn.SBOApplication.Forms.GetForm(pVal.FormTypeEx, pVal.FormTypeCount);

oForm.DataSources.UserDataSources.Add("POSTDATE", SAPbouiCOM.BoDataType.dt_DATE, 10);

oForm.DataSources.UserDataSources.Add("PATH", SAPbouiCOM.BoDataType.dt_SHORT_TEXT, 100);

}

catch (Exception EX)

{

ICreon_SBOAddOn.SetStatusMsg(EX);

}

}

public string FindFile()

{

Thread ShowFolderBrowserThread = default(Thread);

try

{

ShowFolderBrowserThread = new Thread(ShowFolderBrowser);

if (ShowFolderBrowserThread.ThreadState == System.Threading.ThreadState.Unstarted)

{

ShowFolderBrowserThread.SetApartmentState(System.Threading.ApartmentState.STA);

ShowFolderBrowserThread.Start();

}

else if (ShowFolderBrowserThread.ThreadState == System.Threading.ThreadState.Stopped)

{

ShowFolderBrowserThread.Start();

ShowFolderBrowserThread.Join();

}

while (ShowFolderBrowserThread.ThreadState == System.Threading.ThreadState.Running)

{

System.Windows.Forms.Application.DoEvents();

}

if (!string.IsNullOrEmpty(FileName))

{

return FileName;

}

}

catch (Exception ex)

{

ICreon_SBOAddOn.SBOApplication.MessageBox("FileFile" + ex.Message, 1, "", "", "");

}

return "";

}

public void ShowFolderBrowser()

{

System.Diagnostics.Process[] MyProcs = null;

FileName = "";

OpenFileDialog OpenFile = new OpenFileDialog();

try

{

OpenFile.Multiselect = false;

OpenFile.Filter = "All files(.xls)|.xls*";

int filterindex = 0;

try

{

filterindex = 0;

}

catch (Exception ex)

{

}

OpenFile.FilterIndex = filterindex;

OpenFile.RestoreDirectory = true;

MyProcs = Process.GetProcessesByName("SAP Business One");

//if (MyProcs.Length == 2)

//{

for (int i = 0; i <= MyProcs.Length - 1; i++)

{

if (MyProcs<i>.MainWindowTitle != "")

{

WindowWrapper MyWindow = new WindowWrapper(MyProcs<i>.MainWindowHandle);

DialogResult ret = OpenFile.ShowDialog(MyWindow);

if (ret == DialogResult.OK)

{

FileName = OpenFile.FileName;

oEditText = ((SAPbouiCOM.EditText)oForm.Items.Item("2").Specific);

oEditText.String = FileName;

OpenFile.Dispose();

}

else

{

System.Windows.Forms.Application.ExitThread();

}

}

}

//}

}

catch (Exception ex)

{

ICreon_SBOAddOn.SBOApplication.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);

FileName = "";

}

finally

{

OpenFile.Dispose();

}

}

protected override void etITEMPRESSEDBeforeAction(ref SAPbouiCOM.ItemEvent pVal, out bool BubbleEvent)

{

BubbleEvent = true;

try

{

if (pVal.ItemUID == "4")

{

FindFile();

}

if (pVal.ItemUID == "5")

{

string strPath = ((SAPbouiCOM.EditText)oForm.Items.Item("2").Specific).String;

string strDatePosting = ((SAPbouiCOM.EditText)oForm.Items.Item("6").Specific).String;

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:\AUTOPOSTJE.xls;

Extended Properties=Excel 8.0";

System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);

//create a string for the query

string ExcelQuery;

//Sheet1 is the sheet name

//create the query:

//read column with heading A from the Excel file

ExcelQuery = "Select * from [AUTOPOSTJE$]"; // from Sheet1";

System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

ExcelConnection.Open();

//Create a reader

System.Data.OleDb.OleDbDataReader ExcelReader;

ExcelReader = ExcelCommand.ExecuteReader();

//For each row after the first

//Message box the values in the first column i.e. column 0

int i = 1;

double dblCredit = 0.0, dblDebit = 0.0;

oJE = (SAPbobsCOM.JournalEntries)ICreon_SBOAddOn.SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries);

while (ExcelReader.Read())

{

i++;

ICreon_SBOAddOn.SBOApplication.StatusBar.SetText("Auto Posting for JE is in progress... ", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);

string strpartyCode = ExcelReader.GetValue(0).ToString();

string strAcctCode = ExcelReader.GetValue(1).ToString();

if (strpartyCode == "" && strAcctCode=="")

{

int iMsgCon = ICreon_SBOAddOn.SBOApplication.MessageBox("Party Code AND Account Code are missing. Line No - '" + i + "'", 1, "OK", "", "");

if (iMsgCon == 1)

{

BubbleEvent = false;

ExcelConnection.Close();

return;

}

}

string strCredit = ExcelReader.GetValue(2).ToString();

string strDebit = ExcelReader.GetValue(3).ToString();

if (strCredit == "")

{

strCredit = "0.0";

}

dblCredit = Convert.ToDouble(strCredit);

if (strDebit == "")

{

strDebit = "0.0";

}

dblDebit = Convert.ToDouble(strDebit);

string strText = ExcelReader.GetValue(4).ToString();

oJE.TaxDate = Convert.ToDateTime(strDatePosting);

oJE.ReferenceDate = Convert.ToDateTime(strDatePosting);

//oJE.DueDate = Convert.ToDateTime(strDatePosting);

//oJE.VatDate = Convert.ToDateTime(strDatePosting);

if (strpartyCode != "")

{

oJE.Lines.ShortName = strpartyCode;

}

oJE.Lines.Credit = dblCredit;

oJE.Lines.Debit = dblDebit;

oJE.Lines.LineMemo = strText;

if (strAcctCode != "")

{

oJE.Lines.AccountCode = strAcctCode;

}

oJE.Lines.Add();

}

int iErrCode = oJE.Add();

string ErrMsg = "";

if (iErrCode != 0)

{

ICreon_SBOAddOn.SBOCompany.GetLastError(out iErrCode, out ErrMsg);

ICreon_SBOAddOn.SBOApplication.StatusBar.SetText(ErrMsg, SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error);

ExcelConnection.Close();

return;

}

ExcelConnection.Close();

ICreon_SBOAddOn.SBOApplication.StatusBar.SetText("Auto Posting for JE is done successfully", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);

}

}

catch (Exception EX)

{

ICreon_SBOAddOn.SetStatusMsg(EX);

}

}

}

}

I want instaed of J.E Journal Voucher entry should be cerated & everytime i have to delete sheet from excel and then rename it and then create and upldate which i don't want.

Thanks

[Former Member]

Hello

Nice code,

use JV instead of JE (JV is a collection of JE.-s)

Please note: sometimes has a differences between JE and JV.

1. You must declare oJY as Journal Voucher at the same place where you declare JE here:

oJE = (SAPbobsCOM.JournalEntries)ICreon_SBOAddOn.SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries);

2. declare oJE as a part of Voucher

oJE = oJV.JournalEntries.Add();

3. then you must add the following code replacing the int iErrCode = oJE.Add(); Issue the JV instead of JE

int iErrCode = oJV.Add();

[Former Member]

pls use VB .net source code to import from excel

define xl document as sheet and rows and column in that sheet

then use recordset for condition for importing

increase the rows and column value

[Former Member]

Hi RS Siani,

Please clearly state what's your purpose and what error of difficulty you are experiencing. Keep the code to a bare minimum.

Regards,

Vítor Vieira