excel 操作

[jzz870519]

对一个一存在的excel进行修改

我要对这个excel的指定的工作表进行修改

求代码 应该不难

希望哪位能给写个代码

别从网上找代码啊 我看的都头疼了 还是不会啊

还有就是要在指定位置添加一个新的行
最好是能和另外一行样式相同 类似复制那一行

[十八道胡同]

using System;   
using System.Collections.Generic;   
using System.Text;   
using System.Data;   
using System.Data.OleDb;   
using System.Data.SqlClient;   
using System.IO;   
using Microsoft.Office.Interop.Excel;   
namespace TestAccess   
{   
    class Program   
    {   
        static void Main(string[] args)   
        {   
  
            string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";   
            strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";   
            strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";   
            OleDbConnection objConnection = new OleDbConnection(strConnection);   
            objConnection.Open();   
            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);   
            DataSet ds = new DataSet();   
            myCommandd.Fill(ds, "[Sheet1$]");   
            System.Data.DataTable dt = ds.Tables["[Sheet1$]"];   
            Console.WriteLine(dt.Columns[0].ToString());   
            Console.WriteLine(dt.Columns[1].ToString());   
            DataRow drDisplay = dt.Rows[0];   
            int[] num = new int[dt.Columns.Count];   
            for (int j = 0; ; )   
            {   
                for (int i = 0; i < dt.Columns.Count; i++)   
                {   
  
                    if (drDisplay[i] is DBNull) ;   
                    else  
                        num[i] += Convert.ToInt32(drDisplay[i]);   
  
                }   
                if (++j >= dt.Rows.Count) break;   
                drDisplay = dt.Rows[j];   
            }   
            objConnection.Close();   
            object MissingValue = Type.Missing;   
            Microsoft.Office.Interop.Excel.Application app = new Application();   
            Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue, MissingValue, MissingValue,   
MissingValue);   
            Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;   
            for (int i = 0; i < dt.Columns.Count; i++)   
            {   
                //注意下面是i+1,,excel小标默认从1开始   
                wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();   
            }   
  
            wbook.Save();   
            wbook.Close(true, null, null);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);   
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);   
            wsheet = null;   
            wbook = null;   
            app = null;   
            GC.Collect();   
        }   
    }   
}  


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx

[teerhu]

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 System.Text ;
using System.IO ;

    public partial class ToExcel : System.Web.UI.Page
	{
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// 在這裡放置使用者程式碼以初始化網頁
            if (Session["Data"] == null && Session["File"]==null )
            {
                Page.Response.Redirect("default.aspx");
            }

            string filename = Session["File"].ToString();
            System.Text.StringBuilder sb_Extend = new StringBuilder();
            switch (filename.ToLower())
            {
                case "report.xls":                    
                    DataTable dt = (DataTable)Session["Data"];
                    //dt.Columns.Remove(dt.Columns[0]);
                    //dt.Columns.Remove(dt.Columns[14]);           
                    //dt.Columns.Remove(dt.Columns[9]);
                    int r = dt.Rows.Count;
                    int c = dt.Columns.Count;
                    int i, j, k;
                    
                    //給表頭添加背景色
                    for (k = 0; k < c; k++)
                    {
                        sb_Extend.AppendLine("xls.sheets(1).Cells(1," + (k + 1) + ")=\"" + dt.Columns[k].ColumnName.Trim() + "\"\r\n");
                        sb_Extend.AppendLine("xls.sheets(1).Cells(1," + (k + 1) + ").Interior.ColorIndex = 6 \r\n");
                    }

                    //將數據加入EXCEL
                    for (i = 0; i < r; i++)
                    {
                        for (j = 0; j < c; j++)
                            sb_Extend.AppendLine("xls.Sheets(1).Cells(" + (i + 2) + "," + (j + 1) + ")=\"" + dt.Rows[i][j].ToString().Trim() + "\"\r\n");
                    }

                    sb_Extend.AppendLine("xls.sheets(1).Cells.Font.Size = 13\r\n");//將字體大小設置為13磅
                    sb_Extend.AppendLine("xls.sheets(1).Cells.EntireColumn.AutoFit\r\n");//設置單元格大小根據內容自動調整
                    break;
                case "sample.xls":
                //case "sample2.xls":
                    sb_Extend.AppendLine("\r\n");
                    break;
                default:
                    sb_Extend.AppendLine("\r\n");
                    break;
            }
            try
            {
                this.Report.Value = filename;
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                sb.AppendLine("<Script Language=VBScript>");
                sb.AppendLine(" <!--\r\n");
                sb.AppendLine("Sub CreateExcel \r\n");
                sb.AppendLine("Dim xls\r\n");
                sb.AppendLine("Set xls=CreateObject(\"Excel.Application\")\r\n");
                sb.AppendLine("xls.Workbooks.Open(\"\\\\" + System.Configuration.ConfigurationManager.AppSettings["ExlAddr"].Trim().ToString() +filename+"\")\r\n");
                sb.AppendLine("xls.Sheets(1).Select\r\n");
                sb.AppendLine(sb_Extend.ToString());
                sb.AppendLine("xls.ActiveWorkbook.SaveAs(\"C:\\TEMP\\"+filename.Split('.').GetValue(0).ToString().Trim()+"\")\r\n");
                sb.AppendLine("xls.ActiveWorkbook.Saved=true\n");

                sb.AppendLine("xls.Quit\r\n");
                sb.AppendLine("set xls=nothing\r\n");
                sb.AppendLine("End Sub\r\n");
                sb.AppendLine("-->");

                sb.AppendLine("</script>");
                Page.RegisterClientScriptBlock("", sb.ToString());
                }
                catch
                {
                   
                } 
	 }

	}

[teerhu]

用VBS方法,將VBA宏寫入頁面,然後在客戶端執行

[jzz870519]

不想用这种类似数据库连接的方式

有没有别的办法啊