DataTable導出到Excel
谁给个代码 参考下。。。 --------------------编程问答-------------------- 我有等等 --------------------编程问答-------------------- http://topic.csdn.net/u/20091110/16/5ed6d2e1-04cb-4412-9976-97e849c77ac5.html参考 --------------------编程问答--------------------
#region 普通的Excel导出http://hi.baidu.com/niki250/blog/item/97305d17129f60044a90a794.html --------------------编程问答-------------------- /// <summary>
/// <summary>
/// DataTable To Excel
/// </summary>
/// <param name="dt">DataTable Name</param>
/// <param name="typeid">1,Excel 2,XML</param>
/// <param name="FileName">文件名</param>
public void CreateExcel(System.Data.DataTable dt, string typeid, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AddHeader("Content-Disposition",
"attachment; filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
this.EnableViewState = false;
string colHeaders = "", Is_item = "";
int i = 0;
//定义表对象与行对象,同时使用DataSet对其值进行初始化
DataRow[] myRow = dt.Select("");
//typeid=="1"时导出为Excel格式文件;typeid=="2"时导出为XML文件
if (typeid == "1")
{
//取得数据表各列标题,标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
colHeaders += "\n";
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中,逐列取得数据,数据之间以\t分割,结束时加回车符\n
for (i = 0; i < dt.Columns.Count; i++)
{
Is_item += row[i].ToString() + "\t";
}
Is_item += "\n";
resp.Write(Is_item);
Is_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(dt.DataSet.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
#endregion
/// DataTable導出到Excel.繁體OS,無亂碼問題.
/// </summary>
/// <param name="dt"></param>
/// <param name="strFileName">含.xls後綴</param>
public void DownloadAsExcel(DataTable dt, string strFileName)
{
try
{
StringWriter sw = new StringWriter();
string colstr = "";
foreach (DataColumn col in dt.Columns)
{
colstr += col.ColumnName + "\t";
}
sw.WriteLine(colstr);
foreach (DataRow row in dt.Rows)
{
colstr = "";
foreach (DataColumn col in dt.Columns)
{
colstr += row[col.ColumnName].ToString() + "\t";
}
sw.WriteLine(colstr);
}
sw.Close();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + "");
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
System.Web.HttpContext.Current.Response.Write(sw);
System.Web.HttpContext.Current.Response.End();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
} --------------------编程问答--------------------
//先将Excel传到服务器上面--------------------编程问答--------------------
protected void Button1_Click(object sender, EventArgs e)
{
string Excel = string.Empty;
Boolean fileOK = false;
String path = Server.MapPath("~/UploadExcel/");
if (FileUpload1.HasFile)
{
String fileExtension =
System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
String[] allowedExtensions =
{ ".xls" };
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (fileExtension == allowedExtensions[i])
{
fileOK = true;
}
}
}
if (fileOK)
{
try
{
//FileUpload1.PostedFile.SaveAs(path
// + FileUpload1.FileName);
//Excel = FileUpload1.PostedFile.FileName;
Excel=path + FileUpload1.FileName;
Excel = path + System.IO.Path.GetFileName(FileUpload1.FileName);
FileUpload1.PostedFile.SaveAs(Excel);
// Response.Write(Excel);
DataTableToDB(Excel);
//Label1.Text = "File uploaded!";
}
catch (Exception ex)
{
Response.Write(ex.Message);
Label1.Text = "上传失败!";
}
}
else
{
Label1.Text = "文件格式不对.";
}
}
public static void DataTableToDB(string Ex)
{
ASP.admin_bjproduct_list_aspx list = new admin_bjproduct_list_aspx();
BJProductCategory CC = new BJProductCategory();
DataTable dtExcel = ExcelToDataTable(Ex, "Sheet1");
DT = CreateParentTable();
try
{
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
if (dtExcel.Rows[i][0].ToString() != null || dtExcel.Rows[i][0].ToString() != "")
{
DR = DT.NewRow();
int PID = CategoryID(dtExcel.Rows[i][2].ToString());
CC.ID = PID;
WebManager.GetInfo(CC);
if (CC.Title == "")
{
continue;
}
else
{
DR["Keyword"] = dtExcel.Rows[i][0].ToString();
DR["Title"] = dtExcel.Rows[i][1].ToString();
DR["ParentID"] = PID;
DR["tmp1"] = dtExcel.Rows[i][3].ToString();
DR["tmp2"] = float.Parse(dtExcel.Rows[i][4].ToString());
DR["tmp3"] = dtExcel.Rows[i][5].ToString();
}
DT.Rows.Add(DR);
}
else
{
list.AlertMsg("该行数据为空!");
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
using (System.Data.SqlClient.SqlBulkCopy bulkCopy =
new System.Data.SqlClient.SqlBulkCopy(@"Data Source=DGIDC11016\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;max pool size=512;pooling=true;"))
{
bulkCopy.DestinationTableName =
"BJProduct";
bulkCopy.ColumnMappings.Add("Keyword", "Keyword");
bulkCopy.ColumnMappings.Add("Title", "Title");
bulkCopy.ColumnMappings.Add("ParentID", "ParentID");
bulkCopy.ColumnMappings.Add("tmp1", "tmp1");
bulkCopy.ColumnMappings.Add("tmp2", "tmp2");
bulkCopy.ColumnMappings.Add("tmp3", "tmp3");
try
{
bulkCopy.WriteToServer(DT);
list.AlertMsg("数据导入成功!");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
DT.Clear();
}
}
}
//将Excel中的内容插入到DataTable中--------------------编程问答-------------------- 我的这个是可以将产品分类,产品名称一起倒入到Excel中去的
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 8.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
{
conn.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds);
conn.Close();
conn.Dispose();
}
return ds.Tables[0];
}
//获取父类ID
public static int CategoryID(string category)
{
int CID = 1;
string s = "cn";
System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection();
Conn.ConnectionString = @"Data Source=DGIDC11016\WS_HGO;Initial Catalog=F:\我的单子\200904\项目\ZYT\APP_DATA\BJYZT.MDF;User ID=sa;Password=123456;";
Conn.Open();
string sqlcategory = "insert into BJProductCategory (Title,ParentID,Version) values('" + category + "'," + 1 + ",'" + s + "')";
System.Data.SqlClient.SqlCommand Com = new System.Data.SqlClient.SqlCommand(sqlcategory, Conn);
int i = Com.ExecuteNonQuery();
if (i > 0)
{
//取出最大值就是当前插入的ID
string str = "select max(ID) AS ID from BJProductCategory";
System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(str, Conn);
CID = Convert.ToInt32(Cmd.ExecuteScalar());
}
Conn.Close();
Conn.Dispose();
return CID;
}
//Title,ParentID,tmp1,tmp2,tmp3,Version
public static DataTable CreateParentTable()
{
ParentTable.Columns.Clear();
DC = new DataColumn();
DC.ColumnName = "Title";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "Keyword";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "ParentID";
DC.DataType = System.Type.GetType("System.Int32");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "tmp1";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "tmp2";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "tmp3";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
DC = new DataColumn();
DC.ColumnName = "Version";
DC.DataType = System.Type.GetType("System.String");
ParentTable.Columns.Add(DC);
return ParentTable;
}
BJProductCategory Category = new BJProductCategory();
BJProduct Product = new BJProduct();
protected void Button2_Click(object sender, EventArgs e)
{
string Type = TxtCategory.Text.Trim();
if (Type == "")
{
string script = "<script>";
script += "alert('您删除的分类不能为空!')";
script += "</";
script += "script>";
Page.RegisterStartupScript("", script);
}
else
{
Category.Title = Type;
DataTable DTParent = WebManager.List(Category, "", "ID", "&title%", "OrderNo");
if (DTParent.Rows.Count>0)
{
for (int j = 0; j < DTParent.Rows.Count; j++)
{
Product.ParentID = int.Parse(DTParent.Rows[j][0].ToString());
DataTable dt = WebManager.List(Product, "", "ID,Title,ParentID", "&parentid=", "[OrderNo]");
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Product.ID = int.Parse(dt.Rows[i][0].ToString());
WebManager.Delete(Product);
Category.ID = int.Parse(dt.Rows[i][2].ToString());
WebManager.Delete(Category);
}
}
}
AlertMsg("删除成功!");
}
else
{
AlertMsg("没有此分类信息!");
}
}
}
public void AlertMsg(string Msg)
{
string script = "<script>";
script += "alert('" + Msg + "')";
script += "</";
script += "script>";
Page.RegisterStartupScript("", script);
}
如果单独到某一张表的话
google --------------------编程问答-------------------- mark,学习 --------------------编程问答-------------------- 貌似你们都忘了一个很难缠的问题:程序推出后EXCEL进程一直在内存中,打开任务管理器可以看到
补充:.NET技术 , C#