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