C# .net EXCEL

找了一个.net的工作，现在开始又可以专心研究.net了。呵呵，很开心。

对于.net操作excel在网上有很多的例子，这里写的是一个工作中的实例。

第一步，安装excel，我电脑上安装的是office2003。做好相应配置，关于如何配置网上有很多说明和资料。

第二步，添加excel组件的引用。

第三步，上面两步顺利完成后就可以编写代码了。

代码实例如下。

 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using DAL; using Model; using Excel; using System.Diagnostics; public partial class contract : pagebaseAdmin { protected kinderstatusViewModel modelkinder; protected void Page_Load(object sender, EventArgs e) { if (Request["schoolid"] != "" && Request["schoolid"] != null) { kinderstatusViewDal dalkinderstatus = new kinderstatusViewDal(); modelkinder = dalkinderstatus.GetModel(Convert.ToInt32(Request["schoolid"])); txtGardenName.Value = modelkinder.kname; } } protected void btnGenerate_Click(object sender, EventArgs e) { Excel.Application xlApp = null; Excel.Workbook xlWorkbook = null; //Excel.Worksheet xlWorksheet = null; //_Application xlApp = null; //_Workbook xlWorkbook = null; //_Worksheet xlWorksheet = null; //System.Reflection.Missing用于空参数的传参 System.Reflection.Missing oMissing = System.Reflection.Missing.Value; //string saveAsPath = ""; //Request.PhysicalApplicationPath表示获得文件的物理路径 string path = Request.PhysicalApplicationPath+"upload//contract//幼儿园网站合同.xls"; string strName = string.Format("{0}网站合同_{1}{2}.xls", txtGardenName.Value.Trim(), PIUSERID, DateTime.Now.ToString("yyyyMMddHHss")); string saveAsPath = Request.PhysicalApplicationPath + string.Format("upload/contract/{0}", strName); try { xlApp = new ApplicationClass(); xlApp.Visible = true; //xlWorkbook = xlApp.Workbooks.Add(oMissing); //xlWorksheet = xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing) as _Worksheet; //Add方法是生成一个excel文件，Open方法是打开一个已有的excel文件。 xlWorkbook = xlApp.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlPlatform.xlWindows, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //((Microsoft.Office.Interop.Excel._Workbook)xlApp.Workbooks["幼儿园网站合同"]).Activate(); //激活指定的工作簿 ((Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook.Sheets["Sheet1"]).Activate(); Excel.Worksheet sheet = (Excel.Worksheet)xlWorkbook.Sheets["Sheet1"]; string strStartDate = txtStartDate.Value; string strEndDate = txtEndDate.Value; string strGardenName = txtGardenName.Value; string strAmountUpper = txtAmountUpperCase.Value; string strAmountLower = txtAmountLowerCase.Value; string[] arrStartDate = strStartDate.Split('-'); string[] arrEndDate = strEndDate.Split('-'); string strRemark = txtRemark.Value; sheet.Cells[19, 12] = strAmountLower; sheet.Cells[20, 2] = "双方一致同意在本合同履行期（" + arrStartDate[0] + "年" + arrStartDate[1] + "月" + arrStartDate[2] + "日—" + arrEndDate[0] + "年" + arrEndDate[1] + "月" + arrEndDate[2] + "日），乙方向甲方一次性支付服务费计人民币 " + strAmountLower + " 元，该费用乙方于" + arrStartDate[0] + "年" + arrStartDate[1] + "月" + arrStartDate[2] + "日支付。"; sheet.Cells[41, 7] = strAmountLower; sheet.Cells[41, 11] = strAmountUpper; sheet.Cells[44, 3] = strRemark; sheet.Cells[48, 11] = strGardenName; sheet.Cells[50, 3] = arrStartDate[0]+"年"+arrStartDate[1]+"月"+arrStartDate[2]+"日"; sheet.Cells[50, 11] = arrEndDate[0]+"年"+arrEndDate[1]+"月"+arrEndDate[2]+"日"; //另存excel文件 xlWorkbook.SaveAs(saveAsPath, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive, oMissing, oMissing, oMissing, oMissing, oMissing); xlApp.Quit(); } finally { //因为是非托管代码，所以要自己调用垃圾回收----tengyang System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; GC.Collect(); //Dim myProcesses As Process() //杀死excel进程 Process[] myProcesses; myProcesses = Process.GetProcessesByName("EXCEL"); foreach(Process myProcess in myProcesses) { if (!myProcess.CloseMainWindow()) { myProcess.Kill(); } } } // Response.Write(); Download(strName,saveAsPath); } private void Download(string strFileName,string strFilePath){ //string strFilePath = Server.MapPath("~/makeword/") + Request.Params("Export") + ".doc"; //'Dim fi As New FileInfo(strFilePath) FileStream f = new FileStream(strFilePath, FileMode.Open); Byte []buffer=new Byte[f.Length] ; f.Read(buffer, 0, buffer.Length); f.Close(); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)); Response.BinaryWrite(buffer); Response.Flush(); Response.End(); } }

需求很简单，当点按钮的时候，向一个excel模板里的指定单元格插入数据，然后另存到本地。

代码也很简单。在此做个标记，以后有相似的需求就不用辛辛苦苦上网去一点点的找资料了，

而且看自己的代码比看别人的代码要舒服的多。

备注：1.对指定的单元格添加公式：

Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 1];
range.Formula = "=SUM(B1,B2)";