public void GenerateReport(IEnumerable <StudentDto> students) { Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx"); Excel._Worksheet sheet_template = book_template.Sheets["отчет"]; foreach (var ob in students) { //1. Создаем объкт LearnerReport из БД LearnerReport report = new LearnerReport { SNS = $"{ob.surname} {ob.name} {ob.SecondName}", SchoolName = ob.SchoolName, ClassName = ob.ClassName, TestResult5 = ob.TestResult5 }; //2. Экспорт объкта LearnerReport в шаблон xlsx sheet_template.Range["C4"].Value2 = report.SNS; sheet_template.Range["C5"].Value2 = report.SchoolName; sheet_template.Range["C6"].Value2 = report.ClassName; sheet_template.Range["C9"].Value2 = report.TestResult5; //3. Сохраняем полученный файл в .pdf на рабочем столе string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf"; sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name); } book_template.Close(0); book_template = null; app.Quit(); app = null; }
public void CreateStudentBaseSimpleTable() { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 excelPath + @"\report\ImportBaseTableForStudent(simple).xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); m_objExcel.Visible = true; } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); } }
/// <summary> /// 读取Excel文件 /// </summary> /// <param name="pPath"></param> /// <returns></returns> private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return(sheetName); }
public void XtraGridTo(GridView grv, int DongBatDau, int CotBatDau) { try { oXL = new Excel.Application(); oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; //oSheet.UsedRange.Cells.Borders.Value = 1; int SoCotGroup = 0; foreach (GridColumn grc in grv.Columns) { if (grc.GroupIndex >= 0) { SoCotGroup++; } } // Mở file Excel oXL.Visible = true; } catch (Exception ex) { oXL.Quit(); throw ex; } }
public void OnWindowActivate(object workbook) { if (IsConnected) { Excel._Workbook book = this.Parent as Excel._Workbook; if (book != null) { try { string parentBookFullName = (string)book.GetType().InvokeMember( "FullName", System.Reflection.BindingFlags.GetProperty, null, book, null, CultureInfo.CurrentUICulture); string workbookFullName = (string)workbook.GetType().InvokeMember( "FullName", System.Reflection.BindingFlags.GetProperty, null, workbook, null, CultureInfo.CurrentUICulture); if (string.Compare(parentBookFullName, workbookFullName, true) == 0) { Excel._Worksheet sheet = book.ActiveSheet as Excel._Worksheet; if (sheet != null) { try { if (String.Compare(sheet.Name, Worksheet, true) == 0) { adxClearTimesheet.Enabled = true; adxRibbonButtonClearTimesheet.Enabled = true; } } finally { Marshal.ReleaseComObject(sheet); } } } } catch (Exception err) { MessageBox.Show(err.Message, err.Source, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// 读取Excel文件 /// </summary> /// <param name="pPath"></param> /// <returns></returns> private string GetExcelSheetName(string pPath) { //打开一个Excel应用 _excelApp = new Excel.Application(); if (_excelApp == null) { throw new Exception("打开Excel应用时发生错误!"); } _books = _excelApp.Workbooks; //打开一个现有的工作薄 _book = _books.Add(pPath); _sheets = _book.Sheets; //选择第一个Sheet页 _sheet = (Excel._Worksheet)_sheets.get_Item(1); string sheetName = _sheet.Name; ReleaseCOM(_sheet); ReleaseCOM(_sheets); ReleaseCOM(_book); ReleaseCOM(_books); _excelApp.Quit(); ReleaseCOM(_excelApp); return sheetName; }
public void ExportTeacherAllReports(string path, DateTime startDate, DateTime endDate) { string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory; Excel.Application m_objExcel = null; Excel.Workbooks m_objBooks = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.Range m_objRange = null; Excel.Font m_objFont = null; System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value; try { m_objExcel = new Excel.Application(); m_objExcel.DisplayAlerts = false; m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherDutyAll.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); ExportAllNormal(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt); ExportStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt); ExportSingleStat(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt); ExportSingle(startDate, endDate, m_objBook, m_objSheets, m_objSheet, m_objRange, m_objFont, m_objOpt); m_objBook.SaveAs(path, 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_objFont); 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 ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } finally { m_objFont = null; m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
private static void FillExcel(Excel._Worksheet xlSheet, System.Data.DataTable dt) { if (dt.Rows.Count == 0) { return; } long rows = dt.Rows.Count; long cols = dt.Columns.Count; //在第一行写字段名 for (int i = 1; i <= cols; i++) { xlSheet.Cells[1, i] = dt.Columns[i - 1].Caption; } //逐行写数据 for (int j = 1; j <= rows; j++) { for (int i = 1; i <= cols; i++) { xlSheet.Cells[j + 1, i] = dt.Rows[j - 1][i - 1].ToString().Trim(); } } xlSheet.Name = dt.TableName; }
public void CreateStudentBaseSimpleTable() { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 excelPath+@"\report\ImportBaseTableForStudent(simple).xls",Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing)); m_objExcel.Visible = true; } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); } }
/// <summary> /// Create Excel application parameters instances /// </summary> private void CreateExcelRef() { _excelApp = new Excel.Application(); _books = (Excel.Workbooks)_excelApp.Workbooks; _book = (Excel._Workbook)(_books.Add(_optionalValue)); _sheets = (Excel.Sheets)_book.Worksheets; _sheet = (Excel._Worksheet)(_sheets.get_Item(1)); }
protected virtual void ActivateExcel() { _excelApplication = new Excel.Application(); _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); }
public void AllStuInfoPrint(DataSet dsExportData, string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\AllStuInfo.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; dv = dsExportData.Tables[0].DefaultView; for (int row = 1; row < dsExportData.Tables[1].Rows.Count; row++) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Copy(m_objOpt, m_objSheet); } for (int row = 1; row <= dsExportData.Tables[1].Rows.Count; row++) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row); m_objSheet.Name = Convert.ToString(dsExportData.Tables[1].Rows[row - 1][0]); BindingData(m_objSheet.Name); } 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 ExcelFileWriter(string file) { _excelApplication = new Excel.Application(); _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBooks.Open(file); _workBook = (Excel._Workbook)(_workBooks.get_Item(1)); //(Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); }
// Export DataTable into an excel file with field names in the header line // - Save excel file without ever making it visible if filepath is given // - Don't save excel file, just make it visible if no filepath is given public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null) { try { if (Tbl == null || Tbl.Columns.Count == 0) { throw new Exception("ExportToExcel: Null or empty input table!\n"); } // load excel, and create a new workbook Excel.Application excelApp = new Excel.Application(); excelApp.Workbooks.Add(); // single worksheet Excel._Worksheet workSheet = excelApp.ActiveSheet; // column headings for (int i = 0; i < Tbl.Columns.Count; i++) { workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName; } // rows for (int i = 0; i < Tbl.Rows.Count; i++) { // to do: format datetime values before printing for (int j = 0; j < Tbl.Columns.Count; j++) { workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j]; } } // check fielpath if (ExcelFilePath != null && ExcelFilePath != "") { try { workSheet.SaveAs(ExcelFilePath); excelApp.Quit(); MessageBox.Show("Excel file saved!"); } catch (Exception ex) { throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n" + ex.Message); } } else // no filepath is given { excelApp.Visible = true; } } catch (Exception ex) { throw new Exception("ExportToExcel: \n" + ex.Message); } }
public static void getExcelFile() { //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; //IMPORTANT SECTION var dictionary = new Dictionary <string, List <string> >(); //iterate over the rows and columns as it appears in the file //excel is not zero based!! for (int i = 1; i <= rowCount; i++) { //it would be nice if we add some null checking to this variables. Check the article again var col1 = xlRange.Cells[i, 1].Value2.ToString(); var col2 = xlRange.Cells[i, 2].Value2.ToString(); if (dictionary.ContainsKey(col1)) { var existingList = dictionary[col1]; existingList.Add(col2); } else { var newList = new List <string>(); newList.Add(col2); dictionary.Add(col1, newList); } } //Do whatever you'd like with the dictionary //END OF IMPORTANT SECTION //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }
protected virtual int ActivateExcelReturnRowCount(string file) { _excelApplication = new Excel.Application(); _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBooks.Open(file); _workBook = (Excel._Workbook)(_workBooks.get_Item(1)); //(Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); return(_excelSheet.Rows.Count); }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// new code private void Window_Loaded(object sender, RoutedEventArgs e) { List <Food> listFoods = new List <Food>(); using Excel = Microsoft.Office.Interop.Excel; Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"sandbox_test.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; }
public void WriteStuCardInfoXLS(DataSet dsStuInfo, string getPath) { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; if (dsStuInfo.Tables[0].Rows.Count > 0) { objData = new object[dsStuInfo.Tables[0].Rows.Count, 2]; for (int i = 0; i < dsStuInfo.Tables[0].Rows.Count; i++) { objData[i, 0] = dsStuInfo.Tables[0].Rows[i][0].ToString(); objData[i, 1] = dsStuInfo.Tables[0].Rows[i][1].ToString(); } } m_objRange = m_objSheet.get_Range("A2", m_objOpt); m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count, 2); m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; m_objExcel.Visible = true; } catch (Exception e) { Util.WriteLog(e.Message, Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); } }
public void dialog(DataGridView grid) { Excel._Application app = new Excel.Application(); Excel._Workbook wk = app.Workbooks.Add(Type.Missing); Excel._Worksheet sheet = null; sheet = wk.Sheets[1]; sheet = wk.ActiveSheet; sheet.Name = "Entrate"; for (int i = 1; i <= grid.ColumnCount; i++) { sheet.Cells[1, i] = grid.Columns[i - 1].HeaderText; } for (int i = 0; i < grid.Rows.Count; i++) { for (int j = 0; j < grid.Columns.Count; j++) { if (j != 7 && j != 8 && j != 9) { sheet.Cells[i + 2, j + 1] = grid.Rows[i].Cells[j].Value.ToString(); } else { sheet.Cells[i + 2, j + 1] = grid.Rows[i].Cells[j].Value; } } } sheet.Columns.AutoFit(); fw.Close(); SaveFileDialog sf = new SaveFileDialog(); sf.Title = "Salva"; sf.Filter = "XLSX|*.xlsx|XLS|*.xls|Tutti i file|*.*"; sf.InitialDirectory = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); if (sf.ShowDialog() == DialogResult.OK) { try { wk.SaveAs(sf.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wk.Close(); app.Quit(); } catch (Exception ex) { throw new Exception(ex.Message); } } }
public void AddObject(Excel._Worksheet oSheet, ref Excel.Range oRng, object Cell1, object Cell2, object Value, int CellBorder, bool AutoFit) { oRng = oSheet.get_Range(Cell1, Cell2); oRng.Value2 = Value; if (AutoFit) { oRng.EntireColumn.AutoFit(); } oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng.Cells.Borders.Value = CellBorder; }
/// <summary> /// 打开一个Excel工作薄 /// </summary> /// <param name="fileName"></param> public void OpenWorkbook(string fileName) { _mWorkbook = _mExcelApplication.Workbooks.Open(fileName, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing); if (_mWorkbook.Worksheets.Count > 0) { object index = 1; MWorksheet = _mWorkbook.Worksheets[index] as Excel._Worksheet; } }
private void ShowComment() { if (_user == "") { MessageBox.Show("Please login..."); return; } try { Excel._Worksheet sheet = (Excel._Worksheet)ExcelApp.ActiveSheet; _xlsCell = (Excel.Range)ExcelApp.ActiveCell; string _address = _xlsCell.get_AddressLocal(1, 1, Excel.XlReferenceStyle.xlA1, 0, 0).ToString().Replace("$", ""); if (_xlsCell.Comment != null) { string formular = _xlsCell.Comment.Text(Type.Missing, Type.Missing, Type.Missing); if (frm == null) { frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnect, _user); //frm.User = _user; frm.Config = _config; frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed); //frm.Pos = _address; //frm.TopMost = true; frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode)); } //else if (frm.DialogResult == System.Windows.Forms.DialogResult.Yes) //{ // frm.BringToFront(); // frm.GetQueryBuilderFromFomular(formular); //} else { frm.Close(); frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user); frm.User = _user; frm.Config = _config; frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed); //frm.Pos = _address; //frm.TopMost = true; frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));//new WindowWrapper((IntPtr)ExcelApp.Hwnd) } } else { MessageBox.Show("Cell selected is incorrect!"); } } catch (Exception ex) { BUS.CommonControl.AddLog("ErroLog", __documentDirectory + "\\Log", "Addin : " + ex.Message + "\n\t" + ex.Source + "\n\t" + ex.StackTrace); } }
public void NutritionPrint(string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\nutrition.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; int number = m_objSheets.Count; writeCover(); writeStuAmount(); writeStuConvert(); writeACC1(); writeACC2(); writeElement(); 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 ex) { Util.WriteLog(ex.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 NutritionPrint(string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\nutrition.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; int number = m_objSheets.Count; writeCover(); writeStuAmount(); writeStuConvert(); writeACC1(); writeACC2(); writeElement(); 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 ex) { Util.WriteLog(ex.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 ReadUpdateGradeInfo(ref DataTable dtUpdateGradeInfo, string getPath) { OleDbDataAdapter myAdp = null; OleDbConnection oleConn = null; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器 + ";Extended Properties=Excel 8.0"); oleConn.Open(); myAdp = new OleDbDataAdapter("select * from [年班升级$]", oleConn); //获取第一张工作表的内容 myAdp.Fill(dtUpdateGradeInfo); oleConn.Close(); 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_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 static void FillExcelCaption(Excel._Worksheet xlSheet, System.Data.DataTable dt) { if (dt.Rows.Count == 0) { return; } long rows = dt.Rows.Count; long cols = dt.Columns.Count; //在第一行写字段名 for (int i = 1; i <= cols; i++) { xlSheet.Cells[1, i] = dt.Columns[i - 1].Caption; } xlSheet.Name = dt.TableName; }
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 ShowDesign() { if (_user == "") { MessageBox.Show("Please login..."); return; } Excel._Worksheet sheet = (Excel._Worksheet)ExcelApp.ActiveSheet; _xlsCell = (Excel.Range)ExcelApp.ActiveCell; string _address = _xlsCell.get_AddressLocal(1, 1, Excel.XlReferenceStyle.xlA1, 0, 0).ToString(); _address = _address.Replace("$", ""); string formular = _xlsCell.Formula.ToString(); if (frm == null) { frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user); frm.User = _user; frm.Config = _config; frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed); //frm.Pos = _address; //frm.TopMost = true; //IWin32Window wincurrent = new WindowWrapper((IntPtr)ExcelApp.); frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode)); } //else if (frm.DialogResult == System.Windows.Forms.DialogResult.Yes) //{ // frm.BringToFront(); // frm.GetQueryBuilderFromFomular(formular); //} else { frm.Close(); frm = new QDAddIn(_config, _address, ExcelApp, formular, _strConnect, _strConnectDes, _user); frm.User = _user; frm.Config = _config; frm.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frm_FormClosed); //frm.Pos = _address; //frm.TopMost = true; //IWin32Window wincurrent = new WindowWrapper((IntPtr)ExcelApp.Hwnd); frm.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode)); } }
//导出Excel的方法 public void getListView(ListView lsv, string title) { Excel.Application app = null; try { app = new Excel.Application(); app.Visible = true; object obj = System.Reflection.Missing.Value; Excel.Workbooks wb = app.Workbooks; Excel._Workbook iwk = wb.Add(obj); Excel._Worksheet sheet = (Excel._Worksheet)(iwk.ActiveSheet); int colCount = lsv.Columns.Count; int rowCount = lsv.Items.Count; app.Caption = title; string[,] dataArray = new string[rowCount + 1, colCount]; //添加列头标题 for (int i = 0; i < colCount; i++) { dataArray[0, i] = lsv.Columns[i].Text.ToString(); } //添加每一行的数据 for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i + 1, j] = lsv.Items[i].SubItems[j].Text.ToString(); } } sheet.get_Range("A1", sheet.Cells[rowCount + 1, colCount]).Value2 = dataArray; } catch (Exception e) { MessageBox.Show(e.Message); } finally { GC.Collect(); app.Quit(); } }
private void ShowAnalysis() { Excel._Worksheet sheet = ExcelApp.ActiveSheet as Excel.Worksheet; _xlsCell = (Excel.Range)ExcelApp.ActiveCell; string _address = _xlsCell.get_AddressLocal(1, 1, Excel.XlReferenceStyle.xlA1, 0, 0).ToString().Replace("$", ""); if (_xlsCell.Comment != null) { string formular = _xlsCell.Comment.Text(Type.Missing, Type.Missing, Type.Missing); if (frmdrill == null) { frmdrill = new QDAddinDrillDown(_config, _address, ExcelApp, formular, _strConnectDes, _user); frmdrill.User = _user; frmdrill.Config = _config; frmdrill.FormClosed += new FormClosedEventHandler(frmdrill_FormClosed); //frm.Pos = _address; //frm.TopMost = true; frmdrill.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode)); } //else if (frmdrill.DialogResult == System.Windows.Forms.DialogResult.Yes) //{ // frmdrill.BringToFront(); // frmdrill.GetQueryBuilderFromFomular(formular); //} else { frmdrill.Close(); frmdrill = new QDAddinDrillDown(_config, _address, ExcelApp, formular, _strConnectDes, _user); frmdrill.User = _user; frmdrill.Config = _config; frmdrill.FormClosed += new System.Windows.Forms.FormClosedEventHandler(frmdrill_FormClosed); //frm.Pos = _address; //frm.TopMost = true; frmdrill.Show(new WindowWrapper((IntPtr)ExcelApp.DDEAppReturnCode));//new WindowWrapper((IntPtr)ExcelApp.Hwnd) } } else { MessageBox.Show("Cell selected is incorrect!"); } }
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 static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName) { int i, j; object missing = Type.Missing; Excel.ApplicationClass excellApp; excellApp = new Excel.ApplicationClass(); excellApp.Application.Workbooks.Add(true); try { // Add columns name to excel file for (i = 0; i < dtGridView.Columns.Count; i++) { excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name; } for (i = 0; i < dtGridView.Rows.Count; i++) { for (j = 0; j < dtGridView.Columns.Count; j++) { excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value; } } //excellApp.Save(("Loinhuan.xls"); Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet; worksheet.Activate(); worksheet.Name = sheetName; worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing); //excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls"); excellApp.Quit(); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } }
public void GenerateReportsByGrade(string gardenName, DateTime[] dates, Action notify) { KillProcess(); var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); dir += @"\年级统计汇总"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); var reportTemplate = new ReportTemplate(); //按月分组 foreach (var date in dates) { var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date); try { Excel._Worksheet templateSheet = null; 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\GrowUpReport2.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); foreach (var groupByGrade in hierarchy.Root.GetCollection()) { m_objSheets = (Excel.Sheets)m_objBook.Worksheets; templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1); var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = string.Format("{0}部统计汇总", groupByGrade.Key); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}部统计表", gardenName, groupByGrade.Key); int offset = 1; foreach (var groupByClass in groupByGrade.Value.GetCollection()) { try { if (offset >= 2) m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1]).EntireRow.Insert(Excel.XlDirection.xlDown); var itemCount = DoGenerateReportsByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, date, offset); m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount; offset++; //offset多加了一次,下面计算的时候,OFFSET都会减一 var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore1); if (!string.IsNullOrEmpty(ignorePos)) { var regex = new Regex(@"(?<col>\w+)(?<row>\d)"); foreach (var split in ignorePos.Split(',')) { var match = regex.Match(split); var column = match.Groups["col"].Value; var row = match.Groups["row"].Value; var range = m_objSheet.Range[string.Format("{0}6:{0}{1}", column, Convert.ToInt32(row) + offset)]; range.Value = "/"; range = m_objSheet.Range[string.Format("{0}5:{0}{1}", column, Convert.ToInt32(row) + offset)]; range.Interior.ColorIndex = 27; //http://dmcritchie.mvps.org/excel/colors.htm range.EntireColumn.Hidden = true; } } m_objSheet.get_Range(string.Format("A{0}", 6 + offset)).Value = string.Format("统计日期:{0}", date.ToString("yyyy.MM")); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } } templateSheet.Delete(); m_objBook.SaveAs(string.Format("{0}\\年级统计汇总({1}).xls", dir, date.ToString("yyyy.MM")), 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); notify(); } catch (Exception ex) { Util.WriteLog(ex.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 PrintKidStatInfo(string getGrade,string getClass,string getName,string getNumber,string savePath) { try { DataSet dsStuList_Stat = null; object[,] objData = null; DateTime getBegDate = BegDate; DateTime getEndDate = EndDate; string classInfo = string.Empty; using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() ) { if ( getName.Equals("") && getNumber.Equals("") ) { dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass); classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString(); } else { dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber); classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString(); } } if ( dsStuList_Stat.Tables[0].Rows.Count > 0 ) { objData = new object[dsStuList_Stat.Tables[0].Rows.Count,8]; for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ ) { DataSet dsStuCheckInfo_Stat = null; int times = 0; using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() ) { dsStuCheckInfo_Stat = stuCheckInfoDataAccess.GetStuCheckInfo_Stat(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate,ref times); objData[nameRow,0] = dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString(); objData[nameRow,1] = 0; objData[nameRow,2] = 0; objData[nameRow,3] = 0; objData[nameRow,6] = 0; int total = SetAttendDays(); int health = 0; int ill = 0; int watch = 0; int absence = 0; DataRow[] drHealth = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=0"); if (drHealth.Length > 0) { health = Convert.ToInt32(drHealth[0]["stateCount"]); } DataRow[] drWatch = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=2"); if (drWatch.Length > 0) { watch = Convert.ToInt32(drWatch[0]["stateCount"]); } DataRow[] drIll = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=3"); if (drIll.Length > 0) { ill = Convert.ToInt32(drIll[0]["stateCount"]); } absence = total - health - watch - ill; objData[nameRow,1] = string.Format("{0} ({1})", health, ((double)health/(double)total).ToString("0.00%")); objData[nameRow,2] = string.Format("{0} ({1})", watch, ((double)watch/(double)total).ToString("0.00%")); objData[nameRow,3] = string.Format("{0} ({1})", ill, ((double)ill/(double)total).ToString("0.00%")); objData[nameRow,6] = string.Format("{0} ({1})", absence, ((double)absence/(double)total).ToString("0.00%")); // foreach ( DataRow stateRow in dsStuCheckInfo_Stat.Tables[0].Rows ) // { // // // // if ( Convert.ToInt32(stateRow[1]) == 0 ) // { // objData[nameRow,1] = stateRow[0].ToString() // +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")"; // continue; // } // // else if ( Convert.ToInt32(stateRow[1]) == 2 ) // { // objData[nameRow,2] = stateRow[0].ToString() // +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")"; // continue; // } // // else if ( Convert.ToInt32(stateRow[1]) == 3 ) // { // objData[nameRow,3] = stateRow[0].ToString() // +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")"; // continue; // } // // else if ( Convert.ToInt32(stateRow[1]) == -1 ) // { //// int total = SetAttendDays(); //// int health = //// //// objData[nameRow,6] = stateRow[0].ToString() //// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")"; //// continue; // // objData[nameRow,6] = stateRow[0].ToString() // +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")"; // continue; // } // //晨检状态错误 // else // { // Util.WriteLog("晨检状态错误!",Util.EXCEPTION_LOG_TITLE); // break; // } // } objData[nameRow,4] = SetAttendDays(); objData[nameRow,5] = times; objData[nameRow,7] = (times/(double)SetAttendDays()).ToString("0.00%"); } } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidStat.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.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A4",m_objOpt); m_objRange = m_objRange.get_Resize(dsStuList_Stat.Tables[0].Rows.Count,8); 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_objRange.Font.Size = 10; WriteJumpboard(dsStuList_Stat.Tables[0].Rows.Count,classInfo); 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 ReadStuInfoXLS(ref DataSet dsStuInfoXls,string getPath,int classNumbers,ref Hashtable className) { OleDbDataAdapter myAdp = null; OleDbConnection oleConn = null; string[] sheetName = new string[classNumbers]; DataTable[] stuInfoXlsTable = new DataTable[classNumbers]; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; for ( int i=0; i<classNumbers; i++ ) { m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1)); //记录工作表名称 sheetName[i] = m_objSheet.Name.Trim(); className.Add(i,sheetName[i]); } m_objBooks.Close(); oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"); oleConn.Open(); for ( int i=0; i<classNumbers; i++ ) { stuInfoXlsTable[i] = new DataTable("Table"+i.ToString()); myAdp = new OleDbDataAdapter("select * from ["+sheetName[i]+"$]", oleConn); //获取第一张工作表的内容 myAdp.Fill(stuInfoXlsTable[i]); dsStuInfoXls.Tables.Add(stuInfoXlsTable[i]); } oleConn.Close(); 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_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath) { DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className); if (dtPresents == null || dtPresents.Rows.Count == 0) { throw new Exception("没有要使用的数据!"); } else { data.Columns.AddRange(new DataColumn[]{ new DataColumn("小计", Type.GetType("System.Double")), new DataColumn("stuPresent", Type.GetType("System.String")), new DataColumn("stuAbsent", Type.GetType("System.String"))}); if (dtPresents.Rows.Count != data.Rows.Count) { throw new Exception("检测到数据完整性错误,请尝试重新生成数据!"); } else { for (int i = 0; i < dtPresents.Rows.Count; i++) { data.Rows[i]["小计"] = 0; data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"]; data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"]; } } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.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); object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4]; for(int row = 0; row < data.Rows.Count; row++) { objData[row, 0] = row + 1; for (int column = 0; column < data.Columns.Count - 3; column++) { if (column <= 2) { objData[row, column + 1] = data.Rows[row][column]; } else if(column == 3) { objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2]; objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1]; double temp = Convert.ToDouble(data.Rows[row][column]); objData[row, column + 3] = temp; objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 : Convert.ToDouble(objData[data.Rows.Count, column + 3])); } else if (column >= 4 && column < 7) { double temp = Convert.ToDouble(data.Rows[row][column]); objData[row, column + 3] = temp; objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 : Convert.ToDouble(objData[data.Rows.Count, column + 3])); } else if (column == 7) { double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) + Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) + Convert.ToDouble(data.Rows[row][column - 1]); objData[row, column + 3] = temp1; double temp2 = Convert.ToDouble(data.Rows[row][column]); objData[row, column + 4] = temp2; objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 : Convert.ToDouble(objData[data.Rows.Count, column + 3])); objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 : Convert.ToDouble(objData[data.Rows.Count, column + 4])); } else { double temp = Convert.ToDouble(data.Rows[row][column]); objData[row, column + 4] = temp; objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 : Convert.ToDouble(objData[data.Rows.Count, column + 4])); } } } m_objRange = m_objSheet.get_Range("A6", m_objOpt); m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1); 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_objRange.Font.Size = 10; m_objRange = m_objSheet.get_Range("G3", m_objOpt); m_objRange.Value = "各项费用"; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]); m_objRange.Merge(m_objOpt); 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_objRange.Font.Size = 12; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]); m_objRange.Merge(m_objOpt); 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; object[, ] objColumn = new object[1, data.Columns.Count - 5]; for (int column = 3; column < data.Columns.Count - 3; column++ ) { if (column < 7) { objColumn[0, column - 3] = data.Columns[column].ColumnName; } if (column == 7) { objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName; objColumn[0, column - 2] = data.Columns[column].ColumnName; } else { objColumn[0, column - 2] = data.Columns[column].ColumnName; } } m_objRange = m_objSheet.get_Range("G5", m_objOpt); m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5); m_objRange.Value = objColumn; 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_objRange.Font.Size = 10.5; for (int column = 5; column < data.Columns.Count - 3; column++) { m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]); object i = m_objRange.Calculate(); string s = "asf"; } 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); } }
public void ImportCardExcelFile(string id,string name,string grade, string atClass,bool isStu,string savePath) { if(grade.Equals("全部")) { grade = string.Empty; } if(atClass.Equals("全部")) { atClass = string.Empty; } DataSet cardInfo; object[,] objData; string excelTempFilePath = AppDomain.CurrentDomain.BaseDirectory; Excel.Application m_objExcel = null; Excel.Workbooks m_objBooks = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.Range m_objRange = null; Excel.Font m_objFont = null; System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value; try { if(isStu) { using(CardInfoDA cardInfoDA = new CardInfoDA()) { cardInfo = cardInfoDA.GetStuCardNumberForExcel(id,name,grade,atClass); } if(cardInfo.Tables[0].Rows.Count>0) { objData = new Object[cardInfo.Tables[0].Rows.Count,7]; for(int i=0;i<cardInfo.Tables[0].Rows.Count;i++) { objData[i,0] = cardInfo.Tables[0].Rows[i][0].ToString(); objData[i,1] = cardInfo.Tables[0].Rows[i][1].ToString(); objData[i,2] = cardInfo.Tables[0].Rows[i][2].ToString(); objData[i,3] = cardInfo.Tables[0].Rows[i][3].ToString(); objData[i,4] = cardInfo.Tables[0].Rows[i][4].ToString(); objData[i,5] = cardInfo.Tables[0].Rows[i][5].ToString(); objData[i,6] = cardInfo.Tables[0].Rows[i][6].ToString(); } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath+@"report\StudentCardInfo.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.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(cardInfo.Tables[0].Rows.Count,7); 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_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_objFont); 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); } } else { using(CardInfoDA cardInfoDA = new CardInfoDA()) { cardInfo = cardInfoDA.GetTeaCardNumberForExcel(id,name,grade,atClass); } if(cardInfo.Tables[0].Rows.Count>0) { objData = new Object[cardInfo.Tables[0].Rows.Count,6]; for(int i=0;i<cardInfo.Tables[0].Rows.Count;i++) { objData[i,0] = cardInfo.Tables[0].Rows[i][0].ToString(); objData[i,1] = cardInfo.Tables[0].Rows[i][1].ToString(); objData[i,2] = cardInfo.Tables[0].Rows[i][2].ToString(); objData[i,3] = cardInfo.Tables[0].Rows[i][3].ToString(); objData[i,4] = cardInfo.Tables[0].Rows[i][4].ToString(); objData[i,5] = cardInfo.Tables[0].Rows[i][5].ToString(); } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath+@"report\TeacherCardInfo.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.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(cardInfo.Tables[0].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_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_objFont); 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 ex) { Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objFont = null; m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); } }
public void ReadUpdateGradeInfo(ref DataTable dtUpdateGradeInfo,string getPath) { OleDbDataAdapter myAdp = null; OleDbConnection oleConn = null; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; oleConn = new OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;data source=" + getPath //打开驱动器 + ";Extended Properties=Excel 8.0"); oleConn.Open(); myAdp = new OleDbDataAdapter("select * from [年班升级$]", oleConn); //获取第一张工作表的内容 myAdp.Fill(dtUpdateGradeInfo); oleConn.Close(); 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_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
//全日观察打印 public void PrintAbnormalRecord(DataSet dsDailyWatch,string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\ObserveReport.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); object[,] objData = null; if ( dsDailyWatch.Tables[0].Rows.Count > 0 ) { objData = new Object[dsDailyWatch.Tables[0].Rows.Count,8]; for ( int i=0; i<dsDailyWatch.Tables[0].Rows.Count; i++ ) { objData[i,0] = dsDailyWatch.Tables[0].Rows[i]["observetime"].ToString(); objData[i,1] = dsDailyWatch.Tables[0].Rows[i]["stu_name"].ToString(); objData[i,2] = "体温"+dsDailyWatch.Tables[0].Rows[i]["morningReg_heat"].ToString()+"," +"精神"+dsDailyWatch.Tables[0].Rows[i]["morningReg_spirit"].ToString()+"," +"口腔"+dsDailyWatch.Tables[0].Rows[i]["morningReg_mouth"].ToString()+"," +"皮肤"+dsDailyWatch.Tables[0].Rows[i]["morningReg_skin"].ToString(); objData[i,3] = dsDailyWatch.Tables[0].Rows[i]["morningReg_genearchTold"].ToString(); objData[i,4] = dsDailyWatch.Tables[0].Rows[i]["morningReg_treat"].ToString(); objData[i,5] = "一日活动"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_movement"].ToString()+"," +"精神"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_spirit"].ToString()+"," +"食欲"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_appetite"].ToString()+"," +"睡眠"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_sleep"].ToString()+"," +"大小便"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_stool"].ToString()+"," +"咳嗽"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_cough"].ToString()+"," +"其他情况:"+dsDailyWatch.Tables[0].Rows[i]["dailyReg_else"].ToString(); if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_ctrlMoveTreat"]) ) { objData[i,6] += "控制运动量,"; } if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_tendTreat"]) ) { objData[i,6] += "注意生活护理,"; } if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_seafoodTreat"]) ) { objData[i,6] += "忌海鲜,"; } if ( Convert.ToBoolean(dsDailyWatch.Tables[0].Rows[i]["dailyReg_measureHeat"]) ) { objData[i,6] += "按时测体温."; } objData[i,7] = dsDailyWatch.Tables[0].Rows[i]["dailyReg_teacherSign"].ToString(); } m_objRange = m_objSheet.get_Range("A5",m_objOpt); m_objRange = m_objRange.get_Resize(dsDailyWatch.Tables[0].Rows.Count,8); 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_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 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(); } }
public void GenerateCheckReportsGradeByGrade(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify) { var reportTemplate = new ReportTemplate(); var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir); if (!Directory.Exists(gradeDir)) Directory.CreateDirectory(gradeDir); 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\GrowUpReport6.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); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}幼儿在园体验汇总表 ({1}) 应出勤{2}天", gardenName, date.ToString("yyyy-MM月"), checkInDays); checkInDays = checkInDays == 0 ? 1 : checkInDays; int offset = 1; int total1 = 0; int total2 = 0; int total3 = 0; int total4 = 0; int total5 = 0; int total6 = 0; int total7 = 0; int total8 = 0; int total9 = 0; int total10 = 0; int total11 = 0; int total12 = 0; int total13 = 0; int total14 = 0; int total15 = 0; int total16 = 0; int total17 = 0; int total18 = 0; int total19 = 0; int total20 = 0; int total21 = 0; int total22 = 0; int totalStudentCount = 0; foreach (var groupByGrade in hierarchy.Root.GetCollection()) { foreach (var groupByClass in groupByGrade.Value.GetCollection()) { try { if (offset >= 2) { m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } DoGenerateCheckReportsGradeByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, checkInDays, offset, ref total1, ref total2,ref total3, ref total4,ref total5, ref total6,ref total7, ref total8,ref total8, ref total10,ref total11, ref total12, ref total13, ref total14,ref total15, ref total16,ref total17, ref total18,ref total19, ref total20,ref total21, ref total22, ref totalStudentCount); offset++; } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } } offset = offset - 2; WriteCheckReportCell(reportTemplate, "s_total", totalStudentCount.ToString(), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "0_total", string.Format("{0}({1})", total1, (total1 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_total", string.Format("{0}({1})", total2, (total2 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_total", string.Format("{0}({1})", total3, (total3 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "-1_total", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_1_total", string.Format("{0}({1})", total5, (total5 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_2_total", string.Format("{0}({1})", total6, (total6 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_3_total", string.Format("{0}({1})", total7, (total7 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_1_total", string.Format("{0}({1})", total8, (total8 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_2_total", string.Format("{0}({1})", total9, (total9 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_3_total", string.Format("{0}({1})", total10, (total10 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_1_total", string.Format("{0}({1})", total11, (total11 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_2_total", string.Format("{0}({1})", total12, (total12 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_3_total", string.Format("{0}({1})", total13, (total13 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_1_total", string.Format("{0}({1})", total14, (total14 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_2_total", string.Format("{0}({1})", total15, (total15 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_3_total", string.Format("{0}({1})", total16, (total16 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_1_total", string.Format("{0}({1})", total17, (total17 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_2_total", string.Format("{0}({1})", total18, (total18 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_3_total", string.Format("{0}({1})", total19, (total19 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_1_total", string.Format("{0}({1})", total20, (total20 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_2_total", string.Format("{0}({1})", total21, (total21 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_3_total", string.Format("{0}({1})", total22, (total22 / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "0_all", string.Format("{0}({1})", total1 + total2 + total3, ((total1 + total2 + total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_all", string.Format("{0}({1})", total4, ((checkInDays * totalStudentCount - total1 - total2 - total3) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_all", string.Format("{0}({1})", total5 + total6 + total7, ((total5 + total6 + total7) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_all", string.Format("{0}({1})", total8 + total9 + total10, ((total8 + total9 + total10) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_all", string.Format("{0}({1})", total11 + total12 + total13, ((total11 + total12 + total13) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_all", string.Format("{0}({1})", total14 + total15 + total16, ((total14 + total15 + total16) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_all", string.Format("{0}({1})", total17 + total18 + total19, ((total17 + total18 + total9) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "7_all", string.Format("{0}({1})", total20 + total21 + total22, ((total20 + total21 + total22) / ((double)checkInDays * totalStudentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); m_objSheet.get_Range(string.Format("A{0}", 7 + offset)).Value = string.Format("统计日期:{0}", date.ToString("yyyy.MM.dd")); m_objBook.SaveAs(string.Format("{0}\\全园体验({1}).xls", gradeDir, date.ToString("yyyy.MM.dd")), 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); notify(); } catch (Exception ex) { Util.WriteLog(ex.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 DoGenerateReportsPersonByPerson( string gardenName, string className, GrowUpReportDataAccess.ReportGroupByStudent grouping, Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime beginDate, DateTime endDate) { foreach (var groupByStudent in grouping.GetCollection()) { var stuName = groupByStudent.Value.Key; var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = stuName; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}({1}){2} {3} \"快乐成长\"记录情况汇总", gardenName, className, groupByStudent.Key, stuName); int offset = 0; foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection()) { int k = 0; var category = groupByCategory.Key; var reportDateList = groupByCategory.Value.GetAllReports().ToList(); for (int i = 0; i < reportDateList.Count; i++) { var report = reportDateList[i]; if (!string.IsNullOrEmpty(report.Item)) { if (k >= 1) { offset++; //向上插入一行 m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(string.Format("{0}_Item", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = report.Item; } range = reportTemplate.GetPos(string.Format("{0}_Time", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = reportDateList[i].Date.ToString("yyyy年MM月"); } range = reportTemplate.GetPos(string.Format("{0}_Desc", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = report.Content; } range = reportTemplate.GetPos(string.Format("{0}_Pic", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); if (!string.IsNullOrEmpty(report.PicUrl) && !report.PicUrl.Contains("http")) { m_objRange.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(m_objRange.Left); PicTop = Convert.ToSingle(m_objRange.Top); var fileName = AppDomain.CurrentDomain.BaseDirectory + report.PicUrl; if (!File.Exists(fileName)) { if (!string.IsNullOrEmpty(report.RawUrl) && report.RawUrl.Contains("http")) { var buffer = new WebClient().DownloadData(report.RawUrl); using (var stream = new MemoryStream(buffer)) { using (var img = Image.FromStream(stream)) { img.Save(fileName); } } } } if (File.Exists(fileName)) { m_objSheet.Shapes.AddPicture(fileName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, (float)m_objRange.Width, (float)m_objRange.Height); } } else m_objRange.Value = report.PicUrl; } if (i == reportDateList.Count - 1) { if (k >= 1) { //将各级别单元格合并 var currentRow = m_objRange.Row; MergeLevel(currentRow, "C"); MergeLevel(currentRow, "H"); MergeLevel(currentRow, "B"); MergeLevel(currentRow, "A"); } } k++; } } } m_objSheet.Range[string.Format("A2:H{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; m_objSheet.get_Range(string.Format("A{0}", 39 + offset), m_objOpt).Value = string.Format("汇总周期:{0}---{1}", beginDate.ToString("yyyy.MM.dd"), endDate.ToString("yyyy.MM.dd")); } templateSheet.Delete(); }
public void GenerateCheckReportsClassByClass(string gardenName, GrowUpReportDataAccess.CheckReportHierarchy hierarchy, int checkInDays, DateTime date, string dir, Action notify) { var reportTemplate = new ReportTemplate(); var gradeDir = string.Format(@"{0}\管理员报表\幼儿体验汇总", dir); if (!Directory.Exists(gradeDir)) Directory.CreateDirectory(gradeDir); try { foreach (var groupByGrade in hierarchy.Root.GetCollection()) { Excel._Worksheet templateSheet = null; 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\GrowUpReport5.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; templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1); foreach (var groupByClass in groupByGrade.Value.GetCollection()) { try { var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = groupByClass.Key; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}宝宝体验汇总表 ({2})应出勤{3}天", gardenName, groupByClass.Key, date.ToString("yyyy-MM月"), checkInDays); DoGenerateCheckReportsClassByClass(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, date, checkInDays); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } templateSheet.Delete(); m_objBook.SaveAs(string.Format("{0}\\{1}({2}).xls", gradeDir, groupByGrade.Key, date.ToString("yyyy.MM.dd")), 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); notify(); } } catch (Exception ex) { Util.WriteLog(ex.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 GenerateReportsPersonByPerson(string gardenName, DateTime[] dates, Action notify) { KillProcess(); var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\班主任报表"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates); var reportTemplate = new ReportTemplate(); try { foreach (var groupByGrade in hierarchy.Root.GetCollection()) { var gradeDir = string.Format(@"{0}\{1}个人汇总", dir, groupByGrade.Key); if (!Directory.Exists(gradeDir)) Directory.CreateDirectory(gradeDir); foreach (var groupByClass in groupByGrade.Value.GetCollection()) { 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\GrowUpReport1.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); //生成年龄段表头 var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report1"); foreach (var pos in kvp.Value.Split(',')) { m_objSheet.get_Range(pos).Value = kvp.Key; } DoGenerateReportsPersonByPerson(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, dates[0], dates[dates.Length - 1]); m_objBook.SaveAs(string.Format("{0}\\{1}.xls", gradeDir, groupByClass.Key), 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); notify(); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } } } catch (Exception ex) { Util.WriteLog(ex.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 GenerateStatsReports(string gardenName, DateTime[] dates, Action notify) { KillProcess(); var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); dir += "\\年级汇总"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates); var reportTemplate = new ReportTemplate(); try { foreach (var groupByGrade in hierarchy.Root.GetCollection()) { Excel._Worksheet templateSheet = null; 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\GrowUpReport3.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); foreach (var groupByClass in groupByGrade.Value.GetStatsCollection()) { try { int studentCount = groupByGrade.Value.GetStudentCount(groupByClass.Key); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1); var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = string.Format("{0}汇总", groupByClass.Key); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}({1}{2}人) “快乐成长”记录情况汇总(各项表现数量及所占比率)", gardenName, groupByClass.Key, studentCount); //生成年龄段表头 var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report3"); foreach (var pos in kvp.Value.Split(',')) { m_objSheet.get_Range(pos).Value = kvp.Key; } var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore2); if (!string.IsNullOrEmpty(ignorePos)) { foreach (var split in ignorePos.Split(',')) { if (!string.IsNullOrEmpty(split)) { var column = split[0].ToString(); var row = split.Substring(1); var lastRange = m_objSheet.get_Range(string.Format("J{0}", row)); var ignoreRange = m_objSheet.get_Range(split); ignoreRange.Interior.ColorIndex = lastRange.Interior.ColorIndex; ignoreRange.Value = lastRange.Value; } } } DoGenerateStatsReports(gardenName, groupByGrade.Key, groupByClass.Key, studentCount, groupByClass.Value, reportTemplate); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } templateSheet.Delete(); m_objBook.SaveAs(string.Format("{0}\\{1}.xls", dir, groupByGrade.Key), 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); notify(); } } catch (Exception ex) { Util.WriteLog(ex.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 PrintKidDetailInfo(string getGrade,string getClass,string getName,string getNumber,string savePath) { try { DataSet dsStuList_Stat = null; DateTime getBegDate = BegDate; DateTime getEndDate = EndDate; int mbTwoDates = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month); int monthes = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month); string classInfo = string.Empty; using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() ) { if ( getName.Equals("") && getNumber.Equals("") ) { dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass); classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString(); } else { dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber); classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString(); } } if ( dsStuList_Stat.Tables[0].Rows.Count > 0 ) { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidDetail.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.Sheets; for ( int i=0; i<=monthes; i++ ) { GetExcelEndDate(getBegDate,ref getEndDate); m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1)); if ( mbTwoDates >= 1 ) m_objSheet.Copy(m_objOpt,m_objSheet); m_objRange = m_objSheet.get_Range("AE2",m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange = m_objSheet.get_Range("AE3",m_objOpt); m_objRange.Value = classInfo; m_objRange = m_objSheet.get_Range("AE4",m_objOpt); m_objRange.Value = "'" + getBegDate.ToString("yyyy.MM"); WriteMonth(getBegDate); for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ ) { DataSet dsStuCheckInfo_Detail = null; using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() ) { dsStuCheckInfo_Detail = stuCheckInfoDataAccess.GetStuCheckInfo_Detail(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate); WriteExcelForDetail(getBegDate,getEndDate,dsStuCheckInfo_Detail,nameRow, dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString()); } } mbTwoDates--; getBegDate = getEndDate.AddDays(1); getEndDate = EndDate; } 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 PrintDoseInfo(DataSet dsDoseInfo,string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\DiagnosisReport.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); object[,] objData = null; if ( dsDoseInfo.Tables[0].Rows.Count > 0 ) { objData = new Object[dsDoseInfo.Tables[0].Rows.Count,8]; for ( int i=0; i<dsDoseInfo.Tables[0].Rows.Count; i++ ) { objData[i,0] = dsDoseInfo.Tables[0].Rows[i][6].ToString(); objData[i,1] = dsDoseInfo.Tables[0].Rows[i][7].ToString(); objData[i,2] = dsDoseInfo.Tables[0].Rows[i][8].ToString(); objData[i,3] = dsDoseInfo.Tables[0].Rows[i][9].ToString(); objData[i,4] = dsDoseInfo.Tables[0].Rows[i][2].ToString(); objData[i,5] = dsDoseInfo.Tables[0].Rows[i][3].ToString(); objData[i,6] = dsDoseInfo.Tables[0].Rows[i][4].ToString() +" "+dsDoseInfo.Tables[0].Rows[i][10].ToString(); objData[i,7] = dsDoseInfo.Tables[0].Rows[i][5].ToString(); } m_objRange = m_objSheet.get_Range("A3",m_objOpt); m_objRange = m_objRange.get_Resize(dsDoseInfo.Tables[0].Rows.Count,8); for ( int i=2,m=1; i<=m_objRange.Rows.Count; i++,m++ ) { if(objData[m,1].ToString().Equals(objData[m-1,1].ToString())&& objData[m,6].ToString().Substring(0,10).Equals(objData[m-1,6].ToString().Substring(0,10))) { string startAMergeAddress = "A"+((int)(i-1)).ToString(); string endAMergeAddress = "A"+i.ToString(); m_objRange.get_Range(startAMergeAddress,endAMergeAddress).Merge(m_objOpt); string startBMergeAddress = "B"+((int)(i-1)).ToString(); string endBMergeAddress = "B"+i.ToString(); m_objRange.get_Range(startBMergeAddress,endBMergeAddress).Merge(m_objOpt); string startCMergeAddress = "C"+((int)(i-1)).ToString(); string endCMergeAddress = "C"+i.ToString(); m_objRange.get_Range(startCMergeAddress,endCMergeAddress).Merge(m_objOpt); string startDMergeAddress = "D"+((int)(i-1)).ToString(); string endDMergeAddress = "D"+i.ToString(); m_objRange.get_Range(startDMergeAddress,endDMergeAddress).Merge(m_objOpt); } } 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_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 PrintMorningCheckInfo(DataSet dsMorningCheckInfo,string getBegDate,string getEndDate,string savePath) { try { object[,] morningCheckInfoData = null; if ( dsMorningCheckInfo.Tables[0].Rows.Count > 0 ) { morningCheckInfoData = new object[dsMorningCheckInfo.Tables[0].Rows.Count,5]; for ( int i=0; i<dsMorningCheckInfo.Tables[0].Rows.Count; i++ ) { morningCheckInfoData[i,0] = dsMorningCheckInfo.Tables[0].Rows[i][2]; morningCheckInfoData[i,1] = dsMorningCheckInfo.Tables[0].Rows[i][0]; morningCheckInfoData[i,2] = dsMorningCheckInfo.Tables[0].Rows[i][1]; morningCheckInfoData[i,3] = dsMorningCheckInfo.Tables[0].Rows[i][3]; morningCheckInfoData[i,4] = dsMorningCheckInfo.Tables[0].Rows[i][4]; } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfo.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.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("B3",m_objOpt); m_objRange = m_objRange.get_Resize(dsMorningCheckInfo.Tables[0].Rows.Count,5); m_objRange.Value = morningCheckInfoData; 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 = 9; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 2], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 2]); 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[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 3], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 4, 3]); 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[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 2], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 2]); 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[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 3], (Excel.Range)m_objSheet.Cells[dsMorningCheckInfo.Tables[0].Rows.Count + 5, 3]); m_objRange.Value = Convert.ToDateTime(getBegDate).ToString("yyyy.MM.dd")+" -- "+Convert.ToDateTime(getEndDate).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(); } }
private bool hasSheet(string getSheetName) { findSheet = false; for ( int i=1; i<=m_objSheets.Count; i++ ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(i); if ( getSheetName.Equals(m_objSheet.Name.ToString()) ) { findSheet = true; break; } } if ( !findSheet ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheets.Add(m_objSheet,Type.Missing,1,Type.Missing); return false; } else return true; }
public void VisitInfoPrint(DataSet dsAbsDetailInfo,DateTime getBegDate,DateTime 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\VisitInfo.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 < dsAbsDetailInfo.Tables[0].Rows.Count; row ++ ) { for( int column = 1 ;column < dsAbsDetailInfo.Tables[0].Columns.Count-2; column ++) { m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 6, column + 1], (Excel.Range)m_objSheet.Cells[row + 6, column + 1]); m_objRange.Value = dsAbsDetailInfo.Tables[0].Rows[row][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; } if ( row == dsAbsDetailInfo.Tables[0].Rows.Count - 1 ) { m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 2], (Excel.Range)m_objSheet.Cells[row + 8, 3]); m_objRange.MergeCells = true; m_objRange.Value = "园所信息:"; m_objRange.Font.Bold = true; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 8, 4], (Excel.Range)m_objSheet.Cells[row + 8, 4]); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 2], (Excel.Range)m_objSheet.Cells[row + 9, 3]); m_objRange.MergeCells = true; m_objRange.Value = "统计启始时间:"; m_objRange.Font.Bold = true; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 9, 4], (Excel.Range)m_objSheet.Cells[row + 9, 4]); m_objRange.Value = getBegDate; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 2], (Excel.Range)m_objSheet.Cells[row + 10, 3]); m_objRange.MergeCells = true; m_objRange.Value = "统计截止时间:"; m_objRange.Font.Bold = true; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[row + 10, 4], (Excel.Range)m_objSheet.Cells[row + 10, 4]); m_objRange.Value = getEndDate; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.HorizontalAlignment = 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(); } }
public void WriteStuCardInfoXLS(DataSet dsStuInfo,string getPath) { KillProcess(); try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Open( //打开该文件 getPath,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing)); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; if ( dsStuInfo.Tables[0].Rows.Count > 0 ) { objData = new object[dsStuInfo.Tables[0].Rows.Count,2]; for ( int i=0; i<dsStuInfo.Tables[0].Rows.Count; i++ ) { objData[i,0] = dsStuInfo.Tables[0].Rows[i][0].ToString(); objData[i,1] = dsStuInfo.Tables[0].Rows[i][1].ToString(); } } m_objRange = m_objSheet.get_Range("A2",m_objOpt); m_objRange = m_objRange.get_Resize(dsStuInfo.Tables[0].Rows.Count,2); m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.Font.Size = 10; m_objExcel.Visible = true; } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); } }
public void PrintNchsHealthPersonal(string getOutputClass,string getOutputName,string getOutputNumber,DataSet dsHealthOutput, string getBegDate,string getEndDate,string savePath,string getTeacher) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\NchsHealthOutputPersonal", 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; for ( int sheetNumbers=1; sheetNumbers<dsHealthOutput.Tables[0].Rows.Count; sheetNumbers++ ) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Copy(Type.Missing,m_objSheet); } for ( int row=0; row<dsHealthOutput.Tables[0].Rows.Count; row++) { m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(row+1); m_objSheet.Name = dsHealthOutput.Tables[0].Rows[row]["info_stuName"].ToString()+ "("+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"]).Year.ToString()+ "."+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"]).Month.ToString()+")"; m_objRange = m_objSheet.get_Range("E2",m_objOpt); m_objRange.Value = " "+dsHealthOutput.Tables[0].Rows[row]["info_stuName"].ToString(); m_objRange = m_objSheet.get_Range("G2",m_objOpt); m_objRange.Value = " "+dsHealthOutput.Tables[0].Rows[row]["info_stuGender"].ToString(); m_objRange = m_objSheet.get_Range("I2",m_objOpt); m_objRange.Value = " "+Convert.ToDateTime(dsHealthOutput.Tables[0].Rows[row]["info_stuBirthday"]).ToString("yyyy-MM-dd"); m_objRange = m_objSheet.get_Range("B6",m_objOpt); string realAge = dsHealthOutput.Tables[0].Rows[row]["nchsanaly_realage"].ToString(); if ( realAge.IndexOf(".") < 0 ) { m_objRange.Value = realAge; m_objRange = m_objSheet.get_Range("C6",m_objOpt); m_objRange.Value = 0; } else { m_objRange.Value = realAge.Substring(0,realAge.IndexOf(".")); m_objRange = m_objSheet.get_Range("C6",m_objOpt); m_objRange.Value = realAge.Substring(realAge.IndexOf(".")+1); } double getHeight = Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_height"]); m_objRange = m_objSheet.get_Range("B7",m_objOpt); m_objRange.Value = getHeight; if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("上") ) { m_objRange = m_objSheet.get_Range("D7",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中上") ) { m_objRange = m_objSheet.get_Range("E7",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中+") ) { m_objRange = m_objSheet.get_Range("F7",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中-") ) { m_objRange = m_objSheet.get_Range("H7",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_heightresult"].ToString().Equals("中下") ) { m_objRange = m_objSheet.get_Range("J7",m_objOpt); m_objRange.Value = "√"; } else { m_objRange = m_objSheet.get_Range("L7",m_objOpt); m_objRange.Value = "√"; } double getWeight = Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weight"]); m_objRange = m_objSheet.get_Range("B8",m_objOpt); m_objRange.Value = getWeight; if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("上") ) { m_objRange = m_objSheet.get_Range("D8",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中上") ) { m_objRange = m_objSheet.get_Range("E8",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中+") ) { m_objRange = m_objSheet.get_Range("F8",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中-") ) { m_objRange = m_objSheet.get_Range("H8",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weightresult"].ToString().Equals("中下") ) { m_objRange = m_objSheet.get_Range("J8",m_objOpt); m_objRange.Value = "√"; } else { m_objRange = m_objSheet.get_Range("L8",m_objOpt); m_objRange.Value = "√"; } int getGender = dsHealthOutput.Tables[0].Rows[row]["info_stuGender"].ToString().Equals("男")?0:1; double ageHeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsAgeHeightMiddleValue(realAge,getGender).Rows[0]["nchs_middle"]); double ageWeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsAgeWeightMiddleValue(realAge,getGender).Rows[0]["nchs_middle"]); string rangeValue = string.Empty; if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_underweight"].Equals("中度体重低下") || dsHealthOutput.Tables[0].Rows[row]["nchsanaly_underweight"].Equals("重度体重低下") ) { m_objRange = m_objSheet.get_Range("C12",m_objOpt); rangeValue = Math.Abs((Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_weight"])-ageWeightMiddleValue)/ageWeightMiddleValue).ToString("0.0%")+","; m_objRange.Value = rangeValue; m_objRange = m_objSheet.get_Range("A14",m_objOpt); m_objRange.Value = "√"; } if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_stunting"].Equals("中度生长迟缓") || dsHealthOutput.Tables[0].Rows[row]["nchsanaly_stunting"].Equals("重度生长迟缓") ) { m_objRange = m_objSheet.get_Range("C12",m_objOpt); rangeValue += Math.Abs((Convert.ToDouble(dsHealthOutput.Tables[0].Rows[row]["nchsanaly_height"])-ageHeightMiddleValue)/ageHeightMiddleValue).ToString("0.0%"); m_objRange.Value = rangeValue; m_objRange = m_objSheet.get_Range("C14",m_objOpt); m_objRange.Value = "√"; } else { if (rangeValue == string.Empty) { m_objRange = m_objSheet.get_Range("F11", m_objOpt); m_objRange.Value = "等"; m_objRange = m_objSheet.get_Range("C12", m_objOpt); m_objRange.Value = ageWeightMiddleValue.ToString() + "," + ageHeightMiddleValue.ToString(); m_objRange = m_objSheet.get_Range("E14",m_objOpt); m_objRange.Value = "√"; } else { m_objRange = m_objSheet.get_Range("C12",m_objOpt); rangeValue.Replace(",",""); m_objRange.Value = rangeValue; } } double heightWeightMiddleValue = Convert.ToDouble(new HealthManagementDataAccess().GetNchsHeightWeightMiddleValue(getHeight,getGender).Rows[0]["nchs_middle"]); if ( !dsHealthOutput.Tables[0].Rows[row]["nchsanaly_wasting"].ToString().Equals("正常") ) { m_objRange = m_objSheet.get_Range("K11", m_objOpt); m_objRange.Value = "低于"; m_objRange = m_objSheet.get_Range("H13",m_objOpt); m_objRange.Value = Math.Abs((getWeight - heightWeightMiddleValue)/heightWeightMiddleValue).ToString("0.0%"); if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_wasting"].ToString().Equals("中度消瘦") ) { m_objRange = m_objSheet.get_Range("H15",m_objOpt); m_objRange.Value = "√"; } else { m_objRange = m_objSheet.get_Range("G15",m_objOpt); m_objRange.Value = "√"; } } else { if ( !dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("正常") ) { m_objRange = m_objSheet.get_Range("K11", m_objOpt); m_objRange.Value = "高于"; m_objRange = m_objSheet.get_Range("H13",m_objOpt); m_objRange.Value = Math.Abs((getWeight - heightWeightMiddleValue)/heightWeightMiddleValue).ToString("0.0%"); if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("轻度肥胖") ) { m_objRange = m_objSheet.get_Range("I15",m_objOpt); m_objRange.Value = "√"; } else if ( dsHealthOutput.Tables[0].Rows[row]["nchsanaly_obesity"].ToString().Equals("中度肥胖") ) { m_objRange = m_objSheet.get_Range("J15",m_objOpt); m_objRange.Value = "√"; } else { m_objRange = m_objSheet.get_Range("K15",m_objOpt); m_objRange.Value = "√"; } } else { m_objRange = m_objSheet.get_Range("H13", m_objOpt); m_objRange.Value = heightWeightMiddleValue.ToString(); m_objRange = m_objSheet.get_Range("L15", m_objOpt); m_objRange.Value = "√"; } } m_objRange = m_objSheet.get_Range("H17",m_objOpt); m_objRange.Value = getTeacher; m_objRange = m_objSheet.get_Range("H19",m_objOpt); m_objRange.Value = DateTime.Now; m_objRange = m_objSheet.get_Range("H21", m_objOpt); m_objRange.Value = dsHealthOutput.Tables[0].Rows[row]["nchsanaly_checktime"].ToString(); } 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 WriteCardInfoExcel(DataSet dsCardInfo,string savePath) // { // try // { // System.Reflection.Missing m_objOpt = System.Reflection.Missing.Value; // // object[,] objData; // // if ( dsCardInfo.Tables[0] != null ) // { // objData = new object[dsCardInfo.Tables[0].Rows.Count+1,1]; // objData[0,0] = "导出的卡号"; // // for( int i=1; i<=dsCardInfo.Tables[0].Rows.Count; i++ ) // objData[i,0] = dsCardInfo.Tables[0].Rows[i-1]["info_stuCardNumber"]; // // m_objExcel = new Excel.Application(); // m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; // m_objBook = (Excel._Workbook)m_objBooks.Add(true); // // m_objSheets = (Excel.Sheets)m_objBook.Sheets; // m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); // // m_objRange = m_objSheet.get_Range("A1",m_objOpt); // m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count+1,1); // m_objRange.Value = objData; // // 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 ex) // { // Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE); // } // finally // { // m_objFont = null; // m_objRange = null; // m_objSheet = null; // m_objSheets = null; // m_objBook = null; // m_objBooks = null; // m_objExcel = null; // // GC.Collect(); // } // // } public void WriteCardInfoExcel(DataSet dsCardInfo,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.Add(true); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; int index = 0; if (dsCardInfo.Tables[0].Rows.Count > 0) { objData = new object[dsCardInfo.Tables[0].Rows.Count + 1, dsCardInfo.Tables[0].Columns.Count + 2]; objData[0,0] = "年级"; objData[0,1] = "班级"; objData[0,2] = "学号"; objData[0,3] = "卡号"; objData[0,4] = "操作"; objData[0,5] = "统计"; for (int row = 0; row < dsCardInfo.Tables[0].Rows.Count; row++) { objData[row + 1, 0] = dsCardInfo.Tables[0].Rows[row][0]; objData[row + 1, 1] = dsCardInfo.Tables[0].Rows[row][1]; objData[row + 1, 2] = dsCardInfo.Tables[0].Rows[row][2]; objData[row + 1, 3] = dsCardInfo.Tables[0].Rows[row][3]; } foreach(DataRow row in dsCardInfo.Tables[2].Rows) { objData[index + 1, 5] = row[1]; index += Convert.ToInt32(row[1]); } m_objRange = m_objSheet.get_Range("A1",m_objOpt); m_objRange = m_objRange.get_Resize(dsCardInfo.Tables[0].Rows.Count + 4,6); m_objRange.Value = objData; m_objRange.Font.Size = 8; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; RowMerge("A", dsCardInfo.Tables[1]); RowMerge("B", dsCardInfo.Tables[2]); RowMerge("C", dsCardInfo.Tables[3]); RowMerge("F", dsCardInfo.Tables[2]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = "空卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()); m_objRange.Value = GetCardEncryptedNumber() - Convert.ToInt32(dsCardInfo.Tables[4].Rows[0][0]) - Convert.ToInt32(dsCardInfo.Tables[5].Rows[0][0]); m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = "学生卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 3).ToString()); m_objRange.Value = dsCardInfo.Tables[4].Rows[0][0]; m_objRange = m_objSheet.get_Range("A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "A" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = "教师卡"; m_objRange = m_objSheet.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString(), "B" + (dsCardInfo.Tables[0].Rows.Count + 4).ToString()); m_objRange.Value = dsCardInfo.Tables[5].Rows[0][0]; m_objRange.get_Range("B" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString(), "F" + (dsCardInfo.Tables[0].Rows.Count + 2).ToString()).Merge(m_objOpt); 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(); } } catch(Exception ex) { throw ex; } finally { if (m_objRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); m_objRange = null; } if (m_objSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); m_objSheet = null; } if (m_objSheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); m_objSheets = null; } if (m_objBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); m_objBook = null; } if (m_objBooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); m_objBooks = null; } if(m_objExcel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objExcel = null; } GC.Collect(); KillProcess(); } }
//健康评测打印 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(); } }
public void PrintTeaBaseInfo(TeacherBase tBase,string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\TeacherBaseInfo.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); if ( NPrintPicture() ) { m_objPictures = (Excel.Pictures)m_objSheet.Pictures(m_objOpt); m_objPictures.Insert(@"c:\temp.jpg",m_objOpt); m_objPicture = (Excel.Picture)m_objPictures.Item(1); m_objRange = m_objSheet.get_Range("H8","I15"); m_objPicture.Left = (double)m_objRange.Left; m_objPicture.Top = (double)m_objRange.Top; m_objPicture.Width = (double)m_objRange.Width; m_objPicture.Height = (double)m_objRange.Height; } //园所名 m_objRange = m_objSheet.get_Range("C6",m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); //打印日期 m_objRange = m_objSheet.get_Range("H32",m_objOpt); m_objRange.Value = DateTime.Now.Date.ToString("yyyy.MM.dd"); //姓名 m_objRange = m_objSheet.get_Range("C8",m_objOpt); m_objRange.Value = tBase.TName; //性别 m_objRange = m_objSheet.get_Range("E8",m_objOpt); m_objRange.Value = tBase.TSex; //学历 m_objRange = m_objSheet.get_Range("G8",m_objOpt); m_objRange.Value = tBase.TCareer; //家庭电话 m_objRange = m_objSheet.get_Range("C12",m_objOpt); m_objRange.Value = tBase.THomeTel; //手机号码 m_objRange = m_objSheet.get_Range("E12",m_objOpt); m_objRange.Value = tBase.TPhone; //办公电话 m_objRange = m_objSheet.get_Range("G12",m_objOpt); m_objRange.Value = tBase.TWorkTel; //婚否 m_objRange = m_objSheet.get_Range("C16",m_objOpt); m_objRange.Value = tBase.TMerrige; //家庭住址 m_objRange = m_objSheet.get_Range("F16",m_objOpt); m_objRange.Value = tBase.TAddr; //所属部门 m_objRange = m_objSheet.get_Range("C20",m_objOpt); m_objRange.Value = tBase.TDepart; //职务 m_objRange = m_objSheet.get_Range("F20",m_objOpt); m_objRange.Value = tBase.TDuty; //职称 m_objRange = m_objSheet.get_Range("H20",m_objOpt); m_objRange.Value = tBase.TTechnicalPost; //教师等级 m_objRange = m_objSheet.get_Range("C24",m_objOpt); m_objRange.Value = tBase.TLevel; //参加工作时间 m_objRange = m_objSheet.get_Range("G24",m_objOpt); m_objRange.Value = tBase.TWorkTime.ToString("yyyy-MM-dd"); //入园时间 m_objRange = m_objSheet.get_Range("C28",m_objOpt); m_objRange.Value = tBase.TEnterTime.ToString("yyyy-MM-dd"); 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 DoGenerateCheckReportsPersonByPerson(string gardenName, string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping, Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime date, int checkInDays) { foreach (var groupByStudent in grouping.GetCollection()) { var stuName = groupByStudent.Value.Key; var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = stuName; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}({2}) {3} 在园体验汇总表 ({4})应出勤{5}天", gardenName, className, groupByStudent.Key, stuName, date.ToString("yyyy-MM月"), checkInDays); int offset = 1; foreach (var report in groupByStudent.Value.Value.GetAllReports()) { if (offset >= 2) { m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } WriteCheckReportCell(reportTemplate, "date", report.Date, offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State1, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State2, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State3, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State4, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State5, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State6, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State7, "1", offset, ReportTemplate.ReportType.Report4); offset++; } offset = offset - 2; var sum1 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("0")); var sum2 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3")); var sum3 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2")); var sum4 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("-1")); var sum5 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_1")); var sum6 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_2")); var sum7 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_3")); var sum8 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_1")); var sum9 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_2")); var sum10 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_3")); var sum11 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_1")); var sum12 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_2")); var sum13 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_3")); var sum14 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_1")); var sum15 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_2")); var sum16 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_3")); var sum17 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_1")); var sum18 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_2")); var sum19 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_3")); var sum20 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_1")); var sum21 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_2")); var sum22 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_3")); WriteCheckReportCell(reportTemplate, "0_sum", sum1.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_sum", sum2.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_sum", sum3.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "-1_sum", sum4.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_1_sum", sum5.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_2_sum", sum6.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_3_sum", sum7.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_1_sum", sum8.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_2_sum", sum9.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_3_sum", sum10.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_1_sum", sum11.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_2_sum", sum12.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_3_sum", sum13.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_1_sum", sum14.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_2_sum", sum15.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_3_sum", sum16.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_1_sum", sum17.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_2_sum", sum18.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_3_sum", sum19.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_1_sum", sum20.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_2_sum", sum21.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_3_sum", sum22.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_total", (sum1 + sum2 + sum3 + sum4).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_total", (sum5 + sum6 + sum7).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_total", (sum8 + sum9 + sum10).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_total", (sum11 + sum12 + sum13).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_total", (sum14 + sum15 + sum16).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_total", (sum17 + sum18 + sum19).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "7_total", (sum20 + sum21 + sum22).ToString(), offset, ReportTemplate.ReportType.Report4); } templateSheet.Delete(); }
public void FinanMgmtInfoPrint(DataSet dsFinanInfo,string savePath) { int rowStart = 6; try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\FinanMgmtInfo.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); m_objSheet.Copy(m_objSheet,m_objOpt); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); m_objSheet.Name = dsFinanInfo.Tables[0].Rows[0][2].ToString(); for ( int finanRow = 0;finanRow < dsFinanInfo.Tables[0].Rows.Count; finanRow ++ ) { if ( !dsFinanInfo.Tables[0].Rows[finanRow][2].ToString().Equals(m_objSheet.Name) ) { SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[finanRow-1][2].ToString()); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Copy(m_objSheet,m_objOpt); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count-1); m_objSheet.Name = dsFinanInfo.Tables[0].Rows[finanRow][2].ToString(); rowStart = 6; } for ( int column = 1;column < dsFinanInfo.Tables[0].Columns.Count; column ++) { switch ( column ) { case 1: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column],m_objSheet.Cells[rowStart+1,column]); m_objRange.MergeCells = true; m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 3: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]); m_objRange.MergeCells = true; m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 4: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart+1,column-1]); m_objRange.MergeCells = true; m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 5: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 6: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 7: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-1],m_objSheet.Cells[rowStart,column-1]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 8: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 9: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 10: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-4],m_objSheet.Cells[rowStart+1,column-4]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 11: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-4],m_objSheet.Cells[rowStart,column-4]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 12: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart+1,column-5],m_objSheet.Cells[rowStart+1,column-5]); m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; break; case 13: m_objRange = m_objSheet.get_Range(m_objSheet.Cells[rowStart,column-5],m_objSheet.Cells[rowStart+1,column-5]); m_objRange.MergeCells = true; m_objRange.Value = dsFinanInfo.Tables[0].Rows[finanRow][column].ToString(); m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; break; } } rowStart += 2; } SetJumpBoard(rowStart,dsFinanInfo.Tables[0].Rows[dsFinanInfo.Tables[0].Rows.Count-1][2].ToString()); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; 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 GenerateReportsByClass(string gardenName, DateTime[] dates, Action notify) { KillProcess(); var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); dir += @"\班级统计汇总"; if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); var reportTemplate = new ReportTemplate(); //按月分组 foreach(var date in dates) { var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date); try { foreach (var groupByGrade in hierarchy.Root.GetCollection()) { Excel._Worksheet templateSheet = null; 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\GrowUpReport2.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); foreach (var groupByClass in groupByGrade.Value.GetCollection()) { try { m_objSheets = (Excel.Sheets)m_objBook.Worksheets; templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1); var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = string.Format("{0}统计汇总", groupByClass.Key); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}统计表", gardenName, groupByClass.Key); DoGenerateReportsByClass(groupByGrade.Key, groupByClass.Value, reportTemplate, date); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } templateSheet.Delete(); m_objBook.SaveAs(string.Format("{0}\\{1}统计汇总({2}).xls", dir, groupByGrade.Key, date.ToString("yyyy.MM")), 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); notify(); } } catch (Exception ex) { Util.WriteLog(ex.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(); } } }