2017nopi计算机编程,C# vs2017 winForm 用NOPI导入Excel文件到datagridview(示例代码)

usingSystem;usingSystem.Windows.Forms;usingNPOI.SS.UserModel;usingNPOI.XSSF.UserModel;usingNPOI.HSSF.UserModel;usingSystem.IO;namespaceInputExcelTest

{public partial classForm_SelectFile : Form

{publicForm_SelectFile()

{

InitializeComponent();

}private void BtnSelectFile_Click(objectsender, EventArgs e)

{//选择文件

openFileDialog1.Filter= "XLS文件|*.xls|XLSX文件|*.xlsx";//筛选文件类型

openFileDialog1.FileName = "";if (openFileDialog1.ShowDialog() ==DialogResult.OK)

{

InputWorkbook(openFileDialog1.FileName);//执行导入

}

openFileDialog1.Dispose();

}private void BtnCancel_Click(objectsender, EventArgs e)

{

Close();

}//导入工作簿

private void InputWorkbook(stringfilePath)

{

if (filePath != "")

{try{string fileType = filePath.Substring(filePath.LastIndexOf(".") + 1);//取得文件后缀

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);//创建文件流

bool isXls = true;//判断文件类型

if (fileType == "xlsx")

{

isXls= false;

}

IWorkbook workbook= CreateWorkbook(isXls, fs);//创建工作簿

ISheet sheet = workbook.GetSheetAt(0);//取得第一个工作表

int rowCount = sheet.LastRowNum + 1;//取得行数

int colCount = sheet.GetRow(0).LastCellNum;//取得列数//初始化datagridview1

dataGridView1.Rows.Clear();

dataGridView1.Columns.Clear();for (int c = 0; c < colCount; c++)//遍历Excel第一行，生成dataGridView1列名

{

ICell cell= sheet.GetRow(0).GetCell(c);

dataGridView1.Columns.Add(c.ToString()+cell.ToString(), cell.ToString());

}for (int r = 1; r < rowCount; r++)//遍历Excel其他行,生成dataGridView1单元格内容

{//遍历Excel行,从第二行开始

IRow row =sheet.GetRow(r);int index =dataGridView1.Rows.Add();

colCount=row.LastCellNum;for (int c = 0; c < colCount; c++)

{//遍历每个单元格,将单元格内容填入dataGridView1单元格中

ICell cell =row.GetCell(c);if (cell == null)//如果该单元格没有内容，跳过

{continue;

}

dataGridView1.Rows[index].Cells[c].Value=cell.ToString();

}

}

}catch(Exception ex)

{

MessageBox.Show("导入失败:" +ex.Message);

}

}else{

MessageBox.Show("请选择Excel文件");

}

}//创建工作簿

private static IWorkbook CreateWorkbook(boolisXLS, FileStream fs)

{if(isXLS)

{return newHSSFWorkbook(fs);

}else{return newXSSFWorkbook(fs);

}

}

}

}