//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();
}
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);
}
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);
}
// 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();
}
}
// 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);
}
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);
}