asp.net導出數(shù)據到(dào)Excel的(de)方法(完全無組件(jià®> n),不(bù)用(yòng)調用(yòng)Excel.exe)

發表時(shí)間(jiān)：2020-10-18

發布人(rén)：葵宇科(kē)技(jì)

浏覽次數(shù)：58

今天把大(dà)家(jiā)最常用(yòng)的(de)功能(néng) asp.nε§et導出數(shù)據到(dào)Excel, 通(tōng)常的(de)方法是(shì)組'☆₩件(jiàn)形式的(de),但(dàn)是(shì)A®♣£SP.NET 對(duì)殺死Excel.exe支持不÷ε(bù)夠好(hǎo),特别還(hái)需要(yào)設置dc€£om太麻煩了(le).現(xiàn)在我給大(dà)家(jiā)的(de)是(shì)∞☆¶我做(zuò) 開(kāi)發,已經應用(yòng)到(dào)實際項目的(≤<de)方法,該方法不(bù)用(yòng)調用(yòng)Excel.exe進程.有(≥π±yǒu)不(bù)明(míng)白(bái)的(de),可(kě→ )以在下(xià)面留言,我會(huì)做(zuò)解答(dá)的(de). using¶Ω System;

using System.Text;

using System.Data;

using System.Data.OleDb;

namespace PowerAgent.DBUtility

{

/**//// <summary>

/// DataToExcel 的(de)>∏摘要(yào)說(shuō)明(míng)。

/// DataToExcel 的(d≠∞←✘e)摘要(yào)說(shuō)明(míng)。

/// </summary>

public class Data∞↑φToExcel

{

✔; const string ConnectionStrin® ÷₹g = "Provider=Microsoft.Jet.OLEDB.4.0;Da>≠ta Source={0};Extended Properties=Exc®✔el 8.0;";

public D≥‍ataToExcel()

₩₽ {

β↕← }

p↔✔‌©ublic string DataTableToExcel(D♠εataTable dt, string excelPa€≤♥∏th)

&nbs↔☆p; {

αε>‍ if (dt == null)

&✔↓Ωnbsp; {

λ×™‌ return "Da€"×'taTable不(bù)能(néng)為(wèi)空(kōng)";

& ₽¥¶nbsp; }

&nb¶≤>♣sp; int rows = dt.Rows.Count;

&nb¶‌÷sp; int cols = dt.Columns.Count;

βσ§; StringBuilde>εr sb;

&₽ nbsp; string connString;

&n$₽§bsp; if (rows == 0)

€₩ {

¶→>™ return "沒有(y∏ λǒu)數(shù)據";

"✘σ }

×↑ sb = new StringBuilder();

∏×® connString = string.Forma>€t(ConnectionString, excelPath);

'©; //生(shēng)成創建表的(de)腳本

£€αα sb.Append(©γ®¶"CREATE TABLE ");

≠ £∞; sb.Append(dt.Taφ₹αbleName + " ( ");

₩¶ for (int i = 0; i < cols; ππ←i++)

¥‌; {

λ★≥ if (→↑δ≥i < cols - 1)

&n€↓bsp; &nbs€®≤♠p; sb.Append(string.Format("{0} varc£αεhar,", dt.Columns.ColumnName))♠ ;

&nbs&☆ p; else

λ≤ &nbs €p; sb.Append(string.Format(&±™quot;{0} varchar)", dt. §Columns.ColumnName));

&≥σ≥nbsp; }

€₹→ using (OleDbConnection objConn = new Oγ♣leDbConnection(connString))

&nbsγ₽★§p; {

§♥σ§ Olβ'eDbCommand objCmd = new OleDbCommand();↕£≥

σ∞✔; objCmd.Connection = ob¶♠α¶jConn;

&nb♦≥‍sp; objγ₽ Cmd.CommandText = sb.ToString();

&★γδnbsp; &nbs₽γp; try

&¥€nbsp; {

‍↓ objConn.Oε‌'pen();

&nb☆λsp; ♣ β; objCmd.ExecuteNonQue± ×★ry();

₩ &; &nbs© ♥ p; }

&←↓αnbsp; catch ≠♠§π(Exception e)

✘Ω δ✔₩Ω; {

¶α return "在γα♥Excel中創建表失敗，錯(cuò)誤信息：" + e.Message;

&n≠±♣×bsp; &×γ'÷nbsp; }

§÷; 生(shēng)成插入數(shù)據腳本#regiδ∏♥on 生(shēng)成插入數(shù)據腳本

&nb€ ¶±sp; sb.Remove(0, sb.Length);

&>↓±nbsp; sb.Append(&π±€quot;INSERT INTO ");

&nbsλδ' p; sb.Append(dt.TableNam ‍>↑e + " ( ");

&nb→ ™sp; for (int i = 0; i < cols<φ↑; i++)

&≠↔nbsp; {

&↔ &nbs""p; if (i < cols - 1)

₹ → ♠>→✘ sb.Append(dt.Co‌≤÷lumns.ColumnName + ",");

✔♠ ®×; else

&nb₽→λsp; ↑>£ sb.Append(dt.Columns.Col ☆•umnName + ") values ("ε&®☆;);

&nb★★ sp; }

&ε♦nbsp; for (≥₽int i = 0; i < cols; i++)

☆ ; γ✘ {

λ÷¥± if (i <™ ¶✔; cols - 1)

♥ ♣> &n←↓λ>bsp; sb.Append( ↔¥♥"@" + dt.Columns.ColumnName + &qu<ot;,");

&nbs☆€≠↔p; ♣↓ else

★‍ •€®♦sb.Append("@" + dt.Columns→€ λ.ColumnName + ")");

&↑©>↕nbsp; }

γ<₽$; #π♣endregion

¥Ω //建立插入動作(zuò)的(de)Command

&nb >sp; objCmd.CommandText = sb.÷₩ToString();

&nb<δsp; OleDbParameterCollection para€♦m = objCmd.Parameters;

&n₹€bsp; {

∑& ; λ €λ param.Add(new OleDbP<₩αarameter("@" + dt.Columns.ColumnName, Oσ★♣>leDbType.VarChar));

∑₹ }

♦; //遍曆DataTable将數(shù)據插入新建的∏☆∏(de)Excel文(wén)件(jiàn)中

÷ε &nbs®₩p; foreach (DataRow row in d•t.Rows)

&♥≤nbsp; {

€₩≥ for (int i =∏®¶> 0; i < param.Count; i++'₩)

&nbs∏‌↑p; {

&n≠¶₽₩bsp; & &φnbsp; param.Value = row;

₽♣εφ ™♠£ }

÷✔₹₹ &nb§•sp; objCmd.ExecuteNo₹ ☆nQuery();

± }

✘γ return &q≤→± uot;數(shù)據已成功導入Excel";

&nbs↕Ωp; }//end using

}

}//end class

}

昆明(míng)軟件(jiàn)開(kāi)發公司中的₹↑(de)佼佼者，緻力定制(zhì)軟件(jiàn)開(kāi)發，昆明(míng)軟件(j∏&€iàn)開(kāi)發請(qǐng)聯系昆明(míng)葵宇科(kē)技(jì)有(yǒu)♥₩π限公司，電(diàn)話(huà)：15987118523。