NPOI读取EXCEL表至datagridview

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace ExcelToDataGridView
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{

DataTable dt = null;
OpenFileDialog ofd = new OpenFileDialog();//创建一个文件打开窗口
ofd.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";//读取文件格式
if (ofd.ShowDialog() == DialogResult.Cancel)//判断读取文件是否成功
{
MessageBox.Show("你选择的路径有误或者是你打开的文件有误");
return;
}
FileStream fs = new FileStream(ofd.FileName, FileMode.Open, FileAccess.Read);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);//创建一个NPOI的文件流
int sheetCount = workbook.NumberOfSheets;
for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(sheetIndex);
if (sheet == null) continue;
int max = 0;
int Index = 0;
for (int i = 0; i < sheet.LastRowNum; i++)
{
NPOI.SS.UserModel.IRow row1 = sheet.GetRow(i);
if (row1.LastCellNum > max)
{
max = row1.LastCellNum;
Index = i;

}
}
NPOI.SS.UserModel.IRow row = sheet.GetRow(Index);
if (row == null) continue;
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
if (firstCellNum == lastCellNum) continue;
dt = new DataTable(sheet.SheetName);
for(int i = firstCellNum; i < lastCellNum; i++)
{
if (row.GetCell(i) != null)//判断cell单元格是否为空
{
dt.Columns.Add(row.GetCell(i).ToString(), typeof(string));//将单元格值转成string类型

}
//dt.Columns.Add(row.GetCell(i).ToString(), typeof(string));
}
for (int i = 1; i <=sheet.LastRowNum; i++)
{
DataRow newRow= dt.Rows.Add();
//lastCellNum = row.LastCellNum;
for (int j = firstCellNum; j < lastCellNum; j++)
{
ICell cell = sheet.GetRow(i).GetCell(j);

if (cell !=null)
{
string str= cell.ToString();
newRow[j] = str;
}
else
{
newRow[j] = null;
}

}
}

}

dataGridView1.DataSource = dt;

}

 

//datagridview导出

private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog save = new SaveFileDialog();
save.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
if (save.ShowDialog() == DialogResult.Cancel)
{
MessageBox.Show("你选择的路径有误或者是你的保存文件格式不对！");
return;
}
NPOI.SS.UserModel.IWorkbook workbook = null;
if (save.FilterIndex == 1)
{
workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

}
else
{
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();

}
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("test_001");
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
int index = 0;
foreach (DataGridViewColumn item in dataGridView1.Columns)
{
if (item.Visible)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(item.ToString());
index++;
}
}
for(int i=0;i<dataGridView1.RowCount;i++)
{
index = 0;
row = sheet.CreateRow(i + 1);
foreach (DataGridViewColumn item in dataGridView1.Columns)
{
if (item.Visible)
{
NPOI.SS.UserModel.ICell cell = row.CreateCell(index);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(dataGridView1.Rows[i].Cells.ToString());
index++;
}
}

}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
workbook = null;

using (FileStream fs = new FileStream(save.FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}

ms.Close();
ms.Dispose();

}
}
}