[點晴永久免費OA]【C#】使用NPOI封裝能用于絕大部分場景的導出Execl文件的輔助類
當前位置：點晴教程→點晴OA辦公管理信息系統
→『 經驗分享&問題答疑 』
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace C.Customization.Framework
{
/// <summary>
/// Npoi輔助類
/// </summary>
public class NpoiHepler
{
/// <summary>
/// 實體類集合導出指定字段到EXCLE
/// </summary>
/// <param name="cellHeard">單元頭的Key和Value：{ { "UserName", "姓名" }, { "Age", "年齡" } };</param>
/// <param name="enList">數據源</param>
/// <param name="sheetName">工作表名稱</param>
/// <param name="filePath">路徑.xls</param>
/// <returns>
/// 文件的下載地址
/// </returns>
public static MessageInfo EntitysToExcel(Dictionary<string, string> cellHeard, IList enList, string sheetName, string filePath)
{
try
{
// 1.檢測是否存在文件夾，若不存在就建立個文件夾
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
Directory.createDirectory(directoryName);
}
// 2.解析單元格頭部，設置單元頭的中文名稱
HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
ISheet sheet = workbook.createSheet(sheetName); // 工作表
IRow row = sheet.createRow(0);
List<string> keys = cellHeard.Keys.ToList();
for (int i = 0; i < keys.Count; i++)
{
row.createCell(i).SetCellValue(cellHeard[keys[i]]); // 列名為Key的值
sheet.SetColumnWidth(i, 30 * 256);
}
// 3.List對象的值賦值到Excel的單元格里
int rowIndex = 1; // 從第二行開始賦值(第一行已設置為單元頭)
foreach (var en in enList)
{
IRow rowTmp = sheet.createRow(rowIndex);
for (int i = 0; i < keys.Count; i++) // 根據指定的屬性名稱，獲取對象指定屬性的值
{
string cellValue = ""; // 單元格的值
object properotyValue = null; // 屬性的值
System.Reflection.PropertyInfo properotyInfo = null; // 屬性的信息
// 3.1 若屬性頭的名稱包含'.',就表示是子類里的屬性，那么就要遍歷子類，eg：UserEn.UserName
if (keys[i].IndexOf(".") >= 0)
{
// 3.1.1 解析子類屬性(這里只解析1層子類，多層子類未處理)
string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
string subClassName = properotyArray[0]; // '.'前面的為子類的名稱
string subClassProperotyName = properotyArray[1]; // '.'后面的為子類的屬性名稱
System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 獲取子類的類型
if (subClassInfo != null)
{
// 3.1.2 獲取子類的實例
var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
// 3.1.3 根據屬性名稱獲取子類里的屬性類型
properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(subClassEn, null); // 獲取子類屬性的值
}
}
}
else
{
// 3.2 若不是子類的屬性，直接根據屬性名稱獲取對象對應的屬性
properotyInfo = en.GetType().GetProperty(keys[i]);
if (properotyInfo != null)
{
properotyValue = properotyInfo.GetValue(en, null);
}
}
// 3.3 屬性值經過轉換賦值給單元格值
if (properotyValue != null)
{
cellValue = properotyValue.ToString();
// 3.3.1 對時間初始值賦值為空
if (cellValue.Trim() == "0001/1/1 0:00:00"
|| cellValue.Trim() == "0001/1/1 23:59:59"
|| cellValue.Trim() == "1970-01-01 00:00:00")
{
cellValue = "";
}
}
// 3.4 填充到Excel的單元格里
rowTmp.createCell(i).SetCellValue(cellValue);
}
rowIndex++;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.create);
workbook.Write(file);
file.Close();
// 5.返回下載路徑
return new MessageInfo() { IsSucceed = true, Message = filePath };
}
catch (Exception ex)
{
return new MessageInfo() { IsSucceed = false, Message = ex.Message };
}
}
/// <summary>
/// 實體類集合導出指定字段到EXCLE
/// </summary>
/// <param name="cellHeard">單元頭的Key和Value：{ { "UserName", "姓名" }, { "Age", "年齡" } };</param>
/// <param name="enList">數據源</param>
/// <param name="sheetName">工作表名稱</param>
/// <param name="filePath">路徑.xls</param>
/// <returns>
/// 文件的下載地址
/// </returns>
public static MessageInfo DataTableToExcel(Dictionary<string, string> cellHeard, DataTable enList, string sheetName, string filePath)
{
try
{
// 1.檢測是否存在文件夾，若不存在就建立個文件夾
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
Directory.createDirectory(directoryName);
}
// 2.解析單元格頭部，設置單元頭的中文名稱
HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
ISheet sheet = workbook.createSheet(sheetName); // 工作表
IRow row = sheet.createRow(0);
List<string> keys = cellHeard.Keys.ToList();
for (int i = 0; i < keys.Count; i++)
{
row.createCell(i).SetCellValue(cellHeard[keys[i]]); // 列名為Key的值
sheet.SetColumnWidth(i, 30 * 256);
}
// 3.List對象的值賦值到Excel的單元格里
int rowIndex = 1; // 從第二行開始賦值(第一行已設置為單元頭)
for (int en=0;en<enList.Rows.Count;en++)
{
IRow rowTmp = sheet.createRow(rowIndex);
for (int i = 0; i < keys.Count; i++) // 根據指定的屬性名稱，獲取對象指定屬性的值
{
string cellValue = ""; // 單元格的值
object properotyValue = enList.Rows[en][keys[i]]; // 屬性的值
// 3.3 屬性值經過轉換賦值給單元格值
if (properotyValue != null)
{
cellValue = properotyValue.ToString();
// 3.3.1 對時間初始值賦值為空
if (cellValue.Trim() == "0001/1/1 0:00:00"
|| cellValue.Trim() == "0001/1/1 23:59:59"
|| cellValue.Trim() == "1970-01-01 00:00:00")
{
cellValue = "";
}
}
// 3.4 填充到Excel的單元格里
rowTmp.createCell(i).SetCellValue(cellValue);
}
rowIndex++;
}
// 4.生成文件
FileStream file = new FileStream(filePath, FileMode.create);
workbook.Write(file);
file.Close();
// 5.返回下載路徑
return new MessageInfo() { IsSucceed = true, Message = filePath };
}
catch (Exception ex)
{
return new MessageInfo() { IsSucceed = false, Message = ex.Message };
}
}
}
}
/// <summary>
/// 導出
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void Tb1_Export_Click(object sender, EventArgs e)
{
//導出
List<Mem_MemberInfo> mems = Mem_MemberService.GetInstance().GetListAll();
Dictionary<string, string> cellHead = new Dictionary<string, string>();
cellHead[nameof(Mem_MemberInfo.UserName)] = "用戶昵稱";
cellHead[nameof(Mem_MemberInfo.Mobile)] = "手機號";
cellHead[nameof(Mem_MemberInfo.Balance)] = "余額";
cellHead[nameof(Mem_MemberInfo.RealName)] = "真實姓名";
cellHead[nameof(Mem_MemberInfo.IdCardNum)] = "身份證號碼";
cellHead[nameof(Mem_MemberInfo.createTime)] = "注冊時間";
cellHead[nameof(Mem_MemberInfo.Freeze)] = "凍結金額";
cellHead[nameof(Mem_MemberInfo.IdentityName)] = "等級";
string filename = $"用戶數據{DateTime.Now:yyyyMMddHHmmss}.xls";
string filepath = Server.MapPath($"{PageParam.DocumentPath}{filename}");
MessageInfo msg = NpoiHepler.EntitysToExcel(cellHead, mems, "用戶列表", filepath);
if (msg.IsSucceed == false)
{
Alert.ShowInTop("導出失敗" + msg.Message, MessageBoxIcon.Error);
return;
}
FileInfo file = new FileInfo(msg.Message);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
Response.AddHeader("Content-Length", file.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "gb2312");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(filepath);
Response.Flush();
Response.End();
}
/// <summary>
/// 導出
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void Tb1_Export_Click(object sender, EventArgs e)
{
//導出
PageDataBaseInfo pagedata = FUHelper.GridPageData(Grid1, ttbSearch.Text);
pagedata.PageSize = 0;
DataTable dt = Record_WithdrawalService.GetInstance().FindDt(pagedata, "2", RblStatus.selectedValue);
Dictionary<string, string> cellHead = new Dictionary<string, string>();
cellHead["SysNo"] = "系統編號";
cellHead["Mobile"] = "提現賬戶";
cellHead["StateName"] = "狀態";
cellHead["createTime"] = "申請時間";
cellHead["Money"] = "提現金額";
cellHead["Balance"] = "賬戶余額";
cellHead["Freeze"] = "凍結金額";
cellHead["Remark"] = "備注";
string filename = $"提現記錄{DateTime.Now:yyyyMMddHHmmss}.xls";
string filepath = Server.MapPath($"{PageParam.DocumentPath}{filename}");
MessageInfo msg = NpoiHepler.DataTableToExcel(cellHead, dt, "提現記錄", filepath);
if (msg.IsSucceed == false)
{
Alert.ShowInTop("導出失敗" + msg.Message, MessageBoxIcon.Error);
return;
}
FileInfo file = new FileInfo(msg.Message);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
Response.AddHeader("Content-Length", file.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "gb2312");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(filepath);
Response.Flush();
Response.End();
}
該文章在 2022/11/25 15:40:49 編輯過 |
關鍵字查詢
相關文章
正在查詢... 
|
400 186 1886
