//public static void ExportToExcel<T>(List<T> listData, string fileName)
//{
// try
// {
// PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
// PropertyDescriptor prop;
// List<int> guidsColumns = new List<int>();
// for (int i = 0; i < properties.Count; i++)
// {
// prop = properties[i];
// string dataType = prop.PropertyType.ToString();
// if (dataType.ToUpper().Contains("GUID"))
// {
// guidsColumns.Add(Convert.ToChar(i + 1));
// }
// }
// fileName = ValueConverters.MakeValidFileName(fileName);
// ExcelPackage excel = new ExcelPackage(); //install-package epplus
// var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
// workSheet.Cells[1, 1].LoadFromCollection(listData, true);
// workSheet.Cells.AutoFitColumns();
// if (guidsColumns.Count != 0)
// {
// foreach (var item in guidsColumns)
// {
// workSheet.DeleteColumn(item);
// }
// }
// using (var memoryStream = new MemoryStream())
// {
// System.Web.HttpContext.Current.Response.ClearContent();
// System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xlsx");
// excel.SaveAs(memoryStream);
// memoryStream.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
// System.Web.HttpContext.Current.Response.Flush();
// System.Web.HttpContext.Current.Response.End();
// }
// }
// catch (Exception ex)
// {
// Utility.WriteErrorLog("ExportToExcel_" + fileName.Trim(), ref ex);
// }
//}
//public static void ExportToExcel(DbDataReader reader, string fileName)
//{
// try
// {
// ////integer (not really needed unless you need to round numbers, Excel with use default cell properties)
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "0";
// ////integer without displaying the number 0 in the cell
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "#";
// ////number with 1 decimal place
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "0.0";
// ////number with 2 decimal places
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "0.00";
// ////number with 2 decimal places and thousand separator
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";
// ////number with 2 decimal places and thousand separator and money symbol
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";
// ////percentage (1 = 100%, 0.01 = 1%)
// //ws.Cells["A1:A25"].Style.Numberformat.Format = "0%";
// ////default DateTime pattern
// //worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
// ////custom DateTime pattern
// //worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";
// Dictionary<int, string> fieldTypes = new Dictionary<int, string>();
// if (reader.HasRows)
// {
// for (int i = 0; i < reader.FieldCount; i++)
// {
// var columnName = reader.GetName(i);
// var dotNetType = reader.GetFieldType(i).ToString();
// fieldTypes.Add(i, dotNetType.ToString());
// }
// }
// fileName = ValueConverters.MakeValidFileName(fileName);
// ExcelPackage excel = new ExcelPackage(); //install-package epplus
// var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
// workSheet.Cells[1, 1].LoadFromDataReader(reader, true);
// foreach (var item in fieldTypes)
// {
// if (item.Value.ToUpper().Contains("DECIMAL") || item.Value.ToUpper().Contains("DOUBLE"))
// {
// workSheet.Column(item.Key).Style.Numberformat.Format = "#,##0.00";
// }
// if (item.Value.ToUpper().Contains("DATE"))
// {
// workSheet.Column(item.Key).Style.Numberformat.Format = "dd-MM-yyyy HH:mm";
// }
// }
// workSheet.Cells.AutoFitColumns();
// using (var memoryStream = new MemoryStream())
// {
// System.Web.HttpContext.Current.Response.ClearContent();
// System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName + ".xlsx");
// excel.SaveAs(memoryStream);
// memoryStream.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
// System.Web.HttpContext.Current.Response.Flush();
// System.Web.HttpContext.Current.Response.End();
// }
// }
// catch (Exception ex)
// {
// Utility.WriteErrorLog("ExportToExcel_" + fileName.Trim(), ref ex);
// }
//}
public static List <T> DataReaderMapToList <T>(IDataReader dr)
{
List <T> list = new List <T>();
T obj = default(T);
if (dr != null)
{
if (!((System.Data.Common.DbDataReader)dr).HasRows)
{
return(list);
}
var dataReader_fieldNames = Enumerable.Range(0, dr.FieldCount).Select(i => dr.GetName(i).ToUpper().Trim()).ToArray();
while (dr.Read())
{
obj = Activator.CreateInstance <T>();
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
if (dataReader_fieldNames.Contains(prop.Name.ToUpper().Trim()))
{
if (!object.Equals(dr[prop.Name], DBNull.Value))
{
if (prop.PropertyType.Name.ToLower() == "double")
{
prop.SetValue(obj, ValueConverters.StringtoDouble(dr[prop.Name].ToString()), null);
}
else
{
prop.SetValue(obj, dr[prop.Name], null);
}
}
}
}
list.Add(obj);
}
}
if (list == null)
{
list = new List <T>();
}
return(list);
}
public List <CustomerBalances> GetCustomerBalances(string customerNo, DateTime endDate, List <CustomerBalances> custBalances)
{
custBalances = custBalances ?? new List <CustomerBalances>();
custBalances.Clear();
try
{
string sqlstatement = " CustomerBalances '" + ValueConverters.format_sql_string(customerNo) + "','" + ValueConverters.FormatSqlDate(endDate, true) + "'";
DbDataReader reader = DbConnector.GetSqlReader(sqlstatement);
double balance, interest, insurance, penalty, appraisal;
while (reader.Read())
{
balance = ValueConverters.StringtoDouble(reader["Balance"].ToString());
interest = ValueConverters.StringtoDouble(reader["IntBalance"].ToString());
insurance = ValueConverters.StringtoDouble(reader["InsBalance"].ToString());
penalty = ValueConverters.StringtoDouble(reader["PenBalance"].ToString());
appraisal = ValueConverters.StringtoDouble(reader["AppBalance"].ToString());
custBalances.Add(new CustomerBalances
{
CustomerNo = ValueConverters.ConvertNullToEmptyString(reader["CustomerNo"].ToString()),
AccountNo = ValueConverters.ConvertNullToEmptyString(reader["AccountNo"].ToString()),
CustomerName = ValueConverters.ConvertNullToEmptyString(reader["CustomerName"].ToString()),
Category = ValueConverters.ConvertNullToEmptyString(reader["Category"].ToString()),
Ttype = ValueConverters.ConvertNullToEmptyString(reader["ttype"].ToString()),
ProductName = ValueConverters.ConvertNullToEmptyString(reader["ProductName"].ToString()),
Balance = balance,
IntBalance = interest,
InsBalance = insurance,
PenBalance = penalty,
AppBalance = appraisal,
TotalBalance = ValueConverters.Round05(balance + interest + insurance + penalty + appraisal),
OtherCharges = insurance + penalty + appraisal
});
}
reader.Close();
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
}
return(custBalances);
}
public double GetGlBalance(string accountNo)
{
double glBalance = 0.00;
try
{
string query = "Exec GetGlBalance '" + ValueConverters.format_sql_string(accountNo) + "'";
DbDataReader reader = DbConnector.GetSqlReader(query);
while (reader.Read())
{
glBalance = ValueConverters.StringtoDouble(reader["balance"].ToString());
}
reader.Close();
}
catch (Exception ex)
{
Utility.WriteErrorLog(ref ex);
}
return(glBalance);
}