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();
}
}
}
}
uj5u.com熱心網友回復：
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
{
uj5u.com熱心網友回復：
用 NPOI 不行么？uj5u.com熱心網友回復：
參考wps com庫，但是只有企業版支持uj5u.com熱心網友回復：
你好，如果我電腦安裝了企業版的WPS，程式放到安裝WPS個人版的電腦上能讀取到資料么？uj5u.com熱心網友回復：
你好，如果我電腦安裝了企業版的WPS，程式放到安裝WPS個人版的電腦上能讀取到資料么？
uj5u.com熱心網友回復：
問題已解決，謝謝各位大神的指點：是讀取表格資料的連接字串出了問題。將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版本
uj5u.com熱心網友回復：
如果是vs2019要怎么操作呢uj5u.com熱心網友回復：
沒有Kingsoft ET 2.0 Object Library.只有
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/22825.html
標籤：C#
上一篇：winform如何實作如圖效果