我使用以下代码将数据库表中的字段导出到 Excel 中。我想要做的是能够编写一条 SQL 语句从多个表中检索字段并将其导出到 Excel 中。这段代码只允许我导出一张表。另外,如何显示保存提示对话框?示例代码将不胜感激 - 非常感谢!
protected void export_Click(object sender, EventArgs e)
{
string sql = null;
string data = null;
string path = save_as.Text;
int i = 0;
int j = 0;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
SqlConnection cnn = new SqlConnection(GetConnectionString());
cnn.Open();
sql = "SELECT Story, CreationDate FROM Story";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}
xlWorkBook.SaveAs(path+".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
//MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
在 ASP.NET 环境中执行办公自动化并不安全。相反,您应该生成 csv、html 表或 xlsx 文件并将其发送给客户端。对于 xlsx,您可以使用 Office Open XML SDK 来稍微简化一些事情。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)