强大的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 BindingFlagsusing 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 }}
分类:
Web Technique
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 25岁的心里话
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现