一，读取Excel文件内容：
1 //根据excle的路径把第一个sheel中的内容放入datatable
2 public static DataTable ReadExcelToTable()//excel存放的路径
3 {
4 //连接字符串
5 string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "E:/Peng.Tao/TEST/1.xlsx" + ";Extended Properties='Excel8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
6 //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
7 using (OleDbConnection conn = new OleDbConnection(connstring))
8 {
9 conn.Open();
10 DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
11 string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
12 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
13 //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串
14 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
15 DataSet set = new DataSet();
16 ada.Fill(set);
17 return set.Tables[0];
18 }
19 }
二，将数据导入至Excel:
1 protected void Excel1_Click(object sender, EventArgs e) {
2 //设置保存的文档类型
3 //Response.ContentType = "application/ms-word";//保存为Word类型
4 Response.ContentType = "application/ms-excel";//保存为Excel类型
5 //设置编码方式和内容保存的形式
6 Response.ContentEncoding = System.Text.Encoding.UTF8;
7 Response.Charset = "Excel文档";
8 //设置保存为的文件名
9 string dateStr = DateTime.Now.ToString("yyyyMMddHHmmss");
10 string fileName = System.Web.HttpUtility.UrlEncode("料号文件" + dateStr, System.Text.Encoding.UTF8);
11 Response.AppendHeader("content-disposition", "attachment;filename=\"" + fileName + ".xls\"");
12 //开始导出;
13 this.EnableViewState = false;
14 StringWriter oStringWriter = new StringWriter();
15 HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
16 GridView1.RenderControl(oHtmlTextWriter);
17 Response.Write(oStringWriter.ToString());
18 oStringWriter = new StringWriter();
19 oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
20 GridView1.RenderControl(oHtmlTextWriter);
21 Response.Write(oStringWriter.ToString());
22 Response.End();
23 }
24 //添加以下函数防止出现:“Gridview控件需在Runat=server中” 的异常
25 public override void VerifyRenderingInServerForm(Control control)
26 {
27 //base.VerifyRenderingInServerForm(control);
28 }
三，WinForm将DataGridView数据导出至EXCEL
1 private void button2_Click(object sender, EventArgs e)
2 {
3 ToExcel();
4 }
5 public void ToExcel()
6 {
7 try
8 {
9 //没有数据的话就不往下执行
10 if (dataGridView1.Rows.Count == 0)
11 return;
12 //实例化一个Excel.Application对象
13 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
14 //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
15 excel.Visible = true;
16 //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
17 excel.Application.Workbooks.Add(true);
18 //生成Excel中列头名称
19 for (int i = 0; i < dataGridView1.Columns.Count; i++)
20 {
21 if (this.dataGridView1.Columns[i].Visible == true)
22 {
23 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
24 }
25
26 }
27 //把DataGridView当前页的数据保存在Excel中
28 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
29 {
30 System.Windows.Forms.Application.DoEvents();
31 for (int j = 0; j < dataGridView1.Columns.Count; j++)
32 {
33 if (this.dataGridView1.Columns[j].Visible == true)
34 {
35 if (dataGridView1[j, i].ValueType == typeof(string))
36 {
37 excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
38 }
39 else
40 {
41 excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
42 }
43 }
44
45 }
46 }
47 //设置禁止弹出保存和覆盖的询问提示框
48 excel.DisplayAlerts = false;
49 excel.AlertBeforeOverwriting = false;
50 //保存工作簿
51 excel.Application.Workbooks.Add(true).Save();
52 //保存excel文件
53 excel.Save("E:\\Peng.Tao\\Winform\\MyFirstWinform" + "\\Data.xls");
54 //确保Excel进程关闭
55 excel.Quit();
56 excel = null;
57 GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出，使用后正常退出
58 MessageBox.Show(this, "文件已经成功导出！", "信息提示");
59 }
60 catch (Exception ex)
61 {
62 MessageBox.Show(ex.Message, "错误提示");
63 }
64 }
