c# npoi 导出excel 简单应用

最新推荐文章于 2023-07-05 20:00:06 发布

淡淡飘零

最新推荐文章于 2023-07-05 20:00:06 发布

阅读量168

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_29954875/article/details/88033309

本文介绍了如何使用C#结合NPOI库将DataTable和List对象的数据导出为Excel文件。首先展示了如何将DataTable转换为Excel，然后讲解了如何处理List对象并创建对应的Excel工作表。代码中包含创建工作簿、设置表头和数据、保存到指定文件夹的步骤。

参考文章地址：https://blog.youkuaiyun.com/smartsmile2012/article/details/81559320

1. DataTable 导出excel

public static int TableToExcel(DataTable dt, string fileName, Dictionary<string, string> cellheader)
{
string selectPath = string.Empty;
int result = -1;
try
{
FolderBrowserDialog dialog = new FolderBrowserDialog();
dialog.Description = "请选择需存储的文件夹";

if (dialog.ShowDialog() == DialogResult.OK)
{
if (string.IsNullOrEmpty(dialog.SelectedPath))
{
FuncMessage.ShowErrorMsg("文件夹路径不能为空");
}
selectPath = dialog.SelectedPath;
}

string urlPath =Path.Combine( selectPath,fileName);
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") :

workbook.CreateSheet(dt.TableName);

//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
foreach (var item in cellheader)
{
if (dt.Columns[i].ColumnName == item.Key)
{
cell.SetCellValue(item.Value);
break;
}
}
}

//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}

using (FileStream fs = File.OpenWrite(urlPath))
{
workbook.Write(fs);
result = 0;
}
}
catch (Exception ex)
{
return -1;
}
return result;
}

2. List<> 导出excel

List<KeyValuePair<string, string>> cellheader = new List<KeyValuePair<string, string>>();

cellheader.Add(new KeyValuePair<string, string>("xxxxx", "xxxxxxx"));

......

public static int TableToExcel<T>(List<T> dataList, string fileName, List<KeyValuePair<string, string>> cellheader)
{
string selectPath = string.Empty;
int result = -1;

try
{
FolderBrowserDialog dialog = new FolderBrowserDialog();
dialog.Description = "请选择需存储的文件夹";

if (dialog.ShowDialog() == DialogResult.OK)
{
if (string.IsNullOrEmpty(dialog.SelectedPath))
{
FuncMessage.ShowErrorMsg("文件夹路径不能为空");
}
selectPath = dialog.SelectedPath;
}

string urlPath = Path.Combine(selectPath, fileName);

IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < cellheader.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(cellheader[i].Value);
}

Type t = typeof(T);
int rowIndex = 1;
foreach (T item in dataList)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int n = 0; n < cellheader.Count; n++)
{
object pValue = t.GetProperty(cellheader[n].Key).GetValue(item, null);
dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
}
rowIndex++;
}
using (FileStream fs = File.OpenWrite(urlPath))
{
workbook.Write(fs);
result = 0;
}
}
catch (Exception ex)
{
return -1;
}
return result;
}