Excel操作类(VB.NET)

(声明：魏滔序原创，转贴请注明出处。)

'引入Excel的COM组件

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Core

Namespace ExcelEdit
 ''' <summary>
 ''' ExcelEdit 的摘要说明
 ''' </summary>
 Public Class ExcelEdit
  Public mFilename As String
  Public app As Excel.Application
  Public wbs As Excel.Workbooks
  Public wb As Excel.Workbook
  Public wss As Excel.Worksheets
  Public ws As Excel.Worksheet
    '
    ' TODO: 在此处添加构造函数逻辑
    '
  Public Sub New()
  End Sub
  Public Sub Create()
  '创建一个Excel对象
   app = New Excel.Application()
   wbs = app.Workbooks
   wb = wbs.Add(True)
  End Sub
  Public Sub Open(ByVal FileName As String)
  '打开一个Excel文件
   app = New Excel.Application()
   wbs = app.Workbooks
   wb = wbs.Add(FileName)
   'wb = wbs.Open(FileName,  0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "/t", false, false, 0, true,Type.Missing,Type.Missing);
   'wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
   mFilename = FileName
  End Sub
  Public Function GetSheet(ByVal SheetName As String) As Excel.Worksheet
  '获取一个工作表
   Dim s As Excel.Worksheet = DirectCast(wb.Worksheets(SheetName), Excel.Worksheet)
   Return s
  End Function
  Public Function AddSheet(ByVal SheetName As String) As Excel.Worksheet
  '添加一个工作表
   Dim s As Excel.Worksheet = DirectCast(wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)
   s.Name = SheetName
   Return s
  End Function

  Public Sub DelSheet(ByVal SheetName As String)
  '删除一个工作表
   DirectCast(wb.Worksheets(SheetName), Excel.Worksheet).Delete()
  End Sub
  Public Function ReNameSheet(ByVal OldSheetName As String, ByVal NewSheetName As String) As Excel.Worksheet
  '重命名一个工作表一
   Dim s As Excel.Worksheet = DirectCast(wb.Worksheets(OldSheetName), Excel.Worksheet)
   s.Name = NewSheetName
   Return s
  End Function

  Public Function ReNameSheet(ByVal Sheet As Excel.Worksheet, ByVal NewSheetName As String) As Excel.Worksheet
  '重命名一个工作表二

   Sheet.Name = NewSheetName

   Return Sheet
  End Function

  Public Sub SetCellValue(ByVal ws As Excel.Worksheet, ByVal x As Integer, ByVal y As Integer, ByVal value As Object)
  'ws：要设值的工作表     X行Y列     value   值
   ws.Cells(x, y) = value
  End Sub
  Public Sub SetCellValue(ByVal ws As String, ByVal x As Integer, ByVal y As Integer, ByVal value As Object)
  'ws：要设值的工作表的名称 X行Y列 value 值

   GetSheet(ws).Cells(x, y) = value
  End Sub

  Public Sub SetCellProperty(ByVal ws As Excel.Worksheet, ByVal Startx As Integer, ByVal Starty As Integer, ByVal Endx As Integer, ByVal Endy As Integer, ByVal size As Integer, _
   ByVal name As String, ByVal color As Excel.Constants, ByVal HorizontalAlignment As Excel.Constants)
  '设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
   name = "宋体"
   size = 12
   color = Excel.Constants.xlAutomatic
   HorizontalAlignment = Excel.Constants.xlRight
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Name = name
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Size = size
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Color = color
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).HorizontalAlignment = HorizontalAlignment
  End Sub

  Public Sub SetCellProperty(ByVal wsn As String, ByVal Startx As Integer, ByVal Starty As Integer, ByVal Endx As Integer, ByVal Endy As Integer, ByVal size As Integer, _
   ByVal name As String, ByVal color As Excel.Constants, ByVal HorizontalAlignment As Excel.Constants)
   'name = "宋体";
   'size = 12;
   'color = Excel.Constants.xlAutomatic;
   'HorizontalAlignment = Excel.Constants.xlRight;

   Dim ws As Excel.Worksheet = GetSheet(wsn)
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Name = name
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Size = size
   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).Font.Color = color

   ws.get_Range(ws.Cells(Startx, Starty), ws.Cells(Endx, Endy)).HorizontalAlignment = HorizontalAlignment
  End Sub

  Public Sub UniteCells(ByVal ws As Excel.Worksheet, ByVal x1 As Integer, ByVal y1 As Integer, ByVal x2 As Integer, ByVal y2 As Integer)
  '合并单元格
   ws.get_Range(ws.Cells(x1, y1), ws.Cells(x2, y2)).Merge(Type.Missing)
  End Sub

  Public Sub UniteCells(ByVal ws As String, ByVal x1 As Integer, ByVal y1 As Integer, ByVal x2 As Integer, ByVal y2 As Integer)
  '合并单元格
   GetSheet(ws).get_Range(GetSheet(ws).Cells(x1, y1), GetSheet(ws).Cells(x2, y2)).Merge(Type.Missing)

  End Sub

  Public Sub InsertTable(ByVal dt As System.Data.DataTable, ByVal ws As String, ByVal startX As Integer, ByVal startY As Integer)
  '将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
   For i As Integer = 0 To dt.Rows.Count - 1

    For j As Integer = 0 To dt.Columns.Count - 1

     GetSheet(ws).Cells(startX + i, j + startY) = dt.Rows(i)(j).ToString()

    Next
   Next

  End Sub
  Public Sub InsertTable(ByVal dt As System.Data.DataTable, ByVal ws As Excel.Worksheet, ByVal startX As Integer, ByVal startY As Integer)
  '将内存中数据表格插入到Excel指定工作表的指定位置二
   For i As Integer = 0 To dt.Rows.Count - 1

    For j As Integer = 0 To dt.Columns.Count - 1

     ws.Cells(startX + i, j + startY) = dt.Rows(i)(j)

    Next
   Next

  End Sub

  Public Sub AddTable(ByVal dt As System.Data.DataTable, ByVal ws As String, ByVal startX As Integer, ByVal startY As Integer)
  '将内存中数据表格添加到Excel指定工作表的指定位置一
   For i As Integer = 0 To dt.Rows.Count - 1

    For j As Integer = 0 To dt.Columns.Count - 1

     GetSheet(ws).Cells(i + startX, j + startY) = dt.Rows(i)(j)

    Next
   Next

  End Sub
  Public Sub AddTable(ByVal dt As System.Data.DataTable, ByVal ws As Excel.Worksheet, ByVal startX As Integer, ByVal startY As Integer)
  '将内存中数据表格添加到Excel指定工作表的指定位置二
   For i As Integer = 0 To dt.Rows.Count - 1

    For j As Integer = 0 To dt.Columns.Count - 1

     ws.Cells(i + startX, j + startY) = dt.Rows(i)(j)
    Next
   Next

  End Sub
  Public Sub InsertPictures(ByVal Filename As String, ByVal ws As String)
  '插入图片操作一
   GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, _
    150)
   '后面的数字表示位置
  End Sub

  'public void InsertPictures(string Filename, string ws, int Height, int Width)//插入图片操作二
  '{
  '    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
  '}
  'public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)//插入图片操作三
  '{

  '    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;
  '    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;
  '}
Public Sub InsertActiveChart(ByVal ChartType As Excel.XlChartType, ByVal ws As String, ByVal DataSourcesX1 As Integer, ByVal DataSourcesY1 As Integer, ByVal DataSourcesX2 As Integer, ByVal DataSourcesY2 As Integer, _
   ByVal ChartDataType As Excel.XlRowCol)
  '插入图表操作
   ChartDataType = Excel.XlRowCol.xlColumns
   wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing)
   wb.ActiveChart.ChartType = ChartType
   wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells(DataSourcesX1, DataSourcesY1), GetSheet(ws).Cells(DataSourcesX2, DataSourcesY2)), ChartDataType)
   wb.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, ws)
  End Sub
  Public Function Save() As Boolean
  '保存文档
   If mFilename = "" Then
    Return False
   Else
    Try
     wb.Save()
     Return True
    Catch ex As Exception

     Return False
    End Try
   End If
  End Function
  Public Function SaveAs(ByVal FileName As Object) As Boolean
  '文档另存为
   Try
    wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
     Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

    Return True
   Catch ex As Exception

    Return False
   End Try
  End Function
  Public Sub Close()
  '关闭一个Excel对象，销毁对象
   'wb.Save();
   wb.Close(Type.Missing, Type.Missing, Type.Missing)
   wbs.Close()
   app.Quit()
   wb = Nothing
   wbs = Nothing
   app = Nothing
   GC.Collect()
  End Sub
 End Class
End Namespace