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;
        }


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

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值