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

最新推荐文章于 2023-04-09 12:00:52 发布

szt007

最新推荐文章于 2023-04-09 12:00:52 发布

阅读量686

点赞数

文章标签： excel winform c# dataset null button

本文链接：https://blog.csdn.net/szt007/article/details/6163123

版权

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 2
0:     {
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: }