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