C#使用OLEDB读写excel文件
Reading and Writing Excel using OLEDB类库源代码
/**//// <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了!
#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 public static string path = @"TempExcel/STemp.xls"; public static string path2 = "TestUser.xls"; public static string PreFilePath = @"C:/Excel/"; public static void DataSetToLocalExcel(DataSet ds, string srcPath, string outputPath, bool deleteOldFile) { if (ds == null || ds.Tables[0] == null && ds.Tables[0].Rows.Count == 0) { return; } if (deleteOldFile) { if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); } } System.IO.File.Copy(srcPath, outputPath, true); ExcelReader exr = new ExcelReader(); exr.ExcelFilename = outputPath; exr.Headers = true; exr.MixedData = true; exr.KeepConnectionOpen = true; string[] sheetnames = exr.GetExcelSheetNames(); exr.SheetName = sheetnames[0]; DataTable dt = exr.GetTable(); if (dt == null) return; exr.SetPrimaryKey(0); //dt.PrimaryKey = new DataColumn[] { dt.Columns["编号"] }; DataTable dt2 = ds.Tables[0].Copy(); dt.Rows.Clear(); for (int i = 0; i < dt2.Rows.Count; i++) { // Copy the values to the object array DataRow dr = dt.NewRow(); for (int col = 0; col < dt.Columns.Count; col++) { dr[col] = dt2.Rows[i][col]; } dt.Rows.Add(dr); } exr.SetTable(dt); WriteFile#region WriteFile #endregion exr.Close(); exr.Dispose(); exr = null; } private DataSet Get_AllPrices() { try { // Get the employee details string strSql = "SELECT [CustomID] as 编号,[C_Name] as 品名,0 as 最高价格,0 as 最低价格,0 as 平均价格,'元/公斤' as 计量单位,'' as 备注 FROM [PriceCategory] WHERE ( 1=1 AND ([Puser] = 'tuser') )"; SqlConnection objConn = new SqlConnection(@"Data Source=AP6;Initial Catalog=testdb2009 ;Persist Security Info=True;User ID=sa;Password=sa"); SqlDataAdapter daEmp = new SqlDataAdapter(strSql, objConn); daEmp.Fill(dsPrice, "price"); return dsPrice; } catch (Exception Ex) { throw Ex; } } DataSet dsPrice = new DataSet(); protected void btnGetData_Click(object sender, EventArgs e) { DataSetToLocalExcel(Get_AllPrices(), PreFilePath + path, PreFilePath + path2, true); }
这里有点强调下:OleDbConnection特别要注意, 当使用标准串:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:"MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
作为驱动字符串时,结果提示:“操作必须使用一个可更新的查询”错误,具体解决办法参考oledb读写excel出现“操作必须使用一个可更新的查询”错误
来源:http://blog.csdn.net/downmoon/article/details/3796652
加支付宝好友偷能量挖...