.NET操作Excel的两种方式对比

操作Excel最简单的方式就是通过oledb的sql语句操作Excel。

譬如，查询excel中的g_gongcheng这个表：

 OleDbDataAdapter oAdpt = null; DataSet ds = new DataSet(); oConn.Open(); //创建适配器 oAdpt = new OleDbDataAdapter("SELECT distinct GongChengXuHao,gcmc from g_gongcheng where gcmc is not null", oConn); oAdpt.Fill(ds); comboBox_ProjectNO.BeginUpdate(); foreach (DataRow dr in ds.Tables[0].Rows) { this.comboBox_ProjectNO.Items.Add(dr["GongChengXuHao"].ToString() + " " + dr["gcmc"].ToString()); } this.comboBox_ProjectNO.EndUpdate();

 

 

如果是碰到复杂的EXCEL操作，或者说是生成复杂的EXCEL报表，则我们需要用到微软的Microsoft.Office.Interop.Excel.dll类库。

思路：

创建一个Application对象=>得到WorkBook对象=>打开工作薄=>操作工作薄

源代码：

using System; using System.Collections.Generic; using System.Text; using System.IO; using Microsoft.Office.Interop.Excel; using System.Diagnostics; using System.Windows.Forms; namespace NewReport.dataOp { public delegate void AlignDelegate(); class ExcelOperation { #region 成员变量 private string templetFile = null; private string outputFile = null; private object missing = Type.Missing; Microsoft.Office.Interop.Excel.Application app; //引用Excel对象 Microsoft.Office.Interop.Excel.Workbook workBook; //引用WorkBoo对象 Microsoft.Office.Interop.Excel.Worksheet workSheet;//引用Excel工作簿 Microsoft.Office.Interop.Excel.Range range; Microsoft.Office.Interop.Excel.Range range1; Microsoft.Office.Interop.Excel.Range range2; private int sheetCount = 1; //WorkSheet数量 #endregion #region 公共属性 /// <summary> /// 获取WorkSheet数量 /// </summary> public int WorkSheetCount { get { return workBook.Sheets.Count; } } /// <summary> /// Excel模板文件路径 /// </summary> public string TempletFilePath { set { this.templetFile = value; } } /// <summary> /// 输出Excel文件路径 /// </summary> public string OutPutFilePath { set { this.outputFile = value; } } /// <summary> /// 获取当前工作薄的名称 /// </summary> /// <returns></returns> public string getCurrentWorkSheetName { get { return workSheet.Name; } } #endregion #region 公共方法 #region ExcelOperation public ExcelOperation(string templetFilePath, string OutputFilePath) { if (String.IsNullOrEmpty(templetFilePath)) throw new Exception("Excel模板文件路径不能为空！"); if (String.IsNullOrEmpty(OutputFilePath)) throw new Exception("输出Excel文件路径不能为空！"); if (!File.Exists(templetFilePath)) throw new Exception("模板文件不存在！"); this.templetFile = templetFilePath; this.outputFile = OutputFilePath; //创建一个Application对象并使其可见 app = new Microsoft.Office.Interop.Excel.ApplicationClass(); app.Visible = false; //app.Visible = false; //打开模板文件得到WorkBook对象 //workBook = app.Workbooks.Open(Environment.CurrentDirectory + "//" + templetFile, 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); workBook = app.Workbooks.Open(templetFile, 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); //默认打开第一个工作薄 this.workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion #region WorkSheet方法 public int GetRngPage(Microsoft.Office.Interop.Excel.Range TargetCell,Range ActivateCell) { //workSheet.DisplayPageBreaks = false; this.workBook.Activate(); Microsoft.Office.Interop.Excel.Worksheet Sht = this.workSheet; //Sht.DisplayPageBreaks=true; //Sht.ResetAllPageBreaks(); //Sht.DisplayAutomaticPageBreaks=true; //TargetCell.Activate(); //TargetCell.Select(); //Sht.Activate(); //Sht.Select(); Sht.Select(true); string strName = Sht.Name; ActivateCell.Activate(); ActivateCell.Select(); int getRngPage = -1; Microsoft.Office.Interop.Excel.Range Rng; Rng = Sht.UsedRange; int hP, vP, i; hP = Sht.HPageBreaks.Count + 1; vP = Sht.VPageBreaks.Count + 1; for (int k = 1; k <= Sht.VPageBreaks.Count; k++) { if (Sht.VPageBreaks[k].Location.Column > TargetCell.Column) { vP = k; break; } } for (int k = 1; k <= Sht.HPageBreaks.Count; k++) { if (Sht.HPageBreaks[k].Location.Row > TargetCell.Row) { hP = k; break; } } if (Sht.PageSetup.Order == XlOrder.xlDownThenOver) { getRngPage = (vP - 1) * (Sht.HPageBreaks.Count + 1) + hP; } else { getRngPage = (hP - 1) * (Sht.VPageBreaks.Count + 1) + vP; } return getRngPage; } public void CheckPageBreaks() { } /// <summary> /// 改变当前工作薄 /// </summary> /// <param name="sheetIndex"></param> public void changeCurrentWorkSheet(int sheetIndex) { if (sheetIndex < 1) return; if (sheetIndex > this.WorkSheetCount) return; this.workSheet = (Microsoft.Office.Interop.Excel.Worksheet)this.workBook.Sheets.get_Item(sheetIndex); } public void save() { workBook.Save(); } public void Visible(bool tf) { app.Visible = tf; } /// <summary> /// 根据名称打开工作薄 /// </summary> /// <param name="sheetName"></param> public void openSheetByName(string sheetName) { for (int i = 1; i <= this.WorkSheetCount; i++) { this.changeCurrentWorkSheet(i); if (getCurrentWorkSheetName == sheetName) { return; } } } public Microsoft.Office.Interop.Excel.Worksheet openSheetByName1(string sheetName) { for (int i = 1; i <= this.WorkSheetCount; i++) { this.changeCurrentWorkSheet(i); if (getCurrentWorkSheetName == sheetName) return this.workSheet; } return this.workSheet; } #endregion #region Cell 方法 /// <summary> /// 向单元格写入数据，对当前WorkSheet操作 /// </summary> /// <param name="rowIndex"></param> /// <param name="columnIndex"></param> /// <param name="text"></param> public void setCells(int rowIndex, int columnIndex, string text) { try { workSheet.Cells[rowIndex, columnIndex] = text; } catch { this.KillExcelProcess(); throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！"); } } /// <summary> /// 合同当前工作薄中的单元格，并赋值 /// </summary> /// <param name="beginRowIndex"></param> /// <param name="beginColumnIndex"></param> /// <param name="endRowIndex"></param> /// <param name="endColumnIndex"></param> /// <param name="text"></param> /// <param name="align"></param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text, AlignDelegate align) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除，合并才不会出错 range.MergeCells = true; range.Value2 = text; //range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; align(); range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; } /// <summary> /// 合同当前工作薄中的单元格，并赋值,默认居中 /// </summary> /// <param name="beginRowIndex"></param> /// <param name="beginColumnIndex"></param> /// <param name="endRowIndex"></param> /// <param name="endColumnIndex"></param> /// <param name="text"></param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除，合并才不会出错 range.MergeCells = true; range.Value2 = text; range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; } /// <summary> /// 单元格文本左对齐 /// </summary> /// <param name="range"></param> public void alignLeft() { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } /// <summary> /// 单元格文本居中 /// </summary> /// <param name="range"></param> public void alignCenter() { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; } /// <summary> /// 单元格文本右对齐 /// </summary> /// <param name="range"></param> public void alignRight() { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; } #endregion public void setRangFormula(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string sheetName,string RC) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.Formula = "=" + sheetName + "!" + RC; } public void setRangFormula1(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string RC) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.Formula = "=" + RC; } #region Row 方法 /// <summary> /// 插行（在指定行上面插入指定数量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex, int count) { try { range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, this.missing); } } catch (Exception e) { this.KillExcelProcess(); throw e; } } /// <summary> /// 删除行 /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int i = 0; i < count; i++) { range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(); MessageBox.Show(e.Message.ToString()); } } /// <summary> /// 设置行高 /// </summary> /// <param name="startCell"></param> /// <param name="endCell"></param> /// <param name="RowHeight"></param> public void RowHeight(string startCell, string endCell, double RowHeight) { try { range1 = workSheet.get_Range(startCell, endCell); range1.RowHeight = RowHeight; } catch (Exception e) { this.KillExcelProcess(); throw e; } } #endregion #region Range 方法 public Microsoft.Office.Interop.Excel.Range getRange(string startCell, string endCell) { range = workSheet.get_Range(startCell, endCell); return range; } /// <summary> /// 将指定范围区域拷贝到目标区域 /// </summary> /// <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param> /// <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param> /// <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param> public void RangeCopy(string startCell, string endCell, string targetCell) { try { range1 = workSheet.get_Range(startCell, endCell); range2 = workSheet.get_Range(targetCell, this.missing); range1.Copy(range2); } catch (Exception e) { this.KillExcelProcess(); throw e; } } /// <summary> /// 读取单元格数据 /// </summary> /// <param name="rowIndex"></param> /// <param name="columnIndex"></param> public string getCells(int rowIndex, int columnIndex) { object obj = workSheet.get_Range(workSheet.Cells[rowIndex, columnIndex], workSheet.Cells[rowIndex, columnIndex]).Value2; if (obj == null) return "0"; else return obj.ToString(); } #endregion #endregion #region 私有方法 /// <summary> /// 结束Excel进程 /// </summary> private void KillExcelProcess() { Process[] myProcesses; myProcesses = Process.GetProcessesByName("Excel"); //得不到Excel进程ID，暂时只能判断进程启动时间 foreach (Process myProcess in myProcesses) { myProcess.Kill(); } } #endregion } }