学步园

一个的Excel文件操作类--ExcelHelper

 

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
 
namespace DAL
{
    /// <summary>
    /// Peng制作
    /// <para>QQ:378781081</para>
    /// </summary>
    /// <see cref="http://www.cnblogs.com/VAllen"/>
    public class ExcelHelper
    {
        /// <summary>
        /// 初始化一个Excel操作实例
        /// <para>请注意,本连接字符串不支持office2010,如需支持,请自行更改连接字符串格式</para>
        /// </summary>
        /// <param name="pathString">请提供一个Excel文件路径,无论是已创建的或者是未创建的</param>
        public ExcelHelper(string pathString)
        {
            this.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + 
                pathString + 
                    ";Extended Properties=Excel 8.0;Persist Security Info=False";
            odc = new OleDbConnection(ConnectionString);
        }
 
        private readonly string ConnectionString;//连接字符串
        public OleDbConnection odc;//连接对象
 
        /// <summary>
        /// 返回一个Excel文档中的所有数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></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></returns>
        public DataTable ReadExcel()
        {
            odc.Open();
            DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
                new object[] { null, null, null, "TABLE" });
            odc.Close();
            return dt;
        }
 
        /// <summary>
        /// 打开一个已存在的Excel文档,并写入数据集
        /// <para>请注意,Excel文档内的多个Sheet有可能被覆盖</para>
        /// <para>原因:Sheet名相同</para>
        /// </summary>
        /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
        /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
        /// <returns></returns>
        public bool WriteExcel(DataSet ds, string pathString)
        {
            if (!File.Exists(pathString))
            {
                FileStream fs = File.Create(pathString);
                fs.Dispose();
                fs.Close();
            }
            bool result = true;
            Microsoft.Office.Interop.Excel.Application excel = 
                new Microsoft.Office.Interop.Excel.Application();//创建Excel操作对象
            excel.Workbooks.Open(pathString);
            try
            {
                excel.SheetsInNewWorkbook = ds.Tables.Count;//创建sheet的数量
                excel.Workbooks.Add();//添加sheet
                for (int number = 0; number < ds.Tables.Count; number++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet sheet = 
                        excel.ActiveWorkbook.Worksheets[number + 1] as 
                            Microsoft.Office.Interop.Excel.Worksheet;//获取sheet;
                    DataTable dt = ds.Tables[number];//获取表
                    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.Color = 0;//字体颜色
                    range.Interior.ColorIndex = 15;
                    range.ColumnWidth = 15;//列宽
                    range.Borders.LineStyle = 
                        Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//边框样式
                    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 = true;//显示预览
                System.Threading.Thread.Sleep(5000);
                excel.ActiveWorkbook.Save();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            finally
            {
                excel.ActiveWorkbook.Close();//关闭Excel对象
                excel.Quit();//退出
            }
            return result;
        }
 
        /// <summary>
        /// 创建一个新的Excel文件,并写入数据集
        /// <para>请注意,Excel文件可能创建失败</para>
        /// <para>原因:指定Excel文件已存在</para>
        /// </summary>
        /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
        /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
        /// <returns></returns>
        public bool CreateExcel(DataSet ds, string pathString)
        {
            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();
                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];//获取表
                    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 = true;//设置为预览
                System.Threading.Thread.Sleep(5000);//线程延迟5秒再预览
                excel.ActiveWorkbook.SaveAs(pathString, 
                    Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);//另存为
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                result = false;
            }
            finally
            {
                excel.ActiveWorkbook.Close();//关闭Excel对象
                excel.Quit();//退出
            }
            return result;
        }
    }
}

应用示例:

 

using System.Collections.Generic;
using System.Data;
using Model;

namespace DAL
{
    public class StudentExcelServices
    {
        public StudentExcelServices(string pathString)
        {
            this.pathString = pathString;
            eh = new ExcelHelper(pathString);
        }

        private ExcelHelper eh;
        private string pathString;

        //填充一个数据集
        public void ReadExcel(string sheetName,out DataSet ds)
        {
            string sql = string.Format(
              "select * from [{0}] where [编号] is not null",
                  sheetName);
            ds = eh.ReadExcel(sql);
            return ds;
        }

        //读取所有学生信息
        public List<Student> ReadExcel()
        {
            List<Student> studentList = new List<Student>();
            Microsoft.Office.Interop.Excel.Application excel = 
                new Microsoft.Office.Interop.Excel.Application();
            excel.Workbooks.Open(pathString);
            Microsoft.Office.Interop.Excel.Workbook work = 
                excel.Workbooks[1] as Microsoft.Office.Interop.Excel.Workbook;
            //省略封装的代码
            return studentList;
        }

        //打开Excel并且写入数据判断是否成功
        public bool WriteExcel(DataSet ds)
        {
            bool result = eh.WriteExcel(ds, pathString);
            return result;
        }

        //创建Excel并且写入数据判断是否成功
        public bool CreateExcel(DataSet ds)
        {
            bool result = eh.CreateExcel(ds, pathString);
            return result;
        }

        //读取所有班级名称
        public List<string> ReadGradeList(string sheetName)
        {
            List<string> gradeList = new List<string>();
            string sql = string.Format(
              "select [班级] from [{0}] where [班级] is not null group by [班级]",
                  sheetName);
            DataSet ds = eh.ReadExcel(sql);
            foreach (DataTable item in ds.Tables)
            {
                foreach (DataRow row in item.Rows)
                {
                    gradeList.Add(row[0].ToString());
                }
            }
            return gradeList;
        }

        //读取所有Sheet档名称
        public List<string> ReadSheetNameList()
        {
            List<string> sheetNameList = new List<string>();
            DataTable dt = eh.ReadExcel();
            foreach (DataRow item in dt.Rows)
            {
                sheetNameList.Add(item["TABLE_NAME"].ToString());
            }
            return sheetNameList;
        }
    }
}

//这是实体类
using System;

namespace Model
{
    //学生类
    [Serializable]
    public class Student
    {
        public int StudentNo { get; set; }//编号/帐号
        public string LoginPwd { get; set; }//密码
        public string StudentName { get; set; }//姓名
        public string Gender{get;set;}//性别
        public Grade GradeName { get; set; }//班级
        public string Phone { get; set; }//联系电话
        public string Address { get; set; }//地址
        public DateTime BornDate { get; set; }//生日
        public string Email { get; set; }//邮箱
        public string IdentityCard { get; set; }//身份证号码
    }
}

转载的朋友,请不要删除以下行,对此,表示感谢!!!

原文链接:http://www.cnblogs.com/VAllen/articles/ExcelHelper_Peng.html