本文章介绍基于VS2010 Winform 的WPS2016二次开发 ET数据读取程序 本程序支持多个Sheet页面
前提：引用WPS安装目录下的etapi.dll
private void button2_Click(object sender, EventArgs e)
{
//初始化表头
List columnTextsList = new List();
string[] strColumnTexts1 = { "企业名称", "企业类型" };
string[] strColumnTexts2 = { "产品编码", "产品名称", "产品类别" };
columnTextsList.Add(strColumnTexts1);
columnTextsList.Add(strColumnTexts2);
OpenFileDialog fileDialog = new OpenFileDialog();
(*.txt)|*.txt|
fileDialog.Filter = "(*.et)|*.et";
fileDialog.FilterIndex = 1;
fileDialog.RestoreDirectory = true;
if (fileDialog.ShowDialog() == DialogResult.OK)
{
List clientList = new List();
ExcelToDataSet(fileDialog.FileName, columnTextsList, new Action(delegate(string msg, DataSet ds)
{
if (ds != null && ds.Tables.Count > 0)
{
dgv1.DataSource = ds.Tables[0];
if (ds.Tables.Count > 1)
{
dgv2.DataSource = ds.Tables[1];
}
}
else
{
MessageBox.Show(msg);
}
}));
}
}
///
/// 读取et表格数据转换成dataSet
///
/// et表格全路径
/// 规定的表头列表(支持多sheet)
/// 回调函数
private void ExcelToDataSet(string fileName, List columnTextsList, System.Action callback)
{
string msg = "";
List headerList = new List();
DataSet dataSet = new DataSet();
Excel.Application appli = new Excel.Application();
try
{
//读取Excel
Excel._Workbook wk = appli.Workbooks.Open(fileName);
//读取sheet
int sheetsCount = wk.Worksheets.Count;//获取sheet数量
if (sheetsCount <= 0)
{
appli.Workbooks.Close();
appli.Quit();
callback(msg, dataSet);
}
if (columnTextsList.Count > sheetsCount)
{
msg = string.Format("导入表格的sheet数量不符合要求！");
appli.Workbooks.Close();
appli.Quit();
callback(msg, dataSet);
return;
}
//遍历每一个sheet页面
for (int k = 1; k <= sheetsCount; k++)
{
//如果sheet页面的数量大于规定的数量 跳出方法
if (k > columnTextsList.Count) break;
DataTable dt = new DataTable();
Excel.Worksheet sheet = wk.Worksheets.get_Item(k);
Excel.Range range = sheet.UsedRange;
int rowCount = range.Rows.Count;//获取行数
int columCount = range.Columns.Count;//获取列数
//设置列头
for (int j = 1; j <= columCount; j++)
{
dt.Columns.Add(((Excel.Range)range.get_Item(1, j)).Text);
}
//验证Excel中的列是否缺失
for (int i = 0; i < columnTextsList[k - 1].Length; i++)
{
if (!dt.Columns.Contains(columnTextsList[k - 1][i]))
{
msg = string.Format("要导入的文件第"{0}"个sheet页面缺少"{1}"列，请使用正确的模板！", k, columnTextsList[k - 1][i].Trim());
appli.Workbooks.Close();
appli.Quit();
callback(msg, dataSet);
return;
}
}
for (int i = 2; i <= rowCount; i++)
{
DataRow datarow = dt.NewRow();
for (int j = 1; j <= columCount; j++)
{
string title = ((Excel.Range)range.get_Item(j)).Text;
datarow[title] = range.get_Item(i, j).Text;
}
dt.Rows.Add(datarow);
}
dataSet.Tables.Add(dt);
}
//关闭相关程序
appli.Workbooks.Close();
appli.Quit();
callback(msg, dataSet);
}
catch (Exception ex)
{
appli.Workbooks.Close();
appli.Quit();
callback(msg, dataSet);
return;
}
}
