c#写excel文件 小结

C#生成Excel文件，写，操作等等

1.
从
 //建立Excel应用(写Excel Sheet之前)
开始，可以得到写excel文件的程序。不过要得到.xls，Excel2007打开有提示，点确定后能打开。得到.xlsx文件则没问题，可以打开

注意，需要安装Office，这个方法需要项目中 add reference...->COM->Microsoft Excel 12.0 Object Library （版本可随意）
建议：using Excel = Microsoft.Office.Interop.Excel;
就可以直接用Excel这个类，不用打一长串字了


//==============================================================================
 （转自http://www.yj00.com/ArticleView.aspx?id=87）
下面列出几个C#操作Excel的几个关键点，供大家参考：
 
 //连接excel数据源
            string filename = this.openFileDialog2.FileName;
            string filePath = this.openFileDialog1.FileName;
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ filePath + ";Extended Properties=/"Excel 8.0;/"");
            conn.Open();
 
 //建立Excel应用(写Excel Sheet之前)
            Microsoft.Office.Interop.Excel.ApplicationClass oExcel;
            oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            oExcel.UserControl = false;
            Microsoft.Office.Interop.Excel.WorkbookClass wb = (Microsoft.Office.Interop.Excel.WorkbookClass)oExcel.Workbooks.Add(System.Reflection.Missing.Value);
 
//新建一个Sheet,新建后会默认处于焦点状态
wb.Worksheets.Add(Missing.Value, Missing.Value, 1, Missing.Value);
 
//写单元格示例
 
oExcel.Cells[1, 1] = "第一列";
oExcel.Cells[1, 2] = "第二列";
oExcel.Cells[1, 3] = "第三列";
 
//保存工作簿
wb.Saved = true;
 
//生成文件，释放资源
oExcel.ActiveWorkbook.SaveCopyAs(filename);
oExcel.Quit();
System.GC.Collect();

//==============================================================================




2.网上有人说用数据源的方法比较有效，现在转载一下代码：
需要：
using System.Data;
using System.Data.OleDb;


//==============================================================================
（转自http://www.xker.com/page/e2007/0116/10138.html）

        /// <summary>
        /// 读取Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        /// <returns>返回一个数据集</returns>
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }

        /// <summary>
        /// 写入Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        public bool SaveFP2toExcel(string Path)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
                //cmd.ExecuteNonQuery ();
                for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
                {
                    if (fp2.Sheets[0].Cells[i, 0].Text != "")
                    {
                        cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
                         fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
                         "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
                        cmd.ExecuteNonQuery();
                    }
                }
                conn.Close();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
            return false;
        }

//==============================================================================