值得收藏的Excel导出功能:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office;
using Microsoft.Office.Interop.Excel;
namespace WebUI.ExcelOutIn
{
/// <summary>
/// ExcelIn 的摘要说明
/// </summary>
public class ExcelIn : IHttpHandler
{
System.Data.DataTable dt = new System.Data.DataTable();
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True"))
{
conn.Open();
using (IDbCommand comm = conn.CreateCommand())
{
SqlDataAdapter da = new SqlDataAdapter("select * from Users", conn);
da.Fill(dt);
if (ExportExcel("Users", dt, "E:\\")) {
context.Response.Write("导出成功!");
}
}
}
}
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Name = "用户表";
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count; //表行数
long rowRead = 0;
float percent = 0;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true; //加粗
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
//保存文件
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName + reportName+".xls");
fileSaved = true;
xlApp.Application.Workbooks.Close();
xlApp.Quit();
GC.Collect();
return fileSaved;
}
public bool IsReusable
{
get
{
return false;
}
}
}
}