ASP.NET中 Execl导出的六种方法实例

 /// <summary>
 /// 导出Excel
 /// </summary>
 /// <param name="page"></param>
 /// <param name="dt"></param>
 //方法一：
 public void ImportExcel(Page page, DataTable dt)
 {
 try
 { string filename = Guid.NewGuid().ToString() + ".xls";
 string webFilePath = page.Server.MapPath("/" + filename);
 CreateExcelFile(webFilePath, dt);
 using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate))
 {
 //让用户输入下载的本地地址
 page.Response.Clear();
 page.Response.Buffer = true;
 page.Response.Charset = "GB2312"; //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls");
 page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
 page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
 page.Response.ContentType = "application/ms-excel"; // 读取excel数据到内存
 byte[] buffer = new byte[fs.Length - 1];
 fs.Read(buffer, 0, (int)fs.Length - 1); // 写到aspx页面
 page.Response.BinaryWrite(buffer);
 page.Response.Flush();
 //this.ApplicationInstance.CompleteRequest(); //停止页的执行
 fs.Close();
 fs.Dispose(); //删除临时文件
 File.Delete(webFilePath);
 } }
 catch (Exception ex)
 {
 throw ex;
 }
 }

 
 public void ImportExcel(Page page, DataSet ds)
 { try { string filename = Guid.NewGuid().ToString() + ".xls"; string webFilePath = page.Server.MapPath("/" + filename); CreateExcelFile(webFilePath, ds); using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate)) { //让用户输入下载的本地地址 page.Response.Clear(); page.Response.Buffer = true; page.Response.Charset = "GB2312"; //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls"); page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename); page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); page.Response.ContentType = "application/ms-excel"; // 读取excel数据到内存 byte[] buffer = new byte[fs.Length - 1]; fs.Read(buffer, 0, (int)fs.Length - 1); // 写到aspx页面 page.Response.BinaryWrite(buffer); page.Response.Flush(); //this.ApplicationInstance.CompleteRequest(); //停止页的执行 fs.Close(); fs.Dispose(); //删除临时文件 File.Delete(webFilePath); } } catch (Exception ex) { throw ex; } }

 
 public void ImportExcel(Page page, DataTable dt1, DataTable dt2, string conditions) { try { string filename = Guid.NewGuid().ToString() + ".xls"; string webFilePath = page.Server.MapPath("/" + filename); CreateExcelFile(webFilePath, dt1, dt2, conditions); using (FileStream fs = new FileStream(webFilePath, FileMode.OpenOrCreate)) { //让用户输入下载的本地地址 page.Response.Clear(); page.Response.Buffer = true; page.Response.Charset = "GB2312"; //page.Response.AppendHeader("Content-Disposition", "attachment;filename=MonitorResult.xls"); page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename); page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); page.Response.ContentType = "application/ms-excel"; // 读取excel数据到内存 byte[] buffer = new byte[fs.Length - 1]; fs.Read(buffer, 0, (int)fs.Length - 1); // 写到aspx页面 page.Response.BinaryWrite(buffer); page.Response.Flush(); //this.ApplicationInstance.CompleteRequest(); //停止页的执行 fs.Close(); fs.Dispose(); //删除临时文件 File.Delete(webFilePath); } } catch (Exception ex) { throw ex; } }

 
 private void CreateExcelFile(string filePath, DataTable dt) { if (File.Exists(filePath)) { File.Delete(filePath); } OleDbConnection oleDbConn = new OleDbConnection(); OleDbCommand oleDbCmd = new OleDbCommand(); try { string sSql = ""; oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;"""; oleDbConn.Open(); oleDbCmd.CommandType = CommandType.Text; oleDbCmd.Connection = oleDbConn; //写列名 sSql = "CREATE TABLE sheet1("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text,"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,"; } } else { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text)"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++) { sSql = "INSERT INTO sheet1 VALUES("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL,"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ","; } else { sSql += "'" + dt.Rows[j][i].ToString() + "',"; } } } else if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL)"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ")"; } else { sSql += "'" + dt.Rows[j][i].ToString() + "')"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); } } catch (System.Exception ex) { throw ex; } finally { //断开连接 oleDbCmd.Dispose(); oleDbConn.Close(); oleDbConn.Dispose(); } }

 
 private void CreateExcelFile(string filePath, DataSet ds) { if (File.Exists(filePath)) { File.Delete(filePath); } OleDbConnection oleDbConn = new OleDbConnection(); OleDbCommand oleDbCmd = new OleDbCommand(); try { string sSql = ""; oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;"""; oleDbConn.Open(); oleDbCmd.CommandType = CommandType.Text; oleDbCmd.Connection = oleDbConn; //写列名 for(int k=0;k<ds.Tables.Count;k++) { DataTable dt = ds.Tables[k]; sSql = "CREATE TABLE sheet" + (k + 1).ToString() + "("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (dt.Columns[i].DataType.Name == "String" || dt.Columns[i].DataType.Name=="Guid") { sSql += "["+dt.Columns[i].ColumnName + "] Text,"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,"; } } else { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text)"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); for (int j = 0; j < dt.Rows.Count; j++) { sSql = "INSERT INTO sheet" + (k + 1).ToString() + " VALUES("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL,"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ","; } else { sSql += "'" + dt.Rows[j][i].ToString().Replace("'", "''") + "',"; } } } else if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL)"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ")"; } else { sSql += "'" + dt.Rows[j][i].ToString().Replace("'","''") + "')"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); } } } catch (System.Exception ex) { throw ex; } finally { //断开连接 oleDbCmd.Dispose(); oleDbConn.Close(); oleDbConn.Dispose(); } }

 
 private void CreateExcelFile(string filePath, DataTable dt1,DataTable dt2,string conditions) { if (File.Exists(filePath)) { File.Delete(filePath); } OleDbConnection oleDbConn = new OleDbConnection(); OleDbCommand oleDbCmd = new OleDbCommand();

 try { string sSql = ""; oleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;"""; oleDbConn.Open(); oleDbCmd.CommandType = CommandType.Text; oleDbCmd.Connection = oleDbConn; //写列名 sSql = "CREATE TABLE sheet1("; DataTable dt = dt1.Copy(); dt.Columns.Remove("MGUID"); for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text,"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,"; } } else { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text)"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); DataView dv = new DataView(); dv.Table = dt; DataView dv1 = new DataView(); dv1.Table = dt1; if (conditions != "") { dv.RowFilter = conditions; dv1.RowFilter = conditions; } dt = dv.ToTable(); dt1 = dv1.ToTable(); string MGUIDs = ""; for (int j = 0; j < dt.Rows.Count; j++) { MGUIDs += ",'" + dt1.Rows[j]["MGUID"].ToString() + "'"; sSql = "INSERT INTO sheet1 VALUES("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL,"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ","; } else { sSql += "'" + dt.Rows[j][i].ToString() + "',"; } } } else if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL)"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ")"; } else { sSql += "'" + dt.Rows[j][i].ToString() + "')"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); } if (dt2 != null) { sSql = "CREATE TABLE sheet21("; dt = dt2.Copy(); dt.Columns.Remove("MGUID"); dt.Columns.Remove("DGUID"); for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text,"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] Datetime,"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal,"; } } else { if (dt.Columns[i].DataType.Name == "String") { sSql += "[" + dt.Columns[i].ColumnName + "] Text)"; } else if (dt.Columns[i].DataType.Name == "DateTime") { sSql += "[" + dt.Columns[i].ColumnName + "] DateTime)"; } else { sSql += "[" + dt.Columns[i].ColumnName + "] Decimal)"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); dv = new DataView(); dv.Table = dt2;  if (MGUIDs != "") { dv.RowFilter = "MGUID in(" + MGUIDs.Substring(1) + ")"; } dt = dv.ToTable(); for (int j = 0; j < dt.Rows.Count; j++) { sSql = "INSERT INTO sheet1 VALUES("; for (int i = 0; i < dt.Columns.Count; i++) { if (i < dt.Columns.Count - 1) { if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL,"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ","; } else { sSql += "'" + dt.Rows[j][i].ToString() + "',"; } } } else if (DBNull.Value.Equals(dt.Rows[j][i])) { sSql += "NULL)"; } else { if (dt.Columns[i].DataType.Name == "Decimal") { sSql += dt.Rows[j][i].ToString() + ")"; } else { sSql += "'" + dt.Rows[j][i].ToString() + "')"; } } } oleDbCmd.CommandText = sSql; oleDbCmd.ExecuteNonQuery(); } } } catch (System.Exception ex) { throw ex; } finally { //断开连接 oleDbCmd.Dispose(); oleDbConn.Close(); oleDbConn.Dispose(); } }

以上就是ASP.NET中 Execl导出的六种方法实例。我的未来不是梦，是问号。更多关于ASP.NET中 Execl导出的六种方法实例请关注haodaima.com其它相关文章！