Skip to Content
0
Former Member
Feb 28, 2011 at 01:30 PM

Import to Excel

61 Views

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