c#中如何读取wps中的excel表格数据

[阿斯蒂SSSS]

c#中如何将wps中的excel表格数据读取出来存入dataset中，程序读取office excel表格数据没有问题，读取WPS的excel表格数据就无法读取到！！新手求各位大神指教！！！

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;
using System.IO;
using System.Data.OleDb;

namespace 简单对号
{
    public partial class checkNum : Form
    {
        string strBox = "";//存储值，显示到richtextbox文本框中
        int i = 0;  //统计扫描多少条数据
        public checkNum()
        {
            InitializeComponent();
        }

        static DataSet getExcelDataResult = new DataSet();//设置一个变量存储表格getData()的返回值
       // static DataTable myTable=new DataTable();
        DataView myDataView = new System.Data.DataView();
       
        //获取datatable数据的透视表
        private DataView getDataViewResult(DataTable dt,string strRowFilter)
        {
            DataView myDataView = null;
            try
            {
                //dt = getExcelDataResult.Tables[0];

                myDataView = new System.Data.DataView(dt); //dataview数据透视表筛选datatable中的数据，效率提高很多，比用循环遍历快，15000条数据循环遍历3秒，dataview零点几秒
                myDataView.RowFilter = strRowFilter;//dataview中的  RowFilter属性：后跟一个运算符和值要作为筛选依据的列的名称。 值必须在引号中。 例如:"LastName = Smith"
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("错误信息：" + ex.Message, "出现错误");
            }
            return myDataView;
        }

        private void imei1KeyPress(object sender, KeyPressEventArgs e)
        {

                myDataView = null;

                resultBox.Text = "";
                //背景色还原初始状态
                resultBox.BackColor = SystemColors.Control;
                //获得属性中设置的字体，大小，风格
                //判断是否回车按下,e为控件传入的一个对象，里面用户存有按下的键的char值
                //通过Convert.ToChar(System.Windows.Forms.Keys.Enter)我们可以得到Enter键的char值
                //.Enter可替换为其他按键
                //imei1txt.Focus();
                if ((imei1txt.Text.Length != 0) && (e.KeyChar == Convert.ToChar(System.Windows.Forms.Keys.Enter)))
                {
                    try
                    {
                        string strRowFilter = "F1='" + imei1txt.Text + "'"; //dataview数据透视表查询条件
                        myDataView = getDataViewResult(getExcelDataResult.Tables[0], strRowFilter);
                        if (myDataView[0]["F1"].ToString() != "")
                        {
                            dataGridView1.DataSource = myDataView;
                            //entenr键按下，聚焦imei2文本框,同时选中imei2文本框
                            imei2txt.Focus();
                            imei2txt.SelectAll();
                        }                        
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("错误信息：imei："+imei1txt.Text+"不存在数据源中！！！" , "错误信息");
                        imei1txt.SelectAll();
                    }
                }
        }

        private void imei2KeyPress(object sender, KeyPressEventArgs e)
        {
            try
            {
                // 
                if ((imei1txt.Text.Length != 0)&&(imei2txt.Text.Length != 0) && (e.KeyChar == Convert.ToChar(System.Windows.Forms.Keys.Enter)))
                {
                    //获取dataview中的数据
                    //写法一:写法清晰
                    //string dvImei = dv.Table.Rows[0]["imei"].ToString();

                    //写法二：写法短，效率会相应提高
                    string dvImei = myDataView[0][0].ToString();
                    string dvSn = myDataView[0][1].ToString();

                    //测试使用：判断是否能读取到imei和sn
                    //textBox1.Text = dvImei + "\t" + dvSn;
                    if (dvSn == imei2txt.Text.ToString())
                    {
                        resultBox.Text = "PASS";
                        //设置背景色
                        resultBox.BackColor = System.Drawing.Color.Chartreuse;
                        //获得属性中设置的字体，大小，风格
                        resultBox.Font = new Font(resultBox.Font.Name, resultBox.Font.Size, resultBox.Font.Style);
                        //将值写入本地文本文档
                        string str = imei1txt.Text + "\t" + imei2txt.Text + "\r\n";
                        File.AppendAllText("result.txt", str);
                        strBox += str;
                        showBox.Text = strBox;  //追加文本框内容
                        i++;
                        totalLab.Text = "总计" + i + "行";
                    }
                    else
                    {
                        resultBox.Text = "FAIL";
                        //设置背景色
                        resultBox.BackColor = System.Drawing.Color.Red;
                        //获得属性中设置的字体，大小，风格
                        resultBox.Font = new Font(resultBox.Font.Name, resultBox.Font.Size, resultBox.Font.Style);
                    }
                }
                else
                {
                    //当enter键按下后才显示判断结果
                    if (e.KeyChar == Convert.ToChar(System.Windows.Forms.Keys.Enter))
                    {
                        resultBox.Text = "FAIL";
                        //设置背景色
                        resultBox.BackColor = System.Drawing.Color.Red;
                        //获得属性中设置的字体，大小，风格
                        resultBox.Font = new Font(resultBox.Font.Name, resultBox.Font.Size, resultBox.Font.Style);
                    }
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message, "错误信息");
                resultBox.Text = "FAIL";
                //设置背景色
                resultBox.BackColor = System.Drawing.Color.Red;
                //获得属性中设置的字体，大小，风格
                resultBox.Font = new Font(resultBox.Font.Name, resultBox.Font.Size, resultBox.Font.Style);
            }
           
          
            
            if (e.KeyChar == Convert.ToChar(System.Windows.Forms.Keys.Enter))
            {
                //entenr键按下，聚焦imei1文本框,同时选中imei1文本框
                imei1txt.Text = "";
                imei2txt.Text = "";
                imei1txt.Focus();
                imei1txt.SelectAll();
            }
        }

        //窗体加载事件
        private void checkNum_Load(object sender, EventArgs e)
        {
            dataGridView1.ClearSelection();//窗体加载前先清除所有内容
            string filePath = Application.StartupPath + "\\data\\data.xls";//文件路径
           // string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";//excel表格连接字符串
            //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此連接只能操作Excel2007之前(.xls)文件
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此連接可以操作.xls與.xlsx文件
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();//打开连接
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
                DataTable dtExcName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });


                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtExcName.Rows.Count];
                for (int i = 0; i < dtExcName.Rows.Count; i++)
                {
                    strTableNames[i] = dtExcName.Rows[i]["TABLE_NAME"].ToString();
                }
                string strSql = "select * from [" + strTableNames[0] + "]";//表名的写法也应注意不同，对应的excel表为sheet1，在这里要在其后加美元符号￥，并用中括号
                OleDbCommand cmd = new OleDbCommand(strSql, conn);//建立执行命令
                OleDbDataAdapter oda = new OleDbDataAdapter(cmd);//建立数据适配器


                oda.Fill(getExcelDataResult, "myTable");//把数据适配器中的数据读到数据集汇总的一个表中（此处表名为shyman，可以任取表名）
                //指定datagridview1的数据源为数据集ds的第一张表（也就是shyman表），也可以写ds.Table["shyman"]
                dataGridView1.DataSource = getExcelDataResult.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show("错误信息：未找到数据源或excel表格格式不正确（excel需2003格式）！！！");
            }
            finally
            {
                conn.Close();
            }        
        }       
    }
}

[fighting_1982]

1. 首先将wps的相关COM组件添加至引用。project -> add reference -> com-> Kingsoft ET 2.0 Object Library.

2. 代码中添加using KSO; using ET;

3. 打开xls文件的相关代码：

ET.Application etApp;
ET.workbook etbook;
ET.Worksheet etsheet ;
ET.Range etrange;
//获取工作表表格
etApp = new ET.Application();
etbook = (ET.workbook)etApp.Workbooks.Open(@"c:\file.xls");

//获取数据区域
etsheet = (ET.Worksheet)etbook.Worksheets.get_Item(1);

//获取数据区域
etrange = (ET.Range)etsheet.UsedRange;

4. 读取某单元格的数据内容：

string strData = ((ET.Range)etrange.get_Item(i, j)).Text;

5. 写入某单元格的数据内容：

((ET.Range)etrange.get_Item(i, j)).Value = strData;

6. 关闭文件及相关资源：

etbook.Close();
etApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(etrange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etApp);



以下代码是可以运行的

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using KSO;
using ET;

namespace TestWPS
{
public partial class Form1 : Form
{
ET.Application objApp = null;
ET._Workbook objWorkBook = null;
ET.Worksheet objWorkSheet = null;
public Form1()
{
InitializeComponent();
}
private void exportWPS_excel(DataTable dt)
{
object missing = Type.Missing;
ET.Range objRange = null;

try
{
objApp = new ET.Application();

objWorkBook = objApp.Workbooks.Add(Type.Missing);
objWorkSheet = objWorkBook.ActiveSheet;

//设置标题
objWorkSheet.get_Range("A1", "G1").Merge(true); //先进行单元合并
objRange = objWorkSheet.get_Range("A1", "A1");
objRange.Value2 = "表 格 示 例"; //设置合并后的单元格的文本
objRange.RowHeight = 40; //设置行高
//设置字体
objRange.Font.Name = "宋体";
objRange.Font.Size = 20;
objRange.Font.Bold = true;
objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter; //设置字体对其方向

objRange = objWorkSheet.get_Range("A2", "G7");
objRange.Borders.LineStyle = ET.ETLineStyle.etContinuous;
objRange.Borders.Weight = ET.ETBorderWeight.etThin;
objRange.RowHeight = 17;
objRange.Borders.Color = 0; //设置边框的颜色，颜色的值可以按照B,G,R的方式合成
//设置表格的外边框，加粗
objRange.Borders[ET.ETBorderIndex.etEdgeBottom].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeLeft].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeRight].Weight = ET.ETBorderWeight.etMedium;
objRange.Borders[ET.ETBorderIndex.etEdgeTop].Weight = ET.ETBorderWeight.etMedium;
objRange.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
objRange.Font.Name = "宋体";
objRange.Font.Size = 10;

string name = dt.Rows[0][0].ToString();

objWorkSheet.get_Range("A2", "A2").Value2 = "姓 名";
objWorkSheet.get_Range("A2", "A2").ColumnWidth = 60;

objWorkSheet.get_Range("B2", "B2").ColumnWidth = 75;
objWorkSheet.get_Range("B2", "B2").Value2 = name;


objWorkSheet.get_Range("C2", "C2").Value2 = "性 别";
objWorkSheet.get_Range("C2", "C2").ColumnWidth = 60;

objWorkSheet.get_Range("D2", "D2").ColumnWidth = 75;
objWorkSheet.get_Range("D2", "D2").Value2 = dt.Rows[0][1].ToString();

objWorkSheet.get_Range("E2", "E2").Value2 = "出生年月";
objWorkSheet.get_Range("E2", "E2").ColumnWidth = 60;

objWorkSheet.get_Range("F2", "F2").ColumnWidth = 75;
objWorkSheet.get_Range("F2", "F2").Value2 = dt.Rows[0][2].ToString();

objWorkSheet.get_Range("G2", "G7").Merge(false);
objWorkSheet.get_Range("G2", "G2").ColumnWidth = 80;
objWorkSheet.get_Range("G2", "G2").Value2 = "照片";

objWorkSheet.get_Range("A3", "A3").Value2 = "民 族";
objWorkSheet.get_Range("B3", "B3").Value2 = dt.Rows[0][3].ToString();

objWorkSheet.get_Range("C3", "C3").Value2 = "政治面貌";
objWorkSheet.get_Range("D3", "D3").Value2 = dt.Rows[0][4].ToString();

objWorkSheet.get_Range("E3", "E3").Value2 = "籍 贯";
objWorkSheet.get_Range("F3", "F3").Value2 = dt.Rows[0][5].ToString();

objWorkSheet.get_Range("A4", "A4").Value2 = "学 历";
objWorkSheet.get_Range("B4", "B4").Value2 = dt.Rows[0][6].ToString();

objWorkSheet.get_Range("C4", "C4").Value2 = "固定电话";
objWorkSheet.get_Range("D4", "D4").Value2 = dt.Rows[0][7].ToString();

objWorkSheet.get_Range("E4", "E4").Value2 = "移动电话";
objWorkSheet.get_Range("F4", "F4").Value2 = dt.Rows[0][8].ToString();

objWorkSheet.get_Range("A5", "A5").Value2 = "毕业院校";
objWorkSheet.get_Range("B5", "F5").Merge(false);
objWorkSheet.get_Range("B5", "B5").Value2 = dt.Rows[0][9].ToString();

objWorkSheet.get_Range("A6", "A6").Value2 = "家庭住址";
objWorkSheet.get_Range("B6", "F6").Merge(false);
objWorkSheet.get_Range("B6", "B6").Value2 = dt.Rows[0][10].ToString();

objWorkSheet.get_Range("A7", "A7").Value2 = "Email";
objWorkSheet.get_Range("B7", "F7").Merge(false);
objWorkSheet.get_Range("B7", "B7").Value2 = dt.Rows[0][11].ToString();

objWorkBook.SaveAs(string.Format("D:\\{0}.xls", name), missing, missing, missing, missing, missing, ET.ETSaveAsAccessMode.etExclusive, missing, missing, missing, missing);
objWorkBook.Close(missing, missing, missing);
}
finally
{
ReleaseComObject(objRange);
ReleaseComObject(objWorkSheet);
ReleaseComObject(objWorkBook);
}
}

private void ReleaseComObject(object obj)
{
if (obj != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
}

/// <summary>
/// 将DataTable的数据导出显示为报表(使用WPS)
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public string OutputWPSExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
DateTime beforeTime = DateTime.Now;
object missing = Type.Missing;
ET.Range objRange = null;
string filename = "";
try
{
objApp = new ET.Application();

objWorkBook = objApp.Workbooks.Add(Type.Missing);
objWorkSheet = objWorkBook.ActiveSheet;
int rowIndex = 4;
int colIndex = 1;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
//excel.Cells[4, colIndex]
objWorkSheet.Cells[4,colIndex] = col.ColumnName;
//设置标题格式为居中对齐

Range range = objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(4, colIndex),(object) objWorkSheet.Cells.get_Item(4, colIndex));
range.Font.Bold = true;
range.HorizontalAlignment = ET.ETHAlign.etHAlignCenter;
range.Select();
//objWorkSheet.get_Range(objWorkSheet.Cells[4, colIndex], objWorkSheet.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种

}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
objWorkSheet.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowIndex, colIndex), (object)objWorkSheet.Cells.get_Item(rowIndex, colIndex)).HorizontalAlignment = ET.ETHAlign.etHAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
Range txtRange = (Range)objWorkSheet.Cells[rowIndex, colIndex];
txtRange.NumberFormatLocal = "@";
objWorkSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
objWorkSheet.get_Range((object)objWorkSheet.Cells.get_Item(rowIndex, colIndex),(object) objWorkSheet.Cells.get_Item(rowIndex, colIndex)).HorizontalAlignment = ET.ETHAlign.etHAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{

[吉普赛的歌]

用 NPOI 不行么？

[threenewbee]

引用wps com库，但是只有企业版支持

[阿斯蒂SSSS]

你好，如果我电脑安装了企业版的WPS，程序放到安装WPS个人版的电脑上能读取到数据么？

[阿斯蒂SSSS]

引用 3 楼 caozhy 的回复:

引用wps com库，但是只有企业版支持

你好，如果我电脑安装了企业版的WPS，程序放到安装WPS个人版的电脑上能读取到数据么？

[阿斯蒂SSSS]

问题已解决，谢谢各位大神的指点：是读取表格数据的连接字符串出了问题。 将string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; 修改为 string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'";即可，这样不管是office还是WPS的表格都能够读取到，不过我读取的是2003版本的，没有去读取2007版本

[hannniya]

如果是vs2019要怎么操作呢

[微小的鱼]

没有Kingsoft ET 2.0 Object Library.只有