在前一篇文章中，介绍了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总感觉不爽，于是再尝试下别的方法：

Reading and Writing Excel using OLEDB

主要的类文件如下：

/// <summary>

/// Summary description for ExcelReader.

/// </summary>

public class ExcelReader : IDisposable

{

#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

#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

#region Methods

#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

#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

#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

360docimg_501_360docimg_502_        #region simple utilities
360docimg_503_        private string AddWithComma(string strSource, string strAdd)
360docimg_504_360docimg_505_        {
360docimg_507_            if (strSource != "") strSource = strSource += ", ";
360docimg_508_            return strSource + strAdd;
360docimg_509_        }
360docimg_510_
360docimg_511_        private string AddWithAnd(string strSource, string strAdd)
360docimg_512_360docimg_513_        {
360docimg_515_            if (strSource != "") strSource = strSource += " and ";
360docimg_516_            return strSource + strAdd;
360docimg_517_        }
360docimg_518_        #endregion
360docimg_519_
360docimg_520_        private OleDbDataAdapter SetSheetQueryAdapter(DataTable dt)
360docimg_521_360docimg_522_        {
360docimg_524_            // Deleting in Excel workbook is not possible
360docimg_525_            //So this command is not defined
360docimg_526_            try
360docimg_527_360docimg_528_            {
360docimg_530_
360docimg_531_                if (_oleConn == null)
360docimg_532_360docimg_533_                {
360docimg_535_                    throw new Exception("Connection is unassigned or closed.");
360docimg_536_                }
360docimg_537_
360docimg_538_
360docimg_539_                if (_strSheetName.Length == 0)
360docimg_540_                    throw new Exception("Sheetname was not assigned.");
360docimg_541_
360docimg_542_                if (PKCols == null)
360docimg_543_                    throw new Exception("Cannot update excel sheet with no primarykey set.");
360docimg_544_                if (PKCols.Length < 1)
360docimg_545_                    throw new Exception("Cannot update excel sheet with no primarykey set.");
360docimg_546_
360docimg_547_                OleDbDataAdapter oleda = new OleDbDataAdapter(_oleCmdSelect);
360docimg_548_                string strUpdate = "";
360docimg_549_                string strInsertPar = "";
360docimg_550_                string strInsert = "";
360docimg_551_                string strWhere = "";
360docimg_552_
360docimg_553_
360docimg_554_                for (int iPK = 0; iPK < PKCols.Length; iPK++)
360docimg_555_360docimg_556_                {
360docimg_558_                    strWhere = AddWithAnd(strWhere, dt.Columns[iPK].ColumnName + "=?");
360docimg_559_                }
360docimg_560_                strWhere = " Where " + strWhere;
360docimg_561_
360docimg_562_                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
360docimg_563_360docimg_564_                {
360docimg_566_                    strInsert = AddWithComma(strInsert, dt.Columns[iCol].ColumnName);
360docimg_567_                    strInsertPar = AddWithComma(strInsertPar, "?");
360docimg_568_                    strUpdate = AddWithComma(strUpdate, dt.Columns[iCol].ColumnName) + "=?";
360docimg_569_                }
360docimg_570_
360docimg_571_                string strTable = "[" + this.SheetName + "$" + this.SheetRange + "]";
360docimg_572_                strInsert = "INSERT INTO " + strTable + "(" + strInsert + ") Values (" + strInsertPar + ")";
360docimg_573_                strUpdate = "Update " + strTable + " Set " + strUpdate + strWhere;
360docimg_574_
360docimg_575_
360docimg_576_                oleda.InsertCommand = new OleDbCommand(strInsert, _oleConn);
360docimg_577_                oleda.UpdateCommand = new OleDbCommand(strUpdate, _oleConn);
360docimg_578_                OleDbParameter oleParIns = null;
360docimg_579_                OleDbParameter oleParUpd = null;
360docimg_580_                for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
360docimg_581_360docimg_582_                {
360docimg_584_                    oleParIns = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
360docimg_585_                    oleParUpd = new OleDbParameter("?", dt.Columns[iCol].DataType.ToString());
360docimg_586_                    oleParIns.SourceColumn = dt.Columns[iCol].ColumnName;
360docimg_587_                    oleParUpd.SourceColumn = dt.Columns[iCol].ColumnName;
360docimg_588_                    oleda.InsertCommand.Parameters.Add(oleParIns);
360docimg_589_                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
360docimg_590_                    oleParIns = null;
360docimg_591_                    oleParUpd = null;
360docimg_592_                }
360docimg_593_
360docimg_594_                for (int iPK = 0; iPK < PKCols.Length; iPK++)
360docimg_595_360docimg_596_                {
360docimg_598_                    oleParUpd = new OleDbParameter("?", dt.Columns[iPK].DataType.ToString());
360docimg_599_                    oleParUpd.SourceColumn = dt.Columns[iPK].ColumnName;
360docimg_600_                    oleParUpd.SourceVersion = DataRowVersion.Original;
360docimg_601_                    oleda.UpdateCommand.Parameters.Add(oleParUpd);
360docimg_602_                }
360docimg_603_                return oleda;
360docimg_604_            }
360docimg_605_            catch (Exception ex)
360docimg_606_360docimg_607_            {
360docimg_609_                throw ex;
360docimg_610_            }
360docimg_611_
360docimg_612_        }
360docimg_613_
360docimg_614_360docimg_615_        #region command Singe Value Update
360docimg_616_        private bool SetSheetQuerySingelValUpdate(string strVal)
360docimg_617_360docimg_618_        {
360docimg_620_            try
360docimg_621_360docimg_622_            {
360docimg_624_                if (_oleConn == null)
360docimg_625_360docimg_626_                {
360docimg_628_                    throw new Exception("Connection is unassigned or closed.");
360docimg_629_                }
360docimg_630_
360docimg_631_                if (_strSheetName.Length == 0)
360docimg_632_                    throw new Exception("Sheetname was not assigned.");
360docimg_633_
360docimg_634_                _oleCmdUpdate = new OleDbCommand(
360docimg_635_                    @" Update ["
360docimg_636_                    + _strSheetName
360docimg_637_                    + "$" + _strSheetRange
360docimg_638_                    + "] set F1=" + strVal, _oleConn);
360docimg_639_                return true;
360docimg_640_            }
360docimg_641_            catch (Exception ex)
360docimg_642_360docimg_643_            {
360docimg_645_                throw ex;
360docimg_646_            }
360docimg_647_
360docimg_648_
360docimg_649_        }
360docimg_650_        #endregion
360docimg_651_
360docimg_652_
360docimg_653_
360docimg_654_        public void SetPrimaryKey(int intCol)
360docimg_655_360docimg_656_        {
360docimg_658_360docimg_659_            _PKCol = new int[1] { intCol };
360docimg_661_        }
360docimg_662_
360docimg_663_        public DataTable GetTable()
360docimg_664_360docimg_665_        {
360docimg_667_            return GetTable("ExcelTable");
360docimg_668_        }
360docimg_669_
360docimg_670_        private void SetPrimaryKey(DataTable dt)
360docimg_671_360docimg_672_        {
360docimg_674_            try
360docimg_675_360docimg_676_            {
360docimg_678_                if (PKCols != null)
360docimg_679_360docimg_680_                {
360docimg_682_                    //set the primary key
360docimg_683_                    if (PKCols.Length > 0)
360docimg_684_360docimg_685_                    {
360docimg_687_                        DataColumn[] dc;
360docimg_688_                        dc = new DataColumn[PKCols.Length];
360docimg_689_                        for (int i = 0; i < PKCols.Length; i++)
360docimg_690_360docimg_691_                        {
360docimg_693_                            dc[i] = dt.Columns[PKCols[i]];
360docimg_694_                        }
360docimg_695_
360docimg_696_
360docimg_697_                        dt.PrimaryKey = dc;
360docimg_698_
360docimg_699_                    }
360docimg_700_                }
360docimg_701_            }
360docimg_702_            catch (Exception ex)
360docimg_703_360docimg_704_            {
360docimg_706_                throw ex;
360docimg_707_            }
360docimg_708_        }
360docimg_709_
360docimg_710_        public DataTable GetTable(string strTableName)
360docimg_711_360docimg_712_        {
360docimg_714_            try
360docimg_715_360docimg_716_            {
360docimg_718_                //Open and query
360docimg_719_               if (_oleConn == null)  Open();
360docimg_720_                if (_oleConn.State != ConnectionState.Open)
360docimg_721_                    throw new Exception("Connection cannot open error.");
360docimg_722_                if (SetSheetQuerySelect() == false) return null;
360docimg_723_
360docimg_724_                //Fill table
360docimg_725_                OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
360docimg_726_                oleAdapter.SelectCommand = _oleCmdSelect;
360docimg_727_                DataTable dt = new DataTable(strTableName);
360docimg_728_                oleAdapter.FillSchema(dt, SchemaType.Source);
360docimg_729_                oleAdapter.Fill(dt);
360docimg_730_                if (this.Headers == false)
360docimg_731_360docimg_732_                {
360docimg_734_                    if (_strSheetRange.IndexOf(":") > 0)
360docimg_735_360docimg_736_                    {
360docimg_738_                        string FirstCol = _strSheetRange.Substring(0, _strSheetRange.IndexOf(":") - 1);
360docimg_739_                        int intCol = this.ColNumber(FirstCol);
360docimg_740_                        for (int intI = 0; intI < dt.Columns.Count; intI++)
360docimg_741_360docimg_742_                        {
360docimg_744_                            dt.Columns[intI].Caption = ColName(intCol + intI);
360docimg_745_                        }
360docimg_746_                    }
360docimg_747_                }
360docimg_748_                SetPrimaryKey(dt);
360docimg_749_                //Cannot delete rows in Excel workbook
360docimg_750_                dt.DefaultView.AllowDelete = false;
360docimg_751_
360docimg_752_                //Clean up
360docimg_753_                _oleCmdSelect.Dispose();
360docimg_754_                _oleCmdSelect = null;
360docimg_755_                oleAdapter.Dispose();
360docimg_756_                oleAdapter = null;
360docimg_757_                if (KeepConnectionOpen == false) Close();
360docimg_758_                return dt;
360docimg_759_
360docimg_760_            }
360docimg_761_            catch (Exception ex)
360docimg_762_360docimg_763_            {
360docimg_765_                throw ex;
360docimg_766_            }
360docimg_767_        }
360docimg_768_
360docimg_769_
360docimg_770_        private void CheckPKExists(DataTable dt)
360docimg_771_360docimg_772_        {
360docimg_774_            if (dt.PrimaryKey.Length == 0)
360docimg_775_                if (this.PKCols != null)
360docimg_776_360docimg_777_                {
360docimg_779_                    SetPrimaryKey(dt);
360docimg_780_                }
360docimg_781_                else
360docimg_782_                    throw new Exception("Provide an primary key to the datatable");
360docimg_783_        }
360docimg_784_        public DataTable SetTable(DataTable dt)
360docimg_785_360docimg_786_        {
360docimg_788_            try
360docimg_789_360docimg_790_            {
360docimg_792_                DataTable dtChanges = dt.GetChanges();
360docimg_793_                if (dtChanges == null) throw new Exception("There are no changes to be saved!");
360docimg_794_                CheckPKExists(dt);
360docimg_795_                //Open and query
360docimg_796_                if (_oleConn == null) Open();
360docimg_797_                if (_oleConn.State != ConnectionState.Open)
360docimg_798_                    throw new Exception("Connection cannot open error.");
360docimg_799_                if (SetSheetQuerySelect() == false) return null;
360docimg_800_
360docimg_801_                //Fill table
360docimg_802_                OleDbDataAdapter oleAdapter = SetSheetQueryAdapter(dtChanges);
360docimg_803_
360docimg_804_                oleAdapter.Update(dtChanges);
360docimg_805_                //Clean up
360docimg_806_                _oleCmdSelect.Dispose();
360docimg_807_                _oleCmdSelect = null;
360docimg_808_                oleAdapter.Dispose();
360docimg_809_                oleAdapter = null;
360docimg_810_                if (KeepConnectionOpen == false) Close();
360docimg_811_                return dt;
360docimg_812_            }
360docimg_813_            catch (Exception ex)
360docimg_814_360docimg_815_            {
360docimg_817_                throw ex;
360docimg_818_            }
360docimg_819_        }
360docimg_820_
360docimg_821_360docimg_822_        #region Get/Set Single Value
360docimg_823_
360docimg_824_        public void SetSingleCellRange(string strCell)
360docimg_825_360docimg_826_        {
360docimg_828_            _strSheetRange = strCell + ":" + strCell;
360docimg_829_        }
360docimg_830_
360docimg_831_        public object GetValue(string strCell)
360docimg_832_360docimg_833_        {
360docimg_835_            SetSingleCellRange(strCell);
360docimg_836_            object objValue = null;
360docimg_837_            //Open and query
360docimg_838_            if (_oleConn == null) Open();
360docimg_839_            if (_oleConn.State != ConnectionState.Open)
360docimg_840_                throw new Exception("Connection is not open error.");
360docimg_841_
360docimg_842_            if (SetSheetQuerySelect() == false) return null;
360docimg_843_            objValue = _oleCmdSelect.ExecuteScalar();
360docimg_844_
360docimg_845_            _oleCmdSelect.Dispose();
360docimg_846_            _oleCmdSelect = null;
360docimg_847_            if (KeepConnectionOpen == false) Close();
360docimg_848_            return objValue;
360docimg_849_        }
360docimg_850_
360docimg_851_        public void SetValue(string strCell, object objValue)
360docimg_852_360docimg_853_        {
360docimg_855_
360docimg_856_            try
360docimg_857_360docimg_858_            {
360docimg_860_
360docimg_861_                SetSingleCellRange(strCell);
360docimg_862_                //Open and query
360docimg_863_                if (_oleConn == null) Open();
360docimg_864_                if (_oleConn.State != ConnectionState.Open)
360docimg_865_                    throw new Exception("Connection is not open error.");
360docimg_866_
360docimg_867_                if (SetSheetQuerySingelValUpdate(objValue.ToString()) == false) return;
360docimg_868_                objValue = _oleCmdUpdate.ExecuteNonQuery();
360docimg_869_
360docimg_870_                _oleCmdUpdate.Dispose();
360docimg_871_                _oleCmdUpdate = null;
360docimg_872_                if (KeepConnectionOpen == false) Close();
360docimg_873_            }
360docimg_874_            catch (Exception ex)
360docimg_875_360docimg_876_            {
360docimg_878_                throw ex;
360docimg_879_            }
360docimg_880_            finally
360docimg_881_360docimg_882_            {
360docimg_884_                if (_oleCmdUpdate != null)
360docimg_885_360docimg_886_                {
360docimg_888_                    _oleCmdUpdate.Dispose();
360docimg_889_                    _oleCmdUpdate = null;
360docimg_890_                }
360docimg_891_            }
360docimg_892_
360docimg_893_        }
360docimg_894_        #endregion
360docimg_895_
360docimg_896_
360docimg_897_        #endregion
360docimg_898_
360docimg_899_        public
360docimg_900_
360docimg_901_360docimg_902_        #region Dispose / Destructor
360docimg_903_ void Dispose()
360docimg_904_360docimg_905_        {
360docimg_907_            if (_oleConn != null)
360docimg_908_360docimg_909_            {
360docimg_911_                _oleConn.Dispose();
360docimg_912_                _oleConn = null;
360docimg_913_            }
360docimg_914_            if (_oleCmdSelect != null)
360docimg_915_360docimg_916_            {
360docimg_918_                _oleCmdSelect.Dispose();
360docimg_919_                _oleCmdSelect = null;
360docimg_920_            }
360docimg_921_            // Dispose of remaining objects.
360docimg_922_        }
360docimg_923_        #endregion
360docimg_924_
360docimg_925_360docimg_926_        #region CTOR
360docimg_927_        public ExcelReader()
360docimg_928_360docimg_929_        {
360docimg_931_            //
360docimg_932_            // TODO: Add constructor logic here
360docimg_933_            //
360docimg_934_        }
360docimg_935_        #endregion
360docimg_936_    }

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

调用代码如下：

360docimg_937_360docimg_938_
360docimg_939_ public static string path = @"TempExcel\STemp.xls";
360docimg_940_        public static string path2 = "TestUser.xls";
360docimg_941_        public static string PreFilePath = @"C:\Excel\";
360docimg_942_        public static void DataSetToLocalExcel(DataSet ds, string srcPath, string outputPath, bool deleteOldFile)
360docimg_943_360docimg_944_        {
360docimg_946_360docimg_947_            if (ds == null || ds.Tables[0] == null && ds.Tables[0].Rows.Count == 0) { return; }
360docimg_949_            if (deleteOldFile)
360docimg_950_360docimg_951_            {
360docimg_953_360docimg_954_                if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
360docimg_956_            }
360docimg_957_            System.IO.File.Copy(srcPath, outputPath, true);
360docimg_958_            ExcelReader exr = new ExcelReader();
360docimg_959_            exr.ExcelFilename = outputPath;
360docimg_960_            exr.Headers = true;
360docimg_961_            exr.MixedData = true;
360docimg_962_            exr.KeepConnectionOpen = true;
360docimg_963_            string[] sheetnames = exr.GetExcelSheetNames();
360docimg_964_            exr.SheetName = sheetnames[0];
360docimg_965_            DataTable dt = exr.GetTable();
360docimg_966_            if (dt == null) return;
360docimg_967_            exr.SetPrimaryKey(0);
360docimg_968_            //dt.PrimaryKey = new DataColumn[] { dt.Columns["编号"] };
360docimg_969_            DataTable dt2 = ds.Tables[0].Copy();
360docimg_970_            dt.Rows.Clear();
360docimg_971_            for (int i = 0; i < dt2.Rows.Count; i++)
360docimg_972_360docimg_973_            { // Copy the values to the object array
360docimg_975_                DataRow dr = dt.NewRow();
360docimg_976_                for (int col = 0; col < dt.Columns.Count; col++)
360docimg_977_360docimg_978_                {
360docimg_980_                    dr[col] = dt2.Rows[i][col];
360docimg_981_                }
360docimg_982_                dt.Rows.Add(dr);
360docimg_983_            }
360docimg_984_            exr.SetTable(dt);
360docimg_985_360docimg_986_            WriteFile
360docimg_989_            exr.Close();
360docimg_990_            exr.Dispose();
360docimg_991_            exr = null;
360docimg_992_        }
360docimg_993_        private DataSet Get_AllPrices()
360docimg_994_360docimg_995_        {
360docimg_997_            try
360docimg_998_360docimg_999_            {
360docimg_1001_                // Get the employee details
360docimg_1002_                string strSql = "SELECT [CustomID] as 编号,[C_Name] as 品名,0 as 最高价格,0 as 最低价格,0 as 平均价格,'元/公斤' as 计量单位,'' as 备注  FROM [PriceCategory] WHERE ( 1=1 AND ([Puser] = 'tuser') )";
360docimg_1003_                SqlConnection objConn = new SqlConnection(@"Data Source=AP6;Initial Catalog=testdb2009　;Persist Security Info=True;User ID=sa;Password=sa");
360docimg_1004_                SqlDataAdapter daEmp = new SqlDataAdapter(strSql, objConn);
360docimg_1005_                daEmp.Fill(dsPrice, "price");
360docimg_1006_                return dsPrice;
360docimg_1007_            }
360docimg_1008_            catch (Exception Ex)
360docimg_1009_360docimg_1010_            {
360docimg_1012_                throw Ex;
360docimg_1013_            }
360docimg_1014_        }
360docimg_1015_
360docimg_1016_        DataSet dsPrice = new DataSet();
360docimg_1017_        protected void btnGetData_Click(object sender, EventArgs e)
360docimg_1018_360docimg_1019_        {
360docimg_1021_            DataSetToLocalExcel(Get_AllPrices(), PreFilePath + path, PreFilePath + path2, true);
360docimg_1022_        }

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

于是修改为：

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

附上两个方法：StringParse和ShortParse

360docimg_1026_360docimg_1027_代码

  #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

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助! 3w@live.cn

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。