一：C#导入导出EXCEL文件的类

代码如下：

首先将Microsoft Excel 14.0 Object Library 引用导入

using System;

using System.Data;

using System.Data.OleDb;

namespace ZFSoft.Joint

{

    public class ExcelIO

    {

        private int _ReturnStatus;

        private string _ReturnMessage;

        /// <summary>

        /// 执行返回状态

        /// </summary>

        public int ReturnStatus

        {

            get

            {

                return _ReturnStatus;

            }

        }

        /// <summary>

        /// 执行返回信息

        /// </summary>

        public string ReturnMessage

        {

            get

            {

                return _ReturnMessage;

            }

        }

        public ExcelIO()

        {

        }

        /// <summary>

        /// 导入EXCEL到DataSet

        /// </summary>

        /// <param name="fileName">Excel全路径文件名</param>

        /// <returns>导入成功的DataSet</returns>

        public DataTable ImportExcel(string fileName)

        {

            //判断是否安装EXCEL

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                _ReturnStatus = -;

                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";

                return null;

            }

            //判断文件是否被其他进程使用

            Microsoft.Office.Interop.Excel.Workbook workbook;

            try

            {

                workbook = xlApp.Workbooks.Open(fileName, , false, , "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, , true, , );

            }

            catch

            {

                _ReturnStatus = -;

                _ReturnMessage = "Excel文件处于打开状态，请保存关闭";

                return null;

            }

            //获得所有Sheet名称

            int n = workbook.Worksheets.Count;

            string[] SheetSet = new string[n];

            System.Collections.ArrayList al = new System.Collections.ArrayList();

            for (int i = ; i <= n; i++)

            {

                SheetSet[i - ] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;

            }

            //释放Excel相关对象

            workbook.Close(null, null, null);

            xlApp.Quit();

            if (workbook != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

                workbook = null;

            }

            if (xlApp != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;

            }

            GC.Collect();

            //把EXCEL导入到DataSet

            DataSet ds = new DataSet();

            DataTable table = new DataTable();

            string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";

            using (OleDbConnection conn = new OleDbConnection(connStr))

            {

                conn.Open();

                OleDbDataAdapter da;

                string sql = "select * from [" + SheetSet[] + "$] ";

                da = new OleDbDataAdapter(sql, conn);

                da.Fill(ds, SheetSet[]);

                da.Dispose();

                table = ds.Tables[];

                conn.Close();

                conn.Dispose();

            }

            return table;

        }

        /// <summary>

        /// 把DataTable导出到EXCEL

        /// </summary>

        /// <param name="reportName">报表名称</param>

        /// <param name="dt">数据源表</param>

        /// <param name="saveFileName">Excel全路径文件名</param>

        /// <returns>导出是否成功</returns>

        public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)

        {

            if (dt == null)

            {

                _ReturnStatus = -;

                _ReturnMessage = "数据集为空！";

                return false;

            }

            bool fileSaved = false;

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                _ReturnStatus = -;

                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";

                return false;

            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[];//取得sheet1

            worksheet.Cells.Font.Size = ;

            Microsoft.Office.Interop.Excel.Range range;

            long totalCount = dt.Rows.Count;

            long rowRead = ;

            float percent = ;

            worksheet.Cells[, ] = reportName;

            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, ]).Font.Size = ;

            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, ]).Font.Bold = true;

            //写入字段

            for (int i = ; i < dt.Columns.Count; i++)

            {

                worksheet.Cells[, i + ] = dt.Columns[i].ColumnName;

                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ];

                range.Interior.ColorIndex = ;

                range.Font.Bold = true;

            }

            //写入数值

            for (int r = ; r < dt.Rows.Count; r++)

            {

                for (int i = ; i < dt.Columns.Count; i++)

                {

                    worksheet.Cells[r + , i + ] = dt.Rows[r][i].ToString();

                }

                rowRead++;

                percent = ((float)( * rowRead)) / totalCount;

            }

            //此行有可能会出错，解决办法见下面

            range = worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[dt.Rows.Count + , dt.Columns.Count]);

            range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);

            if (dt.Rows.Count > )

            {

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            }

            if (dt.Columns.Count > )

            {

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            }

            //保存文件

            if (saveFileName != "")

            {

                try

                {

                    workbook.Saved = true;

                    workbook.SaveCopyAs(saveFileName);

                    fileSaved = true;

                }

                catch (Exception ex)

                {

                    fileSaved = false;

                    _ReturnStatus = -;

                    _ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;

                }

            }

            else

            {

                fileSaved = false;

            }

            //释放Excel对应的对象

            if (range != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);

                range = null;

            }

            if (worksheet != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);

                worksheet = null;

            }

            if (workbook != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

                workbook = null;

            }

            if (workbooks != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);

                workbooks = null;

            }

            xlApp.Application.Workbooks.Close();

            xlApp.Quit();

            if (xlApp != null)

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

                xlApp = null;

            }

            GC.Collect();

            return fileSaved;

        }

    }

}

这种写法可能出现，Object未包含get_range定义，解决办法是将get_range变为Range：

range = worksheet.Range[worksheet.Cells[, ], worksheet.Cells[dt.Rows.Count + , dt.Columns.Count]];

调用该类的具体使用，导入表中的数据到dataGridView，实际上也是先到dataset里面：

private void button1_Click(object sender, EventArgs e)

        {

            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel Files|*.xlsx";

            if (ofd.ShowDialog() == DialogResult.OK)

            {

                string filename = ofd.FileName;

                ExcelIO ex = new ExcelIO();

                dataGridView1.DataSource = ex.ImportExcel(filename);

            }

        }

调用该类的具体使用，从数据库导出到Excel，实际上也是先到dataset里面：

 private void button2_Click(object sender, EventArgs e)

        {

            CarTableAdapter ca = new CarTableAdapter();

            DataSet1.CarDataTable table = ca.GetData();

            ExcelIO ex = new ExcelIO();

            ex.ExportExcel("qiche",table,@"C:\Users\Administrator\Desktop\例子.xlsx");

        }

二：C# WinForm导出Excel方法

在.NET应用中，导出Excel是很常见的需求，导出Excel报表大致有以下三种方式：Office PIA，文件流和NPOI开源库，.NET开发人员首选的方法，通过COM组件调用Office软件本身来实现文件的创建和读写，但是数据量较大的时候异常缓慢；如下代码所示已经做了优化，将一个二维对象数组赋值到一个单元格区域中（下面的代码中只能用于导出列数不多于26列的数据导出）：

OFFICE PIA：

public static void ExportToExcel(DataSet dataSet, string outputPath)

{

    Excel.ApplicationClass excel = new Excel.ApplicationClass();

    Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);

    int sheetIndex = ;

    foreach (System.Data.DataTable dt in dataSet.Tables)

    {

        object[,] data = new object[dt.Rows.Count + , dt.Columns.Count];

        for (int j = ; j < dt.Columns.Count; j++)

        {

            data[, j] = dt.Columns[j].ColumnName;

        }

        for (int j = ; j < dt.Columns.Count; j++)

        {

            for (int i = ; i < dt.Rows.Count; i++)

            {

                data[i + , j] = dt.Rows[i][j];

            }

        }

        string finalColLetter = string.Empty;

         string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        int colCharsetLen = colCharset.Length;

        if (dt.Columns.Count > colCharsetLen)

        {

            finalColLetter = colCharset.Substring(

                (dt.Columns.Count - ) / colCharsetLen - , );

        }

        finalColLetter += colCharset.Substring(

                (dt.Columns.Count - ) % colCharsetLen, );

        Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.Add(

            workbook.Sheets.get_Item(++sheetIndex),

            Type.Missing, , Excel.XlSheetType.xlWorksheet);

        sheet.Name = dt.TableName;

        string range = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + );

        sheet.get_Range(range, Type.Missing).Value2 = data;

        ((Excel.Range)sheet.Rows[, Type.Missing]).Font.Bold = true;

    }

    workbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,

        Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,

        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    workbook.Close(true, Type.Missing, Type.Missing);

    workbook = null;

    excel.Quit();

    KillSpecialExcel(excel);

    excel = null;

    GC.Collect();

    GC.WaitForPendingFinalizers();

}

 [DllImport("user32.dll", SetLastError = true)]

static extern int GetWindowThreadProcessId(IntPtr hWnd, out int processId);

 static void KillSpecialExcel(Excel.Application app)

{

    try

    {

        if (app != null)

        {

            int processId;

            GetWindowThreadProcessId(new IntPtr(app.Hwnd), out processId);

            System.Diagnostics.Process.GetProcessById(processId).Kill();

        }

    }

    catch (Exception ex)

    {

        throw ex;

    }

}

文件流

这种方法的效率明显高于第一种，而且也不需要安装Office，但是导出的xls文件并不符合Excel的格式标准，在打开生成的xls文件时会提示：The file you are trying to open is in a different format that specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.

public static void ExportToExcel(System.Data.DataSet ds, string path)

{

    StreamWriter sw = null;

    try

    {

        long totalCount = ds.Tables[].Rows.Count;

        sw = new StreamWriter(path, false, Encoding.Unicode);

        StringBuilder sb = new StringBuilder();

        for (int i = ; i < ds.Tables[].Columns.Count; i++)

        {

            sb.Append(ds.Tables[].Columns[i].ColumnName + "\t");

        }

        sb.Append(Environment.NewLine);

        for (int i = ; i < ds.Tables[].Rows.Count; i++)

        {

            for (int j = ; j < ds.Tables[].Columns.Count; j++)

            {

                sb.Append(ds.Tables[].Rows[i][j].ToString() + "\t");

            }

            sb.Append(Environment.NewLine);

        }

        sw.Write(sb.ToString());

        sw.Flush();

    }

    catch (IOException ioe)

    {

        throw ioe;

    }

    finally

    {

        if (sw != null)

        {

            sw.Close();

        }

    }

}

三：WinForm项目开发中Excel用法实例解析

在实际项目的开发过程中，所涉及的EXCEL往往会比较复杂，并且列中还会带有一些计算公式，这就给读取带来了很大的困难，曾经尝试过一些免费的第三方dll，譬如Myxls，NPOI，IExcelDataReader都会出现一些问题，最后采用OLEDB形式读取，再x64操作系统上有点问题，不过采用小技巧即可解决。

namespace DBUtilHelpV2

{

public class OLEDBExcelToolV2

{

static readonly string xls = ".xls";

static readonly string xlsx = ".xlsx";

string _ExcelExtension = string.Empty;//后缀

string _ExcelPath = string.Empty;//路径

string _ExcelConnectString = string.Empty;//链接字符串

static bool _X64Version = false;//是否强制使用x64链接字符串，即xlsx形式

public OLEDBExcelToolV2(string excelPath, bool x64Version)

{

  if (string.IsNullOrEmpty(excelPath))

 throw new ArgumentNullException("excelPath");

  if (!File.Exists(excelPath))

 throw new ArgumentException("excelPath");

  string _excelExtension = Path.GetExtension(excelPath);

  _ExcelExtension = _excelExtension.ToLower();

  _ExcelPath = excelPath;

  _X64Version = x64Version;

  _ExcelConnectString = BuilderConnectionString();

}

/// <summary>

/// 创建链接字符串

/// </summary>

/// <returns></returns>

private string BuilderConnectionString()

{

  Dictionary<string, string> _connectionParameter = new Dictionary<string, string>();

  if (!_ExcelExtension.Equals(xlsx) && !_ExcelExtension.Equals(xls))

  {

 throw new ArgumentException("excelPath");

  }

  if (!_X64Version)

  {

 if (_ExcelExtension.Equals(xlsx))

 {

   // XLSX - Excel 2007, 2010, 2012, 2013

   _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";

   _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";

 }

 else if (_ExcelExtension.Equals(xls))

 {

   // XLS - Excel 2003 and Older

   _connectionParameter["Provider"] = "Microsoft.Jet.OLEDB.4.0";

   _connectionParameter["Extended Properties"] = "'Excel 8.0;IMEX=1'";

 }

  }

  else

  {

 _connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";

 _connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";

  }

  _connectionParameter["Data Source"] = _ExcelPath;

  StringBuilder _connectionString = new StringBuilder();

  foreach (KeyValuePair<string, string> parameter in _connectionParameter)

  {

 _connectionString.Append(parameter.Key);

 _connectionString.Append('=');

 _connectionString.Append(parameter.Value);

 _connectionString.Append(';');

  }

  return _connectionString.ToString();

}

/// <summary>

/// Excel操作

/// DELETE不支持

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public int ExecuteNonQuery(string sql)

{

  int _affectedRows = -;

  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))

  {

 try

 {

   sqlcon.Open();

   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))

   {

 _affectedRows = sqlcmd.ExecuteNonQuery();

   }

 }

 catch (Exception)

 {

   return -;

 }

  }

  return _affectedRows;

}

/// <summary>

/// Excel操作

///获取EXCEL内sheet集合

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public string[] GetExcelSheetNames()

{

  DataTable _schemaTable = null;

  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))

  {

 try

 {

   sqlcon.Open();

   _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

   String[] _excelSheets = new String[_schemaTable.Rows.Count];

   int i = ;

   foreach (DataRow row in _schemaTable.Rows)

   {

 _excelSheets[i] = row["TABLE_NAME"].ToString().Trim();

 i++;

   }

   return _excelSheets;

 }

 catch (Exception)

 {

   return null;

 }

 finally

 {

   if (_schemaTable != null)

   {

 _schemaTable.Dispose();

   }

 }

  }

}

/// <summary>

/// 读取sheet

/// eg:select * from [Sheet1$]

/// </summary>

/// <param name="sql"></param>

/// <returns></returns>

public DataTable ExecuteDataTable(string sql)

{

  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))

  {

 try

 {

   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))

   {

 using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))

 {

   DataTable _dtResult = new DataTable();

   sqldap.Fill(_dtResult);

   return _dtResult;

 }

   }

 }

 catch (Exception)

 {

   return null;

 }

  }

}

/// <summary>

/// 获取excel所有sheet数据

/// </summary>

/// <returns>DataSet</returns>

public DataSet ExecuteDataSet()

{

  DataSet _excelDb = null;

  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))

  {

 try

 {

   sqlcon.Open();

   DataTable _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

   if (_schemaTable != null)

   {

 int i = ;

 _excelDb = new DataSet();

 foreach (DataRow row in _schemaTable.Rows)

 {

   string _sheetName = row["TABLE_NAME"].ToString().Trim();

   string _sql = string.Format("select * from [{0}]", _sheetName);

   using (OleDbCommand sqlcmd = new OleDbCommand(_sql, sqlcon))

   {

  using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))

  {

    DataTable _dtResult = new DataTable();

    _dtResult.TableName = _sheetName;

    sqldap.Fill(_dtResult);

    _excelDb.Tables.Add(_dtResult);

  }

   }

   i++;

 }

   }

 }

 catch (Exception)

 {

   return null;

 }

  }

  return _excelDb;

}

}

}

代码使用方法：

/// <summary>

/// 合并EXCEL数据

/// </summary>

/// <param name="_excelPath">excel路径</param>

private void HandleMergeExcel(string _excelPath)

{

  if (!string.IsNullOrEmpty(_excelPath))

  {

 OLEDBExcelToolV2 _excelHelper = new OLEDBExcelToolV2(_excelPath, true);

 DataSet _excelSource = _excelHelper.ExecuteDataSet();

 HandleExcelSource(_excelSource);

  }

}

若在x64操作系统，将第二个参数设置true，并且按照AccessDatabaseEngine_X64.exe即可正常读取。

最新文章

  1. RabbitMQ之前的那些事
  2. Supercell only provide the best games for players
  3. Java中的转义字符
  4. 反演dp经典
  5. 给Activity设置背景颜色
  6. 关键字 virtual
  7. 设置Cookie
  8. 根据goodsId获得相关商品的列表
  9. JavaScript基础学习
  10. Python读入与写出中文时出现乱码
  11. angular+ionic前后端分离开发项目中的使用
  12. yii2.0 app上集成支付宝支付
  13. WPF防止界面卡死并显示加载中效果
  14. 教师派day1
  15. handle java
  16. Faster R-CNN：详解目标检测的实现过程
  17. bpmn.js &amp; BPMN diagram
  18. QSplineSeries QChartView绘制曲线
  19. .net连接ORACLE数据库
  20. [020]Sencha Ext JS 6.0使用教程2

热门文章

  1. mssql server 2005还原数据库bak文件与“备份集中的数据库备份与现有的xx数据库不同”解决方法
  2. #pragma once 与 #ifndef 解析（转载）
  3. 在ubuntu中启用ftp服务
  4. matlab 函数说明--fspecial
  5. Android实例-闪光灯的控制(XE8+小米2)
  6. PC返回顶部浮动按钮
  7. C++学习笔记（九）：作用域和命名空间
  8. SQLite数据库入门教程
  9. int、bigint、smallint 和 tinyint
  10. 导出cluster log