/// <summary>
/// Reflects into list and makes header row and data rows
/// </summary>
/// <param name="type"></param>
/// <param name="list"></param>
private Excel.Worksheet excelExport(Type type, List <T> list)
{
//make xlApp
Excel.Application xlApp = ExcelOps.makeExcelApp();
xlApp.Workbooks.Add();
xlApp.Visible = true;
//set wks
Excel.Worksheet wks = xlApp.ActiveSheet;
wks.get_Range("1:1").WrapText = true;
wks.get_Range("1:1").Font.Bold = true;
wks.get_Range("A:AC").WrapText = true;
//get property names, place in row 1
PropertyInfo[] properties = type.GetProperties();
Collection <PropertyInfo> props = new Collection <PropertyInfo>();
for (int i = 0; i < properties.Length; i++)
{
Object[] att = properties[i].GetCustomAttributes(true);
for (int j = 0; j < att.Length; j++)
{
Type aType = att[j].GetType();
if (aType.Name == "ExcelExportProperty")
{
props.Add((PropertyInfo)properties[i]);
}
}
}
int col = 1;
foreach (var prop in props)
{
wks.Cells[1, col].Value = prop.Name;
col++;
}
int row = 2;
col = 1;
foreach (var request in list)
{
foreach (var prop in props)
{
wks.Cells[row, col].Value = prop.GetValue(request);
col++;
}
row++;
col = 1;
}
ExcelOps.releaseObject(xlApp);
return(wks);
}
static void Main(string[] args)
{
ExcelOps.InitializeWorkbook();
}
public ActionResult ExportToExcel(string sortPropertyName, string isAscending, string selectedTitleCodesString, string selectedEmployeeId, string selectedDepartmentCodesString)
{
//var employee =
// Repository.OfType<Employee>().Queryable.Where(x => x.Id == selectedEmployeeId).FirstOrDefault();
//var pkEmployee = (employee != null && String.IsNullOrEmpty(employee.Id) == false ? employee.Id : String.Empty);
var user = Esra.Core.Domain.User.GetByLoginId(Repository, User.Identity.Name);
//string userId = user.EmployeeID;
var titleStrings = selectedTitleCodesString.Split('|');
var departmentStrings = selectedDepartmentCodesString.Split('|');
var employees = Employee.GetAllForEmployeeTable(Repository,
user,
IsDepartmentUser,
sortPropertyName,
Convert.ToBoolean(isAscending),
titleStrings,
selectedEmployeeId,
departmentStrings);
// Convert the employees list to a datatable
var eops = new ExcelOps();
var borders = new List <CAESOps.ExcelBorder>();
const string tempString = "";
var stringType = tempString.GetType();
const decimal tempDecimal = new Decimal();
var decimalType = tempDecimal.GetType();
const double tempDouble = new Double();
var doubleType = tempDouble.GetType();
var dt = new DataTable();
// Add the data headers:
dt.Columns.Add("Department Name", stringType);
dt.Columns.Add("Title Code", stringType);
dt.Columns.Add("Salary Grade", stringType);
dt.Columns.Add("Bargaining Unit", stringType);
dt.Columns.Add("Employee Name", stringType);
dt.Columns.Add("Hire Date", stringType);
dt.Columns.Add("Years Of Service", doubleType);
dt.Columns.Add("Begin Date (In Title)", stringType);
dt.Columns.Add("Time In Title", doubleType);
dt.Columns.Add("Experience Begin Date", stringType);
dt.Columns.Add("Years Of Experience", doubleType);
dt.Columns.Add("Pay Rate", doubleType);
dt.Columns.Add("Department Comments", stringType);
dt.Columns.Add("Deans Office Comments", stringType);
foreach (var emp in employees)
{
var row = dt.NewRow();
row["Department Name"] = emp.HomeDepartment.Name;
row["Employee Name"] = emp.FullName;
row["Department Comments"] = emp.DepartmentComments;
row["Deans Office Comments"] = emp.DeansOfficeComments;
row["Title Code"] = emp.TitleCode;
row["Salary Grade"] = emp.SalaryGrade;
row["Bargaining Unit"] = emp.BargainingUnitCode;
row["Hire Date"] = String.Format("{0:MM/dd/yyyy}", emp.AdjustedCareerHireDate);
row["Years Of Service"] = (emp.YearsOfService ?? 0d);
row["Begin Date (In Title)"] = String.Format("{0:MM/dd/yyyy}", emp.AdjustedApptHireDate);
row["Time In Title"] = (emp.TimeInTitle ?? 0d);
row["Experience Begin Date"] = String.Format("{0:MM/dd/yyyy}", emp.ExperienceBeginDate);
row["Years Of Experience"] = (emp.YearsOfExperience ?? 0d);
//row["Years Of Experience"] = emp.YearsOfExperience;
row["Pay Rate"] = emp.PayRate;
if (IsDepartmentUser)
{
// Check if is department employee and blank out field if not:
//if (!Employee.IsDepartmentEmployee(user, emp))
if (!emp.IsDepartmentEmployee)
{
row["Department Name"] = null;
row["Employee Name"] = null;
row["Department Comments"] = null;
row["Deans Office Comments"] = null;
}
}
dt.Rows.Add(row);
}
eops.HorizontalFreeze = 1;
var numberTwoDecimalStyle = new ExcelStyle {
Format = PredefinedExcelStyles.NumberTwoDecimal
};
eops.AddColumnStyle("Years Of Service", numberTwoDecimalStyle);
eops.AddColumnStyle("Time In Title", numberTwoDecimalStyle);
eops.AddColumnStyle("Years Of Experience", numberTwoDecimalStyle);
var currencyStyle = new ExcelStyle {
Format = PredefinedExcelStyles.CurrencyTwoDecimal
};
eops.AddColumnStyle("Pay Rate", currencyStyle);
var byteArray = eops.ExportToExcel(dt);
Session["ExportExcel"] = byteArray;
return(File(byteArray, "application/vnd.ms-excel", "MyExportedFile.xml"));
}
