跪求解决DATAGRID导出数据到EXCEL

[hxy830501]

网 上找了好多,但是都不能用,哪位GG能帮帮MM啊!能不能留下QQ

[fungchou]

最简单的方法，一条记录一行，字段用逗号分隔，导成文本文件，后缀名改成xls

[Dennis_maomao]

public void ToExcel(System.Web.UI.Control ctl,string FileName)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+FileName);
HttpContext.Current.Response.Charset ="UTF-8";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}

[huangbznet]

楼上的太简单了吧,你的这样导出的话,把页面的控件什么的都导出来了,有没有其他的方法的,只把数据导出来,自定义表格的那种导出

[feifeiya1458]

Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet

Try

excelBook = excelApp.Workbooks.Open(Application.StartupPath & "\1.xls")
excelWorksheet = excelBook.Sheets.Item(1)
excelApp.Visible = True
excelWorksheet.Activate()



With excelWorksheet
'合并EXCEL表中的第一行的第一列到第八列
.Range(.Cells(1, 1), .Cells(1, 8)).Merge()
'合并EXCEL表中的第一行的第一列到第八列
.Range(.Cells(2, 1), .Cells(2, 8)).Merge()
'.Cells.HorizontalAlignment = Right
'往两行里面写数据并定义它的格式
.Cells(1, 1).Value = classname & year & term & "课表"
.Cells(1, 1).Font.Size = 20
.Cells(1, 1).Font.bold = True
.Cells(2, 1).Value = Today.ToString

End With
你自己看这段代码，看对你有没有用咯！

[blackhero]

推荐控件 FarPoint

[海边的狼]

提供两种方案
1，直接用流写
如下
StreamWriter sr=new StreamWriter(saveFilePath,false,System.Text.Encoding.Default);
string writeline=FielValue0+Convert.ToChar(9);
...
writeline+=FielValuen+Convert.ToChar(9);
sr.WriteLine(writeline);
2,用oledb连接，跟操作数据一样插入信息
string ole_connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source="+saveFilePath;
OleDbConnection ole_conn = new OleDbConnection(ole_connstring);
ole_conn.Open();
string creatsql="...";
string Insertsql="...";
OleDbCommand da=new OleDbCommand(creatsql,ole_conn);
da.ExecuteNonQuery();
da=new OleDbCommand(Insertsql,ole_conn);
da.ExecuteNonQuery();
在你执行创建表的同时，系统如果发现Excel文件不存在，就自动完成了Excel文件的创建。这点如果没接触过的人，可能会不知道的。

[约修亚]

Protected Function CreateRpts(ByVal ReportID As String, _
ByVal cQuery As clsQuery, ByVal fileName As String, _
ByVal fileType As ReportFileType, _
ByVal cnString As String) As String Implements iABMRpts.CreateRpts
'
' generate the excel file
'
Dim CD As New APLNetShared2.clsSQLData(cnString)
Dim cPB As New APLNetShared2.clsDataParameterListBuilder
Dim ds As New System.Data.DataSet
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim bk As Microsoft.Office.Interop.Excel.Workbook
Dim sh As Microsoft.Office.Interop.Excel.Worksheet
Dim nRow As Integer
Dim sSQL As New System.Text.StringBuilder

bk = xlApp.Workbooks.Add
sh = bk.Worksheets(1)
nRow = 1
'sSQL = " SELECT Policy.SocialSecurityNo,PolicyHolder.FirstName ,PolicyHolder.LastName," & _
'"billingInfo.type,paymentmethod.paymentmethod,policy.policystatus,Policy.NewBusinessPeriod," & _
'"policy.premium6,Policy.NextBillAmount,Policy.RenewalPeriod,Policy.NextBillDate,Policy.Premium2," & _
'"Policy.RenewalPeriod,MAX(TransactionHistory.TransactionDate) as TransactionDate,PolicyHolder.LastName,Policy.Active" & _
'"FROM policy ,PolicyHolder TransactionHistory,billingInfo,paymentmethod " & _
'"WHERE Policy.SocialSecurityNo = PolicyHolder.SocialSecurityNo AND Policy.PolicyNo = TransactionHistory.SourceFile "

sSQL.Append("SELECT Policy.SocialSecurityNo,")
sSQL.Append(" PolicyHolder.FirstName+' '+PolicyHolder.LastName AS FullName,")
sSQL.Append(" BillingInfo.Type, PaymentMethod.PaymentMethod, Policy.PolicyStatus,")
sSQL.Append(" Policy.NewBusinessPeriod, Policy.Premium6, Policy.NextBillAmount,")
sSQL.Append(" Policy.RenewalPeriod, Policy.NextBillDate, Policy.Premium2,")
sSQL.Append(" MAX(TransactionHistory.TransactionDate) AS MaxTransactionDate,")
sSQL.Append(" PolicyHolder.LastName, Policy.Active")
sSQL.Append(" FROM Policy")
sSQL.Append(" LEFT JOIN PolicyHolder ON Policy.SocialSecurityNo=PolicyHolder.SocialSecurityNo")
sSQL.Append(" LEFT JOIN TransactionHistory ON Policy.PolicyNo=TransactionHistory.SourceFile")
sSQL.Append(" LEFT JOIN BillingInfo on Policy.RecordID=BillingInfo.PolicyID")
sSQL.Append(" LEFT JOIN PaymentMethod ON Policy.PaymentMethodID=PaymentMethod.RecordID")

cQuery.CreateQueryString()
If cQuery.QueryString.Trim.Length > 0 Then
sSQL.Append(" WHERE " & cQuery.CreateQueryString)
cPB = cQuery.ParameterBuilder
End If

sSQL.Append(" GROUP BY")
sSQL.Append(" Policy.SocialSecurityNo, PolicyHolder.FirstName, PolicyHolder.LastName,")
sSQL.Append(" BillingInfo.Type, PaymentMethod.PaymentMethod, Policy.PolicyStatus,")
sSQL.Append(" Policy.NewBusinessPeriod, Policy.Premium6, Policy.NextBillAmount,")
sSQL.Append(" Policy.RenewalPeriod, Policy.NextBillDate, Policy.Premium2, Policy.Active")

ds = CD.ExecDataSet(sSQL.ToString, cPB.ParameterList)

sh.Range("A" & nRow).Value = "ID"
sh.Range("B" & nRow).Value = "FULL_NAME"
sh.Range("C" & nRow).Value = "MEMBER_TYPE"
sh.Range("D" & nRow).Value = "CATEGORY"
sh.Range("E" & nRow).Value = "STATUS"
sh.Range("F" & nRow).Value = "JOIN_DATE"
sh.Range("G" & nRow).Value = "OVER_UNDER_PAY"
sh.Range("H" & nRow).Value = "AMOUNT"
sh.Range("I" & nRow).Value = "PAID_THRU"
sh.Range("J" & nRow).Value = "BILL_DATE"
sh.Range("K" & nRow).Value = "BILL_FLAG"
sh.Range("L" & nRow).Value = "BILL_DAY"
sh.Range("M" & nRow).Value = "DUES_AMOUNT"
sh.Range("N" & nRow).Value = "PAID_TILL"
sh.Range("O" & nRow).Value = "LAST_DEBITED"
sh.Range("P" & nRow).Value = "LAST_NAME"
sh.Range("Q" & nRow).Value = "DIRECT_DEBIT"
sh.Range("R" & nRow).Value = "ACTIVE"

If ds.Tables(0).Rows.Count > 0 Then
For Each row As System.Data.DataRow In ds.Tables(0).Rows
nRow += 1
sh.Range("A" & nRow).Value = row("SocialSecurityNo")
sh.Range("B" & nRow).Value = row("FullName")
sh.Range("C" & nRow).Value = row("Type")
sh.Range("D" & nRow).Value = row("PaymentMethod")
sh.Range("E" & nRow).Value = row("PolicyStatus")
sh.Range("F" & nRow).Value = row("NewBusinessPeriod")
sh.Range("G" & nRow).Value = row("Premium6")
sh.Range("H" & nRow).Value = row("NextBillAmount")
sh.Range("I" & nRow).Value = row("RenewalPeriod")
sh.Range("J" & nRow).Value = row("NextBillDate")
sh.Range("K" & nRow).Value = ""
sh.Range("L" & nRow).Value = ""
sh.Range("M" & nRow).Value = row("Premium2")
sh.Range("N" & nRow).Value = row("RenewalPeriod")
sh.Range("O" & nRow).Value = row("MaxTransactionDate")
sh.Range("P" & nRow).Value = row("LastName")
sh.Range("Q" & nRow).Value = row("Active")
sh.Range("R" & nRow).Value = row("Active")
Next
End If

bk.SaveAs(fileName)

bk.Close(False)
xlApp = Nothing

Return "Success"

End Function

[约修亚]

查询的sql语句能更改，希望能对你有所帮助

[linfuguo]

可以参考一下我写的ExcelManager类库
http://www.cnblogs.com/linfuguo/archive/2006/08/19/480976.html
中的相关Excel管理问题！

[linfuguo]

另外，坛子已经有好多，你可以搜一下Excel就可以了！

[异想天开的妄想狂]

站起来讲话　不会没人笑话你　就这事情还用跪着求？BS

[ltolll]

"不会没人笑话你"?
双重否定啊.

我想还是应该从数据源取数据.