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

核心提示：主要的类文件如下：ExcelReaderClass/** <summary>/// Summary description for ExcelReader./// </summary>public class ExcelReader : IDisposable{Variables#region

主要的类文件如下：

ExcelReaderClass

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