C#,NPOI,Export Generic T Data

1.Nuget 下载NPOI;

Install-package NPOI -version 2.4.1

2.下载EF

install-package entityframework -version 6.2.0

3.添加数据,ef  model.edmx

4.建议使用NPOI.XSSF.UserModel;应为XSSF最大行数为1048575,而HSSFWorkBook 最大行数为65535行

5.数据量不能太大,要不然会FileStream写入时会OutOfMemoryException(多写几次),建议100万行以内

当内存溢出时,弹出如下的消息,将break前面的勾选框去掉.

70万数据大概需时160s;

Managed Debugging Assistant 'ContextSwitchDeadlock' : 'The CLR has been unable to transition from COM context 0x142a240 to COM context 0x142a2f8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.'

 

代码如下,亲测有效

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.XSSF.UserModel;
using System.Threading;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;

namespace ConsoleApp315
{
class Program
{
static Stopwatch watch = new Stopwatch();
static int exportNum = 0;
static string logPath = Directory.GetCurrentDirectory() + "\\ExportLog.txt";
static string exportMsg = "";
[STAThread]
static void Main(string[] args)
{
Thread exportThread = new Thread(() =>
{
using (AdventureWorks2017Entities db = new AdventureWorks2017Entities())
{
List<SalesOrderDetail> orderList = db.SalesOrderDetails.ToList();
orderList.AddRange(orderList);
orderList.AddRange(orderList);
ExportTEntity<SalesOrderDetail>(orderList.ToArray());
}
});

exportThread.SetApartmentState(ApartmentState.STA);
exportThread.Start();
exportThread.Join();

System.Windows.Forms.MessageBox.Show(exportMsg);
Console.ReadLine();
}

[STAThread]
static void ExportTEntity<T>(T[] arr)
{
if (arr != null && !arr.Any())
{
return;
}
exportNum = arr.Length;
using (SaveFileDialog sfd = new SaveFileDialog())
{
watch.Start();
XSSFWorkbook book;
sfd.Filter = "Excel Files(.xls)|*.xls|Excel Files(.xlsx)| *.xlsx | All Files | *.*";
sfd.FileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + Guid.NewGuid().ToString() + ".xlsx";
sfd.InitialDirectory = Directory.GetCurrentDirectory();
if (sfd.ShowDialog() == DialogResult.OK)
{
var firstRowData = arr.FirstOrDefault();
book = new XSSFWorkbook();
var sheet = book.CreateSheet("Sheet1");
var firstRow = sheet.CreateRow(0);
var propertiesArr = firstRowData.GetType().GetProperties().Where(x => !x.GetMethod.IsVirtual).ToArray();
for (int i = 0; i < propertiesArr.Length; i++)
{
var column = firstRow.CreateCell(i);
column.SetCellValue(propertiesArr[i].Name);
}
for (int i = 1; i <= arr.Length; i++)
{
var indexRow = sheet.CreateRow(i);
for (int j = 0; j < propertiesArr.Length; j++)
{
var indexColumn = indexRow.CreateCell(j);
var indexColumnName = propertiesArr[j];
var columnValue = indexColumnName.GetValue(arr[i - 1]);
if (columnValue != null)
{
indexColumn.SetCellValue(columnValue.ToString());
}
}
}
using (FileStream stream = File.OpenWrite(sfd.FileName))
{
book.Write(stream);
stream.Close();
}
watch.Stop();
exportMsg = string.Format($"Saved in {sfd.FileName}.\nThere are totally {exportNum} salesorderdetails and cost {watch.ElapsedMilliseconds} millseconds and now is {DateTime.Now.ToString("yyyyMMddHHmmssfff")} \n\n");
File.AppendAllText(logPath, exportMsg);
}
arr = null;
}
}
}
}

posted @   FredGrit  阅读(314)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
阅读排行:
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示