目前已实现的功能：
1、可以指定Excel文件的读取位置；
2、读取Excel中各sheet名称；
3、可以预览各sheet的数据。
Todo：
1、如何将datatable另存为DBF文件；
2、另存DBF时如何指定各字段的类型和长度。
目前界面：
目前代码：
using System;using System.Collections.Generic;using ponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using NPOI;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;using NPOI.XSSF.UserModel;using System.IO;using SocialExplorer.IO.FastDBF;namespace demo{public partial class Form1 : Form{public Form1(){InitializeComponent();}private void Form1_Load(object sender, EventArgs e){//============修正预览界面=======开始================dataGridView1.AllowUserToAddRows = false;//禁止添加行dataGridView1.AllowUserToDeleteRows = false;//禁止删除行 //设置SelectionMode属性为FullRowSelect使控件能够整行选择dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//============修正预览界面=======结束================}private void button1_Click(object sender, EventArgs e){//============选择Excel文件=======开始================ string filepath = string.Empty;OpenFileDialog ofd = new OpenFileDialog();ofd.Multiselect = false;//设置是否可以多选ofd.Title = "请选择要处理的文件";//窗口titleofd.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xlsm)|*.xlsm|Excel(*.*)|*.*";//筛选excel文件if (ofd.ShowDialog() == DialogResult.OK){filepath = ofd.FileName;}textBox1.Text = filepath;//============选择Excel文件=======结束================ //============选择Excel文件的sheet=======开始================ comboBox1.Text = "请选择sheet";//打开一个文档string path = textBox1.Text;XSSFWorkbook workbook;using (FileStream stream = File.OpenRead(path)){workbook = new XSSFWorkbook(stream);}int SheetCount = workbook.NumberOfSheets;//获取表的数量string[] SheetName = new string[SheetCount];//保存表的名称for (int i = 0; i < SheetCount; i++){SheetName[i] = workbook.GetSheetName(i);}foreach (string SheetNameList in SheetName)//测试读取状态comboBox1.Items.Add(SheetNameList);//============选择Excel文件的sheet=======结束================ }private void comboBox1_SelectedIndexChanged(object sender, EventArgs e){//============读取Excel=======开始================ //实例化DataTable来存放数据DataTable dt = new DataTable();string fileName = @textBox1.Text;string sheetName = comboBox1.Text;//Excel的工作表名称bool isColumnName = true;//判断第一行是否为标题列IWorkbook workbook;//创建一个工作薄接口string fileExt = Path.GetExtension(fileName).ToLower();//获取文件的拓展名//创建一个文件流using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)){if (fileExt == ".xlsx"){workbook = new XSSFWorkbook(fs);}else{workbook = null;}//读取选择的sheetISheet sheet = null;int SheetCount = workbook.GetSheetIndex(comboBox1.Text);//从表名获得sheet编号sheet = workbook.GetSheetAt(SheetCount);//当前sheet号//获取表头IRow header = sheet.GetRow(sheet.FirstRowNum);int startRow = 0;//数据的第一行索引if (isColumnName)//表示第一行是列名{startRow = sheet.FirstRowNum + 1;//数据从第二行开始读//遍历表的第一行，即所有的列名for (int i = header.FirstCellNum; i < header.LastCellNum; i++){ICell cell = header.GetCell(i);if (cell != null){//获取列名的值string cellValue = cell.ToString();if (cellValue != null){DataColumn col = new DataColumn(cellValue);dt.Columns.Add(col);}else{DataColumn col = new DataColumn();dt.Columns.Add(col);}}}}//读取数据for (int i = startRow; i <= sheet.LastRowNum; i++){IRow row = sheet.GetRow(i);if (row == null){continue;}DataRow dr = dt.NewRow();for (int j = row.FirstCellNum; j < row.LastCellNum; j++){if (row.GetCell(j) != null){dr[j] = row.GetCell(j).ToString();}}dt.Rows.Add(dr);}}dataGridView1.DataSource = dt;//============读取Excel=======结束================ //============修正预览界面=======开始================//禁用DataGridView控件的排序功能for (int zz = 0; zz < dataGridView1.Columns.Count; zz++)dataGridView1.Columns[zz].SortMode = DataGridViewColumnSortMode.NotSortable;//设置DataGridView控件中的数据以各行换色的形式显示foreach (DataGridViewRow dgvRow in dataGridView1.Rows)//遍历所有行{if (dgvRow.Index % 2 == 0)//判断是否是偶数行{//设置偶数行颜色dataGridView1.Rows[dgvRow.Index].DefaultCellStyle.BackColor = Color.LightSkyBlue;}else//奇数行{//设置奇数行颜色//dataGridView1.Rows[dgvRow.Index].DefaultCellStyle.BackColor = Color.LightBlue;}}dataGridView1.ReadOnly = true;//设置dataGridView1控件的ReadOnly属性，使其为只读//设置dataGridView1控件的DefaultCellStyle.SelectionBackColor属性，使选中行颜色变色dataGridView1.DefaultCellStyle.SelectionBackColor = Color.LightSkyBlue;//============修正预览界面=======结束================}private void button2_Click(object sender, EventArgs e)//根据当前datatable生成dbf{//============打开保存文件对话框=======开始================SaveFileDialog sfd = new SaveFileDialog();sfd.Filter = "DBF(*.dbf)|*.dbf"; //保存的文件扩展名sfd.Title = "保存DBF文件";//对话框标题sfd.DefaultExt = "DBF(*.dbf)|*.dbf";//设置文件默认扩展名 //sfd.InitialDirectory = @"C:\Users\Administrator\Desktop";//设置保存的初始目录if (sfd.ShowDialog() == DialogResult.OK){//用户点击"保存"后执行的代码}//string str = sfd.FileName;//============打开保存文件对话框=======结束================}}}
