Exemplo n.º 1
0
        static DataTable ConvertInOut2(DataTable dataTable,
                                       DataView dvCol,
                                       Worksheet worksheet, int startRow, string keyType)
        {
            string tempData = "", mParent = "", mCondition = "";
            bool   lockRecord = false;
            int    totalRow = 0, stRow = 1;;
            string condition = "";
            string realData = "";
            string replaceCondition = "";
            string header = "", tranCode = "";
            string conditionSection            = "";
            List <ImportExcelToArray> listData = new List <ImportExcelToArray>();
            DataRow            newRow          = null;
            ImportExcelToArray data            = null;
            Range rangeAll                     = worksheet.GetDataRange();

            #region incoming case


            #region Keep data
            for (int i = startRow; i <= rangeAll.RowCount - 1; i++)
            {
                tempData = worksheet.Cells[i, 0].DisplayText;
                if (tempData.IndexOf(keyType) > -1) // is in coming data ?
                {
                    if (tempData.IndexOf("{2:O") > -1)
                    {
                        if (tempData.IndexOf("| {2:O103") > -1 || tempData.IndexOf("| {2:O202") > -1)
                        {
                            header = tempData;

                            data        = new ImportExcelToArray();
                            data.Header = header;
                            lockRecord  = true;
                        }
                    }
                    else if (tempData.IndexOf("{2:I") > -1)
                    {
                        if (tempData.IndexOf("| {2:I103") > -1 || tempData.IndexOf("| {2:I202") > -1)
                        {
                            header = tempData;

                            data        = new ImportExcelToArray();
                            data.Header = header;
                            lockRecord  = true;
                        }
                    }
                }



                if (lockRecord)
                {
                    if (tempData.IndexOf("|---------------------------------------") == -1)
                    {
                        data.Detail.Add(tempData);
                    }
                }


                if (tempData.IndexOf("|---------------------------------------") > -1 && lockRecord) // is in coming data ?
                {
                    listData.Add(data);
                    lockRecord = false;
                }
            }
            #endregion

            if (listData != null && listData.Count > 0)
            {
                foreach (ImportExcelToArray dataArr in listData)

                {
                    if (dataArr.Header.IndexOf("| {2:O103") > -1 || dataArr.Header.IndexOf("| {2:I103") > -1)
                    {
                        conditionSection = "";
                        tranCode         = dataArr.Header.Split(':')[1].Substring(0, 4);
                    }
                    else if (dataArr.Header.IndexOf("| {2:O202") > -1 || dataArr.Header.IndexOf("| {2:I202") > -1)
                    {
                        tranCode         = dataArr.Header.Split(':')[1].Substring(0, 4);
                        conditionSection = "2";
                    }


                    newRow = dataTable.NewRow();

                    //  foreach (string dataExcel in dataArr.Detail)

                    //  {

                    //   DataView dvH= dvCol.RowFilter=""
                    dvCol.RowFilter = string.Format("isnull(ParentID,'')='' and DID='{0}'", dvCol[0]["DID"].ToString());
                    DataView dvH = dvCol;

                    foreach (DataRowView dr in dvH)
                    {
                        condition        = dr["Condition" + conditionSection].ToString();
                        replaceCondition = condition;
                        int start = dataArr.Detail.FindIndex(s => s.IndexOf(condition) > -1);

                        if (start > -1)
                        {
                            if (DTO.Util.Converting.StringToInt(dr["TotalRow" + conditionSection].ToString()) > 0)
                            {
                                DataView dvFilterBySplit = new DataView(dvCol.Table);
                                dvFilterBySplit.RowFilter = string.Format("ParentID='{0}'", string.Format("{0}{1}", dr["DID"].ToString(), dr["No"].ToString()));

                                realData = GetRealData(dataArr.Detail[start]);

                                newRow[dr["ColumnName"].ToString()] = (dr["ColumnName"].ToString() == "TransactionCode" ? tranCode : "") + realData.Replace(replaceCondition, "")
                                                                      .Replace("|", "")
                                                                      .Replace(":/", "")
                                                                      .Replace("}", "")
                                                                      .Replace("{", "").Trim();

                                foreach (DataRowView drForsplite in dvFilterBySplit)
                                {
                                    int maxLength = Converting.StringToInt(drForsplite["PositionLength" + conditionSection].ToString());
                                    start++;

                                    if (dataArr.Detail[start].IndexOf("|    ") > -1)
                                    {
                                        realData = GetRealData(dataArr.Detail[start]);
                                        newRow[drForsplite["ColumnName"].ToString()] = realData.Replace(replaceCondition, "")
                                                                                       .Replace("|", "")
                                                                                       .Replace(":/", "")
                                                                                       .Replace("}", "")
                                                                                       .Replace("{", "").Trim();
                                    }
                                    else
                                    {
                                        break;
                                    }
                                    // newRow[drForsplite["ColumnName"].ToString()] = dataForsplit.Substring(Converting.StringToInt(drForsplite["StartPosition"].ToString())
                                    //                                                                , maxLength == 0 ? ((dataForsplit.Length) - (Converting.StringToInt(drForsplite["StartPosition"].ToString()))) : maxLength);
                                }
                            }
                            else if (dr["StartPosition"].ToString() != "") // :32A: TransactionDate-6-YYMMDD/ CurrecyCode-3/ CurrAmount
                            {
                                DataView dvFilterBySplit = new DataView(dvCol.Table);
                                dvFilterBySplit.RowFilter = string.Format("ParentID='{0}'", string.Format("{0}{1}", dr["DID"].ToString(), dr["No"].ToString()));

                                realData = GetRealData(dataArr.Detail[start]);

                                string dataForsplit = realData.Replace(replaceCondition.ToString(), "")
                                                      .Replace("|", "")
                                                      .Replace(":/", "")
                                                      .Replace("}", "")
                                                      .Replace("{", "").Trim();
                                int maxLength = Converting.StringToInt(dr["PositionLength" + conditionSection].ToString());

                                newRow[dr["ColumnName"].ToString()] = dataForsplit.Substring(Converting.StringToInt(dr["StartPosition"].ToString())
                                                                                             , maxLength == 0 ? ((dataForsplit.Length) - (Converting.StringToInt(dr["StartPosition"].ToString()))) : maxLength);


                                foreach (DataRowView drForsplite in dvFilterBySplit)
                                {
                                    maxLength = Converting.StringToInt(drForsplite["PositionLength" + conditionSection].ToString());
                                    realData  = GetRealData(dataForsplit);
                                    newRow[drForsplite["ColumnName"].ToString()] = realData.Substring(Converting.StringToInt(drForsplite["StartPosition"].ToString())
                                                                                                      , maxLength == 0 ? ((dataForsplit.Length) - (Converting.StringToInt(drForsplite["StartPosition"].ToString()))) : maxLength);
                                }
                            }
                            else
                            {
                                if (replaceCondition != "")
                                {
                                    realData = GetRealData(dataArr.Detail[start]);
                                    newRow[dr["ColumnName"].ToString()] = (dr["ColumnName"].ToString() == "TransactionCode" ? tranCode : "") + realData.Replace(replaceCondition, "")
                                                                          .Replace("|", "")
                                                                          .Replace(":/", "")
                                                                          .Replace("}", "")
                                                                          .Replace("{", "").Trim();
                                }
                            }
                        }
                    }


                    dataTable.Rows.Add(newRow);
                }
            }

            #endregion

            return(dataTable);
        }
Exemplo n.º 2
0
        static DataTable ConvertInOut(DataTable dataTable,
                                      DataView dvCol,
                                      Worksheet worksheet, int startRow, string keyType)
        {
            string  tempData = "", mParent = "", mCondition = "";
            bool    lockRecord = false;
            int     totalRow = 0, stRow = 1;;
            string  condition        = "";
            string  replaceCondition = "";
            string  header           = "";
            string  conditionSection = "";
            DataRow newRow           = null;
            Range   rangeAll         = worksheet.GetDataRange();

            #region incoming case
            for (int i = startRow; i <= rangeAll.RowCount - 1; i++)
            {
                tempData = worksheet.Cells[i, 0].DisplayText;
                if (tempData.IndexOf(keyType) > -1) // is in coming data ?
                {
                    header     = tempData;
                    newRow     = dataTable.NewRow();
                    lockRecord = true;
                }


                if (lockRecord)
                {
                    foreach (DataRowView dr in dvCol)
                    {
                        if (header.IndexOf("| {2:O103") > -1)
                        {
                        }
                        else if (header.IndexOf("| {2:O202") > -1)
                        {
                            conditionSection = "2";
                        }
                        condition        = dr["Condition" + conditionSection].ToString();
                        replaceCondition = condition;
                        bool hasData = false;

                        hasData = tempData.IndexOf(condition) > -1;



                        /*  if (!hasData && condition.IndexOf("50K") > -1 && dr["ParentID"].ToString() == "")
                         * {
                         *    hasData = tempData.IndexOf(":50F:") > -1;
                         *    if (hasData)
                         *    {
                         *        replaceCondition = condition.Replace("50K", "50F");
                         *    }
                         *
                         *    hasData = tempData.IndexOf(":52A:") > -1;
                         *
                         *    if (hasData)
                         *    {
                         *        replaceCondition = condition.Replace("50K", "52A");
                         *    }
                         *
                         *
                         * }*/

                        replaceCondition = replaceCondition.Replace("/", "");



                        if (hasData
                            ) // is in coming data ?
                        {
                            if (DTO.Util.Converting.StringToInt(dr["TotalRow" + conditionSection].ToString()) > 0)
                            {
                                mParent    = string.Format("{0}{1}", dr["DID"].ToString(), dr["No"].ToString());
                                mCondition = condition;
                                stRow      = 1;
                            }
                            else
                            {
                                mParent    = "";
                                mCondition = "";
                            }


                            if (dr["StartPosition"].ToString() != "") // :32A: TransactionDate-6-YYMMDD/ CurrecyCode-3/ CurrAmount
                            {
                                DataView dvFilterBySplit = new DataView(dvCol.ToTable());
                                dvFilterBySplit.RowFilter = string.Format("Condition='{0}'", condition);
                                string dataForsplit = tempData.Replace(replaceCondition.ToString(), "")
                                                      .Replace("|", "")
                                                      .Replace(":/", "")
                                                      .Replace("}", "")
                                                      .Replace("{", "").Trim();
                                foreach (DataRowView drForsplite in dvFilterBySplit)
                                {
                                    int maxLength = Converting.StringToInt(drForsplite["PositionLength" + conditionSection].ToString());

                                    newRow[drForsplite["ColumnName"].ToString()] = dataForsplit.Substring(Converting.StringToInt(drForsplite["StartPosition"].ToString())
                                                                                                          , maxLength == 0 ? ((dataForsplit.Length) - (Converting.StringToInt(drForsplite["StartPosition"].ToString()))) : maxLength);
                                }
                            }

                            else
                            {
                                newRow[dr["ColumnName"].ToString()] = tempData.Replace(replaceCondition, "")
                                                                      .Replace("|", "")
                                                                      .Replace(":/", "")
                                                                      .Replace("}", "")
                                                                      .Replace("{", "").Trim();
                            }
                            break;
                        }
                        else if (dr["ParentID"].ToString() != "" &&
                                 mParent != "" &&
                                 tempData.IndexOf("| :") == -1 &&
                                 tempData.IndexOf("| {") == -1 &&
                                 tempData.IndexOf("| -") == -1) //  :50K:
                        {
                            DataView dvFilterByParent = new DataView(dvCol.ToTable());
                            dvFilterByParent.RowFilter = string.Format("ParentID='{0}'", mParent);
                            foreach (DataRowView drForsplite in dvFilterByParent)
                            {
                                if (string.Format("{0}{1}", mCondition, stRow) == drForsplite["Condition"].ToString())
                                {
                                    newRow[drForsplite["ColumnName"].ToString()] = tempData.Replace(replaceCondition, "")
                                                                                   .Replace("|", "")
                                                                                   .Replace(":/", "")
                                                                                   .Replace("}", "")
                                                                                   .Replace("{", "").Trim();
                                    break;
                                }
                            }

                            stRow++;
                            break;
                        }
                    }
                }


                if (tempData.IndexOf("|---------------------------------------") > -1 && lockRecord) // is in coming data ?
                {
                    dataTable.Rows.Add(newRow);
                    lockRecord = false;
                }
            }

            #endregion

            return(dataTable);
        }