/// <summary> /// Fill the excel sheet with data along with the position specified /// </summary> /// <param name="columnrow"></param> /// <param name="data"></param> private void FillExcelWithData() { _excelRange = _excelSheet.get_Range("A1", _value); _excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount); _excelRange.set_Value(Missing.Value, ExcelData); _excelRange.EntireColumn.AutoFit(); }
/// <summary> /// Method to add an excel rows /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows (string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }
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(); } }
}//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(自动分页,并指定要合并的列索引) /// </summary> /// <param name="arr">二维数组</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> /// <param name="mergeColumnIndex">数组的二维索引,相当于DataTable的列索引,索引从0开始</param> public void ArrayToExcel(string[,] arr, int rows, int top, int left, int mergeColumnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } //将二维数组数据写入Excel for (int i = sheetCount; i >= 1; i--) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将二维数组中的数据写入WorkSheet for (int j = 0; j < endRow - startRow; j++) { for (int k = 0; k < colCount; k++) { workSheet.Cells[top + j, left + k] = arr[startRow + j, k]; } } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = arr[startRow + j, k]; } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; //合并相同行 this.MergeRows(workSheet, left + mergeColumnIndex, top, rows); } }//end ArrayToExcel
public void RealtimeMorningPrint(DataSet dsRealtimeInfoStat_Teacher,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\RealtimeMorningInfoStat_Teacher.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 ( dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count > 0 ) { objData = new object[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count,7]; for ( int i=0; i<dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count; i++ ) { objData[i,0] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][0].ToString(); objData[i,1] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][1].ToString(); objData[i,2] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][2].ToString(); objData[i,3] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][3].ToString(); objData[i,4] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][4].ToString(); objData[i,5] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][5].ToString(); objData[i,6] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][6].ToString(); } } m_objRange = m_objSheet.get_Range("A7",m_objOpt); m_objRange = m_objRange.get_Resize(dsRealtimeInfoStat_Teacher.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_objRange.Font.Size = 10; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+8,1],m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+8,1]); m_objRange.Value = "园所信息:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+8,2],m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+8,3]); m_objRange.MergeCells = true;; m_objRange.Value = " "+new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+9,1],m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+9,1]); m_objRange.Value = "统计日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+9,2],m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count+9,3]); m_objRange.MergeCells = true; m_objRange.Value = " "+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 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(); } }
/// <summary> /// 将DataTable数据写入Excel文件(自动分页,并指定要合并的列索引) /// </summary> /// <param name="dt">DataTable</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> /// <param name="mergeColumnIndex">DataTable中要合并相同行的列索引,从0开始</param> public void DataTableToExcel(DataTable dt, int rows, int top, int left, int mergeColumnIndex) { int rowCount = dt.Rows.Count; //源DataTable行数 int colCount = dt.Columns.Count; //源DataTable列数 sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 // StringBuilder sb; //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将dt中的数据写入WorkSheet // for(int j=0;j<endRow-startRow;j++) // { // for(int k=0;k<colCount;k++) // { // workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString(); // } // } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = dt.Rows[startRow + j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; //合并相同行 this.MergeRows(workSheet, left + mergeColumnIndex, top, rows); } }
}//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(不分页),合并指定列的相同行 /// </summary> /// <param name="arr">二维数组</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> /// <param name="isFormula">填充的数据是否需要计算</param> /// <param name="mergeColumnIndex">需要合并行的列索引</param> public void ArrayToExcel(string[,] arr, int top, int left, bool isFormula, int mergeColumnIndex) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); //注意:使用range.FormulaArray写合并的单元格会出问题 if (isFormula) range.FormulaArray = arr; else range.Value = arr; this.MergeRows(workSheet, mergeColumnIndex, top, rowCount); }//end ArrayToExcel
private void BindingData(string className) { dv.RowFilter = @"info_className = '" + className + "'"; object[,] objData = new object[dv.Count,9]; ArrayList alMergeYPoint = new ArrayList(); int pointer = -1; for (int i = 0; i < dv.Count; i++) { int compareTimes = 0; objData[i,0] = dv[i][0]; objData[i,1] = dv[i][1]; objData[i,2] = dv[i][3]; objData[i,3] = dv[i][4]; objData[i,4] = dv[i][5]; objData[i,5] = dv[i][6]; objData[i,6] = dv[i][7]; objData[i,7] = dv[i][8]; objData[i,8] = dv[i][9]; if (i > pointer) { //innerPoint是EXCEL的行指针 for (int innerPointer = 1; innerPointer < dv.Count-i; innerPointer++) { if (Convert.ToInt32(objData[i,1]) == Convert.ToInt32(dv[i+innerPointer][1])) { compareTimes++; continue; } else { if (compareTimes != 0) { object[,] objRange = new object[1,2]; pointer = i + innerPointer - 1; objRange[0,0] = i; objRange[0,1] = pointer; alMergeYPoint.Add(objRange); } break; } } } } m_objRange = m_objSheet.get_Range("A4",m_objOpt); m_objRange = m_objRange.get_Resize(dv.Count,9); MergeData(alMergeYPoint); 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_objRange.Font.Size = 10; m_objFont = m_objRange.Font; }
/// <summary> /// 将DataTable数据写入Excel文件(自动分页) /// </summary> /// <param name="dt">DataTable</param> /// <param name="rows">每个WorkSheet写入多少行数据</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> public void DataTableToExcel(DataTable dt, int rows, int top, int left) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数 // StringBuilder sb; //复制sheetCount-1个WorkSheet对象 for (int i = 1; i < sheetCount; i++) { workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Copy(missing, workBook.Worksheets[i]); } for (int i = 1; i <= sheetCount; i++) { int startRow = (i - 1) * rows; //记录起始行索引 int endRow = i * rows; //记录结束行索引 //若是最后一个WorkSheet,那么记录结束行索引为源DataTable行数 if (i == sheetCount) endRow = rowCount; //获取要写入数据的WorkSheet对象,并重命名 workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i); workSheet.Name = sheetPrefixName + "-" + i.ToString(); //将dt中的数据写入WorkSheet // for(int j=0;j<endRow-startRow;j++) // { // for(int k=0;k<colCount;k++) // { // workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString(); // } // } //利用二维数组批量写入 int row = endRow - startRow; string[,] ss = new string[row, colCount]; for (int j = 0; j < row; j++) { for (int k = 0; k < colCount; k++) { ss[j, k] = dt.Rows[startRow + j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(row, colCount); range.Value = ss; #region 利用Windwo粘贴板批量拷贝数据(在Web下面行不通) /*sb = new StringBuilder(); for(int j=0;j<endRow-startRow;j++) { for(int k=0;k<colCount;k++) { sb.Append( dt.Rows[startRow + j][k].ToString() ); sb.Append("\t"); } sb.Append("\n"); } System.Windows.Forms.Clipboard.SetDataObject(sb.ToString()); range = (Excel.Range)workSheet.Cells[top,left]; workSheet.Paste(range,false);*/ #endregion } }
/// <summary> /// Method to make columns auto fit according to data /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> private void AutoFitColumns(string startRange, int rowCount, int colCount) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.Columns.AutoFit(); }
private void ExportSingle( DateTime startDate, DateTime endDate, Excel._Workbook m_objBook, Excel.Sheets m_objSheets, Excel._Worksheet m_objSheet, Excel.Range m_objRange, Excel.Font m_objFont, System.Reflection.Missing m_objOpt) { using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0]) { if (dt != null && dt.Rows.Count > 0) { DataView dv = dt.DefaultView; using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0]) { object[, ] objData = null; foreach (DataRow dr in dtBaseInfo.Rows) { dv.RowFilter = "T_Number = " + dr[1].ToString(); objData = new object[dv.Count, 6]; if (dv.Count > 0) { for (int i = 0; i < dv.Count; i++) { objData[i, 0] = dv[i][3].ToString(); objData[i, 1] = dv[i][2].ToString(); objData[i, 2] = dv[i][0].ToString(); objData[i, 3] = dv[i][1].ToString(); objData[i, 4] = dv[i][4].ToString(); objData[i, 5] = dv[i][5].ToString(); } m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4)); m_objSheet.Copy(Type.Missing, m_objSheet); m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5)); m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]); m_objRange = m_objSheet.get_Range("A3", m_objOpt); m_objRange = m_objRange.get_Resize(dv.Count, 6); m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("A" + (dv.Count + 4).ToString(), m_objOpt); m_objRange.Value = "园所:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dv.Count + 4).ToString(), m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dv.Count + 5).ToString(), m_objOpt); m_objRange.Value = "统计开始日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dv.Count + 5).ToString(), m_objOpt); m_objRange.Value = startDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dv.Count + 6).ToString(), m_objOpt); m_objRange.Value = "统计结束日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dv.Count + 6).ToString(), m_objOpt); m_objRange.Value = endDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } } } m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4)); m_objSheet.Delete(); } } }
// 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 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(); } }
private void AutoFitColumns(string startRange, int rowCount, int colCount) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.Columns.AutoFit(); }
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 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(); } }
private void ExportAllNormal( DateTime startDate, DateTime endDate, Excel._Workbook m_objBook, Excel.Sheets m_objSheets, Excel._Worksheet m_objSheet, Excel.Range m_objRange, Excel.Font m_objFont, System.Reflection.Missing m_objOpt) { object[,] objData; using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0]) { if (dt != null && dt.Rows.Count > 0) { objData = new Object[dt.Rows.Count, 6]; for (int i = 0; i < dt.Rows.Count; i++) { objData[i, 0] = dt.Rows[i][3].ToString(); objData[i, 1] = dt.Rows[i][2].ToString(); objData[i, 2] = dt.Rows[i][0].ToString(); objData[i, 3] = dt.Rows[i][1].ToString(); objData[i, 4] = dt.Rows[i][4].ToString(); objData[i, 5] = dt.Rows[i][5].ToString(); } m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); m_objRange = m_objSheet.get_Range("A3", m_objOpt); m_objRange = m_objRange.get_Resize(dt.Rows.Count, 6); m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 4).ToString(), m_objOpt); m_objRange.Value = "园所:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 4).ToString(), m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 5).ToString(), m_objOpt); m_objRange.Value = "统计开始日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 5).ToString(), m_objOpt); m_objRange.Value = startDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 6).ToString(), m_objOpt); m_objRange.Value = "统计结束日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 6).ToString(), m_objOpt); m_objRange.Value = endDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } } }
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 void ExportSingleStat( DateTime startDate, DateTime endDate, Excel._Workbook m_objBook, Excel.Sheets m_objSheets, Excel._Worksheet m_objSheet, Excel.Range m_objRange, Excel.Font m_objFont, System.Reflection.Missing m_objOpt) { object[,] objData; using (DataTable dt = new DutyInfoDA().GetTeacherStatSingle(startDate, endDate)) { if (dt != null && dt.Rows.Count > 0) { objData = new Object[dt.Rows.Count + 1, 6]; int days = SetAttendDays(startDate, endDate); for (int i = 0; i < dt.Rows.Count; i++) { double attendCount = Convert.ToInt32(dt.Rows[i][2]); double shouldAttendCount = days; double absenceCount = shouldAttendCount - attendCount; objData[i, 0] = dt.Rows[i][0].ToString(); objData[i, 1] = dt.Rows[i][1].ToString(); objData[i, 2] = shouldAttendCount; objData[i, 3] = attendCount; objData[i, 4] = absenceCount < 0 ? 0 : absenceCount; objData[i, 5] = shouldAttendCount == 0 ? attendCount.ToString("0.00%") : (attendCount / shouldAttendCount).ToString("0.00%"); } m_objSheets = (Excel.Sheets)m_objBook.Sheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3)); m_objRange = m_objSheet.get_Range("A3", m_objOpt); m_objRange = m_objRange.get_Resize(dt.Rows.Count, 6); m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 4).ToString(), m_objOpt); m_objRange.Value = "园所:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 4).ToString(), m_objOpt); m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 5).ToString(), m_objOpt); m_objRange.Value = "统计开始日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 5).ToString(), m_objOpt); m_objRange.Value = startDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (dt.Rows.Count + 6).ToString(), m_objOpt); m_objRange.Value = "统计结束日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("B" + (dt.Rows.Count + 6).ToString(), m_objOpt); m_objRange.Value = endDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 9; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } } }
public void RealtimeMorningPrint(DataSet dsRealtimeInfoStat_Teacher, 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\RealtimeMorningInfoStat_Teacher.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 (dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count > 0) { objData = new object[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count, 7]; for (int i = 0; i < dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count; i++) { objData[i, 0] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][0].ToString(); objData[i, 1] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][1].ToString(); objData[i, 2] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][2].ToString(); objData[i, 3] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][3].ToString(); objData[i, 4] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][4].ToString(); objData[i, 5] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][5].ToString(); objData[i, 6] = dsRealtimeInfoStat_Teacher.Tables[0].Rows[i][6].ToString(); } } m_objRange = m_objSheet.get_Range("A7", m_objOpt); m_objRange = m_objRange.get_Resize(dsRealtimeInfoStat_Teacher.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_objRange.Font.Size = 10; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 1]); m_objRange.Value = "园所信息:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 2], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 8, 3]); m_objRange.MergeCells = true;; m_objRange.Value = " " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 1]); m_objRange.Value = "统计日期:"; m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange = m_objSheet.get_Range(m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 2], m_objSheet.Cells[dsRealtimeInfoStat_Teacher.Tables[0].Rows.Count + 9, 3]); m_objRange.MergeCells = true; m_objRange.Value = " " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 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 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(); } }
/// <summary> /// 将数据填充到Excel工作表的单元格中 /// </summary> /// <param name="startRange"></param> /// <param name="rowCount"></param> /// <param name="colCount"></param> /// <param name="values"></param> private void AddExcelRows(string startRange, int rowCount, int colCount, object values) { _range = _sheet.get_Range(startRange, _optionalValue); _range = _range.get_Resize(rowCount, colCount); _range.set_Value(_optionalValue, values); }
private void BindingData(string className) { dv.RowFilter = @"info_className = '" + className + "'"; object[,] objData = new object[dv.Count, 9]; ArrayList alMergeYPoint = new ArrayList(); int pointer = -1; for (int i = 0; i < dv.Count; i++) { int compareTimes = 0; objData[i, 0] = dv[i][0]; objData[i, 1] = dv[i][1]; objData[i, 2] = dv[i][3]; objData[i, 3] = dv[i][4]; objData[i, 4] = dv[i][5]; objData[i, 5] = dv[i][6]; objData[i, 6] = dv[i][7].ToString() + "\r\n" + dv[i][8].ToString(); objData[i, 7] = dv[i][9].ToString() + "\r\n" + dv[i][10].ToString(); objData[i, 8] = dv[i][11]; if (i > pointer) { //innerPoint是EXCEL的行指针 for (int innerPointer = 1; innerPointer < dv.Count - i; innerPointer++) { if (Convert.ToInt32(objData[i, 1]) == Convert.ToInt32(dv[i + innerPointer][1])) { compareTimes++; continue; } else { if (compareTimes != 0) { object[,] objRange = new object[1, 2]; pointer = i + innerPointer - 1; objRange[0, 0] = i; objRange[0, 1] = pointer; alMergeYPoint.Add(objRange); } break; } } } } m_objRange = m_objSheet.get_Range("A4", m_objOpt); m_objRange = m_objRange.get_Resize(dv.Count - 1, 9); MergeData(alMergeYPoint); 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_objRange.Font.Size = 10; m_objFont = m_objRange.Font; }
//全日观察打印 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 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 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(); } }
/// <summary> /// 将DataTable数据写入Excel文件(不分页) /// </summary> /// <param name="dt">DataTable</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> public void DataTableToExcel(DataTable dt, int top, int left) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 //利用二维数组批量写入 string[,] arr = new string[rowCount+1, colCount+1]; for (int i = 0; i < colCount;i++ ) { arr[0, i] = dt.Columns[i].ColumnName.ToString(); } for (int j = 0; j < rowCount; j++) { for (int k = 0; k < colCount; k++) { arr[j+1, k] = dt.Rows[j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount+1, colCount+1); range.Value = arr; }
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(); } }
}//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(不分页) /// </summary> /// <param name="arr">二维数组</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(string[,] arr, int top, int left) { int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.FormulaArray = arr; }//end ArrayToExcel
//健康评测打印 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(); } }
}//end ArrayToExcel /// <summary> /// 将二维数组数据写入Excel文件(不分页) /// </summary> /// <param name="sheetIndex">工作表索引</param> /// <param name="arr">二维数组</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(int sheetIndex, string[,] arr, int top, int left) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围,WorkSheet索引不能大于WorkSheet数量!"); } // 改变当前工作表 this.workSheet = (Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex); int rowCount = arr.GetLength(0); //二维数组行数(一维长度) int colCount = arr.GetLength(1); //二维数据列数(二维长度) range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.Value2 = arr; }//end ArrayToExcel
public void ImportTeaOutDetailsReports(DataSet teaOutInfo, string savePath, DateTime getBegDate, DateTime getEndDate) { 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 (teaOutInfo.Tables[0].Rows.Count > 0) { objData = new Object[teaOutInfo.Tables[0].Rows.Count, 8]; for (int i = 0; i < teaOutInfo.Tables[0].Rows.Count; i++) { objData[i, 0] = teaOutInfo.Tables[0].Rows[i][0].ToString(); objData[i, 1] = teaOutInfo.Tables[0].Rows[i][1].ToString(); objData[i, 2] = teaOutInfo.Tables[0].Rows[i][2].ToString(); objData[i, 3] = teaOutInfo.Tables[0].Rows[i][3].ToString(); objData[i, 4] = teaOutInfo.Tables[0].Rows[i][4].ToString(); objData[i, 5] = teaOutInfo.Tables[0].Rows[i][5].ToString(); objData[i, 6] = teaOutInfo.Tables[0].Rows[i][6].ToString(); objData[i, 7] = teaOutInfo.Tables[0].Rows[i][7].ToString(); } m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelTempFilePath + @"report\TeacherOutDetails.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(teaOutInfo.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_objFont = m_objRange.Font; m_objFont.Size = 9; m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 4).ToString(), m_objOpt); m_objRange.Value = "园所: " + new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString(); m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; m_objRange = m_objSheet.get_Range("A" + (teaOutInfo.Tables[0].Rows.Count + 5).ToString(), m_objOpt); m_objRange.Value = "统计日期: " + getBegDate.ToString("yyyy.MM.dd") + "-" + getEndDate.ToString("yyyy.MM.dd"); m_objRange.Font.Bold = true; m_objRange.Font.Size = 12; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; 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(); 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 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); } }