Excel的导入导出.Net
/*------------------------------------------------------------------------------------
* 文件名：ExcelCtrol.cs
* 文件功能描述：Excel的导入导出
*
* 创建标识：刘佳忻 20080722
*
* 修改标识：
* 修改描述：
*
* ----------------------------------------------------------------------------------*/
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>2008-07-22 ljx</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>2008-07-22 ljx</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>2008-07-22 ljx</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>2008-07-22 ljx</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>2008-07-22 ljx</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>
/// 已控件格式导出(2008-07-23)
/// 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文件
/// 2008-07-02 ljx
/// </summary>
/// <param name="FilePath">文件路径</param>
public static void DelExcel(string FilePath)
{
FileInfo ExcelFile = new FileInfo(FilePath);
ExcelFile.Delete();
}
}
}
