private void frmAnPai_Load(object sender, EventArgs e)
{
cmbName.Items.Clear();
foreach (var s in selectTeacher())
{
cmbName.Items.Add(s);
}
if (!string.IsNullOrEmpty(id))
{
int a = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.IndexOf("年");
int b = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.IndexOf("月");
int c = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.IndexOf("日");
int d = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.IndexOf("-");
int f = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.IndexOf(" ");
olds = Main.fm.listView1.CheckedItems[0].SubItems[9].Text;
int year = Convert.ToInt32(Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(0, a));
int month = Convert.ToInt32(Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(5).Substring(0, Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(5).IndexOf("月")));
day = Convert.ToInt32(Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(b + 1).Substring(0, Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(b + 1).IndexOf("日")));
int thisweekday = CalendarTools.weekdays(CalendarTools.CaculateWeekDay(year, month, day));
day = thisweekday;
week = Convert.ToInt32(Main.fm.listView1.CheckedItems[0].SubItems[7].Text);
string classnumber = Main.fm.listView1.CheckedItems[0].SubItems[8].Text.Substring(f + 1).Replace("-", "").Replace("节", "").ToString();
intclassnumber = Convert.ToInt32(classnumber);
textBox1.Text = week.ToString();;
textBox2.Text = thisweekday.ToString();
comboBoxEx3.Text = Main.fm.listView1.CheckedItems[0].SubItems[3].Text;
comboBox7.Text = classnumber;
cmbName.Text = Main.fm.listView1.CheckedItems[0].SubItems[6].Text;
comboBoxEx3.Enabled = false;
buttonX3.Text = "确认修改";
textBox1.Enabled = false;
textBox2.Enabled = false;
comboBox7.Enabled = false;
cmbName.Enabled = false;
}
}
private void frmClasses_Load(object sender, EventArgs e)
{
dtSpareTime = new SqlHelper().getDs("select * from SpareTime_Data", "SpareTime").Tables[0];
thisday = CalendarTools.weekdays(CalendarTools.CaculateWeekDay(CurrentTime.Year, CurrentTime.Month, CurrentTime.Day));
thisweek = CalendarTools.WeekOfYear(CurrentTime.Year, CurrentTime.Month, CurrentTime.Day) - CalendarTools.WeekOfYear(Common.Year, Common.Month, Common.Day) + 1;
DataRow[] dr = dtClasses.Select("Class_Week=" + thisweek + " and Class_Day=" + thisday + "", "Class_Number asc");
if (dr.Length != 0)
{
for (int i = 0; i < dr.Length; i++)
{
string[] strclass = new string[] {
(i + 1).ToString(),
CurrentTime.ToLongDateString() + " " + addseparator(Convert.ToInt32(dr[i][5])) + "节" + " (第" + (thisweek.ToString() + "周 第" + thisday.ToString() + "天") + ")",
dr[i][2].ToString(),
dr[i][8].ToString(),
dr[i][10].ToString(),
dr[i][9].ToString(),
numbersupervisor(thisweek, thisday, Convert.ToInt32(dr[i][5])).ToString()
};
ListViewItem lvi = new ListViewItem(strclass);
listView1.Items.Add(lvi);
}
}
}
public ActionResult ReferenceList(string year = "", string month = "", string day = "", int page = 1)
{
int i = 1;
Common com = new Common();
List <ReferenceModel> referencelist = new List <ReferenceModel>();
if (year != "" && month != "" && day != "")
{
int a = int.Parse(month);
int thisday = CalendarTools.weekdays(CalendarTools.CaculateWeekDay(int.Parse(year), int.Parse(month), int.Parse(day)));
int thisweek = CalendarTools.WeekOfYear(int.Parse(year), int.Parse(month), int.Parse(day)) - CalendarTools.WeekOfYear(Common.Year, Common.Month, Common.Day) + 1;
if (Session["Power"].ToString() == "管理员")
{
List <TeachersModel> telist = DBHelper.ExecuteList <TeachersModel>("select * from teachers where college='" + Session["College"].ToString() + "'", CommandType.Text, null);
referencelist = (from t in telist
join c in classlist on t.TeacherName equals c.TeacherName
where t.College == Session["College"].ToString() && c.Week == thisweek && c.Day == thisday
select new ReferenceModel
{
Cid = c.Cid,
Id = i++,
time = CalendarTools.getdata(Common.Year, thisweek, thisday - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + "" + com.AddSeparator(c.ClassNumber) + "节",
TeacherName = c.TeacherName,
Address = c.Address,
Major = c.Major,
ClassType = c.ClassType,
SupervisorsSum = numbersupervisor(c.Week, c.Day, c.ClassNumber)
}).ToList();
}
else
{
referencelist = (from c in classlist
where c.Week == thisweek && c.Day == thisday
select new ReferenceModel
{
Id = i++,
Cid = c.Cid,
time = CalendarTools.getdata(Common.Year, thisweek, thisday - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + "" + com.AddSeparator(c.ClassNumber) + "节",
TeacherName = c.TeacherName,
Address = c.Address,
Major = c.Major,
ClassType = c.ClassType,
SupervisorsSum = numbersupervisor(c.Week, c.Day, c.ClassNumber)
}).ToList();
}
}
else
{
if (Session["Power"].ToString() == "管理员")
{
List <TeachersModel> telist = DBHelper.ExecuteList <TeachersModel>("select * from teachers where college='" + Session["College"].ToString() + "'", CommandType.Text, null);
referencelist = (from t in telist
join c in classlist on t.TeacherName equals c.TeacherName
where t.College == Session["College"].ToString()
select new ReferenceModel
{
Id = i++,
Cid = c.Cid,
time = CalendarTools.getdata(Common.Year, c.Week, c.Day - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + "" + com.AddSeparator(c.ClassNumber) + "节",
TeacherName = c.TeacherName,
Address = c.Address,
Major = c.Major,
ClassType = c.ClassType,
SupervisorsSum = numbersupervisor(c.Week, c.Day, c.ClassNumber)
}).ToList();
}
else
{
referencelist = (from c in classlist
select new ReferenceModel
{
Id = i++,
Cid = c.Cid,
time = CalendarTools.getdata(Common.Year, c.Week, c.Day - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + "" + com.AddSeparator(c.ClassNumber) + "节",
TeacherName = c.TeacherName,
Address = c.Address,
Major = c.Major,
ClassType = c.ClassType,
SupervisorsSum = numbersupervisor(c.Week, c.Day, c.ClassNumber)
}).ToList();
}
}
IPagedList <ReferenceModel> iplist = referencelist.ToPagedList(page, 10);
return(PartialView(iplist));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
DistinctSupervisor(dtSource.Rows[j][6].ToString(), ListSupervisor); //去职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
newCell.SetCellValue(dtSource.Rows[j][8].ToString() + "\t");
break;
case 2:
newCell.SetCellValue(dtSource.Rows[j][9].ToString() + "\t");
break;
case 3:
newCell.SetCellValue(dtSource.Rows[j][10].ToString() + "\t");
break;
case 4:
newCell.SetCellValue(dtSource.Rows[j][11].ToString() + "\t");
break;
case 5:
newCell.SetCellValue(dtSource.Rows[j][7].ToString() + "\t");
break;
case 6:
newCell.SetCellValue(dtSource.Rows[j][2].ToString() + "\t");
break;
case 7:
newCell.SetCellValue(dtSource.Rows[j][3].ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(
CalendarTools.getdata(Common.Year, Convert.ToInt32(dtSource.Rows[j][3]), Convert.ToInt32(dtSource.Rows[j][4]) - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString()
+ " " + addseparator(Convert.ToInt32(dtSource.Rows[j][5])) + "节" + "\t");
break;
case 9:
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
newCell.SetCellValue(dtSource.Rows[j][12].ToString() + "\t");
break;
}
}
else
{
newCell.SetCellValue(" ");
}
}
#endregion
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 导出安排表
/// </summary>
/// <param name="pids"></param>
/// <returns></returns>
public ActionResult ExportArrage(string[] pids)
{
Common com = new Common();
List <ExportExcelModel> expel = new List <ExportExcelModel>();
foreach (string pid in pids)
{
var exe = from c in classlist
join a in arragelist on c.Cid equals a.Cid
where a.Pid == pid
select new ExportExcelModel
{
classname = c.ClassName,
classcontent = c.ClassContent,
classroom = c.Address,
classtype = c.ClassType,
major = c.Major,
supervisors = a.SuperVisors,
teachername = c.TeacherName,
time = CalendarTools.getdata(Common.Year, c.Week, c.Day - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + "" + com.AddSeparator(c.ClassNumber) + "节",
week = c.Week.ToString()
};
expel.Add(exe.First());
}
string term;
if (Common.Month < 8)
{
term = "二";
}
else
{
term = "一";
}
string filename = string.Format("{0}{1}-{2}学年第{3}学期听课安排检查表", Session["College"], (Common.Year - 1).ToString(), Common.Year.ToString(), term);
string virtualPath = string.Format("~/App_Data/{0}/{1}/{2}", Session["AdminUser"], "听课安排检查表", filename + ".xls");
string fullFileName = this.Server.MapPath(virtualPath);
//创建文件夹，保存文件
string path = Path.GetDirectoryName(fullFileName);
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
ExcelHelper exh = new ExcelHelper();
exh.Export(expel, filename, fullFileName);
return(File(fullFileName, "application/zip-x-compressed", filename + ".xls"));
}
public void placement_listview_write(DataTable dtPlacement, ListView listview, int currentpage, int pagesize)
{
for (int i = 0; i < dtPlacement.Rows.Count; i++)
{
int nowday = Convert.ToInt32(dtPlacement.Rows[i][4]);
int nowWeeks = Convert.ToInt32(dtPlacement.Rows[i][3]);
DistinctSupervisor(dtPlacement.Rows[i][6].ToString(), ListSupervisor);
string time = CalendarTools.getdata(Common.Common.Year, nowWeeks, nowday - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Common.Year, Common.Common.Month, Common.Common.Day)), Common.Common.Month, Common.Common.Day).ToLongDateString();
string[] placement_arrages = new string[]
{
Ordernumber(i + 1, currentpage, pagesize),//序号
dtPlacement.Rows[i][9].ToString(),
dtPlacement.Rows[i][8].ToString(),
dtPlacement.Rows[i][10].ToString(),
dtPlacement.Rows[i][11].ToString(),
dtPlacement.Rows[i][7].ToString(),
FormatTeacher(dtPlacement.Rows[i][2].ToString()),
dtPlacement.Rows[i][3].ToString(), //周次
time + " " + addseparator(Convert.ToInt32(dtPlacement.Rows[i][5])) + "节", //得知具体日期
FormatSupervisor(ListSupervisor),
dtPlacement.Rows[i][12].ToString()
};
ListViewItem lvi = new ListViewItem(placement_arrages);
listview.Items.Add(lvi);
}
}