C#读取EXCEL表数据
1.本篇文章讲的是如何使用C#读取EXCEL表，在百度了很多博客，文章都是写的使用office的一个插件，还需要下载就整的很麻烦。
c# 读取excel数据的两种方法（转） - YellowCool - 博客园 (cnblogs.com)这个文章写了几个方法，但是这些方法都是比较繁琐的。
2.首先，读取EXCEL表的步骤：
2.1只需要下载插件Spire.XLS。
Spire.XLS是E-iceblue开发的一套基于企业级的专业Office文档处理的组件之一，全称Spire.Office for .NET，这款插件在对于office的处理上提供了很大的方便。
这套插件的下载方式：目前官网下载都要账号下载很麻烦，建议下载他们的demo里面有我们需要的dll文件，地址：Free Customized Demo | E-iceblue，下载如下demo解压，在bin文件中会有dll文件。
以前在vs中有免费的插件下载，现在在vs中也不能下载了，穷人就只能这样办了。[/哭了]
2.2下载好插件后，新建项目，在项目中的引用这个文件就行了。
2.3我写了一个读取表数据的类。
使用workbook方法读取EXCEL：
Workbook workbook = new Workbook();
workbook.LoadFromFile("config\\debug.xlsx");
然后一个EXCEL表中可以存在多个附表，所以需要使用循环去查找附表个数，附表个数有一个方法：workbook.worksheet.Cout();
在循环中使用Worksheet对象就能拿到当前循环附表的数据：
Worksheet worksheet = workbook.Worksheets[i];
int iColCount = worksheet.Columns.Count();
int iRowCount = worksheet.Rows.Count();
//拿到ECXEL表的开始单元格和结束单元格
string sRange = "A1:" + AutoSystemTable.sLetters[iColCount - 1] + iRowCount.ToString();//拿到返回的数据集
DataTable m_Dt = new DataTable();m_Dt = worksheet.ExportDataTable(worksheet[sRange], true, false);
//用datagridview控件接收数据
dataGridView1.DataSource = m_Dt;
读取EXCEL表中的数据就完成了，具体的可以借鉴C#组件系列——又一款Excel处理神器Spire.XLS，你值得拥有 - 懒得安分 - 博客园 (cnblogs.com)有很详细的插件教程。
3.完整代码（完整代码也没办法直接复制粘贴使用，因为我使用了其他的插件，有兴趣的话可以去下载哦）
3.1autoSystemConfig.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;
using System.Data;
using Spire.Xls;namespace ConsoleApp1
{
public class AutoSystemTable
{
public static string[] sValueType = new string[] { "int", "float", "double", "text" };
public static string[] sLetters = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };public string m_sName { get; set; }
public DataTable m_DT { get; set; }
public List<string> m_DtColumnNameList { get; set; }
public List<string> m_DtColumnTypeList { get; set; }public List<bool> m_bButtonList { get; set; }
public List<bool> m_bSlideList { get; set; }public AutoSystemTable()
{
m_sName = "";
m_DT = null;
m_DtColumnNameList = new List<string>();
m_DtColumnTypeList = new List<string>();
m_bButtonList = new List<bool>();
m_bSlideList = new List<bool>();
}
}public class AutoSystemConfig
{
public DataTable mINIODt { get; set; }
public DataTable mOUTIODt { get; set; }
public List<string> mAdvancedParamList { get; set; }
public List<string> mPosSpeedParamList { get; set; }
public List<string> mDelayParamList { get; set; }
public List<AutoSystemTable> mAutoSysTableList { get; set; }public AutoSystemConfig()
{
//
Workbook workbook = new Workbook();
workbook.LoadFromFile("config\\debug.xlsx");mAutoSysTableList = new List<AutoSystemTable>();
for (int i = 0; i < workbook.Worksheets.Count(); i++)
{
AutoSystemTable newTable = new AutoSystemTable();Worksheet worksheet = workbook.Worksheets[i];
newTable.m_sName = worksheet.Name;
int iColCount = worksheet.Columns.Count();
int iRowCount = worksheet.Rows.Count();
//拿到ECXEL表的开始单元格和结束单元格
string sRange = "A1:" + AutoSystemTable.sLetters[iColCount - 1] + iRowCount.ToString();
//System.Diagnostics.Debug.WriteLine(sRange);
newTable.m_DT = worksheet.ExportDataTable(worksheet[sRange], true, false);
System.Diagnostics.Debug.WriteLine(worksheet[sRange]);// 分析每个column name，来识别其名字，数据类型和操作类型
for (int j = 0; j < newTable.m_DT.Columns.Count; j++)
{
string sColumnName = newTable.m_DT.Columns[j].ColumnName;
string[] splitNames = sColumnName.Split('#');
newTable.m_DT.Columns[j].ColumnName = splitNames[0];newTable.m_DtColumnNameList.Add(splitNames[0]);
if (splitNames.Length != 3)
throw new Exception("非法的列名称");switch (splitNames[1])
{
case "i":
case "I":
newTable.m_DtColumnTypeList.Add("int");
break;
case "f":
case "F":
newTable.m_DtColumnTypeList.Add("float");
break;
case "d":
case "D":
newTable.m_DtColumnTypeList.Add("double");
break;
case "t":
case "T":
newTable.m_DtColumnTypeList.Add("text");
break;
default:
throw new Exception("非法的列名称");
}switch (splitNames[2])
{
case "b":
case "B":
newTable.m_bButtonList.Add(true);
newTable.m_bSlideList.Add(false);
break;
case "s":
case "S":
newTable.m_bButtonList.Add(false);
newTable.m_bSlideList.Add(true);
break;
case "i":
case "I":
newTable.m_bButtonList.Add(false);
newTable.m_bSlideList.Add(false);
break;
default:
throw new Exception("非法的列名称");
}}
mAutoSysTableList.Add(newTable);
}
}}
}
3.2 Form1.cs
using DevComponents.DotNetBar.Controls;
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;namespace ConsoleApp1
{
public partial class Form1 : Form
{
private AutoSystemConfig m_SysConfig;
public DataTable m_Dt;
public Form1()
{
InitializeComponent();
m_SysConfig = new AutoSystemConfig();
dataGridView1.AllowUserToAddRows = false;dataGridView1.RowTemplate.Height = 30;
dataGridView1.RowsDefaultCellStyle.Font = new Font("宋体", 11, FontStyle.Regular);
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("宋体", 11, FontStyle.Regular);
//InitializeDGV1();
}
#region 窗体加载事件
private void Form1_Load(object sender, EventArgs e) {
show_data();
InitializeDGV1();
}
#endregion
#region 读取EXCEL表的数据
private void show_data() {
m_Dt = new DataTable();
m_Dt = m_SysConfig.mAutoSysTableList[0].m_DT;
dataGridView1.DataSource = m_Dt;
}
#endregion
#region 初始化事件
private void InitializeDGV1() {
//InitializeSlider();
//InitialzeButton();
InitialzeColumn();
}
//初始化控件
private void InitialzeColumn()
{
m_Dt = m_SysConfig.mAutoSysTableList[0].m_DT;
dataGridView1.DataSource = m_Dt;
int initSlider = -1;
int initButton = -1;for (int i = 0; i < m_SysConfig.mAutoSysTableList[0].m_bButtonList.Count; i++)
{
if (m_SysConfig.mAutoSysTableList[0].m_bButtonList[i] == true)
{
string btnName = m_SysConfig.mAutoSysTableList[0].m_DtColumnNameList[i];
string btnValue = (string)m_Dt.Rows[0][i];
dataGridView1.Columns.Insert(i, AddButton(btnName, btnValue));initButton = i;
}
else if (m_SysConfig.mAutoSysTableList[0].m_bSlideList[i] == true)
{
string SliderName = m_SysConfig.mAutoSysTableList[0].m_DtColumnNameList[i];
string Slidervalue = (string)m_Dt.Rows[0][i];
//System.Diagnostics.Debug.WriteLine(Slidervalue);
string[] sValue = Slidervalue.Split('#');
DataGridViewSliderColumn dsc = AddSlide(SliderName, int.Parse(sValue[0]), int.Parse(sValue[1]));
dataGridView1.Columns.Insert(i, dsc);initSlider = i;
((DataGridViewSliderCell)dataGridView1.Rows[0].Cells[i]).Value = 50;
}
}
if (initSlider != -1)
{
if (initButton != -1)
{
if (initSlider < initButton)
{
m_Dt.Columns.RemoveAt(initButton);
m_Dt.Columns.RemoveAt(initSlider);
}
else
{
m_Dt.Columns.RemoveAt(initSlider);
m_Dt.Columns.RemoveAt(initButton);
}
}
else
{
m_Dt.Columns.RemoveAt(initSlider);
}
}
else
{
m_Dt.Columns.RemoveAt(initButton);
}for (int i = 0; i < dataGridView1.Columns.Count - 1; i++)
{
dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
dataGridView1.Columns[dataGridView1.Columns.Count - 1].Width = 80;
}
#endregion
#region 给滑动条赋值以及增加事件
private DataGridViewSliderColumn AddSlide(string sText, int iMin, int iMax)
{
DataGridViewSliderColumn bsc = new DataGridViewSliderColumn();
bsc.Name = "newSlide";
bsc.HeaderText = sText;
bsc.Maximum = iMax;
bsc.Minimum = iMin;
bsc.MinimumWidth = iMax;
bsc.Step = 1;
bsc.Value = bsc.Maximum / 2;
bsc.Text = bsc.Value.ToString();bsc.Click += Bsc_Click;
bsc.BeforeCellPaint += Slide_BeforeCellPaint;
//bsc.Click += Slide_Click;return bsc;
}private void Bsc_Click(object sender, EventArgs e)
{
;
}#region 滑动条在不同值时，值显示不同颜色
void Slide_BeforeCellPaint(object sender, BeforeCellPaintEventArgs e)
{
try
{
DataGridViewSliderColumn sc = sender as DataGridViewSliderColumn;if (sc != null)
{
sc.Text = Convert.ToString(sc.Value);
sc.TextColor = (sc.Value <= 30) ? Color.Red : sc.Value >= 90 ? Color.Green : Color.Blue;
}
}
catch(Exception ex)
{
;
}
}
#endregion
#endregion
#region 给按钮赋值
private DataGridViewButtonXColumn AddButton(string sHeaderText, string sButtonText)
{
DataGridViewButtonXColumn btn = new DataGridViewButtonXColumn();
btn.Name = "btnModify";
btn.HeaderText = sHeaderText;
btn.DefaultCellStyle.NullValue = sButtonText;btn.Click += Button_ButtonClick;
return btn;
}
//按钮事件
void Button_ButtonClick(object sender, EventArgs e)
{
try
{
DataGridViewButtonXCell cell = dataGridView1.CurrentCell as DataGridViewButtonXCell;
MessageBox.Show("China is the best country in the world!", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
;
}
}private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
DataGridViewSliderCell x = dataGridView1.CurrentCell as DataGridViewSliderCell;
}
catch (Exception ex)
{
;
}
}#endregion
//#region 将EXCEL中的数据转成滑动条显示
//private void InitializeSlider()
//{
// m_Dt = m_SysConfig.mAutoSysTableList[0].m_DT;
// dataGridView1.DataSource = m_Dt;
// string sValue = (string)m_SysConfig.mAutoSysTableList[0].m_DT.Rows[0][7];
// string[] splitNames = sValue.Split('#');
// DataGridViewSliderColumn x = AddSlide("速度", int.Parse(splitNames[0]), int.Parse(splitNames[1]));
// dataGridView1.Columns.Insert(7, x);
// ((DataGridViewSliderCell)dataGridView1.Rows[0].Cells[7]).Value = 50;
// m_Dt.Columns.RemoveAt(7);//}
//#endregion
//void Slide_Click(object sender, EventArgs e)
//{
// DevComponents.DotNetBar.SliderItem sc = sender as DevComponents.DotNetBar.SliderItem;
//}
//#region 将EXCEL中的数据转成按钮显示
//private void InitialzeButton()
//{
// m_Dt = m_SysConfig.mAutoSysTableList[0].m_DT;
// dataGridView1.DataSource = m_Dt;
// string sColumn = m_SysConfig.mAutoSysTableList[0].m_DtColumnNameList[7];
// string sValue = (string)m_SysConfig.mAutoSysTableList[0].m_DT.Rows[0][7];
// DataGridViewButtonXColumn btnClo = AddButton("按键", sValue);
// dataGridView1.Columns.Insert(8, btnClo);
// m_Dt.Columns.RemoveAt(7);//}
//#endregion
}
}
3.3下载地址：