//nuget导入npoi我就不说了。。。
private void button1_Click(object sender, EventArgs e)
{
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//交互 ->不需要拖入控件
string strFileName = "";
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"c:\"; //注意这里写路径时要用c而不是c:
openFileDialog.Filter = "Excel files(*.xlsx)|*.xlsx";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
strFileName = openFileDialog.FileName; //路径+文件(含扩展名)
//strName = openFileDialog.SafeFileName; //文件(含扩展名)
}
else { return; }
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//设置dataGridView
//基本设置
dataGridView1.Columns.Clear();
dataGridView1.DataSource = null;
dataGridView1.ColumnCount = 3; //dataGridView1.Rows.Count = 10;只读
dataGridView1.ColumnHeadersVisible = true;
//列头行高
dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing; //列头行高
dataGridView1.ColumnHeadersHeight = 22; //列头行高
dataGridView1.RowTemplate.Height = 22; //数据格子行高
//列头文字，字体，颜色，居中
dataGridView1.EnableHeadersVisualStyles = false;//这样就可以使用当前的主题的样式了，这句话十分关键！
//dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("宋体",12, FontStyle.Bold); //dataGridView1.ColumnHeadersDefaultCellStyle.ForeColor = Color.Purple;
DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle(); // Set the column header style.
columnHeaderStyle.BackColor = Color.Beige;
columnHeaderStyle.ForeColor = Color.Purple;
columnHeaderStyle.Font = new Font("宋体", 9); //, FontStyle..Bold);
columnHeaderStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; //, FontStyle..Bold); 文字居中显示
dataGridView1.ColumnHeadersDefaultCellStyle = columnHeaderStyle;
//固定列
//dataGridView1.Columns[2].Frozen = true;
dataGridView1.AllowUserToAddRows = false; //禁止在末尾追加新行
dataGridView1.RowHeadersVisible = false; //行头可见
dataGridView1.Columns[0].SortMode = DataGridViewColumnSortMode.NotSortable; //禁止排序:NotSortable/排序:Automatic
//dataGridView1.AllowUserToResizeColumns = false; // 禁止用户改变DataGridView1的所有列的列宽
dataGridView1.AllowUserToResizeRows = false;//禁止用户改变DataGridView1の所有行的行高 ->但是可以通过 DataGridViewColumn.Width 或者 DataGridViewRow.Height 属性设定列宽和行高。
//只读(编辑)+光条显示
dataGridView1.EditMode = DataGridViewEditMode.EditProgrammatically; //.EditOnEnter; //设定为编辑模式
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.MultiSelect = false;
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//列名设置
dataGridView1.Columns[0].Name = "班级";
dataGridView1.Columns[0].Width = 35;
dataGridView1.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; //控制对齐
dataGridView1.Columns[1].Name = "姓名";
dataGridView1.Columns[1].Width = 95;
dataGridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
//dataGridView1.Columns[2].Name = "车号";
//dataGridView1.Columns[2].Width = 105;
//dataGridView1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//读入Excel
try
{
//打开文件
FileStream fileStream = new FileStream(strFileName, FileMode.Open);
IWorkbook workbook = null; //新建IWorkbook对象
if (strFileName.IndexOf(".xlsx") > 0) // 2007版本
{ workbook = new XSSFWorkbook(fileStream); } //xlsx数据读入workbook }
else if (strFileName.IndexOf(".xls") > 0) // 2003版本
{ workbook = new HSSFWorkbook(fileStream); } //xls数据读入workbook
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
IRow row;// = sheet.GetRow(0); //新建当前工作表行数据
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++
//取数据
dataGridView1.Rows.Clear();
string strStuGrade, strStuName, strStuTarget;
for (int i = 0; i < sheet.LastRowNum; i++) //遍历所有的行(从0开始)
{
strStuGrade = ""; strStuName = ""; strStuTarget = "";
if (sheet.GetRow(i + 1).GetCell(0) != null) { strStuGrade = sheet.GetRow(i + 1).GetCell(0).ToString(); }
if (sheet.GetRow(i + 1).GetCell(1) != null) { strStuName = sheet.GetRow(i + 1).GetCell(1).ToString(); }
if (sheet.GetRow(i + 1).GetCell(2) != null)
{
strStuTarget = sheet.GetRow(i + 1).GetCell(2).ToString();
if (strStuTarget.Contains("\n"))
{
strStuTarget = strStuTarget.Replace("\n", "");
}
}
//if (i>67) { MessageBox.Show("1111"); }
if (strStuGrade == "")
{
break;
}
dataGridView1.Rows.Add();
dataGridView1.Rows[i].Cells[0].Value = strStuGrade;
dataGridView1.Rows[i].Cells[1].Value = strStuName;
dataGridView1.Rows[i].Cells[2].Value = strStuTarget;
} //for i
sheet = null;
workbook = null;
fileStream.Close();
fileStream.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}