C#操作Excel文件(讀取Excel，寫入Excel)

看到論壇里面不斷有人提問關于讀取excel和導入excel的相關問題。閑暇時間將我所知道的對excel的操作加以總結，現在共享大家，希望給大家能夠給大家帶了一定的幫助。
另外我們還要注意一些簡單的問題1.excel文件只能存儲65535行數據，如果你的數據大于65535行，那么就需要將excel分割存放了。2.關于亂碼，這主要是字符設置問題。

1.加載Excel（讀取excel內容）返回值是一個DataSet

??????? //加載Excel
??????? public static DataSet LoadDataFromExcel(string filePath)
??????? {
??????????? try
??????????? {
??????????????? string strConn;
??????????????? strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
??????????????? OleDbConnection OleConn = new OleDbConnection(strConn);
??????????????? OleConn.Open();
??????????????? String sql = "SELECT * FROM? [Sheet1$]";//可是更改Sheet名稱，比如sheet2，等等

??????????????? OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
??????????????? DataSet OleDsExcle = new DataSet();
??????????????? OleDaExcel.Fill(OleDsExcle, "Sheet1");
??????????????? OleConn.Close();
??????????????? return OleDsExcle;
??????????? }
??????????? catch (Exception err)
??????????? {
??????????????? MessageBox.Show("數據綁定Excel失敗!失敗原因：" + err.Message, "提示信息",
??????????????????? MessageBoxButtons.OK, MessageBoxIcon.Information);
??????????????? return null;
??????????? }
??????? }
2.寫入Excel內容，參數：excelTable是要導入excel的一個table表?

??????? public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
??????? {
??????????? Microsoft.Office.Interop.Excel.Application app =
??????????????? new Microsoft.Office.Interop.Excel.ApplicationClass();
??????????? try
??????????? {
??????????????? app.Visible = false;
??????????????? Workbook wBook = app.Workbooks.Add(true);
??????????????? Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
??????????????? if (excelTable.Rows.Count > 0)
??????????????? {
??????????????????? int row = 0;
??????????????????? row = excelTable.Rows.Count;
??????????????????? int col = excelTable.Columns.Count;
??????????????????? for (int i = 0; i < row; i++)
??????????????????? {
??????????????????????? for (int j = 0; j < col; j++)
??????????????????????? {
??????????????????????????? string str = excelTable.Rows[i][j].ToString();
??????????????????????????? wSheet.Cells[i + 2, j + 1] = str;
??????????????????????? }
??????????????????? }
??????????????? }

??????????????? int size = excelTable.Columns.Count;
??????????????? for (int i = 0; i < size; i++)
??????????????? {
??????????????????? wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
??????????????? }
??????????????? //設置禁止彈出保存和覆蓋的詢問提示框
??????????????? app.DisplayAlerts = false;
??????????????? app.AlertBeforeOverwriting = false;
??????????????? //保存工作簿
??????????????? wBook.Save();
??????????????? //保存excel文件
??????????????? app.Save(filePath);
??????????????? app.SaveWorkspace(filePath);
??????????????? app.Quit();
??????????????? app = null;
??????????????? return true;
??????????? }
??????????? catch (Exception err)
??????????? {
??????????????? MessageBox.Show("導出Excel出錯！錯誤原因：" + err.Message, "提示信息",
??????????????????? MessageBoxButtons.OK, MessageBoxIcon.Information);
??????????????? return false;
??????????? }
??????????? finally
??????????? {
??????????? }
??????? }

C#實現在Excel中將連續多列相同數據項合并

效果圖如下：

代碼如下：

/** <summary>
??????? /// 合并工作表中指定行數和列數數據相同的單元格
??????? /// </summary>
??????? /// <param name="sheetIndex">工作表索引</param>
??????? /// <param name="beginRowIndex">開始行索引</param>
??????? /// <param name="beginColumnIndex">開始列索引</param>
??????? /// <param name="rowCount">要合并的行數</param>
??????? /// <param name="columnCount">要合并的列數</param>
??????? public void MergeWorkSheet(int sheetIndex,int beginRowIndex,int beginColumnIndex,int rowCount,int columnCount)
??????? {

??????????? //檢查參數
??????????? if ( columnCount < 1 || rowCount < 1)
??????????????? return ;

??????????? for(int col=0;col<columnCount;col++)
??????????? {
??????????????? int mark = 0;??????????? //標記比較數據中第一條記錄位置
??????????????? int mergeCount = 1;??????? //相同記錄數，即要合并的行數
??????????????? string text = "";
???????????????
??????????????? for(int row=0;row<rowCount;row++)
??????????????? {
??????????????????? string prvName = "";
??????????????????? string nextName = "";

??????????????????? //最后一行不用比較
??????????????????? if( row + 1 < rowCount)???????
??????????????????? {
??????????????????????? for(int n=0;n<=col;n++)
??????????????????????? {
??????????????????????????? range = (Excel.Range)workSheet.Cells[row + beginRowIndex,n + beginColumnIndex];
??????????????????????????? range = (Excel.Range)range.MergeArea.get_Item(1,1);
??????????????????????????? text = range.Text.ToString();
??????????????????????????? prvName = prvName + text;

??????????????????????????? range = (Excel.Range)workSheet.Cells[row + 1 + beginRowIndex,n + beginColumnIndex];
??????????????????????????? range = (Excel.Range)range.MergeArea.get_Item(1,1);
??????????????????????????? nextName = nextName + range.Text.ToString();

??????????????????????? }
???????????????????????????
??????????????????????? if(prvName == nextName)
??????????????????????? {
??????????????????????????? mergeCount++;

??????????????????????????? if(row == rowCount - 2)
??????????????????????????? {
??????????????????????????????? this.MergeCells(sheetIndex,beginRowIndex + mark,beginColumnIndex + col,beginRowIndex + mark + mergeCount - 1,beginColumnIndex + col,text);
??????????????????????????? }
??????????????????????? }
??????????????????????? else
??????????????????????? {
??????????????????????????? this.MergeCells(sheetIndex,beginRowIndex + mark,beginColumnIndex + col,beginRowIndex + mark + mergeCount - 1,beginColumnIndex + col,text);
??????????????????????????? mergeCount = 1;
??????????????????????????? mark = row + 1;
??????????????????????? }
???????????????????????????
??????????????????? }???????
??????????????? }
??????????? }
??????? }

?

轉載于:https://www.cnblogs.com/ly5201314/archive/2009/06/06/1497448.html

總結

以上是生活随笔為你收集整理的C#操作Excel文件暨C#实现在Excel中将连续多列相同数据项合并的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。