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

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

public static void DataSetToLocalExcel(DataSet dataSet, string outputPath, bool deleteOldFile)<br /> {<br /> if (deleteOldFile)<br /> {<br /> if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }<br /> }<br /> // Create the Excel Application object<br /> ApplicationClass excelApp = new ApplicationClass();<br /> // Create a new Excel Workbook<br /> Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);<br /> int sheetIndex = 0;<br /> // Copy each DataTable<br /> foreach (System.Data.DataTable dt in dataSet.Tables)<br /> {<br /> // Copy the DataTable to an object array<br /> object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];<br /> // Copy the column names to the first row of the object array<br /> for (int col = 0; col < dt.Columns.Count; col++)<br /> {<br /> rawData[0, col] = dt.Columns[col].ColumnName;<br /> }<br /> // Copy the values to the object array<br /> for (int col = 0; col < dt.Columns.Count; col++)<br /> {<br /> for (int row = 0; row < dt.Rows.Count; row++)<br /> {<br /> rawData[row + 1, col] = dt.Rows[row].ItemArray[col];<br /> }<br /> }<br /> // Calculate the final column letter<br /> string finalColLetter = string.Empty;<br /> string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";<br /> int colCharsetLen = colCharset.Length;<br /> if (dt.Columns.Count > colCharsetLen)<br /> {<br /> finalColLetter = colCharset.Substring(<br /> (dt.Columns.Count - 1) / colCharsetLen - 1, 1);<br /> }<br /> finalColLetter += colCharset.Substring(<br /> (dt.Columns.Count - 1) % colCharsetLen, 1);<br /> // Create a new Sheet<br /> Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(<br /> excelWorkbook.Sheets.get_Item(++sheetIndex),<br /> Type.Missing, 1, XlSheetType.xlWorksheet);<br /> excelSheet.Name = dt.TableName;<br /> // Fast data export to Excel<br /> string excelRange = string.Format("A1:{0}{1}",<br /> finalColLetter, dt.Rows.Count + 1);<br /> excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;<br /> // Mark the first row as BOLD<br /> ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;<br /> }<br /> //excelApp.Application.AlertBeforeOverwriting = false;<br /> excelApp.Application.DisplayAlerts = false;<br /> // Save and Close the Workbook<br /> excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,<br /> Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,<br /> Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);<br /> excelWorkbook.Close(true, Type.Missing, Type.Missing);<br /> excelWorkbook = null;<br /> // Release the Application object<br /> excelApp.Quit();<br /> excelApp = null;<br /> // Collect the unreferenced objects<br /> GC.Collect();<br /> GC.WaitForPendingFinalizers();<br /> }

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

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

Reading and Writing Excel using OLEDB

主要的类文件如下：

/**//// <summary><br /> /// Summary description for ExcelReader.<br /> /// </summary><br /> public class ExcelReader : IDisposable<br /> {<br /> Variables#region Variables<br /> private int[] _PKCol;<br /> private string _strExcelFilename;<br /> private bool _blnMixedData = true;<br /> private bool _blnHeaders = false;<br /> private string _strSheetName;<br /> private string _strSheetRange;<br /> private bool _blnKeepConnectionOpen = false;<br /> private OleDbConnection _oleConn;<br /> private OleDbCommand _oleCmdSelect;<br /> private OleDbCommand _oleCmdUpdate;<br /> #endregion<br /> properties#region properties<br /> public int[] PKCols<br /> {<br /> get { return _PKCol; }<br /> set { _PKCol = value; }<br /> }<br /> public string ColName(int intCol)<br /> {<br /> string sColName = "";<br /> if (intCol < 26)<br /> sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToChar((Convert.ToByte((char)'A') + intCol)));<br /> else<br /> {<br /> int intFirst = ((int)intCol / 26);<br /> int intSecond = ((int)intCol % 26);<br /> sColName = Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intFirst);<br /> sColName += Agronet.Common.Framework.Util.SQLParser.StringParse(Convert.ToByte((char)'A') + intSecond);<br /> }<br /> return sColName;<br /> }<br /> public int ColNumber(string strCol)<br /> {<br /> strCol = strCol.ToUpper();<br /> int intColNumber = 0;<br /> if (strCol.Length > 1)<br /> {<br /> intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 65);<br /> intColNumber += Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[1]) - 64) * 26;<br /> }<br /> else<br /> intColNumber = Agronet.Common.Framework.Util.SQLParser.ShortParse(Convert.ToByte(strCol[0]) - 65);<br /> return intColNumber;<br /> }<br /> public String[] GetExcelSheetNames()<br /> {<br /> System.Data.DataTable dt = null;<br /> try<br /> {<br /> if (_oleConn == null) Open();<br /> // Get the data table containing the schema<br /> dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);<br /> if (dt == null) { return null; }<br /> String[] excelSheets = new String[dt.Rows.Count];<br /> int i = 0;<br /> // Add the sheet name to the string array.<br /> foreach (DataRow row in dt.Rows)<br /> {<br /> string strSheetTableName = row["TABLE_NAME"].ToString();<br /> excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);<br /> i++;<br /> }<br /> return excelSheets;<br /> }<br /> catch (Exception ex)//tony 2008.12.31 update<br /> {<br /> string s = ex.Message; return null;<br /> }<br /> finally<br /> {<br /> // Clean up.<br /> if (this.KeepConnectionOpen == false)<br /> {<br /> this.Close();<br /> }<br /> if (dt != null)<br /> {<br /> dt.Dispose();<br /> dt = null;<br /> }<br /> }<br /> }<br /> public string ExcelFilename<br /> {<br /> get { return _strExcelFilename; }<br /> set { _strExcelFilename = value; }<br /> }<br /> public string SheetName<br /> {<br /> get { return _strSheetName; }<br /> set { _strSheetName = value; }<br /> }<br /> public string SheetRange<br /> {<br /> get { return _strSheetRange; }<br /> set<br /> {<br /> if (value.IndexOf(":") == -1) throw new Exception("Invalid range length");<br /> _strSheetRange = value;<br /> }<br /> }<br /> public bool KeepConnectionOpen<br /> {<br /> get { return _blnKeepConnectionOpen; }<br /> set { _blnKeepConnectionOpen = value; }<br /> }<br /> public bool Headers<br /> {<br /> get { return _blnHeaders; }<br /> set { _blnHeaders = value; }<br /> }<br /> public bool MixedData<br /> {<br /> get { return _blnMixedData; }<br /> set { _blnMixedData = value; }<br /> }<br /> #endregion<br /> Methods#region Methods<br /> Excel Connection#region Excel Connection<br /> private string ExcelConnectionOptions()<br /> {<br /> string strOpts = "";<br /> if (this.MixedData == true)<br /> strOpts += "Imex=1;";<br /> if (this.Headers == true)<br /> strOpts += "HDR=Yes;";<br /> else<br /> strOpts += "HDR=No;";<br /> return strOpts;<br /> }<br /> private string ExcelConnection()<br /> {<br /> return<br /> @"Provider=Microsoft.Jet.OLEDB.4.0;" +<br /> @"Data Source=" + _strExcelFilename + ";" +<br /> @"Extended Properties=" + Convert.ToChar(34).ToString() +<br /> @"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).ToString();<br /> }<br /> #endregion<br /> Open / Close#region Open / Close<br /> public void Open()<br /> {<br /> try<br /> {<br /> if (_oleConn != null)<br /> {<br /> if (_oleConn.State == ConnectionState.Open)<br /> {<br /> _oleConn.Close();<br /> }<br /> _oleConn = null;<br /> }<br /> if (System.IO.File.Exists(_strExcelFilename) == false)<br /> {<br /> throw new Exception("Excel file " + _strExcelFilename + "could not be found.");<br /> }<br /> _oleConn = new OleDbConnection(ExcelConnection());<br /> _oleConn.Open();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> public void Close()<br /> {<br /> if (_oleConn != null)<br /> {<br /> if (_oleConn.State != ConnectionState.Closed)<br /> _oleConn.Close();<br /> _oleConn.Dispose();<br /> _oleConn = null;<br /> }<br /> }<br /> #endregion<br /> Command Select#region Command Select<br /> private bool SetSheetQuerySelect()<br /> {<br /> try<br /> {<br /> if (_oleConn == null)<br /> {<br /> throw new Exception("Connection is unassigned or closed.");<br /> }<br /> if (_strSheetName.Length == 0)<br /> throw new Exception("Sheetname was not assigned.");<br /> /**//*<br /> string tmpStr=@"SELECT * FROM ["<br /> + _strSheetName<br /> + "$" + _strSheetRange<br /> + "]";<br /> */<br /> //System.Windows.Forms.MessageBox.Show(tmpStr);<br /> //if(_strSheetName.EndsWith("$")){_strSheetName=_strSheetName.TrimEnd('$');}<br /> _oleCmdSelect = new OleDbCommand(<br /> @"SELECT * FROM ["<br /> + _strSheetName<br /> + "$" //+ _strSheetRange<br /> + "]", _oleConn);<br /> //me<br /> return true;<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> #endregion<br /> simple utilities#region simple utilities<br /> private string AddWithComma(string strSource, string strAdd)<br /> {<br /> if (strSource != "") strSource = strSource += ", ";<br /> return strSource + strAdd;<br /> }<br /> private string AddWithAnd(string strSource, string strAdd)<br /> {<br /> if (strSource != "") strSource = strSource += " and ";<br /> return strSource + strAdd;<br /> }<br /> #endregion<br /> private OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)<br /> {<br /> // Deleting in Excel workbook is not possible<br /> //So this command is not defined<br /> try<br /> {<br /> if (_oleConn == null)<br /> {<br /> throw new Exception("Connection is unassigned or closed.");<br /> }<br /> if (_strSheetName.Length == 0)<br /> throw new Exception("Sheetname was not assigned.");<br /> if (PKCols == null)<br /> throw new Exception("Cannot update excel sheet with no primarykey set.");<br /> if (PKCols.Length < 1)<br /> throw new Exception("Cannot update excel sheet with no primarykey set.");<br /> OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);<br /> string strUpdate = "";<br /> string strInsertPar = "";<br /> string strInsert = "";<br /> string strWhere = "";<br /> for (int iPK = 0; iPK < PKCols.Length; iPK++)<br /> {<br /> strWhere = AddWithAnd(strWhere, dt.Columns[iPK].ColumnName + "=?");<br /> }<br /> strWhere = " Where " + strWhere;<br /> for (int iCol = 0; iCol < dt.Columns.Count; iCol++)<br /> {<br /> strInsert = AddWithComma(strInsert, dt.Columns[iCol].ColumnName);<br /> strInsertPar = AddWithComma(strInsertPar, "?");<br /> strUpdate = AddWithComma(strUpdate, dt.Columns[iCol].ColumnName) + "=?";<br /> }<br /> string strTable = "[" + this.SheetName + "$" + this.SheetRange + "]";<br /> strInsert = "INSERT INTO " + strTable + "(" + strInsert + ") Values (" + strInsertPar + ")";<br /> strUpdate = "Update " + strTable + " Set " + strUpdate + strWhere;<br /> oleda.InsertCommand = new OleDbCommand(strInsert, _oleConn);<br /> oleda.UpdateCommand = new OleDbCommand(strUpdate, _oleConn);<br /> OleDbParameter oleParIns = null;<br /> OleDbParameter oleParUpd = null;<br /> for (int iCol = 0; iCol < dt.Columns.Count; iCol++)<br /> {<br /> oleParIns = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());<br /> oleParUpd = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());<br /> oleParIns.SourceColumn = dt.Columns[iCol].ColumnName;<br /> oleParUpd.SourceColumn = dt.Columns[iCol].ColumnName;<br /> oleda.InsertCommand.Parameters.Add(oleParIns);<br /> oleda.UpdateCommand.Parameters.Add(oleParUpd);<br /> oleParIns = null;<br /> oleParUpd = null;<br /> }<br /> for (int iPK = 0; iPK < PKCols.Length; iPK++)<br /> {<br /> oleParUpd = new OleDbParameter("?", dt.Columns[iPK].DataType.ToString());<br /> oleParUpd.SourceColumn = dt.Columns[iPK].ColumnName;<br /> oleParUpd.SourceVersion = DataRowVersion.Original;<br /> oleda.UpdateCommand.Parameters.Add(oleParUpd);<br /> }<br /> return oleda;<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> command Singe Value Update#region command Singe Value Update<br /> private bool SetSheetQuerySingelValUpdate(string strVal)<br /> {<br /> try<br /> {<br /> if (_oleConn == null)<br /> {<br /> throw new Exception("Connection is unassigned or closed.");<br /> }<br /> if (_strSheetName.Length == 0)<br /> throw new Exception("Sheetname was not assigned.");<br /> _oleCmdUpdate = new OleDbCommand(<br /> @" Update ["<br /> + _strSheetName<br /> + "$" + _strSheetRange<br /> + "] set F1=" + strVal, _oleConn);<br /> return true;<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> #endregion<br /> public void SetPrimaryKey(int intCol)<br /> {<br /> _PKCol = new int[1] { intCol };<br /> }<br /> public DataTable GetTable()<br /> {<br /> return GetTable("ExcelTable");<br /> }<br /> private void SetPrimaryKey(DataTable dt)<br /> {<br /> try<br /> {<br /> if (PKCols != null)<br /> {<br /> //set the primary key<br /> if (PKCols.Length > 0)<br /> {<br /> DataColumn[] dc;<br /> dc = new DataColumn[PKCols.Length];<br /> for (int i = 0; i < PKCols.Length; i++)<br /> {<br /> dc[i] = dt.Columns[PKCols[i]];<br /> }<br /> dt.PrimaryKey = dc;<br /> }<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> public DataTable GetTable(string strTableName)<br /> {<br /> try<br /> {<br /> //Open and query<br /> if (_oleConn == null) Open();<br /> if (_oleConn.State != ConnectionState.Open)<br /> throw new Exception("Connection cannot open error.");<br /> if (SetSheetQuerySelect() == false) return null;<br /> //Fill table<br /> OleDbDataAdapter oleAdapter = new OleDbDataAdapter();<br /> oleAdapter.SelectCommand = _oleCmdSelect;<br /> DataTable dt = new DataTable(strTableName);<br /> oleAdapter.FillSchema(dt, SchemaType.Source);<br /> oleAdapter.Fill(dt);<br /> if (this.Headers == false)<br /> {<br /> if (_strSheetRange.IndexOf(":") > 0)<br /> {<br /> string FirstCol = _strSheetRange.Substring(0, _strSheetRange.IndexOf(":") - 1);<br /> int intCol = this.ColNumber(FirstCol);<br /> for (int intI = 0; intI < dt.Columns.Count; intI++)<br /> {<br /> dt.Columns[intI].Caption = ColName(intCol + intI);<br /> }<br /> }<br /> }<br /> SetPrimaryKey(dt);<br /> //Cannot delete rows in Excel workbook<br /> dt.DefaultView.AllowDelete = false;<br /> //Clean up<br /> _oleCmdSelect.Dispose();<br /> _oleCmdSelect = null;<br /> oleAdapter.Dispose();<br /> oleAdapter = null;<br /> if (KeepConnectionOpen == false) Close();<br /> return dt;<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> private void CheckPKExists(DataTable dt)<br /> {<br /> if (dt.PrimaryKey.Length == 0)<br /> if (this.PKCols != null)<br /> {<br /> SetPrimaryKey(dt);<br /> }<br /> else<br /> throw new Exception("Provide an primary key to the datatable");<br /> }<br /> public DataTable SetTable(DataTable dt)<br /> {<br /> try<br /> {<br /> DataTable dtChanges = dt.GetChanges();<br /> if (dtChanges == null) throw new Exception("There are no changes to be saved!");<br /> CheckPKExists(dt);<br /> //Open and query<br /> if (_oleConn == null) Open();<br /> if (_oleConn.State != ConnectionState.Open)<br /> throw new Exception("Connection cannot open error.");<br /> if (SetSheetQuerySelect() == false) return null;<br /> //Fill table<br /> OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(dtChanges);<br /> oleAdapter.Update(dtChanges);<br /> //Clean up<br /> _oleCmdSelect.Dispose();<br /> _oleCmdSelect = null;<br /> oleAdapter.Dispose();<br /> oleAdapter = null;<br /> if (KeepConnectionOpen == false) Close();<br /> return dt;<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> }<br /> Get/Set Single Value#region Get/Set Single Value<br /> public void SetSingleCellRange(string strCell)<br /> {<br /> _strSheetRange = strCell + ":" + strCell;<br /> }<br /> public object GetValue(string strCell)<br /> {<br /> SetSingleCellRange(strCell);<br /> object objValue = null;<br /> //Open and query<br /> if (_oleConn == null) Open();<br /> if (_oleConn.State != ConnectionState.Open)<br /> throw new Exception("Connection is not open error.");<br /> if (SetSheetQuerySelect() == false) return null;<br /> objValue = _oleCmdSelect.ExecuteScalar();<br /> _oleCmdSelect.Dispose();<br /> _oleCmdSelect = null;<br /> if (KeepConnectionOpen == false) Close();<br /> return objValue;<br /> }<br /> public void SetValue(string strCell, object objValue)<br /> {<br /> try<br /> {<br /> SetSingleCellRange(strCell);<br /> //Open and query<br /> if (_oleConn == null) Open();<br /> if (_oleConn.State != ConnectionState.Open)<br /> throw new Exception("Connection is not open error.");<br /> if (SetSheetQuerySingelValUpdate(objValue.ToString()) == false) return;<br /> objValue = _oleCmdUpdate.ExecuteNonQuery();<br /> _oleCmdUpdate.Dispose();<br /> _oleCmdUpdate = null;<br /> if (KeepConnectionOpen == false) Close();<br /> }<br /> catch (Exception ex)<br /> {<br /> throw ex;<br /> }<br /> finally<br /> {<br /> if (_oleCmdUpdate != null)<br /> {<br /> _oleCmdUpdate.Dispose();<br /> _oleCmdUpdate = null;<br /> }<br /> }<br /> }<br /> #endregion<br /> #endregion<br /> public<br /> Dispose / Destructor#region Dispose / Destructor<br /> void Dispose()<br /> {<br /> if (_oleConn != null)<br /> {<br /> _oleConn.Dispose();<br /> _oleConn = null;<br /> }<br /> if (_oleCmdSelect != null)<br /> {<br /> _oleCmdSelect.Dispose();<br /> _oleCmdSelect = null;<br /> }<br /> // Dispose of remaining objects.<br /> }<br /> #endregion<br /> CTOR#region CTOR<br /> public ExcelReader()<br /> {<br /> //<br /> // TODO: Add constructor logic here<br /> //<br /> }<br /> #endregion<br /> }

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

 调用代码如下：

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

这里有点强调下：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)

于是修改为：

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

附上代码中用到的两个方法：

#region String<br /> public static string EmptyString = string.Empty;<br /> public static string StringParse(string old)<br /> { return StringParse(old, string.Empty); }<br /> public static string StringParse(object old)<br /> { return StringParse(old, string.Empty); }<br /> public static string StringParse(object old, string ReplaceString)<br /> {<br /> if (old == null || old.ToString().Trim().Length == 0)<br /> {<br /> if (ReplaceString == null || ReplaceString.Trim().Length == 0) { return string.Empty; }<br /> else { return ReplaceString.Trim(); }<br /> }<br /> else { return old.ToString().Trim(); }<br /> }<br /> public static string StringParse(string old, string ReplaceString)<br /> {<br /> if (old == null || old.Trim().Length == 0)<br /> {<br /> if (ReplaceString == null || ReplaceString.Trim().Length == 0) { return string.Empty; }<br /> else { return ReplaceString.Trim(); }<br /> }<br /> else { return old.Trim(); }<br /> }<br /> #endregion<br /> #region Short<br /> public static short ShortParse(string old)<br /> { return ShortParse(old, 0); }<br /> public static short ShortParse(object old)<br /> { return ShortParse(old, 0); }<br /> public static short ShortParse(string old, short NullValue)<br /> {<br /> short i = 0;<br /> try<br /> {<br /> if (old != null && old.ToString().IndexOf('.') > 0)<br /> {<br /> string str = old.ToString().Remove(old.ToString().IndexOf('.'));<br /> i = short.Parse(str.Trim());<br /> }<br /> else { i = short.Parse(old.ToString().Trim()); }<br /> }<br /> catch { try { i = NullValue; } catch { i = (short)0; } }<br /> return i;<br /> }<br /> public static short ShortParse(object old, short NullValue)<br /> {<br /> short i = 0;<br /> try<br /> {<br /> if (old != null && old.ToString().IndexOf('.') > 0)<br /> {<br /> string str = old.ToString().Remove(old.ToString().IndexOf('.'));<br /> i = short.Parse(str.Trim());<br /> }<br /> else { i = short.Parse(old.ToString().Trim()); }<br /> }<br /> catch { try { i = NullValue; } catch { i = (short)0; } }<br /> return i;<br /> }<br /> public static short ShortTryParse(object srcObj)<br /> {<br /> short defaultValue;<br /> if (srcObj == null) { return 0; }<br /> Int16.TryParse(srcObj.ToString(), out defaultValue);<br /> return defaultValue;<br /> }<br /> public static short ShortTryParse(object srcObj, short NullValue)<br /> {<br /> short defaultValue;<br /> ////if (srcObj == null) { return 0; }<br /> Int16.TryParse(srcObj.ToString(), out defaultValue);<br /> if (!Int16.TryParse(srcObj.ToString(), out defaultValue)) { Int16.TryParse(NullValue.ToString(), out defaultValue); }<br /> return defaultValue;<br /> }<br /> #endregion