private void RowMerge(string column, DataTable dt) { int index = 0; foreach (DataRow row in dt.Rows) { m_objRange.get_Range(column + (index + 2).ToString(), column + (index + Convert.ToInt32(row[1]) + 1).ToString()).Merge(m_objOpt); index += Convert.ToInt32(row[1]); } }
private void MergeData(ArrayList alMergeYPoint) { string[] columns = new string[] { "A", "B", "C", "D", "E", "F", "G", "H" }; for (int index = 0; index < alMergeYPoint.Count; index++) { int rangeStartPointer = Convert.ToInt32(((object[, ])(alMergeYPoint[index]))[0, 0]) + 1; int rangeEndPointer = Convert.ToInt32(((object[, ])(alMergeYPoint[index]))[0, 1]) + 1; foreach (string column in columns) { string startAddress = column + Convert.ToString(rangeStartPointer); string endAddress = column + Convert.ToString(rangeEndPointer); m_objRange.get_Range(startAddress, endAddress).Merge(m_objOpt); } } }
//服药记录打印 public void PrintDoseInfo(DataSet dsDoseInfo,string savePath) { try { m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\DiagnosisReport.xls", m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt, m_objOpt,m_objOpt,m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1); object[,] objData = null; if ( dsDoseInfo.Tables[0].Rows.Count > 0 ) { objData = new Object[dsDoseInfo.Tables[0].Rows.Count,8]; for ( int i=0; i<dsDoseInfo.Tables[0].Rows.Count; i++ ) { objData[i,0] = dsDoseInfo.Tables[0].Rows[i][6].ToString(); objData[i,1] = dsDoseInfo.Tables[0].Rows[i][7].ToString(); objData[i,2] = dsDoseInfo.Tables[0].Rows[i][8].ToString(); objData[i,3] = dsDoseInfo.Tables[0].Rows[i][9].ToString(); objData[i,4] = dsDoseInfo.Tables[0].Rows[i][2].ToString(); objData[i,5] = dsDoseInfo.Tables[0].Rows[i][3].ToString(); objData[i,6] = dsDoseInfo.Tables[0].Rows[i][4].ToString() +" "+dsDoseInfo.Tables[0].Rows[i][10].ToString(); objData[i,7] = dsDoseInfo.Tables[0].Rows[i][5].ToString(); } m_objRange = m_objSheet.get_Range("A3",m_objOpt); m_objRange = m_objRange.get_Resize(dsDoseInfo.Tables[0].Rows.Count,8); for ( int i=2,m=1; i<=m_objRange.Rows.Count; i++,m++ ) { if(objData[m,1].ToString().Equals(objData[m-1,1].ToString())&& objData[m,6].ToString().Substring(0,10).Equals(objData[m-1,6].ToString().Substring(0,10))) { string startAMergeAddress = "A"+((int)(i-1)).ToString(); string endAMergeAddress = "A"+i.ToString(); m_objRange.get_Range(startAMergeAddress,endAMergeAddress).Merge(m_objOpt); string startBMergeAddress = "B"+((int)(i-1)).ToString(); string endBMergeAddress = "B"+i.ToString(); m_objRange.get_Range(startBMergeAddress,endBMergeAddress).Merge(m_objOpt); string startCMergeAddress = "C"+((int)(i-1)).ToString(); string endCMergeAddress = "C"+i.ToString(); m_objRange.get_Range(startCMergeAddress,endCMergeAddress).Merge(m_objOpt); string startDMergeAddress = "D"+((int)(i-1)).ToString(); string endDMergeAddress = "D"+i.ToString(); m_objRange.get_Range(startDMergeAddress,endDMergeAddress).Merge(m_objOpt); } } m_objRange.Value = objData; m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; m_objRange.WrapText = true; m_objRange.Borders.LineStyle = BorderStyle.FixedSingle; m_objFont = m_objRange.Font; m_objFont.Size = 9; } m_objBook.SaveAs(savePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); } catch(Exception e) { Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE); } finally { m_objRange = null; m_objSheet = null; m_objSheets = null; m_objBook = null; m_objBooks = null; m_objExcel = null; GC.Collect(); KillProcess(); } }
// public void 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 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(); } }