强大的Excel文件的导入导出类

using System;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
/// <summary>
///Excel 的摘要说明
/// </summary>
namespace harry
{
     public class Excel
     {
         #region 读取Excel文件转换成DataTable
 
         /// <summary>
         /// 读取Excel文件转换成DataTable
         /// </summary>
         /// <param name="excelPath">Excel文件路径</param>
         /// <param name="sheetName"></param>
         /// <returns></returns>
         public static System.Data.DataTable Import_Sheet( string excelPath, string sheetName) { return Import_Sql(excelPath, "select * from [" + sheetName + "$]" ); }
 
         /// <summary>
         /// 读取Excel文件转换成DataTable
         /// </summary>
         /// <param name="excelPath">Excel文件路径</param>
         /// <param name="sql">Excel内查询语句</param>
         /// <returns></returns>
         public static System.Data.DataTable Import_Sql( string excelPath, string sql)
         {
             //string 2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + filepath;
             //string 2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';data source=" + fPath;//读EXCEL2003/excel2007
             OleDbConnection conn = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;HDR=1;';data source=" + excelPath);
             OleDbDataAdapter odda = new OleDbDataAdapter(sql, conn);
             System.Data.DataTable dt = new System.Data.DataTable();
             try
             {
                 conn.Open();
                 odda.Fill(dt);
             }
             catch (Exception ex) { throw ex; }
             finally
             {
                 conn.Close();
                 conn.Dispose();
                 odda.Dispose();
             }
             return dt;
         }
 
         #endregion
 
         #region 输出Excel文件
 
         #region 使用office组件输出
 
         public static void Export_Office(System.Data.DataTable dt) { Export_Office(dt, "" , new Model.harry.ExcelInfo()); }
 
         public static void Export_Office(System.Data.DataTable dt, Model.harry.ExcelInfo model) { Export_Office(dt, "" , model); }
 
         public static void Export_Office(System.Data.DataTable dt, string savePath) { Export_Office(dt, savePath, new Model.harry.ExcelInfo()); }
 
         /// <summary>
         /// 输出Excel文件
         /// </summary>
         /// <param name="dt">数据源</param>
         /// <param name="savePath">储存路径</param>
         /// <param name="ExcelInfo">Excel格式化信息对象</param>
         private static void Export_Office(System.Data.DataTable dt, string savePath, Model.harry.ExcelInfo model)
         {
             if (dt.Rows.Count > 0)
             {
                 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                 Microsoft.Office.Interop.Excel._Worksheet format = (Microsoft.Office.Interop.Excel._Worksheet)excel.Application.Workbooks.Add( true ).ActiveSheet;
 
                 //设置行和列的索引
                 int rowIndex = 1, colIndex = 0;
                 //添加列名
                 foreach (DataColumn col in dt.Columns)
                 {
                     colIndex++;
                     excel.Cells[1, colIndex] = col.ColumnName;
                 }
                 //添加数据
                 foreach (DataRow row in dt.Rows)
                 {
                     rowIndex++;
                     colIndex = 0;
                     foreach (DataColumn col in dt.Columns)
                     {
                         colIndex++;
                         excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                     }
                 }
                 if (model != null )
                 {
                     //设置标题是否为粗体
                     format.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Font.Bold = model.TitleBold;
 
                     Range range = format.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex]);
                     //设置字体大小
                     range.Font.Size = model.FontSize;
                     //设置列宽
                     if (model.Width == Model.harry.ExcelInfo.WidthType.auto) { range.Columns.AutoFit(); }
                     else if (model.Width == Model.harry.ExcelInfo.WidthType.size) { range.ColumnWidth = model.WidthSize; }
                     //设置对齐格式
                     if (model.Align == Model.harry.ExcelInfo.AlignType.left) { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; }
                     else if (model.Align == Model.harry.ExcelInfo.AlignType.center) { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; }
                     else if (model.Align == Model.harry.ExcelInfo.AlignType.right) { range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; }
                     else { }
 
                     if (model.Valign == Model.harry.ExcelInfo.ValignType.top) { range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; }
                     else if (model.Valign == Model.harry.ExcelInfo.ValignType.middle) { range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; }
                     else if (model.Valign == Model.harry.ExcelInfo.ValignType.bottom) { range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignBottom; }
                     else { }
                 }
 
                 //不可见,即后台处理
                 excel.Visible = false ;
                 //设置禁止弹出保存的询问提示框
                 excel.DisplayAlerts = false ;
                 设置禁止弹出覆盖的询问提示框
                 //excel.AlertBeforeOverwriting = false;
 
                 try
                 {
                     if (savePath != "" ) { excel.ActiveWorkbook.SaveCopyAs(savePath); }
                     else
                     {
                         excel.Save(AppDomain.CurrentDomain.BaseDirectory + "bak.xls" );
                         if (System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + "bak.xls" )) System.IO.File.Delete(AppDomain.CurrentDomain.BaseDirectory + "bak.xls" );
                     }
                 }
                 catch { }
 
                 finally
                 {
                     excel.Application.Workbooks.Close();
                     excel.Application.Quit();
                     excel.Quit();
                     //释放使用的Excel对象
                     System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                     GC.Collect();
                     KillExcel();
                 }
             }
         }
 
         //删除执行过程中未释放的Excel线程
         private static void KillExcel()
         {
             System.Diagnostics.Process[] excelProcesses = System.Diagnostics.Process.GetProcessesByName( "EXCEL" );
             DateTime startTime = new DateTime();
 
             int processId = 0;
             for ( int i = 0; i < excelProcesses.Length; i++)
             {
                 if (startTime < excelProcesses[i].StartTime)
                 {
                     startTime = excelProcesses[i].StartTime;
                     processId = i;
                 }
             }
             if (excelProcesses[processId].HasExited == false ) { excelProcesses[processId].Kill(); }
         }
 
         #endregion
 
         #region 使用DataGrid输出
 
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataGrid">DataGrid</param>
         public static void Export_DataGrid(DataGrid dg) { Export_DataGrid(dg, null , null , null , false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataGrid">DataGrid</param>
         /// <param name="fileName">文件名称</param>
         public static void Export_DataGrid(DataGrid dg, string fileName) { Export_DataGrid(dg, null , fileName, null , false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataGrid">DataGrid</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         public static void Export_DataGrid(DataGrid dg, string fileName, string formatCellIndex) { Export_DataGrid(dg, null , fileName, formatCellIndex, false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataGrid">DataGrid</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         /// <param name="isWrite">是否直接输出到前台</param>
         /// <returns>Excel文件内容</returns>
         public static string Export_DataGrid(DataGrid dg, string fileName, string formatCellIndex, bool isWrite) { return Export_DataGrid(dg, null , fileName, formatCellIndex, isWrite); }
 
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         public static void Export_DataGrid(System.Data.DataTable dt) { Export_DataGrid( null , dt, null , null , false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         public static void Export_DataGrid(System.Data.DataTable dt, string fileName) { Export_DataGrid( null , dt, fileName, null , false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         public static void Export_DataGrid(System.Data.DataTable dt, string fileName, string formatCellIndex) { Export_DataGrid( null , dt, fileName, formatCellIndex, false ); }
         /// <summary>
         /// 使用DataGrid输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         /// <param name="isWrite">是否直接输出到前台</param>
         /// <returns>Excel文件内容</returns>
         public static string Export_DataGrid(System.Data.DataTable dt, string fileName, string formatCellIndex, bool isWrite) { return Export_DataGrid( null , dt, fileName, formatCellIndex, isWrite); }
 
         private static string Export_DataGrid(DataGrid dg, System.Data.DataTable dt, string fileName, string formatCellIndex, bool isWrite)
         {
             if (dg == null )
             {
                 dg = new DataGrid();
                 dg.DataSource = dt;
                 dg.DataBind();
             }
             foreach (DataGridItem dgi in dg.Items)
             {
                 for ( int j = 0; j < dgi.Cells.Count; j++)
                 {
                     if (formatCellIndex != null && formatCellIndex != "" && formatCellIndex.Contains( "$" + j + "$" )) dgi.Cells[j].Attributes.Add( "style" , "mso-number-format:'@';" );
                 }
             }
             if (fileName == null || fileName == "" ) { fileName = "Excel.xls" ; }
 
             System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
             dg.RenderControl( new System.Web.UI.HtmlTextWriter(oStringWriter));
 
             if (isWrite) { return oStringWriter.ToString(); }
             else
             {
                 //this.EnableViewState = false;
                 HttpResponse response = System.Web.HttpContext.Current.Response;
                 response.Clear();
                 response.Buffer = true ;
                 response.ContentType = "application/vnd.ms-excel" ;
                 response.Charset = "gb2312" ;
                 response.ContentEncoding = System.Text.Encoding.GetEncoding( "gb2312" );
                 response.AppendHeader( "content-disposition" , "attachment;fileName=\"" + fileName + "\"" );
 
                 response.Write(oStringWriter.ToString());
                 response.End();
                 return "" ;
             }
         }
 
         #endregion
 
         #region 使用GridView输出
 
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="GridView">GridView</param>
         public static void Export_GridView(GridView gv) { Export_GridView(gv, null , null , null , false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="GridView">GridView</param>
         /// <param name="fileName">文件名称</param>
         public static void Export_GridView(GridView gv, string fileName) { Export_GridView(gv, null , fileName, null , false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="GridView">GridView</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         public static void Export_GridView(GridView gv, string fileName, string formatCellIndex) { Export_GridView(gv, null , fileName, formatCellIndex, false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="GridView">GridView</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         /// <param name="isWrite">是否直接输出到前台</param>
         /// <returns>Excel文件内容</returns>
         public static string Export_GridView(GridView gv, string fileName, string formatCellIndex, bool isWrite) { return Export_GridView(gv, null , fileName, formatCellIndex, isWrite); }
 
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         public static void Export_GridView(System.Data.DataTable dt) { Export_GridView( null , dt, null , null , false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         public static void Export_GridView(System.Data.DataTable dt, string fileName) { Export_GridView( null , dt, fileName, null , false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         public static void Export_GridView(System.Data.DataTable dt, string fileName, string formatCellIndex) { Export_GridView( null , dt, fileName, formatCellIndex, false ); }
         /// <summary>
         /// 使用GridView输出Excel文件
         /// </summary>
         /// <param name="DataTable">DataTable数据源</param>
         /// <param name="fileName">文件名称</param>
         /// <param name="formatCellIndex">需要格式化的列集合,例如"$1$2$"为1,2列格式化为字符串</param>
         /// <param name="isWrite">是否直接输出到前台</param>
         /// <returns>Excel文件内容</returns>
         public static string Export_GridView(System.Data.DataTable dt, string fileName, string formatCellIndex, bool isWrite) { return Export_GridView( null , dt, fileName, formatCellIndex, isWrite); }
 
         private static string Export_GridView(GridView gv, System.Data.DataTable dt, string fileName, string formatCellIndex, bool isWrite)
         {
             if (gv == null )
             {
                 gv = new GridView();
                 gv.DataSource = dt;
                 gv.DataBind();
             }
             foreach (GridViewRow gvr in gv.Rows)
             {
                 for ( int j = 0; j < gvr.Cells.Count; j++)
                 {
                     if (formatCellIndex != null && formatCellIndex != "" && formatCellIndex.Contains( "$" + j + "$" )) gvr.Cells[j].Attributes.Add( "style" , "mso-number-format:'@';" );
                 }
             }
             if (fileName == null || fileName == "" ) { fileName = "Excel.xls" ; }
 
             System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
             gv.RenderControl( new System.Web.UI.HtmlTextWriter(oStringWriter));
 
             if (isWrite) { return oStringWriter.ToString(); }
             else
             {
                 //this.EnableViewState = false;
                 HttpResponse response = System.Web.HttpContext.Current.Response;
                 response.Clear();
                 response.Buffer = true ;
                 response.ContentType = "application/vnd.ms-excel" ;
                 response.Charset = "gb2312" ;
                 response.ContentEncoding = System.Text.Encoding.GetEncoding( "gb2312" );
                 response.AppendHeader( "content-disposition" , "attachment;fileName=\"" + fileName + "\"" );
 
                 response.Write(oStringWriter.ToString());
                 response.End();
                 return "" ;
             }
         }
 
         #endregion
 
         #endregion
     }
}

转载于:https://www.cnblogs.com/endsock/archive/2011/04/20/2022926.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值