C# Excel ole文件读写操作类

在最近的项目中,需要将生产数据存入Excel中进行保存,于是参照ExcelHelper类(http://www.cnblogs.com/VAllen/articles/ExcelHelper_Peng.html),整理修改了一个Excel操作类,提供了两种连接字符串,支持offic2003格式和office2007及以上版本的xls、xlsx格式,请自行选择。转载请注明源地址。

实现的功能:

新建:

1.按照提供的模板新建空白Excel工作簿;

2.按照DataSet中的数据,新建包含Sheet和表头的新工作簿;

读取:

1.读取Excel工作簿中的数据,存入DataSet;

2.读取Excel第一个表格中的数据,存入DataTable;

写入:

1.向工作簿已有表格中追加数据(添加行和sheet);


注意:

使用时需要在工程中添加Microsoft.Office.Interop.Excel和Microsoft.Office.Core两项引用。


下面是代码部分:

</pre><pre name="code" class="csharp">using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;

namespace YourNamespace
{
    /// <summary>
    /// Excel 操作类,by southpolaris
    /// </summary>
    class ExcelOperate
    {

        private readonly string connectionString;
        public OleDbConnection odc; //连接对象
        private Microsoft.Office.Interop.Excel.Application excel;

        private static bool firstRead = true; //第一次写入获取已有行数
        private static long startIndex = 0;

        /// <summary>
        /// 初始化一个Excel操作实例
        /// </summary>
        /// <param name="pathString">请提供一个Excel文件路径,无论是已创建的或者是未创建的</param>
        public ExcelHelper(string pathStr)
        {
            this.connectionString = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + pathStr + ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\""; //for Office2007-2013
            this.connectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + pathStr +  ";Extended Properties=Excel 8.0;Persist Security Info=False";  //for Office1997-2003
            this.odc = new OleDbConnection(connectionString);
            excel = new Microsoft.Office.Interop.Excel.Application();//create Excel manipulate objects
        }

        /// <summary>
        /// 读取Excel文档所有数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>Excel文档所有数据</returns>
        public DataSet ReadExcel(string sql)
        {
            DataSet ds = new DataSet();
            odc.Open();
            OleDbDataAdapter oda = new OleDbDataAdapter(sql, odc);
            oda.Fill(ds);
            odc.Close();
            return ds;
        }

        /// <summary>
        /// 读取一个Excel文档中第一个Sheet档的Sheet数据
        /// </summary>
        /// <returns>Excel文档中第一个Sheet档的Sheet数据</returns>
        public DataTable ReadExcel()
        {
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string tableName = dt.Rows[0][2].ToString().Trim();
            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", odc);
            oda.Fill(dt);
            odc.Close();
            return dt;
        }

        /// <summary>
        /// 打开一个已存在的Excel文档,并写入数据集
        /// <para>请注意,Excel文档内的多个Sheet有可能被覆盖</para>
        /// <para>原因:Sheet名相同</para>
        /// </summary>
        /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
        /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
        /// <param name="templateFile">可选项,如果提供一个已存在的excel名称路径则以此为模板新建工作簿</param>
        /// <returns></returns>
        public bool WriteExcel(DataSet ds, string pathString, string templateFile)
        {
            if (!File.Exists(pathString))
            {
                FileStream fs = File.Create(pathString);
                fs.Dispose();
                fs.Close();
            }

            bool result = true;
            Microsoft.Office.Interop.Excel.Workbook workBook = excel.Workbooks.Open(pathString, 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); //这里改过了

            try
            {
                excel.SheetsInNewWorkbook = ds.Tables.Count;//创建sheet的数量
                for (int number = 0; number < ds.Tables.Count; number++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet sheet = workBook.Worksheets[number + 1] as Microsoft.Office.Interop.Excel.Worksheet;//获取sheet;
                    DataTable dt = ds.Tables[number];//获取表
                    if (templateFile == null) //没有模板,使用默认样式添加表头信息
                    {
                        int i = 0;
                        for (; i < dt.Columns.Count; i++)//动态添加
                        {
                            sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//添加表头
                        }
                        Microsoft.Office.Interop.Excel.Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]);//编辑区域
                        range.Font.Bold = true;//字体加粗
                        range.Font.Color = 0;//字体颜色
                        range.Interior.ColorIndex = 15;
                        range.ColumnWidth = 15;//列宽
                        range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//边框样式
                        sheet.Name = dt.TableName;//设置sheet名
                    }

                    //添加已有数据
                    DataTable existDt = ReadExcel();
                    removeEmpty(ref existDt);
                    startIndex = existDt.Rows.Count;
                    
                    //写入数据
                    for (int y = 0; y < dt.Rows.Count; y++)//动态添加行数据
                    {
                        for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++)//动态添加列数据
                        {
                            string temp = dt.Rows[y][x].ToString();
                            sheet.Cells[y + startIndex + 1, x + 1] = temp; //赋值
                        }
                    }
                }
                excel.Visible = false;//显示预览false
                excel.DisplayAlerts = false; 
                excel.AlertBeforeOverwriting = false; //设置禁止弹出保存和覆盖的询问提示框
                //保存
                workBook.Save();
                //excel.Save(pathString);
                //excel.SaveWorkspace(pathString);
            }
            catch (Exception ex)
            {
                result = false;
                throw new Exception(string.Format("(1)程序中没有安装Excel程序。\n(2)没有安装Excel所需要支持的.NetFramework\n详细信息:{0}", ex.Message));
            }
            finally
            {
                excel.ActiveWorkbook.Close(true, null, null);//关闭Excel对象
                excel.Quit();//退出
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }

            return result;
        }

        //再次写入时重新读取
        public void StartIndexReset()
        {
            firstRead = true;
        }

        /// <summary>
        /// 创建一个新的Excel文件,并写入数据集
        /// <para>请注意,Excel文件可能创建失败</para>
        /// <para>原因:指定Excel文件已存在</para>
        /// </summary>
        /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
        /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
        /// <param name="templateFile">可选项,如果提供一个已存在的excel名称路径则以此为模板新建工作簿</param>
        /// <returns></returns>
        public bool CreateExcel(DataSet ds, string pathString, string templateString)
        {
            bool result = true;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//create Excel manipulate objects
            try
            {
                excel.SheetsInNewWorkbook = ds.Tables.Count;//获取Sheet档数量
                excel.Workbooks.Add(templateString);
                for (int number = 0; number < ds.Tables.Count; number++)//循环添加Sheet档
                {
                    Microsoft.Office.Interop.Excel.Worksheet sheet = excel.ActiveWorkbook.Worksheets[number + 1] as Microsoft.Office.Interop.Excel.Worksheet;//获取sheet;
                    DataTable dt = ds.Tables[number];//获取表

                    //没有模板,添加表头和格式
                    if (templateString == null)
                    {
                        sheet.Name = dt.TableName;//设置Sheet档名
                        int i = 0;
                        for (; i < dt.Columns.Count; i++)//循环添加列头
                        {
                            sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//设置列头名
                        }
                        Microsoft.Office.Interop.Excel.Range range =
                            excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]);//设置编辑区域
                        range.Font.Bold = true;//字体加粗
                        range.Font.ColorIndex = 0;//字体颜色
                        range.Interior.ColorIndex = 15;//背景颜色
                        range.ColumnWidth = 15;//列宽
                        range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//边框样式
                        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//字体居中
                    }
                   
                    //添加数据
                    for (int y = 0; y < dt.Rows.Count; y++)//循环添加行
                    {
                        for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++)//循环添加列
                        {
                            sheet.Cells[y + 2, x + 1] = dt.Rows[y][x];//设置列值
                        }
                    }
                }
                excel.Visible = false;//设置为预览false
                System.Threading.Thread.Sleep(5000);//线程延迟5秒再预览
                excel.ActiveWorkbook.SaveAs(pathString, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                    null, null, null, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);//另存为
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            finally
            {
                excel.ActiveWorkbook.Close(true, null, null);//关闭Excel对象
                excel.Quit();//退出
            }
            return result;
        }

        public bool CreateEmptyExcel(string pathString, string templateString)
        {
            bool result = true;

            try
            {
                excel.Workbooks.Add(templateString);
                excel.Visible = false;  //设置为预览false
                excel.ActiveWorkbook.SaveAs(pathString, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
                    Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
                    Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);//另存为
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            finally
            {
                excel.ActiveWorkbook.Close(true, null, null);
                excel.Quit();
            }
            return true;
        }

        //循环去除datatable中的空行
        protected void removeEmpty(ref DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {

                        rowdataisnull = false;
                    }

                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }
    }
}


由于项目中仅使用了部分功能,可能还存在缺陷,如有错误请指正,如有更好的思路请指教。

若要在服务器端或没有安装Microsoft Office的环境中对Excel操作,可以使用NPOI类来进行相关操作。

相关文章
相关标签/搜索