學習使用winform實現(xiàn)excel表格的導入導出功能，制作一個小demo。
先畫一個簡單的界面：表名comboBox中的值為數(shù)據(jù)庫中的表名[‘student’,‘teacher’,‘class’,‘school’]，
針對選中的表名進行查詢，上傳和下載。查詢到的數(shù)據(jù)在dataGridView控件中顯示。

Form1.cs 代碼：

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.Data.OleDb;namespace ExcelControlWindowsFormsApp {public partial class Form1 : Form{public Form1(){InitializeComponent();}private void import_button_Click(object sender, EventArgs e){//設(shè)置對話框的初始目錄為C盤this.openFileDialog1.InitialDirectory = "C:\\";//篩選字符串為所有文件this.openFileDialog1.Filter = "所有文件(*.*)|*.*";this.openFileDialog1.RestoreDirectory = true;if (this.openFileDialog1.ShowDialog() == DialogResult.OK){string PicFileName = this.openFileDialog1.FileName.Trim();bool resule = Import(PicFileName);if (resule){MessageBox.Show("上傳成功！");}}}/// <summary>/// 導入Excel文件/// </summary>/// <param name="filePath"></param>/// <returns></returns>private bool Import(string filePath){OleDbConnection con = null;try{string strFileType = System.IO.Path.GetExtension(filePath);string strConn = "";//Excel就好比一個數(shù)據(jù)源一般使用if (strFileType == ".xls"){ // Excel 2003 版本連接字符串strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";}else { // Excel 2007 以上版本連接字符串//HDR：Yes 表示第一行包含列名,在計算行數(shù)時就不包含第一行。NO 則完全相反。//IMEX：0 寫入模式；1 讀取模式；2 讀寫模式。如果報錯為“不能修改表 sheet1 的設(shè)計。它在只讀數(shù)據(jù)庫中”，那就去掉這個，問題解決。strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";}con = new OleDbConnection(strConn);con.Open();string[] names = GetExcelSheetNames(filePath);if (names.Length > 0){foreach (string name in names){using (OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(" select * from [{0}$]", name), con)) {System.Data.DataTable dt = new System.Data.DataTable();adapter.Fill(dt);//將數(shù)據(jù)入庫insertExcelData(dt);}}}return true;}catch (Exception ex){MessageBox.Show(ex.Message);return false;}finally{con.Close();}}/// <summary>/// 獲取excel文件中的sheet名/// </summary>/// <param name="filePath"></param>/// <returns></returns>private string[] GetExcelSheetNames(string filePath){Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbooks wbs = excelApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing);int count = wb.Worksheets.Count;string[] names = new string[count];for (int i = 1; i <= count; i++){names[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i]).Name;}return names;}/// <summary>/// 將數(shù)據(jù)入庫/// </summary>/// <param name="dataTable"></param>private void insertExcelData(DataTable dataTable) {string tableName = this.tableName_comboBox.Text.Trim();string columnName = "";int i = 0;int count = dataTable.Columns.Count;foreach (DataColumn col in dataTable.Columns) {if (i == count - 1) {columnName += "[" + col.ColumnName.Trim() + "]";}else{columnName += "[" + col.ColumnName.Trim() + "],";}i++;}foreach (DataRow row in dataTable.Rows) {string valueData = "";for (int j = 0; j < count; j++){if (j == count - 1){valueData += "'" + row[j].ToString().Trim() + "'";}else{valueData += "'" + row[j].ToString().Trim() + "',";}}string sql = "INSERT INTO [dbo].["+ tableName + "] ("+ columnName + ") VALUES ("+ valueData + ")";SqlHelper sqlHelper = new SqlHelper();sqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);}}/// <summary>/// 查詢數(shù)據(jù)庫中的數(shù)據(jù)/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void select_button_Click(object sender, EventArgs e){DataTable dt = selectData();showDataGridView(dt);}private void export_button_Click(object sender, EventArgs e){string localFilePath = "", fileNameExt = "", FilePath = "";SaveFileDialog saveFileDialog = new SaveFileDialog();//設(shè)置文件類型//書寫規(guī)則例如：txt files(*.txt)|*.txtsaveFileDialog.Filter = "txt files(*.txt)|*.txt|xls files(*.xls)|*.xls|xlsx files(*.xlsx)|*.xlsx|All files(*.*)|*.*";//設(shè)置默認文件名（可以不設(shè)置）saveFileDialog.FileName = this.tableName_comboBox.Text.Trim()+"Data";//主設(shè)置默認文件extension（可以不設(shè)置）saveFileDialog.DefaultExt = "xls";//獲取或設(shè)置一個值，該值指示如果用戶省略擴展名，文件對話框是否自動在文件名中添加擴展名。（可以不設(shè)置）saveFileDialog.AddExtension = true;//設(shè)置默認文件類型顯示順序（可以不設(shè)置）saveFileDialog.FilterIndex = 2;//保存對話框是否記憶上次打開的目錄saveFileDialog.RestoreDirectory = true;// Show save file dialog boxDialogResult result = saveFileDialog.ShowDialog();//點了保存按鈕進入if (result == DialogResult.OK){//獲得文件路徑localFilePath = saveFileDialog.FileName.ToString();//獲取文件名，不帶路徑fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);//獲取文件路徑，不帶文件名FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));//在文件名里加字符//saveFileDialog.FileName.Insert(1,"dameng");//第一種寫入方法---輸入流//為用戶使用 SaveFileDialog 選定的文件名創(chuàng)建讀/寫文件流。//System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog.OpenFile();//輸出文件//fs可以用于其他要寫入的操作//第二種寫入方法---使用Microsoft.Office.Interop.ExcelDataTable dataTable = selectData();bool resule = DataTableToExcel(localFilePath, dataTable, true);if (resule) {MessageBox.Show("下載成功！");}}}/// <summary>/// 導出Excel文件/// </summary>/// /// <param name="dataSet"></param>/// <param name="dataTable">數(shù)據(jù)集</param>/// <param name="isShowExcle">導出后是否打開文件</param>/// <returns></returns>private bool DataTableToExcel(string filePath, DataTable dataTable, bool isShowExcle){//System.Data.DataTable dataTable = dataSet.Tables[0];int rowNumber = dataTable.Rows.Count;int columnNumber = dataTable.Columns.Count;int colIndex = 0;if (rowNumber == 0){return false;}Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];excel.Visible = isShowExcle;Microsoft.Office.Interop.Excel.Range range;foreach (DataColumn col in dataTable.Columns){colIndex++;excel.Cells[1, colIndex] = col.ColumnName;}object[,] objData = new object[rowNumber, columnNumber];for (int r = 0; r < rowNumber; r++){for (int c = 0; c < columnNumber; c++){objData[r, c] = dataTable.Rows[r][c];}}range = worksheet.Range[excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]];range.Value2 = objData;range.NumberFormatLocal = "@";worksheet.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);//excel.Quit();return true;}/// <summary>/// 從數(shù)據(jù)庫中查詢數(shù)據(jù)/// </summary>/// <returns></returns>private DataTable selectData() {DataTable dataTable = new DataTable();SqlHelper sqlHelper = new SqlHelper();string tableName = this.tableName_comboBox.Text.Trim();string sql = "select * from " + tableName;dataTable = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);return dataTable;}private void Form1_Load(object sender, EventArgs e){//初始化 comboBox 數(shù)據(jù)this.tableName_comboBox.Text = "student";//初始化 datagridview 數(shù)據(jù)DataTable dataTable = selectData();showDataGridView(dataTable);}/// <summary>/// 表格數(shù)據(jù)填充/// </summary>/// <param name="dt"></param>private void showDataGridView(DataTable dt) {this.dataGridView1.Columns.Clear();this.dataGridView1.Rows.Clear();this.dataGridView1.Columns.Add("RowState", "rowstate");//添加新列foreach (DataColumn col in dt.Columns){this.dataGridView1.Columns.Add(col.ColumnName, col.ColumnName);}this.dataGridView1.Rows.Clear();this.dataGridView1.Rows.Add(dt.Rows.Count);//增加同等數(shù)量的行數(shù)int i = 0;foreach (DataRow row in dt.Rows)//逐個讀取單元格的內(nèi)容；{DataGridViewRow r1 = this.dataGridView1.Rows[i];r1.Cells[0].Value = row.RowState.ToString();for (int j = 0; j < dt.Columns.Count; j++){r1.Cells[j + 1].Value = row[j].ToString();}i++;}}} }

其中SqlHelper.cs在之前的文章中寫過。文章鏈接：
https://blog.csdn.net/weixin_41561640/article/details/106142100

在調(diào)試過程中出現(xiàn)的問題
1、下載功能：
報錯：檢索 COM 類工廠中 CLSID 為 {00024500-0000-0000-C000-000000000046} 的組件時失敗，原因是出現(xiàn)以下錯誤: 80070005。
解決方法：
控制面板－》管理工具－》組件服務(wù)－》計算機－》我的電腦－》DCom配置－》找到Microsoft Excel 應(yīng)用程序
之后單擊屬性打開此應(yīng)用程序的屬性對話框。 單擊標識選項卡，然后選擇交互式用戶。
2、如果在Dcom配置中找不到Microsoft Excel：
在運行欄中輸入命令：dcomcnfg，打開組件服務(wù)管理窗口，但是卻發(fā)現(xiàn)找不到Microsoft Excel程序，這主要是64位系統(tǒng)的問題，excel是32位的組件，所以在正常的系統(tǒng)組件服務(wù)里是看不到的。
解決方法：
可以通過在運行里面輸入 comexp.msc -32 來打開32位的組件服務(wù)，這里就能看到excel組件了
3、上傳功能：
報錯：System.InvalidOperationException: 未在本地計算機上注冊“microsoft.ACE.oledb.12.0”提供程序。
解決方法：
下載微軟數(shù)據(jù)控件，然后安裝就行了
參考文章：
https://blog.csdn.net/weixin_44668267/article/details/91489903

總結(jié)

以上是生活随笔為你收集整理的C# winform实现excel导入导出 及实现过程中的问题解决的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。