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

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

 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权限。步骤如下：

配置  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总感觉不爽，于是再尝试下别的方法：

主要的类文件如下：

 /**//// <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了！

 调用代码如下：

 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特别要注意，　刚开始用

  提供的标准串：

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

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

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)

    

于是修改为：

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

 

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。

助人等于自助!