using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Excel
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void Form2_DragEnter(object sender, DragEventArgs e)
{
}
private void Form2_DragDrop(object sender, DragEventArgs e)
{
}
private void InputWorkbook(string filePath)
{
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;//取得列数
//初始化datagridview
dataGridView3.Rows.Clear();
dataGridView3.Columns.Clear();
for (int c = 0; c < colCount; c++)//遍历Excel第一行,生成dataGridView1列名
{
ICell cell = sheet.GetRow(0).GetCell(c);
dataGridView3.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 = dataGridView3.Rows.Add();
colCount = row.LastCellNum;
for (int c = 0; c < colCount; c++)
{//遍历每个单元格,将单元格内容填入dataGridView1单元格中
ICell cell = row.GetCell(c);
if (cell == null)//如果该单元格没有内容,跳过
{
continue;
}
dataGridView3.Rows[index].Cells[c].Value = cell.ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show("导入失败: " + ex.Message);
}
}
else
{
MessageBox.Show("请选择Excel文件");
}
}
//创建工作簿
private static IWorkbook CreateWorkbook(bool isXLS, FileStream fs)
{
if (isXLS)
{
return new HSSFWorkbook(fs);
}
else
{
return new XSSFWorkbook(fs);
}
}
private void Form2_Load(object sender, EventArgs e)
{
}
private void dataGridView3_DragDrop(object sender, DragEventArgs e)
{
string filepath = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();
string extension = System.IO.Path.GetExtension(filepath);//文件后缀名
if (extension == ".xls" || extension == ".xlsx")
{
textBox2.Text = filepath;
}
}
private void dataGridView3_DragEnter(object sender, DragEventArgs e)
{
string filepath = ((System.Array)e.Data.GetData(DataFormats.FileDrop)).GetValue(0).ToString();//文件完整路径
string extension = System.IO.Path.GetExtension(filepath);//文件后缀名
if (e.Data.GetDataPresent(DataFormats.FileDrop))
e.Effect = DragDropEffects.Link;
else e.Effect = DragDropEffects.None;
if (extension == ".xls" || extension == ".xlsx")
{
InputWorkbook(filepath);
}
}
private void dataGridView3_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
NPOI导入excel
最新推荐文章于 2024-06-17 23:31:56 发布