C#导入导出Excel表的数据

一：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 = -1;
                _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
                return null;
            }

            //判断文件是否被其他进程使用           
            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _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 = 1; i <= n; i++)
            {
                SheetSet[i - 1] = ((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[0] + "$] ";
                da = new OleDbDataAdapter(sql, conn);
                da.Fill(ds, SheetSet[0]);
                da.Dispose();
                table = ds.Tables[0];
                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 = -1;
                _ReturnMessage = "数据集为空！";
                return false;
            }

            bool fileSaved = false;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                _ReturnStatus = -1;
                _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[1];//取得sheet1
            worksheet.Cells.Font.Size = 10;
            Microsoft.Office.Interop.Excel.Range range;

            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;

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

            //写入字段
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;

            }
            //写入数值
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            //此行有可能会出错，解决办法见下面
            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, 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 > 0)
            {
                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 > 1)
            {
                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 = -1;
                    _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[2, 1], worksheet.Cells[dt.Rows.Count + 2, 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 = 0;
    foreach (System.Data.DataTable dt in dataSet.Tables)
    {
        object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            data[0, j] = dt.Columns[j].ColumnName;
        }
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data[i + 1, 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 - 1) / colCharsetLen - 1, 1);
        }
        finalColLetter += colCharset.Substring(
                (dt.Columns.Count - 1) % colCharsetLen, 1);

        Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.Add(
            workbook.Sheets.get_Item(++sheetIndex),
            Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
        sheet.Name = dt.TableName;
        string range = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
        sheet.get_Range(range, Type.Missing).Value2 = data;
        ((Excel.Range)sheet.Rows[1, 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[0].Rows.Count;
        sw = new StreamWriter(path, false, Encoding.Unicode);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
        {
            sb.Append(ds.Tables[0].Columns[i].ColumnName + "\t");
        }
        sb.Append(Environment.NewLine);
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                sb.Append(ds.Tables[0].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 = -1;
  using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
  {
 try
 {
   sqlcon.Open();
   using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
   {
 _affectedRows = sqlcmd.ExecuteNonQuery();
   }
 }
 catch (Exception)
 {
   return -1;
 }
  }
  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 = 0;
   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 = 0;
 _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即可正常读取。

posted @ 2014-11-12 13:18 从小学吐槽阅读(968) 评论(0) 编辑收藏举报

刷新页面返回顶部

彩云之遥

C#导入导出Excel表的数据

公告