.net数据库实现Excel的导入与导出
参考路径：https://www.cnblogs.com/splendidme/archive/2012/01/05/2313314.html
1.default.aspx文件
1 <form id="form1" runat="server"> 2 <table style="width: 858px"> 3 <tr> 4 <td style="width: 334px"> 5 <asp:Button ID="readFromDB" runat="server" 6 7 OnClick="readFromDB_Click" Text="从数据库读取数据" /> 8 <asp:GridView ID="GridView1" runat="server" 9 10 AutoGenerateColumns="True" BackColor="White" BorderColor="#E7E7FF" 11 12 BorderStyle="None" BorderWidth="1px" CellPadding="3" Font-Names="Arial" 13 14 Font-Size="12px" GridLines="Horizontal" RowStyle-HorizontalAlign="Center" 15 16 Width="98%"> 17 <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" /> 18 19 <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" 20 21 HorizontalAlign="Center" /> 22 <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" 23 24 ForeColor="#F7F7F7" /> 25 <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" 26 27 HorizontalAlign="Right" /> 28 <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" 29 30 ForeColor="#F7F7F7" HorizontalAlign="Center" /> 31 <AlternatingRowStyle BackColor="#F7F7F7" /> </asp:GridView> 32 <asp:FileUpload ID="FileUpload1" runat="server" /> 33 <asp:Button ID="Import" runat="server" OnClick="Import_Click" Text=" 34 35 导入" /> 36 <asp:Button ID="output" runat="server" OnClick="btnOut_Click" Text=" 37 38 导出" /> 39 </td> 40 </tr> 41 </table> 42 </form>
2.default.aspx.cs文件
1 using System; 2 using System.Data; 3 using System.Configuration; 4 using System.Collections; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 using System.Data.SqlClient; 12 using System.Data.OleDb; 13 using System.Text; 14 using JJoobb.Web; 15 using System.IO; 16 public partial class _Default : System.Web.UI.Page 17 { 18 string strConn = SiteSetting.ConnectionString; 19 string sqlSelectALL = SiteSetting.sqlSelALL; 20 string exToDB = SiteSetting.ExToDB; 21 protected void Page_Load(object sender, EventArgs e) 22 { 23 24 } 25 protected void readFromDB_Click(object sender, EventArgs e) 26 { 27 Bind(); 28 } 29 public void btnOut_Click(object sender, EventArgs e) 30 { 31 try 32 { 33 CreateExcel(getds()); 34 Response.Write("<script>alert('数据导出成功!')</script>"); 35 } 36 catch 37 { 38 Response.Write("<script>alert('数据导出失败!')</script>"); 39 } 40 } 41 public void CreateExcel(DataSet ds) 42 { 43 string outPutPath=SiteSetting.OutPutPath +DateTime.Now.ToString 44 45 ("yyyyMMddHHmmss-")+SiteSetting.FileName; 46 FileStream file = new FileStream(outPutPath, FileMode.Create); 47 StreamWriter sw = new StreamWriter(file,Encoding.Default);//设置编码为当 48 49 面页面编码 50 string colHeaders = "", ls_item = ""; 51 //定义表对象与行对象，同时用DataSet对其值进行初始化 52 DataTable dt = ds.Tables[0]; 53 DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数 54 55 据筛选目的 56 int i = 0; 57 int cl = dt.Columns.Count; 58 //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车 59 60 符 61 for (i = 0; i < cl; i++) 62 { 63 if (i == (cl - 1))//最后一列，加\n 64 { 65 colHeaders += dt.Columns[i].Caption.ToString() + "\n"; 66 } 67 else 68 { 69 colHeaders += dt.Columns[i].Caption.ToString() + "\t"; 70 } 71 } 72 sw.Write(colHeaders); 73 foreach (DataRow row in myRow) 74 { 75 //当前行数据写入输出流，并且置空ls_item以便下行数据 76 for (i = 0; i < cl; i++) 77 { 78 if (i == (cl - 1)) 79 { 80 ls_item += row[i].ToString() + "\n"; 81 } 82 else 83 { 84 ls_item += row[i].ToString() + "\t"; 85 } 86 } 87 sw.Write(ls_item); 88 ls_item = ""; 89 } 90 sw.Flush(); 91 sw.Close(); 92 file.Close(); 93 } 94 95 //从数据库取出要导出的Detset数据集 96 private DataSet getds() 97 { 98 SqlConnection conns = new SqlConnection(strConn); 99 SqlDataAdapter da = new SqlDataAdapter(sqlSelectALL, conns); 100 DataSet ds = new DataSet(); 101 da.Fill(ds); 102 conns.Close(); 103 conns.Dispose(); 104 return ds; 105 } 106 //绑定数据 107 public void Bind() 108 { 109 DataSet ds = new DataSet(); 110 using (SqlConnection conn = new SqlConnection()) 111 { 112 SqlDataAdapter sda = new SqlDataAdapter(sqlSelectALL, strConn); 113 sda.Fill(ds, "ex_test"); 114 } 115 GridView1.DataSource = ds.Tables["ex_test"]; 116 GridView1.DataBind(); 117 } 118 //导入数据 119 protected void Import_Click(object sender, EventArgs e) 120 { 121 string getErrMsg = ""; 122 DataSet excelDs = new DataSet(); 123 if (FileUpload1.PostedFile.FileName == "") 124 { 125 Response.Write("<script language=javascript>alert('请选择要上传的文件 126 127 ！');</script>"); 128 return; 129 } 130 //从Excel读取数据 131 string filePath = FileUpload1.PostedFile.FileName; 132 string connString = exToDB + filePath; 133 OleDbConnection excelConn = new OleDbConnection(connString); 134 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM 135 136 [Sheet1$]", excelConn); 137 try 138 { 139 ExcelDA.Fill(excelDs, "ex_test"); 140 } 141 catch (Exception err) 142 { 143 Response.Write(err.Message); 144 } 145 finally 146 { 147 excelConn.Close(); 148 excelConn = null; 149 } 150 //将数据写入数据库 151 if (excelDs.Tables[0].Rows.Count != 0) 152 { 153 SqlConnection sqlConn = new SqlConnection(strConn); 154 sqlConn.Open(); 155 SqlCommand myCommand = sqlConn.CreateCommand(); 156 SqlTransaction myTrans = sqlConn.BeginTransaction(); 157 myCommand.Transaction = myTrans; 158 try 159 { 160 for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++) 161 { 162 string sql = "insert into ex_test(id, name, sex,email,address) values('" 163 164 + excelDs.Tables[0].Rows[i]["编号"].ToString() + "','" + excelDs.Tables[0].Rows[i][" 165 166 姓名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["性别"].ToString() + "','" + 167 168 excelDs.Tables[0].Rows[i]["邮箱"].ToString() + "','" + excelDs.Tables[0].Rows[i]["地 169 170 址"].ToString() + "')"; 171 myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); 172 } 173 myTrans.Commit(); 174 } 175 catch (Exception ex) 176 { 177 getErrMsg = ex.Message.ToString(); 178 Response.Write(ex.Message.ToString()); 179 myTrans.Rollback(); 180 } 181 finally 182 { 183 sqlConn.Close(); 184 sqlConn = null; 185 } 186 187 } 188 //返回提示信息 189 if (getErrMsg == "" || getErrMsg == null) 190 { 191 Response.Write("<script language='Javascript'>alert('导入成功！') 192 193 </script>"); 194 Bind(); 195 } 196 else 197 { 198 Response.Write("<script language='Javascript'>alert('导入失败！') 199 200 </script>"); 201 return; 202 } 203 } 204 205 }
3.SiteSetting.cs
1 using System; 2 using System.Configuration; 3 using System.Collections.Generic; 4 using System.Text; 5 6 /// <summary> 7 /// SiteSetting 的摘要说明 8 /// </summary> 9 namespace JJoobb.Web 10 { 11 public class SiteSetting 12 { 13 //数据库连接字符串 14 public static string ConnectionString = 15 16 ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; 17 18 //导出文件名称 19 public static string FileName = "OutPut.xls"; 20 21 //导出文件地址 22 public static string OutPutPath = "C:\\Documents and 23 24 Settings\\Administrator\\桌面\\excleFile\\"; 25 26 //sql语句 27 public static string sqlSelALL = "select 28 29 id,title,contents,userName,phone,email,CONVERT(varchar(100), AddDate, 20) 30 31 AddDate,ReContents from Feedback"; 32 33 //从excel读数据 34 public static string ExToDB = "Provider=Microsoft.Jet.OLEDB.4.0;Extended 35 36 Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source="; 37 } 38 }
4.web.config
<connectionStrings> <add name="connectionString" connectionString="server=local;uid=sa;pwd=pwd;database=TestExcel" providerName="System.Data.SqlClient"/> </connectionStrings>
