注释很详尽,就不多说了,简单解释下原理:
GetExcelSheet方法:导入功能。原理为把Excel上传至服务器,然后把Excel当作数据库读数据到DataTable返回,然后删除服务器端的Excel,最后对返回的DataTable进行数据处理。
ReportToExcel方法:导出方法一。原理为把控件以Excel的形式输出,但是只支持浏览器下载,因为迅雷识别到的是网页地址,下载到的会是整个网页。
ExportExcel方法:导出方法二。原理为通过com操作Excel,需要引入Excel安装包中的Microsoft.Office.Interop.Excel.dll文件(具体dll文件位置自行百度),并且需要对DCOM进行权限配置(配置方式参考:http://blog.csdn.net/lploveme/article/details/8215265)。优点是稳定性、兼容性好,支持各种方式下载。
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;
using Spring.Context.Support;
using DataTable = System.Data.DataTable;
using Page = System.Web.UI.Page;
using Spring.Context;
using IBLL;
using Model;
namespace Common
{
public class DataToExcel : System.Web.UI.Page
{
#region IBLL层实例
static IApplicationContext ctx = ContextRegistry.GetContext();
/// <summary>
/// T_DictionaryBLL实例
/// </summary>
public static IBLL<T_Dictionary> DictionaryBLL
{
get { return ctx.GetObject("T_DictionaryBLL") as IBLL<T_Dictionary>; }
}
#endregion IBLL层实例
/// <summary>
/// 导入-将Excel中的数据放到DataTable中
/// </summary>
/// <param name="page">上传页面</param>
/// <param name="up">上传控件</param>
/// <param name="path">上传路径</param>
/// <param name="table">DataTable表名</param>
/// <returns></returns>
public static DataTable GetExcelSheet(Page page, FileUpload up, string path, string table = "ExcelTable")
{
//验证文件格式
string fileType = "xls|xlsx";
if (!up.HasFile || fileType.IndexOf(Upload.GetExtension(up.FileName)) < 0)
{
MessageBox.Show(page, "请上传.xls或.xlsx结尾的Excel文件!", 1);
return null;
}
if (up.FileContent.Length > 1024 * 1024 * 10)
{
MessageBox.Show(page, "上传文件不允许超过10M!", 1);
return null;
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffff") + up.FileName;//文件名称
string fullUrl = page.Server.MapPath(path);//物理路径
if (!Directory.Exists(fullUrl))
{
Directory.CreateDirectory(fullUrl); //如果不存在,则创建
}
string webFilePath = fullUrl + "//" + fileName; // 服务器端文件路径
up.SaveAs(webFilePath);
//office2007之前 仅支持.xls
//const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
//支持.xls和.xlsx,即包括office2010等版本的 HDR=Yes代表第一行是标题,不是数据;
string cmdText = ConfigurationManager.AppSettings["excelType"].ToString();
DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, webFilePath));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet名称
string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
//查询sheet中的数据
string strSql = "select * from [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds, table);
dt = ds.Tables[0];
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
//删除文件
Upload upload = new Upload();
upload.DeleteImg(path + fileName);
}
}
/// <summary>
/// 导出Excel,此方法较简单,但是不支持迅雷等下载工具,只能浏览器下载
/// </summary>
/// <param name="control">导出控件</param>
/// <param name="name">导出文件名字</param>
/// /// <param name="name">表头</param>
public static void ReportToExcel(System.Web.UI.Control control, string name, string head)
{
//HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + ".xls");
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType = "application/ms-excel";
control.EnableViewState = false;
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
control.RenderControl(oHtmlTextWriter);
StringBuilder table = new StringBuilder("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=GB2312 \"><title>Copyright by SDU</title></head><body>");
table.Append(head);
table.Append(oStringWriter.ToString());
table.Append("</table></body></html>");
while (table.ToString().Contains("<td class=\"text-center\""))
{
int start = table.ToString().IndexOf("<td class=\"text-center\"");
int count = table.ToString().IndexOf("</td>", start) + 5 - start;
table.Remove(start, count);
}
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Write(table.ToString());
HttpContext.Current.Response.End();
}
/// <summary>
/// 导出Excel,此方法虽然复杂,需要服务端配置一下DCOM权限,但是兼容性非常好,支持任何下载
/// </summary>
/// <typeparam name="T">要导出的类型</typeparam>
/// <param name="list">要导出的List</param>
/// <param name="column">要导出的列</param>
/// <param name="name">导出文件的名字</param>
/// <param name="page">操作页面</param>
/// <param name="path">文件暂存路径</param>
/// <param name="joinRow">要合并的行及数据</param>
public static void ExportExcel<T>(List<T> list, Dictionary<string, string> column, Dictionary<string, string> dictionary, string name, Page page, string path, Dictionary<string, string> joinRow= null)
{
string fileName = name+DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls"; //文件名称
string fullUrl = page.Server.MapPath(path); //物理路径
if (!Directory.Exists(fullUrl))
{
Directory.CreateDirectory(fullUrl); //如果不存在,则创建
}
//删除2天之前的文件
ClearDateOutImg(page, path);
string webFilePath = fullUrl + fileName; // 服务器端文件路径
if (File.Exists(webFilePath))
{
File.Delete(webFilePath);
}
object Nothing = System.Reflection.Missing.Value; //定义Nothing
Application excleApp = new ApplicationClass(); //创建Excel应用程序对象的一个实例,相当于我们从开始菜单打开Excel应用程序,对excleApp进行初始化
Workbook excleWork = excleApp.Workbooks.Add(Nothing); //exclework,对exclework进行初始化
Worksheet ws = (Worksheet)excleWork.Worksheets[1]; //定义ws为工作文档中的第一个sheet
int i = 1;
//设置列名
foreach (KeyValuePair<string, string> pair in column)
{
ws.Cells[1, i++] = pair.Value;
}
//获取合并列起始位置
int j = i;
//设置合并的列名及值
if (joinRow != null)
{
foreach (KeyValuePair<string, string> pair in joinRow)
{
ws.Cells[1, i] = pair.Key;
ws.Cells[2, i++] = pair.Value;
}
}
for (int r = 0; r < list.Count; r++)
{
Type type = list[r].GetType(); //获取类型
int c = 1;
foreach (KeyValuePair<string, string> pair in column)
{
System.Reflection.PropertyInfo propertyInfo = type.GetProperty(pair.Key); //获取指定名称的属性
string value = propertyInfo.GetValue(list[r], null).ToString(); //获取属性值
//转换数据字典
foreach (KeyValuePair<string, string> dPair in dictionary)
{
if (pair.Key == dPair.Key)
{
value = DictionaryBLL.GetName(dPair.Value, propertyInfo.GetValue(list[r], null).ToString());
}
}
ws.Cells[r + 2, c++] = value;
}
}
//合并单元格
while (i>j)
{
Range rangeProgram = ws.get_Range(ws.Cells[2,j], ws.Cells[list.Count+1,j]);//获取需要合并的单元格的范围
rangeProgram.Application.DisplayAlerts = false;
rangeProgram.Merge(Missing.Value);
rangeProgram.Application.DisplayAlerts = true;
j++;
}
#region 填充Excel图标
/*
Range range1 = ws.get_Range("A1", "A1");//选定(A1,A1)这个单元格
range1.Value2 = "3";//对这个单元格进行填充内容
range1 = ws.get_Range("A2", "A2");//同上
range1.Value2 = "5.7";//同上
range1 = ws.get_Range("A3", "A3");//同上
range1.Value2 = "4.8";//同上
range1 = ws.get_Range("A4", "A4");//同上
range1.Value2 = "9.2";//同上
range1 = ws.get_Range("A5", "A5");//同上
range1.Value2 = "6.4";//同上
excleWork.Charts.Add(Nothing, Nothing, Nothing, Nothing);//添加一个图表
excleWork.ActiveChart.ChartType = XlChartType.xl3DColumnClustered;//设置图表的类型是三维柱状图
excleWork.ActiveChart.SetSourceData(ws.get_Range("A1", "A5"), XlRowCol.xlColumns);//设置这个三维柱状图的数据源
excleWork.ActiveChart.Location(XlChartLocation.xlLocationAsObject, "sheet1");//设置你要将这个柱状图添加到什么地方
excleWork.ActiveChart.HasTitle = true;//设置柱状图是否有标题
excleWork.ActiveChart.ChartTitle.Text = "创建图表";//设置标题的内容为“创建图表”
excleWork.ActiveChart.HasDataTable = false;//设置柱状图是否有数据表
*/
#endregion 填充Excel图标
try
{
excleWork.SaveAs(webFilePath, Nothing, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive,
Nothing, Nothing, Nothing, Nothing, Nothing); //保存动态生成的excle表
//释放资源
excleApp.Application.Quit();
if (excleApp != null)
{
excleApp = null;
}
MessageBox.ShowAndRedirectParent(page, "导出成功!", 4, path + fileName);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 删除过期的文件。
/// </summary>
private static void ClearDateOutImg(Page page, string path)
{
//先取得文件夹中的文件列表
string[] fileEntries = System.IO.Directory.GetFiles(page.Server.MapPath(path));
foreach (string singFiles in fileEntries)//遍历文件列表
{
//将文件的生成日期与系统日期相比,如果是2天以前生成的文件,删除它
if (
System.DateTime.Compare(System.IO.File.GetCreationTime(singFiles).AddDays(2), System.DateTime.Now) <
0)
{
System.IO.File.Delete(singFiles);
}
}
}
}
}