Question

ASP.NET 把一个datatable导出到EXCEL有什么办法呢？

网上搜了很多方法，共同特点就是OFFICE 2010打开会乱码。
有没什么办法导出来的格式是真EXCEL格式？

就一个DATATABLE
导出还要能导入的~

Answer 1

导出Excel

private void GridExport(DataTable dt, string strFile, string strExt)

{

string strAppType = "";

switch (strExt)

{

case "xls":

strAppType = "application/ms-excel";

break;

case "doc":

strAppType = "application/ms-word";

break;

case "txt":

strAppType = "application/ms-txt";

break;

case "html":

case "htm":

strAppType = "application/ms-html";

break;

default: return;

}

GridView MyGridView = new GridView();

MyGridView.DataSource = dt;

MyGridView.DataBind();

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.AddHeader("Content-Type", "text/html; charset=GB2312");

HttpContext.Current.Response.AppendHeader("Content-Disposition", string.Format("attachment;filename={0}.{1}", HttpUtility.UrlEncode(strFile,Encoding.GetEncoding("GB2312")), strExt));

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

HttpContext.Current.Response.ContentType = strAppType;

//MyGridView.Page.EnableViewState = false;

//二、定义一个输入流

System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);

System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

//三、将目标数据绑定到输入流输出

MyGridView.RenderControl(oHtmlTextWriter);

HttpContext.Current.Response.Write(oStringWriter.ToString());

HttpContext.Current.Response.End();

}
导入
private DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
DataSet ds = new DataSet();
try
{
conn.Open();
string strExcel = "select * from [sheet1$],[sheet2$],[sheet3$]";
OleDbDataAdapter command = new OleDbDataAdapter(strExcel, conn);
command.Fill(ds);
conn.Close();
}
catch
{
}
return ds;
}
}

Answer 2

//dataset导出EXCEL
public void CreateExcel(DataSet ds, string FileType, string FileName)
{
//try
//{
//HttpResponse resp;
//resp = Page.Response;
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");

Response.ContentType = FileType;

string colHeaders = string.Empty;
string ls_item = string.Empty;

//定义表对象与行对象，同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;

//取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{

if (i == (cl - 1))//最后一列，加n
{
colHeaders += ColChange(dt.Columns[i].Caption.ToString()) + "\n";
}
else
{
colHeaders += ColChange(dt.Columns[i].Caption.ToString()) + "\t";
}
}
Response.Output.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息

//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}

}
Response.Output.Write(ls_item);
ls_item = string.Empty;

}

Response.Output.Flush();
Response.End();
//}
//catch
//{
//}
}

//调用函数
CreateExcel(ds, "application/ms-excel", ”到处的文件名“);

追问

这样导出无法导入了啊·

追答

你问的就是怎么导出.
导入的EXCEL表必须和数据库的格式统一。

//这是一个比较简单的导入
protected void Button1_Click1(object sender, EventArgs e)
{
SqlConnection mycon = con();
//string sqlstr = "insert into test select Dev_Mac, Dev_Type, Dev_SW, Pro_Reason,B_Num,Box_Num,Supply,In_Time,Out_Time,Dev_State,Rep_Count,Client_Num,Worker_Numin,Worker_Numout,C_Address,Sell_Name,Take_Name,Tra_Num from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:/exc.xls',sheet1$)";

//string sqlstr = "insert into insert_table select Dev_Mac from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:/excel.xls',sheet2$)";

string sqlstr = "insert into insert_table select Dev_Mac,Dev_State from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:/excel.xls',sheet3$)";

SqlCommand cmd = new SqlCommand(sqlstr, mycon);
mycon.Open();
try
{
cmd.ExecuteNonQuery();
}
catch
{

}

mycon.Close();
}

Answer 3

用插件。自己写的不一定有人家提供的好。