using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Office.Core;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
namespace ExcelControl
{
public class ToExcel
{
public string mFilename;
public Microsoft.Office.Interop.Excel.Application app;
public Microsoft.Office.Interop.Excel.Workbooks wbs;
public Microsoft.Office.Interop.Excel.Workbook wb;
public Microsoft.Office.Interop.Excel.Worksheets wss;
public Microsoft.Office.Interop.Excel.Worksheet ws;
public ToExcel()
{
Create();
}
public void Create()//创建一个Microsoft.Office.Interop.Excel对象
{
app = new Microsoft.Office.Interop.Excel.Application();
//设置禁止弹出保存和覆盖的询问提示框
//app.DisplayAlerts = false;
//app.AlertBeforeOverwriting = false;
wbs = app.Workbooks;
wb = wbs.Add(true);
ws = wb.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
}
public bool SaveAs(object FileName)//文档另存为
{
try
{
wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
return true;
}
catch (Exception ex)
{
return false;
}
}
public void Close()//关闭一个Microsoft.Office.Interop.Excel对象,销毁对象
{
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//清空excelSheet対象
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
ws = null;
//清空excelBook対象
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
//清空excelApp対象
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
wbs.Close();
//清空excelBooks対象
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
wbs = null;
GC.Collect();
}
#region 数据表操作
public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)//获取一个工作表
{
Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[SheetName];
return s;
}
public void InsertTable(System.Data.DataTable dt,
string ws, int startX, int startY)
//将内存中数据表格插入到Microsoft.Office.Interop.Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
}
public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)
//将内存中数据表格插入到Microsoft.Office.Interop.Excel指定工作表的指定位置二
{
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
ws.Cells[startX + i, j + startY] = dt.Rows[i][j];
}
}
}
#endregion
#region 设置单元格的值
public void SetCellValue(int x, int y, object value)//X行Y列 value值
{
ws.Cells[x, y] = value;
}
#endregion
#region 设置单元格格式
public void UniteCells(int x1, int y1, int x2, int y2) //合并单元格
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
public void SetCellAlignment(int Startx, int Starty, //单元格对齐方式1
int Endx, int Endy, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
{
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
}
public void SetCellAlignment(int x, int y, //单元格对齐方式2
Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)
{
SetCellAlignment(x, y, x, y, HorizontalAlignment);
}
public void SetCellFont(int Startx, int Starty, //字体1
int Endx, int Endy, int fontSize, bool isBold)
{
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = fontSize;
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Bold = isBold;
}
public void SetCellFont(int x, int y, int fontSize, bool isBold) //字体2
{
SetCellFont(x, y, x, y, fontSize, isBold);
}
public void SetCellBorder(int Startx, int Starty, int Endx, int Endy) //边框
{
Microsoft.Office.Interop.Excel.Range excelRange =
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]);
excelRange.Borders.LineStyle = 1;
}
public void SetCellSize(int Startx, int Starty, int Endx, int Endy, int height) //行高、列宽
{
Microsoft.Office.Interop.Excel.Range excelRange =
ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]);
excelRange.EntireColumn.AutoFit(); //自动调整列宽
excelRange.RowHeight = height;
}
#endregion
}
}
注:需要引用Microsoft.Office.Interop.Excel.dll和OFFICE.DLL