C# EF ExcelHelper 导入导出
【摘要】
//EF导出示例 var query = from u in orderQuery join pro in dbContext.Project on u.projectNo equals pro.projectNo join org in dbContext.Org on u.orgNo equ...
-
//EF导出示例
-
var query = from u in orderQuery
-
join pro in dbContext.Project on
-
u.projectNo equals pro.projectNo
-
join org in dbContext.Org on
-
u.orgNo equals org.orgNo into JoinedEmpDept
-
from dept in JoinedEmpDept.DefaultIfEmpty()
-
select new
-
{
-
学校名称= pro.projectName,
-
所属班级=dept.orgName,
-
费用类型=u.orderName,
-
学生姓名=u.userName,
-
身份证号=u.userNo,
-
手机号=u.userPhone,
-
订单编号=u.orderNo,
-
费用金额=u.orderAmount/100.0,
-
支付状态=u.payState==1?"已支付":"未支付",
-
支付时间=u.payTime,
-
银行流水号=u.ccbOrderId,
-
添加时间=u.createTime,
-
备注=u.remark
-
};
-
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
-
string file = localPath + fileName;
-
using (ExcelHelper excelHelper = new ExcelHelper(file))
-
{
-
var list = query.OrderByDescending(u => u.添加时间).ToList();
-
excelHelper.ListToExcel(list,"费用");
-
return ReturnNode.ReturnSuccess(fileName);
-
}
-
//EF导出示例2，SQL直接导出
-
try
-
{
-
var sql = "SELECT * from user ";
-
-
//输出排名
-
//sql = string.Format("SELECT @rank :=@rank + 1 AS 排名,A.* FROM ( {0}) A, (SELECT @rank := 0) B;", sql);
-
-
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
-
string file = localPath + fileName;
-
using (ExcelHelper excelHelper = new ExcelHelper(file))
-
{
-
System.Data.Common.DbCommand cmd = dbContext.Database.Connection.CreateCommand();
-
cmd.CommandText = sql;
-
cmd.CommandType = CommandType.Text;
-
//转成sqlcommand正常查询语句。
-
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
-
da.SelectCommand = cmd as MySql.Data.MySqlClient.MySqlCommand;
-
DataTable dt = new DataTable();
-
da.Fill(dt);
-
excelHelper.DataTableToExcel(dt, "用户表",true);
-
return ReturnNode.ReturnSuccess(serverPath + fileName);
-
}
-
}
-
catch (Exception ex)
-
{
-
return ReturnNode.ReturnError(ex.Message);
-
}
-
//导入示例
-
string file = "C:\\Data\\模板.xlsx";
-
using (ExcelHelper excelHelper = new ExcelHelper(file))
-
{
-
DataTable dt = excelHelper.ExcelToDataTable("Sheet1", true);
-
-
int num = 0;
-
for (int i = 0; i < dt.Rows.Count; ++i)
-
{
-
DataRow row = dt.Rows[i];
-
string index = row[0].ToString();
-
string orgName = row[1].ToString();
-
string orgNo = row[2].ToString();
-
string orderName = row[3].ToString();
-
string orderAmount = row[4].ToString();
-
string userName = row[5].ToString();
-
string userNo = row[6].ToString();
-
string userPhone = row[7].ToString();
-
string remark = row[8].ToString();
-
-
-
num++;
-
}
-
}
-
using NPOI.HSSF.UserModel;
-
using NPOI.SS.UserModel;
-
using NPOI.XSSF.UserModel;
-
using System;
-
using System.Collections.Generic;
-
using System.ComponentModel;
-
using System.Data;
-
using System.Diagnostics;
-
using System.IO;
-
using System.Linq;
-
using System.Text;
-
using System.Threading.Tasks;
-
-
namespace YFAPICommon.Lib
-
{
-
class ExcelHelper : IDisposable
-
{
-
private string fileName = null; //文件名
-
private NPOI.SS.UserModel.IWorkbook workbook = null;
-
private FileStream fs = null;
-
private bool disposed;
-
-
public ExcelHelper(string fileName)
-
{
-
this.fileName = fileName;
-
disposed = false;
-
}
-
-
/// <summary>
-
/// 将DataTable数据导入到excel中
-
/// </summary>
-
/// <param name="data">要导入的数据</param>
-
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
-
/// <param name="sheetName">要导入的excel的sheet的名称</param>
-
/// <returns>导入数据行数(包含列名那一行)</returns>
-
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
-
{
-
int i = 0;
-
int j = 0;
-
int count = 0;
-
ISheet sheet = null;
-
-
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
-
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
-
workbook = new XSSFWorkbook();
-
else if (fileName.IndexOf(".xls") > 0) // 2003版本
-
workbook = new HSSFWorkbook();
-
-
try
-
{
-
if (workbook != null)
-
{
-
sheet = workbook.CreateSheet(sheetName);
-
}
-
else
-
{
-
return -1;
-
}
-
-
if (isColumnWritten == true) //写入DataTable的列名
-
{
-
IRow row = sheet.CreateRow(0);
-
for (j = 0; j < data.Columns.Count; ++j)
-
{
-
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
-
}
-
count = 1;
-
}
-
else
-
{
-
count = 0;
-
}
-
-
for (i = 0; i < data.Rows.Count; ++i)
-
{
-
IRow row = sheet.CreateRow(count);
-
for (j = 0; j < data.Columns.Count; ++j)
-
{
-
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
-
}
-
++count;
-
}
-
workbook.Write(fs); //写入到excel
-
return count;
-
}
-
catch (Exception ex)
-
{
-
Console.WriteLine("Exception: " + ex.Message);
-
return -1;
-
}
-
}
-
-
/// <summary>
-
/// 将List数据导入到excel中
-
/// </summary>
-
/// <param name="data">要导入的数据</param>
-
/// <param name="sheetName">要导入的excel的sheet的名称</param>
-
/// <returns>导入数据行数(包含列名那一行)</returns>
-
public int ListToExcel<T>(List<T> data, string sheetName)
-
{
-
-
ISheet sheet = null;
-
-
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
-
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
-
workbook = new XSSFWorkbook();
-
else if (fileName.IndexOf(".xls") > 0) // 2003版本
-
workbook = new HSSFWorkbook();
-
-
try
-
{
-
if (workbook != null)
-
{
-
sheet = workbook.CreateSheet(sheetName);
-
}
-
else
-
{
-
return -1;
-
}
-
-
-
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
-
IRow row0 = sheet.CreateRow(0);
-
for (int i = 0; i < properties.Count; i++)
-
{
-
PropertyDescriptor property = properties[i];
-
Debug.WriteLine(property.Name);
-
row0.CreateCell(i).SetCellValue(property.Name);
-
}
-
-
int count = 1;
-
object[] values = new object[properties.Count];
-
foreach (T item in data)
-
{
-
IRow row = sheet.CreateRow(count);
-
for (int j = 0; j < values.Length; j++)
-
{
-
object obj = properties[j].GetValue(item);
-
Debug.WriteLine(properties[j].GetValue(item));
-
row.CreateCell(j).SetCellValue(obj==null?"":obj.ToString());
-
-
}
-
++count;
-
}
-
workbook.Write(fs); //写入到excel
-
return count;
-
}
-
catch (Exception ex)
-
{
-
Console.WriteLine("Exception: " + ex.Message);
-
return -1;
-
}
-
}
-
-
/// <summary>
-
/// 将excel中的数据导入到DataTable中
-
/// </summary>
-
/// <param name="sheetName">excel工作薄sheet的名称</param>
-
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
-
/// <returns>返回的DataTable</returns>
-
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
-
{
-
ISheet sheet = null;
-
DataTable data = new DataTable();
-
int startRow = 0;
-
try
-
{
-
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
-
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
-
workbook = new XSSFWorkbook(fs);
-
else if (fileName.IndexOf(".xls") > 0) // 2003版本
-
workbook = new HSSFWorkbook(fs);
-
-
if (sheetName != null)
-
{
-
sheet = workbook.GetSheet(sheetName);
-
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
-
{
-
sheet = workbook.GetSheetAt(0);
-
}
-
}
-
else
-
{
-
sheet = workbook.GetSheetAt(0);
-
}
-
if (sheet != null)
-
{
-
IRow firstRow = sheet.GetRow(0);
-
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
-
-
if (isFirstRowColumn)
-
{
-
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
-
{
-
ICell cell = firstRow.GetCell(i);
-
if (cell != null)
-
{
-
string cellValue = cell.StringCellValue;
-
if (cellValue != null)
-
{
-
DataColumn column = new DataColumn(cellValue);
-
data.Columns.Add(column);
-
}
-
}
-
}
-
startRow = sheet.FirstRowNum + 1;
-
}
-
else
-
{
-
startRow = sheet.FirstRowNum;
-
}
-
-
//最后一列的标号
-
int rowCount = sheet.LastRowNum;
-
for (int i = startRow; i <= rowCount; ++i)
-
{
-
IRow row = sheet.GetRow(i);
-
if (row == null)
-
break;
-
//continue; //没有数据的行默认是null
-
-
DataRow dataRow = data.NewRow();
-
for (int j = row.FirstCellNum; j < cellCount; ++j)
-
{
-
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
-
dataRow[j] = row.GetCell(j).ToString();
-
}
-
data.Rows.Add(dataRow);
-
}
-
}
-
-
return data;
-
}
-
catch (Exception ex)
-
{
-
Console.WriteLine("Exception: " + ex.Message);
-
//System.Windows.Forms.MessageBox.Show(ex.Message);
-
return null;
-
}
-
}
-
-
internal void ListToExcel(List<object> list, string v)
-
{
-
throw new NotImplementedException();
-
}
-
-
public void Dispose()
-
{
-
Dispose(true);
-
GC.SuppressFinalize(this);
-
}
-
-
protected virtual void Dispose(bool disposing)
-
{
-
if (!this.disposed)
-
{
-
if (disposing)
-
{
-
if (fs != null)
-
fs.Close();
-
}
-
-
fs = null;
-
disposed = true;
-
}
-
}
-
}
-
}
文章来源: zzzili.blog.csdn.net，作者：清雨小竹，版权归原作者所有，如需转载，请联系作者。
原文链接：zzzili.blog.csdn.net/article/details/95599785
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）