Excel操作類(VB.NET)

趨明 2012-03-27

展開全文

(聲明：魏滔序原創(chuàng)，轉(zhuǎn)貼請注明出處。)

'引入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: 在此處添加構(gòu)造函數(shù)邏輯
    '
  Public Sub New()
  End Sub
  Public Sub Create()
  '創(chuàng)建一個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：要設(shè)值的工作表     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：要設(shè)值的工作表的名稱 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)
  '設(shè)置一個單元格的屬性   字體，   大小，顏色   ，對齊方式
   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)
  '將內(nèi)存中數(shù)據(jù)表格插入到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)
  '將內(nèi)存中數(shù)據(jù)表格插入到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)
  '將內(nèi)存中數(shù)據(jù)表格添加到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)
  '將內(nèi)存中數(shù)據(jù)表格添加到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)
   '后面的數(shù)字表示位置
  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()
  '關(guān)閉一個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