public IQueryable<Test> GetAllTestsForStudent(Student student)
{
var result = this.tests
.All()
.Where(t => t.Students.Contains(student));
return result;
}
public void EnrollStudent(Student student)
{
if(!this.students.All().Contains(student))
{
this.students.Add(student);
}
}
public void Init()
{
Console.WriteLine("启动Inset.Init");
using (var db = SugarDao.GetInstance())
{
db.Insert(GetInsertItem()); //插入一条记录 (有主键也好，没主键也好，有自增列也好都可以插进去)
db.InsertRange(GetInsertList()); //批量插入 支持（别名表等功能）
db.SqlBulkCopy(GetInsertList()); //批量插入 适合海量数据插入
//设置不插入列
db.DisableInsertColumns = new string[] { "sex" };//sex列将不会插入值
Student s = new Student()
{
name = "张" + new Random().Next(1, int.MaxValue),
sex = "gril"
};
var id = db.Insert(s); //插入
//查询刚插入的sex是否有值
var sex = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).sex;//无值
var name = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).name;//有值
//SqlBulkCopy同样支持不挺入列设置
db.SqlBulkCopy(GetInsertList());
//清空禁止插入列
db.DisableInsertColumns = null;
//添加禁止插入列
db.AddDisableInsertColumns("name","id");
}
}
private static void ParseInput(string path)
{
using (var reader = new StreamReader(path))
{
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
if (line == null)
{
continue;
}
var words = line
.Split(new char[] { ' ', '|' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
var student = new Student()
{
FirstName = words[0],
LastName = words[1]
};
var course = new Course()
{
Name = words[2]
};
AddOrCreate(course, student);
}
}
}
public void CreatingNewStudent_ShouldGenerateIdInAllowedRange()
{
var student = new Student("Pesho");
Assert.GreaterOrEqual(student.Id, Constants.ID_MIN_VALUE);
Assert.LessOrEqual(student.Id, Constants.ID_MAX_VALUE);
}
public System.Collections.Generic.List <Models.Student> GetStudents()
{
List <Models.Student> result = new List <Models.Student>();
var dbManager = new DBManager("DBConnection");
var students = dbManager.GetDataTable("DAH_User1_GetAll", CommandType.StoredProcedure, null);
Models.Student element;
if (students != null)
{
for (int index = 0; index < students.Rows.Count; index++)
{
element = new Models.Student();
element.Id = Convert.ToInt32(students.Rows[index]["studentId"].ToString());
element.studentFirst_name = students.Rows[index]["studentFirst_name"].ToString();
element.studentLast_name = students.Rows[index]["studentLast_name"].ToString();
element.student_gender = students.Rows[index]["student_gender"].ToString();
string s = Convert.ToString(students.Rows[index]["student_Dob"]);
DateTime DOB = Convert.ToDateTime(s);
element.student_Dob = DOB;
//element.student_Dob = Convert.ToDateTime(students.Rows[index]["student_Dob"].ToString());
element.Passsword = students.Rows[index]["Passsword"].ToString();
element.student_mobileno = Convert.ToInt64(students.Rows[index]["student_mobileno"].ToString());
element.student_address = students.Rows[index]["student_address"].ToString();
result.Add(element);
}
}
return(result);
}
public Student GetStudentById(int Id)
{
Student element = new Student();
var dbManager = new DBManager("connection");
var parameters = new List <IDbDataParameter>();
parameters.Add(dbManager.CreateParameter("@Event", "SelectById", DbType.String));
parameters.Add(dbManager.CreateParameter("@Student_id", Id, DbType.Int32));
var Info = dbManager.GetDataTable("StudentDetails_Proc", CommandType.StoredProcedure, parameters.ToArray());
element = new Models.Student();
element.Id = Convert.ToInt32(Info.Rows[0]["student_id"].ToString());
element.Name = Info.Rows[0]["student_name"].ToString();
element.Gender = Info.Rows[0]["student_gender"].ToString();
element.PhoneNumber = Convert.ToInt64(Info.Rows[0]["student_phone"].ToString());
element.Marks = Convert.ToInt16(Info.Rows[0]["student_marks"].ToString());
element.Address = Info.Rows[0]["student_address"].ToString();
return(element);
}
public void setWeekly(Worksheet targetSheet, Models.Student student)
{
int m = 0, t = 0, w = 0, th = 0, f = 0;
foreach (var b in student.Behaviors)
{
var lastWeek = DateTime.Now.AddDays(-7);
if (b.TimeRecorded.CompareTo(lastWeek) >= 0)
{
switch (b.TimeRecorded.DayOfWeek.ToString())
{
case "Monday": m++;
break;
case "Tuesday": t++;
break;
case "Wednesday": w++;
break;
case "Thursday": th++;
break;
case "Friday": f++;
break;
}
}
}
SetCellValue(targetSheet, "F2", m);
SetCellValue(targetSheet, "G2", t);
SetCellValue(targetSheet, "H2", w);
SetCellValue(targetSheet, "I2", th);
SetCellValue(targetSheet, "J2", f);
}
public void UnpairWatchWithStudent(Student student)
{
try {
this.WatchPairedWith(student).Unpair();
}
catch (WatchDoesNotExistException) { }
}
public void setMonthly(Worksheet targetSheet, Models.Student student)
{
int m = 0, t = 0, w = 0, th = 0, f = 0;
foreach (var b in student.Behaviors)
{
{
switch (b.TimeRecorded.DayOfWeek.ToString())
{
case "Monday": m++;
break;
case "Tuesday": t++;
break;
case "Wednesday": w++;
break;
case "Thursday": th++;
break;
case "Friday": f++;
break;
}
}
SetCellValue(targetSheet, "F3", m);
SetCellValue(targetSheet, "G3", t);
SetCellValue(targetSheet, "H3", w);
SetCellValue(targetSheet, "I3", th);
SetCellValue(targetSheet, "J3", f);
}
}
public List <Models.Student> GetStudentDetails()
{
List <Models.Student> student = new List <Student>();
var dbManager = new DBManager("constr");
var data = dbManager.GetDataTable("Student_Select", CommandType.StoredProcedure);
Models.Student s;
if (data != null)
{
for (int i = 0; i < data.Rows.Count; i++)
{
s = new Models.Student();
s.Id = Convert.ToInt32(data.Rows[i]["student_id"].ToString());
s.Name = data.Rows[i]["student_name"].ToString();
s.Gender = data.Rows[i]["student_gender"].ToString();
s.Marks = Convert.ToDouble(data.Rows[i]["student_marks"].ToString());
s.Phone = Convert.ToInt64(data.Rows[i]["student_phone"].ToString());
s.Address = data.Rows[i]["student_address"].ToString();
student.Add(s);
}
}
return(student);
}
public List <Models.Student> GetInfo()
{
List <Models.Student> result = new List <Models.Student>();
var dbManager = new DBManager("connection");
var parameters = new List <IDbDataParameter>();
parameters.Add(dbManager.CreateParameter("@Event", "Select", DbType.String));
var Info = dbManager.GetDataTable("StudentDetails_Proc", CommandType.StoredProcedure, parameters.ToArray());
Models.Student element;
if (Info != null)
{
for (int index = 0; index < Info.Rows.Count; index++)
{
element = new Models.Student();
element.Id = Convert.ToInt32(Info.Rows[index]["student_id"].ToString());
element.Name = Info.Rows[index]["student_name"].ToString();
element.Gender = Info.Rows[index]["student_gender"].ToString();
element.PhoneNumber = Convert.ToInt64(Info.Rows[index]["student_phone"].ToString());
element.Marks = Convert.ToInt16(Info.Rows[index]["student_marks"].ToString());
element.Address = Info.Rows[index]["student_address"].ToString();
result.Add(element);
}
}
return(result);
}
public void loadStudent(Worksheet targetSheet, Models.Student student)
{
SetCellValue(targetSheet, "A2", student.FirstName + " " + student.LastName);
SetCellValue(targetSheet, "B2", student.BehaviorName);
SetCellValue(targetSheet, "C2", student.Teacher);
SetCellValue(targetSheet, "E2", "Weekly");
SetCellValue(targetSheet, "E3", "Total");
}
private static Student GetInsertItem1()
{
Student s = new Student()
{
name = "张" + new Random().Next(1, int.MaxValue)
};
return s;
}
public void AddingCourseToStudent_ShouldPass()
{
var student = new Student("Pesho");
var course = new Course("Math");
course.Subscribe(student);
Assert.AreEqual(course.Students.Count, 1);
}
// sort students by first name and last name in descending order.
public static Student[] OrderStudents(Student[] students)
{
Student[] orderedStudents = students
.OrderByDescending(student => student.FirstName)
.ThenByDescending(student => student.LastName)
.ToArray();
return orderedStudents;
}
// finds the first name and last name of all students with age between 18 and 24.
public static string[] AgeRange(Student[] students)
{
string[] result = students
.Where(student => student.Years >= 18 && student.Years <= 24)
.Select(student => String.Format("{0}\t{1}", student.FirstName, student.LastName))
.ToArray();
return result;
}
public Watch WatchPairedWith(Student student)
{
try {
return Collection.Where(w => w.Student.Id == student.Id).First();
}
catch {
throw new WatchDoesNotExistException();
}
}
private static void AddOrCreate(Course course, Student student)
{
if (!StudentsCourses.ContainsKey(course))
{
StudentsCourses[course] = new OrderedBag<Student>();
}
StudentsCourses[course].Add(student);
}
public void UnsibscribingStudentFromCourse_ShouldPass(string name)
{
var course = new Course("Math");
var student = new Student(name);
course.Subscribe(student);
course.Unsubscribe(student);
Assert.AreEqual(course.Students.Count, 0);
}
public bool Validate(Student model)
{
if (StandartValidate(model.Firstname))
{
errMsg += "Invalid length of the field firstname! \n";
}
if (StandartValidate(model.Lastname))
{
errMsg += "Invalid length of the field lastname! \n";
}
if (StandartValidate(model.Middlename))
{
errMsg += "Invalid length of the field middlename! \n";
}
if (model.Cource < 0 || model.Cource > maxSizeCourseNumber)
{
errMsg += "Invalid length of the field cource! \n";
}
if (StandartValidate(model.Type))
{
errMsg += "Invalid length of the field type! \n";
}
if (model.Parent < -1 || model.Parent > maxSizeParentId)
{
errMsg += "Invalid length of the field parent! \n";
}
if (StandartValidate(model.Phone))
{
errMsg += "Invalid length of the field phone! \n";
}
if (StandartValidate(model.Passport))
{
errMsg += "Invalid length of the field passport! \n";
}
if (model.Sex<0 || model.Sex>2)
{
errMsg += "Invalid length of the field sex! \n";
}
if (StandartValidate(model.BirthDate))
{
errMsg += "Invalid length of the field birthdate! \n";
}
if (StandartValidate(model.Address))
{
errMsg += "Invalid length of the field address! \n";
}
if (errMsg == null)
{
return true;
}
else
{
AppDelegate.Instance.Alert.ShowAlert(errMsg);
return false;
}
}
public void StudentShouldSubscribeToCourseOnlyOnce()
{
var student = new Student("Pesho");
var course = new Course("Math");
course.Subscribe(student);
course.Subscribe(student);
course.Subscribe(student);
Assert.AreEqual(course.Students.Count, 1);
}
public void AddingMultipleStudentsToCourse()
{
var course = new Course("Biology");
for (int i = 0; i < 35; i++)
{
var student = new Student(i.ToString());
course.Subscribe(student);
}
Assert.AreEqual(course.Students.Count, Constants.MAX_STUDENTS_IN_COURSE);
}
public async Task CreateNewStudent(Student student)
{
await Task.Run(() =>
{
AppDelegate.Instance.dataController.CreateNewStudent(() =>
{
AppDelegate.Instance.Context.ChangeLoaderVisible(false);
AppDelegate.Instance.Alert.ShowAlert("Student successfully created! No errors reported.", true);
},
student);
});
}
private Models.Student BuildStudent()
{
Models.Student s = new Models.Student();
s.Id = txtID.Text;
s.FirstName = txtFName.Text;
s.LastName = txtLName.Text;
s.Address = txtAddress.Text;
s.City = txtCity.Text;
s.PostalCode = txtPostal.Text;
s.PhoneNumber = txtPhone.Text;
s.StartDate = dtpStart.Value;
s.EndDate = dtpEnd.Value;
s.Program = cmbProgram.SelectedItem.ToString();
s.Status = (StudentStatus)cmbStatus.SelectedItem;
return(s);
}
private void BindStudent(Models.Student s)
{
try
{
lblFName.Text = s.FirstName;
lblLName.Text = s.LastName;
lblProgram.Text = s.Program;
lblBalance.Text = s.BalanceDue.ToString("c");
lblEnd.Text = s.EndDate.ToShortDateString();
lblStart.Text = s.StartDate.ToShortDateString();
lblStatus.Text = s.Status.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void BindStudent(Models.Student s)
{
label11.Visible = false;
groupBox1.Visible = true;
student = s;
txtID.Text = s.Id;
txtFName.Text = s.FirstName;
txtLName.Text = s.LastName;
txtAddress.Text = s.Address;
txtCity.Text = s.City;
txtPostal.Text = s.PostalCode;
txtPhone.Text = s.PhoneNumber;
dtpStart.Value = s.StartDate;
dtpEnd.Value = s.EndDate;
cmbProgram.SelectedItem = s.Program;
cmbStatus.SelectedItem = s.Status;
lblBlance.Text = s.BalanceDue.ToString("c");
}
public void setHourlyRecord(Worksheet targetSheet, Models.Student student)
{
var collector = new System.Collections.Generic.Dictionary <ExcelExport.Key, int>();
foreach (var b in student.Behaviors)
{
var key = new ExcelExport.Key {
DayOfWeek = b.TimeRecorded.DayOfWeek.ToString(), Hour = b.TimeRecorded.Hour
};
if (collector.ContainsKey(key))
{
collector[key] += 1;
}
else
{
collector.Add(key, 1);
}
}
foreach (var key in collector.Keys)
{
var value = key.Hour - 1;
switch (key.DayOfWeek.ToString())
{
case "Monday": ((Range)(targetSheet.Cells[6, value])).Value = collector[key];
break;
case "Tuesday": ((Range)(targetSheet.Cells[7, value])).Value = collector[key];
break;
case "Wednesday": ((Range)(targetSheet.Cells[8, value])).Value = collector[key];
break;
case "Thursday": ((Range)(targetSheet.Cells[9, value])).Value = collector[key];
break;
case "Friday": ((Range)(targetSheet.Cells[10, value])).Value = collector[key];
break;
default:
break;
}
}
}
public void CreateNewStudent(Student student)
{
if (student.Avatar == "default")
{
student.Avatar = CommonSettings.GetDefaultAvatar(student.Sex);
}
PostHandler(CommonSettings.CreateNewStudentUrlString(), CommonSettings.CreateNewStudentDataString(student));
}
public void Unpair()
{
Student = null;
}
public void PairWith(Student student)
{
Student = student;
}
public async Task CreateNewStudent(Action action, Student student)
{
if(student.Avatar!= "default")
{
student.Avatar = SendImage(student.Avatar, student.Id, 1);
}
try {
dataProvider.CreateNewStudent(student);
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
}
action();
}
public async Task EditStudent(Action action, Student student)
{
try
{
student.Avatar = SendImage(student.Avatar, student.Id, 1);
}
catch (Exception e)
{
}
dataProvider.EditStudent(student);
action();
}
// from students[] finds all students whose first name is before its last name alphabetically.
public static Student[] FirstBeforeLastName(Student[] students)
{
Student[] returnStudents = students.Where(s => s.FirstName.CompareTo(s.LastName) < 0).ToArray();
return returnStudents;
}
public void PairWith(Student student)
{
Student = student;
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
string aaa = null;
var xxx= db.Queryable<School>().Where(it => it.name==aaa ).ToList();
var sl2 = db.Sqlable().Form<Student>("s").SelectToList<Student>("id");
var sl = db.Sqlable().Form<Student>("s").SelectToList<Student>("*");
db.Delete<Student,int>(1, 2);
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = db.GetInt("select top 1 id from Student") };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
int maxId= db.Queryable<Student>().Max<Student,int>("id");
int minId = db.Queryable<Student>().Where(c=>c.id>10).Min<Student, int>("id");
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
db.Update<School,int>(new { name = "蓝翔2" }, 1,3,12);
db.Update<School, string>(new { name = "蓝翔2" },new string []{"1","2"});
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School,int>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School,string>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id="+stud.id);
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
public Enrollment()
{
Student = new Student();
Course = new Course();
}
public static void AssignStudent(Models.Student s)
{
StudentPassed.Invoke(s, EventArgs.Empty);
}
public EditStudentViewModel(Student student)
{
_canExecute = true;
_studentId = student.Id;
_studentFirstname = student.Firstname;
_studentLastname = student.Lastname;
_studentMiddlename = student.Middlename;
_studentCource = student.Cource;
_studentType = student.Type;
_studentDepartment = student.Parent;
_studentPhone = student.Phone;
_studentPassport = student.Passport;
_studentSex = student.Sex;
_studentBirthDate = student.BirthDate;
_studentAddress = student.Address;
_studentAvatar = student.RealAvatar;
sexVariant = new List<string>();
sexVariant.Add("Male");
sexVariant.Add("Female");
}
public void UnsubscribingNotSubscribedStudent_ShouldDoNothing()
{
var course = new Course("Math");
var student = new Student("Pesho");
var pesho = new Student("Pesho");
course.Subscribe(student);
course.Unsubscribe(pesho);
Assert.AreEqual(course.Students.Count, 1);
}
public void EditStudent(Student student)
{
PostHandler(CommonSettings.EditStudentUrlString(),CommonSettings.EditStudentDataString(student));
}
public void Unpair()
{
Student = null;
}
private void Controller_StudentPassed(object sender, EventArgs e)
{
student = (Models.Student)sender;
BindStudent();
SetState(1);
}
public static string EditStudentDataString(Student student)
{
return "queryName=Student&id=" + student.Id + "&Firstname=" + student.Firstname + "&Lastname=" + student.Lastname + "&Middlename=" + student.Middlename + "&Cource=" + student.Cource + "&Type=" + student.Type + "&Parent=" + student.Parent + "&Phone=" + student.Phone + "&Passport=" + student.Passport + "&Sex=" + student.Sex + "&BirthDate=" + student.BirthDate + "&Address=" + student.Address + "&Avatar=" + student.Avatar;
}
public void ExcelMain(Models.Student student)
{
var lname = student.LastName;
// Declare a variable for the Excel ApplicationClass instance.
Application excelApplication = null;
Workbook newWorkbook = null;
Worksheet targetSheet = null;
ChartObjects chartObjects = null;
ChartObject newChartObject = null;
// Declare variables for the Workbooks.Open method parameters.
string paramWorkbookPath = student.LastName + "-" + student.FirstName + ".xlsx";
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
object paramTitle = "Daily Breakdown for:" + student.LastName + "-" + student.FirstName;
object paramCategoryTitle = "Days of the Week";
object paramValueTitle = "# of offenses recorded";
try
{
// Create an instance of the Excel ApplicationClass object.
excelApplication = new Application();
// Create a new workbook with 1 sheet in it.
newWorkbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
// Change the name of the sheet.
targetSheet = (Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Student";
SetCellValue(targetSheet, "A1", "Name");
SetCellValue(targetSheet, "B1", "Behavior");
SetCellValue(targetSheet, "C1", "Teacher");
loadStudent(targetSheet, student);
setWeekly(targetSheet, student);
setMonthly(targetSheet, student);
setForm(targetSheet);
setHourlyRecord(targetSheet, student);
dataRange = targetSheet.get_Range("A1", "J1");
dataRange.EntireColumn.AutoFit();
// Get the range holding the chart data.
dataRange = targetSheet.get_Range("E1", "J3");
// Get the ChartObjects collection for the sheet.
chartObjects = (ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Add a Chart to the collection.
newChartObject = chartObjects.Add(0, 200, 300, 300);
// Create a new chart of the data.
newChartObject.Chart.ChartWizard(dataRange, XlChartType.xl3DColumn, paramChartFormat, XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing);
//////////////////////////////////////////////
// Hourly Breakdown Chart //
//////////////////////////////////////////////
paramTitle = "Hourly Breakdown for: " + student.LastName + "-" + student.FirstName;
// Get the range holding the chart data.
dataRange = targetSheet.get_Range("E5", "O10");
// Get the ChartObjects collection for the sheet.
chartObjects = (ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Add a Chart to the collection.
newChartObject = chartObjects.Add(400, 200, 300, 300);
// Create a new chart of the data.
newChartObject.Chart.ChartWizard(dataRange, XlChartType.xl3DColumn, paramChartFormat, XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing);
// Save the workbook.
newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Release the references to the Excel objects.
newChartObject = null;
chartObjects = null;
dataRange = null;
targetSheet = null;
// Show Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Visible = true;
excelApplication.UserControl = true;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void Add()
{
PropertyBag["student"] = new Student();
}