private void BindWeatherReportToTable(object weatherDatasource, CellRange bindingRange)
{
Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];
// Remove all data bindings bound to the specified data source.
sheet.DataBindings.Remove(weatherDatasource);
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
// Create a table and bind the data source to the table.
try {
WorksheetTableDataBinding sheetDataBinding = sheet.DataBindings.BindTableToDataSource(weatherDatasource, bindingRange, dsOptions);
sheetDataBinding.Table.Style = spreadsheetControl1.Document.TableStyles[BuiltInTableStyleId.TableStyleMedium14];
// Adjust the column width.
sheetDataBinding.Range.AutoFitColumns();
}
catch (Exception e) {
MessageBox.Show(e.Message, "Binding Exception");
}
}
private void BindWeatherReportToRange(object weatherDatasource, CellRange bindingRange)
{
Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];
// Check for range conflicts.
var dataBindingConflicts = sheet.DataBindings.
Where(d => (d.Range.RightColumnIndex >= bindingRange.LeftColumnIndex) || (d.Range.BottomRowIndex >= bindingRange.TopRowIndex));
if (dataBindingConflicts.Count() > 0)
{
MessageBox.Show("Cannot bind the range to data.\r\nThe worksheet contains other binding ranges which may conflict.", "Range Conflict");
return;
}
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
// Bind the data source to the worksheet range.
WorksheetDataBinding sheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, bindingRange, dsOptions);
// Adjust the column width.
sheetDataBinding.Range.AutoFitColumns();
}
private void barBtnBindMyFishes_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
// Bind the data source to the worksheet range.
this.fishesDataBinding = spreadsheetControl1.Document.Worksheets[0].DataBindings.BindToDataSource(MyFishesSource.Data, 2, 5, dsOptions);
// Highlight the binding range.
this.fishesDataBinding.Range.FillColor = System.Drawing.Color.LightCyan;
// Adjust column width.
spreadsheetControl1.Document.Worksheets[0].Range.FromLTRB(5, 2, this.fishesDataBinding.Range.RightColumnIndex, this.fishesDataBinding.Range.BottomRowIndex).AutoFitColumns();
}
private void BindingDataSource()
{
run();
Workbook = spreadsheetControl1.Document;
Workbook.LoadDocument(@"Document\situation.xlsx", DocumentFormat.Xlsx);
IWorkbook workbook = spreadsheetControl1.Document;
Worksheet worksheet = Workbook.Worksheets[0];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
worksheet.DataBindings.Clear();
worksheet.Columns.ClearOutline();
//worksheet.DataBindings.BindTableToDataSource(null);
worksheet.DataBindings.BindTableToDataSource(dataView, 4, 1, options);
worksheet.Cells.AutoFitColumns();
worksheet.Cells.AutoFitRows();
worksheet.Cells.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Left;
worksheet.Cells.Style.Alignment.Vertical = SpreadsheetVerticalAlignment.Top;
worksheet.ActiveView.Zoom = 80;
//worksheet.RowColumnHeadersVisible = false;
}
finally
{
workbook.EndUpdate();
}
}
private void BindWeatherReport(object weatherDatasource)
{
if (this.weatherDataBinding != null)
{
spreadsheetControl1.Document.Worksheets[0].DataBindings.Remove(this.weatherDataBinding);
}
#region #BindTheList
// Specify the binding options.
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
dsOptions.CellValueConverter = new MyWeatherConverter();
dsOptions.SkipHiddenRows = true;
// Bind the data source to the worksheet range.
Worksheet sheet = spreadsheetControl1.Document.Worksheets[0];
WorksheetDataBinding sheetDataBinding = sheet.DataBindings.BindToDataSource(weatherDatasource, 2, 1, dsOptions);
#endregion #BindTheList
this.weatherDataBinding = sheetDataBinding;
// Highlight the binding range.
this.weatherDataBinding.Range.FillColor = System.Drawing.Color.Lavender;
// Adjust column width.
spreadsheetControl1.Document.Worksheets[0].Range.FromLTRB(1, 1, this.weatherDataBinding.Range.RightColumnIndex, this.weatherDataBinding.Range.BottomRowIndex).AutoFitColumns();
}
private void btnCmd_Click(object pSender, EventArgs pEventArgs)
{
SimpleButton pCmd = pSender as SimpleButton;
string sCls = pCmd.Name.Substring(6, 2);
switch (sCls)
{
case "10":
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
OpenFileDialog opd = new OpenFileDialog();
opd.InitialDirectory = "C\\";
opd.FileName = "";
opd.Filter = "xlsxFile(*.xlsx)|*.xlsx|xlsFile(*.xls)|*.xls";
opd.Title = "Excel Export";
if (opd.ShowDialog() == DialogResult.OK)
{
fileName = opd.SafeFileName;
fileFullName = opd.FileName;
filePath = fileFullName.Replace(fileName, "");
// 선택한 엑셀 파일 spread control에 바인딩
using (FileStream stream = new FileStream(opd.FileName.ToString(), FileMode.Open, FileAccess.Read))
{
spreadsheetControl2.LoadDocument(stream);
IWorkbook workbook = spreadsheetControl2.Document;
Worksheet sheet_0 = workbook.Worksheets["COCKPIT"];
IWorkbook workbook2 = spreadsheetControl1.Document;
Worksheet sheet2_0 = workbook2.Worksheets[0];
Range usedRange = sheet_0.GetUsedRange();
if (sheet2_0.Cells[2, 5].Value.ToString() != null && sheet2_0.Cells[2, 5].Value.ToString() != "")
{
if (CoFAS_DevExpressManager.ShowQuestionMessage("작업중인 데이터가 있습니다. 계속 진행하시겠습니까?") == DialogResult.No)
{
return;
}
else
{
using (FileStream st = new FileStream("Documents\\COCKPIT_SHEET.xlsx", FileMode.Open))
{
spreadsheetControl1.LoadDocument(st, DocumentFormat.Xlsx);
}
}
}
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
DataTable dt = new DataTable();
for (int i = 0; i < usedRange.RowCount; i++)
{
sheet2_0.Cells[i + 1, 0].Value = sheet_0.Cells[12 + i, 0].Value.ToString();
sheet2_0.Cells[i + 1, 1].Value = sheet_0.Cells[12 + i, 2].Value.ToString();
}
}
}
}
catch (Exception ex)
{
CoFAS_DevExpressManager.ShowInformationMessage("엑셀 파일을 확인해주세요.");
}
finally
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
break;
case "20":
IWorkbook workbook3 = spreadsheetControl1.Document;
Worksheet sheet_3 = workbook3.Worksheets[0];
if (sheet_3.Cells[2, 5].Value.ToString() != null && sheet_3.Cells[2, 5].Value.ToString() != "")
{
// Save the modified document to a stream.
using (FileStream stream = new FileStream("Documents\\SaveDocuments\\" + sheet_3.Cells[2, 5].Value.ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx",
FileMode.Create, FileAccess.ReadWrite))
{
workbook3.SaveDocument(stream, DocumentFormat.Xlsx);
CoFAS_DevExpressManager.ShowInformationMessage("저장 되었습니다.");
}
}
else
{
CoFAS_DevExpressManager.ShowInformationMessage("PO 넘버를 입력해주세요.");
}
break;
case "30":
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
OpenFileDialog opd2 = new OpenFileDialog();
opd2.FileName = "";
opd2.InitialDirectory = Application.StartupPath + "\\Documents\\SaveDocuments";
opd2.Filter = "xlsxFile(*.xlsx)|*.xlsx|xlsFile(*.xls)|*.xls";
opd2.Title = "Excel Import";
if (opd2.ShowDialog() == DialogResult.OK)
{
fileName = opd2.SafeFileName;
fileFullName = opd2.FileName;
filePath = fileFullName.Replace(fileName, "");
// 선택한 엑셀 파일 spread control에 바인딩
using (FileStream stream = new FileStream(opd2.FileName.ToString(), FileMode.Open, FileAccess.Read))
{
spreadsheetControl1.LoadDocument(stream);
}
}
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
break;
default: break;
}
}
private void Sheet_Info_Sheet_Data(DataTable tTempData, string ORDER_ID) //추가
{
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
// _pMaterialInspectRegisterEntity.ORDER_ID = _luORDER_ID.Text.ToString();
IWorkbook workbook = _sdMAIN.Document;
string _pPART_CODE = tTempData.Rows[0]["PART_CODE"].ToString();
using (DBManager pDBManager = new DBManager())
{
_pucWorkOrderInfoPopup_T50Entity.PRODUCTION_ORDER_ID = ORDER_ID;
_pucWorkOrderInfoPopup_T50Entity.PART_CODE = _pPART_CODE;
_dsList = new ucWorkOrderInfoPopup_T50Provider(pDBManager).ucWorkOrderPopup_info_ExcelBinding(_pucWorkOrderInfoPopup_T50Entity);
////생산지시정보 및 제품정보
_dtList2 = _dsList.Tables[0];
//자재정보
_dtList3 = _dsList.Tables[1];
if (_dtList2 != null && _dtList2.Rows.Count > 0)
{
Worksheet sheet_1 = workbook.Worksheets[1];
Worksheet sheet_0 = workbook.Worksheets[0];
Range data_range = sheet_1.GetDataRange();
sheet_1.Clear(data_range);
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = false;
//당일 날짜, 로그인명
sheet_0.Cells["G1"].Value = DateTime.Today.ToString("yyyy.MM.dd");
sheet_0.Cells["G3"].Value = _pUSER_CODE;
//바코드정보
sheet_0.Cells["C5"].Value = "*" + ORDER_ID + "*";//"*WO190131000001*";
int i = 0;
int j = 0;
//라우팅정보 for 엑셀은 0부터 시작
for (i = 0; i < _dtList2.Rows.Count; i++)
{
string seq = "";
seq = _dtList2.Rows[i]["SEQ"].ToString();
sheet_0.Rows[6][2].Value = " " + _dtList2.Rows[i]["PART_CODE"].ToString();
sheet_0.Rows[7][2].Value = " " + _dtList2.Rows[i]["PART_NAME"].ToString();
sheet_0.Rows[11][i].Value = seq;
sheet_0.Rows[12][i].Value = _dtList2.Rows[i]["PROCESS_CODE"].ToString();
sheet_0.Rows[13][i].Value = _dtList2.Rows[i]["PROCESS_GUBUN"].ToString();
sheet_0.Rows[14][i].Value = _dtList2.Rows[i]["PROCESS_NAME"].ToString();
sheet_0.Rows[16][0].Value = _dtList2.Rows[0]["CARVE"].ToString();
sheet_0.Rows[22][0].Value = _dtList2.Rows[0]["MESSAGE"].ToString();
}
if (_dtList3 != null && _dtList3.Rows.Count > 0)
{
for (j = 0; j < _dtList3.Rows.Count; j++)
{
sheet_0.Rows[8][2].Value = " " + _dtList3.Rows[j]["PART_CODE"].ToString();
sheet_0.Rows[9][2].Value = " " + _dtList3.Rows[j]["PART_NAME"].ToString();
}
}
else
{
CoFAS_DevExpressManager.ShowInformationMessage("BOM 정보를 불러오지 못했습니다.");
}
}
else
{
CoFAS_DevExpressManager.ShowInformationMessage("조회 내역이 없습니다.");
}
}
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
_sdMAIN.ShowPrintPreview();
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager.TargetSite.ToString()));
}
finally
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
}
private void Sheet_Info_Sheet_Data() //추가
{
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
// _pRawMaterialInspectRegisterEntity.ORDER_ID = _luORDER_ID.Text.ToString();
IWorkbook workbook = _sdMAIN.Document;
using (DBManager pDBManager = new DBManager())
{
string _terminal_name = _luTTERMINAL_LIST.Text;
string _check_list = _luTCHECK_CATEGORY.Text;
_pEquipmentHistoryEntity.CRUD = "R";
_pEquipmentHistoryEntity.DATE_FROM = DateTime.Parse(_luTORDER_DATE.FromDateTime.ToString()).ToString("yyyyMMdd");
_pEquipmentHistoryEntity.DATE_TO = DateTime.Parse(_luTORDER_DATE.ToDateTime.ToString()).ToString("yyyyMMdd");
_pEquipmentHistoryEntity.TERMINAL_NAME = _terminal_name.ToString();
_pEquipmentHistoryEntity.CHECK_LIST = _check_list.ToString();
// _dtList = new MaterialOrderRegisterProvider(pDBManager).Sheet_Info_Sheet_Data(_pRawMaterialInspectRegisterEntity);
_dtList = new EquipmentHistoryProvider(pDBManager).EquipmentHistory_info_ExcelBinding(_pEquipmentHistoryEntity);
//품목정보용
// _dtList = _dsList.Tables[0];
//시험항목검사결과(엑셀바인딩용)
_dtList_01 = _dsList.Tables[1];
_dtList_02 = _dsList.Tables[2];
if (_dtList != null && _dtList.Rows.Count > 0)
{
Worksheet sheet_1 = workbook.Worksheets["데이터"];
Worksheet sheet_0 = workbook.Worksheets[0];
Worksheet sheet_2 = workbook.Worksheets["저장용 데이터"];
Range data_range = sheet_1.GetDataRange();
sheet_1.Clear(data_range);
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
/*
* 발주서 양식에 데이터를 바인딩할 때, row와 column의 인덱스 또는 범위(range)를 직접 지정하여 바인딩해야 한다.
* 현재, 발주번호 1건에 1개의 품목이 정의된 상태에서는 'data' 시트에 '기본 정보'와 '품목 조회 부분'을 같이 불러왔다.
* 그리고 엑셀의 참조 기능을 이용하여 발주서 양식을 완성했다.
*
* 하지만, 발주번호 1건에 여러 개의 품목이 정의된 상태는 바인딩 방법을 다르게 해야 한다.
* DataSet으로 '기본 정보'와 '품목 조회 부분'을 따로 불러와야 한다.
* '기본 정보'는 'data' 시트에 바인딩한 후, 엑셀의 참조 기능을 이용한다.
* 그리고, '품목 조회 부분'은 rows.count()를 이용해 row의 수를 파악하여 발주서 양식에 행을 추가한다.
* 그 후, 행을 추가한 부분에 '품목 조회 부분'의 위치를 직접 지정하여 바인딩해야 한다.
*
*/
sheet_1.DataBindings.BindToDataSource(_dtList, 1, 0, dsOptions);
sheet_1.DataBindings.BindToDataSource(_dtList_01, 4, 0, dsOptions);
}
else
{
CoFAS_DevExpressManager.ShowInformationMessage("조회 내역이 없습니다.");
}
}
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager.TargetSite.ToString()));
}
finally
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
}
private void Sheet_Info_Sheet_Data()
{
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
_pSampleExcelBindingEntity.CRUD = "R"; // pCRUD;
_pSampleExcelBindingEntity.DATE_FROM = DateTime.Parse(_luINOUT_DATE.FromDateTime.ToString()).ToString("yyyyMMdd"); //Convert.ToString(_luINOUT_DATE.FromDateTime).Substring(0, 10).Replace("-", "");
_pSampleExcelBindingEntity.DATE_TO = DateTime.Parse(_luINOUT_DATE.ToDateTime.ToString()).ToString("yyyyMMdd"); //Convert.ToString(_luINOUT_DATE.ToDateTime).Substring(0, 10).Replace("-", "");
_pSampleExcelBindingEntity.PART_CODE = _luPART_CODE.Text;
_pSampleExcelBindingEntity.PART_NAME = _luPART_NAME.Text;
_pSampleExcelBindingEntity.VEND_CODE = "";// _luVEND_CODE.Text;
_pSampleExcelBindingEntity.VEND_NAME = _luVEND_NAME.Text;
//new sheet에 dt 바인딩
//_dtList = new SampleExcelBindingBusiness().Sheet_Info_Sheet_Data(_pSampleExcelBindingEntity);
_dsList = new SampleExcelBindingBusiness().Sheet_Info_Sheet_Data(_pSampleExcelBindingEntity);
//각각 필요한 현황들 바인딩
_dtList_01 = _dsList.Tables[0];
_dtList_02 = _dsList.Tables[1];
_dtList_03 = _dsList.Tables[2];
_dtList_04 = _dsList.Tables[3];
//IWorkbook workbook = _sdMAIN.Document;
workbook = _sdMAIN.Document;
//원본 데이터 바인딩할 엑셀Sheet명
//Worksheet worksheet_1 = workbook.Worksheets["Data"]; //sample
////Worksheet worksheet_BackData_01 = workbook.Worksheets["Back_Data_01"];
////Worksheet worksheet_BackData_02 = workbook.Worksheets["Back_Data_02"];
////Worksheet worksheet_BackData_03 = workbook.Worksheets["Back_Data_03"];
////Worksheet worksheet_BackData_04 = workbook.Worksheets["Back_Data_04"];
Worksheet worksheet_BackData_01 = workbook.Worksheets["Data01"];
// if (_dtList != null)
if (_dsList != null)
{
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = false;
// worksheet_1.DataBindings.BindToDataSource(_dtList, 1, 0, dsOptions);
//피벗테이블에 들어갈 각 표들의 원본 데이터 바인딩
// 1.제품입고현황
// worksheet_BackData_01.DataBindings.BindToDataSource(_dtList_01, 1, 0, dsOptions);
// 2.기간별생산실적현황
// worksheet_BackData_02.DataBindings.BindToDataSource(_dtList_02, 1, 0, dsOptions);
// 3.제품미출고현황
// worksheet_BackData_03.DataBindings.BindToDataSource(_dtList_03, 1, 0, dsOptions);
// 4.제품재고현황
// worksheet_BackData_04.DataBindings.BindToDataSource(_dtList_04, 1, 0, dsOptions);
}
else
{
CoFAS_DevExpressManager.ShowInformationMessage("조회 내역이 없습니다.");
}
//18.06.22 원본 : 현황하나만 보여주는 경우
/*
* //피벗테이블이 있는 시트번호
* Worksheet worksheet_2 = workbook.Worksheets[1];
* Worksheet worksheet_3 = workbook.Worksheets[0]; //보여줄것
*
* //백데이터 범위
* Range backdata_range = worksheet_1.GetDataRange();
* worksheet_2.PivotTables[0].ChangeDataSource(backdata_range);
*
* // 피벗시트에서 피벗테이블 범위 알아오기 -> 차트에 범위 갱신
* Range PivotTable_range = worksheet_2.GetDataRange();
*
* //변경된 차트의 데이터범위를 조정
* //worksheet_2.Charts[0].SelectData(worksheet_2[pivotdata_range]);
* //worksheet_3.Charts[0].SelectData(worksheet_2[pivotdata_range]);
* worksheet_2.Charts[0].SelectData(PivotTable_range);
* worksheet_3.Charts[0].SelectData(PivotTable_range);
*
* workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
* workbook.PivotCaches.RefreshAll();
* //수정될경우 이벤트 호출
* workbook.ActiveSheetChanged += Workbook_ActiveSheetChanged; ;
* //workbook.change
*/
//피벗테이블이 있는 시트번호
////Worksheet worksheet_PivotTable_01 = workbook.Worksheets["Pivot_01"];
////Worksheet worksheet_PivotTable_02 = workbook.Worksheets["Pivot_02"];
////Worksheet worksheet_PivotTable_03 = workbook.Worksheets["Pivot_03"];
////Worksheet worksheet_PivotTable_04 = workbook.Worksheets["Pivot_04"];
//보여줄 현황 시트(차트있는 시트)
////Worksheet worksheet_Chart_01 = workbook.Worksheets["제품입고현황"];
////Worksheet worksheet_Chart_02 = workbook.Worksheets["기간별생산실적현황"];
////Worksheet worksheet_Chart_03 = workbook.Worksheets["제품미출고현황"];
////Worksheet worksheet_Chart_04 = workbook.Worksheets["제품재고현황"];
//백데이터 범위 재지정
////Range backdata_range_01 = worksheet_PivotTable_01.GetDataRange();
////Range backdata_range_02 = worksheet_PivotTable_02.GetDataRange();
////Range backdata_range_03 = worksheet_PivotTable_03.GetDataRange();
////Range backdata_range_04 = worksheet_PivotTable_04.GetDataRange();
////worksheet_PivotTable_01.Charts[0].SelectData(backdata_range_01);
////worksheet_PivotTable_02.Charts[0].SelectData(backdata_range_02);
////worksheet_PivotTable_03.Charts[0].SelectData(backdata_range_03);
////worksheet_PivotTable_04.Charts[0].SelectData(backdata_range_04);
//worksheet_PivotTable_01.Charts[0].ChangeDataSource(worksheet_BackData_01.GetDataRange());
//worksheet_PivotTable_02.Charts[0].ChangeDataSource(worksheet_BackData_02.GetDataRange());
//worksheet_PivotTable_03.Charts[0].ChangeDataSource(worksheet_BackData_03.GetDataRange());
//worksheet_PivotTable_04.Charts[0].ChangeDataSource(worksheet_BackData_04.GetDataRange());
//변경된 차트의 데이터범위를 조정
////worksheet_Chart_01.Charts[0].SelectData(backdata_range_01);
////worksheet_Chart_02.Charts[0].SelectData(backdata_range_02);
////worksheet_Chart_03.Charts[0].SelectData(backdata_range_03);
////worksheet_Chart_04.Charts[0].SelectData(backdata_range_04);
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];
workbook.PivotCaches.RefreshAll();
//수정될경우 이벤트 호출
workbook.ActiveSheetChanged += Workbook_ActiveSheetChanged;;
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager));
}
finally
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
}
private void btnImportDataNorm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
xtraOpenFileDialog1.Title = "Auswahl einer DATANORM Datei";
xtraOpenFileDialog1.Filter = "Alle Dateien (*.*)|*.*";
xtraOpenFileDialog1.FilterIndex = 2;
xtraOpenFileDialog1.CheckFileExists = true;
xtraOpenFileDialog1.CheckPathExists = true;
xtraOpenFileDialog1.FileName = string.Empty;
xtraOpenFileDialog1.RestoreDirectory = true;
xtraOpenFileDialog1.ShowDialog();
if (!string.IsNullOrEmpty(xtraOpenFileDialog1.FileName))
{
IWorkbook workbook = spreadsheetControl1.Document;
bool isOSExists = false;
bool isILExists = false;
bool isVLExists = false;
foreach (Worksheet ws in workbook.Sheets)
{
if (ws.Name == "Blatt im Original")
{
var result = XtraMessageBox.Show("Das Blatt enthält Daten. Möchten Sie den Prozess fortsetzen?", "Bestätigung!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (Convert.ToString(result) == "No")
{
return;
}
ws.ConditionalFormattings.Clear();
ws.DataValidations.Clear();
isOSExists = true;
}
else if (ws.Name == "Import des Logs")
{
ws.ConditionalFormattings.Clear();
isILExists = true;
}
else if (ws.Name == "Log mit Validierungsangaben")
{
ws.ConditionalFormattings.Clear();
isVLExists = true;
}
}
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Importieren...");
if (isOSExists)
{
workbook.Worksheets.Add("Sheet1");
workbook.Worksheets.Remove(workbook.Worksheets["Blatt im Original"]);
}
if (isILExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Import des Logs"]);
}
if (isVLExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Log mit Validierungsangaben"]);
}
DataTable dt = DNU.ProccessFile(xtraOpenFileDialog1.FileName, Application.UserAppDataPath, Path.GetDirectoryName(Application.ExecutablePath));
SplashScreenManager.Default.SetWaitFormDescription("Datenverarbeitung … ");
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Blatt im Original";
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = sheet.Tables.Add(dt, 0, 0, options);
table.Columns[0].Name = "Artikel_Id";
table.Columns[1].Name = "Artikelnummer";
table.Columns[2].Name = "Kurztext1";
table.Columns[3].Name = "Kurztext2";
table.Columns[4].Name = "WG";
table.Columns[5].Name = "WA";
table.Columns[6].Name = "WI";
table.Columns[7].Name = "A";
table.Columns[8].Name = "B";
table.Columns[9].Name = "L";
table.Columns[10].Name = "Preis";
sheet.DataValidations.Add(sheet["E:K"], DataValidationType.Custom, "=AND(ISNUMBER(E1))");
sheet.Range["N:N"].NumberFormat = "mmm/yyyy";
sheet.Columns.AutoFit(0, 10);
}
finally
{
workbook.EndUpdate();
}
btnSaveandConfirm.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
SplashScreenManager.CloseForm(false);
}
}
catch (Exception ex)
{
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show(ex.Message);
}
}
private void btnValidateDataNorm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
try
{
if (dtValidityDate == null)
{
return;
}
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
Range range = worksheet.GetDataRange();
Range SelectedRange = worksheet.SelectedCell;
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Validieren...");
int CCount = 11;
int rCount = range.RowCount;
DataTable dt = new DataTable();
for (int j = 0; j < CCount; j++)
{
if (Convert.ToString(worksheet.Rows[0][j].Value) == "Preis")
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(decimal));
}
else
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(string));
}
}
DataRow dr = null;
for (int i = 1; i < rCount; i++)
{
dr = dt.NewRow();
for (int j = 0; j < CCount; j++)
{
try
{
CellValue v = worksheet.Rows[i][j].Value;
dr[j] = Convert.ToString(v);
}
catch (Exception) { }
}
dt.Rows.Add(dr);
}
if (dt.Rows.Count > 0)
{
BSupplier ObjBSupplier = new BSupplier();
DataSet dsResults = ObjBSupplier.ValidateDataNorm(SupplierID, dtValidityDate, dt);
if (dsResults.Tables[0].Rows.Count > 0)
{
IWorkbook workbook = spreadsheetControl1.Document;
bool isVLExists = false;
bool isOLExists = false;
bool isILExists = false;
foreach (Worksheet ws in workbook.Sheets)
{
if (ws.Name == "Blatt im Original")
{
ws.ConditionalFormattings.Clear();
isOLExists = true;
}
else if (ws.Name == "Log mit Validierungsangaben")
{
ws.ConditionalFormattings.Clear();
isVLExists = true;
}
else if (ws.Name == "Import des Logs")
{
ws.ConditionalFormattings.Clear();
isILExists = true;
}
}
if (isOLExists)
{
workbook.Worksheets.Add("Sheet1");
workbook.Worksheets.Remove(workbook.Worksheets["Blatt im Original"]);
}
if (isVLExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Log mit Validierungsangaben"]);
}
if (isILExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Import des Logs"]);
}
if (dsResults.Tables.Count > 1)
{
if (dsResults.Tables[1].Rows.Count > 0)
{
btnSaveandConfirm.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
}
else
{
btnSaveandConfirm.Visibility = DevExpress.XtraBars.BarItemVisibility.Always;
}
}
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Blatt im Original";
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = sheet.Tables.Add(dsResults.Tables[0], 0, 0, options);
table.Columns[0].Name = "Artikel_Id";
table.Columns[1].Name = "Artikelnummer";
table.Columns[2].Name = "Kurztext1";
table.Columns[3].Name = "Kurztext2";
table.Columns[4].Name = "WG";
table.Columns[5].Name = "WA";
table.Columns[6].Name = "WI";
table.Columns[7].Name = "A";
table.Columns[8].Name = "B";
table.Columns[9].Name = "L";
table.Columns[10].Name = "Preis";
table.Columns[11].Name = "New/Update";
table.Columns[12].Name = "OldPreis";
table.Columns[13].Name = "Validity Date";
table.Columns[14].Name = "Comments";
sheet.DataValidations.Add(sheet["E:K"], DataValidationType.Custom, "=AND(ISNUMBER(E1))");
sheet.Range["N:N"].NumberFormat = "mmm/yyyy";
}
finally
{
workbook.EndUpdate();
}
if (dsResults.Tables[1].Rows.Count > 0)
{
workbook.Worksheets.Add("Log mit Validierungsangaben");
Worksheet Vsheet = workbook.Worksheets["Log mit Validierungsangaben"];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = Vsheet.Tables.Add(dsResults.Tables[1], 0, 0, options);
table.Columns[0].Name = "Art_Id";
table.Columns[1].Name = "Comments";
}
finally
{
workbook.EndUpdate();
}
sheet.Columns.AutoFit(0, 14);
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets["Blatt im Original"];
Range LogRange = Vsheet.GetDataRange();
ApplyNotExistsArticleFormatingforValidationLog(sheet, LogRange, Vsheet);
string stRange = SelectedRange.GetReferenceA1();
spreadsheetControl1.SelectedCell = sheet.Cells[stRange];
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Angaben wurden validiert. Bitte prüfen Sie die Hinweise zur Validierung.");
}
else
{
string stRange = SelectedRange.GetReferenceA1();
spreadsheetControl1.SelectedCell = sheet.Cells[stRange];
sheet.Columns.AutoFit(0, 14);
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Angaben wurden validiert. Sämtliche Angaben sind korrekt und vollständig.");
}
}
}
}
catch (Exception ex)
{
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void btnSaveandConfirm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
try
{
if (SupplierID == null)
{
return;
}
if (dtValidityDate == null)
{
return;
}
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
Range range = worksheet.GetDataRange();
int CCount = 11;
int rCount = range.RowCount;
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Importieren...");
DataTable dt = new DataTable();
for (int j = 0; j < CCount; j++)
{
if (Convert.ToString(worksheet.Rows[0][j].Value) == "Preis")
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(decimal));
}
else
{
dt.Columns.Add(Convert.ToString(worksheet.Rows[0][j].Value), typeof(string));
}
}
DataRow dr = null;
for (int i = 1; i < rCount; i++)
{
dr = dt.NewRow();
for (int j = 0; j < CCount; j++)
{
try
{
CellValue v = worksheet.Rows[i][j].Value;
dr[j] = Convert.ToString(v);
}
catch (Exception) {}
}
dt.Rows.Add(dr);
}
if (dt.Rows.Count > 0)
{
BSupplier ObjBSupplier = new BSupplier();
DataTable dtResults = ObjBSupplier.ImportDataNorm(SupplierID, dtValidityDate, dt);
if (dtResults.Rows.Count > 0)
{
IWorkbook workbook = spreadsheetControl1.Document;
bool isILExists = false;
bool isVLExists = false;
foreach (Worksheet ws in workbook.Sheets)
{
if (ws.Name == "Blatt im Original")
{
ws.ConditionalFormattings.Clear();
}
else if (ws.Name == "Import des Logs")
{
ws.ConditionalFormattings.Clear();
isILExists = true;
}
else if (ws.Name == "Log mit Validierungsangaben")
{
ws.ConditionalFormattings.Clear();
isVLExists = true;
}
}
if (isILExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Import des Logs"]);
}
if (isVLExists)
{
workbook.Worksheets.Remove(workbook.Worksheets["Log mit Validierungsangaben"]);
}
workbook.Worksheets.Add("Import des Logs");
Worksheet sheet = workbook.Worksheets["Import des Logs"];
workbook.BeginUpdate();
try
{
ExternalDataSourceOptions options = new ExternalDataSourceOptions()
{
ImportHeaders = true
};
Table table = sheet.Tables.Add(dtResults, 0, 0, options);
table.Columns[0].Name = "Art_ID";
table.Columns[1].Name = "WG";
table.Columns[2].Name = "WA";
table.Columns[3].Name = "WI";
table.Columns[4].Name = "A";
table.Columns[5].Name = "B";
table.Columns[6].Name = "L";
table.Columns[7].Name = "Error";
}
finally
{
workbook.EndUpdate();
}
Range LogRange = sheet.GetUsedRange();
ApplyNotExistsArticleFormatingforImportLog(worksheet, LogRange, sheet);
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets["Blatt im Original"];
}
}
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show("Die Daten wurden erfolgreiche importiert und gespeichert.");
}
catch (Exception ex)
{
SplashScreenManager.CloseForm(false);
XtraMessageBox.Show(ex.Message);
}
}
// DB 처리
#region ○ 메인조회 - MainFind_DisplayData()
private void MainFind_DisplayData()
{
try
{
CoFAS_DevExpressManager.SetCursor(this, Cursors.WaitCursor);
// string strPART_TYPE = _luPART_TYPE.GetValue();
// pPRODUCTION_ORDER_ID = "";
//string pPART_NAME = _luCD_NM.Text;
// string pPART_CODE = _luCD.Text;
string pPROCESS_CODE_MST = "";
string pFROM_DATE = "";
string pTO_DATE = "";
_pCRUD = "R";
//_dtList = new ucWorkOrderDocRegPopupBusiness().ucWorkOrderDocRegPopup_Return(_pCRUD, _pLANGUAGE_TYPE, pPRODUCTION_ORDER_ID, pPART_NAME, pPART_CODE, pPROCESS_CODE_MST).Tables[0];
// _dtList = new ucWorkOrderDocRegPopupBusiness().ucWorkOrderDocRegPopup_Return(_pCRUD, _pLANGUAGE_TYPE, pPRODUCTION_ORDER_ID, "", "", pPROCESS_CODE_MST).Tables[0];
_dsList = new ucWorkOrderDocRegPopupBusiness().ucWorkOrderDocRegPopup_Return2(_pCRUD, _pLANGUAGE_TYPE, _pucWorkOrderDocRegPopupEntity.PART_CODE, _pucWorkOrderDocRegPopupEntity.PROCESS_CODE_MST);
_dtList = _dsList.Tables[0];
_dtList2 = _dsList.Tables[1];
if (_pCRUD == "")
{
_dtList.Rows.Clear();
}
if ((_dtList != null && _dtList.Rows.Count > 0) || (_dtList != null && _pCRUD == ""))
{
// CoFAS_DevExpressManager.BindGridControl(_gdMAIN, _gdMAIN_VIEW, _dtList);
IWorkbook workbook = _sdMAIN.Document;
//Worksheet sheet_1 = workbook.Worksheets["데이터"];
Worksheet sheet_1 = workbook.Worksheets[1];
Worksheet sheet_0 = workbook.Worksheets[0];
// Worksheet sheet_2 = workbook.Worksheets["저장용 데이터"];
Range data_range = sheet_1.GetDataRange();
sheet_1.Clear(data_range);
ExternalDataSourceOptions dsOptions = new ExternalDataSourceOptions();
dsOptions.ImportHeaders = true;
sheet_1.DataBindings.BindToDataSource(_dtList, 3, 0, dsOptions);
sheet_1.DataBindings.BindToDataSource(_dtList2, 0, 0, dsOptions);
//string Select_PartType = _gdMAIN_VIEW.GetFocusedRowCellValue("PART_TYPE").ToString();
//Worksheet sheet_1 = workbook.Worksheets["데이터"];
string Select_PartType = "1003";// sheet_1.GetCellValue(8, 1).ToString();//_gdMAIN_VIEW.GetFocusedRowCellValue("PART_TYPE").ToString();
string name_tmp = string.Empty;
if (Select_PartType == "1001")
{
}
else if (Select_PartType == "1003")
{
// sheet_0.Cells["G7"].SetValue(_dtList2.Rows[0]["make_no"]);
_dtList4 = _dsList.Tables[2];
string menufacture_data = string.Empty;
//제조공정
//고도화 이후, 품목마다 공정 매핑 수정필요
/*
*
* string menufacture_seq = string.Empty;
* for (int i =0; i< _dtList4.Rows.Count;i++)
* {
* menufacture_seq +="공정 " + (i + 1) + ":" + _dtList4.Rows[i]["PROCESS_NAME"] + "\n" + _dtList4.Rows[i]["PROCESS_VALUE"] + "\n";
* sheet_0.Cells["M11"].SetValue(menufacture_seq);
* }
*/
menufacture_data = _dtList4.Rows[0]["PROCESS_VALUE"].ToString();
sheet_0.Cells["M11"].SetValue(menufacture_data);
}
if (_dtList.Rows.Count == 0)
{
// CoFAS_DevExpressManager.BindGridControl(_gdMAIN, _gdMAIN_VIEW, _dtList);
}
}
}
catch (ExceptionManager pExceptionManager)
{
CoFAS_DevExpressManager.ShowErrorMessage(string.Format("{0}\n{1}", pExceptionManager.Exception.Message.ToString(), pExceptionManager.TargetSite.ToString()));
}
finally
{
_gdMAIN_VIEW.BestFitColumns();
CoFAS_DevExpressManager.SetCursor(this, Cursors.Default);
}
}
