C# WINFORM Excel 导入导出 类

以下是C# winform sheet导入导出的操作

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

///

///Excel 的摘要说明

///

namespace harry

{

public class Excel

{

#region读取Excel文件转换成DataTable

///

///读取Excel文件转换成DataTable

///

///Excel文件路径

///

///

public static System.Data.DataTable Import_Sheet(string excelPath, string sheetName) { return Import_Sql(excelPath, "select * from [" + sheetName + "$]"); }

///

///读取Excel文件转换成DataTable

///

///Excel文件路径

///Excel内查询语句

///

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()); }

///

///输出Excel文件

///

///数据源

///储存路径

///Excel格式化信息对象

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输出

///

///使用DataGrid输出Excel文件

///

///DataGrid

public static void Export_DataGrid(DataGrid dg) { Export_DataGrid(dg, null, null, null, false); }

///

///使用DataGrid输出Excel文件

///

///DataGrid

///文件名称

public static void Export_DataGrid(DataGrid dg, string fileName) { Export_DataGrid(dg, null, fileName, null, false); }

///

///使用DataGrid输出Excel文件

///

///DataGrid

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

public static void Export_DataGrid(DataGrid dg, string fileName, string formatCellIndex) { Export_DataGrid(dg, null, fileName, formatCellIndex, false); }

///

///使用DataGrid输出Excel文件

///

///DataGrid

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

串

///是否直接输出到前台

///Excel文件内容

public static string Export_DataGrid(DataGrid dg, string fileName, string formatCellIndex, bool isWrite) { return Export_DataGrid(dg, null, fileName, formatCellIndex, isWrite); }

///

///使用DataGrid输出Excel文件

///

///DataTable数据源

public static void Export_DataGrid(System.Data.DataTable dt) { Export_DataGrid(null, dt, null, null, false); }

///

///使用DataGrid输出Excel文件

///

///DataTable数据源

///文件名称

public static void Export_DataGrid(System.Data.DataTable dt, string fileName)

{ Export_DataGrid(null, dt, fileName, null, false); }

///

///使用DataGrid输出Excel文件

///

///DataTable数据源

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

串

public static void Export_DataGrid(System.Data.DataTable dt, string fileName, string formatCellIndex) { Export_DataGrid(null, dt, fileName, formatCellIndex, false); } ///

///使用DataGrid输出Excel文件

///

///DataTable数据源

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

///是否直接输出到前台

///Excel文件内容

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输出

///

///使用GridView输出Excel文件

///

///GridView

public static void Export_GridView(GridView gv) { Export_GridView(gv, null, null, null, false); }

///

///使用GridView输出Excel文件

///

///GridView

///文件名称

public static void Export_GridView(GridView gv, string fileName) { Export_GridView(gv, null, fileName, null, false); }

///

///使用GridView输出Excel文件

///

///GridView

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符串

public static void Export_GridView(GridView gv, string fileName, string formatCellIndex) { Export_GridView(gv, null, fileName, formatCellIndex, false); }

///

///使用GridView输出Excel文件

///

///GridView

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符串

///是否直接输出到前台

///Excel文件内容

public static string Export_GridView(GridView gv, string fileName, string formatCellIndex, bool isWrite) { return Export_GridView(gv, null, fileName, formatCellIndex, isWrite); }

///

///使用GridView输出Excel文件

///

///DataTable数据源

public static void Export_GridView(System.Data.DataTable dt) { Export_GridView(null, dt, null, null, false); }

///

///使用GridView输出Excel文件

///

///DataTable数据源

///文件名称

public static void Export_GridView(System.Data.DataTable dt, string fileName)

{ Export_GridView(null, dt, fileName, null, false); }

///

///使用GridView输出Excel文件

///

///DataTable数据源

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

串

public static void Export_GridView(System.Data.DataTable dt, string fileName, string formatCellIndex) { Export_GridView(null, dt, fileName, formatCellIndex, false); } ///

///使用GridView输出Excel文件

///

///DataTable数据源

///文件名称

///需要格式化的列集合，例如"$1$2$"为1,2列格式化为字符

串

///是否直接输出到前台

///Excel文件内容

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