DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(d
主要的类文件如下：
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
}
思路：通过读出Excel模板文件到DataTale,再把数据填充到DataTable,文件另存下就OK了！
调用代码如下：
DataSetToLocalExcel
public static string path = @"TempExcelSTemp.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)
于是修改为：
Code
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:"MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=2";
相关文章：
