/// <summary>
/// Les cells selectionnées dans le sheet actif
/// </summary>
///
public Range GetSelectedRange()
{
try
{
DevExpress.Spreadsheet.Range selection = this.spreadsheetControl.Selection;
if (selection == null)
{
return(null);
}
DevExpress.Spreadsheet.Worksheet worksheet = this.spreadsheetControl.ActiveWorksheet;
if (worksheet == null)
{
return(null);
}
Sheet sheet = new Sheet(worksheet.Index + 1, worksheet.Name);
Range range = new Range(sheet);
foreach (DevExpress.Spreadsheet.Range area in selection.Areas)
{
RangeItem item = new RangeItem(area.TopRowIndex + 1
, area.BottomRowIndex + 1, area.LeftColumnIndex + 1,
area.RightColumnIndex + 1);
range.Items.Add(item);
}
//rangePreviousValue = range;
return(range);
}
catch (Exception)
{
return(null);
}
}
private void LoadSheet()
{
dt.Rows.Clear();
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(openFileDialog1.FileName);
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
if (i <= workbook.Sheets.Count)
{
txtStoreCode.Text = worksheet.Name;
DataTable TempTable = new DataTable();
string xlConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtAddress.Text + ";Extended Properties=\"Excel 12.0;\";";
using (var myCommand = new OleDbDataAdapter("SELECT [EAN Code],[PI Qty] as [PI Qty] FROM [" + txtStoreCode.Text + "$]", xlConn))
{
myCommand.Fill(TempTable);
if (TempTable.Rows.Count > 0)
{
foreach (DataRow dr in TempTable.Rows)
{
DataSet ds = ProjectFunctions.GetDataSet("sp_LoadEANDataNew '" + dr["EAN Code"].ToString() + "'");
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dataRow = dt.NewRow();
dataRow["ARTNO"] = ds.Tables[0].Rows[0]["ARTNO"].ToString();
dataRow["ARTALIAS"] = ds.Tables[0].Rows[0]["ARTALIAS"].ToString();
dataRow["VARIANTART"] = ds.Tables[0].Rows[0]["VARIANT ART"].ToString();
dataRow["ARTMRP"] = ds.Tables[0].Rows[0]["ARTMRP"].ToString();
dataRow["SIZE"] = ds.Tables[0].Rows[0]["SIZE"].ToString();
dataRow["COLOR"] = ds.Tables[0].Rows[0]["COLOR"].ToString();
dataRow["COLSYSID"] = ds.Tables[0].Rows[0]["COLSYSID"].ToString();
dataRow["SZSYSID"] = ds.Tables[0].Rows[0]["SZSYSID"].ToString();
dataRow["ARTSYSID"] = ds.Tables[0].Rows[0]["ARTSYSID"].ToString();
dataRow["GrpDesc"] = ds.Tables[0].Rows[0]["GrpDesc"].ToString();
dataRow["GrpSubDesc"] = ds.Tables[0].Rows[0]["GrpSubDesc"].ToString();
if (dr["PI Qty"].ToString().Trim().Length == 0)
{
dr["PI Qty"] = 0;
}
dataRow["PIQty"] = Convert.ToDecimal(dr["PI Qty"]);
dataRow["EANNo"] = Convert.ToDecimal(dr["EAN Code"]);
dataRow["GrpHSNCode"] = ds.Tables[0].Rows[0]["GrpHSNCode"].ToString();
dt.Rows.Add(dataRow);
}
}
InfoGrid.DataSource = dt;
InfoGridView.BestFitColumns();
}
}
}
else
{
XtraMessageBox.Show("PIS Created Successfully");
}
}
/// <summary>
/// getActiveSheetIndex
/// </summary>
/// <returns>La sheet active</returns>
public int getActiveSheetIndex()
{
DevExpress.Spreadsheet.Worksheet worksheet = this.spreadsheetControl.ActiveWorksheet;
if (worksheet != null)
{
return(worksheet.Index + 1);
}
return(-1);
}
private void Populate_Muncde_BSA_ccyy_Y(int finYear)
{
try
{
xlSheet = xlControl.Document.Worksheets["Sheet1"];
xlControl.BeginUpdate();
xlSheet.Workbook.BeginUpdate();
string celladdress = "A6";
//var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
xlSheet.Cells[celladdress].Value = finYear.ToString();
//QueryDb
string index = string.Empty;
DataRow row = null;
int nStart = 5;
using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_BSA_ccyy_YTableAdapter())
{
DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_BSA_ccyy_YDataTable();
TA.Fill(tbl);
DataTable tblResult = tbl;
for (int i = 0; i < tblResult.Rows.Count; i++)
{
row = tblResult.Rows[i];
index = row["Code"].ToString();
for (int j = nStart; j < 47; j++)
{
// xlField = xlWorkSheet.Rows.Cells[j, 3];
if (xlSheet.Cells[j, 2].DisplayText == index)
{
//xlField = xlWorkSheet.Rows.Cells[j, 5];
xlSheet.Cells[j, 4].Formula = "= 0" + row["EstYearEnd"].ToString();
nStart = j;
break;
}
}
}
}
}
catch (Exception ex)
{
}
finally
{
xlSheet.Workbook.EndUpdate();
xlControl.EndUpdate();
}
}
protected Point GetSelectionColumnBounds(Range selectedRange = null)
{
DevExpress.Spreadsheet.Worksheet workSheet = null;
if (this.spreadsheetControl.ActiveWorksheet != null)
{
workSheet = this.spreadsheetControl.ActiveWorksheet;
}
DevExpress.Spreadsheet.Range UsedRange = null, UsableRange = null;
int debutCol, finCol;
Ui.Office.Sheet sheet = new Ui.Office.Sheet(workSheet.Index + 1, workSheet.Name);
if (selectedRange != null)
{
UsedRange = workSheet.GetUsedRange();
int debutLigneUsedRange = UsedRange.TopRowIndex + 1;
int finLigneUsedRange = UsedRange.BottomRowIndex + 1;
int debutColUsedRange = UsedRange.LeftColumnIndex + 1;
int finColUsedRange = UsedRange.RightColumnIndex + 1;
int debutLigneUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[0].Row : selectedRange.Items[0].Row1;
int finLigneUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[selectedRange.Cells.Count - 1].Row : selectedRange.Items[0].Row2;
int debutColUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[0].Column : selectedRange.Items[0].Column1;
int finColUsableRange = selectedRange.CellCount > 0 ? selectedRange.Cells[selectedRange.Cells.Count - 1].Column : selectedRange.Items[0].Column2;
debutCol = (debutColUsedRange < debutColUsableRange) ? debutColUsableRange : debutColUsedRange;
finCol = (finColUsedRange > finColUsableRange) ? finColUsableRange : finColUsedRange;
}
else
{
UsableRange = workSheet.GetUsedRange();
UsedRange = UsableRange;
debutCol = UsedRange.LeftColumnIndex + 1;
finCol = UsedRange.RightColumnIndex + 1;
}
return(new Point(debutCol, finCol));
}
private void BtnSave_Click(object sender, EventArgs e)
{
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Loading Data ");
if (ValidateData())
{
InfoGridView.CloseEditor();
InfoGridView.UpdateCurrentRow();
using (var sqlcon = new SqlConnection(ProjectFunctions.GetConnection()))
{
sqlcon.Open();
var sqlcom = sqlcon.CreateCommand();
var transaction = sqlcon.BeginTransaction("SaveTransaction");
sqlcom.Connection = sqlcon;
sqlcom.Transaction = transaction;
sqlcom.CommandType = CommandType.Text;
try
{
if (S1 == "&Add")
{
txtPINo.Text = GetNewInvoiceDocumentNo().PadLeft(6, '0');
sqlcom.CommandText = "Insert into PIMstNew(PINo,PIDate,PIStoreCode)values(@PINo,@PIDate,@PIStoreCode)";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
}
if (S1 == "Edit")
{
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "Update PIMstNew Set PIStoreCode=@PIStoreCode Where PINo = @PINo And PIDate=@PIDate ";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.Parameters.AddWithValue("@PIStoreCode", txtStoreCode.Text.Trim());
sqlcom.Parameters.Clear();
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = " Delete from PIDataNew Where PINo = @PINo And PIDate=@PIDate ";
sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
}
SplashScreenManager.Default.SetWaitFormDescription("Saving Data ");
//foreach (DataRow dr in dt.Rows)
//{
// sqlcom.CommandType = CommandType.Text;
// sqlcom.CommandText = " Insert into PIDataNew"
// + " (PINo,PIDate,PIQty,EANNo)"
// + " values(@PINo,@PIDate,@PIQty,@EANNo)";
// sqlcom.Parameters.AddWithValue("@PINo", txtPINo.Text.Trim());
// sqlcom.Parameters.AddWithValue("@PIDate", Convert.ToDateTime(txtPIDate.Text));
// sqlcom.Parameters.AddWithValue("@PIQty", Convert.ToDecimal(dr["PIQty"]));
// sqlcom.Parameters.AddWithValue("@EANNo", dr["EANNo"].ToString());
// sqlcom.ExecuteNonQuery();
// sqlcom.Parameters.Clear();
//}
DataTable dtFinal = new DataTable();
dtFinal.Columns.Add("PINo", typeof(string));
dtFinal.Columns.Add("PIDate", typeof(string));
dtFinal.Columns.Add("PIQty", typeof(string));
dtFinal.Columns.Add("EANNo", typeof(string));
dtFinal.Rows.Clear();
foreach (DataRow dr in (InfoGrid.DataSource as DataTable).Rows)
{
DataRow drRow = dtFinal.NewRow();
drRow["PINo"] = txtPINo.Text.Trim();
drRow["PIDate"] = Convert.ToDateTime(txtPIDate.Text).ToString("yyyy-MM-dd");
drRow["PIQty"] = dr["PIQty"].ToString();
drRow["EANNo"] = dr["EANNo"].ToString();
dtFinal.Rows.Add(drRow);
}
dtFinal.AcceptChanges();
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.CommandText = "sp_InsertPINew";
sqlcom.CommandTimeout = 600000;
SqlParameter param = new SqlParameter
{
ParameterName = "@PerformaInv",
Value = dtFinal
};
sqlcom.Parameters.Add(param);
sqlcom.ExecuteNonQuery();
sqlcom.Parameters.Clear();
transaction.Commit();
//ProjectFunctions.SpeakError("PI Data Saved Successfully");
sqlcon.Close();
if (FromExcel == "Y")
{
// SplashScreenManager.Default.SetWaitFormDescription("Saving PI " + i.ToString());
i++;
LoadSheet();
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(openFileDialog1.FileName);
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[i];
if (i <= workbook.Sheets.Count)
{
BtnSave_Click(null, e);
}
else
{
SplashScreenManager.CloseForm(false);
Close();
}
}
else
{
SplashScreenManager.CloseForm(false);
}
Close();
FromExcel = "N";
}
catch (Exception ex)
{
ProjectFunctions.SpeakError("Something Wrong. \n I am going to Roll Back." + ex.Message);
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
ProjectFunctions.SpeakError("Something Wrong. \n Roll Back Failed." + ex2.Message);
}
}
}
}
}
private void Populate_Muncde_OSA_ccyy_Mnn(int finYear, string monthEnd)
{
try
{
//xlWorkSheet = (Excel.Worksheet) xlWorkBook.Sheets["Sheet1"];
xlSheet = xlControl.Document.Worksheets["Sheet1"];
xlControl.BeginUpdate();
xlSheet.Workbook.BeginUpdate();
string celladdress = "A10";
//var cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
//xlSheet.Cells[celladdress].Value = finYear.ToString();
xlSheet.Cells[celladdress].Value = finYear.ToString();
//b monthend
celladdress = "B10";
//cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
//cell.Value = monthEnd;
xlSheet.Cells[celladdress].Value = monthEnd;
//c _regionalIdentifier
celladdress = "C10";
// cell = xlWorkSheet.get_Range(celladdress, Type.Missing);
xlSheet.Cells[celladdress].Value = _regionalIdentifier;
//QueryDb
string index = string.Empty;
DataRow row = null;
int nStart = 9;// 10;
using (DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter TA = new DAL.mSCOA_VaultDataSetTableAdapters.GetMuncde_OSA_ccyy_MnnTableAdapter())
{
DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable tbl = new DAL.mSCOA_VaultDataSet.GetMuncde_OSA_ccyy_MnnDataTable();
TA.Fill(tbl);
DataTable tblResult = tbl; // DBCall.GetMuncde_OSA_ccyy_Mnn();
for (int i = 0; i < tblResult.Rows.Count; i++)
{
row = tblResult.Rows[i];
index = row["Code"].ToString();
if (!string.IsNullOrEmpty(index))
{
for (int j = nStart; j < 3896; j++)
{
//xlField = xlSheet.Rows[j, 11].Value ;
if (xlSheet.Cells[j, 10].DisplayText == index)
{
//xlField = xlWorkSheet.Rows.Cells[j, 10];
decimal val = 0;
decimal.TryParse(row[monthEnd].ToString(), out val);
xlSheet.Cells[j, 9].Formula = "= " + val.ToString();
nStart = j;
break;
}
}
}
}
}
}
catch (Exception ex)
{
}
finally
{
xlSheet.Workbook.EndUpdate();
xlControl.EndUpdate();
}
}
public void Dispose()
{
xlSheet = null;
xlControl = null;
}
private void InitializeWorksheet()
{
DevExpress.Spreadsheet.Worksheet sheet = spreadsheetControl1.ActiveWorksheet;
sheet.Cells[0, 0].Formula = "SIN(B1)";
sheet.Cells[0, 1].Value = 30;
}
