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