private void compareClass(string getGradeName, int getColumn) //查询该膳食安排所含有的班级 { int gradeNumber = Convert.ToInt32(new GradesDataAccess().GetGradeNumber(getGradeName, "").Tables[0].Rows[0][0]); if (getGradeName.Equals("小班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, getColumn - 3], m_objSheet.Cells[6, getColumn - 3]); m_objRange.set_Item(1, 1, new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), gradeNumber)); } if (getGradeName.Equals("中班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, getColumn + 1], m_objSheet.Cells[6, getColumn + 1]); m_objRange.set_Item(1, 1, new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), gradeNumber)); } if (getGradeName.Equals("大班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, getColumn + 5], m_objSheet.Cells[6, getColumn + 5]); m_objRange.set_Item(1, 1, new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), gradeNumber)); } if (getGradeName.Equals("托班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, getColumn + 9], m_objSheet.Cells[6, getColumn + 9]); m_objRange.set_Item(1, 1, new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), gradeNumber)); } if (getGradeName.Equals("特色班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, getColumn + 13], m_objSheet.Cells[6, getColumn + 13]); m_objRange.set_Item(1, 1, new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), gradeNumber)); } }
private void setACC1(string getFoodName, double getFoodAmount, int getRow, int getColumn, bool isFirRow) { if (isFirRow) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow, getColumn], m_objSheet.Cells[getRow, getColumn]); m_objRange.set_Item(1, 1, getFoodName); } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow, getColumn], m_objSheet.Cells[getRow, getColumn]); m_objRange.set_Item(1, 1, getFoodAmount); } }
private void writeCover() //封面 { try { m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[17, 6], m_objSheet.Cells[17, 6]); m_objRange.set_Item(1, 1, new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[18, 6], m_objSheet.Cells[18, 6]); m_objRange.set_Item(1, 1, BegDate.Date.ToString("yyyy年MM月")); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } }
private void getDairy() //计算乳类总数 { DataSet dsGetDairy = new FoodManagementDataAccess().GetDairy(BegDate.Date.ToString("yyyy-MM-dd"), EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM"))); if (dsGetDairy.Tables[0].Rows[0][0] is DBNull) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[140, 2], m_objSheet.Cells[140, 2]); m_objRange.set_Item(1, 1, 0); } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[140, 2], m_objSheet.Cells[140, 2]); m_objRange.set_Item(1, 1, Convert.ToDouble(dsGetDairy.Tables[0].Rows[0][0])); } }
private void setElement(double getProtein, double getFat, double getCarbohydrate, int getRow, int getColumn) { switch (getColumn) { case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow, getColumn], m_objSheet.Cells[getRow, getColumn]); m_objRange.set_Item(1, 1, getProtein); break; case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow, getColumn], m_objSheet.Cells[getRow, getColumn]); m_objRange.set_Item(1, 1, getFat); break; case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow, getColumn], m_objSheet.Cells[getRow, getColumn]); m_objRange.set_Item(1, 1, getCarbohydrate); break; } }
private void writeStuAmount() //人数 { try { int endTime = Convert.ToInt32(EndDate.Date.ToString("dd")); int begTime; int rangeRow = 6; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2)); foreach (DataRow row in new GradesDataAccess().GetGradeInfoList(0).Tables[0].Rows) { int countTotal = 0; if (Convert.ToInt32(row[0]) > 0) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow, 1], m_objSheet.Cells[rangeRow, 1]); m_objRange.set_Item(1, 1, row[1].ToString()); for (begTime = Convert.ToInt32(BegDate.Date.ToString("dd")); begTime <= endTime; begTime++) { try { using (FoodManagementDataAccess foodManagementDataAccess = new FoodManagementDataAccess()) { int getAmount = foodManagementDataAccess.GetStuAmountForNut(BegDate, EndDate, Convert.ToInt32(BegDate.Date.ToString("MM")), begTime, Convert.ToInt32(row[0])); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow, 1], m_objSheet.Cells[rangeRow, 1]); m_objRange.set_Item(1, begTime + 1, getAmount.ToString()); countTotal += Convert.ToInt32(getAmount); } } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } } m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow, 17], m_objSheet.Cells[rangeRow, 17]); m_objRange.set_Item(1, 17, countTotal.ToString()); rangeRow++; } } } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } }
private void writeACC2() //月食物营养摄取表2 { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(6)); int setRow = 6; int setColumn = 1; int endTime = Convert.ToInt32(EndDate.Date.ToString("dd")); int begTime; DataSet dsGetConsumption = new FoodManagementDataAccess().GetAcc2(BegDate.Date.ToString("yyyy-MM-dd"), EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM"))); DataSet dsGetEachDayConsum = null; foreach (DataRow row in dsGetConsumption.Tables[0].Rows) { setColumn = 1; double countTotal = 0; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow, setColumn], m_objSheet.Cells[setRow, setColumn]); m_objRange.set_Item(1, 1, row[2].ToString()); for (begTime = Convert.ToInt32(BegDate.Date.ToString("dd")); begTime <= endTime; begTime++) { dsGetEachDayConsum = new FoodManagementDataAccess().GetAcc2EachDay(BegDate.Date.ToString("yyyy-MM-dd"), EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM")), begTime, Convert.ToInt32(row[0])); if (dsGetEachDayConsum.Tables[0].Rows.Count > 0) { setColumn++; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow, setColumn], m_objSheet.Cells[setRow, setColumn]); m_objRange.set_Item(1, 1, dsGetEachDayConsum.Tables[0].Rows[0][0].ToString()); countTotal += Convert.ToDouble(dsGetEachDayConsum.Tables[0].Rows[0][0]); } else { setColumn++; } } m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow, 33], m_objSheet.Cells[setRow, 33]); m_objRange.set_Item(1, 1, countTotal); setRow++; } }
private void writeStuConvert() //折合日人数 { try { string getTotal = string.Empty; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3)); foreach(DataRow row in new GradesDataAccess().GetGradeInfoList(0).Tables[0].Rows) { if(Convert.ToInt32(row[0])>0) { if(row[1].ToString().Equals("托班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3,8],m_objSheet.Cells[3,8]); m_objRange.set_Item(1,7,row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4,8],m_objSheet.Cells[4,8]); m_objRange.set_Item(1,7,"2岁组(含以下)"); } if(row[1].ToString().Equals("小班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3,2],m_objSheet.Cells[3,2]); m_objRange.set_Item(1,1,row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4,2],m_objSheet.Cells[4,2]); m_objRange.set_Item(1,1,"3岁组"); } if(row[1].ToString().Equals("中班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3,4],m_objSheet.Cells[3,4]); m_objRange.set_Item(1,3,row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4,4],m_objSheet.Cells[4,4]); m_objRange.set_Item(1,3,"4岁组"); } if(row[1].ToString().Equals("大班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3,6],m_objSheet.Cells[3,6]); m_objRange.set_Item(1,5,row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4,6],m_objSheet.Cells[4,6]); m_objRange.set_Item(1,5,"5岁组(含以上)"); } if(row[1].ToString().Equals("特色班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3,10],m_objSheet.Cells[3,10]); m_objRange.set_Item(1,9,row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4,10],m_objSheet.Cells[4,10]); m_objRange.set_Item(1,9,"3岁以上"); } } } foreach(DataRow row in new FoodManagementDataAccess().GetMealForGrade().Tables[0].Rows) { string getSuitGrade = row[3].ToString(); int count = 0; for ( int k=0; k<getSuitGrade.Length; k++ ) if(getSuitGrade.Substring(k,1).Equals(",")) count ++; string delimStr = ","; char [] delimiter = delimStr.ToCharArray(); string[] spilt = null; int j = 0; for ( int i=1; i<=count+1; i++ ) //确定指定标识切割符数量 { spilt = getSuitGrade.Split(delimiter,i+1); if ( Convert.ToInt32(row[5]) == 1 ) { string s = spilt[j]; compareClass(spilt[j],5); } if ( Convert.ToInt32(row[6]) == 1 ) { string s = spilt[j]; compareClass(spilt[j],6); } if ( Convert.ToInt32(row[7]) == 1 ) { string s = spilt[j]; compareClass(spilt[j],7); } if ( Convert.ToInt32(row[8]) == 1 ) { string s = spilt[j]; compareClass(spilt[j],8); } j += 1; //索取需要的字符串 } } } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } }
//健康评测打印 public void PrintHealth(bool printType1st,bool printType2nd,bool printType3rd,string getOutputGrade, string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput, string getBegDate,string getEndDate,string savePath) { try { m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\health.xls", m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt, m_objOpt,m_objOpt,m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; if ( printType1st ) { if ( !getOutputName.Equals("") || !getOutputNumber.Equals("")) { if ( dsHealthOutput.Tables[0].Rows.Count > 0 ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString(); setTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } } setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1); } } else { if ( !getOutputGrade.Equals("") ) { if ( getOutputClass.Equals("") ) { int rowBeg = 4; for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) ) { rowBeg = 4; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString(); setTitle(printType1st); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } rowBeg++; } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } rowBeg++; } if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 ) { if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString())) setJumpBoard(rowBeg-4,dsHealthOutput,i); } else setJumpBoard(rowBeg-4,dsHealthOutput,i); } } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString(); setTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } } setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1); } } else { int rowBeg = 4; for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) ) { rowBeg = 4; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString(); setTitle(printType1st); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } rowBeg++; } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-2].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,12],m_objSheet.Cells[rowBeg,12]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,13],m_objSheet.Cells[rowBeg,13]); //m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } rowBeg++; } if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 ) { if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString())) setJumpBoard(rowBeg-4,dsHealthOutput,i); } else setJumpBoard(rowBeg-4,dsHealthOutput,i); } } } } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = "未划分班级"; setTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count; j++ ) { if (j <= 12) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-1].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } } else { if (j == 14) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,12],m_objSheet.Cells[i+4,12]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } if (j == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,13],m_objSheet.Cells[i+4,13]); m_objRange.NumberFormatLocal = "@"; m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); } } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } } } DataView dv = dsHealthOutput.Tables[0].DefaultView; dv.RowFilter = "HealthAnaly_isFat = 1"; if (dv.Count > 0) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheets.Add(Type.Missing, m_objSheet, 1, Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = "肥胖儿情况汇总表"; m_objRange = m_objSheet.get_Range("D1",m_objOpt); m_objRange.Value = " 肥胖儿情况汇总表"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 11; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A3",m_objOpt); m_objRange.Value = "班级"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.RowHeight = 21.75; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("B3",m_objOpt); m_objRange.Value = "姓名"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("C3",m_objOpt); m_objRange.Value = "性别"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.ColumnWidth = 5; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("D3",m_objOpt); m_objRange.Value = "出生日期"; m_objRange.ColumnWidth = 12; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("E3",m_objOpt); m_objRange.Value = "体检日期"; m_objRange.ColumnWidth = 12; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("F3",m_objOpt); m_objRange.Value = "年龄"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("G3",m_objOpt); m_objRange.Value = "身高"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("H3",m_objOpt); m_objRange.Value = "评价"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("I3",m_objOpt); m_objRange.Value = "体重"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("J3",m_objOpt); m_objRange.Value = "评价"; m_objRange.Font.Bold = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("K3",m_objOpt); m_objRange.Value = "身高测体重评价"; m_objRange.Font.Bold = true; m_objRange.ColumnWidth = 12; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("L3",m_objOpt); m_objRange.Value = "WHO肥胖 X值"; m_objRange.Font.Bold = true; m_objRange.ColumnWidth = 12; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("M3",m_objOpt); m_objRange.Font.Bold = true; m_objRange.Value = "肥胖度"; m_objRange.ColumnWidth = 13; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("N3",m_objOpt); m_objRange.Font.Bold = true; m_objRange.Value = "超重10-19.9%"; m_objRange.ColumnWidth = 13; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("O3",m_objOpt); m_objRange.Font.Bold = true; m_objRange.Value = "轻度肥胖20-29.9%"; m_objRange.ColumnWidth = 13; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("P3",m_objOpt); m_objRange.Font.Bold = true; m_objRange.Value = "中度肥胖30-49.9%"; m_objRange.ColumnWidth = 13; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range("Q3",m_objOpt); m_objRange.Font.Bold = true; m_objRange.Value = "重度肥胖﹥50%"; m_objRange.ColumnWidth = 13; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; object[,] objData = new Object[dv.Count,17]; for ( int i=0; i<dv.Count; i++ ) { objData[i,0] = dv[i][1].ToString(); objData[i,1] = dv[i][2].ToString(); objData[i,2] = dv[i][3].ToString(); objData[i,3] = Convert.ToDateTime(dv[i][5]).ToString("yyyy-MM-dd"); objData[i,4] = Convert.ToDateTime(dv[i][4]).ToString("yyyy-MM-dd"); objData[i,5] = "'" + dv[i][6].ToString(); objData[i,6] = dv[i][7].ToString(); objData[i,7] = dv[i][8].ToString(); objData[i,8] = dv[i][9].ToString(); objData[i,9] = dv[i][10].ToString(); objData[i,10] = dv[i][14].ToString(); objData[i,11] = dv[i][17].ToString(); // objData[i,10] = dv[i][17].ToString(); // objData[i,11] = dv[i][14].ToString(); objData[i,12] = dv[i][16].ToString(); int fatLevel = Convert.ToInt32(dv[i][18]); switch (fatLevel) { case 1 :objData[i,13] = "√"; break; case 2 :objData[i,14] = "√"; break; case 3 :objData[i,15] = "√"; break; case 4 :objData[i,16] = "√"; break; default: break; } } m_objRange = m_objSheet.get_Range("A4",m_objOpt); m_objRange = m_objRange.get_Resize(dv.Count,17); m_objRange.NumberFormatLocal = "@"; m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("A" + (4 + dv.Count).ToString(), m_objOpt); m_objRange.Value = "总计"; m_objRange = m_objSheet.get_Range("A" + (4 + dv.Count).ToString(), "M" + (4 + dv.Count).ToString()); m_objRange.Merge(m_objOpt); m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("N" + (4 + dv.Count).ToString(), m_objOpt); m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=1").Length; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("O" + (4 + dv.Count).ToString(), m_objOpt); m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=2").Length; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("P" + (4 + dv.Count).ToString(), m_objOpt); m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=3").Length; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("Q" + (4 + dv.Count).ToString(), m_objOpt); m_objRange.Value = dsHealthOutput.Tables[0].Select("HealthAnaly_FatLevel=4").Length; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; } if ( printType2nd ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("均值汇总"); DataSet dsClass = new StuInfoDataAccess().GetClassList("","",""); for ( int i=0; i<dsClass.Tables[0].Rows.Count; i++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+7,1],m_objSheet.Cells[i+7,1]); m_objRange.Value = dsClass.Tables[0].Rows[i][1].ToString(); m_objRange.RowHeight = 25; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; } m_objRange = m_objSheet.get_Range("B7","O"+(dsClass.Tables[0].Rows.Count+7).ToString()); //初始化单元格内容 m_objRange.Value = "0"; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange = m_objSheet.get_Range("A"+(dsClass.Tables[0].Rows.Count+7).ToString(),m_objOpt); m_objRange.Value = "合计"; m_objRange.RowHeight = 25; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; int heightCalcTotal = 0; int weightCalcTotal = 0; for ( int row=0; row<dsClass.Tables[0].Rows.Count; row++ ) { int perRowHeightTotal = 0; int perRowWeightTotal = 0; for ( int column=2; column<=17; column++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,column],m_objSheet.Cells[6,column]); string getValue = m_objRange.Value.ToString(); if ( column < 10 ) { DataSet dsHeightAnalyStat = new HealthManagementDataAccess().GetHeightAnalyStat(getBegDate,getEndDate,getValue,dsClass.Tables[0].Rows[row][1].ToString()); if ( dsHeightAnalyStat.Tables[0].Rows.Count != 0 ) { perRowHeightTotal += Convert.ToInt32(dsHeightAnalyStat.Tables[0].Rows[0][0]); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]); m_objRange.Value = dsHeightAnalyStat.Tables[0].Rows[0][0].ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } if (column == 9) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]); m_objRange.Value = perRowHeightTotal.ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } if ( row+1 == dsClass.Tables[0].Rows.Count ) { if (column < 9) { DataSet dsHeightTotal = new HealthManagementDataAccess().GetHeightAnalyTotal(getBegDate,getEndDate,getValue); if ( dsHeightTotal.Tables[0].Rows.Count != 0 ) { heightCalcTotal += Convert.ToInt32(dsHeightTotal.Tables[0].Rows[0][0]); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column], m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]); m_objRange.Value = dsHeightTotal.Tables[0].Rows[0][0].ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column], m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]); m_objRange.Value = heightCalcTotal.ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } } } else { DataSet dsWeightStat = new HealthManagementDataAccess().GetWeightAnalyStat(getBegDate,getEndDate,getValue,dsClass.Tables[0].Rows[row][1].ToString()); if ( dsWeightStat.Tables[0].Rows.Count != 0 ) { perRowWeightTotal += Convert.ToInt32(dsWeightStat.Tables[0].Rows[0][0]); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]); m_objRange.Value = dsWeightStat.Tables[0].Rows[0][0].ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } if (column == 17) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row+7,column],m_objSheet.Cells[row+7,column]); m_objRange.Value = perRowWeightTotal.ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } if ( row+1 == dsClass.Tables[0].Rows.Count ) { if (column < 17) { DataSet dsWeightTotal = new HealthManagementDataAccess().GetWeightAnalyTotal(getBegDate,getEndDate,getValue); if ( dsWeightTotal.Tables[0].Rows.Count != 0 ) { weightCalcTotal += Convert.ToInt32(dsWeightTotal.Tables[0].Rows[0][0]); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column], m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]); m_objRange.Value = dsWeightTotal.Tables[0].Rows[0][0].ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column], m_objSheet.Cells[dsClass.Tables[0].Rows.Count+7,column]); m_objRange.Value = weightCalcTotal.ToString(); m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Columns.Font.Size = 12; } } } } } m_objRange = m_objSheet.get_Range("K"+(dsClass.Tables[0].Rows.Count+9).ToString(),m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()+" "+ DateTime.Now.ToString("yyyy.MM"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 16; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } if ( printType3rd ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("超均值汇总"); DataSet dsGrade = new StuInfoDataAccess().GetGradeList("",""); DataTable dtFatStat = this.GetFatStat(getBegDate, getEndDate); for ( int i=0; i<dsGrade.Tables[0].Rows.Count; i++ ) { int fatNumber = 0; string fatDesc = string.Empty; switch ( i ) { case 0: { DataView dvFatStat = dtFatStat.DefaultView; dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString()); if (dvFatStat.Count > 0) { fatNumber = Convert.ToInt32(dvFatStat[0][1]); fatDesc = string.Format("(肥胖{0})", fatNumber); } m_objRange = m_objSheet.get_Range("B5",m_objOpt); m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc; m_objRange = m_objSheet.get_Range("B8",m_objOpt); m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("C8",m_objOpt); m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString(); m_objRange = m_objSheet.get_Range("D8",m_objOpt); m_objRange.Value = ((getOverH-fatNumber)/getSum).ToString(); //身高评测 m_objRange = m_objSheet.get_Range("E8",m_objOpt); m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("F8",m_objOpt); m_objRange.Value = (getOverW/getSum).ToString(); } break; case 1: { DataView dvFatStat = dtFatStat.DefaultView; dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString()); if (dvFatStat.Count > 0) { fatNumber = Convert.ToInt32(dvFatStat[0][1]); fatDesc = string.Format("(肥胖{0})", fatNumber); } m_objRange = m_objSheet.get_Range("G5",m_objOpt); m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc; m_objRange = m_objSheet.get_Range("G8",m_objOpt); m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("H8",m_objOpt); m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString(); m_objRange = m_objSheet.get_Range("I8",m_objOpt); m_objRange.Value = ((getOverH-fatNumber)/getSum).ToString(); //身高评测 m_objRange = m_objSheet.get_Range("J8",m_objOpt); m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("K8",m_objOpt); m_objRange.Value = (getOverW/getSum).ToString(); } break; case 2: { DataView dvFatStat = dtFatStat.DefaultView; dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString()); if (dvFatStat.Count > 0) { fatNumber = Convert.ToInt32(dvFatStat[0][1]); fatDesc = string.Format("(肥胖{0})", fatNumber); } m_objRange = m_objSheet.get_Range("B11",m_objOpt); m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc; m_objRange = m_objSheet.get_Range("B17",m_objOpt); m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("C17",m_objOpt); m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString(); m_objRange = m_objSheet.get_Range("D17",m_objOpt); m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测 m_objRange = m_objSheet.get_Range("E17",m_objOpt); m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("F17",m_objOpt); m_objRange.Value = (getOverW/getSum).ToString(); } break; case 3: { DataView dvFatStat = dtFatStat.DefaultView; dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString()); if (dvFatStat.Count > 0) { fatNumber = Convert.ToInt32(dvFatStat[0][1]); fatDesc = string.Format("(肥胖{0})", fatNumber); } m_objRange = m_objSheet.get_Range("G11",m_objOpt); m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc; m_objRange = m_objSheet.get_Range("G17",m_objOpt); m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("H17",m_objOpt); m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString(); m_objRange = m_objSheet.get_Range("I17",m_objOpt); m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测 m_objRange = m_objSheet.get_Range("J17",m_objOpt); m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("K17",m_objOpt); m_objRange.Value = (getOverW/getSum).ToString(); } break; case 4: { DataView dvFatStat = dtFatStat.DefaultView; dvFatStat.RowFilter = string.Format("info_gradeName = '{0}'", dsGrade.Tables[0].Rows[i][1].ToString()); if (dvFatStat.Count > 0) { fatNumber = Convert.ToInt32(dvFatStat[0][1]); fatDesc = string.Format("(肥胖{0})", fatNumber); } m_objRange = m_objSheet.get_Range("B20",m_objOpt); m_objRange.Value = dsGrade.Tables[0].Rows[i][1].ToString() + fatDesc; m_objRange = m_objSheet.get_Range("B26",m_objOpt); m_objRange.Value = getAll(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("C26",m_objOpt); m_objRange.Value = (getHOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate) - fatNumber).ToString(); m_objRange = m_objSheet.get_Range("D26",m_objOpt); m_objRange.Value = ((getOverH - fatNumber)/getSum).ToString(); //身高评测 m_objRange = m_objSheet.get_Range("E26",m_objOpt); m_objRange.Value = getWOPer(Convert.ToInt32(dsGrade.Tables[0].Rows[i][0]),getBegDate,getEndDate).ToString(); m_objRange = m_objSheet.get_Range("F26",m_objOpt); m_objRange.Value = (getOverW/getSum).ToString(); } break; } } DataSet dsWhoTotal = new HealthManagementDataAccess().GetWhoTotal(getBegDate,getEndDate); m_objRange = m_objSheet.get_Range("A8",m_objOpt); m_objRange.Value = dsWhoTotal.Tables[0].Rows[0][0].ToString(); m_objRange = m_objSheet.get_Range("H28",m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()+" "+DateTime.Now.ToString("yyyy.MM"); } m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
private void writeCover() //封面 { try { m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[17,6],m_objSheet.Cells[17,6]); m_objRange.set_Item(1,1,new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[18,6],m_objSheet.Cells[18,6]); m_objRange.set_Item(1,1,BegDate.Date.ToString("yyyy年MM月")); } catch (Exception ex) { Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE); } }
private void getDairy() //计算乳类总数 { DataSet dsGetDairy = new FoodManagementDataAccess().GetDairy(BegDate.Date.ToString("yyyy-MM-dd"),EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM"))); if ( dsGetDairy.Tables[0].Rows[0][0] is DBNull ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[140,2],m_objSheet.Cells[140,2]); m_objRange.set_Item(1,1,0); } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[140,2],m_objSheet.Cells[140,2]); m_objRange.set_Item(1,1,Convert.ToDouble(dsGetDairy.Tables[0].Rows[0][0])); } }
private void setElement(double getProtein,double getFat,double getCarbohydrate,int getRow,int getColumn) { switch(getColumn) { case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow,getColumn],m_objSheet.Cells[getRow,getColumn]); m_objRange.set_Item(1,1,getProtein); break; case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow,getColumn],m_objSheet.Cells[getRow,getColumn]); m_objRange.set_Item(1,1,getFat); break; case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow,getColumn],m_objSheet.Cells[getRow,getColumn]); m_objRange.set_Item(1,1,getCarbohydrate); break; } }
private void setACC1(string getFoodName,double getFoodAmount,int getRow,int getColumn,bool isFirRow) { if ( isFirRow ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow,getColumn],m_objSheet.Cells[getRow,getColumn]); m_objRange.set_Item(1,1,getFoodName); } else { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[getRow,getColumn],m_objSheet.Cells[getRow,getColumn]); m_objRange.set_Item(1,1,getFoodAmount); } }
private void compareClass(string getGradeName,int getColumn) //查询该膳食安排所含有的班级 { int gradeNumber = Convert.ToInt32(new GradesDataAccess().GetGradeNumber(getGradeName,"").Tables[0].Rows[0][0]); if(getGradeName.Equals("小班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,getColumn-3],m_objSheet.Cells[6,getColumn-3]); m_objRange.set_Item(1,1,new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")),gradeNumber)); } if(getGradeName.Equals("中班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,getColumn+1],m_objSheet.Cells[6,getColumn+1]); m_objRange.set_Item(1,1,new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")),gradeNumber)); } if(getGradeName.Equals("大班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,getColumn+5],m_objSheet.Cells[6,getColumn+5]); m_objRange.set_Item(1,1,new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")),gradeNumber)); } if(getGradeName.Equals("托班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,getColumn+9],m_objSheet.Cells[6,getColumn+9]); m_objRange.set_Item(1,1,new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")),gradeNumber)); } if(getGradeName.Equals("特色班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6,getColumn+13],m_objSheet.Cells[6,getColumn+13]); m_objRange.set_Item(1,1,new FoodManagementDataAccess().GetAllStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")),gradeNumber)); } }
private void writeACC2() //月食物营养摄取表2 { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(6)); int setRow = 6; int setColumn = 1; int endTime = Convert.ToInt32(EndDate.Date.ToString("dd")); int begTime; DataSet dsGetConsumption = new FoodManagementDataAccess().GetAcc2(BegDate.Date.ToString("yyyy-MM-dd"),EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM"))); DataSet dsGetEachDayConsum = null; foreach(DataRow row in dsGetConsumption.Tables[0].Rows) { setColumn = 1; double countTotal = 0; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow,setColumn],m_objSheet.Cells[setRow,setColumn]); m_objRange.set_Item(1,1,row[2].ToString()); for ( begTime=Convert.ToInt32(BegDate.Date.ToString("dd")); begTime<=endTime; begTime++ ) { dsGetEachDayConsum = new FoodManagementDataAccess().GetAcc2EachDay(BegDate.Date.ToString("yyyy-MM-dd"),EndDate.Date.ToString("yyyy-MM-dd"), Convert.ToInt32(BegDate.Date.ToString("MM")),begTime,Convert.ToInt32(row[0])); if ( dsGetEachDayConsum.Tables[0].Rows.Count > 0 ) { setColumn ++; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow,setColumn],m_objSheet.Cells[setRow,setColumn]); m_objRange.set_Item(1,1,dsGetEachDayConsum.Tables[0].Rows[0][0].ToString()); countTotal += Convert.ToDouble(dsGetEachDayConsum.Tables[0].Rows[0][0]); } else setColumn++; } m_objRange = m_objSheet.get_Range(m_objSheet.Cells[setRow,33],m_objSheet.Cells[setRow,33]); m_objRange.set_Item(1,1,countTotal); setRow ++; } }
public void RealtimeMorningPrint(DataSet dsRealtimeMorning, string savePath) { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\RealtimeMorningInfo.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; //获取第一个打印页 m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for (int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row++) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 7, 1], m_objSheet.Cells[row + 7, 1]); m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; for (int column = 3; column < dsRealtimeMorning.Tables[0].Columns.Count; column++) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 7, column - 1], m_objSheet.Cells[row + 7, column - 1]); m_objRange.set_Item(1, 1, dsRealtimeMorning.Tables[0].Rows[row][column].ToString()); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } if (row == dsRealtimeMorning.Tables[0].Rows.Count - 1) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 9, 1], m_objSheet.Cells[row + 9, 1]); m_objRange.Value = "园所信息:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 9, 2], m_objSheet.Cells[row + 9, 2]); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 10, 1], m_objSheet.Cells[row + 10, 1]); m_objRange.Value = "统计日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[row + 10, 2], m_objSheet.Cells[row + 10, 2]); m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd"); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; } } m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } finally { m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
public void PrintNchsHealthSummary(bool printType1st,bool printType2nd,string getOutputGrade, string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput, string getBegDate,string getEndDate,string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\NchsHealthOutputSummary", m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt, m_objOpt,m_objOpt,m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; if ( printType1st ) { if ( !getOutputName.Equals("") || !getOutputNumber.Equals("")) { if ( dsHealthOutput.Tables[0].Rows.Count > 0 ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0]["info_className"].ToString(); SetNchsTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } } setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1); } } else { if ( !getOutputGrade.Equals("") ) { if ( getOutputClass.Equals("") ) { int rowBeg = 4; for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) ) { rowBeg = 4; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString(); SetNchsTitle(printType1st); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } rowBeg++; } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } rowBeg++; } if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 ) { if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString())) setJumpBoard(rowBeg-4,dsHealthOutput,i); } else setJumpBoard(rowBeg-4,dsHealthOutput,i); } } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[0][1].ToString(); SetNchsTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } } setJumpBoard(dsHealthOutput.Tables[0].Rows.Count,dsHealthOutput,dsHealthOutput.Tables[0].Rows.Count-1); } } else { int rowBeg = 4; for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { if ( !hasSheet(dsHealthOutput.Tables[0].Rows[i][1].ToString()) ) { rowBeg = 4; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[i][1].ToString(); SetNchsTitle(printType1st); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } rowBeg++; } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowBeg,j-1],m_objSheet.Cells[rowBeg,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } rowBeg++; } if ( i < dsHealthOutput.Tables[0].Rows.Count - 1 ) { if ( !dsHealthOutput.Tables[0].Rows[i][1].ToString().Equals(dsHealthOutput.Tables[0].Rows[i+1][1].ToString())) setJumpBoard(rowBeg-4,dsHealthOutput,i); } else setJumpBoard(rowBeg-4,dsHealthOutput,i); } } } } else { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = "未划分班级"; SetNchsTitle(printType1st); for ( int i=0; i<dsHealthOutput.Tables[0].Rows.Count; i++ ) { for ( int j=2; j<dsHealthOutput.Tables[0].Columns.Count-1; j++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+4,j-1],m_objSheet.Cells[i+4,j-1]); m_objRange.NumberFormatLocal = "@"; if ( j==2 ) m_objRange.set_Item(1,1,"("+dsHealthOutput.Tables[0].Rows[i][j-1].ToString()+")"+ dsHealthOutput.Tables[0].Rows[i][j].ToString()); else { if ( j != 5 ) m_objRange.set_Item(1,1,dsHealthOutput.Tables[0].Rows[i][j].ToString()); else m_objRange.set_Item(1,1,Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd")); } m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } } } if ( printType2nd ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item("均值汇总"); DataSet dsClass = new StuInfoDataAccess().GetClassList("","",""); for ( int i=0; i<dsClass.Tables[0].Rows.Count; i++ ) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[i+6,1],m_objSheet.Cells[i+6,1]); m_objRange.Value = dsClass.Tables[0].Rows[i][1].ToString(); m_objRange.RowHeight = 23.25; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Font.Size = 10; } m_objRange = m_objSheet.get_Range("A"+(dsClass.Tables[0].Rows.Count+6).ToString(),m_objOpt); m_objRange.Value = "合计"; m_objRange.RowHeight = 23.25; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Font.Size = 10; object[,] objData = new object[dsClass.Tables[0].Rows.Count+1,31]; int heightResult_1 = 0; int heightResult_2 = 0; int heightResult_3 = 0; int heightResult_4 = 0; int heightResult_5 = 0; int heightResult_6 = 0; int upMiddleHeightResult = 0; int weightResult_1 = 0; int weightResult_2 = 0; int weightResult_3 = 0; int weightResult_4 = 0; int weightResult_5 = 0; int weightResult_6 = 0; int upMiddleWeightResult = 0; int totalStudentNumbers = 0; int totalCheckNumbers = 0; int totalHeightResult_1 = 0; int totalHeightResult_2 = 0; int totalHeightResult_3 = 0; int totalHeightResult_4 = 0; int totalHeightResult_5 = 0; int totalHeightResult_6 = 0; int totalUpMiddleHeightResult = 0; int totalWeightResult_1 = 0; int totalWeightResult_2 = 0; int totalWeightResult_3 = 0; int totalWeightResult_4 = 0; int totalWeightResult_5 = 0; int totalWeightResult_6 = 0; int totalUpMiddleWeightResult = 0; for ( int row=0; row<dsClass.Tables[0].Rows.Count; row++ ) { string getAddr = dsClass.Tables[0].Rows[row]["info_machineAddr"].ToString(); DataSet dsStudentsOnSummary = new HealthManagementDataAccess().GetNchsStudentsOnSummary(getBegDate,getEndDate,getAddr); int studentNumbers = Convert.ToInt32(dsStudentsOnSummary.Tables[0].Rows[0][0]); totalStudentNumbers += studentNumbers; if ( studentNumbers == 0 ) { for ( int column=0; column<=30; column++ ) { objData[row,column] = 0; } continue; } objData[row,0] = studentNumbers; int checkNumbers = Convert.ToInt32(dsStudentsOnSummary.Tables[1].Rows[0][0]); totalCheckNumbers += checkNumbers; if ( checkNumbers == 0 ) { for ( int column=1; column<=30; column++ ) { objData[row,column] = 0; } } else { objData[row,1] = checkNumbers; objData[row,2] = ((double)checkNumbers/(double)studentNumbers*100).ToString("0.00"); heightResult_1 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"下",getAddr).Rows[0][0]); totalHeightResult_1 += heightResult_1; objData[row,3] = heightResult_1; objData[row,4] = ((double)heightResult_1/(double)checkNumbers*100).ToString("0.00"); heightResult_2 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中下",getAddr).Rows[0][0]); totalHeightResult_2 += heightResult_2; objData[row,5] = heightResult_2; objData[row,6] = ((double)heightResult_2/(double)checkNumbers*100).ToString("0.00"); heightResult_3 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中-",getAddr).Rows[0][0]); totalHeightResult_3 += heightResult_3; objData[row,7] = heightResult_3; objData[row,8] = ((double)heightResult_3/(double)checkNumbers*100).ToString("0.00"); heightResult_4 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中+",getAddr).Rows[0][0]); totalHeightResult_4 += heightResult_4; objData[row,9] = heightResult_4; objData[row,10] = ((double)heightResult_4/(double)checkNumbers*100).ToString("0.00"); heightResult_5 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"中上",getAddr).Rows[0][0]); totalHeightResult_5 += heightResult_5; objData[row,11] = heightResult_5; objData[row,12] = ((double)heightResult_5/(double)checkNumbers*100).ToString("0.00"); heightResult_6 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightOnSummary(getBegDate,getEndDate,"上",getAddr).Rows[0][0]); totalHeightResult_6 += heightResult_6; objData[row,13] = heightResult_6; objData[row,14] = ((double)heightResult_6/(double)checkNumbers*100).ToString("0.00"); upMiddleHeightResult = Convert.ToInt32(new HealthManagementDataAccess().GetNchsHeightUpMiddleOnSummary(getBegDate,getEndDate,getAddr).Rows[0][0]); totalUpMiddleHeightResult += upMiddleHeightResult; objData[row,15] = upMiddleHeightResult; objData[row,16] = ((double)upMiddleHeightResult/(double)checkNumbers*100).ToString("0.00"); weightResult_1 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"下",getAddr).Rows[0][0]); totalWeightResult_1 += weightResult_1; objData[row,17] = weightResult_1; objData[row,18] = ((double)weightResult_1/(double)checkNumbers*100).ToString("0.00"); weightResult_2 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中下",getAddr).Rows[0][0]); totalWeightResult_2 += weightResult_2; objData[row,19] = weightResult_2; objData[row,20] = ((double)weightResult_2/(double)checkNumbers*100).ToString("0.00"); weightResult_3 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中-",getAddr).Rows[0][0]); totalWeightResult_3 += weightResult_3; objData[row,21] = weightResult_3; objData[row,22] = ((double)weightResult_3/(double)checkNumbers*100).ToString("0.00"); weightResult_4 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中+",getAddr).Rows[0][0]); totalWeightResult_4 += weightResult_4; objData[row,23] = weightResult_4; objData[row,24] = ((double)weightResult_4/(double)checkNumbers*100).ToString("0.00"); weightResult_5 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"中上",getAddr).Rows[0][0]); totalWeightResult_5 += weightResult_5; objData[row,25] = weightResult_5; objData[row,26] = ((double)weightResult_5/(double)checkNumbers*100).ToString("0.00"); weightResult_6 = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightOnSummary(getBegDate,getEndDate,"上",getAddr).Rows[0][0]); totalWeightResult_6 += weightResult_6; objData[row,27] = weightResult_6; objData[row,28] = ((double)weightResult_6/(double)checkNumbers*100).ToString("0.00"); upMiddleWeightResult = Convert.ToInt32(new HealthManagementDataAccess().GetNchsWeightUpMiddleOnSummary(getBegDate,getEndDate,getAddr).Rows[0][0]); totalUpMiddleWeightResult += upMiddleHeightResult; objData[row,29] = upMiddleHeightResult; objData[row,30] = ((double)upMiddleWeightResult/(double)checkNumbers*100).ToString("0.00"); } } if ( totalStudentNumbers !=0 && totalCheckNumbers != 0 ) { objData[dsClass.Tables[0].Rows.Count,0] = totalStudentNumbers; objData[dsClass.Tables[0].Rows.Count,1] = totalCheckNumbers; objData[dsClass.Tables[0].Rows.Count,2] = ((double)totalCheckNumbers/(double)totalStudentNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,3] = totalHeightResult_1; objData[dsClass.Tables[0].Rows.Count,4] = ((double)totalHeightResult_1/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,5] = totalHeightResult_2; objData[dsClass.Tables[0].Rows.Count,6] = ((double)totalHeightResult_2/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,7] = totalHeightResult_3; objData[dsClass.Tables[0].Rows.Count,8] = ((double)totalHeightResult_3/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,9] = totalHeightResult_4; objData[dsClass.Tables[0].Rows.Count,10] = ((double)totalHeightResult_4/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,11] = totalHeightResult_5; objData[dsClass.Tables[0].Rows.Count,12] = ((double)totalHeightResult_5/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,13] = totalHeightResult_6; objData[dsClass.Tables[0].Rows.Count,14] = ((double)totalHeightResult_6/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,15] = totalUpMiddleHeightResult; objData[dsClass.Tables[0].Rows.Count,16] = ((double)totalUpMiddleHeightResult/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,17] = totalWeightResult_1; objData[dsClass.Tables[0].Rows.Count,18] = ((double)totalWeightResult_1/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,19] = totalWeightResult_2; objData[dsClass.Tables[0].Rows.Count,20] = ((double)totalWeightResult_2/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,21] = totalWeightResult_3; objData[dsClass.Tables[0].Rows.Count,22] = ((double)totalWeightResult_3/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,23] = totalWeightResult_4; objData[dsClass.Tables[0].Rows.Count,24] = ((double)totalWeightResult_4/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,25] = totalWeightResult_5; objData[dsClass.Tables[0].Rows.Count,26] = ((double)totalWeightResult_5/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,27] = totalWeightResult_6; objData[dsClass.Tables[0].Rows.Count,28] = ((double)totalWeightResult_6/(double)totalCheckNumbers*100).ToString("0.00"); objData[dsClass.Tables[0].Rows.Count,29] = totalUpMiddleWeightResult; objData[dsClass.Tables[0].Rows.Count,30] = ((double)totalUpMiddleWeightResult/(double)totalCheckNumbers*100).ToString("0.00"); } else objData = null; m_objRange = m_objSheet.get_Range("B6",m_objOpt); m_objRange = m_objRange.get_Resize(dsClass.Tables[0].Rows.Count+1,31); if ( objData != null ) m_objRange.Value = objData; else m_objRange.Value = 0; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.Font.Size = 10; m_objFont = m_objRange.Font; } m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
private void writeStuConvert() //折合日人数 { try { string getTotal = string.Empty; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3)); foreach (DataRow row in new GradesDataAccess().GetGradeInfoList(0).Tables[0].Rows) { if (Convert.ToInt32(row[0]) > 0) { if (row[1].ToString().Equals("托班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 8], m_objSheet.Cells[3, 8]); m_objRange.set_Item(1, 7, row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4, 8], m_objSheet.Cells[4, 8]); m_objRange.set_Item(1, 7, "2岁组(含以下)"); } if (row[1].ToString().Equals("小班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 2], m_objSheet.Cells[3, 2]); m_objRange.set_Item(1, 1, row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4, 2], m_objSheet.Cells[4, 2]); m_objRange.set_Item(1, 1, "3岁组"); } if (row[1].ToString().Equals("中班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 4], m_objSheet.Cells[3, 4]); m_objRange.set_Item(1, 3, row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4, 4], m_objSheet.Cells[4, 4]); m_objRange.set_Item(1, 3, "4岁组"); } if (row[1].ToString().Equals("大班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 6], m_objSheet.Cells[3, 6]); m_objRange.set_Item(1, 5, row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4, 6], m_objSheet.Cells[4, 6]); m_objRange.set_Item(1, 5, "5岁组(含以上)"); } if (row[1].ToString().Equals("特色班")) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 10], m_objSheet.Cells[3, 10]); m_objRange.set_Item(1, 9, row[1].ToString()); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[4, 10], m_objSheet.Cells[4, 10]); m_objRange.set_Item(1, 9, "3岁以上"); } } } foreach (DataRow row in new FoodManagementDataAccess().GetMealForGrade().Tables[0].Rows) { string getSuitGrade = row[3].ToString(); int count = 0; for (int k = 0; k < getSuitGrade.Length; k++) { if (getSuitGrade.Substring(k, 1).Equals(",")) { count++; } } string delimStr = ","; char [] delimiter = delimStr.ToCharArray(); string[] spilt = null; int j = 0; for (int i = 1; i <= count + 1; i++) //确定指定标识切割符数量 { spilt = getSuitGrade.Split(delimiter, i + 1); if (Convert.ToInt32(row[5]) == 1) { string s = spilt[j]; compareClass(spilt[j], 5); } if (Convert.ToInt32(row[6]) == 1) { string s = spilt[j]; compareClass(spilt[j], 6); } if (Convert.ToInt32(row[7]) == 1) { string s = spilt[j]; compareClass(spilt[j], 7); } if (Convert.ToInt32(row[8]) == 1) { string s = spilt[j]; compareClass(spilt[j], 8); } j += 1; //索取需要的字符串 } } } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } }
public void RealtimeMorningPrint(DataSet dsRealtimeMorning,string savePath) { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\RealtimeMorningInfo.xls", m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt, m_objOpt,m_objOpt,m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; //获取第一个打印页 m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); for ( int row = 0; row < dsRealtimeMorning.Tables[0].Rows.Count; row ++ ) { m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, 1], (Excel.Range)m_objSheet.Cells[row + 7, 1]); m_objRange.Value = dsRealtimeMorning.Tables[0].Rows[row][1].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; for ( int column = 3;column < dsRealtimeMorning.Tables[0].Columns.Count; column ++) { m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 7, column - 1], (Excel.Range)m_objSheet.Cells[row + 7, column - 1]); m_objRange.set_Item(1,1,dsRealtimeMorning.Tables[0].Rows[row][column].ToString()); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; } if ( row == dsRealtimeMorning.Tables[0].Rows.Count - 1 ) { m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 1], (Excel.Range)m_objSheet.Cells[row + 9, 1]); m_objRange.Value = "园所信息:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 2]); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 1], (Excel.Range)m_objSheet.Cells[row + 10, 1]); m_objRange.Value = "统计日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 2]); m_objRange.Value = DateTime.Now.ToString("yyyy-MM-dd"); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; } } m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
private void writeStuAmount() //人数 { try { int endTime = Convert.ToInt32(EndDate.Date.ToString("dd")); int begTime; int rangeRow = 6; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2)); foreach(DataRow row in new GradesDataAccess().GetGradeInfoList(0).Tables[0].Rows) { int countTotal = 0; if(Convert.ToInt32(row[0])>0) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow,1],m_objSheet.Cells[rangeRow,1]); m_objRange.set_Item(1,1,row[1].ToString()); for( begTime = Convert.ToInt32(BegDate.Date.ToString("dd")); begTime <= endTime; begTime++ ) { try { using ( FoodManagementDataAccess foodManagementDataAccess = new FoodManagementDataAccess() ) { int getAmount = foodManagementDataAccess.GetStuAmountForNut(BegDate,EndDate,Convert.ToInt32(BegDate.Date.ToString("MM")), begTime,Convert.ToInt32(row[0])); m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow,1],m_objSheet.Cells[rangeRow,1]); m_objRange.set_Item(1,begTime+1,getAmount.ToString()); countTotal += Convert.ToInt32(getAmount); } } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } } m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rangeRow,17],m_objSheet.Cells[rangeRow,17]); m_objRange.set_Item(1,17,countTotal.ToString()); rangeRow++; } } } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } }