asp.net 使用NPOI，泛型反射，XML导入导出excel

asp.net开发企业级程序，经常要用到Excel导入导出功能，NPOI提供了比较好的解决方案，但是因为各数据库表的字段不同，而且导入导出的数据列名需要中文命名（数据表字段一般以英文命名），所以需要做一个比较通用的方法，减少日后的工作量。

由于开发中用到三层架构，数据表在程序中用model实体类去表示，所以想到用泛型反射去实现，使用XML去映射数据库表字段与Excel列标题的关系。

比如model的类如下

/// author 柏柏 2013-05-16

public class CNS_CensusMember : ModuleBase

{

public int ID { set; get; }

/// <summary>

/// 户号

/// </summary>

public string RegisterNO { set; get; }

/// <summary>

/// 姓名

/// </summary>

public string Name { set; get; }

/// <summary>

/// 联系电话

/// </summary>

public string Tel { set; get; }

/// <summary>

/// 与户主关系

/// </summary>

public string Relation { set; get; }

/// <summary>

/// 曾用名

/// </summary>

public string OtherName { set; get; }

/// <summary>

/// 性别

/// </summary>

public System.Nullable<short> Sex { set; get; }

/// <summary>

/// 出生地

/// </summary>

public string PlaceOfBirth { set; get; }

/// <summary>

/// 民族

/// </summary>

public string Nationalilty { set; get; }

/// <summary>

/// 祖籍

/// </summary>

public string PlaceOfAncestral { set; get; }

/// <summary>

/// 出生日期

/// </summary>

public System.Nullable<System.DateTime> Brithday { set; get; }

/// <summary>

/// 住址

/// </summary>

public string Address { set; get; }

/// <summary>

/// 宗教信养

/// </summary>

public string Religion { set; get; }

/// <summary>

/// 身份证号

/// </summary>

public string IDCard { set; get; }

/// <summary>

/// 身高

/// </summary>

public string Height { set; get; }

/// <summary>

/// 血型

/// </summary>

public string TypeOfBlood { set; get; }

/// <summary>

/// 文化程度

/// </summary>

public System.Nullable<int> EducationID { set; get; }

/// <summary>

/// 婚姻状况

/// </summary>

public string Marriage { set; get; }

/// <summary>

/// 兵役状况

/// </summary>

public string MilitaryService { set; get; }

/// <summary>

/// 服务处所

/// </summary>

public string Company { set; get; }

/// <summary>

/// 职业

/// </summary>

public string Occupation { set; get; }

/// <summary>

/// 政治面目

/// </summary>

public System.Nullable<int> PoliticalID { set; get; }

/// <summary>

/// 迁入日期

/// </summary>

public System.Nullable<System.DateTime> IngoingDate { set; get; }

/// <summary>

/// 原住地

/// </summary>

public string PreviousAddress { set; get; }

/// <summary>

/// 迁入原因

/// </summary>

public string IngoingReason { set; get; }

/// <summary>

/// 迁出日期

/// </summary>

public System.Nullable<System.DateTime> MoveOutDate { set; get; }

/// <summary>

/// 迁出到

/// </summary>

public string MoveToAddress { set; get; }

/// <summary>

/// 是否注销

/// </summary>

public System.Nullable<bool> IsCanceled { set; get; }

/// <summary>

/// 注销日期

/// </summary>

public System.Nullable<System.DateTime> CancelDate { set; get; }

/// <summary>

/// 注销原因

/// </summary>

public string CancelReason { set; get; }

/// <summary>

/// 备注

/// </summary>

public string Remark { set; get; }

/// <summary>

/// 文化水平

/// </summary>

public string Education { set; get; }

/// <summary>

/// 户别

/// </summary>

public string CensusTypeName { set; get; }

/// <summary>

/// 政治面目

/// </summary>

public string Political { set; get; }

/// <summary>

/// 性别

/// </summary>

public string SexName { set; get; }

/// <summary>

/// 户主

/// </summary>

public string HouseHolder { set; get; }

}

再写一个xml文件作为与Excel的映射，如下。headerText对应excel的列标题，propertyName对于数据库数据字段名，也就是model的属性名。

<?xml version="1.0" encoding="utf-8" ?>

</module>

再写实体类对应XML文件，以便对xml的读取和对excel列头的操作，如下

/// author 柏柏 2013-05-16

public class ExcelHeader

{

private Dictionary<string, string> _Mapping = new Dictionary<string, string>();

/// <summary>

/// 映射值的转换

/// </summary>

public Dictionary<string, string> Mapping

{

get { return _Mapping; }

set { _Mapping = value; }

}

public string MappingTo { set; get; }

public int Width { set; get; }

public string HeaderText { set; get; }

public string PropertyName { set; get; }

public string DataType { set; get; }

public ExcelHeader()

{

}

public ExcelHeader(string headerText, string propertyName, int width)

{

this.HeaderText = headerText;

this.Width = width;

this.PropertyName = propertyName;

}

读取xml的类如下

/// <summary>

/// author 柏柏 2013-05-16

/// </summary>

public class WebExcelHelper

{

public List<ExcelHeader> GetExcelHeader(string template)//template为xml的路径名称

{

XmlTextReader reader = new XmlTextReader(System.Web.HttpContext.Current.Server.MapPath(template));

XmlDocument doc = new XmlDocument();

doc.Load(reader);

string format = "^[0-9]*$";

Regex regex = new Regex(format);

List<ExcelHeader> headerList = new List<ExcelHeader>();

foreach (XmlNode node in doc.DocumentElement.ChildNodes)

{

ExcelHeader header = new ExcelHeader();

if (node.Attributes["headerText"] != null)

header.HeaderText = node.Attributes["headerText"].Value;

if (node.Attributes["propertyName"] != null)

header.PropertyName = node.Attributes["propertyName"].Value;

if (node.Attributes["dataType"] != null)

header.DataType = node.Attributes["dataType"].Value;

if (node.Attributes["to"] != null)

header.MappingTo = node.Attributes["to"].Value;

if (node.Attributes["width"] != null)

{

string width = node.Attributes["width"].Value;

if (regex.IsMatch(width))

{

header.Width = int.Parse(node.Attributes["width"].Value);

}

foreach (XmlNode subNode in node.ChildNodes)

{

header.Mapping.Add(subNode.Attributes["text"].Value, subNode.Attributes["value"].Value);

}

headerList.Add(header);

}

return headerList;

}

再来看看NPOI导入导出的方法，这个类是参考网上的方法，只是把用DataTable的实现改为泛型的实现

public class ExcelHelper

{

/// <summary>

/// 作者

/// </summary>

public string Author { set; get; }

/// <summary>

/// 应用程序名

/// </summary>

public string ApplicationName { set; get; }

/// <summary>

/// 介绍、简介

/// </summary>

public string Comments { set; get; }

/// <summary>

/// NPOI简单Demo，快速入门代码

/// </summary>

/// <param name="dtSource"></param>

/// <param name="strFileName"></param>

/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>

public void ExportEasy(DataTable dtSource, string strFileName)

{

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.CreateSheet();

//填充表头

HSSFRow dataRow = sheet.CreateRow(0);

foreach (DataColumn column in dtSource.Columns)

{

dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

}

//填充内容

for (int i = 0; i < dtSource.Rows.Count; i++)

{

dataRow = sheet.CreateRow(i + 1);

for (int j = 0; j < dtSource.Columns.Count; j++)

{

dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());

}

//保存

using (MemoryStream ms = new MemoryStream())

{

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

{

workbook.Write(ms);

ms.Flush();

ms.Position = 0;

byte[] data = ms.ToArray();

fs.Write(data, 0, data.Length);

fs.Flush();

}

sheet.Dispose();

workbook.Dispose();

}

/// <summary>

/// ListToExcel

/// </summary>

/// <param name="list">数据源</param>

/// <param name="strFileName">文件保存路径</param>

/// <param name="nameList">列头信息</param>

public void ListToExcel<T>(List<T> list, string strHeaderText, string strFileName, List<ExcelHeader> hearderList)

{

//保存

using (MemoryStream ms = this.Export<T>(list, strHeaderText, hearderList))

{

using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))

{

ms.Flush();

ms.Position = 0;

byte[] data = ms.ToArray();

fs.Write(data, 0, data.Length);

fs.Flush();

}

private short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)

{

short s = 0;

HSSFPalette XlPalette = workbook.GetCustomPalette();

HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);

if (XlColour == null)

{

if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)

{

if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)

{

NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;

XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);

}

else { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); }

s = XlColour.GetIndex();

}

else

s = XlColour.GetIndex();

return s;

}

/// <summary>

/// 从excel导入数据到数组

/// </summary>

/// <typeparam name="T"></typeparam>

/// <param name="ExcelFileStream"></param>

/// <param name="SheetIndex"></param>

/// <param name="HeaderRowIndex"></param>

/// <returns></returns>

public List<T> Import<T>(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, List<ExcelHeader> list)

{

HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);

HSSFSheet sheet = workbook.GetSheetAt(SheetIndex);

List<T> resultList = new List<T>();

HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);

int cellCount = headerRow.LastCellNum;

Dictionary<int, string> dict = new Dictionary<int, string>();

for (int i = headerRow.FirstCellNum; i < cellCount; i++)

{

dict.Add(i, headerRow.GetCell(i).StringCellValue);

}

int rowCount = sheet.LastRowNum;

T model = default(T);

for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)

{

HSSFRow row = sheet.GetRow(i);

model = Activator.CreateInstance<T>();//产生一个新的泛型对象

for (int j = row.FirstCellNum; j < cellCount; j++)

{

ExcelHeader header = list.Find(f => f.HeaderText == dict[j]);

string property = header.PropertyName;

string value = row.GetCell(j).ToString();

if (header.Mapping.Count > 0)

{

value = header.Mapping[value];

property = header.MappingTo;

}

PropertyInfo prop = model.GetType().GetProperty(property);

try

{

if (prop == null)

continue;

switch (header.DataType)

{

case "System.Decimal":

if (Regexlib.MatchDecimal(value))

prop.SetValue(model, Convert.ToDecimal(value), null);

break;

case "System.Int16":

if (Regexlib.MatchInt(value))

prop.SetValue(model, Convert.ToInt16(value), null);

break;

case "System.Int32":

if (Regexlib.MatchInt(value))

prop.SetValue(model, Convert.ToInt32(value), null);

break;

case "System.Boolean":

if(value!=null&&value.Length>0)

prop.SetValue(model, Convert.ToBoolean(value), null);

break;

case "System.DateTime":

if (Regexlib.MatchDate(value))

prop.SetValue(model, Convert.ToDateTime(value), null);

break;

default:

prop.SetValue(model, value, null);

break;

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

resultList.Add(model);

}

ExcelFileStream.Close();

workbook = null;

sheet = null;

return resultList;

}

最后就是使用方法，使用时只需修改一个泛型model的对象和XML的路径就可以了，当然需要做一个相应的XML文件，如下在于XML可以在程序外部修改。当然需要更改导入导出的字段时只需要修改XML文件就可以了。

导出方法

protected void btnLoadOut_Click(object sender, EventArgs e)

{

List<FineOffice.Modules.CNS_CensusMember> list = memberBll.GetListALL();

FineOffice.Web.WebExcelHelper excelHelper = new FineOffice.Web.WebExcelHelper();

List<FineOffice.Common.FileHelper.ExcelHeader> headerList = excelHelper.GetExcelHeader("~/Config/Template/CensusMember.xml");//XML路径名

FineOffice.Common.FileHelper.ExcelHelper toExcel = new FineOffice.Common.FileHelper.ExcelHelper();

System.IO.MemoryStream ms = toExcel.Export<FineOffice.Modules.CNS_CensusMember>(list, gridMember.Title, headerList);导出

byte[] output = ms.ToArray();

FineOffice.Web.FileTypeHelper typeHelper = new FineOffice.Web.FileTypeHelper();

Response.AddHeader("Content-Disposition", "attachment; filename=" + typeHelper.ToHexString(string.Format("{0}{1:yyyyMMdd}", gridMember.Title, DateTime.Now) + ".xls"));

Response.AddHeader("Content-Length", output.Length.ToString());

Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//设置输出流的字符集

Response.OutputStream.Write(output, 0, output.Length); //输出

Response.Flush();

Response.End();

}

导入方法

protected void btnLoadIn_Click(object sender, EventArgs e)

{

try

{

if (uploadFile.HasFile)

{

HttpPostedFile excel=uploadFile.PostedFile;

Stream fileStream = excel.InputStream;

FineOffice.Web.WebExcelHelper excelHelper = new FineOffice.Web.WebExcelHelper();

System.Collections.Generic.List<FineOffice.Common.FileHelper.ExcelHeader> headerList = excelHelper.GetExcelHeader("~/Config/Template/CensusMember.xml");

FineOffice.Common.FileHelper.ExcelHelper toExcel = new FineOffice.Common.FileHelper.ExcelHelper();

List<FineOffice.Modules.CNS_CensusMember> list= toExcel.Import<FineOffice.Modules.CNS_CensusMember>(fileStream, 0, 0, headerList);

fileStream.Close();

uploadFile.Reset();

gridMember.DataSource = list;

gridMember.DataBind();

}

catch (Exception ex)

{

Alert.Show(ex.Message);

}

至此就完成了

欢迎转载，转载请注明出处，博客园：http://www.cnblogs.com/feng84/archive/2013/05/18/3085010.html

http://www.lizinet.com/space/hai/Blog12/Post/13.aspx

posted on 2013-05-18 10:35 柏柏阅读(948) 评论(1) 编辑收藏举报