C#用NPOI导出导入Excel帮助类
作者:lbx_15887055073 时间:2022-09-27 00:23:43
本文实例为大家分享了C#用NPOI导出导入Excel帮助类的具体代码,供大家参考,具体内容如下
1、准备工作
在管理NuGet程序包中搜索 NPOI,安装 NPOI 到要项目中。
2、帮助类
/// <summary>
/// DataTable写入Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="strExcelFileName"></param>
/// <returns></returns>
public bool GridToExcelByNPOI(DataTable dt, string strExcelFileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="fileName">fileName</param>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
IWorkbook workbook = null;
FileStream fs = null;
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) 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);
return null;
}
}
3、使用
(1)DataTable写入Excel
var Sql = "select id,name from test";
var dt = DB.DB.ExecuteDataTable(Sql);
//保存文件
string saveName = "temp_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string DownloadExcelDic = "DownloadExcel";
string webDic = AppDomain.CurrentDomain.BaseDirectory + DownloadExcelDic;
if (!Directory.Exists(webDic))//判断目录是否存在
{
Directory.CreateDirectory(webDic);//不存在则创建新目录
}
string path = webDic + @"\" + saveName; //目录+文件名+后缀名
bool isBuild = GridToExcelByNPOI(dt, path);
(2)导入Excel返回DataTable
//1、先上传文件,将文件保存到服务器上
//2、导入Excel
string fileDic = "";//文件路径
var dt = ExcelToDataTable(fileDic, "sheet1", true);
来源:https://blog.csdn.net/lbx_15887055073/article/details/82194414
标签:C#,NPOI,Excel
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
java连接SQL Server数据库的超详细教程
2023-04-05 21:46:22
![](https://img.aspxhome.com/file/2023/5/63095_0s.png)
Spring探秘之如何妙用BeanPostProcessor
2021-07-01 22:41:46
Java根据ip地址获取归属地实例详解
2023-11-25 06:24:38
![](https://img.aspxhome.com/file/2023/6/60186_0s.jpg)
C#实现伪装文件夹功能
2023-04-28 14:21:38
![](https://img.aspxhome.com/file/2023/0/76410_0s.png)
VS2019配置OpenCV时找不到Microsoft.Cpp.x64.user的解决方法
2023-07-01 01:57:36
![](https://img.aspxhome.com/file/2023/1/100301_0s.png)
详解Spring Security如何配置JSON登录
2023-02-08 17:39:07
![](https://img.aspxhome.com/file/2023/3/72353_0s.png)
Android6.0 消息机制原理解析
2023-08-06 12:19:44
springcloud之自定义简易消费服务组件
2022-01-29 00:18:24
![](https://img.aspxhome.com/file/2023/8/61878_0s.png)
Java中的Kotlin 内部类原理
2021-10-26 23:02:13
C#自定义针对URL地址的处理类实例
2022-09-12 16:54:02
C#使⽤XmlReader和XmlWriter操作XML⽂件
2023-12-13 10:25:00
Java中final变量使用总结
2022-09-29 08:32:00
Java使用Apache.POI中HSSFWorkbook导出到Excel的实现方法
2022-05-24 17:14:13
![](https://img.aspxhome.com/file/2023/3/63323_0s.png)
Java中Steam流的用法详解
2021-12-16 14:18:50
java读取文件内容的三种方法代码片断分享(java文件操作)
2023-11-21 06:53:20
C语言头文件<string.h>函数详解
2023-07-01 18:59:34
![](https://img.aspxhome.com/file/2023/2/105162_0s.png)
java生成json实现隐藏掉关键属性
2021-12-07 17:31:29
Java调取创蓝253短信验证码的实现代码
2021-11-05 00:48:10
IDEA全量替换一次性解决旧项目并将所有文件换行符改为LF问题
2022-09-17 18:44:32
![](https://img.aspxhome.com/file/2023/7/71927_0s.png)
Android实现CoverFlow效果控件的实例代码
2023-06-23 13:12:43
![](https://img.aspxhome.com/file/2023/5/83545_0s.png)