Question

asp.net怎么实现点击导出按钮将查询到的datatable中的数据直接导出到excel文件。

查询数据得到datatable，现在的问题是怎么不经过gridview等控件，直接遍历datatable将数据导出到excel文件呢？

Answer 1

StringWriter sw = new StringWriter();
sw.WriteLine("Schemeid,MailCount");//标题
foreach (DataRow dr in dt.Rows)
{//字段名
sw.WriteLine(dr["Schemeid"].ToString() + "," + dr["MailCount"].ToString());
}
sw.Close();

string name = DateTime.Now.ToString() + ".csv";//以当前时间为excel表命名

Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));

Response.ContentType = "vnd.ms-excel.numberformat:yyyy-MM-dd ";

Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

Response.Write(sw);

Response.End();

Answer 2

lalcount.Visible = true;
lalcount.Text = "正在导出,请稍候....";
try
{
string filename;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.Filter = "excel文档(*.xls)|*.xls";
//sfd.OpenFile();
sfd.ShowDialog();
if (sfd.FileName != "")
{
if (sfd.FileName.LastIndexOf(".xls") <= 0)
{
sfd.FileName = sfd.FileName + ".xls";
}
filename = sfd.FileName;
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;

Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);//工作薄
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//工作页
DataTable dt1 = (DataTable)dataGrid.DataSource;\\请注意这里
float percent = 0;
long rowRead = 0;
if (dt1 == null)
{
MessageBox.Show("没有数据要导出Excel,请核实");
return;
}
long totalCount = dt1.Rows.Count;
//this.progressBar1.Visible = true;
for (int i = 0; i < dt1.Rows.Count; i++)
{
//this.progressBar1.Value = i;
for (int j = 0; j < dt1.Columns.Count; j++)
{
if (i == 0)
{
worksheet.Cells[1, j + 1] = dt1.Columns[j].ColumnName;
}
worksheet.Cells[i + 2, j + 1] = dt1.Rows[i][j].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//this.progressBar1.Text = "正在导出数据[" + percent.ToString("0.00") + "%]...";
}
//this.progressBar1.Visible = false;
workbook.Saved = true;
workbook.SaveCopyAs(filename);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
MessageBox.Show("导出Excel完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//button2_Click(null,null);
lalcount.Visible = false;

}
}
catch (Exception ex)
{
MessageBox.Show("导出Excel失败!" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
}