[转]一个winform的Excel对数据的处理源代码(C#,C/S)

准备自己在研究,留着先,转的,多谢作者,忘了留地址了...

   1: using System;

   2: using System.Collections.Generic;

   3: using System.ComponentModel;

   4: using System.Data;

   5: using System.Drawing;

   6: using System.Text;

   7: using System.Windows.Forms;

   8: using Microsoft.Office.Interop.Excel;

   9: using System.Data.SqlClient;

  10: using System.Data.OleDb;

  11: using System.Reflection;

  12:  

  13: namespace ExcelPrj

  14: {

  15:     /// <summary>

  16:     /// Excel 系统中的主文件Excel.exe 本身就是 COM 组件,通过在.NET 项目中引用Exel.exe 文件可以实现对Excel 的功能控制

  17:     /// 与COM 组件相互操作是通过使用"包装类"(Wrapper Class) 和"代理"(Proxy) 的机制实现的.包装类使.NET 程序可以识别COM 组件提供的接口,而代理类则是提供对 COM 接口的访问

  18:     /// </summary>

  19:     public partial class Form1 : Form

  20:     {

  21:         public Form1()

  22:         {

  23:             InitializeComponent();

  24:         }

  25:         private void button3_Click(object sender, EventArgs e)

  26:         {

  27:             ExportTasks(Bind(), dataGridView1);

  28:         }

  29:         //如果 Excel 安装在计算机上,侧导出表格内容到 Excel

  30:         public void ExportTasks(DataSet TasksData, DataGridView TasksGridView)

  31:         {

  32:             // 定义要使用的Excel 组件接口

  33:             // 定义Application 对象,此对象表示整个Excel 程序

  34:             Microsoft.Office.Interop.Excel.Application excelApp = null;

  35:             // 定义Workbook对象,此对象代表工作薄

  36:             Microsoft.Office.Interop.Excel.Workbook workBook;

  37:             // 定义Worksheet 对象,此对象表示Execel 中的一张工作表

  38:             Microsoft.Office.Interop.Excel.Worksheet ws = null;

  39:             //定义Range对象,此对象代表单元格区域

  40:             Microsoft.Office.Interop.Excel.Range r;

  41:  

  42:             int row = 1; int cell = 1;

  43:             try

  44:             {

  45:                 //初始化 Application 对象 excelApp

  46:                 excelApp = new Microsoft.Office.Interop.Excel.Application();

  47:                 //在工作薄的第一个工作表上创建任务列表

  48:                 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

  49:  

  50:                 ws = (Worksheet)workBook.Worksheets[1];

  51:  

  52:                 // 命名工作表的名称为 "Task Management"

  53:                 ws.Name = "Task Management";

  54:  

  55:                 #region 创建表格的列头

  56:                 // 遍历数据表中的所有列

  57:                 foreach (DataGridViewColumn cs in TasksGridView.Columns)

  58:                 {

  59:                     // 假如并不想把主键也显示出来

  60:                     if (cs.HeaderText != "编号")

  61:                     {

  62:                         ws.Cells[row, cell] = cs.HeaderText;

  63:                         r = (Range)ws.Cells[row, cell];

  64:  

  65:                         ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

  66:  

  67:                         //此处用来设置列的样式

  68:                         cell++;

  69:                     }

  70:                 }

  71:  

  72:                 // 创建行,把数据视图记录输出到对应的Excel 单元格

  73:                 for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++)

  74:                 {

  75:                     for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++)

  76:                     {

  77:  

  78:                         ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString();

  79:                         // r = (Range)ws.Cells[i,j];

  80:  

  81:                         Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);

  82:                         rg.EntireColumn.ColumnWidth = 20;

  83:                         // rg.Columns.AutoFit();

  84:                         rg.NumberFormatLocal = "@";

  85:                     }

  86:  

  87:                 }

  88:                 #endregion

  89:             }

  90:             catch (Exception ex)

  91:             {

  92:                 MessageBox.Show(ex.ToString());

  93:             }

  94:  

  95:             //显示 Excel

  96:             excelApp.Visible = true;

  97:  

  98:         }

  99:  

 100:         private void button5_Click(object sender, EventArgs e)

 101:         {

 102:             DataSet ds = Bind();

 103:             dataGridView1.DataSource = ds.Tables[0];

 104:         }

 105:         private DataSet Bind()

 106:         {

 107:             SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");

 108:             SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);

 109:             DataSet ds = new DataSet();

 110:             da.Fill(ds);

 111:             return ds;

 112:         }

 113:         private void button2_Click(object sender, EventArgs e)

 114:         {

 115:             SaveFileDialog sfd = new SaveFileDialog();

 116:             sfd.Title = "请选择将导出的EXCEL文件存放路径";

 117:             sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";

 118:             sfd.Filter = "Excel文档(*.xls)|*.xls";

 119:             sfd.ShowDialog();

 120:  

 121:             if (sfd.FileName != "")

 122:             {

 123:  

 124:                 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

 125:                 if (excelApp == null)

 126:                 {

 127:                     MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");

 128:                 }

 129:                 else

 130:                 {

 131:                     Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;

 132:                     Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);

 133:                     Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

 134:                     DataSet ds = Bind();

 135:                     for (int i = 1; i < ds.Tables[0].Rows.Count; i++)

 136:                     {

 137:                         for (int j = 1; j < ds.Tables[0].Columns.Count; j++)

 138:                         {

 139:                             if (i == 1)

 140:                             {

 141:                                 worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;

 142:                             }

 143:                             worksheet.Cells[i + 1, j] = ds.Tables[0].Rows[i][j].ToString();

 144:                         }

 145:                     }

 146:                     //保存方式一：保存WorkBook

 147:                     //workbook.SaveAs(@"F:CData.xls",

 148:                     // Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,

 149:                     // Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,

 150:                     // Missing.Value,Missing.Value);

 151:  

 152:                     //保存方式二：保存WorkSheet

 153:                     // worksheet.SaveAs(@"F:CData2.xls",

 154:                     // Missing.Value, Missing.Value, Missing.Value, Missing.Value,

 155:                     // Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

 156:  

 157:                     保存方式三

 158:                     //workbook.Saved = true;

 159:                     //workbook.SaveCopyAs(sfd.FileName);

 160:  

 161:  

 162:                     System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);

 163:                     worksheet = null;

 164:                     System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);

 165:                     workbook = null;

 166:                     workbooks.Close();

 167:                     System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);

 168:                     workbooks = null;

 169:                     excelApp.Quit();

 170:                     System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);

 171:                     excelApp = null;

 172:                     MessageBox.Show("导出Excel完成!");

 173:                 }

 174:             }

 175:  

 176:  

 177:         }

 178:         private void button4_Click(object sender, EventArgs e)

 179:         {

 180:             string strExcelFileName = @"F:\2007-07-16-学生信息.xls";

 181:             string strSheetName = "sheet1";

 182:  

 183:             #region Aspnet 操作Excel 正确

 184:             源的定义

 185:             //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";

 186:  

 187:             Sql语句

 188:             //string strExcel = "select * from [" + strSheetName + "$]";

 189:  

 190:             定义存放的数据表

 191:             //DataSet ds = new DataSet();

 192:  

 193:             连接数据源

 194:             //OleDbConnection conn = new OleDbConnection(strConn);

 195:  

 196:             //conn.Open();

 197:  

 198:             适配到数据源

 199:             //OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);

 200:             //adapter.Fill(ds,"res");

 201:  

 202:             //conn.Close();

 203:  

 204:              一般的情况下. Excel 表格的第一行是列名

 205:             //dataGridView2.DataSource = ds.Tables["res"];

 206:             #endregion

 207:  

 208:             #region COM 组件读取复杂Excel

 209:             Microsoft.Office.Interop.Excel.Application excelApp = null;

 210:             Microsoft.Office.Interop.Excel.Workbook workBook;

 211:             Microsoft.Office.Interop.Excel.Worksheet ws = null;

 212:             try

 213:             {

 214:                 excelApp = new Microsoft.Office.Interop.Excel.Application();

 215:                 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);

 216:                 ws = (Worksheet)workBook.Worksheets[1];

 217:                 //Excel 默认为 256 列..

 218:                 MessageBox.Show(ws.Cells.Columns.Count.ToString());

 219:                 excelApp.Quit();

 220:             }

 221:             catch (Exception ex)

 222:             {

 223:                 throw ex;

 224:             }

 225:             #endregion

 226:         }

 227:     }

 228: }

转载于:https://www.cnblogs.com/zfanswer/archive/2009/03/30/1425216.html