using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Configuration;
using System.Data.SqlClient;
using System.Management;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.IO;
namespace test1
{
public partial class excel
{
public static string _connection=ConfigurationManager.ConnectionStrings["excel"].ConnectionString;
public System.Data.DataTable returntable()
{
System.Data.DataTable table = new System.Data.DataTable();
string connection = _connection;
using(SqlConnection con=new SqlConnection(connection))
{
string comandtext = "select * from t_user";
using (SqlCommand cmd = new SqlCommand(comandtext,con))
{
con.Open();
SqlDataAdapter command = new SqlDataAdapter(cmd);
command.Fill(table);
}
}
return table;
}
/* private void SaveExcel(DataTable dt, string projectName, string spubdate)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
//解决中文乱码问题
Response.AppendHeader("Content-Disposition", "online; filename=" + System.Web.HttpUtility.UrlEncode(projectName, System.Text.Encoding.UTF8) + "_" + spubdate + ".xls");
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excle";
string colHeaders = "", ls_item = "'";
////定义表对象与行对象，同时用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 += "id" + "\n";
}
if (i == (cl - 2))
{
colHeaders += "name" + "\t";
}
}
Response.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() + "\r\n";
}
else
{
ls_item += row[i].ToString() + ",";
}
}
Response.Write(ls_item);
ls_item = "'";
}
Response.End();
dt.Clear();
dt.Dispose();
}*/
public void ex(System.Data.DataTable dt,string filename)//此函数为windows应用程序在客户端可以运行，不推荐。
{
object objMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application myexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = myexcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);
Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;
//myexcel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet mysheet = m_objWorkSheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Range myrang = myexcel.get_Range(myexcel.Cells[1, 1], myexcel.Cells[8, 8]);
myexcel.Visible = true;
myexcel.Caption = "lianxi";
mysheet.Cells[1, 1] = "id";
mysheet.Cells[2, 2] = "name";
int i = 2;
foreach(DataRow row in dt.Rows )
{
mysheet.Cells[i, 1] = row["id"].ToString();
mysheet.Cells[i, 2] = row["name"].ToString();
i++;
}
if (File.Exists(filename))
{
File.Delete(filename);
}
m_objWorkBook.SaveAs("f:\\"+filename+".xls ", objMissing, objMissing, objMissing, objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
objMissing, objMissing, objMissing, objMissing,
objMissing);
myexcel = null;
}
#region 生成Excel(Asp.Net)
/// <summary>
/// 生成Excel
/// </summary>
/// <param name="datatable">数据源</param>
/// <param name="FileName">文件名(无需后缀)</param>
/* public void CreateExcel(System.Data.DataTable datatable, string FileName)
{
HttpResponse resp;
//resp = Page.Response;
resp = HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
string colHeaders = "", ls_item = "";
//定义表对象与行对象，同时用DataSet对其值进行初始化
System.Data.DataTable dt = datatable;
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 += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
resp.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";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
*/
#endregion
public void createxcel(System.Data.DataTable datatable, string filename)
{
HttpResponse response;
response = HttpContext.Current.Response;
response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
response.AppendHeader("Content-Disposition", "attachment:filename"+filename+".xls");
string colheaders = "", ls_items = "";
System.Data.DataTable dt = datatable;
DataRow[] row = dt.Select();
int i = 0;
int cl = dt.Columns.Count;
for (i = 0; i < cl; i++)
{
if (i == cl - 1)
{
colheaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colheaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
response.Write(colheaders);
foreach (DataRow rows in row)
{
for (i = 0; i < cl; i++)
{
if (i == cl)
{
ls_items += rows[i].ToString() + "\n";
}
else
{
ls_items += rows[i].ToString() + "\n";
}
}
response.Write(ls_items);
ls_items = "";
}
response.End();
}
}
}