excle表格导出到本地

来源：互联网发布：mac充电灯不亮编辑：程序博客网时间：2024/05/17 02:33

//导出后台点击事件
protected void btn_EXCEL_Click(object sender, EventArgs e)
{
string sname = hykh.Value;
string sh = stkh.Value;
string nam = name.Value;
string te = tel.Value;

string miq = minye.Value;
string maq = maxye.Value;
int mij = minjf.Value == "" ? 0 : int.Parse(minjf.Value);
int maj = maxjf.Value == "" ? 0 : int.Parse(maxjf.Value);
if (Session["UserModes"] != null)
{
int t = Convert.ToInt32(ddl_dj.SelectedValue);
Models.Users mst = (Models.Users)Session["UserModes"];
string saname = mst.Uname;
var list = wxmDal.GetSList(saname, sname, sh, nam, te, t, miq, maq, mij, maj).Select((s, i) => new
{
i = i + 1,
s.Wxmcnum,
s.Wxmcname,
s.Wxmctel,
s.Wxmchykye,
s.Wxmcjifen,
s.Wxmcshzt,
s.Wxmcsqsj
}).OrderByDescending(w => w.Wxmcsqsj).ToList();
comment.dropdown dyhyxx = new comment.dropdown();
string[] cels = new string[] { "编号", "会员卡号",
"会员姓名", "会员电话","可用余额","可用积分", "是否会员(1会员/0非会员)","注册时间"};
dyhyxx.dataToExcel(list, cels, "会员信息报表.xls");
}

}

//工具类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data;
using org.in2bits.MyXls;
using System.Reflection;
using Models;
using System.IO;
using System.Web.UI;
namespace TempCms.comment
{
public class dropdown : System.Web.UI.Page
{

/// <summary>
/// 导出excel表
/// </summary>
/// <param name="list">表内容 list集合</param>
/// <param name="columns"> 表头字段</param>
/// <param name="fileName">文件名称加后缀</param>
public void dataToExcel(IList list, string[] columns, string fileName)
{
DataTable dt = new DataTable();
dt = ToDataTable(list, columns);
XlsDocument xls = new XlsDocument();
//xls.FileName = DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xls";
xls.FileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.Add("Excel");
org.in2bits.MyXls.Cell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < columns.Length; i++)
{
cell = sheet.Cells.Add(1, i + 1, columns[i].ToString());//输出DataGridView列头名
cell.Font.Weight = FontWeight.Bold;
cell.TopLineStyle = 2;
}
//把DataGridView当前页的数据保存在Excel中

for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());
}
}
}

//然后就是保存了
// xls.Save();//winform
xls.Send();//web
}
/// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
public static DataTable ToDataTableTow(IList list)
{
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();

foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
/// <summary>
/// 将泛型集合类转换成DataTable
/// </summary>
/// <typeparam name="T">集合项类型</typeparam>
/// <param name="list">集合</param>
/// <param name="propertyName">需要返回的列的列名</param>
/// <returns>数据集(表)</returns>
public static DataTable ToDataTable(IList list, params string[] propertyName)
{
List<string> propertyNameList = new List<string>();
if (propertyName != null)
propertyNameList.AddRange(propertyName);
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
result.Columns.Add(pi.Name);
}
else
{
if (!propertyNameList.Contains(pi.Name))
result.Columns.Add(pi.Name);
}
}

for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
else
{
// if (propertyNameList.Contains(pi.Name))
// {
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
// }
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}

/// <summary>
/// 导出excel表
/// </summary>
/// <param name="list">表内容 list集合</param>
/// <param name="columns"> 表头字段</param>
/// <param name="fileName">文件名称加后缀</param>
public void dataToExcel(DataTable dt, string fileName)
{
XlsDocument xls = new XlsDocument();
//xls.FileName = DateTime.Now.ToString("yyyyMMdd hhmmss") + ".xls";
xls.FileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.Add("Excel");
org.in2bits.MyXls.Cell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
cell = sheet.Cells.Add(1, i + 1, dt.Columns[i].Caption);//输出DataGridView列头名
cell.Font.Weight = FontWeight.Bold;
cell.TopLineStyle = 2;
}
//把DataGridView当前页的数据保存在Excel中

for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());
}
}
}
xls.Send();//web
}

/// <summary>
/// 下载文件
/// </summary>
/// <param name="fileName">下载的文件名称</param>
/// <param name="filePath">下载文件的绝对地址</param>
public void GetDopdownFile(string fileName, string filePath)
{
FileInfo info = new FileInfo(filePath);
long fileSize = info.Length;
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachement;filename=" + fileName);
//指定文件大小
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
System.Web.HttpContext.Current.Response.WriteFile(filePath, 0, fileSize);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.Close();

}

}
}

0 0