Question

ASP.NET 导出Excel问题

我写的2种方法：
方法1：private void GetExcelData(){
string TemplatePath = Server.MapPath(@"~/uploadfiles/temp.xlsx");
string ExportPath = Server.MapPath("") + @"\ExportReports";
if (!Directory.Exists(ExportPath))
Directory.CreateDirectory(ExportPath);
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (null == app) return;
app.Visible = false;
app.UserControl = true;
Workbooks workboos = app.Workbooks;
_Workbook workbook = workboos.Add(TemplatePath);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (null == worksheet) return;
//全部产品编号
List<Products> list = new SProducts().GetListProducts();
foreach (Products p in list){
//读取全部客户编号
var result = new SCustomers().GetListProducts();
foreach (Customers c in result){
worksheet.Cells[column, 1] = "";
//这里有52列
column++;
}
}
string str = DateTime.Now.ToString("yyyyMMddHHmmss");
string strExportPath = ExportPath + @"\" + str + ".xlsx";
workbook.SaveAs(strExportPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);

Process[] p1 = Process.GetProcesses();
for(int x=0;x<p1.Length;x++){
if (p1[x].ProcessName == "EXCEL"){
//<identity impersonate="true" userName="Administrator" password="123456" />
p1[x].Kill();
}
}
app = null;
}

方法1的 速度真是难以接受，1744条数据要10分钟，

方法2;
//定义文档类型、字符编码
Response.Clear();
Response.Buffer = true;
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>");
Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
Response.Write(html);
Response.End();

方法2，导出的Excel太大，目前导出有1744 * 27 = 47088 条数据，导出来有33MB，配置低的电脑根本打不开，

请问：还有没有别的方法导出，或者 把方法1帮我优化下， 方法2 压缩下也行

Answer 1

public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

}

private void BindData(DataTable dt)
{
repData.DataSource = dt;
repData.DataBind();
}
protected void btnLoadDt_Click(object sender, EventArgs e)
{
BindData(LoadDataTable());
}

//创建DataTable
private DataTable LoadDataTable()
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("userName");
dt.Columns.Add(dc);
dc = new DataColumn("userSex");
dt.Columns.Add(dc);

DataRow dr = dt.NewRow();
dr[0] = "fengyan";
dr[1] = "male";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = "efly";
dr[1] = "male";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = "楚旋";
dr[1] = "male";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = "51aspx";
dr[1] = "male";
dt.Rows.Add(dr);

return dt;
}

protected void btnExportExcel_Click(object sender, EventArgs e)
{
//得到需要导入Excel的DataTable
DataTable dt = LoadDataTable();
//将其列名添加进去！ （这一步注意是为了方便以后将该Excel导入内存表中 自动创建列名用。）
//DataRow dr = dt.NewRow();
//dr[0] = "userName";
//dr[1] = "userSex";
//dt.Rows.InsertAt(dr, 0);

//实例化一个Excel助手工具类
ExcelHelper ex = new ExcelHelper();
//导入所有！（从第一行第一列开始）
ex.DataTableToExcel(dt, 1, 1);
//导出Excel保存的路径！
ex.OutputFilePath = txtExcelPath.Text;
ex.OutputExcelFile();

}

protected void btnExcelToDataTable_Click(object sender, EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFromExcel.Text + ";Extended Properties=Excel 8.0";
//链接Excel
OleDbConnection cnnxls = new OleDbConnection(strConn);
//读取Excel里面有 表Sheet1
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet ds = new DataSet();
//将Excel里面有表内容装载到内存表中！
oda.Fill(ds);
DataTable dt = ds.Tables[0];
BindData(dt);
}
}

Answer 2

http://hi.baidu.com/louqianglove/blog/item/44ccb5fe73553c75024f56f7.html

这里是一个gridview导出excel的不知道能不能用上你可以看看