DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon)

在前一篇文章中，介绍了DataSet导出到Excel时客户端生成文件的几种思路，接着往下说，服务器端生成文件，用户直接下载，应该格式是可以保证的！

于是直接调用Excel的API生成。代码如下：

DataSetToLocalExcel
 public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteOldFile)
        {
            if (deleteOldFile)
            {
                if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
            }
            // Create the Excel Application object
            ApplicationClass excelApp = new ApplicationClass();

            // Create a new Excel Workbook
            Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

            int sheetIndex = 0;

            // Copy each DataTable
            foreach (System.Data.DataTable dt in dataSet.Tables)
            {

                // Copy the DataTable to an object array
                object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                // Copy the column names to the first row of the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rawData[0, col] = dt.Columns[col].ColumnName;
                }

                // Copy the values to the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    for (int row = 0; row < dt.Rows.Count; row++)
                    {
                        rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                    }
                }

                // Calculate the final column letter
                string finalColLetter = string.Empty;
                string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                int colCharsetLen = colCharset.Length;

                if (dt.Columns.Count > colCharsetLen)
                {
                    finalColLetter = colCharset.Substring(
                        (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                }

                finalColLetter += colCharset.Substring(
                        (dt.Columns.Count - 1) % colCharsetLen, 1);

                // Create a new Sheet
                Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
                    excelWorkbook.Sheets.get_Item(++sheetIndex),
                    Type.Missing, 1, XlSheetType.xlWorksheet);

                excelSheet.Name = dt.TableName;

                // Fast data export to Excel
                string excelRange = string.Format("A1:{0}{1}",
                    finalColLetter, dt.Rows.Count + 1);

                excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                // Mark the first row as BOLD
                ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
            }
            //excelApp.Application.AlertBeforeOverwriting = false;
            excelApp.Application.DisplayAlerts = false;
            // Save and Close the Workbook
            excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excelWorkbook.Close(true, Type.Missing, Type.Missing);
            excelWorkbook = null;

            // Release the Application object
            excelApp.Quit();
            excelApp = null;

            // Collect the unreferenced objects
            GC.Collect();
            GC.WaitForPendingFinalizers();

        }

 

说明下，其中的  xlsApp.Application.DisplayAlerts   =   false;  的作用是不显示确认对话框    

也可以逐Cell读取，那样可能会慢。本方法速度还过得去。

生成Winform代码测试没错，部署时，以为只要引用两个dll就可以了

Microsoft.Office.Interop.Excel.dll

Office.dll

那成想，问题接着来了，当在WebForm下调用时，　提示“检索   COM   类工厂中   CLSID   为   {00024500-0000-0000-C000-000000000046}   的组件时失败，原因是出现以下错误:   8000401a ”

晕！　Google下，解决方案是在服务器上安装Office,并配置DCOM权限。步骤如下：

 

Code
配置  DCOM  中  EXCEL  应用程序:
要在交互式用户帐户下设置  Office  自动化服务器，请按照下列步骤操作： 
1.  以管理员身份登录到计算机，并使用完整安装来安装（或重新安装）Office。为了实现系统的可靠性，建议您将  Office  CD-ROM  中的内容复制到本地驱动器并从此位置安装  Office。 
2.  启动要自动运行的  Office  应用程序。这会强制该应用程序进行自我注册。 
3.  运行该应用程序后，请按  Alt+F11  以加载  Microsoft  Visual  Basic  for  Applications  (VBA)  编辑器。这会强制  VBA  进行初始化。 
4.  关闭应用程序，包括  VBA。 
5.  单击开始，单击运行，然后键入  DCOMCNFG。选择要自动运行的应用程序。应用程序名称如下所示： 
Microsoft  Access  97  -  Microsoft  Access  数据库
Microsoft  Access  2000/2002  -  Microsoft  Access  应用程序
Microsoft  Excel  97/2000/2002  -  Microsoft  Excel  应用程序
Microsoft  Word  97  -  Microsoft  Word  Basic
Microsoft  Word  2000/2002  -  Microsoft  Word  文档 
单击属性打开此应用程序的属性对话框。
6.  单击安全选项卡。验证使用默认的访问权限和使用默认的启动权限已选中。 
7.  单击标识选项卡，然后选择交互式用户。 
8.  单击确定，关闭属性对话框并返回主应用程序列表对话框。 
9.  在  DCOM  配置对话框中，单击默认安全性选项卡。 
10.  单击访问权限的编辑默认值。验证访问权限中是否列出下列用户，如果没有列出，则添加这些用户： 
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
*  这些帐户仅在计算机上安装了  Internet  Information  Server  (IIS)  的情况下才存在。 
11.  确保允许每个用户访问，然后单击确定。 
12.  单击启动权限的编辑默认值。验证启动权限中是否列出下列用户，如果没有列出，则添加这些用户： 
SYSTEM
INTERACTIVE
Everyone
Administrators
IUSR_ <machinename> *
IWAM_ <machinename> *
*  这些帐户仅在计算机上安装有  IIS  的情况下才存在。 
13.  确保允许每个用户访问，然后单击确定。 
14.  单击确定关闭  DCOMCNFG。 
如果你之前起用了身份模拟  (在  web.config  中配置了  <identity  impersonate= "true "/>  )  ,需要删除之! 
15.更新安装office，把.net可编程组件安装到本机（excel组件）
如果还是不行.干脃把交互式用户 换成"启动用户" 

 
折腾了一番，总算可以用了！·只是服务器上装Office总感觉不爽，于是再尝试下别的方法：

Reading and Writing Excel using OLEDB

主要的类文件如下：

ExcelReaderClass
 /**//// <summary>
    /// Summary description for ExcelReader.
    /// </summary>
    public class ExcelReader : IDisposable
    {
        Variables#region Variables
        private int[] _PKCol;
        private string _strExcelFilename;
        private bool _blnMixedData = true;
        private bool _blnHeaders = false;
        private string _strSheetName;
        private string _strSheetRange;
        private bool _blnKeepConnectionOpen = false;
        private OleDbConnection _oleConn;
        private OleDbCommand _oleCmdSelect;
        private OleDbCommand _oleCmdUpdate;
        #endregion

        properties#region properties

        public int[] PKCols
        {
            get { return _PKCol; }
            set { _PKCol = value; }
        }

        public string ColName(int intCol)
        {
            string sColName = "";
            if (intCol < 26)
                sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToChar((Convert.ToByte((char)'A') + intCol)));
            else
            {
                int intFirst = ((int)intCol / 26);
                int intSecond = ((int)intCol % 26);
                sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intFirst);
                sColName += Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intSecond);
            }
            return sColName;
        }

        public int ColNumber(string strCol)
        {
            strCol = strCol.ToUpper();
            int intColNumber = 0;
            if (strCol.Length > 1)
            {
                intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 65);
                intColNumber += Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 64) * 26;
            }
            else
                intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[0]) - 65);
            return intColNumber;
        }



        public String[] GetExcelSheetNames()
        {

            System.Data.DataTable dt = null;

            try
            {
                if (_oleConn == null) Open();

                // Get the data table containing the schema
                dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null) { return null; }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    string strSheetTableName = row["TABLE_NAME"].ToString();
                    excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
                    i++;
                }
                return excelSheets;
            }
            catch (Exception ex)//tony 2008.12.31 update
            {
                string s = ex.Message; return null;
            }
            finally
            {
                // Clean up.
                if (this.KeepConnectionOpen == false)
                {
                    this.Close();
                }
                if (dt != null)
                {
                    dt.Dispose();
                    dt = null;
                }
            }
        }

        public string ExcelFilename
        {
            get { return _strExcelFilename; }
            set { _strExcelFilename = value; }
        }

        public string SheetName
        {
            get { return _strSheetName; }
            set { _strSheetName = value; }
        }

        public string SheetRange
        {
            get { return _strSheetRange; }
            set
            {
                if (value.IndexOf(":") == -1) throw new Exception("Invalid range length");
                _strSheetRange = value;
            }
        }

        public bool KeepConnectionOpen
        {
            get { return _blnKeepConnectionOpen; }
            set { _blnKeepConnectionOpen = value; }
        }

        public bool Headers
        {
            get { return _blnHeaders; }
            set { _blnHeaders = value; }
        }

        public bool MixedData
        {
            get { return _blnMixedData; }
            set { _blnMixedData = value; }
        }
        #endregion

        Methods#region Methods



        Excel Connection#region Excel Connection
        private string ExcelConnectionOptions()
        {
            string strOpts = "";
            if (this.MixedData == true)
                strOpts += "Imex=1;";
            if (this.Headers == true)
                strOpts += "HDR=Yes;";
            else
                strOpts += "HDR=No;";
            return strOpts;
        }



        private string ExcelConnection()
        {
            return
                @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                @"Data Source=" + _strExcelFilename + ";" +
                @"Extended Properties=" + Convert.ToChar(34).ToString() +
                @"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).ToString();
        }
        #endregion


        Open / Close#region Open / Close
        public void Open()
        {
            try
            {
                if (_oleConn != null)
                {
                    if (_oleConn.State == ConnectionState.Open)
                    {
                        _oleConn.Close();
                    }
                    _oleConn = null;
                }

                if (System.IO.File.Exists(_strExcelFilename) == false)
                {
                    throw new Exception("Excel file " + _strExcelFilename + "could not be found.");
                }
                _oleConn = new OleDbConnection(ExcelConnection());
                _oleConn.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Close()
        {
            if (_oleConn != null)
            {
                if (_oleConn.State != ConnectionState.Closed)
                    _oleConn.Close();
                _oleConn.Dispose();
                _oleConn = null;
            }
        }
        #endregion

        Command Select#region Command Select
        private bool SetSheetQuerySelect()
        {
            try
            {
                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed.");
                }

                if (_strSheetName.Length == 0)
                    throw new Exception("Sheetname was not assigned.");
                /**//*
                                string tmpStr=@"SELECT * FROM [" 
                                    + _strSheetName 
                                    + "$" + _strSheetRange
                                    + "]";
                */
                //System.Windows.Forms.MessageBox.Show(tmpStr);

                //if(_strSheetName.EndsWith("$")){_strSheetName=_strSheetName.TrimEnd('$');}
                _oleCmdSelect = new OleDbCommand(
                    @"SELECT * FROM ["
                    + _strSheetName
                    + "$" //+ _strSheetRange
                    + "]", _oleConn);
                //me

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        #endregion

        simple utilities#region simple utilities
        private string AddWithComma(string strSource, string strAdd)
        {
            if (strSource != "") strSource = strSource += ", ";
            return strSource + strAdd;
        }

        private string AddWithAnd(string strSource, string strAdd)
        {
            if (strSource != "") strSource = strSource += " and ";
            return strSource + strAdd;
        }
        #endregion

        private OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)
        {
            // Deleting in Excel workbook is not possible
            //So this command is not defined
            try
            {

                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed.");
                }


                if (_strSheetName.Length == 0)
                    throw new Exception("Sheetname was not assigned.");

                if (PKCols == null)
                    throw new Exception("Cannot update excel sheet with no primarykey set.");
                if (PKCols.Length < 1)
                    throw new Exception("Cannot update excel sheet with no primarykey set.");

                OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);
                string strUpdate = "";
                string strInsertPar = "";
                string strInsert = "";
                string strWhere = "";


                for (int iPK = 0; iPK < PKCols.Length; iPK++)
                {
                    strWhere = AddWithAnd(strWhere, dt.Columns[iPK].ColumnName + "=?");
                }
                strWhere = " Where " + strWhere;

                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
                {
                    strInsert = AddWithComma(strInsert, dt.Columns[iCol].ColumnName);
                    strInsertPar = AddWithComma(strInsertPar, "?");
                    strUpdate = AddWithComma(strUpdate, dt.Columns[iCol].ColumnName) + "=?";
                }

                string strTable = "[" + this.SheetName + "$" + this.SheetRange + "]";
                strInsert = "INSERT INTO " + strTable + "(" + strInsert + ") Values (" + strInsertPar + ")";
                strUpdate = "Update " + strTable + " Set " + strUpdate + strWhere;


                oleda.InsertCommand = new OleDbCommand(strInsert, _oleConn);
                oleda.UpdateCommand = new OleDbCommand(strUpdate, _oleConn);
                OleDbParameter oleParIns = null;
                OleDbParameter oleParUpd = null;
                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
                {
                    oleParIns = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
                    oleParUpd = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
                    oleParIns.SourceColumn = dt.Columns[iCol].ColumnName;
                    oleParUpd.SourceColumn = dt.Columns[iCol].ColumnName;
                    oleda.InsertCommand.Parameters.Add(oleParIns);
                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
                    oleParIns = null;
                    oleParUpd = null;
                }

                for (int iPK = 0; iPK < PKCols.Length; iPK++)
                {
                    oleParUpd = new OleDbParameter("?", dt.Columns[iPK].DataType.ToString());
                    oleParUpd.SourceColumn = dt.Columns[iPK].ColumnName;
                    oleParUpd.SourceVersion = DataRowVersion.Original;
                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
                }
                return oleda;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        command Singe Value Update#region command Singe Value Update
        private bool SetSheetQuerySingelValUpdate(string strVal)
        {
            try
            {
                if (_oleConn == null)
                {
                    throw new Exception("Connection is unassigned or closed.");
                }

                if (_strSheetName.Length == 0)
                    throw new Exception("Sheetname was not assigned.");

                _oleCmdUpdate = new OleDbCommand(
                    @" Update ["
                    + _strSheetName
                    + "$" + _strSheetRange
                    + "] set F1=" + strVal, _oleConn);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        #endregion



        public void SetPrimaryKey(int intCol)
        {
            _PKCol = new int[1] { intCol };
        }

        public DataTable GetTable()
        {
            return GetTable("ExcelTable");
        }

        private void SetPrimaryKey(DataTable dt)
        {
            try
            {
                if (PKCols != null)
                {
                    //set the primary key
                    if (PKCols.Length > 0)
                    {
                        DataColumn[] dc;
                        dc = new DataColumn[PKCols.Length];
                        for (int i = 0; i < PKCols.Length; i++)
                        {
                            dc[i] = dt.Columns[PKCols[i]];
                        }


                        dt.PrimaryKey = dc;

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetTable(string strTableName)
        {
            try
            {
                //Open and query
               if (_oleConn == null)  Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection cannot open error.");
                if (SetSheetQuerySelect() == false) return null;

                //Fill table
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
                oleAdapter.SelectCommand = _oleCmdSelect;
                DataTable dt = new DataTable(strTableName);
                oleAdapter.FillSchema(dt, SchemaType.Source);
                oleAdapter.Fill(dt);
                if (this.Headers == false)
                {
                    if (_strSheetRange.IndexOf(":") > 0)
                    {
                        string FirstCol = _strSheetRange.Substring(0, _strSheetRange.IndexOf(":") - 1);
                        int intCol = this.ColNumber(FirstCol);
                        for (int intI = 0; intI < dt.Columns.Count; intI++)
                        {
                            dt.Columns[intI].Caption = ColName(intCol + intI);
                        }
                    }
                }
                SetPrimaryKey(dt);
                //Cannot delete rows in Excel workbook
                dt.DefaultView.AllowDelete = false;

                //Clean up
                _oleCmdSelect.Dispose();
                _oleCmdSelect = null;
                oleAdapter.Dispose();
                oleAdapter = null;
                if (KeepConnectionOpen == false) Close();
                return dt;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        private void CheckPKExists(DataTable dt)
        {
            if (dt.PrimaryKey.Length == 0)
                if (this.PKCols != null)
                {
                    SetPrimaryKey(dt);
                }
                else
                    throw new Exception("Provide an primary key to the datatable");
        }
        public DataTable SetTable(DataTable dt)
        {
            try
            {
                DataTable dtChanges = dt.GetChanges();
                if (dtChanges == null) throw new Exception("There are no changes to be saved!");
                CheckPKExists(dt);
                //Open and query
                if (_oleConn == null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection cannot open error.");
                if (SetSheetQuerySelect() == false) return null;

                //Fill table
                OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(dtChanges);

                oleAdapter.Update(dtChanges);
                //Clean up
                _oleCmdSelect.Dispose();
                _oleCmdSelect = null;
                oleAdapter.Dispose();
                oleAdapter = null;
                if (KeepConnectionOpen == false) Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        Get/Set Single Value#region Get/Set Single Value

        public void SetSingleCellRange(string strCell)
        {
            _strSheetRange = strCell + ":" + strCell;
        }

        public object GetValue(string strCell)
        {
            SetSingleCellRange(strCell);
            object objValue = null;
            //Open and query
            if (_oleConn == null) Open();
            if (_oleConn.State != ConnectionState.Open)
                throw new Exception("Connection is not open error.");

            if (SetSheetQuerySelect() == false) return null;
            objValue = _oleCmdSelect.ExecuteScalar();

            _oleCmdSelect.Dispose();
            _oleCmdSelect = null;
            if (KeepConnectionOpen == false) Close();
            return objValue;
        }

        public void SetValue(string strCell, object objValue)
        {

            try
            {

                SetSingleCellRange(strCell);
                //Open and query
                if (_oleConn == null) Open();
                if (_oleConn.State != ConnectionState.Open)
                    throw new Exception("Connection is not open error.");

                if (SetSheetQuerySingelValUpdate(objValue.ToString()) == false) return;
                objValue = _oleCmdUpdate.ExecuteNonQuery();

                _oleCmdUpdate.Dispose();
                _oleCmdUpdate = null;
                if (KeepConnectionOpen == false) Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_oleCmdUpdate != null)
                {
                    _oleCmdUpdate.Dispose();
                    _oleCmdUpdate = null;
                }
            }

        }
        #endregion


        #endregion

        public

        Dispose / Destructor#region Dispose / Destructor
 void Dispose()
        {
            if (_oleConn != null)
            {
                _oleConn.Dispose();
                _oleConn = null;
            }
            if (_oleCmdSelect != null)
            {
                _oleCmdSelect.Dispose();
                _oleCmdSelect = null;
            }
            // Dispose of remaining objects.
        }
        #endregion

        CTOR#region CTOR
        public ExcelReader()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        #endregion
    }

思路：通过读出Excel模板文件到DataTale,再把数据填充到DataTable,文件另存下就OK了！

 调用代码如下：

DataSetToLocalExcel
 public static string path = @"TempExcel\STemp.xls";
        public static string path2 = "TestUser.xls";
        public static string PreFilePath = @"C:\Excel\";
        public static void DataSetToLocalExcel(DataSet ds, string srcPath, string outputPath, bool deleteOldFile)
        {
            if (ds == null || ds.Tables[0] == null && ds.Tables[0].Rows.Count == 0) { return; }
            if (deleteOldFile)
            {
                if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
            }
            System.IO.File.Copy(srcPath, outputPath, true);
            ExcelReader exr = new ExcelReader();
            exr.ExcelFilename = outputPath;
            exr.Headers = true;
            exr.MixedData = true;
            exr.KeepConnectionOpen = true;
            string[] sheetnames = exr.GetExcelSheetNames();
            exr.SheetName = sheetnames[0];
            DataTable dt = exr.GetTable();
            if (dt == null) return;
            exr.SetPrimaryKey(0);
            //dt.PrimaryKey = new DataColumn[] { dt.Columns["编号"] };
            DataTable dt2 = ds.Tables[0].Copy();
            dt.Rows.Clear();
            for (int i = 0; i < dt2.Rows.Count; i++)
            { // Copy the values to the object array
                DataRow dr = dt.NewRow();
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    dr[col] = dt2.Rows[i][col];
                }
                dt.Rows.Add(dr);
            }
            exr.SetTable(dt);
            WriteFile#region WriteFile
           
            #endregion
            exr.Close();
            exr.Dispose();
            exr = null;
        }
        private DataSet Get_AllPrices()
        {
            try
            {
                // Get the employee details
                string strSql = "SELECT [CustomID] as 编号,[C_Name] as 品名,0 as 最高价格,0 as 最低价格,0 as 平均价格,'元/公斤' as 计量单位,'' as 备注  FROM [PriceCategory] WHERE ( 1=1 AND ([Puser] = 'tuser') )";
                SqlConnection objConn = new SqlConnection(@"Data Source=AP6;Initial Catalog=testdb2009　;Persist Security Info=True;User ID=sa;Password=sa");
                SqlDataAdapter daEmp = new SqlDataAdapter(strSql, objConn);
                daEmp.Fill(dsPrice, "price");
                return dsPrice;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        DataSet dsPrice = new DataSet();
        protected void btnGetData_Click(object sender, EventArgs e)
        {
            DataSetToLocalExcel(Get_AllPrices(), PreFilePath + path, PreFilePath + path2, true);
        }

 

这里有点强调下：OleDbConnection特别要注意，　刚开始用http://www.connectionstrings.com/excel

  提供的标准串：

 

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:"MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

结果提示：“操作必须使用一个可更新的查询”。因为读取结果正常，以为是excel没有写权限所致，增加了相应权限后，结果依然如故。这下火了！　Google下，　有解决方案

http://www.cnblogs.com/richinger/archive/2008/09/28/1301170.html

A： HDR ( HeaDer Row )设置
    若指定值为Yes，代表 Excel 档中的工作表第一行是栏位名称

    若指定值為 No，代表 Excel 档中的工作表第一行就是資料了，沒有栏位名称

    B：IMEX ( IMport EXport mode )设置

     IMEX 有三种模式，各自引起的读写行为也不同，容後再述：
     0 is Export mode
     1 is Import mode
     2 is Linked mode (full update capabilities)

    

于是修改为：

Code
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:"MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=2";

 附上两个方法：StringParse和ShortParse

代码

   #region  String

         public   static   string  EmptyString  =   string .Empty;
         public   static   string  StringParse( string  old)
        {  return  StringParse(old,  string .Empty); }
         public   static   string  StringParse( object  old)
        {  return  StringParse(old,  string .Empty); }
         public   static   string  StringParse( object  old,  string  ReplaceString)
        {
             if  (old  ==   null   ||  old.ToString().Trim().Length  ==   0 )
            {
                 if  (ReplaceString  ==   null   ||  ReplaceString.Trim().Length  ==   0 ) {  return   string .Empty; }
                 else  {  return  ReplaceString.Trim(); }
            }
             else  {  return  old.ToString().Trim(); }
        }
         public   static   string  StringParse( string  old,  string  ReplaceString)
        {
             if  (old  ==   null   ||  old.Trim().Length  ==   0 )
            {
                 if  (ReplaceString  ==   null   ||  ReplaceString.Trim().Length  ==   0 ) {  return   string .Empty; }
                 else  {  return  ReplaceString.Trim(); }
            }
             else  {  return  old.Trim(); }
        }
         #endregion

  #region  Short
         public   static   short  ShortParse( string  old)
        {  return  ShortParse(old,  0 ); }
         public   static   short  ShortParse( object  old)
        {  return  ShortParse(old,  0 ); }
         public   static   short  ShortParse( string  old,  short  NullValue)
        {
             short  i  =   0 ;
             try
            {
                 if  (old  !=   null   &&  old.ToString().IndexOf( ' . ' )  >   0 )
                {
                     string  str  =  old.ToString().Remove(old.ToString().IndexOf( ' . ' ));
                    i  =   short .Parse(str.Trim());
                }
                 else  { i  =   short .Parse(old.ToString().Trim()); }

            }
             catch  {  try  { i  =  NullValue; }  catch  { i  =  ( short ) 0 ; } }
             return  i;
        }
         public   static   short  ShortParse( object  old,  short  NullValue)
        {
             short  i  =   0 ;
             try
            {
                 if  (old  !=   null   &&  old.ToString().IndexOf( ' . ' )  >   0 )
                {
                     string  str  =  old.ToString().Remove(old.ToString().IndexOf( ' . ' ));
                    i  =   short .Parse(str.Trim());
                }
                 else  { i  =   short .Parse(old.ToString().Trim()); }
            }
             catch  {  try  { i  =  NullValue; }  catch  { i  =  ( short ) 0 ; } }
             return  i;
        }
         public   static   short  ShortTryParse( object  srcObj)
        {
             short  defaultValue;
             if  (srcObj  ==   null ) {  return   0 ; }
            Int16.TryParse(srcObj.ToString(),  out  defaultValue);
             return  defaultValue;
        }
         public   static   short  ShortTryParse( object  srcObj,  short  NullValue)
        {
             short  defaultValue;
             /// /if (srcObj == null) { return 0; }
            Int16.TryParse(srcObj.ToString(),  out  defaultValue);
             if  ( ! Int16.TryParse(srcObj.ToString(),  out  defaultValue)) { Int16.TryParse(NullValue.ToString(),  out  defaultValue); }
             return  defaultValue;
        }
         #endregion

 

转载于:https://www.cnblogs.com/downmoon/archive/2009/01/16/1376702.html