Fork me on GitHub

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

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

 

  1 using System;
  2 using System.Data;
  3 using System.Data.OleDb;
  4 using System.IO;
  5  
  6 namespace DAL
  7 {
  8     /// <summary>
  9     /// Peng制作
 10     /// <para>QQ:378781081</para>
 11     /// </summary>
 12     /// <see cref="http://www.cnblogs.com/VAllen"/>
 13     public class ExcelHelper
 14     {
 15         /// <summary>
 16         /// 初始化一个Excel操作实例
 17         /// <para>请注意,本连接字符串不支持office2010,如需支持,请自行更改连接字符串格式</para>
 18         /// </summary>
 19         /// <param name="pathString">请提供一个Excel文件路径,无论是已创建的或者是未创建的</param>
 20         public ExcelHelper(string pathString)
 21         {
 22             this.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + 
 23                 pathString + 
 24                     ";Extended Properties=Excel 8.0;Persist Security Info=False";
 25             odc = new OleDbConnection(ConnectionString);
 26         }
 27  
 28         private readonly string ConnectionString;//连接字符串
 29         public OleDbConnection odc;//连接对象
 30  
 31         /// <summary>
 32         /// 返回一个Excel文档中的所有数据
 33         /// </summary>
 34         /// <param name="sql">SQL语句</param>
 35         /// <returns></returns>
 36         public DataSet ReadExcel(string sql)
 37         {
 38             DataSet ds = new DataSet();
 39             odc.Open();
 40             OleDbDataAdapter oda = new OleDbDataAdapter(sql, odc);
 41             oda.Fill(ds);
 42             odc.Close();
 43             return ds;
 44         }
 45  
 46         /// <summary>
 47         /// 返回一个Excel文档中第一个Sheet档的Sheet数据
 48         /// </summary>
 49         /// <returns></returns>
 50         public DataTable ReadExcel()
 51         {
 52             odc.Open();
 53             DataTable dt = odc.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
 54                 new object[] { null, null, null, "TABLE" });
 55             odc.Close();
 56             return dt;
 57         }
 58  
 59         /// <summary>
 60         /// 打开一个已存在的Excel文档,并写入数据集
 61         /// <para>请注意,Excel文档内的多个Sheet有可能被覆盖</para>
 62         /// <para>原因:Sheet名相同</para>
 63         /// </summary>
 64         /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
 65         /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
 66         /// <returns></returns>
 67         public bool WriteExcel(DataSet ds, string pathString)
 68         {
 69             if (!File.Exists(pathString))
 70             {
 71                 FileStream fs = File.Create(pathString);
 72                 fs.Dispose();
 73                 fs.Close();
 74             }
 75             bool result = true;
 76             Microsoft.Office.Interop.Excel.Application excel = 
 77                 new Microsoft.Office.Interop.Excel.Application();//创建Excel操作对象
 78             excel.Workbooks.Open(pathString);
 79             try
 80             {
 81                 excel.SheetsInNewWorkbook = ds.Tables.Count;//创建sheet的数量
 82                 excel.Workbooks.Add();//添加sheet
 83                 for (int number = 0; number < ds.Tables.Count; number++)
 84                 {
 85                     Microsoft.Office.Interop.Excel.Worksheet sheet = 
 86                         excel.ActiveWorkbook.Worksheets[number + 1] as 
 87                             Microsoft.Office.Interop.Excel.Worksheet;//获取sheet;
 88                     DataTable dt = ds.Tables[number];//获取表
 89                     sheet.Name = dt.TableName;//设置sheet名
 90                     int i = 0;
 91                     for (; i < dt.Columns.Count; i++)//动态添加
 92                     {
 93                         sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//添加表头
 94                     }
 95                     Microsoft.Office.Interop.Excel.Range range = 
 96                         excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]);//编辑区域
 97                     range.Font.Bold = true;//字体加粗
 98                     range.Font.Color = 0;//字体颜色
 99                     range.Interior.ColorIndex = 15;
100                     range.ColumnWidth = 15;//列宽
101                     range.Borders.LineStyle = 
102                         Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//边框样式
103                     for (int y = 0; y < dt.Rows.Count; y++)//动态添加行数据
104                     {
105                         for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++)//动态添加列数据
106                         {
107                             sheet.Cells[y + 2, x + 1] = dt.Rows[y][x];//赋值
108                         }
109                     }
110                 }
111                 excel.Visible = true;//显示预览
112                 System.Threading.Thread.Sleep(5000);
113                 excel.ActiveWorkbook.Save();
114             }
115             catch (Exception ex)
116             {
117                 ex.Message.ToString();
118                 result = false;
119             }
120             finally
121             {
122                 excel.ActiveWorkbook.Close();//关闭Excel对象
123                 excel.Quit();//退出
124             }
125             return result;
126         }
127  
128         /// <summary>
129         /// 创建一个新的Excel文件,并写入数据集
130         /// <para>请注意,Excel文件可能创建失败</para>
131         /// <para>原因:指定Excel文件已存在</para>
132         /// </summary>
133         /// <param name="ds">数据集,至少包含一个或多个表(Sheet)</param>
134         /// <param name="pathString">请提供一个Excel文件路径,如果不存在则自动创建</param>
135         /// <returns></returns>
136         public bool CreateExcel(DataSet ds, string pathString)
137         {
138             bool result = true;
139             Microsoft.Office.Interop.Excel.Application excel = 
140                 new Microsoft.Office.Interop.Excel.Application();//create Excel manipulate objects
141             try
142             {
143                 excel.SheetsInNewWorkbook = ds.Tables.Count;//获取Sheet档数量
144                 excel.Workbooks.Add();
145                 for (int number = 0; number < ds.Tables.Count; number++)//循环添加Sheet档
146                 {
147                     Microsoft.Office.Interop.Excel.Worksheet sheet = 
148                         excel.ActiveWorkbook.Worksheets[number + 1] as 
149                             Microsoft.Office.Interop.Excel.Worksheet;//获取sheet;
150                     DataTable dt = ds.Tables[number];//获取表
151                     sheet.Name = dt.TableName;//设置Sheet档名
152                     int i = 0;
153                     for (; i < dt.Columns.Count; i++)//循环添加列头
154                     {
155                         sheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//设置列头名
156                     }
157                     Microsoft.Office.Interop.Excel.Range range = 
158                         excel.get_Range(excel.Cells[1, 1], excel.Cells[1, i]);//设置编辑区域
159                     range.Font.Bold = true;//字体加粗
160                     range.Font.ColorIndex = 0;//字体颜色
161                     range.Interior.ColorIndex = 15;//背景颜色
162                     range.ColumnWidth = 15;//列宽
163                     range.Borders.LineStyle = 
164                         Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//边框样式
165                     range.HorizontalAlignment = 
166                         Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//字体居中
167                     for (int y = 0; y < dt.Rows.Count; y++)//循环添加行
168                     {
169                         for (int x = 0; x < dt.Rows[y].Table.Columns.Count; x++)//循环添加列
170                         {
171                             sheet.Cells[y + 2, x + 1] = dt.Rows[y][x];//设置列值
172                         }
173                     }
174                 }
175                 excel.Visible = true;//设置为预览
176                 System.Threading.Thread.Sleep(5000);//线程延迟5秒再预览
177                 excel.ActiveWorkbook.SaveAs(pathString, 
178                     Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);//另存为
179             }
180             catch (Exception ex)
181             {
182                 ex.Message.ToString();
183                 result = false;
184             }
185             finally
186             {
187                 excel.ActiveWorkbook.Close();//关闭Excel对象
188                 excel.Quit();//退出
189             }
190             return result;
191         }
192     }
193 }

应用示例:

 

  1 using System.Collections.Generic;
  2 using System.Data;
  3 using Model;
  4 
  5 namespace DAL
  6 {
  7     public class StudentExcelServices
  8     {
  9         public StudentExcelServices(string pathString)
 10         {
 11             this.pathString = pathString;
 12             eh = new ExcelHelper(pathString);
 13         }
 14 
 15         private ExcelHelper eh;
 16         private string pathString;
 17 
 18         //填充一个数据集
 19         public void ReadExcel(string sheetName,out DataSet ds)
 20         {
 21             string sql = string.Format(
 22               "select * from [{0}] where [编号] is not null",
 23                   sheetName);
 24             ds = eh.ReadExcel(sql);
 25             return ds;
 26         }
 27 
 28         //读取所有学生信息
 29         public List<Student> ReadExcel()
 30         {
 31             List<Student> studentList = new List<Student>();
 32             Microsoft.Office.Interop.Excel.Application excel = 
 33                 new Microsoft.Office.Interop.Excel.Application();
 34             excel.Workbooks.Open(pathString);
 35             Microsoft.Office.Interop.Excel.Workbook work = 
 36                 excel.Workbooks[1] as Microsoft.Office.Interop.Excel.Workbook;
 37             //省略封装的代码
 38             return studentList;
 39         }
 40 
 41         //打开Excel并且写入数据判断是否成功
 42         public bool WriteExcel(DataSet ds)
 43         {
 44             bool result = eh.WriteExcel(ds, pathString);
 45             return result;
 46         }
 47 
 48         //创建Excel并且写入数据判断是否成功
 49         public bool CreateExcel(DataSet ds)
 50         {
 51             bool result = eh.CreateExcel(ds, pathString);
 52             return result;
 53         }
 54 
 55         //读取所有班级名称
 56         public List<string> ReadGradeList(string sheetName)
 57         {
 58             List<string> gradeList = new List<string>();
 59             string sql = string.Format(
 60               "select [班级] from [{0}] where [班级] is not null group by [班级]",
 61                   sheetName);
 62             DataSet ds = eh.ReadExcel(sql);
 63             foreach (DataTable item in ds.Tables)
 64             {
 65                 foreach (DataRow row in item.Rows)
 66                 {
 67                     gradeList.Add(row[0].ToString());
 68                 }
 69             }
 70             return gradeList;
 71         }
 72 
 73         //读取所有Sheet档名称
 74         public List<string> ReadSheetNameList()
 75         {
 76             List<string> sheetNameList = new List<string>();
 77             DataTable dt = eh.ReadExcel();
 78             foreach (DataRow item in dt.Rows)
 79             {
 80                 sheetNameList.Add(item["TABLE_NAME"].ToString());
 81             }
 82             return sheetNameList;
 83         }
 84     }
 85 }
 86 
 87 //这是实体类
 88 using System;
 89 
 90 namespace Model
 91 {
 92     //学生类
 93     [Serializable]
 94     public class Student
 95     {
 96         public int StudentNo { get; set; }//编号/帐号
 97         public string LoginPwd { get; set; }//密码
 98         public string StudentName { get; set; }//姓名
 99         public string Gender{get;set;}//性别
100         public Grade GradeName { get; set; }//班级
101         public string Phone { get; set; }//联系电话
102         public string Address { get; set; }//地址
103         public DateTime BornDate { get; set; }//生日
104         public string Email { get; set; }//邮箱
105         public string IdentityCard { get; set; }//身份证号码
106     }
107 }

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

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

posted @ 2012-07-08 19:03  VAllen  阅读(6795)  评论(11)  编辑  收藏  举报