Question

asp.net从sql数据库几个不同的表中导出在同一个Excel表格

有一个导出Excel表格的按钮，以点击按钮，就将数据库两个表的内容，导出到Excel表中，注意是两个表，例如table1，table2两个表的内容导入的一个Excel表格中。我是菜鸟，请高手指教

Answer 1

public bool ExportExcel(DataTable myDataTable, string saveFileName)
{
try
{
if (saveFileName.IndexOf(":") < 0) return false; //被点了取消

Excel.Application xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value;
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return false;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range;
range = worksheet.Columns;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平对齐设置
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直对齐设置
range.ColumnWidth = 15;
#region 写入字段
worksheet.Cells[1, 1] = "HostID";
worksheet.Cells[1, 2] = "HostName";
worksheet.Cells[1, 3] = "HostType";
worksheet.Cells[1, 4] = "Channels";
worksheet.Cells[1, 5] = "ServerIP";
worksheet.Cells[1, 6] = "ServerPort";
worksheet.Cells[1, 7] = "UserID";
worksheet.Cells[1, 8] = "UserPass";
worksheet.Cells[1, 9] = "ValidDate";
#endregion
//写入数值

for (int r = 0; r < myDataTable.Rows.Count; r++)
{
worksheet.Cells[r + 2, 1] = myDataTable.Rows[r]["HostID"];
worksheet.Cells[r + 2, 2] = myDataTable.Rows[r]["HostName"];
worksheet.Cells[r + 2, 3] = myDataTable.Rows[r]["HostType"];
worksheet.Cells[r + 2, 4] = myDataTable.Rows[r]["Channels"];
worksheet.Cells[r + 2, 5] = myDataTable.Rows[r]["ServerIP"];
worksheet.Cells[r + 2, 6] = myDataTable.Rows[r]["ServerPort"];
worksheet.Cells[r + 2, 7] = myDataTable.Rows[r]["UserID"];
worksheet.Cells[r + 2, 8] = myDataTable.Rows[r]["UserPass"];
worksheet.Cells[r + 2, 9] = myDataTable.Rows[r]["ValidDate"];

}
worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.Close(missing, missing, missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
catch (Exception ex)
{
return false;
}
}
myDataTable 是一个datatable 你把2个表的数据组成一个datable 循环出来就好了

追问

table1(姓名,班级,院系...)
table2（出生日期,家庭住址,求职意向...）
导出的Excel表格（姓名，班级，家庭住址）
这样可以实现吗？要是再增加几个表格也可以实现吗

追答

可以啊
你只要把这个几个表的数据读取出来 放在一个datable里面 就可以了

Answer 2

1.联查sql语句:
select * from table1 a,table2 b where a.条件1=b.条件2
将查询后生成的表格保存在一个网页中.
2.导出excel
在生成的页面头部加入语句:
<%
response.ContentType ="application/vnd.ms-excel"
Response.AddHeader "content-disposition", "inline; filename = 你要导出的excel文件名.xls"
%>
3.从其他页加入按钮或链接调用此网页,即可.

Answer 3

1.联查sql语句:
select
*
from
table1
a,table2
b
where
a.条件1=b.条件2
将查询后生成的表格保存在一个网页中.
2.导出excel
在生成的页面头部加入语句:
<%
response.ContentType
="application/vnd.ms-excel"
Response.AddHeader
"content-disposition",
"inline;
filename
=
你要导出的excel文件名.xls"
%>
3.从其他页加入按钮或链接调用此网页,即可.

Answer 4

首先你需要两表联查，然后存入datatable中，在使用LZ哪位朋友的方法。

追问

怎么联查呢