asp.net導(dǎo)出Excel類庫代碼分享
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
/// <summary>
///ExcelClass 的摘要說明
/// </summary>
public class ExcelClass
{
/// <summary>
/// 構(gòu)建ExcelClass類
/// </summary>
public ExcelClass()
{
this.m_objExcel = new Excel.Application();
}
/// <summary>
/// 構(gòu)建ExcelClass類
/// </summary>
/// <param name="objExcel">Excel.Application</param>
public ExcelClass(Excel.Application objExcel)
{
this.m_objExcel = objExcel;
}
/// <summary>
/// 列標(biāo)號
/// </summary>
private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
/// <summary>
/// 獲取描述區(qū)域的字符
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
public string GetAix(int x, int y)
{
char[] AChars = AList.ToCharArray();
if (x >= 26) { return ""; }
string s = "";
s = s + AChars[x - 1].ToString();
s = s + y.ToString();
return s;
}
/// <summary>
/// 給單元格賦值1
/// </summary>
/// <param name="x">行號</param>
/// <param name="y">列號</param>
/// <param name="align">對齊（CENTER、LEFT、RIGHT）</param>
/// <param name="text">值</param>
public void setValue(int y, int x, string align, string text)
{
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
if (align.ToUpper() == "CENTER")
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
if (align.ToUpper() == "LEFT")
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
if (align.ToUpper() == "RIGHT")
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
}
/// <summary>
/// 給單元格賦值2
/// </summary>
/// <param name="x">行號</param>
/// <param name="y">列號</param>
/// <param name="text">值</param>
public void setValue(int y, int x, string text)
{
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
}
/// <summary>
/// 給單元格賦值3
/// </summary>
/// <param name="x">行號</param>
/// <param name="y">列號</param>
/// <param name="text">值</param>
/// <param name="font">字符格式</param>
/// <param name="color">顏色</param>
public void setValue(int y, int x, string text, System.Drawing.Font font, System.Drawing.Color color)
{
this.setValue(x, y, text);
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.Font.Size = font.Size;
range.Font.Bold = font.Bold;
range.Font.Color = color;
range.Font.Name = font.Name;
range.Font.Italic = font.Italic;
range.Font.Underline = font.Underline;
}
/// <summary>
/// 插入新行
/// </summary>
/// <param name="y">模板行號</param>
public void insertRow(int y)
{
Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
range.Copy(miss);
range.Insert(Excel.XlDirection.xlDown, miss);
range.get_Range(GetAix(1, y), GetAix(25, y));
range.Select();
sheet.Paste(miss, miss);
}
/// <summary>
/// 把剪切內(nèi)容粘貼到當(dāng)前區(qū)域
/// </summary>
public void past()
{
string s = "a,b,c,d,e,f,g";
sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s);
}
/// <summary>
/// 設(shè)置邊框
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
/// <param name="Width"></param>
public void setBorder(int x1, int y1, int x2, int y2, int Width)
{
Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), miss);
((Excel.Range)range.Cells[x1, y1]).ColumnWidth = Width;
}
public void mergeCell(int x1, int y1, int x2, int y2)
{
Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
range.Merge(true);
}
public Excel.Range getRange(int x1, int y1, int x2, int y2)
{
Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
return range;
}
private object miss = Missing.Value; //忽略的參數(shù)OLENULL
private Excel.Application m_objExcel;//Excel應(yīng)用程序?qū)嵗?
private Excel.Workbooks m_objBooks;//工作表集合
private Excel.Workbook m_objBook;//當(dāng)前操作的工作表
private Excel.Worksheet sheet;//當(dāng)前操作的表格
public Excel.Worksheet CurrentSheet
{
get
{
return sheet;
}
set
{
this.sheet = value;
}
}
public Excel.Workbooks CurrentWorkBooks
{
get
{
return this.m_objBooks;
}
set
{
this.m_objBooks = value;
}
}
public Excel.Workbook CurrentWorkBook
{
get
{
return this.m_objBook;
}
set
{
this.m_objBook = value;
}
}
/// <summary>
/// 打開Excel文件
/// </summary>
/// <param name="filename">路徑</param>
public void OpenExcelFile(string filename)
{
UserControl(false);
m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss,
miss, miss, miss, miss, miss, miss, miss);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = m_objExcel.ActiveWorkbook;
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void UserControl(bool usercontrol)
{
if (m_objExcel == null) { return; }
m_objExcel.UserControl = usercontrol;
m_objExcel.DisplayAlerts = usercontrol;
m_objExcel.Visible = usercontrol;
}
public void CreateExceFile()
{
UserControl(false);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss, miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
public void ReleaseExcel()
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
sheet = null;
GC.Collect();
}
public bool KillAllExcelApp()
{
try
{
if (m_objExcel != null) // isRunning是判斷xlApp是怎么啟動的flag.
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
//釋放COM組件，其實就是將其引用計數(shù)減1
//System.Diagnostics.Process theProc;
foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
//先關(guān)閉圖形窗口。如果關(guān)閉失敗...有的時候在狀態(tài)里看不到圖形窗口的excel了，
//但是在進(jìn)程里仍然有EXCEL.EXE的進(jìn)程存在，那么就需要殺掉它:p
if (theProc.CloseMainWindow() == false)
{
theProc.Kill();
}
}
m_objExcel = null;
return true;
}
}
catch
{
return false;
}
return true;
}
}
/// <summary>
/// 點擊打印按鈕事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Sendbu_Click(object sender, EventArgs e)
{
try
{
//查找部門分類用戶
DataTable Duser = EduOA.DBUtility.DbHelperSQL.Query("select count(*) as count,d.Id as DId FROM OA_User u,OA_Department d where u.DepartmentID=d.Id group by d.Id").Tables[0];
ExcelClass Ec = new ExcelClass();//創(chuàng)建Excel操作類對象
int Ycount = 1;
Ec.CreateExceFile();//創(chuàng)建Excel文件
Ec.setValue(Ycount, 1, "CENTER", "組織部門");
Ec.setValue(Ycount, 2, "CENTER", "姓名");
Ec.setValue(Ycount, 3, "CENTER", "性別");
Ec.setValue(Ycount, 4, "CENTER", "職位");
Ec.setValue(Ycount, 5, "CENTER", "移動電話");
Ec.setValue(Ycount, 6, "CENTER", "電話");
Ec.setValue(Ycount, 7, "CENTER", "電子郵箱");
Ec.setBorder(1, 1, 1, 1, 50);
Ec.setBorder(1, 2, 2, 2, 20);
Ec.setBorder(1, 5, 5, 5, 20);
Ec.setBorder(1, 6, 6, 6, 20);
Ec.setBorder(1, 7, 7, 7, 20);
for (int i = 0; i < Duser.Rows.Count; i++)
{
Ycount += 1;
Ec.setValue(Ycount, 1, "CENTER", Common.DeleteHtml(Getdept(Duser.Rows[i]["count"], Duser.Rows[i]["DId"])));
DataTable dtuser = GetData(Duser.Rows[i]["DId"]);
for (int k = 0; k < dtuser.Rows.Count; k++)
{
Ec.setValue(Ycount, 2, "CENTER", dtuser.Rows[k]["TrueName"].ToString());
Ec.setValue(Ycount, 3, "CENTER", dtuser.Rows[k]["sex"].ToString());
Ec.setValue(Ycount, 4, "CENTER", dtuser.Rows[k]["PositionId"].ToString());
Ec.setValue(Ycount, 5, "CENTER", dtuser.Rows[k]["Telephone"].ToString());
Ec.setValue(Ycount, 6, "CENTER", dtuser.Rows[k]["Mobile"].ToString());
Ec.setValue(Ycount, 7, "CENTER", dtuser.Rows[k]["Email"].ToString());
Ycount += 1;
}
}
string path = Server.MapPath("Contactfiles\\");
Ec.SaveAs(path+"通訊錄.xlsx");
//*******釋放Excel資源***********
Ec.ReleaseExcel();
Response.Redirect("Contactfiles/通訊錄.xlsx");
}
catch (Exception ex)
{
PageError("導(dǎo)出出錯！"+ex.ToString(),"");
}
}
- .NET6導(dǎo)入和導(dǎo)出EXCEL
- Asp.Net Core實現(xiàn)Excel導(dǎo)出功能的實現(xiàn)方法
- ASP.NET Core 導(dǎo)入導(dǎo)出Excel xlsx 文件實例
- asp.net DataTable導(dǎo)出Excel自定義列名的方法
- ASP.NET使用GridView導(dǎo)出Excel實現(xiàn)方法
- Asp.Net使用Npoi導(dǎo)入導(dǎo)出Excel的方法
- asp.net導(dǎo)出excel的簡單方法實例
- ASP.NET導(dǎo)出數(shù)據(jù)到Excel的實現(xiàn)方法
- Asp.net中DataTable導(dǎo)出到Excel的方法介紹
- ASP.NET用DataSet導(dǎo)出到Excel的方法
- asp.net GridView導(dǎo)出到Excel代碼
- ASP.NET MVC把表格導(dǎo)出到Excel
相關(guān)文章
ASP.NET 圖片加水印防盜鏈實現(xiàn)代碼，需要的朋友可以參考下。2011-12-12
不使用web服務(wù)(Service)實現(xiàn)文本框自動完成擴(kuò)展
以前寫Ajax 的AutoCompleteExtender功能，都需要寫WCF Service或是Web Service數(shù)據(jù)源，下面的演示，不用寫Service來實現(xiàn)文本框的AutoCompete extender功能，感興趣的朋友可以參考下哈2013-04-04
實現(xiàn)onmouseover和onmouseout應(yīng)用于RadioButtonList或CheckBoxList控件上
一直想實現(xiàn)onmouseover和onmouseout應(yīng)用于RadioButtonList或CheckBoxList控件上。此功能就是當(dāng)鼠標(biāo)經(jīng)過時RadioButtonList或CheckBoxList每一個Item時，讓Item有特效顯示，離開時，恢復(fù)原樣有演示動畫，感興趣的朋友可以了解下啊2013-01-01
這篇文章主要介紹了ASP.Net中利用CSS實現(xiàn)多界面的兩種方法,包括動態(tài)加載css樣式與動態(tài)設(shè)置頁面同類控件的方法來實現(xiàn)該功能,是非常具有實用價值的技巧,需要的朋友可以參考下2014-12-12
調(diào)試ASP.NET應(yīng)用程序的方法和技巧
調(diào)試ASP.NET應(yīng)用程序的方法和技巧...2006-09-09
關(guān)于.NET動態(tài)代理的介紹和應(yīng)用簡介
關(guān)于.NET動態(tài)代理的介紹和應(yīng)用簡介...2006-09-09
如何在ASP.Net Core使用分布式緩存的實現(xiàn)
這篇文章主要介紹了如何在ASP.Net Core使用分布式緩存的實現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
asp.net 該行已經(jīng)屬于另一個表錯誤的解決方法
從一個TABLE中取一行放到另一個TABLE里報錯： 該行已經(jīng)屬于另一個表。的解決辦法 用下面來個方法就OK了。2010-05-05
這篇文章主要為大家詳細(xì)介紹了asp.net MVC分頁代碼,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-09-09
ASP.NET開源導(dǎo)入導(dǎo)出庫Magicodes.IE完成Csv導(dǎo)入導(dǎo)出的方法
這篇文章主要介紹了ASP.NET開源導(dǎo)入導(dǎo)出庫Magicodes.IE完成Csv導(dǎo)入導(dǎo)出的方法,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-06-06