Import to Excel
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