C#操作Excel知识点

近期在使用C#操作excel，主要是读取excel模板，复制其中的模板sheet页，生成多个sheet页填充相应数据后另存到excel文件，所用到的知识点如下。

　　一、添加引用和命名空间

　　添加Microsoft.Office.Interop.Excel引用，它的默认路径是C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll

　　代码中添加引用using Microsoft.Office.Interop.Excel;

　　二、Excel类的简单介绍

　　此命名空间下关于Excel类的结构分别为：

　　ApplicationClass -就是我们的excel应用程序。

　　Workbook -就是我们平常见的一个个excel文件，经常是使用Workbooks类对其进行操作。

　　Worksheet -就是excel文件中的一个个sheet页。

　　Worksheet.Cells[row, column] -就是某行某列的单元格，注意这里的下标row和column都是从1开始的，跟我平常用的数组或集合的下标有所不同。

　　知道了上述基本知识后，利用此类来操作excel就清晰了很多。

　　三、Excel的操作

　　任何操作Excel的动作首先肯定是用excel应用程序，首先要new一个ApplicationClass实例，并在最后将此实例释放。

　　ApplicationClass xlsApp = new ApplicationClass();  // 1.创建Excel应用程序对象的一个实例，相当于我们从开始菜单打开Excel应用程序。

　　if (xlsApp == null)

　　{

　　//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装Excel

　　}

　　1.打开现有的Excel文件

　　Workbook workbook = xlsApp.Workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

　　Worksheet mySheet = workbook.Sheets[1] as Worksheet; //第一个sheet页

　　mySheet.Name = "testsheet";  //这里修改sheet名称

　　2.复制sheet页

　　mySheet.Copy(Type.Missing, workbook.Sheets[1]); //复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个

　　注意 这里Copy方法的两个参数，指是的复制出来新的sheet页是在指定sheet页的前面还是后面，上面的例子就是指复制的sheet页在第一个sheet页的后面。

　　3.删除sheet页

　　xlsApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。

　　(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();

　　4.选中sheet页

　　(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //选中某个sheet页

　　5.另存excel文件

　　workbook.Saved = true;

　　workbook.SaveCopyAs(filepath);

　　6.释放excel资源

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

　　workbook = null;

　　xlsApp.Quit();

　　xlsApp = null;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

添加的文件头:

    using System.Reflection; // 引用这个才能使用Missing字段

    using Excel;

 

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

              excel.Visible = true;       //激活Excel

           Workbook wBook   = excel.Workbooks.Add(true);

      //     Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet;

           Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;     

 

 

                   excel.Cells[3, 5] = "本公司电话: " + Phone;

                   excel.Cells[4, 5] = "本公司传真: " + Zhen;

                   excel.Cells[5, 5] = "联系人: " + ComName;

                   excel.Cells[4, 1] = "客户: " + CustomerName;

                   excel.Cells[5, 1] = "联系人: " + Associate;

                   excel.Cells[3, 8] = "户名:";

                   excel.Cells[3, 9] = AccountName;

                   excel.Cells[4, 8] = "开户行:";

                   excel.Cells[4, 9] = BranchName;

                   excel.Cells[5, 8] = "帐号:";

                   excel.Cells[5, 9] = "'" + AccountID;

 

 

                      //设置禁止弹出保存和覆盖的询问提示框

           excel.DisplayAlerts = false;

           excel.AlertBeforeOverwriting = false;

           //保存工作薄

         //  wBook.Save();

           //每次保存激活的表，这样才能多次操作保存不同的Excel表，默认保存位置是在”我的文档"

           

           excel.Cells.Font.Size = 12;

           excel.Cells.Font.Bold = false;

         //  Excel.Range m_objRange = m_objRange.get_Range(1, 3);

           wSheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Size = 24;

           wSheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Bold = true;

           wSheet.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).Font.ColorIndex = 3;//此处设为红色，不能用Font.Color来设置颜色

         //  m_objRange.Cells.Font.Size = 24;

         //  m_objRange.Cells.Font.Bold = true;

 

           excel.ActiveWorkbook.SaveCopyAs(filename);

 

 

        excel.Quit();

     代码注释部分只是简单描述各语句的原由，个别的还是值得推敲的。

 

     语句一 Workbook wBook   = excel.Workbooks.Add(true);

Workbooks.Add的参数是个object类型，通常使用true或null,表明工作簿在默认文档下创建，或者使用枚举值

     XlWBATemplate.xlWBATWorksheet,但如果传入一个excel完整文件名，却相当于打开已有工作簿。

 

     语句二 Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;    这样可以操作多个工作表的话，实例化之后加入到wBook.Worksheets中去。如果是打开已存在的工作簿，这条语句也可能会报错，最好是调用wBook.ActiveSheet来获取或者再加些判断。

 

     语句三

      excel.ActiveWorkbook.SaveCopyAs(filename);这两句代码至关重要，而且必不可少，否则，保存时会弹出“是否保存sheet1.xls”的对话框。判断当前激活的表，并保存这个表。

 

     语句四 excel.Quit();

     这个关闭一直有疑点，因为C#操作com非托管对象时，凭借Quit()还没有释放掉对象，excel进程不一定会终止,于是,有人使用KillProcess()来处理,我个人认为这不是一个好主意,可能会破坏其它正在执行的excel进程。目前我使用app

     =

     null;权作安慰吧。不过有一点是一定要做到，就是在Quit()前不能再有任何更改，不然还是会弹出保存的对话框。所以退出前确保一定是执行过WorkBook或是Application的Save()方法的。

 

 

 

 

 

VS2005[C#] 操作 Excel 全攻略

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;
namespace ExcelPrj
{
    /// <summary>
    /// Excel 系统中的主文件Excel.exe本身就是 COM 组件,通过在.NET项目中引用Exel.exe 文件可以实现对Excel 的功能控制
    /// 与COM组件相互操作是通过使用"包装类"(Wrapper Class)和"代理"(Proxy) 的机制实现的.包装类使.NET程序可以识别COM 组件提供的接口,而代理类则是提供对 COM接口的访问
    /// </summary>
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button3_Click(object sender, EventArgs e)
        {
            ExportTasks(Bind(), dataGridView1);
        }
        //如果 Excel 安装在计算机上,侧导出表格内容到 Excel
        public void ExportTasks(DataSet TasksData, DataGridView TasksGridView)
        {
            // 定义要使用的Excel 组件接口
            // 定义Application对象,此对象表示整个Excel 程序
            Microsoft.Office.Interop.Excel.Application excelApp = null ;
            // 定义Workbook对象,此对象代表工作薄
            Microsoft.Office.Interop.Excel.Workbook workBook;
            // 定义Worksheet对象,此对象表示Execel 中的一张工作表
            Microsoft.Office.Interop.Excel.Worksheet ws=null;
            //定义Range对象,此对象代表单元格区域
            Microsoft.Office.Interop.Excel.Range r;
            int row = 1; int cell = 1;
            try
            {
              //初始化 Application对象 excelApp
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                //在工作薄的第一个工作表上创建任务列表
                workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                ws =(Worksheet)workBook.Worksheets[1];
                // 命名工作表的名称为 "Task Management"
                ws.Name = "Task Management";
                #region 创建表格的列头
                // 遍历数据表中的所有列
                foreach (DataGridViewColumn cs in TasksGridView.Columns)
                {
                    // 假如并不想把主键也显示出来
                    if (cs.HeaderText != "编号")
                    {
                        ws.Cells[row, cell] = cs.HeaderText;
                        r = (Range)ws.Cells[row, cell];
                        ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
             
                        //此处用来设置列的样式
                        cell++;
                    }
                }
           
                // 创建行,把数据视图记录输出到对应的Excel单元格
                for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++)
                {
                    for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++)
                    {
                       
                        ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString();
                      // r = (Range)ws.Cells[i,j];
                        Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);
                        rg.EntireColumn.ColumnWidth = 20;
                  //    rg.Columns.AutoFit();
                        rg.NumberFormatLocal = "@";
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
           
            //显示 Excel
            excelApp.Visible = true;         
           
        }
        private void button5_Click(object sender, EventArgs e)
        {
            DataSet ds = Bind();
            dataGridView1.DataSource = ds.Tables[0];
        }
        private DataSet Bind()
        {
            SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");
            SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "请选择将导出的EXCEL文件存放路径";
            sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";
            sfd.Filter = "Excel文档(*.xls)|*.xls";
            sfd.ShowDialog();
         
            if (sfd.FileName != "")
            {
               
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                if (excelApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                }
                else
                {
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1];
                    DataSet ds=Bind();
                    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                    {
                        for (int j = 1; j < ds.Tables[0].Columns.Count;j++ )
                        {
                            if (i == 1)
                            {
                                worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;
                               
                            }
                            worksheet.Cells[i+1, j] = ds.Tables[0].Rows[i][j].ToString();
                        }
                    }
                    //保存方式一：保存WorkBook
                      //workbook.SaveAs(@"F:\CData.xls",
                      // Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                      // Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
                      // Missing.Value,Missing.Value);
                      //保存方式二：保存WorkSheet
                      // worksheet.SaveAs(@"F:\CData2.xls",
                      // Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                      // Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    ////保存方式三
                    //workbook.Saved = true;
                    //workbook.SaveCopyAs(sfd.FileName);
               
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    workbook = null;
                    workbooks.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    workbooks = null;
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                    MessageBox.Show("导出Excel完成!");
                }
            }
         
        }
        private void button4_Click(object sender, EventArgs e)
        {
            string strExcelFileName = @"F:\\2007-07-16-学生信息.xls";
            string strSheetName = "sheet1";
            #region Aspnet 操作Excel  正确
            ////源的定义
            //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";
            ////Sql语句
            //string strExcel = "select * from  [" + strSheetName + "$]";
            ////定义存放的数据表
            //DataSet ds = new DataSet();
            ////连接数据源
            //OleDbConnection conn = new OleDbConnection(strConn);
            //conn.Open();
            ////适配到数据源
            //OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
            //adapter.Fill(ds,"res");
            //conn.Close();
            //// 一般的情况下. Excel 表格的第一行是列名
            //dataGridView2.DataSource = ds.Tables["res"];
            #endregion
            #region COM 组件读取复杂Excel
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            Microsoft.Office.Interop.Excel.Workbook workBook;
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            try
            {
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                workBook = excelApp.Workbooks.Open(@"F:\\Book1.xls", 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);
                ws = (Worksheet)workBook.Worksheets[1];
                       
                //Excel 默认为 256 列..
                MessageBox.Show(ws.Cells.Columns.Count.ToString());
                excelApp.Quit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            #endregion
        }
    }
}

C#操作Excel多个sheet的具体的操作

 

1. private void DataViewExcelBySheetMultipleDt(  

2. DataSet ds, string fileName)     

3. {     

4. try    

5. {     

6. int sheetCount = ds.Tables.Count;     

7. GC.Collect();     

8. Application excel;     

9. _Workbook xBk;     

10._Worksheet xSt = null;     

11.excel = new ApplicationClass();     

12.xBk = excel.Workbooks.Add(true);     

13.    

14.int rowIndex = 0;     

15.int colIndex = 0;     

16.for (int sheetIndex = 0;   

17.sheetIndex < sheetCount; sheetIndex++)     

18.{     

19.rowIndex = 1;     

20.colIndex = 1;     

21.xSt = (_Worksheet)xBk.Worksheets.Add(  

22.Type.Missing, Type.Missing, 1, Type.Missing);     

23.switch (sheetIndex)     

24.{     

25.case 0:     

26.xSt.Name = "test1";     

27.break;     

28.case 1:     

29.xSt.Name = "test2";     

30.break;     

31.case 2:     

32.xSt.Name = "test3";     

33.break;     

34.case 3:     

35.xSt.Name = "test4";     

36.break;     

37.}     //C# 操作Excel多个sheet的具体的操作

38.foreach (DataColumn col in ds.Tables[sheetIndex].Columns)     

39.{     

40.xSt.get_Range(excel.Cells[rowIndex,   

41.colIndex], excel.Cells[rowIndex,   

42.colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;     

43.xSt.get_Range(excel.Cells[rowIndex,   

44.colIndex], excel.Cells[rowIndex,   

45.colIndex]).Font.Bold = true;     

46.excel.Cells[rowIndex, colIndex++] = col.ColumnName;     

47.}     

48.foreach (DataRow row in ds.  

49.Tables[sheetIndex].Rows)     

50.{     

51.rowIndex++;     

52.colIndex = 1;     

53.foreach (DataColumn col in ds.  

54.Tables[sheetIndex].Columns)     

55.{     

56.if (col.DataType == System.Type.GetType(  

57."System.DateTime"))     

58.{     

59.if (!"".Equals(row[col.ColumnName].ToString()))     

60.excel.Cells[rowIndex, colIndex] =   

61.(Convert.ToDateTime(row[col.ColumnName].  

62.ToString())).ToString("MM/dd/yyyy");     

63.else    

64.excel.Cells[rowIndex, colIndex] = "";     

65.}     

66.else if (col.DataType == S  

67.ystem.Type.GetType("System.String"))     

68.{     

69.excel.Cells[rowIndex, colIndex] = "'" +   

70.row[col.ColumnName].ToString();     

71.}     

72.else    

73.{     

74.excel.Cells[rowIndex, colIndex] =   

75.row[col.ColumnName].ToString();     

76.}     

77.colIndex++;     

78.}     //C# 操作Excel多个sheet的具体的操作

79.}     

80.Range allDataWithTitleRange = xSt.get_Range(  

81.excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);     

82.allDataWithTitleRange.Select();     

83.allDataWithTitleRange.Columns.AutoFit();     

84.allDataWithTitleRange.Borders.LineStyle = 1;     

85.}     

86.string exportDir = "~/Attachment/";     

87.string absFileName = HttpContext.  

88.Current.Server.MapPath(  

89.System.IO.Path.Combine(exportDir, fileName));     

90.xBk.SaveCopyAs(absFileName);     

91.xBk.Close(false, null, null);     

92.excel.Quit();     

93.    

94.System.Runtime.InteropServices.  

95.Marshal.ReleaseComObject(xBk);     

96.System.Runtime.InteropServices.  

97.Marshal.ReleaseComObject(excel);     

98.System.Runtime.InteropServices.  

99.Marshal.ReleaseComObject(xSt);     

100.     //C# 操作Excel多个sheet的具体的操作

101. xBk = null;     

102. excel = null;     

103. xSt = null;     

104. GC.Collect();     

105. }     

106. catch (Exception ex)     

107. {     

108.     

109. }     

110. }    

111. private void DataViewExcelBySheetMultipleDt(  

112. DataSet ds, string fileName)  

113. {  

114. try 

115. {  

116. int sheetCount = ds.Tables.Count;  

117. GC.Collect();  

118. Application excel;  

119. _Workbook xBk;  

120. _Worksheet xSt = null;  

121. excel = new ApplicationClass();  

122. xBk = excel.Workbooks.Add(true);  

123.  //C#操作Excel多个sheet的具体的操作

124. int rowIndex = 0;  

125. int colIndex = 0;  

126. for (int sheetIndex = 0;  

127.  sheetIndex < sheetCount; sheetIndex++)  

128. {  

129. rowIndex = 1;  

130. colIndex = 1;  

131. xSt = (_Worksheet)xBk.Worksheets.Add(  

132. Type.Missing, Type.Missing, 1, Type.Missing);  

133. switch (sheetIndex)  

134. {  

135. case 0:  

136. xSt.Name = "test1";  

137. break;  

138. case 1:  

139. xSt.Name = "test2";  

140. break;  

141. case 2:  

142. xSt.Name = "test3";  

143. break;  

144. case 3:  

145. xSt.Name = "test4";  

146. break;  

147. }  

148. foreach (DataColumn col in ds.Tables[sheetIndex].Columns)  

149. {  //C# 操作Excel多个sheet的具体的操作

150. xSt.get_Range(excel.Cells[rowIndex,   

151. colIndex], excel.Cells[rowIndex,   

152. colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;  

153. xSt.get_Range(excel.Cells[rowIndex,   

154. colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;  

155. excel.Cells[rowIndex, colIndex++] = col.ColumnName;  

156. }  

157. foreach (DataRow row in ds.Tables[sheetIndex].Rows)  

158. {  

159. rowIndex++;  

160. colIndex = 1;  

161. foreach (DataColumn col in ds.Tables[  

162. sheetIndex].Columns)  

163. {  

164. if (col.DataType == System.Type.GetType(  

165. "System.DateTime"))  

166. {  

167. if (!"".Equals(row[col.ColumnName].ToString()))  

168. excel.Cells[rowIndex, colIndex] = (  

169. Convert.ToDateTime(row[col.ColumnName].  

170. ToString())).ToString("MM/dd/yyyy");  

171. else 

172. excel.Cells[rowIndex, colIndex] = "";  

173. }  

174. else if (col.DataType ==   

175. System.Type.GetType("System.String"))  

176. {  

177. excel.Cells[rowIndex,   

178. colIndex] = "'" + row[col.ColumnName].ToString();  

179. }  

180. else 

181. {  

182. excel.Cells[rowIndex,  

183.  colIndex] = row[col.ColumnName].ToString();  

184. }  

185. colIndex++;  

186. }  

187. }  //C# 操作Excel多个sheet的具体的操作

188. Range allDataWithTitleRange = xSt.get_Range(  

189. excel.Cells[1, 1],   

190. excel.Cells[rowIndex, colIndex - 1]);  

191. allDataWithTitleRange.Select();  

192. allDataWithTitleRange.Columns.AutoFit();  

193. allDataWithTitleRange.Borders.LineStyle = 1;  

194. }  

195. string exportDir = "~/Attachment/";  

196. string absFileName = HttpContext.Current.Server.  

197. MapPath(System.IO.Path.Combine(exportDir, fileName));  

198. xBk.SaveCopyAs(absFileName);  

199. xBk.Close(false, null, null);  

200. excel.Quit();  

201.  

202. System.Runtime.InteropServices.  

203. Marshal.ReleaseComObject(xBk);  

204. System.Runtime.InteropServices.  

205. Marshal.ReleaseComObject(excel);  

206. System.Runtime.InteropServices.  

207. Marshal.ReleaseComObject(xSt);  

208.  

209. xBk = null;  

210. excel = null;  

211. xSt = null;  

212. GC.Collect();  

213. }  

214. catch (Exception ex)  

215. {  

216.  

217. }  

218. }  

上面方法,首先形成一个多个DataTable的DataSet,

C# 操作Excel重点还是

1. 生成一个新的xls时,打开方式,总是会提示进程占用

2. 用不同的sheet时一定要命名

3. 使用传入一个datatable时,总是会重写第一个sheet