Excel的导入导出.Net

      在开发中，总是要碰到Excel的转换问题，这就是现代办公的需要。总是会碰到将一下资料上传或者下载，或者DB和Excel之间的互换。最近，忙一个专案也有用到，随便就整理了一下。代码如下：

/**//*------------------------------------------------------------------------------------ * 文件名：ExcelCtrol.cs * 文件功能描述：Excel的导入导出 * * 创建标识：jiali765 20101022 * * 修改标识： * 修改描述：Excel操作 ** ----------------------------------------------------------------------------------*/using System;using System.Text;using System.Collections;using System.Configuration;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.IO;using Wiscom.Data;using Excel=Microsoft.Office.Interop.Excel;namespace Wiscom.Urp.ReceiveFreshman{ /**//// <summary> /// Excel的控制和基本操作 /// </summary> public class ExcelCtrol { /**//// <summary> /// <br>获取Excel中的表名字</br> /// <br>2010-10-22 ljj</br> /// </summary> /// <param name="filepath">Excel文件路径</param> public static ArrayList GetExcelSheetName(string filepath) { ArrayList al = new ArrayList(); string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } } catch (Exception ex) {} return al; } /**//// <summary> /// <br>excel文件读取到DataSet</br> /// <br>2010-10-22 ljj</br> /// </summary> /// <param name="filepath">Excel文件路径</param> /// <param name="sheetname">Excel文件中的表</param> public static DataSet ExcelDataSet(string filepath, string sheetname) { string strConn; DataSet ds = new DataSet(); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn); oada.Fill(ds); } catch (Exception ex) { } return ds; } /**//// <summary> /// 得到宿舍信息Excel文件的DataSet /// <br>2010-10-22 ljj</br> /// </summary> /// <param name="filepath">Excel文件路径</param> /// <param name="sheetname">Excel文件中的表</param> /// <returns></returns> public static DataSet Get_Ssxx_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet { string strConn; DataSet ds = new DataSet(); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn); oada.Fill(ds); } catch (Exception ex) { } return ds; } public static DataSet Get_StudentInfo_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet { string strConn; DataSet ds = new DataSet(); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,性别,院系代码,专业代码,班级代码,学籍状态,出生年月,生源地,籍贯,身份证号,民族编码,政治面貌码 from [" + sheetname + "]", strConn); oada.Fill(ds); } catch (Exception ex) { } return ds; } /**//// <summary> /// 得到宿舍信息Excel文件的DataSet /// <br>2010-10-22 ljj</br> /// </summary> /// <param name="filepath">文件路径</param> /// <param name="sheetname">Excel文件中的表</param> /// <returns></returns> public static DataSet Get_Ssxx_DataSet(string filepath, ArrayList sheetname) //把excel文件读取到DataSet { string strConn; DataSet ds = new DataSet(); strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn); oada.Fill(ds); } catch (Exception ex) { } return ds; } /**//// <summary> /// 导出 DataSet为Excel文件格式[!导出的Excel没有加工!] /// <br>2010-10-22 ljj</br> /// </summary> /// <param name="strFileName">导出名称</param> /// <param name="dt">DataSet</param> public static void ExportExcel(string strFileName, DataSet dt) { //清除Response缓存内容 HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; strFileName = strFileName + ".xls"; //确定字符的编码格式 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName)); HttpContext.Current.Response.ContentType = "aapplication/vnd.ms-excel"; HttpContext.Current.Response.Charset = "gb2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); GridView dv = new GridView(); dv.DataSource = dt; //dv.RowDataBound += GridView2_RowDataBound;//事件 dv.DataBind(); try { dv.Page.EnableViewState = false; } catch { } System.IO.StringWriter swBody = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hwBody = new System.Web.UI.HtmlTextWriter(swBody); dv.RenderControl(hwBody); //消除乱码特别设定，非常规方法 string strExcel = ""; strExcel = ""; strExcel += hwBody.InnerWriter.ToString(); HttpContext.Current.Response.Write(strExcel); HttpContext.Current.Response.End(); } /**//// <summary> /// 已控件格式导出(2010-10-22) /// public override void VerifyRenderingInServerForm(Control control) /// 必须调用该函数之前,重写VerifyRenderingInServerForm /// </summary> /// <param name="ctl">GridView控件</param> /// <param name="FileName">文件输出名字</param> public static void ToExcel(Control ctl, string FileName) { HttpContext.Current.Response.Charset = "Gb2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName); ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); } public static void ToExcel(String Sql, string FileName) { //Add Response header HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", FileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; //GET Data From Database try { SqlDataReader dr = (SqlDataReader)DataHelper.GetDataReader(Sql); StringBuilder sb = new StringBuilder(); // //Add Header // for (int count = 0; count < dr.FieldCount; count++) { if (dr.GetName(count) != null) sb.Append(dr.GetName(count)); if (count < dr.FieldCount - 1) { sb.Append(","); } } HttpContext.Current.Response.Write(sb.ToString() + "/n"); HttpContext.Current.Response.Flush(); // //Append Data // while (dr.Read()) { sb = new StringBuilder(); for (int col = 0; col < dr.FieldCount - 1; col++) { if (!dr.IsDBNull(col)) sb.Append(dr.GetValue(col).ToString().Replace(",", " ")); sb.Append(","); } if (!dr.IsDBNull(dr.FieldCount - 1)) sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " ")); HttpContext.Current.Response.Write(sb.ToString() + "/n"); HttpContext.Current.Response.Flush(); } dr.Dispose(); } catch (Exception ex) { HttpContext.Current.Response.Write(ex.Message); } finally { } HttpContext.Current.Response.End(); } public static void ToExcel(DataSet ds, string strExcelFileName) { Excel.Application excel = new Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass(); // obj.SaveAs("c:/zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex = 1; int colIndex = 0; excel.Application.Workbooks.Add(true); System.Data.DataTable table = ds.Tables[0]; foreach (DataColumn col in table.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; } foreach (DataRow row in table.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } excel.Visible = false; //excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null); excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //wkbNew.SaveAs strBookName //excel.Save(strExcelFileName); excel.Quit(); excel = null; GC.Collect();//垃圾回收 } /**//// <summary> /// 删除Excel文件 /// 2010-10-20 ljj /// </summary> /// <param name="FilePath">文件路径</param> public static void DelExcel(string FilePath) { FileInfo ExcelFile = new FileInfo(FilePath); ExcelFile.Delete(); } } }

 

转换的思路有很多，比较普遍的就是先用DataSet做桥接！