private void simpleButton1_Click(object sender, EventArgs e)
{
string filename = "";
splashScreenManager1.SplashFormStartPosition = DevExpress.XtraSplashScreen.SplashFormStartPosition.Default;
try
{
filename = CUtil.LoadExcel(spread1);
splashScreenManager1.ShowWaitForm();
IWorkbook workbook = spread1.Document;
foreach (Worksheet item in workbook.Worksheets)
{
int nRow = item.GetUsedRange().RowCount;
IEnumerable <Cell> searchResult = ExcelDataSourceExtension.FindCell(item, "측점");
string strPos2;
string strPos1;
string strPos3;
foreach (Cell cell in searchResult)
{
if (cell.ColumnIndex != 1)
{
continue;
}
CPipeData Data = new CPipeData();
Data.SheetName = item.Name;
Data.Data1Position = "B3:AN3";
Data.Data1RowIndex = 2;
Data.Data3Position = "AQ3:AR3";
Data.Data3RowIndex = 2;
strPos1 = string.Format("{0}{1}:{2}{3}", "B", 3, "AN", cell.RowIndex - 1);
strPos3 = string.Format("{0}{1}:{2}{3}", "AT", 3, "AW", cell.RowIndex - 1);
if (nRow == cell.RowIndex + 1)
{
strPos2 = string.Format("{0}{1}:{2}{3}", "B", cell.RowIndex + 1, "H", nRow);
}
else
{
strPos2 = string.Format("{0}{1}:{2}{3}", "B", cell.RowIndex + 1, "H", nRow - 1);
}
// = string.Format("{0}{1}:{2}{3}", "AQ", 3, "AR", nRow - 1);
Data.Data2Position = strPos2;
Data.Data2RowIndex = cell.RowIndex;
Data.Data1 = ExcelDataSourceExtension.ExcelToDataSource(filename, item, strPos1);
Data.Data2 = ExcelDataSourceExtension.ExcelToDataSource(filename, item, strPos2);
DataTable extend = ExcelDataSourceExtension.ExcelToDataSource(filename, item, strPos3);
SetExtend(Data.Data1, extend);
// Data.Data3 = GetDataTable(item, strPos3);
//Data.ManholeDt = GetManholeData1(Data.Data1);
PipeMngr.Add(Data);
}
}
int nCnt = PipeMngr.Data.Count;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
splashScreenManager1.CloseWaitForm();
}
}
/// <summary>
/// 막서 데이터로 변환한다.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void simpleButton2_Click(object sender, EventArgs e)
{
splashScreenManager1.SplashFormStartPosition = DevExpress.XtraSplashScreen.SplashFormStartPosition.Default;
splashScreenManager1.ShowWaitForm();
String sheetName = "";
try
{
IWorkbook workbook = spread1.Document;
CPipeDataMngr PipeMngr = new CPipeDataMngr();
///PipeTool을 처리하기 위한 필드값을 찾는다.
///
int n = workbook.Worksheets.Count;
foreach (Worksheet item in workbook.Worksheets)
{
sheetName = item.Name;
//sheet에 (H) 가 있으면 skip
if (item.Name.Contains("H") == true)
{
continue;
}
CPipeData Data = new CPipeData();
Data.SheetName = item.Name;
Data.Data1Position = "B3:AN3";
Data.Data1RowIndex = 2;
Data.Data3Position = "AQ3:AR3";
Data.Data3RowIndex = 2;
//Data2의 시작 위치를 찾아볼까
SearchOptions options = new SearchOptions();
options.SearchBy = SearchBy.Columns;
options.SearchIn = SearchIn.Values;
options.MatchEntireCellContents = true;
//item.Search("측점", options);
//worksheet의 유효 데이터 Row를 구한다.
int nRow = item.GetUsedRange().RowCount;
string strPos2;
string strPos1;
string strPos3;
// Data.Data3 = GetDataTable(item, Data.Data3Position);
IEnumerable <Cell> searchResult = item.Search("측점", options);
foreach (Cell cell in searchResult)
{
if (cell.ColumnIndex != 1)
{
continue;
}
strPos1 = string.Format("{0}{1}:{2}{3}", "B", 3, "AN", cell.RowIndex - 1);
if (nRow == cell.RowIndex + 1)
{
strPos2 = string.Format("{0}{1}:{2}{3}", "B", cell.RowIndex + 1, "H", nRow);
}
else
{
strPos2 = string.Format("{0}{1}:{2}{3}", "B", cell.RowIndex + 1, "H", nRow - 1);
}
// = string.Format("{0}{1}:{2}{3}", "AQ", 3, "AR", nRow - 1);
Data.Data2Position = strPos2;
Data.Data2RowIndex = cell.RowIndex;
Data.Data1 = GetDataTable(item, strPos1);
Data.Data2 = GetDataTable(item, strPos2);
// Data.Data3 = GetDataTable(item, strPos3);
Data.ManholeDt = GetManholeData1(Data.Data1);
}
////포장측점이 있는지 찾는다.
//IEnumerable<Cell> searchResult2 = item.Search("포장측점", options);
//foreach (Cell cell in searchResult2)
//{
// strPos3 = string.Format("{0}{1}:{2}{3}", "AQ", 3, "AR", nRow - 1);
// Data.Data3 = GetDataTable(item, strPos3);
//}
MakeFLO(Data);
PipeMngr.Add(Data);
}
DataTable MaxerDt = PipeMngr.GetMaxerInputData();
adGridView1.PopulateColumns(MaxerDt);
gridControl1.DataSource = MaxerDt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show(sheetName);
}
finally
{
splashScreenManager1.CloseWaitForm();
}
}