private void btnIn_Click(object sender, EventArgs e) { DataTable dt = (DataTable)dgvDocGia.DataSource; Excel.Application exApp; Excel.Workbook exBook; Excel.Worksheet exSheet; Excel.Range exRange; exApp = new Excel.Application(); exBook = (Excel.Workbook)(exApp.Workbooks.Add(true)); exSheet = (Excel.Worksheet)exBook.ActiveSheet; int iRow = 7; for (int i = 0; i < dt.Rows.Count; i++) { exSheet.Cells[iRow, 2] = dt.Rows[i]["MaDG"].ToString(); exSheet.Cells[iRow, 3] = dt.Rows[i]["TenDG"].ToString(); exSheet.Cells[iRow, 4] = dt.Rows[i]["GioiTinh"].ToString(); exSheet.Cells[iRow, 5] = dt.Rows[i]["NgayMuon"].ToString(); exSheet.Cells[iRow, 6] = dt.Rows[i]["DiaChi"].ToString(); iRow++; } exRange = exSheet.get_Range("B6", "F" + (iRow - 1).ToString()); exRange.Borders.Color = Color.Black.ToArgb(); exRange.Font.Size = "18"; Excel.Range cl1 = exSheet.get_Range("B6", "B6"); cl1.Value = "Mã độc giả"; cl1.ColumnWidth = "15"; Excel.Range cl2 = exSheet.get_Range("C6", "C6"); cl2.Value = "Tên độc giả"; cl2.ColumnWidth = "30"; Excel.Range cl3 = exSheet.get_Range("D6", "D6"); cl3.Value = "Giới tính"; cl3.ColumnWidth = "15"; Excel.Range cl4 = exSheet.get_Range("E6", "E6"); cl4.Value = "Ngày mượn"; cl4.ColumnWidth = "30"; Excel.Range cl5 = exSheet.get_Range("F6", "F6"); cl5.Value = "Địa chỉ"; cl5.ColumnWidth = "30"; Excel.Range cl6 = exSheet.get_Range("B3", "F4"); cl6.Merge(false); cl6.HorizontalAlignment = 3; cl6.VerticalAlignment = 3; cl6.Value = "THỐNG KÊ ĐỘC GIẢ ĐÃ MƯỢN SÁCH"; cl6.Font.Size = "20"; exApp.UserControl = false; exApp.Visible = true; }
private void btnExport_Click(object sender, EventArgs e) { try { if (dgvMzyj.DataSource == null) { return; } Excel.Application myExcel = new Excel.ApplicationClass(); //设置不显示 myExcel.ScreenUpdating = false; //添加工作簿 myExcel.Workbooks.Add(true); //设置工作表 Excel.Worksheet mySheet = (Excel.Worksheet)myExcel.ActiveWorkbook.ActiveSheet; mySheet.Name = "TrasenHis"; //设置标题 Excel.Range myTitle = mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, dgvMzyj.Columns.Count]); myTitle.Merge(true); if (rdoSk.Checked == true) { myTitle.Value2 = this.Text + "(" + rdoSk.Text + ")"; } else { myTitle.Value2 = this.Text + "(" + rdoJk.Text + ")"; } myTitle.Font.Name = "黑体"; myTitle.Font.Size = 20; myTitle.Font.Bold = true; myTitle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; myTitle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置条件 Excel.Range myNote = mySheet.get_Range(mySheet.Cells[2, 1], mySheet.Cells[2, dgvMzyj.Columns.Count]); myNote.Merge(true); myNote.Value2 = dateTimePickerBegin.Value.ToLongDateString() + " " + dateTimePickerBegin.Value.ToLongTimeString() + " —— " + dateTimePickerEnd.Value.ToLongDateString() + " " + dateTimePickerEnd.Value.ToLongTimeString(); myNote.Font.Name = "宋体"; myNote.Font.Size = 12; myNote.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; myNote.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //写入表头 for (int i = 0; i < dgvMzyj.Columns.Count; i++) { myExcel.Cells[3, i + 1] = dgvMzyj.Columns[i].HeaderText; } //设置表头 Excel.Range myHeader = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[3, dgvMzyj.Columns.Count]); myHeader.Font.Size = 12; myHeader.Font.Bold = false; myHeader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //写入数据 for (int i = 0; i < dgvMzyj.Rows.Count; i++) { for (int j = 0; j < dgvMzyj.Columns.Count; j++) { myExcel.Cells[i + 4, j + 1] = dgvMzyj[j, i].Value; } } //设置表格 Excel.Range myTable = mySheet.get_Range(mySheet.Cells[3, 1], mySheet.Cells[dgvMzyj.Rows.Count + 3, dgvMzyj.Columns.Count]); myTable.Columns.AutoFit(); myTable.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; myTable.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; myTable.Borders.Weight = Excel.XlBorderWeight.xlMedium; //设置显示 myExcel.Visible = true; myExcel.ScreenUpdating = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
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); } }
private void btnIn_Click(object sender, EventArgs e) { myDatabase db = new myDatabase(); SqlConnection con = new SqlConnection(db.conSt); string madg = cbDocGia.Text; string sql = string.Format("Select * from MuonTraSach where MaDG='{0}'", madg); db.getData(sql); refreshDataGridView1(sql); DataTable dt = (DataTable)dgvMuontra.DataSource; Excel.Application exApp; Excel.Workbook exBook; Excel.Worksheet exSheet; Excel.Range exRange; exApp = new Excel.Application(); exBook = (Excel.Workbook)(exApp.Workbooks.Add(true)); exSheet = (Excel.Worksheet)exBook.ActiveSheet; int iRow = 7; for (int i = 0; i < dt.Rows.Count; i++) { exSheet.Cells[iRow, 2] = dt.Rows[i]["MaDG"].ToString(); exSheet.Cells[iRow, 3] = dt.Rows[i]["MaSach"].ToString(); exSheet.Cells[iRow, 4] = dt.Rows[i]["SoLuong"].ToString(); exSheet.Cells[iRow, 5] = dt.Rows[i]["NgayMuon"].ToString(); exSheet.Cells[iRow, 6] = dt.Rows[i]["NgayHenTra"].ToString(); iRow++; } exRange = exSheet.get_Range("B6", "F" + (iRow - 1).ToString()); exRange.Borders.Color = Color.Black.ToArgb(); exRange.Font.Size = "18"; Excel.Range cl1 = exSheet.get_Range("B6", "B6"); cl1.Value = "Mã độc giả"; cl1.ColumnWidth = "15"; Excel.Range cl2 = exSheet.get_Range("C6", "C6"); cl2.Value = "Mã sách"; cl2.ColumnWidth = "20"; Excel.Range cl3 = exSheet.get_Range("D6", "D6"); cl3.Value = "Số lượng"; cl3.ColumnWidth = "15"; Excel.Range cl4 = exSheet.get_Range("E6", "E6"); cl4.Value = "Ngày mượn"; cl4.ColumnWidth = "30"; Excel.Range cl5 = exSheet.get_Range("F6", "F6"); cl5.Value = "Ngày hẹn trả"; cl5.ColumnWidth = "30"; Excel.Range cl6 = exSheet.get_Range("B3", "F4"); cl6.Merge(false); cl6.HorizontalAlignment = 3; cl6.VerticalAlignment = 3; cl6.FormulaR1C1 = "PHIẾU MƯỢN SÁCH"; cl6.Font.Size = "20"; exApp.UserControl = false; exApp.Visible = true; }
/// <summary> /// 将datatable导出excel文件 /// </summary> /// <param name="dt">需要导出的datatable</param> /// <param name="AbosultedFilePath">导出文件的绝对路径</param> /// <returns></returns> public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath) { dt.Columns.Remove("id"); dt.Columns.Remove("year"); dt.Columns.Remove("month"); dt.Columns.Remove("jobflowid"); dt.AcceptChanges(); //检查数据表是否为空,如果为空,则退出 if (dt == null) { return(false); } //创建Excel应用程序对象,如果未创建成功则退出 Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel"); return(false); } //创建Excel的工作簿 Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; range = (Excel.Range)worksheet.get_Range("A1", "I1");//获取表格第一行 range.Merge(0); worksheet.Cells[1, 1] = this.biaoti.InnerText; range.Font.Size = 22; range.Font.Bold = true; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.EntireColumn.AutoFit(); range.EntireRow.AutoFit(); //写入标题 for (int i = 0; i < dt.Columns.Count + 1; i++) { //写入标题名称 if (i == 0) { worksheet.Cells[2, i + 1] = "序号"; //加入序号列 } else { worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName]; } range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Font.Bold = true;//粗体 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.Interior.ColorIndex = 15; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); if (i == 0)//序号列宽度设为自动调整 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit(); } } } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count + 1; c++) { range = (Excel.Range)worksheet.Cells[r + 3, c + 1]; //写入内容 if (c == 0) //增加序号 { worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); } else if (dt.Columns[c - 1].ColumnName == "happendate") //时间列 { worksheet.Cells[r + 3, c + 1] = ((DateTime)dt.Rows[r][c - 1]).ToString("yyyy年MM月dd日"); } else if (dt.Columns[c - 1].ColumnName == "ausmoney") //金额列 { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); range.NumberFormat = "#,##0.00"; } else if (dt.Columns[c - 1].ColumnName == "payStatus") //支付列 { if (dt.Rows[r][c - 1].ToString() == "1") { worksheet.Cells[r + 3, c + 1] = "已支付"; } else { worksheet.Cells[r + 3, c + 1] = "未支付"; } } else { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); } //设置样式 range.Font.Size = 9; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动 if (c == 0) //序号列宽度设为自动 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } //设置合计那一行 range = (Excel.Range)worksheet.get_Range("A" + (dt.Rows.Count + 3).ToString(), "I" + (dt.Rows.Count + 3).ToString()); range.Font.ColorIndex = 41; //range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone; range.Font.Size = 10; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 range.EntireColumn.AutoFit(); //自动调整列宽 worksheet.Cells[3 + dt.Rows.Count, 1] = "合计:"; //合计那一行的第一列 if (dt.Rows.Count == 0) { worksheet.Cells[3 + dt.Rows.Count, 7] = 0; } else { worksheet.Cells[3 + dt.Rows.Count, 7] = "=SUM(G3:G" + (dt.Rows.Count + 2).ToString() + ")"; } range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; System.Windows.Forms.Application.DoEvents(); try { workbook.Saved = true; workbook.SaveCopyAs(AbosultedFilePath); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); return(false); } workbook.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion return(true); }
//健康评测打印 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(); } }
/// <summary> /// 数据窗口DataWindow/DataStore导出成HTML,再转成EXCEL /// </summary> /// <param name="DS"></param> /// <param name="volExcel"></param> public static void ExportDataStore(Sybase.DataWindow.DataStore DS, clsVolDatawindowToExcel[] volExcel) { SaveFileDialog FD = new SaveFileDialog(); FD.Filter = "Excel 文档|*.xls"; FD.Title = "导出"; FD.ShowDialog(); string filename = FD.FileName.Trim(); if (filename != "") { DS.SaveAs(filename, Sybase.DataWindow.FileSaveAsType.HtmlTable, true, Sybase.DataWindow.FileSaveAsEncoding.Utf8); if (volExcel != null) { //定义 object MissingValue = Type.Missing; Excel.Application Excel_app = new Excel.ApplicationClass(); //打开Excel文挡 Excel.Workbook Excel_work = Excel_app.Workbooks.Open(filename, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); //获取当前工作列表 Excel.Worksheet mySheet = (Excel.Worksheet)Excel_work.Worksheets[1]; for (int i = volExcel.Length - 1; i >= 0; i--) { Excel.Range range = null; range = (Excel.Range)mySheet.Rows.get_Item(1, Type.Missing); //在顶行插入空行 range.Rows.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing); for (int j = 0; j < volExcel[i].m_title_text.Length; j++) { //合并行 if (volExcel[i].m_endcommn[j] == "ALL") { range = mySheet.get_Range(volExcel[i].m_firstcommn[j], mySheet.Cells[1, mySheet.UsedRange.Columns.Count]); } else { range = mySheet.get_Range(volExcel[i].m_firstcommn[j], volExcel[i].m_endcommn[j]); } range.Merge(MissingValue); range.Value2 = volExcel[i].m_title_text[j]; if (volExcel[i].m_HorizontalAlignment[j] == "0") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } else if (volExcel[i].m_HorizontalAlignment[j] == "L") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } else { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; } range.RowHeight = volExcel[i].m_rowheight[j]; } } mySheet.UsedRange.Rows.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; mySheet = null; Excel_app.ActiveWorkbook.Close(true, filename.ToString(), null); Excel_app.Workbooks.Close(); Excel_app.Quit(); Excel_app = null; } } }
public void createAnalysisDoc(int section_count, int sample_count) { app1 = new Excel.Application(); app1.Visible = false; workbook1 = app1.Workbooks.Add(1); workbook1.Sheets.Add(Count: 1); Analysis = (Excel.Worksheet)workbook1.Sheets[1]; Analysis.Name = "Analysis"; int j; int i; string title; string sample_title; for (j = 1; j <= section_count; j++) { int level = Convert.ToUInt16((sample_count * 2 + 12) * (j - 1)); title = string.Format("Stage {0}", j.ToString()); addData(level + 2, 1, title, "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[level + 2, 1], Analysis.Cells[level + 2, 9]]; workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; workSheet_range.Merge(); workSheet_range.Font.Bold = true; addData(level + 3, 1, "Manual", "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[level + 3, 1], Analysis.Cells[level + 3, 9]]; workSheet_range.Font.Italic = true; workSheet_range.Font.Bold = false; workSheet_range.Merge(); addData(level + 4, 2, "Time", "analysis"); addData(level + 4, 3, "Q Value Loaded (SVD)", "analysis"); addData(level + 4, 4, "Q Value Unloaded (SVD)", "analysis"); addData(level + 4, 5, "Q Value (NA)", "analysis"); addData(level + 4, 6, "Resonant Frequency (NA)", "analysis"); addData(level + 4, 7, "Resonant Frequency (SVD)", "analysis"); addData(level + 4, 8, "Cavity Temperature (C)", "analysis"); addData(level + 4, 9, "Room Temperature (C)", "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[level + 4, 1], Analysis.Cells[level + 4, 9]]; workSheet_range.Font.Bold = true; for (i = 0; i < sample_count; i++) { sample_title = string.Format("Sample {0}", (i + 1).ToString()); addData(level + 5 + i, 1, sample_title, "analysis"); } addData(level + 5 + sample_count, 1, "Average", "analysis"); addData(level + 6 + sample_count, 1, "Standard Dev.", "analysis"); addData(level + 8 + sample_count, 1, "Automatic", "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[level + 8 + sample_count, 1], Analysis.Cells[level + 8 + sample_count, 9]]; workSheet_range.Font.Italic = true; workSheet_range.Font.Bold = false; workSheet_range.Merge(); addData(level + 9 + sample_count, 2, "Time", "analysis"); addData(level + 9 + sample_count, 3, "Q Value Loaded (SVD)", "analysis"); addData(level + 9 + sample_count, 4, "Q Value Unloaded (SVD)", "analysis"); addData(level + 9 + sample_count, 5, "Q Value (NA)", "analysis"); addData(level + 9 + sample_count, 6, "Resonant Frequency (NA)", "analysis"); addData(level + 9 + sample_count, 7, "Resonant Frequency (SVD)", "analysis"); addData(level + 9 + sample_count, 8, "Cavity Temperature (C)", "analysis"); addData(level + 9 + sample_count, 9, "Room Temperature (C)", "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[level + 9 + sample_count, 1], Analysis.Cells[level + 9 + sample_count, 9]]; workSheet_range.Font.Bold = true; for (i = 0; i < sample_count; i++) { sample_title = string.Format("Sample {0}", (i + 1).ToString()); addData(level + 10 + sample_count + i, 1, sample_title, "analysis"); } addData(level + 10 + (2) * sample_count, 1, "Average", "analysis"); addData(level + 11 + (2) * sample_count, 1, "Standard Dev.", "analysis"); workSheet_range = Analysis.Range[Analysis.Cells[1, 1], Analysis.Cells[level + 1000 + sample_count, 1]]; workSheet_range.Font.Bold = 1; } }
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); } }
/// <summary> /// 将datatable导出excel文件 /// </summary> /// <param name="dt">需要导出的datatable</param> /// <param name="AbosultedFilePath">导出文件的绝对路径</param> /// <returns></returns> public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath) { //检查数据表是否为空,如果为空,则退出 if (dt == null) { return(false); } //创建Excel应用程序对象,如果未创建成功则退出 Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { System.Web.HttpContext.Current.Response.Write("无法创建Excel对象,可能你的电脑未装Excel"); return(false); } //创建Excel的工作簿 Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1 Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; range = (Excel.Range)worksheet.get_Range("A1", "O1"); //获取表格中第一行 range.Merge(0); //合并第一行 worksheet.Cells[1, 1] = this.selectyear.SelectedValue + "年度报销费用-部门报销费用按月度汇总"; //大标题 range.Font.Size = 22; range.Font.Bold = true; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.EntireColumn.AutoFit(); range.EntireRow.AutoFit(); //写入标题 for (int i = 0; i < dt.Columns.Count + 1; i++) { //写入标题名称 if (i == 0) { worksheet.Cells[2, i + 1] = "序号"; } else { worksheet.Cells[2, i + 1] = di[dt.Columns[i - 1].ColumnName];//从第二行的第一格开始写数据 } //设置标题的样式 range = (Excel.Range)worksheet.Cells[2, i + 1]; range.Font.Bold = true; //粗体 range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 range.Interior.ColorIndex = 15; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色 if (i == 0) //序号列宽度自动 { range.EntireColumn.AutoFit(); } else { //设置单元格的宽度,如果小于9就设置为9,如果大于。则设置为自动 if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } //写入DataTable中数据的内容 for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count + 1; c++) { range = (Excel.Range)worksheet.Cells[r + 3, c + 1]; //写入内容 if (c == 0) { if (r == dt.Rows.Count - 1) { worksheet.Cells[r + 3, c + 1] = ""; } else { worksheet.Cells[r + 3, c + 1] = (r + 1).ToString(); //得到序号 } range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中 } else if (c == 1) { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString(); } else { worksheet.Cells[r + 3, c + 1] = dt.Rows[r][c - 1].ToString().Split(',')[0]; range.NumberFormat = "#,##0.00"; } //设置样式 range.Font.Size = 9; //字体大小 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框 //设置单元格的宽度,如果小于8.5就设置为8.5,如果大于。则设置为自动 if (c == 0) //序号列宽度设为自动 { range.EntireColumn.AutoFit(); } else { if (range.EntireColumn.ColumnWidth <= 8.5) { range.EntireColumn.ColumnWidth = 8.5; } else { range.EntireColumn.AutoFit();//自动设置列宽 } } } if (r == dt.Rows.Count - 1) { range = (Excel.Range)worksheet.get_Range("A" + (r + 3).ToString(), "B" + (r + 3).ToString()); range.Merge(0); range.Value = "合计"; range.Font.Size = 9; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中 range.EntireColumn.AutoFit(); } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } try { workbook.Saved = true; workbook.SaveCopyAs(AbosultedFilePath); } catch (Exception ex) { System.Web.HttpContext.Current.Response.Write("导出文件时出错,文件可能正被打开!\n" + ex.ToString()); return(false); } workbook.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killID = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killID = m; } } if (excelProc[killID].HasExited == false) { excelProc[killID].Kill(); } #endregion return(true); }
public void MergeExcel(string FileName, C1.Win.C1FlexGrid.C1FlexGrid fg) { try { oXL = new Excel.Application(); oWB = (Excel._Workbook)(oXL.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet.UsedRange.Cells.Borders.Value = 1; // Thực hiện Merge C1.Win.C1FlexGrid.CellRange cr; object obj; for (int i = 0; i < fg.Rows.Count; i++) { if (fg.Rows[i].Visible) { for (int j = 0; j < fg.Cols.Count; j++) { if (fg.Cols[j].Visible) { cr = fg.GetMergedRange(i, j); if ((!cr.IsSingleCell && (cr.r1 == i)) && (cr.c1 == j)) { oRng = oSheet.get_Range(TinhToaDo(cr.r1 + 1, cr.c1 + 1), TinhToaDo(cr.r2 + 1, cr.c2 + 1)); obj = fg[i, j]; oRng.Value2 = string.Empty; oRng.Merge(Missing.Value); oRng.Value2 = ReturnValue(obj); } } } } } // Xóa các cột, hàng đã bị ẩn for (int i = oSheet.UsedRange.Rows.Count - 1; i > 0; i--) { oRng = oSheet.get_Range("A" + i.ToString(), Missing.Value); if ((bool)oRng.EntireRow.Hidden == true) { oRng.EntireRow.Delete(Excel.XlDirection.xlToRight); } } for (int i = oSheet.UsedRange.Columns.Count - 1; i > 0; i--) { oRng = oSheet.get_Range(TinhToaDo(1, i), Missing.Value); if ((bool)oRng.EntireColumn.Hidden == true) { oRng.EntireColumn.Delete(Excel.XlDirection.xlDown); } } oWB.Save(); oXL.Visible = true; } catch (Exception ex) { //oXL.Quit(); throw ex; } }