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

最新推荐文章于 2022-01-03 10:52:43 发布
最新推荐文章于 2022-01-03 10:52:43 发布
阅读量578 收藏 1
点赞数
分类专栏： ASP.net 文章标签： excel dataset 服务器 exception string microsoft
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/faunjoe/article/details/4535259
版权

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

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

view plaincopy to clipboardprint?
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();  
       } 
 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权限。步骤如下：

view plaincopy to clipboardprint?
配置  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组件）  
如果还是不行.干脃把交互式用户 换成"启动用户"  
配置  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

主要的类文件如下：
view plaincopy to clipboardprint?
/** <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  
    } 
/** <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了！

 调用代码如下：

view plaincopy to clipboardprint?
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);  
       } 
 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)

于是修改为：

view plaincopy to clipboardprint?
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:"MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=2"; 

 

faunjoe
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Dataset导出到Excel
06-08
将Dataset导出到Excel，本程序阐述基本功能-Dataset exported to Excel, the program described the basic functions of
C# 源码 EXCEL导入到Dataset，Dataset导出到Excel
05-29
C# 源码 EXCEL导入到Dataset，Dataset导出到Excel
通过DataReader对象将数据表中的所有记录循环输出到Excel表
m0_63904022的博客
01-03 444
//reader为DataReader对象 if (reader.HasRows) { int i = 0; //循环输出DataReader对象到Excel表 while (reader.Read()) { //从Excel工作表中的第2行，第1列开始填写 for (int j = 0; j < reader.FieldCount; j++) { excelSheet.Cells[i + 2, j + 1] = reader[j.
DataSet to Excel
weixin_33778778的博客
11-26 155
private void toexcel(DataSet ds, string title) { StringBuilder sb = new StringBuilder(); Response.Clear(); Response.Buffer = false; Response...
DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon)
邀月周记
01-16 1万+
  在前一篇文章中，介绍了DataSet导出到Excel时客户端生成文件的几种思路，接着往下说，服务器端生成文件，用户直接下载，应该格式是可以保证的！于是直接调用Excel的API生成。代码如下： public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteO
C#gridview，table导出到Excel和Word
04-13
另外，考虑到性能和安全性，通常不推荐直接在服务器端生成大型文件，因为这可能导致内存消耗过大。对于大量数据，可以考虑分批导出或者使用服务器端组件，如EPPlus库来生成Excel文件，从而提高效率并降低资源消耗。 ...
asp.net(C#)超级完美的dataset生成excel控件有说明，有示例
09-05
这种方法通常用于服务器端生成报表或导出数据为Excel格式，用户可以直接下载，而无需中间存储步骤。 标签“C# DATASET EXCEL 控件”暗示了我们使用C#语言，利用特定的库或者控件来处理数据集与Excel文件的交互。在...
Gridview 导出Excel
03-04
当用户点击导出按钮时，服务器端生成Excel文件内容并将其作为HTTP响应发送给浏览器，浏览器接收到响应后会自动触发下载行为。 接下来，我们将详细讲解实现步骤： 1. 创建GridView：在ASP.NET页面中添加GridView...
GridView中导出数据到 Excel
06-11
需要注意的是，由于使用Microsoft.Office.Interop.Excel库会依赖于Office安装，因此在服务器端可能会带来兼容性和性能问题。为了解决这个问题，可以使用开源库如EPPlus，它能脱离Office环境直接生成Excel文件，性能...
ASP.NET中导入和导出标准Excel文件
09-09
总结来说，通过使用ASP.NET的OleDb驱动程序，开发者可以有效地在服务器端处理Excel文件的导入和导出，而无需在服务器上安装完整的Excel应用程序。这种方法提高了系统的可移植性和效率，同时确保了数据的准确性和一致...
三种读取Excel方法
热门推荐
luyuncsd123的专栏
01-14 4万+
转自：http://blog.csdn.net/happy09li/article/details/7431967 三种读取Excel方法 ---------------------------------------------------方法一 [csharp] view plaincopyprint? ///   
异常—Cause: java.sql.SQLException: Data source is closed
娜娜的专栏
06-07 7071
报错如下： 原因如下：
导出Dataset到Excel，并保持格式。
yyj135的专栏
06-17 2254
说明：本文有参考网上的代码，部份内容为本人修改。开发环境：C# 2005 (WinForm)因项目需要，要实现将DataSet导出到Excel，同时要求： 并且操持格式不变。 WorkSheet以DataTable的Name命名。 速度快。 不用保存文件，导出后直接打开这个Excle文件即可。      （因为不保存的话，不能杀死这
Data source is closed
weixin_34129696的博客
06-01 2342
2019独角兽企业重金招聘Python工程师标准>>> ...
最新解决小程序反编译$gwx is not defined和__vd_version_info__ is not defined
bby0823的博客
08-08 1639
百度出来一堆收费教程，全是简书上的，真优秀啊。秉承知识有价，分享无价的精神，我终于用谷歌找到了众多免费教程，感谢无私分享的各位大佬们。分享有你，春暖花开。 下面是正事： 由于微信小程序升级的原因， github 上的 wxappUnpacker-master 小程序反编译工具已部分失效，运行 node wuWxapkg.js 命令时会报 $gwx is not defined 或vd_versio...
训练并导出tensorflow Lite模型中的一些问题 及解决办法
qq_41786188的博客
01-02 3949
##1.ubuntu18.04连接镜像autonist.iso 安装ubuntu或者centos时，vmware提示"快速安装，然后会使用一个autonist.iso文件来快速安装，并且装完系统重启了，它插入一个操作：安装vm-tools"。 https://www.cnblogs.com/zjutzz/p/6049328.html 断开autonist的连接出现的问题:终端中使用apt-get安...
有关java.sql.SQLException: Data source is closed
weixin_39553978的博客
05-29 1万+
问题的现象，线上的系统莫明的报错 ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Data source is closed at org.myb...
dataset导出excel
最新发布
04-02
将dataset导出为Excel文件可以使用Python中的pandas库。pandas库提供了一个名为DataFrame的数据结构，可以方便地处理和操作数据。下面是将dataset导出为Excel文件的步骤： 1. 首先，确保你已经安装了pandas库。如果...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值