关于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();
}
}
最佳答案
2
这是我现在处理Excel，希望对你有所帮助
Code
其他回答(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,...
这些看上去稍显简单，大概还是要看具体应用吧。
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)
{
}
}
由于客户端不一定能够装有office相应组件，所以我的策略是先在服务器端写入一个Excel到指定目录，给客户端下载之后，再把服务器文件删除
/// <summary>
/// 删除指定路径的Excel
/// </summary>
/// <param name="deletePath">要删除文件的路径</param>
/// <returns>是否删除成功</returns>
public static bool DeleteExcel(string deletePath)
{
File.Delete(deletePath);
return true;
}
以上代码都经过测试成功，且不存在中文乱码问题。希望能够解决你的问题！
