c# 里面 导数据到 EXCEl 速度很慢是怎么回事 ？

Question

语句如下 总共110列  读到100行时要1分多钟 200行时要 3分多钟 1千行 就不动了是怎么回事 哪位高手看看是怎么回事 有没有什么好的方法可以当 让导数据到excel时速度会快一点 
goodsql //表示sql语句 DataView dvQuery = Query.DoQuery(goodsql);  


if(dvQuery==null)
{
//INF-00001:{0}沒有資料可顯示！
throw new Exception(UIMsg.GetMessageString("INF-00001",""));
}
dvQuery =TrimDataView(dvQuery);

for(int i=0;i<dvQuery.Count;i++) //行數
{
int InsertIndex = 3;
for(int j=0;j<dvQuery.Table.Columns.Count;j++)
{

sqlExcelColumn = "UPDATE [{0}${1}{2}:{3}{2}] SET ";
sqlExcelColumn = string.Format(sqlExcelColumn,"Sheet1", GetExcelColumnName(InsertIndex), Convert.ToString(19+i),GetExcelColumnName(InsertIndex));
sqlExcelColumn += "F1='"+dvQuery[i][dvQuery.Table.Columns[j].Caption].ToString().Trim()+"'";
objCmd.CommandText = sqlExcelColumn;
objCmd.ExecuteNonQuery();
InsertIndex =InsertIndex+1;


}
}
}

Answer 1

private void ImageButton1_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
Response.Clear(); 
Response.Buffer= true; 
Response.Charset="utf-8";    
Response.AppendHeader("Content-Disposition","attachment;filename=新建 Microsoft Excel 工作表.xls"); 
Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为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);
this.DG.RenderControl(oHtmlTextWriter);  //----DB(datagrid)
Response.Write(oStringWriter.ToString());
Response.End();
}这个很快。

Answer 2

jerryma888888 你这个方法快是快但是不能输入到EXCEl模板里面

Answer 3

我的代码和你类似,但很快阿,600条左右数据,1-2秒以内搞定.....            using (OleDB oledb = new OleDB())
            {
                oledb.InitConnection(OleDB.ExcelConnectionString(file, true));
                OleDbParameter[] p = new OleDbParameter[8];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Text = string.Format("Exproting data {0}/{1}", i, dt.Rows.Count);
                    Application.DoEvents();
                    string ref_num = DataBase.GetValue<string>(dt.Rows[i], "Ref_Number", "");
                    if (!ref_num.StartsWith("39"))
                    {
                        string pay_method = DataBase.GetValue<string>(dt.Rows[i], "Pay_Method", "");
                        string trans_code = DataBase.GetValue<string>(dt.Rows[i], "Transact_Code", "");
                        string ap_number = DataBase.GetValue<string>(dt.Rows[i], "AP_Number", "");
                        string country_code = DataBase.GetValue<string>(dt.Rows[i], "Country_Code", "");
                        string account = DataBase.GetValue<string>(dt.Rows[i], "Account", "");
                        string currency = DataBase.GetValue<string>(dt.Rows[i], "Currence_Code", "");
                        decimal amount = DataBase.GetValue<decimal>(dt.Rows[i], "Amount", 0);
                        p[0] = OleDB.NewParameter("@ref_num", ref_num, OleDbType.VarChar);
                        p[1] = OleDB.NewParameter("@pay_method", pay_method, OleDbType.VarChar);
                        p[2] = OleDB.NewParameter("@trans_code", trans_code, OleDbType.VarChar);
                        p[3] = OleDB.NewParameter("@ap_number", ap_number, OleDbType.VarChar);
                        p[4] = OleDB.NewParameter("@country_code", country_code, OleDbType.VarChar);
                        p[5] = OleDB.NewParameter("@account", account, OleDbType.VarChar);
                        p[6] = OleDB.NewParameter("@currence_code", currency, OleDbType.VarChar);
                        p[7] = OleDB.NewParameter("@amount", amount.ToString("0.00"), OleDbType.VarChar);
                        count += oledb.DoCommand("insert into [Sheet1$](Ref_Number,Pay_Method,Transact_Code,AP_Number,Country_Code,ACCT,CCY,AMT) values(@ref_num,@pay_method,@trans_code,@ap_number,@country_code,@account,@currence_code,@amount)", true, p);
                    }
                }
                oledb.CloseConnection();
            }
        /// <summary>
        /// 执行指定的SQL语句或存储过程
        /// </summary>
        /// <param name="oledbCmdLine">SQL语句或存储过程名</param>
        /// <param name="connAlwaysOpen">吃否保持Connection对象为打开状态</param>
        /// <param name="param">包含DB参数的数组，可以为 null</param>
        /// <returns>改变的数据条数</returns>
        public int DoCommand(string oledbCmdLine, bool connAlwaysOpen, params OleDbParameter[] param)
        {
            OleDbCommand oledbcmd = new OleDbCommand(oledbCmdLine, oledbconn);
            if (_comtype != _tmpcomtype)
            {
                oledbcmd.CommandType = _tmpcomtype;
                _tmpcomtype = _comtype;
            }
            else
            {
                oledbcmd.CommandType = _comtype;
            }
            CopyOleDbParameter(param, oledbcmd.Parameters);
            int ret = 0;
            try
            {
                if (!connAlwaysOpen)
                {
                    if (oledbconn.State != ConnectionState.Closed)
                        oledbconn.Close();
                    oledbconn.Open();
                }
                else
                {
                    if (oledbconn.State == ConnectionState.Closed)
                        oledbconn.Open();
                }
                ret = oledbcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (Settings.Config.HideException)
                    ret = 0;
                else
                {
                    ExceptionSQL ex1 = new ExceptionSQL(ex, oledbCmdLine, param);
                    ex1.SQL_Command = oledbcmd;
                    throw ex1;
                }
            }
            finally
            {
                if (!connAlwaysOpen)
                    oledbconn.Close();
                oledbcmd.Parameters.Clear();
            }
            return ret;
        }

Answer 4

我在excel里面要的效果 第一行 是从C19开始写 DI19结束
                       第二行是从C20开始写 DI20结束
                       第三行是从C21开始写 DI21结束
                        .........................以上面的这种格式忘excel写数据
在oracle里面也是对应上面的格式读出来的                                     哪位知道该怎么弄

Answer 5

var idTmr = ""; 
function copy(tabid) 
{ 
var oControlRange = document.body.createControlRange(); 
oControlRange.add(tabid,0); 
oControlRange.select(); 
document.execCommand("Copy"); 
} 
function toExcel(tabid){ 
copy(tabid); 
try 
{ 
var xls = new ActiveXObject( "Excel.Application" ); 
} 
catch(e) 
{ 
alert( "Excel没有安装或浏览器设置不正确.请启用所有Active控件和插件"); 
return false; 
} 
xls.visible = true; 
var xlBook = xls.Workbooks.Add; 
var xlsheet = xlBook.Worksheets(1); 
xlBook.Worksheets(1).Activate; 
for(var i=0;i<tabid.rows(0).cells.length;i++){ 
xlsheet.Columns(i+1).ColumnWidth=15; 
} 
xlsheet.Paste; xlsheet.Range(xlsheet.Cells(2,1),xlsheet.Cells(tblAttribute.rows.length+2,tblAttribute.rows(2).cells.length)).Borders.LineStyle=1;xls=null; 
idTmr = window.setInterval("Cleanup();",1); 
} 
function Cleanup() { 
window.clearInterval(idTmr); 
CollectGarbage(); 
} 
</script>

Answer 6

把它当数据表操作，动态拼接SQL语句，如第一行用18个空值占位，第二行用19个空值占位等。

Answer 7

(1)先构造好要导数据的结构stringBuffer
(2)然后使用系统粘贴板:
System.Windows.Forms.Clipboard.SetDataObject("");
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
((Excel.Range)SheetX.Cells[j,1]).Select();                  //粘贴到第3列
SheetX.Paste(oMissing,oMissing);
System.Windows.Forms.Clipboard.SetDataObject("");