C# 資料導出到Excel

涓婚???C#涓?灏??版??瀵煎?ュ??xcel涓?,?跺???規???寰?澶?绉?,浣???搴??ㄧ????澧?涓???.

搴?璇?灏?璇?杩??ヤ富棰?.

棣???璇存????????,宸ヤ???瑕?,????瑕???涓?涓?灏?DataGrid涓????版??瀵煎?哄??xcel涓?涔?????xcel涓?缂?杈?瀹???瀵煎???????舵?存?版?版??搴???杩?涔?涓?涓?????

??寮?濮???瀹??版?規?寰?绠???,?ㄧ?涓??懼?頒?涓?涓?渚?瀛?,浠塊????浜?浠ュ??澶???浜?涓??瑰???棰?浠ュ????瀹?!?瑰?繪????瀹?姣???浠???浠ュ?虹?頒?涓?涓?杞界????绀烘?,?跺???ㄦ?烽???╀?瀛?璺?寰?,OK,????瀵煎????搴??寰?????涓??㈡??涓昏?浠ｇ??,??????澶ф?浠ｇ??,蹇?瑕???娉ㄩ?????藉??浜?:

?//璁闆?寮???璐?绌垮?ㄦ??)using System.Data.OleDb;

?//璁闆?寮???sing System.IO;

?//dg涓?ataGrid???藉??

?dg.Attributes.Add("style", "vnd.ms-excel numberformat:@"); //姝ゅ?ュ???戒負璁╀?瀛???Excel涓烘?????煎?,濡??版??涓???001 涓?浼?琚?淇?瀛???1,Excel???ㄧ?00?繪??浜?~娌″??娉?璋?璁╀???0??

??????? dg.AllowPaging = false;

??????? dg.DataSource = dt; //璇瘋??宸辯?DataGrid涓?涓??版??婧?

??????? dg.DataBind();

??????? Response.Clear();

??????? Response.Buffer = true;

??????? Response.Charset = "GB2312";

??????? Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");//FileName??涓?杞芥?剁????瀛?,褰??剁?ㄦ?峰??浠ヨ??宸辨?存????????? //璁劇疆杈??烘?涓虹??浣?涓???

??????? Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

??????? //璁劇疆杈??烘??浠剁被??涓?xcel??浠???????? Response.ContentType = "application/ms-excel";

??????? this.EnableViewState = false;

??????? System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);

??????? System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);

??????? System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

??????? dg.RenderControl(oHtmlTextWriter);

??????? Response.Write(oStringWriter.ToString());

??????? Response.End();

///

OK缁???浜?,灏辮?涔?绠???,浣跨?ㄦ????杈??烘?瀹???

濡???浣????沖?煎?轟?涓?Excel,???沖??璇?浣?,OK浣??????藉???頒?~

But!

濡???浣?????涓??烽??瑕???瀵煎???????沖??璇?浣?~This is not enough~....

瀵煎?虹??Excel浣???浠ュ?抽??缂?杈???涓?涓?,????~ NND灞??舵??涓?涓?浼?Excel,浠???婧???浠跺??舵??Html?煎???!kao.骞?

浣???濡???浣?淇?瀛?涓?涓??????╂??????Excel?煎?,浠?灏辯?绋?浜???涓???????Excel~

??浠ュ?逛??ㄦ?鋒?ヨ?翠?浼????頒袱绉?????绗?涓?涓?浠?寰?????,????浠?寰?????,瀵煎?轟?,浠?涔??芥病骞插??瀵煎???ヤ?,???ラ??,浣?浼???"??kao,杩?灏?瀛?涓????ユ?捐??????"????,??涔??ㄥ?涓????版?¤?涔?楠?杩?....浣???璋?璁╀漢瀹舵???ㄦ?峰??

杩??蜂??㈠?圭??灏辨??涓?涓?Html?煎???Excel~濡???浠?缂?杈???淇?瀛???瀵煎???誨??灏辨??涓?涓???????Excel??浠?褰??朵?,浣?涓虹?搴???杩?涓ょ????典??借???瀹?,OK涓??㈢?涓?娈靛??浠ュ???跺?煎?矽?涓ょ??煎???浠ｇ??:

?/// <summary>

??????? /// 杩???xcel,灏?Excel?????版??杞??㈡??DataTable

??????? /// </summary>

??????? /// <param name="filePath">Excel璺?寰?</param>

??????? /// <returns>杞??㈡??DataTable浠ュ????Excel??瀹?lt;/returns>

??????? public DataTable ImporExcel(string filePath)

??????? {

??????????? DataTable ds = new DataTable();

??????????? //Excel杩??ュ??绗?覆

??????????? OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=/"Excel 8.0;/"");

??????????? //try??涓烘?ｅ父?版???煎?? 杩?????瀵規????Excel?煎???

??????????? try

??????????? {

??????????????? conn.Open();

??????????????? string fileName = filePath.Substring(filePath.LastIndexOf("//") + 1);???? //??寰???浠跺??+?╁???

??????????????? fileName = fileName.Substring(0, fileName.IndexOf("."));????????????????? //?繪???╁???,???╂??浠跺??

??????????????? OleDbCommand myOleDbCommand = new OleDbCommand("select * from [" + fileName + "$]", conn);?? //寰??拌〃????????瀹???????????????? OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);

??????????????? myData.Fill(ds);

??????????????? conn.Close();

??????????????? return ds;

??????????? }

??????????? //catch??涓哄?煎?ョ??Excel?煎?,??杩???????浠?寰??頒?涓?ds? 杩?????瀵?tml?煎???

??????????? catch

??????????? {

??????????????? FileStream yuanfile = new FileStream(filePath, FileMode.Open);?? //褰??朵?,濡???浠?瀵煎?ョ????涓??劇??...浣?杩???杩???浠ュ??try,??????澶ф???璺?

//??寮???浠???????????????? StreamReader tmpStream = new StreamReader(yuanfile, System.Text.Encoding.GetEncoding("utf-8"));? //璇誨?烘??浠??煎?涓?B2312

??????????????? string tmpStr = tmpStream.ReadToEnd();??????????????????????????????????????????????????????????? //?翠釜璇誨?烘??浠???????????????? tmpStream.Close();

??????????????? ds = GetDataTbaleFromString(tmpStr);

??????????????? tmpStr = "";

??????????????? return ds;

??????????? }

??????? /// <summary>

??????? /// 灏???瀹?Html瀛?绗?覆???版??杞??㈡??DataTable瀵矽薄 --?規????<tr><td>??绛??規??瀛?绗??琛?澶???

??????? /// </summary>

??????? /// <param name="tmpHtml">Html瀛?绗?覆</param>

??????? /// <returns></returns>

??????? public DataTable GetDataTbaleFromString(string tmpHtml)

??????? {

??????????? string tmpStr = tmpHtml;

??????????? DataTable dt = new DataTable();

??????????? //???ょ??涓?涓?<tr>涔?????????涓?涓?</tr>涔??????ㄥ??

??????????? int index = tmpStr.IndexOf("<tr");

??????????? if (index > -1)

??????????? {

??????????????? tmpStr = tmpStr.Substring(index);

??????????? }

??????????? else

??????????? {

??????????????? return dt;

??????????? }

??????????? index = tmpStr.LastIndexOf("</tr");

??????????? if (index > -1)

??????????? {

??????????????? tmpStr = tmpStr.Substring(0, index + 5);

??????????? }

??????????? else

??????????? {

??????????????? return dt;

??????????? }

??????????? bool exitsSparator = false;

??????????? char Separator = Convert.ToChar("^");

??????????? //濡?????瀛?绗?覆涓?????????绗???^????????瀹??挎?㈡??

??????????? if (tmpStr.IndexOf(Separator.ToString()) > -1)

??????????? {

??????????????? exitsSparator = true;

??????????????? tmpStr = tmpStr.Replace("^", "^$&^");

??????????? }

??????????? //?規??</tr>????

??????????? string[] tmpRow = tmpStr.Replace("</tr>", "^").Split(Separator); //浣跨??灏?瀛?绗?覆??寮?

??????????? for (int i = 0; i < tmpRow.Length - 1; i++)

??????????? {

??????????????? DataRow newRow = dt.NewRow();

??????????????? string tmpStrI = tmpRow[i];

??????????????? if (tmpStrI.IndexOf("<tr>") > -1)

??????????????? {

??????????????????? tmpStrI = tmpStrI.Substring(tmpStrI.IndexOf("<tr>"));

??????????????????? if (tmpStrI.IndexOf("display:none") < 0 || tmpStrI.IndexOf("display:none") > tmpStrI.IndexOf(">"))

??????????????????? {

??????????????????????? tmpStrI = tmpStrI.Replace("</td>", "^");

??????????????????????? string[] tmpField = tmpStrI.Split(Separator);

??????????????????????? for (int j = 0; j < tmpField.Length - 1; j++)

??????????????????????? {

??????????????????????????? tmpField[j] = RemoveString(tmpField[j], "<font>");

??????????????????????????? index = tmpField[j].LastIndexOf(">") + 1;

??????????????????????????? if (index > 0)

??????????????????????????? {

??????????????????????????????? string field = tmpField[j].Substring(index, tmpField[j].Length - index);

??????????????????????????????? if (exitsSparator) field = field.Replace("^$&^", "^");

??????????????????????????????? if (i == 0)

??????????????????????????????? {

??????????????????????????????????? string tmpFieldName = field;

??????????????????????????????????? int sn = 1;

??????????????????????????????????? while (dt.Columns.Contains(tmpFieldName))

??????????????????????????????????? {

??????????????????????????????????????? tmpFieldName = field + sn.ToString();

??????????????????????????????????????? sn += 1;

??????????????????????????????????? }

??????????????????????????????????? dt.Columns.Add(tmpFieldName);

??????????????????????????????? }

??????????????????????????????? else

??????????????????????????????? {

??????????????????????????????????? newRow[j] = field;

??????????????????????????????? }

??????????????????????????? }//end of if(index>0)

??????????????????????? }

??????????????????????? if (i > 0)

??????????????????????? {

??????????????????????????? dt.Rows.Add(newRow);??? //灏??寸??濂界??DataRow娣誨????t??

??????????????????????? }

??????????????????? }

??????????????? }

??????????? }

??????????? dt.AcceptChanges();

??????????? return dt;

??????? }

?? /// <summary>

??????? /// 浠???瀹?Html瀛?绗?覆涓????ゆ??瀹???瀵矽薄

??????? /// </summary>

??????? /// <param name="tmpHtml">Html瀛?绗?覆</param>

??????? /// <param name="remove">??瑕????ょ??瀵矽薄--渚?濡?杈???lt;font>??????lt;font ???????>"</font></param>

??????? /// <returns></returns>

??????? public string RemoveString(string tmpHtml, string remove)

??????? {

??????????? tmpHtml = tmpHtml.Replace(remove.Replace("<", "</"), "");? //????lt;,</杩?绫誨??绗???????????? tmpHtml = tmpHtml.Replace("&nbsp;", "");?????????????????? //???ょ┖?煎??绗?&nbsp;) 浣????????????浠?涔???澶?浜???,浣?灏卞??浠?涔?,??瀹㈡?,??涓???缁??????㈠??!

??????????? tmpHtml = RemoveStringHead(tmpHtml, remove);

??????????? return tmpHtml;

??????? }

??????? /// <summary>

??????? /// ??渚??規?RemoveString()浣跨????????? /// </summary>

??????? /// <param name="tmpHtml"></param>

??????? /// <param name="remove"></param>

??????? /// <returns>澶???濂界??瀛?绗?覆</returns>

??????? private string RemoveStringHead(string tmpHtml, string remove)

??????? {

??????????? //涓轟??逛究娉ㄩ??锛???璁捐??ュ????emove="<font>"

??????????? if (remove.Length < 1)

??????????? {

??????????????? return tmpHtml;//????emove涓虹┖锛?涓?澶???杩???

??????????? }

??????????? if ((remove.Substring(0, 1) != "<" || remove.Substring(remove.Length - 1) != ">"))

??????????? {

??????????????? return tmpHtml;//????emove涓???<?????>锛?涓?澶???杩???

??????????? }

??????????? int IndexS = tmpHtml.IndexOf(remove.Replace(">", ""));//?ユ?鋸??<font????浣?缃?

??????????? int IndexE = -1;

??????????? if (IndexS > -1)

??????????? {

??????????????? string tmpRight = tmpHtml.Substring(IndexS, tmpHtml.Length - IndexS);

??????????????? IndexE = tmpRight.IndexOf(">");

??????????????? if (IndexE > -1)

??????????????? {

??????????????????? tmpHtml = tmpHtml.Substring(0, IndexS) + tmpHtml.Substring(IndexS + IndexE + 1);

??????????????? }

??????????????? if (tmpHtml.IndexOf(remove.Replace(">", "")) > -1)

??????????????? {

??????????????????? tmpHtml = RemoveStringHead(tmpHtml, remove);

??????????????? }

??????????? }

??????????? return tmpHtml;

??????? }

///

OK,缁???,??涓や釜绫繪????瀵圭??涓?涓???瀹??版?規?,涔?灏辨??Html??瀵煎????涓轟???Html?煎?,??浠ヤ?涓??界?ㄤ?涓??規??存?ヨ?誨??杩???浣???浠?姣?绔?????瑙?寰???Html??浠??懼?拌?寰?,寰???涓?涓?,澶???涓?涓?灏卞?烘?ヤ?!

OK?版??瀵煎?ュ?煎?哄?ㄩ??K浜?!

????寰?涓????????界??姝ｄ?缁???杩?绡???绔???,骞朵?????涓??蜂????頒?杩?涓???棰?????涓?渚??舵病??瑙ｅ?蟲?ュ?繪?劇??妗??????????借?缁??????跨??浜?....

??long totalCount = dt.Rows.Count;?? //dt??琛???涓?浼?寰???瑕???//FileName涓鴻礬寰?(娉ㄦ????瀛?瑕???琛ㄧ????瀛?涓???姣?濡??ㄦ?℃?規?涓?搴?涓?xcel,?跺??杩?3涓????闆?璇ュ?ㄧ?ㄥ????浠ｆ?跨??,?靛????宸卞??????涓??逛?)

??????? string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName+ ";Extended ?roperties=Excel 8.0;";

??????? OleDbConnection objConn = new OleDbConnection(connString);?

??????? OleDbCommand objCmd = new OleDbCommand();

??????? objCmd.Connection = objConn;

??????? objConn.Open();

??????? //??寤鴻〃??缁???

??????? objCmd.CommandText = "CREATE TABLE excel(ID int ,瀛?娈?varchar)";

??????? objCmd.ExecuteNonQuery();

??????? //???ヨ〃

??????? for (int i = 0; i < dt.Rows.Count; i++)

??????? {

??????????? objCmd.CommandText = "INSERT INTO excel(ID,瀛?娈? values"

??????????????? + "('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "')";

??????????? objCmd.ExecuteNonQuery();

??????? }

??????? objConn.Close();

/

璇風??涓?娈典唬??....??寤轟?涓?????Excel,浠ｇ??浣?涔???浠ョ戶缁??╁?,姣?濡?璇磋?╃?ㄦ?瘋??宸遍???╄礬寰???浠?涔???,绛?绛?涓?绯誨????寮鴻?涓?涓?,杩?涓??規?璨?浼艱В?充?Excel杩?绋?娌℃???抽??????棰?!????寮???xcel缁?浠?????绻?????浠ｇ??,璋㈣阿!

???瀵煎??灏辯?ㄥ????涓??㈤?ｆ?墊????Excel??灏?K浜?~

杩???灏辨??web??杩?涓?瀵煎?烘???ㄦ???″?ㄤ???,浣???缁?涓?瓒??炬?ヨ?╃?ㄦ?蜂?杞藉氨OK浜?.

??娆″己璋?!?ㄥ??浠ｇ???ユ?浜?缃?缁?,??浜哄??浜?涓?涓??寸????澶???,??????涓轟???搴???宸辯??绋?搴?,?ㄦ?ゆ??????浜?涓?涓?澶ф???璺?,??涓???绮?杩??誨氨?ㄥ??~ 淇?璇?浣?姝葷?缈?~~~!!!