Imports System.Data
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports System.Collections.Generic
Partial Class MIT2_AcceptQuery
Inherits System.Web.UI.Page
Dim db As SqlDb = New SqlDb()
Dim ds As DataSet
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
Dim sql = "SELECT DISTINCT 公司編號,公司名稱 FROM MITDEVIL.料號_count"
ddlDataSet(pear, sql, "公司名稱", "公司編號")
End If
End Sub
Sub ddlDataSet(ByVal dll As DropDownList, ByVal sql As String, ByVal text As String, ByVal value As String)
ds = db.FillDataSet(sql)
dll.Items.Clear()
dll.DataSource = ds
dll.DataTextField = text
dll.DataValueField = value
dll.DataBind()
dll.Items.Insert(0, "")
End Sub
Protected Sub BTN_search_Click(sender As Object, e As System.EventArgs) Handles BTN_search.Click
Dim sql As String = "SELECT 驗收單號 ,驗收序號 ,驗收人員 ,CONVERT(varchar(10),資料日期,111) 資料日期 ," & _
" 廠商編號 ,廠商名稱 ,料號 ,品名規格, 數量, 價格 ,單位 ,幣別 ,備註 ,廠別 ,匯率 ,異動類型 ," & _
" CONVERT(varchar(10),異動日期 ,111) 異動日期 ,集團類別 ,Price_Copy,Money,Date_Price_Copy " & _
" FROM [MITDEVIL].[計劃異動] WHERE 1=1 "
If pear.Text = "" Then
System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, GetType(UpdatePanel), "AjaxMsgBox", "alert('請選擇基準公司！');", True)
Exit Sub
Else
sql = sql + " AND 公司編號='" + pear.SelectedItem.Value + "'"
End If
If part_number.Text <> "" Then
sql = sql + " AND 料號='" + part_number.Text + "'"
End If
If stradate.Value <> "" Then
sql = sql + " AND 資料日期 >='" + stradate.Value + "'"
End If
If enddate.Value <> "" Then
sql = sql + " AND 資料日期 <= '" + enddate.Value + "'"
End If
If createdate.Value <> "" Then
sql = sql + " AND convert(char(10),創建日期,111) = convert(char(10),'" + createdate.Value + "',111)"
End If
sql = sql + " ORDER BY 資料日期 DESC "
ViewState("AcceptDT") = db.FillDataSet(sql).Tables(0)
GridView1.DataSource = db.FillDataSet(sql)
ViewState("sql") = sql
GridView1.DataBind()
End Sub
Protected Sub btn_export_Click(sender As Object, e As System.EventArgs) Handles btn_export.Click
Dim arr As New ArrayList
Try
arr.Add("驗收單號")
arr.Add("驗收序號")
arr.Add("驗收人員")
arr.Add("資料日期")
arr.Add("廠商編號")
arr.Add("廠商名稱")
arr.Add("料號")
arr.Add("品名規格")
arr.Add("數量")
arr.Add("價格")
arr.Add("單位")
arr.Add("幣別")
arr.Add("備註")
arr.Add("廠別")
arr.Add("匯率")
arr.Add("異動類型")
arr.Add("異動日期")
arr.Add("集團類別")
arr.Add("價格冊單價")
arr.Add("價格冊幣別")
arr.Add("價格冊生效日期")
SaveDate(CreateExcel(db.FillDataSet(ViewState("sql")), arr), "驗收單資料.xls")
Catch ex As Exception
Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "MsgBox", "alert(轉檔失敗,請重試或者聯繫系統負責人');", True)
' Return
End Try
End Sub
Private Function CreateExcel(ByVal ds As DataSet, ByVal arrs As ArrayList) As NPOI.SS.UserModel.IWorkbook
Dim workbook As IWorkbook = New NPOI.HSSF.UserModel.HSSFWorkbook()
Dim sheet As New ArrayList
Dim sheet1 As NPOI.SS.UserModel.ISheet = workbook.CreateSheet(arrs(0))
sheet.Add(sheet1)
If arrs.Count = 4 Then
Dim sheet4 As NPOI.SS.UserModel.ISheet = workbook.CreateSheet(arrs(3))
sheet.Add(sheet4)
End If
Dim headstyle As ICellStyle = workbook.CreateCellStyle
Dim headfontstyle As HSSFFont = workbook.CreateFont
'Dim textfontstyle As HSSFFont = workbook.CreateFont
Dim textstyle As HSSFCellStyle = workbook.CreateCellStyle
Dim textstyle2 As HSSFCellStyle = workbook.CreateCellStyle
Dim textstyle3 As HSSFCellStyle = workbook.CreateCellStyle
Dim textstyle4 As HSSFCellStyle = workbook.CreateCellStyle
Dim formact As HSSFDataFormat = workbook.CreateDataFormat
Dim arr As New ArrayList
With headfontstyle
.Boldweight = NPOI.SS.UserModel.FontBoldWeight.Bold
End With
'With textfontstyle
' .Boldweight = NPOI.SS.UserModel.FontBoldWeight.Bold
' .Color = NPOI.SS.UserModel.FontColor.Red
'End With
With headstyle
.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center
' .VerticalAlignment = SS.UserModel.VerticalAlignment.Center
'.WrapText = True
.SetFont(headfontstyle)
.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin
.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin
.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin
.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin
End With
With textstyle
.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left
.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin
.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin
.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin
.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin
End With
With textstyle2 '日期格式的
.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center
.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin
.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin
.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin
.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin
.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd")
End With
With textstyle3 '數值帶千分位格式的
.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right
.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin
.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin
.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin
.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin
'.DataFormat = HSSFDataFormat.GetBuiltinFormat("G/通用格式")
.DataFormat = formact.GetFormat("0.00")
End With
With textstyle4 '數值格式的
.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right
.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin
.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin
.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin
.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin
.DataFormat = formact.GetFormat("0")
End With
arr.Add(textstyle)
arr.Add(textstyle2)
arr.Add(textstyle3)
arr.Add(textstyle4)
'開始寫入SHEET1
'创建行
Dim row As NPOI.SS.UserModel.IRow = sheet(0).CreateRow(0)
For k As Integer = 0 To arrs.Count - 1
'创建单元格
Dim cell As NPOI.SS.UserModel.ICell = row.CreateCell(k)
'设置单元格值
cell.SetCellValue(arrs(k).ToString)
cell.CellStyle = headstyle
Next
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
'创建行
row = sheet(0).CreateRow(i + 1)
For j As Integer = 0 To ds.Tables(0).Columns.Count - 1
'创建单元格
Dim cell As NPOI.SS.UserModel.ICell = row.CreateCell(j)
'设置单元格值
'cell.SetCellValue(ds.Tables(0).Rows(i)(field.Item(j).ToString).ToString)
' cell.CellStyle = textstyle
SetCellValueByColumnType(row, j, ds.Tables(0).Rows(i)(j), ds.Tables(0).Columns(j), arr)
Next
Next
For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
sheet(0).AutoSizeColumn(i)
Next
Return workbook
End Function
Private Sub SetCellValueByColumnType(ByVal myExcelRow As NPOI.SS.UserModel.IRow, ByVal myNewCellIndex As Integer, _
ByVal myValue As Object, ByVal myDataColumn As DataColumn, ByVal arr As ArrayList)
If myExcelRow Is Nothing Then Exit Sub
If myNewCellIndex < 0 Then Exit Sub
If myValue Is Nothing Then Exit Sub
If myDataColumn Is Nothing Then Exit Sub
Select Case (myDataColumn.DataType.ToString())
Case "System.String" '://字符串类型
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
Case "System.DateTime" '://日期类型
If Not IsDBNull(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(Convert.ToDateTime(myValue).ToShortDateString)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(1)
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End If
Case "System.Boolean" '://布尔型
If Not IsDBNull(myValue) Then
If Convert.ToBoolean(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue("True")
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue("False")
End If
End If
Case "System.Int16", "System.Int32", "System.Int64" '://整型
If Not IsDBNull(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(Convert.ToInt64(myValue))
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(3)
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End If
Case "System.Byte"
If Not IsDBNull(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(Convert.ToByte(myValue))
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End If
Case "System.Decimal" '://浮点型
If Not IsDBNull(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(Convert.ToDecimal(myValue))
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(2)
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End If
Case "System.Double"
If Not IsDBNull(myValue) Then
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(Convert.ToDouble(myValue))
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(2)
Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End If
Case Else
myExcelRow.CreateCell(myNewCellIndex).SetCellValue(myValue.ToString().Trim)
myExcelRow.GetCell(myNewCellIndex).CellStyle = arr(0)
End Select
End Sub
Private Sub SaveDate(ByVal workbook As NPOI.SS.UserModel.IWorkbook, ByVal ExcelName As String)
Dim ms As IO.MemoryStream
Try
'建立資料流
ms = New IO.MemoryStream()
'寫入資料流
workbook.Write(ms)
'在頁面輸出資料
Dim filename As String = "attachment;filename=" & HttpUtility.UrlEncode(ExcelName, Encoding.UTF8).ToString()
Response.AddHeader("Content-Disposition", filename)
Response.BinaryWrite(ms.ToArray())
Catch ex As Exception
Finally
'釋放資源
If Not ms Is Nothing Then
ms.Dispose()
ms.Close()
End If
End Try
End Sub
Protected Sub GridView1_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
GridView1.PageIndex = e.NewPageIndex
GridView1.DataSource = ViewState("AcceptDT")
GridView1.DataBind()
End Sub
End Cl给gridciew1绑定一个翻页功能
最新发布
博客展示了Btn_clear_Click方法的几种调用方式，包括传入Nothing和System.EventArgs对象，以及使用PerformClick方法触发点击事件。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
