一，读取Excel文件内容：
1 1 //根据excle的路径把第一个sheel中的内容放入datatable 2 2 public static DataTable ReadExcelToTable()//excel存放的路径 3 3 { 4 4 //连接字符串 5 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 6 //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 7 7 using (OleDbConnection conn = new OleDbConnection(connstring)) 8 8 { 9 9 conn.Open(); 1010 DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字 1111 string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字 1212 string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 1313 //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串 1414 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring); 1515 DataSet set = new DataSet(); 1616 ada.Fill(set); 1717 return set.Tables[0]; 1818 } 1919 }
二，将数据导入至Excel:
1 1 protected void Excel1_Click(object sender, EventArgs e) { 2 2 //设置保存的文档类型 3 3 //Response.ContentType = "application/ms-word";//保存为Word类型 4 4 Response.ContentType = "application/ms-excel";//保存为Excel类型 5 5 //设置编码方式和内容保存的形式 6 6 Response.ContentEncoding = System.Text.Encoding.UTF8; 7 7 Response.Charset = "Excel文档"; 8 8 //设置保存为的文件名 9 9 string dateStr = DateTime.Now.ToString("yyyyMMddHHmmss"); 1010 string fileName = System.Web.HttpUtility.UrlEncode("料号文件" + dateStr, System.Text.Encoding.UTF8); 1111 Response.AppendHeader("content-disposition", "attachment;filename=\"" + fileName + ".xls\""); 1212 //开始导出; 1313 this.EnableViewState = false; 1414 StringWriter oStringWriter = new StringWriter(); 1515 HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter); 1616 GridView1.RenderControl(oHtmlTextWriter); 1717 Response.Write(oStringWriter.ToString()); 1818 oStringWriter = new StringWriter(); 1919 oHtmlTextWriter = new HtmlTextWriter(oStringWriter); 2020 GridView1.RenderControl(oHtmlTextWriter); 2121 Response.Write(oStringWriter.ToString()); 2222 Response.End(); 2323 } 2424 //添加以下函数防止出现:“Gridview控件需在Runat=server中” 的异常 2525 public override void VerifyRenderingInServerForm(Control control) 2626 { 2727 //base.VerifyRenderingInServerForm(control); 2828 }
三，WinForm将DataGridView数据导出至EXCEL
1 1 private void button2_Click(object sender, EventArgs e) 2 2 { 3 3 ToExcel(); 4 4 } 5 5 public void ToExcel() 6 6 { 7 7 try 8 8 { 9 9 //没有数据的话就不往下执行 1010 if (dataGridView1.Rows.Count == 0) 1111 return; 1212 //实例化一个Excel.Application对象 1313 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); 1414 //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 1515 excel.Visible = true; 1616 //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错 1717 excel.Application.Workbooks.Add(true); 1818 //生成Excel中列头名称 1919 for (int i = 0; i < dataGridView1.Columns.Count; i++) 2020 { 2121 if (this.dataGridView1.Columns[i].Visible == true) 2222 { 2323 excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; 2424 } 2525 2626 } 2727 //把DataGridView当前页的数据保存在Excel中 2828 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) 2929 { 3030 System.Windows.Forms.Application.DoEvents(); 3131 for (int j = 0; j < dataGridView1.Columns.Count; j++) 3232 { 3333 if (this.dataGridView1.Columns[j].Visible == true) 3434 { 3535 if (dataGridView1[j, i].ValueType == typeof(string)) 3636 { 3737 excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); 3838 } 3939 else 4040 { 4141 excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); 4242 } 4343 } 4444 4545 } 4646 } 4747 //设置禁止弹出保存和覆盖的询问提示框 4848 excel.DisplayAlerts = false; 4949 excel.AlertBeforeOverwriting = false; 5050 //保存工作簿 5151 excel.Application.Workbooks.Add(true).Save(); 5252 //保存excel文件 5353 excel.Save("E:\\Peng.Tao\\Winform\\MyFirstWinform" + "\\Data.xls"); 5454 //确保Excel进程关闭 5555 excel.Quit(); 5656 excel = null; 5757 GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出，使用后正常退出 5858 MessageBox.Show(this, "文件已经成功导出！", "信息提示"); 5959 } 6060 catch (Exception ex) 6161 { 6262 MessageBox.Show(ex.Message, "错误提示"); 6363 } 6464 }