.Net操作Excel总结

.Net操作Excel，主要有两种方式：1.使用ADO.net 操作EXCEL数据 ；2.调用EXCEL COM组件，操作EXCEL文件

 

1.使用ADO.net 操作EXCEL数据

/// <summary>
        /// ADO.Net 获取 Excel 数据
        /// </summary>
        /// <param name="inputFile">指定的Excel文件</param>
        /// <param name="sheetName">指定的工作表名；如果为空，则默认为第一个工作表</param>
        /// <returns>DataTable 数据表</returns>
        public static DataTable GetExcelDataAsOleDB(string inputFile, string sheetName)
        {
            if (inputFile == null || inputFile == "")
                throw new MyExcelHelperException("Excel文件路径不能为空！");
            if (!File.Exists(inputFile))
                throw new MyExcelHelperException("指定的Excel文件不存在！");

            

            OleDbConnection conn = null;
            try
            {
                string connStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0'",
                                                inputFile);

                conn = new OleDbConnection(connStr);
                conn.Open();
                string sql = string.Format("select * from [{0}$]", sheetName);

                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, sheetName);

                return ds.Tables[sheetName];
            }
            catch (Exception ex)
            {
                throw new MyExcelHelperException("OleDB操作异常: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

 

2.调用EXCEL COM组件

/// <summary>
        /// Excel应用获取 Excel 数据
        /// </summary>
        /// <param name="inputFile">指定的Excel文件</param>
        /// <param name="sheetName">指定的工作表名；如果为空，则默认为第一个工作表</param>
        /// <param name="x1">左上位置</param>
        /// <param name="y1">左上位置</param>
        /// <param name="x2">右下位置</param>
        /// <param name="y2">右下位置</param>
        /// <returns>DataTable 数据表</returns>
        public static DataTable GetExcelData(string inputFile, string sheetName, int x1, int y1, int x2, int y2)
        {
            if (inputFile == null || inputFile == "")
                throw new MyExcelHelperException("Excel文件路径不能为空！");
            if (!File.Exists(inputFile))
                throw new MyExcelHelperException("指定的Excel文件不存在！");

            // 打开 Excel 文件，取得 WorkBook 对象
            Excel.Workbook workBook = xApp.Workbooks.Open(inputFile,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);

            Excel.Worksheet workSheet = null;
            if (sheetName == "" || sheetName == null)
            {
                // 取得第一个 WorkSheet
                workSheet = (Excel.Worksheet)workBook.Worksheets[1];
            }
            else
            {
                foreach (Excel.Worksheet item in workBook.Worksheets)
                {
                    if (sheetName.Equals(item.Name, StringComparison.OrdinalIgnoreCase))
                    {
                        workSheet = item;
                        break;
                    }
                }
            }
            if (workSheet == null)
            {
                // 关闭工作薄
                workBook.Close(Excel.XlSaveAction.xlDoNotSaveChanges, inputFile, Missing.Value);
                throw new MyExcelHelperException("指定名称的Excel工作表不存在！");
            }

            DataTable dt = new DataTable();
            try
            {
                int dataRows = x2 - x1;
                int dataCols = y2 - y1 + 1;

                // 表头
                for (int j = 0; j < dataCols; j++)
                {
                    Excel.Range range = (Excel.Range)workSheet.Cells[x1, y1 + j];
                    dt.Columns.Add(range.Value2.ToString());
                }

                // 表内容
                for (int i = 0; i < dataRows; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < dataCols; j++)
                    {
                        Excel.Range range = (Excel.Range)workSheet.Cells[x1 + 1 + i, y1 + j];
                        dr[j] = range.Value2;
                    }
                    dt.Rows.Add(dr);
                }
            }
            catch (Exception ex)
            {
                throw new MyExcelHelperException("数据读取异常: " + ex.Message);
            }
            finally
            {
                // 关闭工作薄
                workBook.Close(Excel.XlSaveAction.xlDoNotSaveChanges, inputFile, Missing.Value);
            }

            return dt;
        }

 

 

/// <summary>
  /// 将 DataTable 数据写入Excel文件
  /// </summary>
  /// <param name="outputFile">指定的Excel输出文件</param>
  /// <param name="dt">DataTable</param>
  /// <param name="x1">表格数据起始行索引</param>
  /// <param name="y1">表格数据起始列索引</param>
  public static void ExportDataTableToExcel(string outputFile, DataTable dt,int x1,int y1)
  {
            if (outputFile == null || outputFile == "")
                throw new MyExcelHelperException("Excel文件路径不能为空！");

            Excel.Workbook workBook = null;
            try
            {
                // 新建一个 WorkBook
                workBook = xApp.Workbooks.Add(Missing.Value);

                //得到 WorkSheet 对象(第一个)
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];

                int rowCount = dt.Rows.Count;  //DataTable行数
                int colCount = dt.Columns.Count; //DataTable列数
               
                string[,] arr = new string[rowCount+1, colCount];

                // 写入表头
                for (int j = 0; j < colCount; j++)
                {
                    arr[0, j] = dt.Columns[j].ToString();
                }

                // 写入内容
                for (int i = 1; i < rowCount+1; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        arr[i, j] = dt.Rows[i-1][j].ToString();
                    }
                }
                Excel.Range range = (Excel.Range)workSheet.Cells[x1, y1];
                range = range.get_Resize(rowCount+1, colCount);
                range.Value2 = arr;

                try
                {
                    // 保存工作薄
                    workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml,
                                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                                    Missing.Value, Missing.Value, Missing.Value);
                }
                catch (Exception)
                { }

            }
            catch (Exception ex)
            {
                throw new MyExcelHelperException("数据导出异常: " + ex.Message);
            }
            finally
            {
                // 关闭工作薄
                workBook.Close(Excel.XlSaveAction.xlDoNotSaveChanges, outputFile, Missing.Value);
            }

  }