Private Function ExportExcel(ByVal strProjGUID As String, ByVal strBldGUIDList As String) As String
Try
Dim INT_STARTROW As Integer = 9
Dim INT_ENDCOL As Integer = 10
Dim INT_STARTCOL As Integer = 7
Dim templateFileName As String = Server.MapPath("/Slxt/CWGL/Excel模板.xls")
Dim workbook As NPOI.HSSF.UserModel.HSSFWorkbook = CreateExcel(templateFileName)
Dim ws As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(0)
Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(1)
'Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.CreateSheet("ShtDictionary")
Dim row As NPOI.HSSF.UserModel.HSSFRow
Dim cell As NPOI.HSSF.UserModel.HSSFCell
Dim constraint, constraint1 As NPOI.HSSF.UserModel.DVConstraint
Dim dataValidation As NPOI.HSSF.UserModel.HSSFDataValidation
Dim dataValidation2 As NPOI.HSSF.UserModel.HSSFDataValidation
Dim styleReadonly As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
Dim styleEdit As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
Dim font As NPOI.HSSF.UserModel.HSSFFont
'設置樣式變量
styleReadonly.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
styleReadonly.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
styleReadonly.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
'1.2.設置字體
font = workbook.CreateFont()
font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
'font.FontHeightInPoints = 11.0
font.FontName = "宋體"
styleReadonly.SetFont(font)
'1.3.設置只讀
styleReadonly.IsLocked = True
'2.設置可編輯單元格樣式
'2.1.設置單元格背景色
styleEdit.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
styleEdit.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
styleEdit.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
styleEdit.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@")
'2.2.設置字體
font = workbook.CreateFont()
font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index
'font.FontHeightInPoints = 11.0
font.FontName = "宋體"
styleEdit.SetFont(font)
'2.3.設置可編輯
styleEdit.IsLocked = False
'3.0 設置標題行字體
Dim styleRed As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
Dim fontRed As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
'3.1.設置字體
fontRed.Color = NPOI.HSSF.Util.HSSFColor.Red.Index
fontRed.FontName = "宋體"
'4.0 數據行“--”只讀且居右
Dim noneStyle As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
Dim noneFont As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
'4.1.設置單元格背景色
noneStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
noneStyle.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
noneStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
noneStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right
'4.2.設置字體
noneFont.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
noneFont.FontName = "宋體"
noneStyle.SetFont(noneFont)
'4.3.設置不可編輯
noneStyle.IsLocked = True
Dim strSQL As String = ""
'先填充表1，用於引用值范圍
strSQL = " select Jzkj from s_Jzkj where buGUID='" & Session("BUGUID") & "' and IsQy=1 order by JzkjGUID"
Dim i, j As Integer
Dim dtRange As DataTable = MyDB.GetDataTable(strSQL)
Dim rowCountRange As Integer = dtRange.Rows.Count
Dim SheetName = "Sheet2"
For i = 0 To rowCountRange - 1
row = GetRow(wsRange, i)
cell = GetCell(row, 0)
cell.SetCellValue(dtRange.Rows(i).Item("Jzkj").ToString)
Next
cell = GetCell(GetRow(wsRange, 0), 1)
cell.SetCellValue("未結轉")
cell = GetCell(GetRow(wsRange, 1), 1)
cell.SetCellValue("預結轉")
cell = GetCell(GetRow(wsRange, 2), 1)
cell.SetCellValue("結轉")
'wsRange.ProtectSheet("slxt")
Dim range1 As NPOI.SS.UserModel.IName = workbook.CreateName()
range1.RefersToFormula = String.Format("Sheet2!$A$1:$A${0}", rowCountRange)
range1.NameName = "TypeRange"
Dim range2 As NPOI.SS.UserModel.IName = workbook.CreateName()
range2.RefersToFormula = "Sheet2!$B$1:$B$3"
range2.NameName = "StatusRange"
strBldGUIDList = strBldGUIDList.Replace(";", "','")
strSQL = "select " & _
"recordGUID,ProjName,AreaName,BldName,RoomCode,RoomInfo,CarryOverStatus,CarryOverType,isnull(CarryOverMonth,'') as CarryOverMonth, isnull(LEFT(CarryOverMonth,4),'') AS CarryOverYear, convert(varchar(10),FactJFDate,120) as FactJFDate " & _
"FROM vs_SaleCarryOver WHERE projGUID='" & strProjGUID & "' AND CarryOverStatus='未結轉' and bldGUID in('" & strBldGUIDList & "')"
Dim dtTemp As DataTable = MyDB.GetDataTable(strSQL)
'插入記錄行
Dim rowCount As Integer = dtTemp.Rows.Count
For i = 0 To rowCount - 1
row = GetRow(ws, i + INT_STARTROW)
For j = 0 To INT_ENDCOL
cell = GetCell(row, j)
cell.SetCellValue(dtTemp.Rows(i)(j).ToString)
If j < 6 Then
cell.CellStyle = noneStyle
Else
cell.CellStyle = styleEdit
End If
Next
Next
constraint = NPOI.HSSF.UserModel.DVConstraint.CreateNumericConstraint(NPOI.HSSF.UserModel.DVConstraint.ValidationType.DECIMAL, NPOI.HSSF.UserModel.DVConstraint.OperatorType.BETWEEN, "0", "99999999999.99")
//constraint = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("TypeRange")
dataValidation = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 7, 7), constraint)
ws.AddValidationData(dataValidation)
constraint1 = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("StatusRange")
dataValidation2 = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 6, 6), constraint1)
ws.AddValidationData(dataValidation2)
ws.ProtectSheet("slxt")
Dim strFileName As String = "/TempFiles/" & CInt(Int(&H7FFFFFFF * Rnd(9999) + 1)).ToString & ".xls"
Using fs As New FileStream(Server.MapPath(strFileName), FileMode.Create)
workbook.Write(fs)
fs.Close()
End Using
Return String.Format("OK|{0}|{1}", strFileName, Date.Now.ToString("yyyy-mm-dd"))
Catch ex As Exception
Return String.Format("FAIL|", ex.Message)
End Try
If MyDB.GetDataItemInt(strSQL) = 0 Then
Return "OK"
Else
Return "NO"
End If
''
End Function
