|
#region SqlServer導(dǎo)入到Excel
//新創(chuàng)建的Excel文件路徑
public string strSaveFileName;
//存放SqlServer內(nèi)容
public DataSet dsSqlServer;
/// <summary>
/// 選擇Excel的保存路徑
/// </summary>
/// <returns>已經(jīng)選擇好文件路徑則返回true， 否則false</returns>
public bool ChoicePath()
{
//保存對(duì)話框
SaveFileDialog savePath = new SaveFileDialog();
if (savePath.ShowDialog() == DialogResult.OK)
{
strSaveFileName = savePath.FileName;
//判斷文件是否已存在
if (IsExist(strSaveFileName))
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
/// <summary>
/// 判斷文件是否已存在，存在則詢問是否覆蓋
/// </summary>
/// <param name="fileName">文件路徑</param>
/// <returns>“不存在”或“存在并允許覆蓋”返回true， 存在但不覆蓋返回false</returns>
public bool IsExist(string fileName)
{
//判斷文件是否已存在
if (System.IO.File.Exists(fileName + ".xls"))
{ //提示是否覆蓋
if (MessageBox.Show("該文件已經(jīng)存在，是否覆蓋？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return false;
}
else
{ //刪除已有文件
System.IO.File.Delete(fileName + ".xls");
return true;
}
}
return true;
}
/// <summary>
/// 獲取SqlServer內(nèi)容，存放到dsSqlServer里面
/// </summary>
public void GetDs()
{
//要執(zhí)行的sql語(yǔ)句
String strSql = "select * from CutClassTable";
//SqlServer連接語(yǔ)句，該實(shí)例數(shù)據(jù)庫(kù)為“MyDataBase”
string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True";
//創(chuàng)建連接
SqlConnection sqlCon = new SqlConnection(strCon);
//.......
SqlDataAdapter sqlDa = new SqlDataAdapter(strSql, strCon);
dsSqlServer = new DataSet();
sqlCon.Open();
sqlDa.Fill(dsSqlServer, "Info");
sqlCon.Close();
}
/// <summary>
/// 創(chuàng)建Excel文件，在我的其它文章里，有講這方面內(nèi)容，不清楚的可翻看前面章節(jié)
/// </summary>
private void CreateExcel()
{
try
{
//創(chuàng)建Excel對(duì)象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//創(chuàng)建新的Excel工作簿
Microsoft.Office.Interop.Excel.Workbook wBook = excel.Application.Workbooks.Add(Missing.Value);
//使Excel不可視
excel.Visible = false;
//設(shè)置禁止彈出保存和覆蓋的詢問提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = true;
//保存
wBook.SaveAs(@strSaveFileName,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
wBook = null;
//必須有該語(yǔ)句，才能有效結(jié)束，否則每次運(yùn)行會(huì)產(chǎn)生一個(gè)Excel
excel.Quit();
excel = null;
}
catch (Exception err)
{
//錯(cuò)誤提示
MessageBox.Show("Excel操作出錯(cuò)！錯(cuò)誤原因：" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// 把數(shù)據(jù)導(dǎo)入Excel
/// </summary>
private void InsertToExcel()
{
//下面是Excel數(shù)據(jù)庫(kù)訪問操作：
//連接字符串
string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '" + strSaveFileName + "';Extended Properties='Excel 8.0;HDR=NO'";
//要執(zhí)行的sql語(yǔ)句，采用stringbuilder類
StringBuilder strbSql = new StringBuilder();
//創(chuàng)建OleDb連接對(duì)象.
OleDbConnection oleDbCon = new OleDbConnection(strCon);
//創(chuàng)建執(zhí)行對(duì)象
OleDbCommand oleDbCom = new OleDbCommand();
//賦予連接對(duì)象
oleDbCom.Connection = oleDbCon;
//打開連接
oleDbCon.Open();
//下面是數(shù)據(jù)插入到Excel
try
{
//創(chuàng)建一張新的工作表，表名為MySheet。你或許疑惑為什么不在原有的工作區(qū)Sheet1里導(dǎo)入，在后面我會(huì)講到！
string strSql = "create table MySheet (學(xué)號(hào) char(20), 姓名 char(20), 曠課次數(shù) int, 原因 char(255)) ";
//賦予sql語(yǔ)句
oleDbCom.CommandText = strSql;
//執(zhí)行sql語(yǔ)句，創(chuàng)建一個(gè)新表
oleDbCom.ExecuteNonQuery();
//循環(huán)插入數(shù)據(jù)
for (int i = 0; i < dsSqlServer.Tables[0].Rows.Count; i++)
{
//要注意 參數(shù)要用 ' '括起來(lái)的。例如： 'value'
strbSql.Append("insert into [MySheet$] values('");
for (int j = 0; j < 3; j++)
{
strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[j].ToString() + "','");
}
strbSql.Append(dsSqlServer.Tables[0].Rows[i].ItemArray[3].ToString() + "')");
//stringbuilder轉(zhuǎn)為string類型，因?yàn)閟ql語(yǔ)句不能直接執(zhí)行stringbuilder類
strSql = strbSql.ToString();
oleDbCom.CommandText = strSql;
oleDbCom.ExecuteNonQuery();
//清除strbSql過往信息
strbSql.Remove(0, strbSql.Length);
}
//插入成功提示
MessageBox.Show("導(dǎo)入Excel成功！請(qǐng)查看?。?, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//失敗提示
MessageBox.Show("導(dǎo)入Excel過程中發(fā)生錯(cuò)誤！/n錯(cuò)誤提示：" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
oleDbCon.Close();
}
}
/// <summary>
/// 導(dǎo)入數(shù)據(jù)到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntToExcel_Click(object sender, EventArgs e)
{
//判斷是否選擇好路徑
if (ChoicePath())
{
//創(chuàng)建一個(gè)Excel文件
CreateExcel();
//獲取SqlServer表內(nèi)容，存放到dsSqlServer里面
GetDs();
//導(dǎo)入數(shù)據(jù)到Excel
InsertToExcel();
}
}
#endregion
|
