首页新闻找找看学习计划

关于ASP.NET导入Excel文档时,如何将数据放入流并导出

0
悬赏园豆:40 [已解决问题] 解决于 2009-05-25 18:22
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Office.Interop.Owc11;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ShowTable();
//显示表中的数据
}
}

private void ShowTable()//显示表中的数据
{
string myCon = ConfigurationManager.ConnectionStrings["table"].ConnectionString;

SqlConnection con
= new SqlConnection(myCon);
con.Open();
string sql = "select * From dbo.mytable";
SqlCommand com
= new SqlCommand(sql, con);

DataSet ds
= new DataSet();
SqlDataAdapter da
= new SqlDataAdapter(com);
da.Fill(ds);

Repeater1.DataSource
= ds.Tables[0];//用Repeater循环
Repeater1.DataBind();
}

/// <summary>
/// 导入到Excel文档(此段代码未完成,高手帮忙看看)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
ChartSpace mychartSpace
= new ChartSpace();
SpreadsheetClass myexl
= new SpreadsheetClass();
Worksheet mysheet
= myexl.ActiveSheet;

mysheet.Cells[
1, 1] = "销售额";
mysheet.Cells[
1, 2] = "内容";
mysheet.Cells[
1, 3] = "时间";

//attachment 参数表示作为附件下载,可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc    .xls    .txt   .htm
Response.AppendHeader("Content-Disposition", "attachment;filename=FileFlow.xls");
Response.ContentEncoding
= System.Text.Encoding.GetEncoding("GB2312");
//Response.ContentType指定文件类型 可以为application/ms-excel、
//application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
//定义一个输入流
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

for (int i = 0; i < Repeater1.Items.Count; i++)//从Repeater1中取出数据
{
CheckBox ch2
= this.Repeater1.Items[i].FindControl("CheckBox2") as CheckBox;
if (ch2 != null && ch2.Checked == true)
{
//将数据存入mysheet.Cells中
mysheet.Cells[i + 2, 1] = (Repeater1.Items[i].FindControl("Label1") as Label).Text;
mysheet.Cells[i
+ 2, 2] = (Repeater1.Items[i].FindControl("Label2") as Label).Text;
mysheet.Cells[i
+ 2, 3] = (Repeater1.Items[i].FindControl("Label3") as Label).Text;

}
}

System.Web.UI.HtmlTextWriter oHtmlTextWriter
= new System.Web.UI.HtmlTextWriter(oStringWriter);
this.RenderControl(oHtmlTextWriter);
//this 表示输出本页,你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
Response.Write(oStringWriter.ToString());
Response.End();
}
}

        
凹凸曼的主页 凹凸曼 | 初学一级 | 园豆:65
提问于:2009-05-21 06:32
< >
分享
最佳答案
2

这是我现在处理Excel,希望对你有所帮助

 

Code
月光小提琴 | 初学一级 |园豆:44 | 2009-05-21 08:46
其他回答(2)
0

方法很多样,如果我没弄错,也可导出类似 

<table>

<tr><td>a</td><td>a1</td><td>a2</td><td>...</td></tr>

<tr><td>b</td><td>b1</td><td>b2</td><td>...</td></tr>

</table>

结构的文本文件(扩展名 .xls ?)。还可以导出 .cvs(?) 文件,结构类似为

a,a1,a2,...

b,b1,b2,...

这些看上去稍显简单,大概还是要看具体应用吧。

陛下 | 园豆:3938 (老鸟四级) | 2009-05-21 09:18
0

Code
/// <summary>
/// 将指定Datatable的数据导出到指定路径下的Excel表格
/// </summary>
/// <param name="table">要导出的数据表格</param>
/// <param name="path">保存的路径(包含文件名)</param>
/// <returns>是够导出成功</returns>
public static bool TableToExcel(System.Data.DataTable table, string path)
{
bool isSuccess = true;
object miss = Missing.Value;
Application excelApp
= new Application();
excelApp.Workbooks.Add(miss);
try
{
Worksheet workSheet
= (Worksheet)excelApp.Worksheets[1];
int rowCount = table.Rows.Count + 1;
int colCount = table.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < colCount; i++)
{
dataArray[
0, i] = table.Columns[i].ToString();
for (int j = 0; j < table.Rows.Count; j++)
{
dataArray[(j
+ 1), i] = table.Rows[j][i].ToString();
}
}
workSheet.get_Range(workSheet.Cells[
1, 1], workSheet.Cells[rowCount, colCount]).Value2 = dataArray;
Workbook workBook
= excelApp.Workbooks[1];
workBook.SaveAs(path, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
workBook.Close(
false, miss, miss);
workBook
= null;
}
catch
{
isSuccess
= false;
}
finally
{
excelApp.Quit();
excelApp
= null;
GC.Collect();
KillExcelProcess();
//结束excel进程
}
return isSuccess;
}
/// <summary>
/// 强制结束Excel进程
/// </summary>
public static void KillExcelProcess()
{
int ProceedingCount = 0;
try
{
System.Diagnostics.Process[] ProceddingCon
= System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process IsProcedding in ProceddingCon)
{
if (IsProcedding.ProcessName.ToUpper() == "EXCEL")
{
ProceedingCount
+= 1;
IsProcedding.Kill();
}
}
}
catch (System.Exception err)
{

}
}

 

调用的时候只要把datable数据和路径传递过来就好

Code

 

由于客户端不一定能够装有office相应组件,所以我的策略是先在服务器端写入一个Excel到指定目录,给客户端下载之后,再把服务器文件删除

 

Code
/// <summary>
/// 删除指定路径的Excel
/// </summary>
/// <param name="deletePath">要删除文件的路径</param>
/// <returns>是否删除成功</returns>
public static bool DeleteExcel(string deletePath)
{
File.Delete(deletePath);
return true;
}

 

以上代码都经过测试成功,且不存在中文乱码问题。希望能够解决你的问题!

Luffy Huang | 园豆:25 (初学一级) | 2009-05-22 09:32
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册