Esempio n. 1
0
		private void ExportSingleStat(
			DateTime startDate, 
			DateTime endDate,
			Excel._Workbook m_objBook, 
			Excel.Sheets m_objSheets, 
			Excel._Worksheet m_objSheet, 
			Excel.Range m_objRange,
			Excel.Font m_objFont,
			System.Reflection.Missing m_objOpt)
		{
			object[,] objData;
			using(DataTable dt = new DutyInfoDA().GetTeacherStatSingle(startDate, endDate))
			{
				if (dt != null && dt.Rows.Count > 0 )
				{
					objData = new Object[dt.Rows.Count + 1,6];
					int days = SetAttendDays(startDate, endDate);
					for( int i=0; i<dt.Rows.Count; i++ )
					{
						double attendCount = Convert.ToInt32(dt.Rows[i][2]);
						double shouldAttendCount = days;
						double absenceCount = shouldAttendCount - attendCount;
						objData[i, 0] = dt.Rows[i][0].ToString();
						objData[i, 1] = dt.Rows[i][1].ToString();
						objData[i, 2] = shouldAttendCount;
						objData[i, 3] = attendCount;
						objData[i, 4] = absenceCount < 0 ? 0 : absenceCount ;
						objData[i, 5] = shouldAttendCount == 0 ? attendCount.ToString("0.00%") : (attendCount / shouldAttendCount).ToString("0.00%");
					}

					m_objSheets = (Excel.Sheets)m_objBook.Sheets;
					m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3));
					m_objRange = m_objSheet.get_Range("A3",m_objOpt);
					m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);

					m_objRange.Value = objData;

					m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
					m_objRange.WrapText = true;
					m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
					m_objFont = m_objRange.Font;
					m_objFont.Size = 9;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
					m_objRange.Value = "园所:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
					m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
					m_objRange.Value = "统计开始日期:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
					m_objRange.Value = startDate.ToString("yyyy.MM.dd");
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
					m_objRange.Value = "统计结束日期:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
					m_objRange.Value = endDate.ToString("yyyy.MM.dd");
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
				}
			}
		}
Esempio n. 2
0
		private void ExportSingle(
			DateTime startDate, 
			DateTime endDate,
			Excel._Workbook m_objBook, 
			Excel.Sheets m_objSheets, 
			Excel._Worksheet m_objSheet, 
			Excel.Range m_objRange,
			Excel.Font m_objFont,
			System.Reflection.Missing m_objOpt)
		{
			using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
			{
				if (dt != null && dt.Rows.Count > 0)
				{
					DataView dv = dt.DefaultView;
					using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0])
					{
						object[, ] objData = null;
						foreach(DataRow dr in dtBaseInfo.Rows)
						{
							dv.RowFilter = "T_Number = " + dr[1].ToString();
							objData = new object[dv.Count, 6];
							if (dv.Count > 0)
							{
								for (int i = 0; i < dv.Count; i++)
								{
									objData[i,0] = dv[i][3].ToString();
									objData[i,1] = dv[i][2].ToString();
									objData[i,2] = dv[i][0].ToString();
									objData[i,3] = dv[i][1].ToString();
									objData[i,4] = dv[i][4].ToString();
									objData[i,5] = dv[i][5].ToString();
								}
							
								m_objSheets = (Excel.Sheets)m_objBook.Sheets;
								m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
								m_objSheet.Copy(Type.Missing, m_objSheet);
								m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5));
								m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]);
								m_objRange = m_objSheet.get_Range("A3",m_objOpt);
								m_objRange = m_objRange.get_Resize(dv.Count, 6);
								m_objRange.Value = objData;

								m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
								m_objRange.WrapText = true;
								m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
								m_objFont = m_objRange.Font;
								m_objFont.Size = 9;

								m_objRange = m_objSheet.get_Range("A"+(dv.Count+4).ToString(),m_objOpt);
								m_objRange.Value = "园所:";
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

								m_objRange = m_objSheet.get_Range("B"+(dv.Count+4).ToString(),m_objOpt);
								m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

								m_objRange = m_objSheet.get_Range("A"+(dv.Count+5).ToString(),m_objOpt);
								m_objRange.Value = "统计开始日期:";
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

								m_objRange = m_objSheet.get_Range("B"+(dv.Count+5).ToString(),m_objOpt);
								m_objRange.Value = startDate.ToString("yyyy.MM.dd");
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

								m_objRange = m_objSheet.get_Range("A"+(dv.Count+6).ToString(),m_objOpt);
								m_objRange.Value = "统计结束日期:";
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

								m_objRange = m_objSheet.get_Range("B"+(dv.Count+6).ToString(),m_objOpt);
								m_objRange.Value = endDate.ToString("yyyy.MM.dd");
								m_objRange.Font.Bold = true;
								m_objRange.Font.Size = 9;
								m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
							}
						}
					}
					m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
					m_objSheet.Delete();
				}
			}
		}
Esempio n. 3
0
		private void ExportAllNormal(
			DateTime startDate, 
			DateTime endDate,
			Excel._Workbook m_objBook, 
			Excel.Sheets m_objSheets, 
			Excel._Worksheet m_objSheet, 
			Excel.Range m_objRange,
			Excel.Font m_objFont,
			System.Reflection.Missing m_objOpt)
		{
			object[,] objData;
			using(DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty,string.Empty,string.Empty,string.Empty,startDate,endDate,100).Tables[0])
			{
				if ( dt != null && dt.Rows.Count > 0 )
				{
					objData = new Object[dt.Rows.Count,6];

					for( int i=0; i<dt.Rows.Count; i++ )
					{
						objData[i,0] = dt.Rows[i][3].ToString();
						objData[i,1] = dt.Rows[i][2].ToString();
						objData[i,2] = dt.Rows[i][0].ToString();
						objData[i,3] = dt.Rows[i][1].ToString();
						objData[i,4] = dt.Rows[i][4].ToString();
						objData[i,5] = dt.Rows[i][5].ToString();
					}
					
					m_objSheets = (Excel.Sheets)m_objBook.Sheets;
					m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
					m_objRange = m_objSheet.get_Range("A3",m_objOpt);
					m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);

					m_objRange.Value = objData;

					m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
					m_objRange.WrapText = true;
					m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
					m_objFont = m_objRange.Font;
					m_objFont.Size = 9;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
					m_objRange.Value = "园所:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
					m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
					m_objRange.Value = "统计开始日期:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
					m_objRange.Value = startDate.ToString("yyyy.MM.dd");
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
					m_objRange.Value = "统计结束日期:";
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

					m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
					m_objRange.Value = endDate.ToString("yyyy.MM.dd");
					m_objRange.Font.Bold = true;
					m_objRange.Font.Size = 9;
					m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
				}
			}
		}