'引入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)建一個(gè)Excel對象
app = New Excel.Application()
wbs = app.Workbooks
wb = wbs.Add(True)
End Sub
Public Sub Open(ByVal FileName As String)
'打開一個(gè)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
'獲取一個(gè)工作表
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
'添加一個(gè)工作表
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)
'刪除一個(gè)工作表
DirectCast(wb.Worksheets(SheetName), Excel.Worksheet).Delete()
End Sub
Public Function ReNameSheet(ByVal OldSheetName As String, ByVal NewSheetName As String) As Excel.Worksheet
'重命名一個(gè)工作表一
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
'重命名一個(gè)工作表二
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è)置一個(gè)單元格的屬性 字體， 大小，顏色 ，對齊方式
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指定工作表的指定位置 為在使用模板時(shí)控制格式時(shí)使用一
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)閉一個(gè)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
