C#使用NPOI实现Excel导入导出功能

C#使⽤NPOI实现Excel导⼊导出功能本⽂实例为⼤家分享了C#使⽤NPOI实现Excel导⼊导出的具体代码，供⼤家参考，具体内容如下Excel导⼊
使⽤OpenFileDiolog控件和button结合，选择⽂件导⼊，将路径显⽰在⽂本框
设置按钮点击事件，将⽂件路径赋给textBox.Text
private void Department_SUM_Click(object sender, EventArgs e)
{
OpenFileDialog open = new OpenFileDialog();
open.ShowDialog();
textBox1.Text = open.FileName;
}
实现excel导⼊，通过textBox1.Text来获取⽂件路径
private void button_Excel_Click(object sender, EventArgs e)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
String txtpath = textBox1.Text;
fs = File.OpenRead(txtpath);
workbook = new XSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0); //获取excel表格的第⼀个sheet
if (sheet != null)
{
//⾏的LastRowNum是0~N-1
//列的LastCellNum是1~N
int rowCount = stRowNum;
if (rowCount > 0)
{
IRow firstrow = sheet.GetRow(0);
int cellCount = stCellNum;
for (int i = 0; i <= rowCount - 1; i++)
{
//获取⾏的第6和第7列数据，如果cell类型是⽂本，则通过StringCellValue取值
//如果cell类型是数值，则通过NumericCellValue来取值
row = sheet.GetRow(i + 1);
row.Cells[5].NumericCellValue;
row.Cells[6].StringCellValue;
//可以将Cell的数据存放在list中，这⾥假设将两列cell的数据存⼊list1，list2
}
fs.Close();
}
//实际存放DataTable的位置
//调⽤⾃定义⽅法，实现导出
Add_DataTable_To_Excel(txtpath, table, sheet_name);
}
}
要实现excel导出，先将程序中的excel存为DataTable格式
本段代码存在于上⾯代码“//实际存放DataTable的位置”位置
DataTable table = new DataTable();
DataRow dr;
table.Columns.Add("列名1", System.Type.GetType("System.String"));
table.Columns.Add("列名2", System.Type.GetType("System.Double"));
for (int i = 0; i < list4.Count; i++)
{
dr = table.NewRow();
dr["列名1"] = list1i];
dr["列名2"] = list2[i].ToString("0.0000"); //将存⼊的数据格式保存为保留四位⼩数
table.Rows.Add(dr);
}
通过⽅法导出excel，传参为⽂件路径，DataTable，表名
通过获取要导⼊数据的⽬标excel的内容，导⼊数据，要将excel导出的⽅式
public bool Add_DataTable_To_Excel(string output_file_path, DataTable dt, string sheet_name)
{
FileStream fs = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
XSSFWorkbook xssfworkbook = null;
fs = new FileStream(output_file_path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
xssfworkbook = new XSSFWorkbook(fs);
sheet = xssfworkbook.GetSheet(sheet_name);
//设置马上要使⽤的Cell数据格式
IDataFormat dataformat = xssfworkbook.CreateDataFormat();
ICellStyle style0 = xssfworkbook.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("0.0000");
ICellStyle style1 = xssfworkbook.CreateCellStyle();
style1.DataFormat = dataformat.GetFormat("0.00%");
if (sheet != null)
{
int rowCount = stRowNum;
if (rowCount > 0)
{
IRow firstrow = sheet.GetRow(0);
int cellCount = stCellNum;
for (int i = 0; i <= rowCount - 1; i++)
{
row = sheet.GetRow(i + 1);
//表中有⾏为空，将空的⾏影响消除
if (!"".Equals(row.Cells[code_index].StringCellValue))
{
row = sheet.GetRow(i + 1);
for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
if (row.Cells[code_index].StringCellValue.Equals(dt.Rows[j][0]))
{
//遍历将DataTable中的数据存⼊Cell的值 row.Cells[1].SetCellValue(Convert.ToDouble(dt.Rows[j][0].ToString()));
row.Cells[1].CellStyle = style0;
row.Cells[2].SetCellValue(Convert.ToDouble(dt.Rows[j][1].ToString()) / Convert.ToDouble(dt.Rows[j][1].ToString())); row.Cells[2].CellStyle = style1;
}
}
}
}
}
}
//导出excel
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
using (FileStream fss = new FileStream(txtpath, FileMode.Create, FileAccess.Write)) //保存为Excel⽂件 {
fss.Write(buf, 0, buf.Length);
fss.Flush();
}
return true;
}
基础的Excel⽂件的导⼊导出功能到这⾥全部完成
以上就是本⽂的全部内容，希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。