{"id":150600,"date":"2023-02-23T14:15:55","date_gmt":"2023-02-23T06:15:55","guid":{"rendered":"http:\/\/www.idc.net\/help\/150600\/"},"modified":"2023-02-23T14:15:55","modified_gmt":"2023-02-23T06:15:55","slug":"%e9%ab%98%e6%95%88%e4%be%bf%e6%8d%b7%ef%bc%81%e6%95%b0%e6%8d%ae%e5%ba%93%e6%8a%a5%e8%a1%a8%e5%af%bc%e5%87%baexcel%e6%95%99%e7%a8%8b-%e6%95%b0%e6%8d%ae%e5%ba%93%e6%8a%a5%e8%a1%a8%e5%af%bc%e5%87%baexce","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/150600\/","title":{"rendered":"\u9ad8\u6548\u4fbf\u6377\uff01\u6570\u636e\u5e93\u62a5\u8868\u5bfc\u51faExcel\u6559\u7a0b (\u6570\u636e\u5e93\u62a5\u8868\u5bfc\u51faexcel)"},"content":{"rendered":"<p>\u5728\u4f01\u4e1a\u7ba1\u7406\u3001\u6570\u636e\u5206\u6790\u548c\u4e1a\u52a1\u51b3\u7b56\u4e2d\uff0c\u6570\u636e\u662f\u6700\u91cd\u8981\u7684\u6709\u529b\u5de5\u5177\u3002\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\uff0c\u6570\u636e\u9700\u8981\u4ece\u6570\u636e\u5e93\u5bfc\u51fa\u62a5\u8868\uff0c\u4ee5\u4f9b\u8fdb\u4e00\u6b65\u5206\u6790\u548c\u4f7f\u7528\u3002Excel\u4f5c\u4e3a\u4e00\u6b3e\u529f\u80fd\u5f3a\u5927\u7684\u529e\u516c\u8f6f\u4ef6\uff0c\u662f\u4e00\u4e2a\u7075\u6d3b\u7684\u62a5\u8868\u5de5\u5177\uff0c\u9002\u7528\u4e8e\u5404\u79cd\u5f62\u5f0f\u7684\u6570\u636e\u5904\u7406\u548c\u6c47\u603b\u3002\u56e0\u6b64\uff0c\u5728\u672c\u6587\u4e2d\uff0c\u6211\u4eec\u5c06\u4e3a\u60a8\u4ecb\u7ecd\u5982\u4f55\u9ad8\u6548\u4fbf\u6377\u5730\u5c06\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u5bfc\u51fa\u4e3aExcel\u62a5\u8868\u3002<\/p>\n<p>1. \u51c6\u5907\u5de5\u4f5c<\/p>\n<p>\u60a8\u9700\u8981\u786e\u5b9a\u6240\u9700\u8981\u5bfc\u51fa\u7684\u6570\u636e\u7c7b\u578b\u4ee5\u53ca\u9700\u8981\u5bfc\u51fa\u7684\u6570\u636e\u6570\u76ee\u3002\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u9700\u8981\u5bfc\u51fa\u7684\u6570\u636e\u53ef\u4ee5\u901a\u8fc7\u4ee5\u4e0b\u65b9\u5f0f\u8fdb\u884c\u7b5b\u9009\uff1a<\/p>\n<p>\u2013 \u6309\u7167\u65f6\u95f4\u8303\u56f4\uff1a\u65f6\u95f4\u6bb5\u3001\u6708\u4efd\u3001\u5b63\u5ea6\u7b49\uff1b<\/p>\n<p>\u2013 \u6309\u7167\u5730\u533a\uff1a\u56fd\u5bb6\u3001\u5730\u533a\u3001\u57ce\u5e02\u7b49\uff1b<\/p>\n<p>\u2013 \u6309\u7167\u90e8\u95e8\uff1a\u9500\u552e\u3001\u8fd0\u8425\u3001\u5e02\u573a\u7b49\u3002<\/p>\n<p>\u9664\u4e86\u4ee5\u4e0a\u7684\u7b5b\u9009\u6761\u4ef6\uff0c\u60a8\u8fd8\u9700\u8981\u786e\u5b9a\u9700\u8981\u5bfc\u51fa\u7684\u5b57\u6bb5\u3002\u60a8\u53ef\u4ee5\u9009\u62e9\u5bfc\u51fa\u6240\u6709\u5b57\u6bb5\u6216\u4ec5\u9009\u62e9\u6709\u7528\u7684\u5b57\u6bb5\u3002\u8fd9\u5c06\u6709\u52a9\u4e8e\u4fdd\u8bc1\u62a5\u8868\u7684\u7b80\u6d01\u6027\u548c\u6613\u8bfb\u6027\u3002<\/p>\n<p>2. \u6570\u636e\u5e93\u67e5\u8be2<\/p>\n<p>\u7b2c\u4e8c\u6b65\uff0c\u60a8\u9700\u8981\u5728\u6570\u636e\u5e93\u4e2d\u8fdb\u884c\u67e5\u8be2\uff0c\u4ee5\u68c0\u7d22\u6240\u9700\u6570\u636e\u3002\u67e5\u8be2\u64cd\u4f5c\u53ef\u4ee5\u4f7f\u7528SQL\u8bed\u53e5\u5b9e\u73b0\u3002\u4ee5\u4e0b\u662f\u4e00\u4e9b\u793a\u4f8b\u67e5\u8be2\u4ee5\u53ca\u5176\u76f8\u5173\u529f\u80fd\uff1a<\/p>\n<p>\u2013 \u67e5\u8be2\u7279\u5b9a\u65f6\u95f4\u6bb5\u5185\u7684\u8ba2\u5355\u6570\u91cf\uff1aSELECT COUNT(*) FROM Orders WHERE OrderDate BETWEEN \u20182023\/01\/01\u2019 AND \u20182023\/12\/31\u2019;<\/p>\n<p>\u2013 \u6309\u90e8\u95e8\u7b5b\u9009\u9500\u552e\u989d\uff1aSELECT SUM(Amount) FROM Orders WHERE Department =\u2019Sales\u2019;<\/p>\n<p>\u2013 \u7b5b\u9009\u9500\u552e\u5355\u4ef7\u66f4\u9ad8\u7684\u4ea7\u54c1\uff1aSELECT MAX(Price) FROM Products;<\/p>\n<p>3. \u5bfc\u51faExcel\u62a5\u8868<\/p>\n<p>\u6700\u540e\u4e00\u6b65\uff0c\u60a8\u9700\u8981\u5c06\u67e5\u8be2\u7ed3\u679c\u5bfc\u51fa\u5230Excel\u62a5\u8868\u4e2d\u3002\u4ee5\u4e0b\u662f\u4e00\u4e9b\u5e38\u89c1\u7684\u65b9\u6cd5\uff1a<\/p>\n<p>\u2013 \u5bfc\u51fa\u5230CSV\u683c\u5f0f:CSV\u683c\u5f0f\u53ef\u4ee5\u4f7f\u7528Excel\u8f7b\u677e\u5bfc\u5165\uff0c\u5e76\u4e14\u53ef\u4ee5\u901a\u8fc7\u7528\u4e00\u4e9b\u7b80\u5355\u7684\u6587\u672c\u7f16\u8f91\u5668\u6765\u6253\u5f00\u548c\u7f16\u8f91\u3002\u8fd9\u662f\u6700\u7b80\u5355\u7684\u65b9\u6cd5\uff0c\u4f46CSV\u683c\u5f0f\u7f3a\u5c11\u683c\u5f0f\u548c\u6837\u5f0f\u3002<\/p>\n<p>\u2013 \u7279\u5b9a\u5de5\u5177\u5bfc\u51fa\uff1a\u6709\u5f88\u591a\u5de5\u5177\u53ef\u4ee5\u5b9e\u73b0\u4ece\u6570\u636e\u5e93\u76f4\u63a5\u5bfc\u51fa\u5230Excel\u4e2d\u3002\u8fd9\u4e9b\u5de5\u5177\u4f7f\u7528ODBC\u8fde\u63a5\u5230\u6570\u636e\u5e93\uff0c\u53ef\u4ee5\u5728\u5bfc\u51fa\u4e4b\u524d\u52a0\u5165\u6837\u5f0f\u548c\u683c\u5f0f\u7b49\u9644\u52a0\u5185\u5bb9\uff0c\u5305\u62ec\u56fe\u8868\u3001\u6ce8\u91ca\u7b49\u7b49\u3002\u5177\u4f53\u64cd\u4f5c\u53ef\u53c2\u8003\u5de5\u5177\u624b\u518c\u3002<\/p>\n<p>\u2013 \u81ea\u52a8\u5316\u811a\u672c\uff1a\u60a8\u53ef\u4ee5\u4f7f\u7528Python\u6216\u5176\u4ed6\u81ea\u52a8\u5316\u5de5\u5177\u6765\u5bfc\u51fa\u6570\u636e\u5e76\u5c06\u5176\u63d2\u5165Excel\u5de5\u4f5c\u8868\u4e2d\u3002\u901a\u8fc7\u7f16\u5199\u81ea\u52a8\u5316\u811a\u672c\uff0c\u60a8\u53ef\u4ee5\u5728\u4e00\u5b9a\u7a0b\u5ea6\u4e0a\u63d0\u9ad8\u6548\u7387\uff0c\u5e76\u4e14\u53ef\u4ee5\u8f7b\u677e\u5730\u4ee5\u540e\u518d\u6b21\u4f7f\u7528\u3002<\/p>\n<p>\u7ed3\u8bba<\/p>\n<p>\u5bfc\u51faExcel\u62a5\u8868\u901a\u5e38\u662f\u4e00\u4e2a\u5fc5\u8981\u800c\u8017\u65f6\u7684\u4efb\u52a1\u3002\u4f46\u662f\uff0c\u901a\u8fc7\u524d\u671f\u51c6\u5907\u5de5\u4f5c\u548c\u6b63\u786e\u7684\u5bfc\u51fa\u65b9\u6cd5\uff0c\u60a8\u53ef\u4ee5\u8f7b\u677e\u5b9e\u73b0\u9ad8\u6548\u548c\u4fbf\u6377\u7684\u6570\u636e\u5bfc\u51fa\u3002\u795d\u60a8\u5728\u60a8\u7684\u5de5\u4f5c\u548c\u4e1a\u52a1\u51b3\u7b56\u4e2d\u53d6\u5f97\u5de8\u5927\u6210\u529f\uff01<\/p>\n<p><strong>\u76f8\u5173\u95ee\u9898\u62d3\u5c55\u9605\u8bfb\uff1a<\/strong><\/p>\n<ul>\n<li>\u600e\u6837\u6279\u91cf\u5bfc\u51faexcel\u8868\u683c<\/li>\n<li>ASP.NET\u4ece\u6570\u636e\u5e93\u5bfc\u51fa\u5230EXCEL<\/li>\n<li>\u5982\u4f55\u7528access\u7684\u5b8f\u5c06\u6570\u636e\u5bfc\u51fa\u5230excel\u6587\u4ef6<\/li>\n<\/ul>\n<h3>\u600e\u6837\u6279\u91cf\u5bfc\u51faexcel\u8868\u683c<\/h3>\n<p>\u6b65\u9aa4\u4e00\uff1a\u5728\u62a5\u8868\u5c81\u5c81\u7eb3\u754c\u9762\uff0c\u70b9\u51fb\u6211\u6536\u85cf\u7684\u5e38\u7528\u529f\u80fd\u4e2d\u7684\u3010\u6279\u91cf\u5bfc\u51fa\u5230EXCEL\u3011\uff0c\u53ef\u9009\u62e9\u591a\u5f20\u62a5\u8868\u4e00\u8d77\u5bfc\u51fa\uff0c<\/p>\n<p>\u6b65\u9aa4\u4e8c\uff1a\u5de6\u952e\u70b9\u51fb\u3010\u5168\u9009\u3011\uff0c\u518d\u70b9\u51fb\u3010\u786e\u5b9a\u3011\uff0c\u5373\u53ef\u5bfc\u51fa\u6240\u6709\u62a5\u8868\u4e3aEXCEL\u8868\u683c\u3002<\/p>\n<p>\u6574\u4e2a\u9879\u76ee\u4e2d\uff1a<\/p>\n<p>\u6b65\u9aa4\u4e00\uff1a\u9879\u76ee\u7ba1\u7406\u754c\u9762\u2014\u4e4e\u6ca1\u2014\u6211\u6536\u85cf\u7684\u5e38\u7528\u529f\u80fd\u2014\u2014\u9884\u89c8\u6574\u4e2a\u9879\u76ee\u62a5\u8868\u2014\u2014\u96c0\u8001\u6279\u91cf\u5bfc\u51fa\u5230excel\u2014\u2014\u52fe\u9009\u9700\u8981\u5bfc\u51fa\u7684\u62a5\u8868\u2014\u2014\u786e\u5b9a\u3002<\/p>\n<p>\u4f60\u53ef\u4ee5\u4e86\u89e3\u4e0b\u535a\u4e3a\u5c0f\u5e2e\u8f6f\u4ef6\u673a\u5668\u4eba\u5de5\u5177\uff0c\u5b83\u80fd\u5b9e\u73b0\u81ea\u52a8\u91c7\u96c6\u8f6f\u4ef6\u754c\u9762\u663e\u793a\u7684\u5404\u79cd\u6570\u636e\uff0c\u6279\u91cf\u5bfc\u51faexcel\u8868\u683c\u6216\u6570\u636e\u5e93\u6587\u4ef6\u3002\u643a\u9646<\/p>\n<p>\u4f60\u4e0b\u8f7d\u4e00\u4e2a\u5c0f\u5e2e\u8f6f\u4ef6\u673a\u5668\u4eba\uff0c\u5728\u7535\u8111\u7aef\u6253\u5f00\u9700\u8981\u91c7\u96c6\u65e6\u865a\u7684\u8f6f\u4ef6\u7cfb\u7edf\uff0c\u7528\u5c0f\u5e2e\u8f6f\u4ef6\u673a\u5668\u4eba\u914d\u7f6e\u4e00\u4e2a\u91c7\u96c6\u5de5\u5177\uff0c\u8bbe\u7f6e\u4f60\u8981\u91c7\u96c6\u7684\u5b57\u6bb5\uff0c\u6bd4\u5982\u8ba2\u5355\u53f7\u3001\u7269\u6d41\u53f7\u3001\u5e97\u94fa\u540d\u3001\u91d1\u989d\u7b49\u7b49\uff0c\u914d\u8fa9\u8fdf\u9877\u7f6e\u597d\u540e\u4e00\u952e\u5c31\u80fd\u5bfc\u51fa\u4e86\uff0c\u5bfc\u51fa\u7684\u662fExcel\u8868\u683c\uff0c\u6240\u6709\u5b57\u6bb5\u660e\u7ec6\u4e00\u76ee\u4e86\u7136\u3002<\/p>\n<p>\u5e0c\u671b\u53ef\u4ee5\u5e2e\u52a9\u5230\u4f60\u3002<\/p>\n<h3>ASP.NET\u4ece\u6570\u636e\u5e93\u5bfc\u51fa\u5230EXCEL<\/h3>\n<p>\u7ed9\u4f60\u6e90\u7801\u81ea\u5df1\u7814\u7a76\u4e00\u4e0b\uff0c\u4e0d\u4f1a\u6765\u627e\u6211<\/p>\n<p>c#\u5bfc\u51faexcel\uff1a<\/p>\n<p>using System;<\/p>\n<p>using System.Collections.Generic;<\/p>\n<p>using System.ComponentModel;<\/p>\n<p>using System.Data;<\/p>\n<p>using System.Drawing;<\/p>\n<p>using System.Text;<\/p>\n<p>using System.Windows.Forms;<\/p>\n<p>namespace WindowsApplication3<\/p>\n<p>{<\/p>\n<p> public partial class Form1 : Form<\/p>\n<p> {<\/p>\n<p>private Microsoft.Office.Interop.Excel.Application m_objExcel = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel._Workbook m_objBook = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel.Range m_objRange = null;<\/p>\n<p>\/\/private Microsoft.Office.Interop.Excel.Font m_objFont = null;<\/p>\n<p>\/\/private Microsoft.Office.Interop.Excel.QueryTables m_objQryTables = null;<\/p>\n<p>private Microsoft.Office.Interop.Excel._QueryTable m_objQryTable = null;<\/p>\n<p>\/\/ Frequenty-used variable for optional arguments.<\/p>\n<p>private object m_objOpt = System.Reflection.Missing.Value;<\/p>\n<p>\/\/DataBase-used variable<\/p>\n<p>private System.Data.SqlClient.SqlConnection sqlConn = null;<\/p>\n<p>private string strConnect = \u201c\u6a31\u5b59\u8fdfData Source=\u2019localhost\u2019;Password = ;User ID=sa;Initial Catalog=TEST\u201d;<\/p>\n<p>private System.Data.SqlClient.SqlCommand sqlCmd = null;<\/p>\n<p>\/\/\u51ef\u6a21Sheets variable<\/p>\n<p>private double dbSheetSize = 65535;\/\u810a\u674e\/the hight limit number in one sheet<\/p>\n<p>private int intSheetTotalSize = 0;\/\/total record can divied sheet number<\/p>\n<p>private double dbTotalSize = 0;\/\/record total number<\/p>\n<p>public Form1()<\/p>\n<p>{<\/p>\n<p>InitializeComponent();<\/p>\n<p>}<\/p>\n<p>private int GetTotalSize()<\/p>\n<p>{<\/p>\n<p>sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);<\/p>\n<p>sqlCmd = new System.Data.SqlClient.SqlCommand(\u201cSelect Count(*) From PD_WORKBIL_MST\u201d, sqlConn);<\/p>\n<p>sqlConn.Open();<\/p>\n<p>dbTotalSize = (int)sqlCmd.ExecuteScalar();<\/p>\n<p>sqlConn.Close();<\/p>\n<p>return (int)Math.Ceiling(dbTotalSize \/ this.dbSheetSize);<\/p>\n<p>}<\/p>\n<p>private void DeclareExcelApp()<\/p>\n<p>{<\/p>\n<p>m_objExcel = new Microsoft.Office.Interop.Excel.Application();<\/p>\n<p>m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;<\/p>\n<p>m_objBook = (Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(m_objOpt));<\/p>\n<p>m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;<\/p>\n<p>intSheetTotalSize = GetTotalSize();<\/p>\n<p>if (intSheetTotalSize <\/p>\n<p>\u2026\u2026.\/\/\u5176\u5b83\u914d\u7f6e\u4fe1\u606f <\/p>\n<p>\u2026\u2026.\/\/\u5176\u5b83\u914d\u7f6e\u4fe1\u606f <\/p>\n<\/p>\n<p>using System; <\/p>\n<p>using System.Data; <\/p>\n<p>using System.Data.SqlClient; <\/p>\n<p>using System.Configuration; <\/p>\n<p>using System.Web; <\/p>\n<p>using System.Web.Security; <\/p>\n<p>using System.Web.UI; <\/p>\n<p>using System.Web.UI.WebControls; <\/p>\n<p>using System.Web.UI.WebControls.WebParts; <\/p>\n<p>using System.Web.UI.HtmlControls; <\/p>\n<p>using System.Reflection; <\/p>\n<p>using Microsoft.Office.Core; <\/p>\n<p>\/\/\/ <\/p>\n<p>\/\/\/ OutPutExcel \u7684\u6458\u8981\u8bf4\u660e <\/p>\n<p>\/\/\/ <\/p>\n<p>public class OutPutExcel <\/p>\n<p>{ <\/p>\n<p> public OutPutExcel() <\/p>\n<p> { <\/p>\n<p>\/\/ <\/p>\n<p>\/\/ TODO: \u5728\u6b64\u5904\u6dfb\u52a0\u6784\u9020\u51fd\u6570\u903b\u8f91 <\/p>\n<p>\/\/ <\/p>\n<p> } <\/p>\n<p> \/\/\u8bbe\u7f6e\u6587\u4ef6\u7684\u540d\u79f0\u5c5e\u6027. <\/p>\n<p> private string _xfileName; <\/p>\n<p> public string xFileName <\/p>\n<p> { <\/p>\n<p>get { return _xfileName; } <\/p>\n<p>set { _xfileName = value; } <\/p>\n<p> } <\/p>\n<p> public void OutputExcel(DataView dv, string titleStr,string sheetName) <\/p>\n<p> { <\/p>\n<p>GC.Collect(); <\/p>\n<p>Excel.Application excel;\/\/ = new Application(); <\/p>\n<p>int rowIndex = 4; <\/p>\n<p>int colIndex = 0; <\/p>\n<p>Excel._Workbook xBk; <\/p>\n<p>Excel._Worksheet xSt; <\/p>\n<p>excel = new Excel.ApplicationClass(); <\/p>\n<p>xBk = excel.Workbooks.Add(true); <\/p>\n<p>xSt = (Excel._Worksheet)xBk.ActiveSheet; <\/p>\n<p>xSt.Name = sheetName; <\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u53d6\u5f97\u8868\u683c\u4e2d\u5404\u5217\u7684\u6807\u9898 <\/p>\n<p>\/\/ <\/p>\n<p>foreach (DataColumn col in dv.Table.Columns) <\/p>\n<p>{ <\/p>\n<p>colIndex++; <\/p>\n<p>excel.Cells = col.ColumnName; <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;\/\/\u8bbe\u7f6e\u6807\u9898\u683c\u5f0f\u4e3a\u5c45\u4e2d\u5bf9\u9f50 <\/p>\n<\/p>\n<p>} <\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u53d6\u5f97\u8868\u683c\u4e2d\u7684\u6570\u636e <\/p>\n<p>\/\/ <\/p>\n<p>foreach (DataRowView row in dv) <\/p>\n<p>{ <\/p>\n<p>rowIndex++; <\/p>\n<p>colIndex = 0; <\/p>\n<p>foreach (DataColumn col in dv.Table.Columns) <\/p>\n<p>{ <\/p>\n<p> colIndex++; <\/p>\n<p> if (col.DataType == System.Type.GetType(\u201cSystem.DateTime\u201d)) <\/p>\n<p> { <\/p>\n<p> excel.Cells = (Convert.ToDateTime(row<\/p>\n<p> .ToString())).ToString(\u201cyyyy-MM-dd\u201d); <\/p>\n<\/p>\n<p> xSt.get_Range(excel.Cells, excel.Cells).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;\/\/\u8bbe\u7f6e\u65e5\u671f\u578b\u7684\u5b57\u6bb5\u683c\u5f0f\u4e3a\u5c45\u4e2d\u5bf9\u9f50 <\/p>\n<\/p>\n<p> } <\/p>\n<p> else <\/p>\n<p> if (col.DataType == System.Type.GetType(\u201cSystem.String\u201d)) <\/p>\n<p> { <\/p>\n<p>excel.Cells = \u201c\u2018\u201d + row<\/p>\n<p> .ToString(); <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;\/\/\u8bbe\u7f6e\u5b57\u7b26\u578b\u7684\u5b57\u6bb5\u683c\u5f0f\u4e3a\u5c45\u4e2d\u5bf9\u9f50 <\/p>\n<\/p>\n<p> } <\/p>\n<p> else <\/p>\n<p> { <\/p>\n<p>excel.Cells = row<\/p>\n<p> .ToString(); <\/p>\n<\/p>\n<p> } <\/p>\n<p>} <\/p>\n<p>} <\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u52a0\u8f7d\u4e00\u4e2a\u5408\u8ba1\u884c <\/p>\n<p>\/\/ <\/p>\n<p>int rowSum = rowIndex; <\/p>\n<p>\/\/int colSum = 2; <\/p>\n<p>\/\/excel.Cells = \u201c\u5408\u8ba1\u201d; <\/p>\n<\/p>\n<p>\/\/xSt.get_Range(excel.Cells, excel.Cells).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u8bbe\u7f6e\u9009\u4e2d\u7684\u90e8\u5206\u7684\u989c\u8272 <\/p>\n<p>\/\/ <\/p>\n<p>\/\/xSt.get_Range(excel.Cells, excel.Cells).Select(); <\/p>\n<\/p>\n<p>\/\/xSt.get_Range(excel.Cells, excel.Cells).Interior.ColorIndex = 19;\/\/\u8bbe\u7f6e\u4e3a\u6d45\u9ec4\u8272\uff0c\u5171\u8ba1\u670956\u79cd <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u53d6\u5f97\u6574\u4e2a\u62a5\u8868\u7684\u6807\u9898 <\/p>\n<p>\/\/ <\/p>\n<p>excel.Cells = titleStr; <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u8bbe\u7f6e\u6574\u4e2a\u62a5\u8868\u7684\u6807\u9898\u683c\u5f0f <\/p>\n<p>\/\/ <\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Font.Bold = true; <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Font.Size = 12; <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u8bbe\u7f6e\u62a5\u8868\u8868\u683c\u4e3a\u6700\u9002\u5e94\u5bbd\u5ea6 <\/p>\n<p>\/\/ <\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Select(); <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Columns.AutoFit(); <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u8bbe\u7f6e\u6574\u4e2a\u62a5\u8868\u7684\u6807\u9898\u4e3a\u8de8\u5217\u5c45\u4e2d <\/p>\n<p>\/\/ <\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Select(); <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u8bbe\u7f6e\u62a5\u8868\u8868\u683c\u91cc\u7684\u5b57\u4f53\u5927\u5c0f. <\/p>\n<p>\/\/ <\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Font.Size = 9; <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u7ed8\u5236\u8fb9\u6846 <\/p>\n<p>\/\/ <\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Borders.LineStyle = 1; <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Borders.Weight = Excel.XlBorderWeight.xlMedium;\/\/\u8bbe\u7f6e\u5de6\u8fb9\u7ebf\u52a0\u7c97 <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Borders.Weight = Excel.XlBorderWeight.xlMedium;\/\/\u8bbe\u7f6e\u4e0a\u8fb9\u7ebf\u52a0\u7c97 <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Borders.Weight = Excel.XlBorderWeight.xlMedium;\/\/\u8bbe\u7f6e\u53f3\u8fb9\u7ebf\u52a0\u7c97 <\/p>\n<\/p>\n<p>xSt.get_Range(excel.Cells, excel.Cells).Borders.Weight = Excel.XlBorderWeight.xlMedium;\/\/\u8bbe\u7f6e\u4e0b\u8fb9\u7ebf\u52a0\u7c97 <\/p>\n<\/p>\n<p>\/\/ <\/p>\n<p>\/\/\u663e\u793a\u6548\u679c <\/p>\n<p>\/\/ <\/p>\n<p>excel.Visible = false; <\/p>\n<p>\/\/xSt.Export(Server.MapPath(\u201c.\u201d)+\u201d\\\\\u201d+this.xlfile.Text+\u201d.xls\u201d,SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML); <\/p>\n<p>xBk.SaveCopyAs(HttpContext.Current.Server.MapPath(\u201c.\u201d) + \u201c\\\\\u201d + xFileName+ \u201c.xls\u201d); <\/p>\n<p>dv.Dispose(); <\/p>\n<p>xBk.Close(false, null, null); <\/p>\n<p>excel.Quit(); <\/p>\n<p>System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); <\/p>\n<p>System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); <\/p>\n<p>System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); <\/p>\n<p>xBk = null; <\/p>\n<p>excel = null; <\/p>\n<p>xSt = null; <\/p>\n<p>GC.Collect(); <\/p>\n<p>string path = HttpContext.Current.Server.MapPath(xFileName + \u201c.xls\u201d); <\/p>\n<p>System.IO.FileInfo file = new System.IO.FileInfo(path); <\/p>\n<p>HttpContext.Current.Response.Clear(); <\/p>\n<p>HttpContext.Current.Response.Charset = \u201cGB2312\u201d; <\/p>\n<p>HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; <\/p>\n<p>\/\/ \u6dfb\u52a0\u5934\u4fe1\u606f\uff0c\u4e3a\u201d\u6587\u4ef6\u4e0b\u8f7d\/\u53e6\u5b58\u4e3a\u201d\u5bf9\u8bdd\u6846\u6307\u5b9a\u9ed8\u8ba4\u6587\u4ef6\u540d <\/p>\n<p>HttpContext.Current.Response.AddHeader(\u201cContent-Disposition\u201d, \u201cattachment; filename=\u201d + HttpContext.Current.Server.UrlEncode(file.Name)); <\/p>\n<p>\/\/ \u6dfb\u52a0\u5934\u4fe1\u606f\uff0c\u6307\u5b9a\u6587\u4ef6\u5927\u5c0f\uff0c\u8ba9\u6d4f\u89c8\u5668\u80fd\u591f\u663e\u793a\u4e0b\u8f7d\u8fdb\u5ea6 <\/p>\n<p>HttpContext.Current.Response.AddHeader(\u201cContent-Length\u201d, file.Length.ToString()); <\/p>\n<p>\/\/ \u6307\u5b9a\u8fd4\u56de\u7684\u662f\u4e00\u4e2a\u4e0d\u80fd\u88ab\u5ba2\u6237\u7aef\u8bfb\u53d6\u7684\u6d41\uff0c\u5fc5\u987b\u88ab\u4e0b\u8f7d <\/p>\n<p>HttpContext.Current.Response.ContentType = \u201capplication\/ms-excel\u201d; <\/p>\n<p>\/\/ \u628a\u6587\u4ef6\u6d41\u53d1\u9001\u5230\u5ba2\u6237\u7aef <\/p>\n<p>HttpContext.Current.Response.WriteFile(file.FullName); <\/p>\n<p>\/\/ \u505c\u6b62\u9875\u9762\u7684\u6267\u884c <\/p>\n<p>HttpContext.Current.Response.End(); <\/p>\n<p> } <\/p>\n<p>}<\/p>\n<p>\u8c03\u7528\u4e3e\u4f8b: <\/p>\n<p> protected void btnOutPut_Click(object sender, EventArgs e) <\/p>\n<p> { <\/p>\n<p>string ParentCode = string.Empty; <\/p>\n<p>string projectunitcode = string.Empty; <\/p>\n<p>\/\/string MaterailCode = string.Empty ; <\/p>\n<p>if (Request.QueryString != null) <\/p>\n<\/p>\n<p>{ <\/p>\n<p>if (Request.QueryString.ToString() != string.Empty) <\/p>\n<\/p>\n<p>{ <\/p>\n<p> ParentCode = Request.QueryString.ToString(); <\/p>\n<\/p>\n<p>} <\/p>\n<p>} <\/p>\n<p>if (Request.QueryString != null) <\/p>\n<\/p>\n<p>{ <\/p>\n<p>if (Request.QueryString.ToString() != string.Empty) <\/p>\n<\/p>\n<p>{ <\/p>\n<p> projectunitcode = Request.QueryString.ToString(); <\/p>\n<\/p>\n<p>} <\/p>\n<p>} <\/p>\n<p>CpcecDB cpcecdb = new CpcecDB(); <\/p>\n<p>DataSet ds = cpcecdb.getCodeMaterialInfoss(ParentCode, projectunitcode); <\/p>\n<p>DataTable dt = ds.Tables; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8bbe\u5907\u7f16\u53f7\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8bbe\u5907\u540d\u79f0\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u751f\u4ea7\u5382\u7f16\u7801\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u751f\u4ea7\u5382\u540d\u79f0\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u5408\u540c\u4ea4\u8d27\u65e5\u671f\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8c03\u6574\u4ea4\u8d27\u65e5\u671f\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u5b9e\u9645\u5b8c\u6210\u65e5\u671f\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8bbe\u5907\u72b6\u6001\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8fdb\u5ea6\u60c5\u51b5\u201d; <\/p>\n<\/p>\n<p>dt.Columns.ColumnName = \u201c\u8d28\u91cf\u72b6\u51b5\u201d; <\/p>\n<\/p>\n<p>OutPutExcel outputexcel = new OutPutExcel(); <\/p>\n<p>outputexcel.xFileName = \u201chuangrun124\u201d; <\/p>\n<p>outputexcel.OutputExcel(dt.DefaultView, \u201c\u62a5\u8868\u201d,\u201d\u201d); <\/p>\n<\/p>\n<p> } <\/p>\n<p>\u7b2c\u4e09\u4e2a\u4eba\u7684\uff1a<\/p>\n<p>\u524d\u53f0\u4ee3\u7801 <\/p>\n<p>HTML code<\/p>\n<p> Untitled Page<\/p>\n<\/p>\n<\/p>\n<p>\u540e\u53f0\u4ee3\u7801 <\/p>\n<p>C# code<\/p>\n<p>using System;<\/p>\n<p>using System.Data;<\/p>\n<p>using System.Configuration;<\/p>\n<p>using System.Web;<\/p>\n<p>using System.Web.Security;<\/p>\n<p>using System.Web.UI;<\/p>\n<p>using System.Web.UI.WebControls;<\/p>\n<p>using System.Web.UI.WebControls.WebParts;<\/p>\n<p>using System.Web.UI.HtmlControls;<\/p>\n<p>using System.IO;<\/p>\n<p>public partial class _Default : System.Web.UI.Page <\/p>\n<p>{<\/p>\n<p> db mydb = new db();<\/p>\n<p> protected void Page_Load(object sender, EventArgs e)<\/p>\n<p> {<\/p>\n<p>getData();<\/p>\n<p> }<\/p>\n<p> public void getData()<\/p>\n<p> {<\/p>\n<p>GridView1.DataSource = mydb.getData();<\/p>\n<p>GridView1.DataBind();<\/p>\n<p> }<\/p>\n<p> public override void VerifyRenderingInServerForm(Control control)<\/p>\n<p> {<\/p>\n<p> }<\/p>\n<p> protected void btToExcel_Click(object sender, EventArgs e)<\/p>\n<p> {<\/p>\n<p>db.ExportExcel(GridView1);<\/p>\n<p> }<\/p>\n<p>}<\/p>\n<p>using System;<\/p>\n<p>using System.Data;<\/p>\n<p>using System.Data.SqlClient;<\/p>\n<p>using System.Configuration;<\/p>\n<p>using System.Web;<\/p>\n<p>using System.Web.Security;<\/p>\n<p>using System.Web.UI;<\/p>\n<p>using System.IO;<\/p>\n<p>using System.Web.UI.WebControls;<\/p>\n<p>using System.Web.UI.WebControls.WebParts;<\/p>\n<p>using System.Web.UI.HtmlControls;<\/p>\n<p>\/\/\/ <\/p>\n<p>\/\/\/ Summary description for db<\/p>\n<p>\/\/\/ <\/p>\n<p>public class db<\/p>\n<p>{<\/p>\n<p> public string strCn = ConfigurationManager.ConnectionStrings.ConnectionString;<\/p>\n<\/p>\n<p> public db()<\/p>\n<p> {<\/p>\n<p>\/\/<\/p>\n<p>\/\/ TODO: Add constructor logic here<\/p>\n<p>\/\/<\/p>\n<p> }<\/p>\n<p> public DataTable getData()<\/p>\n<p> {<\/p>\n<p>DataTable dt = new DataTable();<\/p>\n<p>SqlDataAdapter sqlAda = new SqlDataAdapter(\u201cselect id ,name from tb4 \u201c, strCn);<\/p>\n<p>sqlAda.Fill(dt);<\/p>\n<p>return dt;<\/p>\n<p> }<\/p>\n<p> \/\/\/ <\/p>\n<p> \/\/\/ \u5bfc\u51fa\u6210\u4e3aExcel<\/p>\n<p> \/\/\/ <\/p>\n<p> \/\/\/ \u76ee\u6807GridView<\/p>\n<p> public static void ExportExcel(GridView exportTargetGridView)<\/p>\n<p> {<\/p>\n<p>HttpContext.Current.Response.ClearContent();<\/p>\n<p>HttpContext.Current.Response.Charset = \u201cGB2312\u201d;<\/p>\n<p>HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;<\/p>\n<p>HttpContext.Current.Response.AddHeader(\u201ccontent-disposition\u201d, \u201cattachment; filename=MyExcelFile.doc\u201d);<\/p>\n<p>HttpContext.Current.Response.ContentType = \u201capplication\/word\u201d;<\/p>\n<p>StringWriter sw = new StringWriter();<\/p>\n<p>HtmlTextWriter htw = new HtmlTextWriter(sw);<\/p>\n<p>exportTargetGridView.RenderControl(htw);<\/p>\n<p>HttpContext.Current.Response.Write(sw.ToString());<\/p>\n<p>HttpContext.Current.Response.End();<\/p>\n<p> }<\/p>\n<p>}<\/p>\n<p>\u76f4\u63a5\u8f93\u51fa\uff0c\u5148\u8f93\u51fa\u4e00\u4e2aExcel\u5934\uff0c\u7136\u540e\u8f93\u51fa\u5185\u5bb9\uff01\u4e0d\u9700\u8981\u5f15\u7528!<\/p>\n<h3>\u5982\u4f55\u7528access\u7684\u5b8f\u5c06\u6570\u636e\u5bfc\u51fa\u5230excel\u6587\u4ef6<\/h3>\n<p>\u4e0d\u8981\u5728access\u91cc\u9762\u5199\u8fa9\u79df\u4e4e\u660e\u5b8f\uff0c\u53ea\u643a\u9877\u5146\u628aaccess\u5f53\u6210\u4e00\u4e2a\u6570\u636e\u5e93<\/p>\n<p>\u7528excel\u91cc\u53bb\u94fe\u63a5access\u6293\u6570\u636e<\/p>\n<p>\u5bfc\u51fa\u5230 Microsoft Excel \u6216\u5176\u4ed6\u7535\u5b50\u8868\u683c\u7a0b\u5e8f<\/p>\n<p>\u6709\u4e09\u79cd\u65b9\u6cd5\u53ef\u4ee5\u5728 Microsoft Excel \u6216\u5176\u6ed1\u6d3e\u4ed6\u7535\u5b50\u8868\u683c\u7a0b\u5e8f\u4e2d\u4f7f\u7528 Microsoft Access \u6570\u636e\uff1a<\/p>\n<p>\u53ef\u4ee5\u5c06\u6570\u636e\u8868\u4f5c\u4e3a\u8010\u8ba9\u731c\u65e0\u683c\u5f0f\u6570\u636e\u5bfc\u51fa\u5230 Microsoft Excel \u6216\u5176\u4ed6\u7535\u5b50\u8868\u683c\u7a0b\u5e8f\u4e2d\u3002<\/p>\n<p>\u53ef\u4ee5\u660c\u578b\u5c06\u6570\u636e\u8868\u3001\u7a97\u4f53\u6216\u62a5\u8868\u7684\u5bfc\u51fa\u76f4\u63a5\u4f5c\u4e3a Excel (.xls) \u6587\u4ef6\u6216\u5de5\u4f5c\u8868\u4fdd\u5b58\u3002<\/p>\n<p>\u53ef\u4ee5\u5c06\u6570\u636e\u8868\u3001\u7a97\u4f53\u6216\u62a5\u8868\u7684\u5bfc\u51fa\u4f5c\u4e3a Excel(.xls) \u6587\u4ef6\u4fdd\u5b58\u5e76\u81ea\u52a8\u8f7d\u5165\u5230 Microsoft Excel \u4e2d\uff0c\u5176\u65b9\u6cd5\u662f\uff1a\u4f7f\u7528\u201c\u5de5\u5177\u201d\u83dc\u5355\u201cOffice \u94fe\u63a5\u201d\u5b50\u83dc\u5355\u4e2d\u7684\u201c\u7528 MS Excel \u5206\u6790\u201d\u547d\u4ee4\u3002<\/p>\n<p>\u5728\u540e\u4e24\u79cd\u60c5\u51b5\u4e0b\uff0c\u53ef\u4ee5\u4fdd\u7559\u5927\u591a\u6570\u683c\u5f0f\uff0c\u4f8b\u5982\u5b57\u4f53\u548c\u989c\u8272\u3002\u62a5\u8868\u7684\u7ec4\u7ea7\u522b\u4f5c\u4e3a Microsoft Excel \u5927\u7eb2\u7ea7\u522b\u4fdd\u5b58\uff0c\u7a97\u4f53\u4ee5\u6570\u636e\u7684\u8868\u7684\u5f62\u5f0f\u4fdd\u5b58\u3002<\/p>\n<p>\u6570\u636e\u5e93\u62a5\u8868\u5bfc\u51faexcel\u7684\u4ecb\u7ecd\u5c31\u804a\u5230\u8fd9\u91cc\u5427\uff0c\u611f\u8c22\u4f60\u82b1\u65f6\u95f4\u9605\u8bfb\u672c\u7ad9\u5185\u5bb9\uff0c\u66f4\u591a\u5173\u4e8e\u6570\u636e\u5e93\u62a5\u8868\u5bfc\u51faexcel,\u9ad8\u6548\u4fbf\u6377\uff01\u6570\u636e\u5e93\u62a5\u8868\u5bfc\u51faExcel\u6559\u7a0b,\u600e\u6837\u6279\u91cf\u5bfc\u51faexcel\u8868\u683c,ASP.NET\u4ece\u6570\u636e\u5e93\u5bfc\u51fa\u5230EXCEL,\u5982\u4f55\u7528access\u7684\u5b8f\u5c06\u6570\u636e\u5bfc\u51fa\u5230excel\u6587\u4ef6\u7684\u4fe1\u606f\u522b\u5fd8\u4e86\u5728\u672c\u7ad9\u8fdb\u884c\u67e5\u627e\u5594\u3002<\/p>\n<p><a href=\"http:https:\/\/idc.net\/\">\u9999\u6e2f\u670d\u52a1\u5668<\/a>\u9996\u9009\u540e\u6d6a\u4e91\uff0c2H2G\u9996\u670810\u5143\u5f00\u901a\u3002<br \/>\u540e\u6d6a\u4e91\uff08www.IDC.Net\uff09\u63d0\u4f9b\u7b80\u5355\u597d\u7528\uff0c\u4ef7\u683c\u539a\u9053\u7684\u9999\u6e2f\/\u7f8e\u56fd\u4e91\u670d\u52a1\u5668\u548c\u72ec\u7acb\u670d\u52a1\u5668\u3002IDC+ISP+ICP\u8d44\u8d28\u3002ARIN\u548cAPNIC\u4f1a\u5458\u3002\u6210\u719f\u6280\u672f\u56e2\u961f15\u5e74\u884c\u4e1a\u7ecf\u9a8c\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u4f01\u4e1a\u7ba1\u7406\u3001\u6570\u636e\u5206\u6790\u548c\u4e1a\u52a1\u51b3\u7b56\u4e2d\uff0c\u6570\u636e\u662f\u6700\u91cd\u8981\u7684\u6709\u529b\u5de5\u5177\u3002\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\uff0c\u6570\u636e\u9700\u8981\u4ece\u6570\u636e\u5e93\u5bfc\u51fa\u62a5\u8868\uff0c\u4ee5\u4f9b\u8fdb\u4e00\u6b65 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7038],"tags":[],"class_list":["post-150600","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/150600","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/comments?post=150600"}],"version-history":[{"count":0,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/150600\/revisions"}],"wp:attachment":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media?parent=150600"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/categories?post=150600"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/tags?post=150600"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}