elcel操作

最新推荐文章于 2024-09-14 10:43:19 发布

xgy2000

最新推荐文章于 2024-09-14 10:43:19 发布

阅读量2.6k

点赞数

文章标签： null excel action object asp.net string

本文链接：https://blog.csdn.net/xgy2000/article/details/436001

版权

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Jobsinfo;
using GHProject.EntityDAO;
using GHProject.EntityDataManagement;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;
using System.Diagnostics;
namespace GHProject.Web.Report
{
/// <summary>
/// DepartAction 的摘要说明。
/// </summary>
public class DepartAction : System.Web.UI.Page
{
  // Excel object references.
  private Excel.Application m_objExcel = null;
  private Excel.Workbooks m_objBooks = null;
  private Excel._Workbook m_objBook = null;
  private Excel.Sheets m_objSheets = null;
  private Excel._Worksheet m_objSheet = null;
  private Excel.Range m_objRange = null;
  private object m_objOpt = System.Reflection.Missing.Value;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面

   string strFilePath=Server.MapPath("");
   string strTemplate=strFilePath+" //DepartAction.xls";
   // Start a new workbook in Excel.
   m_objExcel = new Excel.Application();
   m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
   m_objBook = (Excel._Workbook)(m_objBooks.Open(strTemplate,0,true,5,"","",false,Excel.XlPlatform.xlWindows,true,true,false,0,true,false,false));

   // Add data to cells in the first worksheet in the new workbook.
   m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
   m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

//插入数值
InsertCells();

   //如果存在先删除
   if(System.IO.File.Exists(strFilePath + " //Report.xls"))
   {
    System.IO.File.Delete(strFilePath + " //Report.xls");
   }

   // Save the Workbook and quit Excel.
   m_objBook.SaveAs(strFilePath + " //Report.xls",m_objOpt, "", "",
    false,false, Excel.XlSaveAsAccessMode.xlShared,
    m_objOpt, m_objOpt,m_objOpt, m_objOpt,m_objOpt);

   //杀死所有excel进程
   m_objBook.Close(false, m_objOpt, m_objOpt);
   m_objExcel.Quit();
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
   m_objRange = null;
   m_objSheet = null;
   m_objSheets = null;
   m_objBooks = null;
   m_objBook = null;
   m_objExcel = null;
   GC.Collect();
   stopExcel();
   Response.Redirect("Report.xls",true);

}

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.Load += new System.EventHandler(this.Page_Load);

  }
  #endregion
  public void stopExcel()
  {
   Process[] pProcess;
   pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
   foreach(Process pPro in pProcess)
   {
    pPro.Kill();
   }
  }
  private void InsertCells()
  {
   EntityData entity=WritManagement.GetWritByNO("2004_2","Depart_Action_T","DepartAction","SelectByQuarter");
   if(entity.Tables[0].Rows.Count==0)
   {
   }
   else
   {
    for(int i=0;i<entity.Tables[0].Rows.Count;i++)
    {
     DataRow row=entity.GetRecord("Depart_Action_T",i);
     if(Convert.ToInt32(row["ItemID"])==1)
     {
      m_objRange = m_objSheet.get_Range("C13", m_objOpt);
      m_objRange.set_Value(m_objOpt,row["PieceCount"]);
     }
     if(Convert.ToInt32(row["ItemID"])==2)
     {
      m_objRange = m_objSheet.get_Range("D13", m_objOpt);
      m_objRange.set_Value(m_objOpt,row["PieceCount"]);
      m_objRange = m_objSheet.get_Range("E13", m_objOpt);
      m_objRange.set_Value(m_objOpt,row["Money"]);
     }

    }
    DataRow row1=entity.GetRecord("Depart_Action_T");
    m_objRange.set_Value(m_objOpt,row1["CheckPerson"]);
    m_objRange = m_objSheet.get_Range("M23", m_objOpt);
    m_objRange.set_Value(m_objOpt,Convert.ToDateTime(row1["ReportTime"]).ToShortDateString());
    m_objRange = m_objSheet.get_Range("J23", m_objOpt);
    m_objRange.set_Value(m_objOpt,row1["Telephone"]);
   }

}
}
}