C#操作Excel

本文主要讲解如何用C#创建Excel、将DataTable的数据导出到Excel中、以及将Excel中的数据导入到DataSet中。

以前看过很多这方面的文章，有的将DataTable中数据一个单元格一个单元格的放进Excel,还有的用OleDb操作Excel.我呢?不太一样,

对于导出到Excel:将DataTable的数据放到2维数组中,然后一次性导入Excel中。

对于导出到DataSet:使用OleDb，这个还是很方便的。

1、要操作Excel需要引用dll吧，如图所示

在引用中会有Microsoft.Office.Interop.Excel.dll以及Office.dll产生

 

1、创建excel

        using Excel = Microsoft.Office.Interop.Excel;

        /// <summary>
        /// ExcelApplication
        /// </summary>
        private Excel.Application _excelApplication = null;
        /// <summary>
        /// ExcelWorkbook
        /// </summary>
        private Excel._Workbook _workBook = null;
        /// <summary>
        /// ExcelWorksheet
        /// </summary>
        private Excel._Worksheet _worksheet = null;

 

        /// <summary>
        /// 生成一个空Excel文件,只有一个sheet
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public bool CreateExcelFile(string sheetname)
        {
            bool returnflag = true;
            try
            {
                Excel.Workbooks m_objBooks = (Excel.Workbooks)this._excelApplication.Workbooks;
                this._workBook = (Excel._Workbook)(m_objBooks.Add(Missing.Value));
                int totalsheet = this._workBook.Worksheets.Count;
                for (int i = 0; i < totalsheet-1; i++)
                {
                    ((Excel.Worksheet)_workBook.Worksheets[1]).Delete();//删除多余sheet,只保留一个
                }

                this._worksheet = (Excel.Worksheet)this._workBook.Worksheets[1];
                //this._workBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                if (sheetname == null || sheetname == string.Empty)
                {
                    this.ReNameSheet(_worksheet, "Sheet1");
                }
                else
                {
                    this.ReNameSheet(_worksheet, sheetname);
                }
            }
            catch (Exception ex)
            {
                CloseExcelOperator();
                returnflag = false;
            }
            return returnflag;
        }

       /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <returns></returns>
        public bool SaveExcel()
        {
            bool returnFlag = true;
                try
                {
                    _workBook.Save();
                    returnFlag = true;
                }
                catch (Exception ex)
                {
                    returnFlag = false;
                }
            return returnFlag;
        }

         /// <summary>
         /// 另存Excel文件

         /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public bool SaveExcelAs(string filePath)
        {
            bool returnFlag = true;
            try
            {

               //filePath是文件存储路径
                if (filePath == null || filePath == string.Empty || filePath == null || filePath == string.Empty)
                {
                    returnFlag = false;
                }
                else
                {
                    this._excelApplication.DisplayAlerts = false;
                    this._workBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    //this._workBook.SaveCopyAs(filePath);
                }
            }
            catch (Exception ex)
            {
                returnFlag = false;

            }
            finally
            {
                this.CloseExcelOperator();
            }
            return returnFlag;
        }

        /// <summary>
        /// 关闭Excel操作
        /// </summary>
        /// <returns></returns>
        public bool CloseExcelOperator()
        {
            bool returnFlag = true;
            try
            {
                if (_workBook != null)
                {
                    _workBook.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (_excelApplication != null)
                {
                    _excelApplication.Quit();
                }
                _workBook = null;
                _excelApplication = null;
                GC.Collect();
            }
            catch (Exception ex)
            {
                returnFlag = false;
            }
            return returnFlag;
            //excel.Workbooks[1].SaveCopyAs(FileName);//另存为filename
            //excel.Workbooks[1].Saved = true;//不让他提示你,修改之 后是否保存
            //excel.Quit();//关闭它,如果考虑进程的话要GC.Collect();

        }

        /// <summary>
        /// 设置单元格数值，将DataTable的数据放到object[,] ret数组中，然后调用该函数，将值存到object value中

        /// 用的感觉挺好
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="value"></param>
        public void SetCellValue(Excel._Worksheet ws, int Startx, int Starty, int Endx, int Endy, object value)
        //ws：要设值的工作表的名称 X行Y列 value 值
        {
            _worksheet .get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Value2 = value;
        }

        /// <summary>
        /// 采用OleDB读取EXCEL文件，将数据存放到DataSet
        /// </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;";
            //HDR=Yes把第一行当做标题，IMEX=1把数据都当成文本处理
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=/"Excel 8.0;HDR=No;IMEX=1/"";     
            OleDbConnection conn = new OleDbConnection(strConn);
            DataSet ds = null;
            try
            {
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim();//得到第一个sheet名
                //得到sheet名,取第二~九列。注：此时strConn中HDR=No
                strExcel = "select F2 as Name,F3 as LinkMan,F4 as TEL,F5 as fax,F6 as Email ,F7 as Province,F8 as city ,F9 as Street,F10 as Description from [" + tableName + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds);
                myCommand.Dispose();
            }
            catch (OleDbException ex)
            {
                CommonHandler.ShowError(ex.Message);
                ds = null;
            }
            catch (Exception ex)
            {
                CommonHandler.ShowError(ex.Message);
                ds = null;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
            return ds;
        }

    }