http://download.csdn.net/detail/diaodiaop/7611721
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Text.RegularExpressions;
using System;
using System.Reflection;
using System.Xml.Serialization;
using System.Xml;
using System.Xml.Schema; public class MyExcelHelper
{
public class x2003
{
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
} //转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
byte[] buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
} /// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
} public class x2007
{
#region Excel2007
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
} //转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
byte[] buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
} /// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
} public static DataTable GetDataTable(string filepath)
{
DataTable dt = new DataTable("xls");
if (Regex.IsMatch(filepath, ".xls$"))
{
dt = x2003.ExcelToTableForXLS(filepath);
}
else
{
dt = x2007.ExcelToTableForXLSX(filepath);
}
return dt;
} /// <summary>
/// 根据文件路径与单元头，获取我们需要的DataTable
/// </summary>
/// <param name="filepath">文件路径</param>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } }</param>
/// <returns></returns>
public static DataTable GetDataTable(string filepath, Dictionary<string, string> cellHeard)
{
DataTable dt = new DataTable("xls");
if (Regex.IsMatch(filepath, ".xls$"))
{
dt = x2003.ExcelToTableForXLS(filepath);
}
else
{
dt = x2007.ExcelToTableForXLSX(filepath);
} DataTable result = createDataTableKey(cellHeard);
DataRow row;
foreach(DataRow r in dt.Rows)
{
row = result.NewRow();
foreach (KeyValuePair<string, string> kvp in cellHeard)
{
if (dt.Columns.Contains(kvp.Value))
{
row[kvp.Key] = r[kvp.Value];
}
else {
row[kvp.Key] = string.Empty;
}
}
result.ImportRow(row);
}
return dt;
} /// <summary>
/// 根据单元头的键转换成我们 需要的DataTable
/// </summary>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } }</param>
/// <param name="dt">数据源</param>
/// <returns></returns>
public static DataTable ChangeDataTableKey(Dictionary<string, string> cellHeard,DataTable dt)
{
DataTable result = createDataTableKey(cellHeard);
DataRow row;
foreach (DataRow r in dt.Rows)
{
row = result.NewRow();
foreach (KeyValuePair<string, string> kvp in cellHeard)
{
if (dt.Columns.Contains(kvp.Value))
{
row[kvp.Key] = r[kvp.Value];
}
else
{
row[kvp.Key] = string.Empty;
}
}
result.Rows.Add(row);
}
return result;
} /// <summary>
/// 根据单元头值转换成我们 需要的DataTable
/// </summary>
/// <param name="cellHeard">单元头的Key和Value：{ { "UserName", "姓名" }, { "Age", "年龄" } }</param>
/// <param name="dt">数据源</param>
/// <returns></returns>
public static DataTable ChangeDataTableValue(Dictionary<string, string> cellHeard, DataTable dt)
{
DataTable result = createDataTableValue(cellHeard);
DataRow row;
foreach (DataRow r in dt.Rows)
{
row = result.NewRow();
foreach (KeyValuePair<string, string> kvp in cellHeard)
{
if (dt.Columns.Contains(kvp.Value))
{
row[kvp.Value] = r[kvp.Value];
}
else
{
row[kvp.Value] = string.Empty;
}
}
result.Rows.Add(row);
}
return result;
} private static DataTable createDataTableValue(Dictionary<string, string> cellHeard)
{
DataTable dt = new DataTable();
foreach (KeyValuePair<string, string> kvp in cellHeard)
{
DataColumn col = new DataColumn(kvp.Value);
dt.Columns.Add(col);
}
return dt;
} private static DataTable createDataTableKey(Dictionary<string, string> cellHeard)
{
DataTable dt = new DataTable();
foreach (KeyValuePair<string, string> kvp in cellHeard)
{
DataColumn col = new DataColumn(kvp.Key);
dt.Columns.Add(col);
}
return dt;
} public static List<T> ConvertToList<T>(DataTable dt) where T : new()
{
// 定义集合
List<T> list = new List<T>(); // 获得此模型的类型
Type type = typeof(T);
//定义一个临时变量
string tempName = string.Empty;
//遍历DataTable中所有的数据行 // 获得此模型的公共属性
PropertyInfo[] propertys = (new T()).GetType().GetProperties();
foreach (DataRow dr in dt.Rows)
{
T t = new T();
//遍历该对象的所有属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;//将属性名称赋值给临时变量
//检查DataTable是否包含此列（列名==对象的属性名）
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;//该属性不可写，直接跳出
//取值
object value = dr[tempName];
//如果非空，则赋给对象的属性
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
//对象添加到泛型集合中
list.Add(t);
} return list; }
} [Serializable]
public class SerializableDictionary<TKey, TValue> : Dictionary<TKey, TValue>, IXmlSerializable
{
public SerializableDictionary() { }
public void WriteXml(XmlWriter write) // Serializer
{
XmlSerializer KeySerializer = new XmlSerializer(typeof(TKey));
XmlSerializer ValueSerializer = new XmlSerializer(typeof(TValue)); foreach (KeyValuePair<TKey, TValue> kv in this)
{
write.WriteStartElement("SerializableDictionary");
write.WriteStartElement("key");
KeySerializer.Serialize(write, kv.Key);
write.WriteEndElement();
write.WriteStartElement("value");
ValueSerializer.Serialize(write, kv.Value);
write.WriteEndElement();
write.WriteEndElement();
}
}
public void ReadXml(XmlReader reader) // Deserializer
{
reader.Read();
XmlSerializer KeySerializer = new XmlSerializer(typeof(TKey));
XmlSerializer ValueSerializer = new XmlSerializer(typeof(TValue)); while (reader.NodeType != XmlNodeType.EndElement)
{
reader.ReadStartElement("SerializableDictionary");
reader.ReadStartElement("key");
TKey tk = (TKey)KeySerializer.Deserialize(reader);
reader.ReadEndElement();
reader.ReadStartElement("value");
TValue vl = (TValue)ValueSerializer.Deserialize(reader);
reader.ReadEndElement();
reader.ReadEndElement();
this.Add(tk, vl);
reader.MoveToContent();
}
reader.ReadEndElement(); }
public XmlSchema GetSchema()
{
return null;
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//DataTable enlist2 = MyExcelHelper.ChangeDataTableValue(GetHead(), MyExcelHelper.GetDataTable(@"E:\代码&数据备份\宁海专项资金\01代码\NhFunds\SFWebSite\UpFiles\ExcelFiles\地方贡献审核明细表模板.xls")); //datagrid1.DataSource = enlist2;
//datagrid1.DataBind();
//Session[_key] = enlist2; //return;
StringBuilder errorMsg = new StringBuilder(); // 错误信息
try
{ #region 1.获取Excel文件并转换为一个List集合 // 1.1存放Excel文件到本地服务器
HttpPostedFile filePost = Request.Files["filed"]; // 获取上传的文件
if (filePost == null)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('请选择文件上传');", true);
return;
}
string filePath = SaveExcelFile(filePost); // 保存文件并获取文件路径
if (string.IsNullOrEmpty(filePath))
{
Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "alert('文件上传失败');", true);
return;
} // 单元格抬头
// key：实体对象属性名称，可通过反射获取值
// value：属性对应的中文注解
Dictionary<string, string> cellheader = GetHead(); DataTable enlist = MyExcelHelper.ChangeDataTableValue(cellheader, MyExcelHelper.GetDataTable(filePath));
List<lz.T_GongXian> list = MyExcelHelper.ConvertToList<lz.T_GongXian>(MyExcelHelper.ChangeDataTableValue(cellheader, enlist));
datagrid1.DataSource = enlist;
datagrid1.DataBind();
Session[_key] = enlist;
#endregion // 3.TODO：对List集合进行持久化存储操作。如：存储到数据库 // 4.返回操作结果 }
catch (Exception ex)
{
throw ex;
}
} private Dictionary<string, string> GetHead()
{ SerializableDictionary<string, string> cellheader = new SerializableDictionary<string, string>();
cellheader.Add("id", "序号");
cellheader.Add("QYMC", "企业名称");
cellheader.Add("SBXM", "申报项目");
cellheader.Add("SDBZJY", "审定补助金额");
cellheader.Add("ZZS", "增值税（含免抵）");
cellheader.Add("YYS", "营业税");
cellheader.Add("GRSDS", "个人所得税(仅限个人独资企业）");
cellheader.Add("QYSDS", "企业所得税");
cellheader.Add("YHS", "印花税");
cellheader.Add("CJS", "城建税");
cellheader.Add("FCS", "房产税");
cellheader.Add("TDSYS", "土地使用税");
cellheader.Add("DFLCHJ", "地方留成合计数");
cellheader.Add("BZCY", "地方留成合计数与审定补助合计数差额");
cellheader.Add("HDBZZJ", "核定补助资金");
cellheader.Add("remark", "备注"); return cellheader;
}