分类分类
2015-06-28 00:00作者：网管联盟
关于导出excel报表，网上也是一搜一大把。整理一下，无非就是几种思路，有利用安装excel软件或插件的服务器直接生成，或者直接在客户端生成（通常都是利用excel软件或插件直接在浏览器生成）。反正万变不离其宗，离开excel插件，这个活你还真的干不了，由此你可以看到软件公司尤其是微软的强大。下面贴一个比较简单的导出excel报表的方法。在安装了office2003的机器上，通过ie浏览器可以成功生成excel，而且一直有人在使用。如果你在测试的时候发现这个根本无法使用，请注意，这个很可能和你的机器配置有关，别怀疑代码的正确性。下面就一个利用iBatis开发的例子来简单说明一下。
1、实体类
Code
using System;
using System.Collections.Generic;
using System.Text;
#region Apache Notice
/*****************************************************************************
* $Header: $
* $Revision: 383115 $
* $Date: 2006-04-15 13:21:51 +0800 (星期六, 04 三月 2006) $
*
* IBatisNetDemo
* Copyright (C) 2006 - Shanyou Zhang
*
* Licensed under the Apache License, Version 2.0 (the &License&);
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an &AS IS& BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
********************************************************************************/
#endregion
namespace IBatisNetDemo.Domain
{
[Serializable]
public class Person
{
private int id;
private string firstName;
private string lastName;
private DateTime? birthDate;
private double? weightInKilograms;
private double? heightInMeters;
public Person() { }
public int Id
{
get { return id; }
set { id = value; }
}
public string FirstName
{
get { return firstName; }
set { firstName = value; }
}
public string LastName
{
get { return lastName; }
set { lastName = value; }
}
public DateTime? BirthDate
{
get { return birthDate; }
set { birthDate = value; }
}
public double? WeightInKilograms
{
get { return weightInKilograms; }
set { weightInKilograms = value; }
}
public double? HeightInMeters
{
get { return heightInMeters; }
set { heightInMeters = value; }
}
}
}
2、导出excel报表主程序方法
Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.UI;
using System.IO;
using System.Reflection;
#p#副标题#e#
namespace DotNet.Common.Util
{
/// <summary>
/// 导出excel 简单实现
/// </summary>
public static class ExcelUtil
{
private static Page currentPage = HttpContext.Current.Handler as Page;
private static Object sycObj = new Object();
private static int incremental = 10;
/// <summary>
/// 按照时间生成excel名称 防止生成相同名的excel造成文件覆盖
/// </summary>
/// <returns></returns>
private static string CreateExcelName()
{
lock (sycObj)
{
incremental = incremental + 1;
if (incremental > 99)
incremental = 10;
return Convert.ToInt64(DateTime.Now.ToString(&yyyyMMddHHmmssfff&) + incremental).ToString();
}
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name=&T&>泛型实体</typeparam>
/// <param name=&response&></param>
/// <param name=&listColumes&>要显示的列名</param>
/// <param name=&listProperty&>要显示的导出属性名 和实体的属性名有关,顺序由显示的列确定 可以同listColumes</param>
/// <param name=&listModel&>实体集合</param>
public static void ExportExcel<T>(HttpResponse response, IList<string> listColumns, IList<string> listProperty, IList<T> listModel) where T : class, new()
{
if (listColumns.Count == 0)
{
throw new IndexOutOfRangeException(&No Columnes!&);
}
if (listColumns.Count != listProperty.Count)
{
throw new ArgumentException(&Columns and properties length are not equal.&);
}
string sheetName = &sheetName&;
using (StringWriter writer = new StringWriter())
{
writer.WriteLine(&<html xmlns:x=&urn:schemas-microsoft-com:office:excel&>&);
writer.WriteLine(&<head>&);
writer.WriteLine(&<!--[if gte mso 9]>&);
writer.WriteLine(&<xml>&);
writer.WriteLine(& <x:ExcelWorkbook>&);
writer.WriteLine(& <x:ExcelWorksheets>&);
writer.WriteLine(& <x:ExcelWorksheet>&);
writer.WriteLine(& <x:Name>& + sheetName + &</x:Name>&);
writer.WriteLine(& <x:WorksheetOptions>&);
writer.WriteLine(& <x:Print>&);
writer.WriteLine(& <x:ValidPrinterInfo />&);
writer.WriteLine(& </x:Print>&);
writer.WriteLine(& </x:WorksheetOptions>&);
writer.WriteLine(& </x:ExcelWorksheet>&);
writer.WriteLine(& </x:ExcelWorksheets>&);
writer.WriteLine(&</x:ExcelWorkbook>&);
writer.WriteLine(&</xml>&);
writer.WriteLine(&<![endif]-->&);
writer.WriteLine(&</head>&);
writer.WriteLine(&<body>&);
writer.WriteLine(&<table>&);
writer.WriteLine(&<tr>&);
foreach (string item in listColumns)
{
writer.WriteLine(&<td>& + item + &</td>&); //列名
}
writer.WriteLine(&</tr>&);
//通过反射 显示要显示的列
BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识
Type objType = typeof(T);
PropertyInfo[] propInfoArr = objType.GetProperties(bf);
foreach (T model in listModel)
{
writer.WriteLine(&<tr>&);
foreach (PropertyInfo propInf
相关文章