示例#1
0
        //public static Excel.Worksheet InsertColumn<T>(Excel.Worksheet xlWs, string columnName, List<T> values)
        //{

        //}

        public static void SaveExcel(DataTable dataTable, string fileName)
        {
            FormControl.SetStatus("Saving Excel File...");
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
            string dir = new FileInfo(fileName).DirectoryName;

            Excel.Application xlApp   = new Excel.Application();
            Excel.Workbook    xlWb    = xlApp.Workbooks.Add(Type.Missing);
            Excel.Worksheet   xlWs    = xlWb.Worksheets[1];
            Excel.Range       xlRange = null;
            Type[]            types   = new Type[dataTable.Columns.Count];
            int rowIndex = 0;
            int colIndex = 0;

            subPerc      = 0.00;
            mainPercStep = FormControl.MaxPercSubProc / dataTable.Rows.Count;

            foreach (DataColumn column in dataTable.Columns)
            {
                xlRange       = xlWs.Cells[1, column.Ordinal + 1] as Excel.Range;
                xlRange.Value = column.ColumnName;

                if (dataTable.Columns[colIndex].DataType == typeof(string))
                {
                    xlRange.EntireColumn.NumberFormat = "@";
                }
                else if (dataTable.Columns[colIndex].DataType == typeof(DateTime))
                {
                    xlRange.EntireColumn.NumberFormat = "dd/mm/yyyy hh:mm:ss";
                }

                colIndex++;
            }

            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex = dataTable.Rows.IndexOf(row);
                subPerc  = (rowIndex + 1) / (double)dataTable.Rows.Count * 100.00;

                FormControl.SetSubProgress(subPerc);
                FormControl.SetMainProgress(mainPercStep);

                xlRange       = xlWs.Range[xlWs.Cells[rowIndex + 2, 1], xlWs.Cells[rowIndex + 2, dataTable.Columns.Count]];
                xlRange.Value = row.ItemArray;
            }

            xlWb.SaveAs(fileName, Excel.XlFileFormat.xlExcel8);
            xlWb.Close();
            xlApp.Quit();
        }
示例#2
0
        public static List <T> ToList <T>(string query, Dictionary <string, object> parameters = null, bool acceptDuplicate = false) where T : class, new()
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
            SqlDataReader rdr      = null;
            List <T>      list     = new List <T>();
            int           rowCount = 0;

            FormControl.SetStatus("Querying Database...");

            T obj = new T();

            PropertyInfo[] props = obj.GetType().GetProperties();
            PropertyInfo   propInfo;

            rdr = Query(query, out rowCount, parameters);
            double stepSub = 100.00 / rowCount;

            mainPercStep = FormControl.MaxPercSubProc / rowCount;

            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    FormControl.SetSubProgress(stepSub);
                    FormControl.SetMainProgress(mainPercStep);

                    obj = new T();

                    for (int i = 0; i < props.Length; i++)
                    {
                        propInfo = props[i];
                        propInfo.SetValue(obj, rdr.GetValue(i), null);
                    }

                    if (!acceptDuplicate && list.Contains(obj))
                    {
                        continue;
                    }

                    list.Add(obj);
                }
            }
            else
            {
                return(null);
            }

            return(list);
        }
示例#3
0
        public static void SaveAsText(DataTable table, string fileName, string del = "|")
        {
            double        subPerc = 0.00;
            List <string> lines   = new List <string>();

            mainPercStep = FormControl.MaxPercSubProc / table.Rows.Count;

            lines.Add(String.Join(del, table.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray()));

            foreach (DataRow row in table.Rows)
            {
                subPerc = (table.Rows.IndexOf(row) + 1) / table.Rows.Count * 100.00;
                FormControl.SetSubProgress(subPerc);
                FormControl.SetMainProgress(mainPercStep);
                lines.Add(String.Join(del, row.ItemArray));
            }

            File.WriteAllLines(fileName, lines);
        }
示例#4
0
        // Create List of Generic type from Excel file
        public static List <T> ToList <T>(string excelFilePath, int[] columns = null, int headerRow = 1, bool acceptDuplicate = false) where T : class, new()
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
            Excel.Application xlApp = null;
            Excel.Workbook    xlWb  = null;
            subPerc = 0.00;
            string line;

            try
            {
                FormControl.SetStatus("Reading Excel File...");
                List <T>       list     = new List <T>();
                PropertyInfo[] props    = null;
                PropertyInfo   propInfo = null;
                xlApp = new Excel.Application();
                xlWb  = xlApp.Workbooks.Open(excelFilePath);
                Excel.Worksheet xlWs        = xlWb.Worksheets[1];
                Excel.Range     xlRange     = xlWs.UsedRange;
                int             rowCount    = xlRange.Rows.Count;
                int             columnCount = (columns != null && columns.Length > 0) ? columns.Length : xlRange.Columns.Count;
                int             recordCount = rowCount - headerRow;
                mainPercStep = FormControl.MaxPercSubProc / recordCount;

                T obj = new T();
                props = obj.GetType().GetProperties();

                if (columns == null || columns.Length == 0)
                {
                    columns = Enumerable.Range(1, columnCount).ToArray();
                }

                for (int row = 1; row <= recordCount; row++)
                {
                    subPerc = row / (double)recordCount * 100.00;
                    FormControl.SetSubProgress(subPerc);
                    FormControl.SetMainProgress(mainPercStep);

                    obj  = new T();
                    line = string.Empty;

                    for (int col = 0; col < columnCount; col++)
                    {
                        object value;
                        try
                        {
                            propInfo = props[col];
                            Type type = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;
                            value = Convert.ChangeType(xlWs.Cells[headerRow + row, columns[col]].Value, type);
                            propInfo.SetValue(obj, value, null);
                        }
                        catch //(Exception ex)
                        {
                            continue;
                        }

                        line += value != null?value.ToString() : String.Empty;
                    }

                    if (!acceptDuplicate && list.Contains(obj) || String.IsNullOrEmpty(line.Trim()))
                    {
                        continue;
                    }

                    list.Add(obj);
                }

                return(list);
            }
            catch
            {
                return(null);
            }
            finally
            {
                xlWb.Close();
                xlApp.Quit();
            }
        }
示例#5
0
        // Transfer Text file data to DataTable
        public static DataTable FromTextFile(string textFilePath, char del = '|', string[] customColumns = null)
        {
            FormControl.SetStatus("Reading Text File...");
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");
            DataTable dataTable = new DataTable();
            Type      dataType;

            string[] lines   = File.ReadAllLines(textFilePath);
            string[] headers = lines[0].Split(del);
            string   headerName;
            object   defaultValue;
            double   subPerc = 0.0;

            mainPercStep = FormControl.MaxPercSubProc / (lines.Length - 1);

            foreach (string header in headers)
            {
                headerName   = header;
                defaultValue = null;
                dataType     = typeof(string);

                if (customColumns != null && customColumns.Length != 0)
                {
                    foreach (string column in customColumns)
                    {
                        string[] columns = column.Split(del);

                        if (header.Trim().ToLower().Equals(columns[0].Trim().ToLower()))
                        {
                            headerName = CreateColumn(dataTable, columns);
                            break;
                        }
                    }
                }

                if (!dataTable.Columns.Contains(headerName))
                {
                    DataColumn dataColumn = new DataColumn
                    {
                        DefaultValue = defaultValue,
                        DataType     = dataType,
                        ColumnName   = headerName
                    };

                    dataTable.Columns.Add(dataColumn);
                }
            }

            Type[] types = new Type[headers.Length];

            for (int row = 1; row < lines.Length; row++)
            {
                subPerc = row / (lines.Length - 1.00) * 100.00;
                FormControl.SetSubProgress(subPerc);
                FormControl.SetMainProgress(mainPercStep);

                string rowString = lines[row];

                if (rowString.Trim().Length == 0)
                {
                    continue;
                }

                DataRow dataRow = dataTable.Rows.Add();

                for (int col = 0; col < headers.Length; col++)
                {
                    if (row == 1)
                    {
                        types[col] = dataTable.Columns[col].DataType;
                    }

                    string[] columns = rowString.Split(del);

                    if (!String.IsNullOrEmpty(columns[col]))
                    {
                        if (types[col] == typeof(string))
                        {
                            dataRow[col] = columns[col];
                        }
                        else if (types[col] == typeof(int))
                        {
                            dataRow[col] = Int32.Parse(columns[col]);
                        }
                        else if (types[col] == typeof(DateTime))
                        {
                            dataRow[col] = DateTime.Parse(columns[col]);
                        }
                        else if (types[col] == typeof(double))
                        {
                            dataRow[col] = Double.Parse(columns[col]);
                        }
                        else if (types[col] == typeof(bool))
                        {
                            dataRow[col] = Boolean.Parse(columns[col]);
                        }
                        else
                        {
                            dataRow[col] = columns[col];
                        }
                    }
                }
            }

            return(dataTable);
        }
示例#6
0
        private void Process()
        {
            Thread.Sleep(1000);
            DataTable          table;
            List <PhoneNumber> pNumbers;
            List <CCLN>        cclns;

            FormControl.MaxPercSubProc = 100.00 / 6;

            string textFile   = @"C:\Users\sculabat\Desktop\Project\Arrow Global\Text File Template.txt";
            string teleAppend = @"C:\Users\sculabat\Desktop\Project\Arrow Global\Trace Tele-Appends - Template.xls";
            string cclnFile   = @"C:\Users\sculabat\Desktop\Project\Arrow Global\CCL_Number_Mapping.xlsx";

            string[] customColumns = new string[]
            {
                "ArrowKey||txt|",
                "CustAcctNo||txt|",
                "DefaultDate|||01/01/1900",
                "DefaultAmount|||0.00",
                "Debtor1_addressLine1|Address line 1||",
                "Debtor1_addressLine2|Address line 2||",
                "Debtor1_addressLine3|City||",
                "Debtor1_addressLine4|County||",
                "Debtor1_postcode|Post Code||",
            };

            table = Table.FromTextFile(textFile, customColumns: customColumns);

            FormControl.ViewData(table);

            int[] columns = new int[] { 1, 2, 4, 5 };
            pNumbers = ExcelFile.ToList <PhoneNumber>(teleAppend, columns);

            //string arrowKeys = String.Join(", ", pNumbers.AsEnumerable().Select(c => c.ArrowKey).ToArray());

            columns = new int[] { 1, 2 };
            cclns   = ExcelFile.ToList <CCLN>(cclnFile, columns);

            Table.InsertColumns(table, new string[] { "NumberMobile1|43||", "NumberWork1|44||", "NumberHome1|45||", "Entity|51|int|2913", "CCLN|52|int|" });

            double subPerc  = 0.00;
            double stepPerc = FormControl.MaxPercSubProc / table.Rows.Count;

            FormControl.SetStatus("Formatting Data...");

            foreach (DataRow row in table.Rows)
            {
                subPerc = (table.Rows.IndexOf(row) + 1) / (double)table.Rows.Count * 100.00;
                FormControl.SetSubProgress(subPerc);
                FormControl.SetMainProgress(stepPerc);

                string      arrowKey = row["arrowKey"].ToString().Trim().ToLower();
                PhoneNumber pNum     = pNumbers.Find(p => p.ArrowKey.Trim().ToLower() == arrowKey);

                if (pNum != null)
                {
                    row["NumberMobile1"] = PadLeft(pNum.Mobile, 11);
                    row["NumberWork1"]   = PadLeft(pNum.Work, 11);
                    row["NumberHome1"]   = PadLeft(pNum.Home, 11);
                }

                string buyerName = row["BuyerName"].ToString().Trim().ToLower();
                CCLN   ccln      = cclns.Find(c => c.ArrowEntity.Trim().ToLower() == buyerName);

                if (ccln != null)
                {
                    row["CCLN"] = ccln.LicenseNumber;
                }

                row["HomePhoneNumber"]   = PadLeft(row["HomePhoneNumber"], 11);
                row["WorkPhoneNumber"]   = PadLeft(row["WorkPhoneNumber"], 11);
                row["MobilePhoneNumber"] = PadLeft(row["MobilePhoneNumber"], 11);

                if (!IsNullOrEmpty(row["Debtor1_addressLine5"]))
                {
                    row["County"] = String.Format("{0}, {1}", row["County"], row["Debtor1_addressLine5"]);
                }
            }

            FormControl.ViewData(table);

            string excelFileName = textFile.Remove(textFile.LastIndexOf('.'), 4) + " - To Load.xls";

            ExcelFile.SaveExcel(table, excelFileName);

            Table.SaveAsText(table, excelFileName.Replace("xls", "csv"));

            FormControl.SetStatus("Done!");

            //Thread.Sleep(3000);

            //Environment.Exit(0);
        }