1 Private Function ExportExcel(ByVal strProjGUID As String, ByVal strBldGUIDList As String) As String
2 Try
3
4
5 Dim INT_STARTROW As Integer = 9
6 Dim INT_ENDCOL As Integer = 10
7 Dim INT_STARTCOL As Integer = 7
8
9
10 Dim templateFileName As String = Server.MapPath("/Slxt/CWGL/Excel模板.xls")
11
12
13 Dim workbook As NPOI.HSSF.UserModel.HSSFWorkbook = CreateExcel(templateFileName)
14 Dim ws As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(0)
15 Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.GetSheetAt(1)
16 'Dim wsRange As NPOI.HSSF.UserModel.HSSFSheet = workbook.CreateSheet("ShtDictionary")
17 Dim row As NPOI.HSSF.UserModel.HSSFRow
18 Dim cell As NPOI.HSSF.UserModel.HSSFCell
19 Dim constraint, constraint1 As NPOI.HSSF.UserModel.DVConstraint
20 Dim dataValidation As NPOI.HSSF.UserModel.HSSFDataValidation
21 Dim dataValidation2 As NPOI.HSSF.UserModel.HSSFDataValidation
22
23 Dim styleReadonly As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
24 Dim styleEdit As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
25 Dim font As NPOI.HSSF.UserModel.HSSFFont
26
27 '设置样式变量
28 styleReadonly.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
29 styleReadonly.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
30 styleReadonly.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
31 '1.2.设置字体
32 font = workbook.CreateFont()
33 font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
34 'font.FontHeightInPoints = 11.0
35 font.FontName = "宋体"
36 styleReadonly.SetFont(font)
37 '1.3.设置只读
38 styleReadonly.IsLocked = True
39 '2.设置可编辑单元格样式
40 '2.1.设置单元格背景色
41 styleEdit.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
42 styleEdit.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
43 styleEdit.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index
44 styleEdit.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("@")
45 '2.2.设置字体
46 font = workbook.CreateFont()
47 font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index
48 'font.FontHeightInPoints = 11.0
49 font.FontName = "宋体"
50 styleEdit.SetFont(font)
51 '2.3.设置可编辑
52 styleEdit.IsLocked = False
53
54 '3.0 设置标题行字体
55 Dim styleRed As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
56 Dim fontRed As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
57 '3.1.设置字体
58 fontRed.Color = NPOI.HSSF.Util.HSSFColor.Red.Index
59 fontRed.FontName = "宋体"
60 '4.0 数据行“--”只读且居右
61 Dim noneStyle As NPOI.HSSF.UserModel.HSSFCellStyle = workbook.CreateCellStyle()
62 Dim noneFont As NPOI.HSSF.UserModel.HSSFFont = workbook.CreateFont()
63 '4.1.设置单元格背景色
64 noneStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
65 noneStyle.FillPattern = NPOI.SS.UserModel.FillPattern.ThickBackwardDiagonals
66 noneStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index
67 noneStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right
68 '4.2.设置字体
69 noneFont.Color = NPOI.HSSF.Util.HSSFColor.Black.Index
70 noneFont.FontName = "宋体"
71 noneStyle.SetFont(noneFont)
72 '4.3.设置不可编辑
73 noneStyle.IsLocked = True
74
75
76 Dim strSQL As String = ""
77
78 '先填充表1，用于引用值范围
79 strSQL = " select Jzkj from s_Jzkj where buGUID='" & Session("BUGUID") & "' and IsQy=1 order by JzkjGUID"
80 Dim i, j As Integer
81 Dim dtRange As DataTable = MyDB.GetDataTable(strSQL)
82 Dim rowCountRange As Integer = dtRange.Rows.Count
83 Dim SheetName = "Sheet2"
84
85 For i = 0 To rowCountRange - 1
86 row = GetRow(wsRange, i)
87 cell = GetCell(row, 0)
88 cell.SetCellValue(dtRange.Rows(i).Item("Jzkj").ToString)
89 Next
90
91 cell = GetCell(GetRow(wsRange, 0), 1)
92 cell.SetCellValue("未结转")
93
94 cell = GetCell(GetRow(wsRange, 1), 1)
95 cell.SetCellValue("预结转")
96
97 cell = GetCell(GetRow(wsRange, 2), 1)
98 cell.SetCellValue("结转")
99 'wsRange.ProtectSheet("slxt")
100
101 Dim range1 As NPOI.SS.UserModel.IName = workbook.CreateName()
102 range1.RefersToFormula = String.Format("Sheet2!$A$1:$A${0}", rowCountRange)
103 range1.NameName = "TypeRange"
104
105 Dim range2 As NPOI.SS.UserModel.IName = workbook.CreateName()
106 range2.RefersToFormula = "Sheet2!$B$1:$B$3"
107 range2.NameName = "StatusRange"
108
109
110 strBldGUIDList = strBldGUIDList.Replace(";", "','")
111
112 strSQL = "select " & _
113 "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 " & _
114 "FROM vs_SaleCarryOver WHERE projGUID='" & strProjGUID & "' AND CarryOverStatus='未结转' and bldGUID in('" & strBldGUIDList & "')"
115 Dim dtTemp As DataTable = MyDB.GetDataTable(strSQL)
116
117 '插入记录行
118
119
120
121 Dim rowCount As Integer = dtTemp.Rows.Count
122
123 For i = 0 To rowCount - 1
124 row = GetRow(ws, i + INT_STARTROW)
125 For j = 0 To INT_ENDCOL
126 cell = GetCell(row, j)
127 cell.SetCellValue(dtTemp.Rows(i)(j).ToString)
128 If j < 6 Then
129 cell.CellStyle = noneStyle
130 Else
131 cell.CellStyle = styleEdit
132 End If
133 Next
134
135 Next
136
137 constraint = NPOI.HSSF.UserModel.DVConstraint.CreateNumericConstraint(NPOI.HSSF.UserModel.DVConstraint.ValidationType.DECIMAL, NPOI.HSSF.UserModel.DVConstraint.OperatorType.BETWEEN, "0", "99999999999.99")
138 //constraint = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("TypeRange")
139 dataValidation = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 7, 7), constraint)
140 ws.AddValidationData(dataValidation)
141
142 constraint1 = NPOI.HSSF.UserModel.DVConstraint.CreateFormulaListConstraint("StatusRange")
143 dataValidation2 = New NPOI.HSSF.UserModel.HSSFDataValidation(New NPOI.SS.Util.CellRangeAddressList(INT_STARTROW, 65535, 6, 6), constraint1)
144 ws.AddValidationData(dataValidation2)
145
146 ws.ProtectSheet("slxt")
147
148
149 Dim strFileName As String = "/TempFiles/" & CInt(Int(&H7FFFFFFF * Rnd(9999) + 1)).ToString & ".xls"
150 Using fs As New FileStream(Server.MapPath(strFileName), FileMode.Create)
151 workbook.Write(fs)
152 fs.Close()
153 End Using
154 Return String.Format("OK|{0}|{1}", strFileName, Date.Now.ToString("yyyy-mm-dd"))
155 Catch ex As Exception
156 Return String.Format("FAIL|", ex.Message)
157 End Try
158
159 If MyDB.GetDataItemInt(strSQL) = 0 Then
160 Return "OK"
161 Else
162 Return "NO"
163 End If
164
165 ''
166 End Function
