Office中国论坛/Access中国论坛
标题:
asp.net保存文件方法请教
[打印本页]
作者:
zxp
时间:
2009-9-11 09:06
标题:
asp.net保存文件方法请教
asp.net中我想把DataGrid中的数据导出到EXCEL中,提示保存文件对话框。(我是用VB语言来开发),有谁知道这个该怎么做吗?
作者:
fan0217
时间:
2009-9-15 10:42
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports Excel
Namespace FJPMIS.priceClient.Public
Class ExportExcel
'
''' <summary>
''' 将DataGridView中的数据导出到Excel中,并加载显示出来(无加载模板)
''' 只用于一般的导出Excel
''' </summary>
''' <param name="caption">要显示的页头</param>
''' <param name="date">打印日期</param>
''' <param name="dgv">要进行导出的DataGridView</param>
Public Sub ExportToExcel(ByVal caption As String, ByVal [date] As String, ByVal dgv As DataGridView)
'DataGridView可见列数
Dim visiblecolumncount As Integer = 0
For i As Integer = 0 To dgv.Columns.Count - 1
If dgv.Columns(i).Visible = True AndAlso (TypeOf dgv.Columns(i) Is DataGridViewTextBoxColumn) Then
visiblecolumncount += 1
End If
Next
Try
'当前操作列的索引
Dim currentcolumnindex As Integer = 1
'当前操作行的索引
Dim Mylxls As New Excel.ApplicationClass()
Mylxls.Application.Workbooks.Add(True)
'Mylxls.Cells.Font.Size = 10.5; //设置默认字体大小
'设置标头
Mylxls.Caption = caption
'显示表头
Mylxls.Cells(1, 1) = caption
'显示时间
Mylxls.Cells(2, 1) = [date]
For i As Integer = 0 To dgv.Columns.Count - 1
If dgv.Columns(i).Visible = True AndAlso (TypeOf dgv.Columns(i) Is DataGridViewTextBoxColumn) Then
'如果显示
Mylxls.Cells(3, currentcolumnindex) = dgv.Columns(i).HeaderText
Mylxls.get_Range(Mylxls.Cells(3, currentcolumnindex), Mylxls.Cells(3, currentcolumnindex)).Cells.Borders.LineStyle = 1
'设置边框
Mylxls.get_Range(Mylxls.Cells(3, currentcolumnindex), Mylxls.Cells(3, currentcolumnindex)).ColumnWidth = dgv.Columns(i).Width / 8
'Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).Font.Bold = true; //粗体
'Mylxls.get_Range(Mylxls.Cells[3, currentcolumnindex], Mylxls.Cells[3, currentcolumnindex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中显示
currentcolumnindex += 1
End If
Next
Mylxls.get_Range(Mylxls.Cells(1, 1), Mylxls.Cells(1, visiblecolumncount)).MergeCells = True
'合并单元格
Mylxls.get_Range(Mylxls.Cells(1, 1), Mylxls.Cells(1, 1)).RowHeight = 30
'行高
'Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Name = "黑体";
'Mylxls.get_Range(Mylxls.Cells[1, 1], Mylxls.Cells[1, 1]).Font.Size = 14; //字体大小
Mylxls.get_Range(Mylxls.Cells(1, 1), Mylxls.Cells(1, visiblecolumncount)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
'居中显示
Mylxls.get_Range(Mylxls.Cells(2, 1), Mylxls.Cells(2, 2)).MergeCells = True
'合并
Mylxls.get_Range(Mylxls.Cells(2, 1), Mylxls.Cells(2, 2)).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
'左边显示
'Mylxls.get_Range(Mylxls.Cells[1, 2], Mylxls.Cells[1, 2]).ColumnWidth = 12; //列宽度
Dim dataArray As Object(,) = New Object(dgv.Rows.Count - 1, visiblecolumncount - 1) {}
'当前操作列的索引
'int currentcolumnindex = 1;
'当前操作行的索引
For i As Integer = 0 To dgv.Rows.Count - 1
'循环填充数据
currentcolumnindex = 1
For j As Integer = 0 To dgv.Columns.Count - 1
If dgv.Columns(j).Visible = True AndAlso (TypeOf dgv.Columns(j) Is DataGridViewTextBoxColumn) Then
If dgv(j, i).Value IsNot Nothing Then
'如果单元格内容不为空
dataArray(i, currentcolumnindex - 1) = dgv(j, i).Value.ToString()
End If
currentcolumnindex += 1
End If
Next
Next
Mylxls.get_Range(Mylxls.Cells(4, 1), Mylxls.Cells(dgv.Rows.Count + 3, visiblecolumncount)).Value2 = dataArray
'设置边框
Mylxls.get_Range(Mylxls.Cells(4, 1), Mylxls.Cells(dgv.Rows.Count + 3, visiblecolumncount)).Cells.Borders.LineStyle = 1
'设置边框
Mylxls.Visible = True
Catch
MessageBox.Show("信息导出失败,请确认你的机子上装有Microsoft Office Excel 2003!", "错误", MessageBoxButtons.OK, MessageBoxIcon.[Error])
Finally
End Try
End Sub
'
''' <summary>
''' 将DataGridView中的数据导出到Excel中,并加载显示出来(加载模板)
''' 仅用于导出已定义好模版的Excel导出,主要如“旬报表”,“月报表”等
''' 请注意:模板应放在应程序的PrintTemplate目录下
''' </summary>
''' <param name="ModelName">模版的名称</param>
''' <param name="Date">打印日期</param>
''' <param name="dgv">要进行导出的DataGridView</param>
Public Sub ExportToExcelByModel(ByVal ModelName As String, ByVal [Date] As String, ByVal dgv As DataGridView)
Dim m_objExcel As Excel.Application = Nothing
Dim m_objBook As Excel._Workbook = Nothing
Dim m_objSheets As Excel.Sheets = Nothing
Dim m_objSheet As Excel._Worksheet = Nothing
Dim m_objOpt As Object = System.Reflection.Missing.Value
Try
m_objExcel = New Excel.Application()
Dim path As String = System.Windows.Forms.Application.StartupPath.ToString().Replace(vbBack & "in" & vbNullChar & "ebug", "") & vbNullChar & "rintTemplate"";"
path = path + ModelName
m_objBook = m_objExcel.Workbooks.Open(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, _
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, _
m_objOpt, m_objOpt, m_objOpt)
m_objSheets = DirectCast(m_objBook.Worksheets, Excel.Sheets)
m_objSheet = DirectCast((m_objSheets.get_Item(1)), Excel._Worksheet)
'填充日期
m_objExcel.Cells(2, 1) = [Date].ToString()
'当前操作列的索引
Dim currentcolumnindex As Integer = 1
'当前操作行的索引
Dim currentrowindex As Integer = 4
For i As Integer = 0 To dgv.Rows.Count - 1
'循环填充数据
currentcolumnindex = 1
currentrowindex = 4 + i
For j As Integer = 0 To dgv.Columns.Count - 1
If dgv.Columns(j).Visible = True Then
If dgv(j, i).Value IsNot Nothing Then
'如果单元格内容不为空
m_objExcel.Cells(currentrowindex, currentcolumnindex) = dgv(j, i).Value.ToString()
End If
m_objExcel.get_Range(m_objExcel.Cells(currentrowindex, currentcolumnindex), m_objExcel.Cells(currentrowindex, currentcolumnindex)).Cells.Borders.LineStyle = 1
'设置边框
currentcolumnindex += 1
End If
Next
Next
m_objExcel.DisplayAlerts = False
m_objExcel.Visible = True
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel)
Catch
MessageBox.Show("信息导出失败,请确认你的机子上装有Microsoft Office Excel 2003并且模版未被删除!", "错误", MessageBoxButtons.OK, MessageBoxIcon.[Error])
Finally
GC.Collect()
End Try
End Sub
End Class
End Namespace
复制代码
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3