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
}
}
#endregion