C#操作Excel（导入导出）

来源：互联网发布：部落冲突知乎编辑：程序博客网时间：2024/05/01 19:40

    /// <summary>
      /// 创建excel文档
      /// </summary>
      /// <param name="Path"></param>

        public void CreateExcel(string Path)
        {
            try
            {
                 ///定义一个Missing值,这样我们用不到的设定值就可以用Missing来代替了.
                  object m_objOpt = System.Reflection.Missing.Value;
                  ///建立新的Excel应用
                  Excel.Application    m_objExcel = new Excel.Application();　
　                  ///获取应用中的所有数据表
                  Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;　　
                   ///新建数据表
                  Excel._Workbook m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));　　
                   ///获取数据表中所有工作表
                  Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;　　
                   ///选取第一个工作表
                  Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));　　
                    ///命名工作表为"原始数据表"
                  //m_objSheet.Name="原始数据表";　　
                  ///定义一个object数组
　                object[] objTitle = {"地区","统计时间", "成功条数", "失败条数"};
                    ///获得表格区域,区域从A1开始
　　               Excel.Range m_objRange = m_objSheet.get_Range("A1", m_objOpt);　
　                  ///定义表格区域为1行,"objTitle的个数"列
                   m_objRange = m_objRange.get_Resize(1,objTitle.Length);　
　                  ///将objTitle写入表格区域中,Excel会自动对齐的呵呵,放心
                   m_objRange.set_Value(m_objOpt,objTitle);　　

                 ///保存Excel文件
　　                m_objBook.SaveAs(Path, m_objOpt, m_objOpt,
                                m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                                m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);　　
                    ///关闭数据表
　　                m_objBook.Close(false, m_objOpt, m_objOpt);
                  ///关闭Excel应用
　　                m_objExcel.Quit();
                  listinfo.Invoke(bindListViewDelegate, new object[] { "创建数据表：" + Path+"成功！"});
                  writelog(DateTime.Now.ToString() + "创建数据表：" + Path + "成功！");
            }
            catch (Exception ex)
            {
                listinfo.Invoke(bindListViewDelegate, new object[] { "创建Excel发生错误：" + ex.Message });
                writelog(DateTime.Now.ToString() + "创建数据表：" + Path + "异常：" + ex.Message);
            }

        }
        /// <summary>
        /// 写入Excel文档
        /// </summary>
        /// <param name="Path">文件名称</param>
        public void SavetoExcel(string Path,DataSet dsexcel)
        {
            try
            {
                if (dsexcel == null)
                    return ;
                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;

                for (int i = 0; i < dsexcel.Tables[0].Rows.Count; i++)
                {

                    cmd.CommandText = "INSERT INTO [sheet1$] VALUES('" + dsexcel.Tables[0].Rows[i][0].ToString() + "','" +
                         dsexcel.Tables[0].Rows[i][1].ToString() + "','" + dsexcel.Tables[0].Rows[i][2].ToString() +
                         "','" + dsexcel.Tables[0].Rows[i][3].ToString() + "')";
                    cmd.ExecuteNonQuery();

                }
                dsexcel.Clear();
                dsexcel.Dispose();
                conn.Close();
                listinfo.Invoke(bindListViewDelegate, new object[] { "写入Excel文件"+ Path +"成功：" });
                writelog(DateTime.Now.ToString() + "写入Excel文件：" + Path + "成功！");
            }
            catch (Exception ex)
            {
                listinfo.Invoke(bindListViewDelegate, new object[] { "写入Excel发生错误：" + ex.Message });
                writelog(DateTime.Now.ToString() + "写入Excel：" + Path + "异常：" + ex.Message);
            }

        }

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