导出Excel
测试代码
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExcel_OnClick(object sender, EventArgs e)
{
//直接创建一个DataTable
DataTable dt = new DataTable("cart");
DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("point", Type.GetType("System.Int16"));
DataColumn dc3 = new DataColumn("number", Type.GetType("System.Int16"));
DataColumn dc4 = new DataColumn("totalpoint", Type.GetType("System.Int64"));
DataColumn dc5 = new DataColumn("prizeid", Type.GetType("System.String"));
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
//以上代码完成了DataTable的构架,但是里面是没有任何数据的
//填充了10条相同的记录进去
for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["prizename"] = "娃娃";
dr["point"] = 10;
dr["number"] = 1;
dr["totalpoint"] = 10;
dr["prizeid"] = "001";
dt.Rows.Add(dr);
}
ExcelHelper.DataTableToExcel(dt, "test");
}
实际项目
protected void btnExcel_OnClick(object sender, EventArgs e)
{
IQueryable<V_UnitJoin> q = DB.V_UnitJoins;
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
q = q.Where(r => r.UnitName.Contains(searchText) || r.TrueName.Contains(searchText));
}
int v = int.Parse(ddlState.SelectedValue);
if (v != -1)
{
q = q.Where(r => r.State == v);
}
var query = q.Select(m => new { 单位名称 = m.UnitName, 姓名 = m.TrueName, 身份证 = m.IDCard, 性别 = m.Sex, 出生年月 = m.Birth, 民族 = m.Nationality, 身份证地址 = m.Address, 职位 = m.Title, 入职时间 = m.JoinDate, 创建时间 = m.CreateTime }).AsEnumerable();
DataTable dt = query.ToDataTable(rec => new object[] { query });
ExcelHelper.DataTableToExcel(dt, "用工信息");
}
Excel导入
/// <summary>
/// 批量入职 excel导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void excelFileUpload_FileSelected(object sender, EventArgs e)
{
if (excelFileUpload.HasFile)
{
string fileName = excelFileUpload.ShortFileName;
string ext = Path.GetExtension(fileName);
if (!ValidateFileType(ext))
{
Alert.Show("无效的文件类型!");
return;
}
string virtualPath;
string filePath;
PerSave(fileName, out virtualPath, out filePath);
excelFileUpload.SaveAs(filePath);
DataTable dt = ExcelHelper.ExcelSheetImportToDataTable(filePath, "Sheet1");
#region 导入DB
string account = GetIdentityName();
Employee emp = DB.Employees.Where(m => m.Name == account).FirstOrDefault();
ZH_Unit unit = DB.ZH_Units.Where(m => m.AdminId == emp.ID).FirstOrDefault();
List<ZH_UnitJoin> list = new List<ZH_UnitJoin>();
if (unit != null)
{
foreach (DataRow dr in dt.Rows)
{
string name = dr["姓名"].ToString();
string idcard = dr["身份证号码"].ToString();
string joinDate = dr["入职时间"].ToString();
string address = dr["身份证地址"].ToString();
string title = dr["职位"].ToString();
ZH_UnitJoin item = new ZH_UnitJoin();
item.AdminId = emp.ID;
item.UnitId = unit.Id;
item.TrueName = name;
item.IDCard = idcard;
item.JoinDate = DateTime.Parse(joinDate);
item.Address = address;
item.Title = title;
item.State = 0;
item.CreateTime = DateTime.Now;
list.Add(item);
}
if (list.Count > 0)
{
DB.ZH_UnitJoins.AddRange(list);
DB.SaveChanges();
BindGrid();
}
}
else
{
Alert.ShowInTop("添加失败!企业账号未绑定!");
}
#endregion
}
}
/// <summary>
/// 验证文件后缀
/// </summary>
/// <param name="ext"></param>
/// <returns></returns>
private bool ValidateFileType(string ext)
{
bool res = false;
List<string> list = new List<string>()
{
".xls",
".xlsx"
};
if (list.Contains(ext))
{
res = true;
}
return res;
}
/// <summary>
/// 预上传,生产文件保存的真实路径
/// </summary>
/// <param name="fileName"></param>
/// <param name="virtualPath"></param>
/// <param name="filePath"></param>
private void PerSave(string fileName, out string virtualPath, out string filePath)
{
string datafolder = "/upload/" + DateTime.Now.Year.ToString() + ("0" + DateTime.Now.Month.ToString()).Substring(("0" + DateTime.Now.Month.ToString()).Length - 2, 2) + "/"; //文件夹
string newFileName = DateTime.Now.Ticks.ToString() + "_" + fileName; //新的文件名
virtualPath = datafolder + newFileName; //虚拟路径
filePath = Server.MapPath(virtualPath); //文件保存的地址
if (!Directory.Exists(Path.GetDirectoryName(filePath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(filePath));
}
}
NPOI 操作Excel 辅助类
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace Gzh.Utils.NPOI_2._2._1_binary_package
{
public class ExcelHelper
{
/// <summary>
/// Excel To DataTable
/// </summary>
/// <param name="filePath">绝对路径</param>
/// <param name="sheetName">SheetName</param>
/// <returns></returns>
public static DataTable ExcelSheetImportToDataTable(string filePath, string sheetName)
{
DataTable dt = new DataTable();
if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())
{
#region .xls文件处理:HSSFWorkbook
HSSFWorkbook hssfworkbook = null;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0) continue;//The firt row is title,no need import
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
else
{
#region .xlsx文件处理:XSSFWorkbook
XSSFWorkbook hssfworkbook = null;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
//SET EVERY COLUMN NAME
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (row.RowNum == 0) continue;//The firt row is title,no need import
for (int i = 0; i < row.LastCellNum; i++)
{
if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
#endregion
}
return dt;
}
/// <summary>
/// NPOI导出Excel文档
/// </summary>
/// <param name="dtData"></param>
/// <param name="fileName"></param>
public static void DataTableToExcel(DataTable dt, string fileName)
{
// 当前上下文
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
string myFileName = "MyExcelFile";
if (!string.IsNullOrEmpty(fileName))
{
myFileName = fileName;
}
#region NPOI 导出
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(myFileName);
if (dt != null)
{
int cols = dt.Columns.Count;
#region 表头
NPOI.SS.UserModel.IRow rowheader = sheet.CreateRow(0);
for (int j = 0; j < cols; j++)
{
rowheader.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
}
#endregion
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < cols; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
// ...
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
curContext.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", myFileName));
curContext.Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
#endregion
}
}
}
辅助类
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace Gzh.Utils.MyConvert
{
public static class MyConvert
{
#region List<T>/IEnumerable 转换到 DataTable
/// <summary>
/// Convert a List{T} to a DataTable.
/// </summary>
public static DataTable ToDataTable<T>(List<T> items)
{
var tb = new DataTable(typeof(T).Name);
PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in props)
{
Type t = GetCoreType(prop.PropertyType);
tb.Columns.Add(prop.Name, t);
}
foreach (T item in items)
{
var values = new object[props.Length];
for (int i = 0; i < props.Length; i++)
{
values[i] = props[i].GetValue(item, null);
}
tb.Rows.Add(values);
}
return tb;
}
/// <summary>
/// Determine of specified type is nullable
/// </summary>
private static bool IsNullable(Type t)
{
return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
}
/// <summary>
/// Return underlying type if type is Nullable otherwise return the type
/// </summary>
private static Type GetCoreType(Type t)
{
if (t != null && IsNullable(t))
{
if (!t.IsValueType)
{
return t;
}
else
{
return Nullable.GetUnderlyingType(t);
}
}
else
{
return t;
}
}
/*
* 示例:
* var query = from ....;
* DataTable dt = query.ToDataTable(rec => new object[] { query });
*
*/
public static DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;
// Could add a check to verify that there is an element 0
foreach (T rec in varlist)
{
// Use reflection to get property names, to create table, Only first time, others will follow
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
}
dtReturn.Rows.Add(dr);
}
return (dtReturn);
}
public delegate object[] CreateRowDelegate<T>(T t);
#endregion
public static IList<T> ConvertTo<T>(DataTable table)
{
if (table == null)
{
return null;
}
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
return ConvertTo<T>(rows);
}
public static IList<T> ConvertTo<T>(IList<DataRow> rows)
{
IList<T> list = null;
if (rows != null)
{
list = new List<T>();
foreach (DataRow row in rows)
{
T item = CreateItem<T>(row);
list.Add(item);
}
}
return list;
}
public static T CreateItem<T>(DataRow row)
{
T obj = default(T);
if (row != null)
{
obj = Activator.CreateInstance<T>();
foreach (DataColumn column in row.Table.Columns)
{
PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
try
{
object value = row[column.ColumnName];
prop.SetValue(obj, value, null);
}
catch
{ //You can log something here
//throw;
}
}
}
return obj;
}
}
/*
* 把查询结果以DataTable返回很方便,但是在检索数据时又很麻烦,没有模型类型检索方便。
所以很多人都是按照以下方式做的:
* // 获得查询结果
DataTable dt = DbHelper.ExecuteDataTable(...);
// 把DataTable转换为IList<UserInfo>
IList<UserInfo> users = ConvertToUserInfo(dt);
*
问题:如果此系统有几十上百个模型,那不是每个模型中都要写个把DataTable转换为此模型的方法吗?
解决:能不能写个通用类,可以把DataTable转换为任何模型,呵呵,这就需要利用反射和泛型了
* // 获得查询结果
DataTable dt = DbHelper.ExecuteDataTable(...);
// 把DataTable转换为IList<UserInfo>
IList<UserInfo> users = ModelConvertHelper<UserInfo>.ConvertToModel(dt);
*/
public class ModelConvertHelper<T> where T : new()
{
public static IList<T> ConvertToModel(DataTable dt)
{
// 定义集合
IList<T> ts = new List<T>();
// 获得此模型的类型
Type type = typeof(T);
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
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);
}
}
ts.Add(t);
}
return ts;
}
}
}