#region NPOI导入
DialogResult dr = openFileDialog1.ShowDialog();//对话框
if (dr == DialogResult.OK)
{
using (FileStream fs = File.OpenRead(openFileDialog1.FileName))
{
HSSFWorkbook wb = new HSSFWorkbook(fs);
//获取工作薄
ISheet Isheet = wb.GetSheetAt(0);
DataTable dt = new DataTable();
//循环输出表格中的值
for (int i = 0; i <= Isheet.LastRowNum; i++)
{
//读取当前行数据
IRow Irow = Isheet.GetRow(i);
if (Irow != null)
{
for (int j = 0; j <= Irow.LastCellNum; j++)
{
//获取表格
ICell icl = Irow.GetCell(j);
if (icl != null)
{
//Console.Write(icl.ToString() + " ");
if (i == 0)
{
dt.Columns.Add(icl.ToString());
}
else
{
DataRow drow = dt.NewRow();
dt.Rows.Add(drow);
dt.Rows[i-1][j] = icl.ToString();
}
}
}
}
//Console.WriteLine();
}
dataGridView1.DataSource = dt;
}
}
#endregion
#region NPOI导出
DialogResult dr = saveFileDialog1.ShowDialog();//对话框
if (dr == DialogResult.OK)
{
string sql = "select *from student";
DataTable dt = GetTable.get(sql);
//创建excel
HSSFWorkbook wb = new HSSFWorkbook();
//创建sheet
ISheet Isheet = wb.CreateSheet("sheet1");
//创建第一行
IRow Irow = Isheet.CreateRow(0);
//创建第一行的单元格
ICell Icl0 = Irow.CreateCell(0);
ICell Icl1 = Irow.CreateCell(1);
ICell Icl2 = Irow.CreateCell(2);
//给第一行的单元格添加标题
Icl0.SetCellValue("Id");
Icl1.SetCellValue("姓名");
Icl2.SetCellValue("年龄");
//循环把datatable的数据给表格
for (int i = 1; i <= dt.Rows.Count; i++)
{
DataRow row=dt.Rows[i-1];
//创建表格的新行
IRow Irow_i = Isheet.CreateRow(i);
//创建该行的单元格
ICell icel0 = Irow_i.CreateCell(0);
icel0.SetCellValue(row[0].ToString());
ICell icel1 = Irow_i.CreateCell(1);
icel1.SetCellValue(row[1].ToString());
ICell icel2 = Irow_i.CreateCell(2);
icel2.SetCellValue(row[2].ToString());
}
//打开或者创建xlsx文件
using (FileStream fs = File.OpenWrite(saveFileDialog1.FileName + ".xlsx"))
{
//向打开的文件写入数据并保存
wb.Write(fs);
MessageBox.Show("导出成功");
}
}
#endregion
Demo下载地址:https://download.csdn.net/download/a2242880246/11041266