添加引用
using ICSharpCode.SharpZipLib.Zip;
public void CreateDirectory(string DirectoryPath) { if (!Directory.Exists(DirectoryPath)) // 判断当前目录是否存在。 { Directory.CreateDirectory(DirectoryPath); // 建立上传文件存放目录。 } } protected void dlZipDir(string strPath, string strFileName) { MemoryStream ms = null; Response.ContentType = "application/octet-stream"; strFileName = HttpUtility.UrlEncode(strFileName); Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName + ".zip"); ms = new MemoryStream(); zos = new ZipOutputStream(ms); strBaseDir = strPath + ""; addZipEntry(strBaseDir); zos.Finish(); zos.Close(); this.DeleteFolder(strPath); Response.Clear(); Response.BinaryWrite(ms.ToArray()); Response.End(); } protected void addZipEntry(string PathStr) { DirectoryInfo di = new DirectoryInfo(PathStr); foreach (DirectoryInfo item in di.GetDirectories()) { addZipEntry(item.FullName); } foreach (FileInfo item in di.GetFiles()) { FileStream fs = File.OpenRead(item.FullName); byte[] buffer = new byte[fs.Length]; fs.Read(buffer, 0, buffer.Length); string strEntryName = item.FullName.Replace(strBaseDir + "\\", ""); ZipEntry entry = new ZipEntry(strEntryName); zos.PutNextEntry(entry); zos.Write(buffer, 0, buffer.Length); fs.Close(); } } public void DeleteFolder(string dir) { if (Directory.Exists(dir)) //如果存在这个文件夹删除之 { foreach (string d in Directory.GetFileSystemEntries(dir)) { if (File.Exists(d)) File.Delete(d); //直接删除其中的文件 else DeleteFolder(d); //递归删除子文件夹 } Directory.Delete(dir, true); //删除已空文件夹 } } /// <summary> /// 单元格属性 /// </summary> /// <param name="range">单元格对象</param> /// <param name="MergeCells">是否合并</param> /// <param name="Bold">是否字体加粗</param> /// <param name="Num">文本左(0),中(1),右(2)</param> public void ExcelRange(Range range, bool MergeCells, bool Bold, int Num) { //高度 range.RowHeight = 20; //字体大小 range.Font.Size = 10; //是否 单元格合并 还是 自动合并 if (MergeCells == true) { range.MergeCells = true; } //设置字体加粗 if (Bold == true) { range.Font.Bold = true; } //宽度 range.ColumnWidth = 9.4; //字符型 range.NumberFormatLocal = "@"; //设置字体颜色 range.Font.ColorIndex = 0; range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range.HorizontalAlignment = Num == 0 ? Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft : Num == 1 ? Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter : Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; //设置边框样式 range.Borders.LineStyle = XlLineStyle.xlContinuous; } /// <summary> /// 学生信息下载 /// </summary> /// <param name="StudentModel">学生对象</param> /// <param name="FamilyList">家庭成员集合</param> /// <param name="StudentPremiumList">获奖集合</param> /// <param name="ExcelPath">Excel文件绝对路径</param> /// <param name="ImagePath">照片绝对路径</param> protected void OutPortExcel(StudentModel StudentModel, List<FamilyNumberModel> FamilyList, List<StudentPremiumModel> StudentPremiumList, string ExcelPath, string ImagePath) { object miss = Missing.Value; Application excel = new Application(); Workbooks workbooks = excel.Workbooks; Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet worksheet = (Worksheet)workbook.Worksheets.Add(miss, miss, miss, miss); try { excel.Selection.NumberFormatLocal = "@"; //-----------------------------------第一行标题------------------------------------------ worksheet.Cells[1, 1] = "XX学生信息"; //获取标题行的单元格 Range range1 = (Range)worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 10]]; this.ExcelRange(range1, true, true, 1); range1.RowHeight = 30; range1.Font.Size = 20; //-----------------------------------数据行------------------------------------------------- worksheet.Cells[2, 1] = "基本信息"; //获取标题行的单元格 Range range2_1 = (Range)worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[15, 1]]; this.ExcelRange(range2_1, true, true, 0); range2_1.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; range2_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //第2行 range2_1.Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Cells[2, 2] = "中考号"; Range range2_3 = (Range)worksheet.Range[worksheet.Cells[2, 2], worksheet.Cells[2, 2]]; this.ExcelRange(range2_3, false, false, 0); worksheet.Cells[2, 3] ="'"+TextToHtml(StudentModel.StudentCode.ToString()); Range range2_4 = (Range)worksheet.Range[worksheet.Cells[2, 3], worksheet.Cells[2, 4]]; this.ExcelRange(range2_4, true, false, 0); worksheet.Cells[2, 5] = "姓名"; Range range2_5 = (Range)worksheet.Range[worksheet.Cells[2, 5], worksheet.Cells[2, 5]]; this.ExcelRange(range2_5, false, false, 0); worksheet.Cells[2, 6] = TextToHtml(StudentModel.StudentName); Range range2_6 = (Range)worksheet.Range[worksheet.Cells[2, 6], worksheet.Cells[2, 8]]; this.ExcelRange(range2_6, true, false, 0); worksheet.Cells[2, 9] = "照片"; Range range2_9 = (Range)worksheet.Range[worksheet.Cells[2, 9], worksheet.Cells[7, 9]]; this.ExcelRange(range2_6, true, false, 0); //第3行 worksheet.Cells[3, 2] = "毕业学校"; Range range3_2 = (Range)worksheet.Range[worksheet.Cells[3, 2], worksheet.Cells[3, 2]]; this.ExcelRange(range3_2, false, false, 0); worksheet.Cells[3, 3] = TextToHtml(StudentModel.GraduateSchool2.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "") + StudentModel.GraduateSchool.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "")); Range range3_3 = (Range)worksheet.Range[worksheet.Cells[3, 3], worksheet.Cells[3, 4]]; this.ExcelRange(range3_3, true, false, 0); worksheet.Cells[3, 5] = "性别"; Range range3_5 = (Range)worksheet.Range[worksheet.Cells[3, 5], worksheet.Cells[3, 5]]; this.ExcelRange(range3_5, false, false, 0); worksheet.Cells[3, 6] = TextToHtml(StudentModel.SexName); Range range3_6 = (Range)worksheet.Range[worksheet.Cells[3, 6], worksheet.Cells[3, 8]]; this.ExcelRange(range3_6, true, false, 0); //第4行 worksheet.Cells[4, 2] = "籍贯"; Range range4_2 = (Range)worksheet.Range[worksheet.Cells[4, 2], worksheet.Cells[4, 2]]; this.ExcelRange(range4_2, false, false, 0); worksheet.Cells[4, 3] = TextToHtml(StudentModel.LocationName.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "")); Range range4_3 = (Range)worksheet.Range[worksheet.Cells[4, 3], worksheet.Cells[4, 4]]; this.ExcelRange(range4_3, true, false, 0); worksheet.Cells[4, 5] = "名族"; Range range4_5 = (Range)worksheet.Range[worksheet.Cells[4, 5], worksheet.Cells[4, 5]]; this.ExcelRange(range4_5, false, false, 0); worksheet.Cells[4, 6] = TextToHtml(StudentModel.NationName); Range range4_6 = (Range)worksheet.Range[worksheet.Cells[4, 6], worksheet.Cells[4, 8]]; this.ExcelRange(range4_6, true, false, 0); //第4行 worksheet.Cells[5, 2] = "政治面貌"; Range range5_2 = (Range)worksheet.Range[worksheet.Cells[5, 2], worksheet.Cells[5, 2]]; this.ExcelRange(range5_2, false, false, 0); worksheet.Cells[5, 3] = TextToHtml(StudentModel.PoliticalName); Range range5_3 = (Range)worksheet.Range[worksheet.Cells[5, 3], worksheet.Cells[5, 4]]; this.ExcelRange(range5_3, true, false, 0); worksheet.Cells[5, 5] = "入团时间"; Range range5_5 = (Range)worksheet.Range[worksheet.Cells[5, 5], worksheet.Cells[5, 5]]; this.ExcelRange(range4_5, false, false, 0); worksheet.Cells[5, 6] =(StudentModel.PoliticalDate == MinDateTime ? string.Empty : StudentModel.PoliticalDate.ToString(DateFormat)); Range range5_6 = (Range)worksheet.Range[worksheet.Cells[5, 6], worksheet.Cells[5, 8]]; this.ExcelRange(range5_6, true, false, 0); range5_6.NumberFormat = "yyyy-MM-dd"; //第5行 worksheet.Cells[6, 2] = "出生日期"; Range range6_2 = (Range)worksheet.Range[worksheet.Cells[6, 2], worksheet.Cells[6, 2]]; this.ExcelRange(range6_2, false, false, 0); worksheet.Cells[6, 3] =(StudentModel.BirthdayDate == MinDateTime ? string.Empty : StudentModel.BirthdayDate.ToString(DateFormat)); Range range6_3 = (Range)worksheet.Range[worksheet.Cells[6, 3], worksheet.Cells[6, 4]]; this.ExcelRange(range6_3, true, false, 0); range6_3.NumberFormat = "yyyy-MM-dd"; worksheet.Cells[6, 5] = "健康状态"; Range range6_5 = (Range)worksheet.Range[worksheet.Cells[6, 5], worksheet.Cells[6, 5]]; this.ExcelRange(range6_5, false, false, 0); worksheet.Cells[6, 6] = TextToHtml(StudentModel.HealthName); Range range6_6 = (Range)worksheet.Range[worksheet.Cells[6, 6], worksheet.Cells[6, 8]]; this.ExcelRange(range6_6, true, false, 0); //第6行 worksheet.Cells[7, 2] = "户口类型"; Range range7_2 = (Range)worksheet.Range[worksheet.Cells[7, 2], worksheet.Cells[7, 2]]; this.ExcelRange(range7_2, false, false, 0); worksheet.Cells[7, 3] = TextToHtml(StudentModel.LocationAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "")); Range range7_3 = (Range)worksheet.Range[worksheet.Cells[7, 3], worksheet.Cells[7, 4]]; this.ExcelRange(range7_3, true, false, 0); worksheet.Cells[7, 5] = "户口所在地"; Range range7_5 = (Range)worksheet.Range[worksheet.Cells[7, 5], worksheet.Cells[7, 5]]; this.ExcelRange(range7_5, false, false, 0); worksheet.Cells[7, 6] = TextToHtml(StudentModel.BirthDayAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "")); Range range7_6 = (Range)worksheet.Range[worksheet.Cells[7, 6], worksheet.Cells[7, 8]]; this.ExcelRange(range7_6, true, false, 0); //第7行 worksheet.Cells[8, 2] = "出生地"; Range range8_2 = (Range)worksheet.Range[worksheet.Cells[8, 2], worksheet.Cells[8, 2]]; this.ExcelRange(range8_2, false, false, 0); worksheet.Cells[8, 3] = TextToHtml(StudentModel.BirthDayAddress.Replace("╋", "").Replace("├─", "").Replace("├", "").Replace("请选择", "")); Range range8_3 = (Range)worksheet.Range[worksheet.Cells[8, 3], worksheet.Cells[8, 4]]; this.ExcelRange(range8_3, true, false, 0); worksheet.Cells[8, 5] = "血型"; Range range8_5 = (Range)worksheet.Range[worksheet.Cells[8, 5], worksheet.Cells[8, 5]]; this.ExcelRange(range8_5, false, false, 0); worksheet.Cells[8, 6] = TextToHtml(StudentModel.Blood); Range range8_6 = (Range)worksheet.Range[worksheet.Cells[8, 6], worksheet.Cells[8, 10]]; this.ExcelRange(range8_6, true, false, 0); //第8行 worksheet.Cells[9, 2] = "学级"; Range range9_2 = (Range)worksheet.Range[worksheet.Cells[9, 2], worksheet.Cells[9, 2]]; this.ExcelRange(range9_2, false, false, 0); worksheet.Cells[9, 3] = TextToHtml(StudentModel.ScienceSessionName); Range range9_3 = (Range)worksheet.Range[worksheet.Cells[9, 3], worksheet.Cells[9, 4]]; this.ExcelRange(range9_3, true, false, 0); worksheet.Cells[9, 5] = "身份证号"; Range range9_5 = (Range)worksheet.Range[worksheet.Cells[9, 5], worksheet.Cells[9, 5]]; this.ExcelRange(range9_5, false, false, 0); worksheet.Cells[9, 6] ="'"+TextToHtml(StudentModel.IDNumber); Range range9_6 = (Range)worksheet.Range[worksheet.Cells[9, 6], worksheet.Cells[9, 10]]; this.ExcelRange(range9_6, true, false, 0); //第9行 worksheet.Cells[10, 2] = "港澳台侨外"; Range range10_2 = (Range)worksheet.Range[worksheet.Cells[10, 2], worksheet.Cells[10, 2]]; this.ExcelRange(range10_2, false, false, 0); worksheet.Cells[10, 3] = (StudentModel.IsForeign == decimal.One ? "是" : "否"); Range range10_3 = (Range)worksheet.Range[worksheet.Cells[10, 3], worksheet.Cells[10, 4]]; this.ExcelRange(range10_3, true, false, 0); worksheet.Cells[10, 5] = "邮政编码"; Range range10_5 = (Range)worksheet.Range[worksheet.Cells[10, 5], worksheet.Cells[10, 5]]; this.ExcelRange(range10_5, false, false, 0); worksheet.Cells[10, 6] = TextToHtml(StudentModel.PostalCode); Range range10_6 = (Range)worksheet.Range[worksheet.Cells[10, 6], worksheet.Cells[10, 10]]; this.ExcelRange(range10_6, true, false, 0); //第10行 worksheet.Cells[11, 2] = "联系电话"; Range range11_2 = (Range)worksheet.Range[worksheet.Cells[11, 2], worksheet.Cells[11, 2]]; this.ExcelRange(range11_2, false, false, 0); worksheet.Cells[11, 3] = "'"+TextToHtml(StudentModel.Phone); Range range11_3 = (Range)worksheet.Range[worksheet.Cells[11, 3], worksheet.Cells[11, 4]]; this.ExcelRange(range11_3, true, false, 0); worksheet.Cells[11, 5] = "电子邮箱"; Range range11_5 = (Range)worksheet.Range[worksheet.Cells[11, 5], worksheet.Cells[11, 5]]; this.ExcelRange(range11_5, false, false, 0); worksheet.Cells[11, 6] = TextToHtml(StudentModel.Email); Range range11_6 = (Range)worksheet.Range[worksheet.Cells[11, 6], worksheet.Cells[11, 10]]; this.ExcelRange(range11_6, true, false, 0); //第11行 worksheet.Cells[12, 2] = "通信地址"; Range range12_2 = (Range)worksheet.Range[worksheet.Cells[12, 2], worksheet.Cells[12, 2]]; this.ExcelRange(range12_2, false, false, 0); worksheet.Cells[12, 3] = TextToHtml(StudentModel.Address); Range range12_3 = (Range)worksheet.Range[worksheet.Cells[12, 3], worksheet.Cells[12, 10]]; this.ExcelRange(range12_3, true, false, 0); //第12行 worksheet.Cells[13, 2] = "家庭地址"; Range range13_2 = (Range)worksheet.Range[worksheet.Cells[13, 2], worksheet.Cells[3, 2]]; this.ExcelRange(range13_2, false, false, 0); worksheet.Cells[13, 3] = TextToHtml(StudentModel.Address); Range range13_3 = (Range)worksheet.Range[worksheet.Cells[13, 3], worksheet.Cells[13, 10]]; this.ExcelRange(range13_3, true, false, 0); //第13行 worksheet.Cells[14, 2] = "现住地址"; Range range14_2 = (Range)worksheet.Range[worksheet.Cells[14, 2], worksheet.Cells[14, 2]]; this.ExcelRange(range14_2, false, false, 0); worksheet.Cells[14, 3] = TextToHtml(StudentModel.CurrentAddress); Range range14_3 = (Range)worksheet.Range[worksheet.Cells[14, 3], worksheet.Cells[14, 10]]; this.ExcelRange(range14_3, true, false, 0); //第14行 worksheet.Cells[15, 2] = "特长"; Range range15_2 = (Range)worksheet.Range[worksheet.Cells[15, 2], worksheet.Cells[15, 2]]; this.ExcelRange(range15_2, false, false, 0); worksheet.Cells[15, 3] = TextToHtml(StudentModel.Speciality); Range range15_3 = (Range)worksheet.Range[worksheet.Cells[15, 3], worksheet.Cells[15, 10]]; this.ExcelRange(range15_3, true, false, 0); //----------------------------------------扩展信息----------------------------------------------------- worksheet.Cells[16, 1] = "扩展信息"; Range range16_1 = (Range)worksheet.Range[worksheet.Cells[16, 1], worksheet.Cells[16, 1]]; this.ExcelRange(range16_1, false, true, 1); worksheet.Cells[16, 2] = "是否留守儿童"; Range range16_2 = (Range)worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[16, 2]]; this.ExcelRange(range16_2, false, false, 1); worksheet.Cells[16, 3] = (StudentModel.IsVillageStay == decimal.One ? "是" : "否"); Range range16_3 = (Range)worksheet.Range[worksheet.Cells[16, 3], worksheet.Cells[16, 3]]; this.ExcelRange(range16_3, false, false, 0); worksheet.Cells[16, 4] = "是否独生子女"; Range range16_4 = (Range)worksheet.Range[worksheet.Cells[16, 4], worksheet.Cells[16, 4]]; this.ExcelRange(range16_4, false, false, 1); worksheet.Cells[16, 5] = (StudentModel.IsOnlyChild == decimal.One ? "是" : "否"); Range range16_5 = (Range)worksheet.Range[worksheet.Cells[16, 5], worksheet.Cells[16, 5]]; this.ExcelRange(range16_5, false, false, 0); worksheet.Cells[16, 6] = "是否孤儿"; Range range16_6 = (Range)worksheet.Range[worksheet.Cells[16, 6], worksheet.Cells[16, 6]]; this.ExcelRange(range16_6, false, false, 1); worksheet.Cells[16, 7] = (StudentModel.IsOrphan == decimal.One ? "是" : "否"); Range range16_7 = (Range)worksheet.Range[worksheet.Cells[16, 7], worksheet.Cells[16, 10]]; this.ExcelRange(range16_7, true, false, 0); //-------------------------------------------监护人与家庭成员信息--------------------------------------------- worksheet.Cells[17, 1] = "监护人与家庭成员信息"; Range range17_1 = (Range)worksheet.Range[worksheet.Cells[17, 1], worksheet.Cells[22, 1]]; this.ExcelRange(range17_1, true, true, 1); worksheet.Cells[17, 2] = "序号"; Range range17_2 = (Range)worksheet.Range[worksheet.Cells[17, 2], worksheet.Cells[17, 2]]; this.ExcelRange(range17_2, true, false, 1); worksheet.Cells[17, 3] = "关系"; Range range17_3 = (Range)worksheet.Range[worksheet.Cells[17, 3], worksheet.Cells[17, 3]]; this.ExcelRange(range17_3, false, false, 1); worksheet.Cells[17, 4] = "是否监护人"; Range range17_4 = (Range)worksheet.Range[worksheet.Cells[17, 4], worksheet.Cells[17, 4]]; this.ExcelRange(range17_4, false, false, 1); worksheet.Cells[17, 5] = "姓名"; Range range17_5 = (Range)worksheet.Range[worksheet.Cells[17, 5], worksheet.Cells[17, 5]]; this.ExcelRange(range17_5, false, false, 1); worksheet.Cells[17, 6] = "文化程度"; Range range17_6 = (Range)worksheet.Range[worksheet.Cells[17, 6], worksheet.Cells[17, 6]]; this.ExcelRange(range17_6, false, false, 1); worksheet.Cells[17, 7] = "联系电话"; Range range17_7 = (Range)worksheet.Range[worksheet.Cells[17, 7], worksheet.Cells[17, 7]]; this.ExcelRange(range17_7, false, false, 1); worksheet.Cells[17, 8] = "职务"; Range range17_8 = (Range)worksheet.Range[worksheet.Cells[17, 8], worksheet.Cells[17, 8]]; this.ExcelRange(range17_8, false, false, 1); worksheet.Cells[17, 9] = "工作单位"; Range range17_9 = (Range)worksheet.Range[worksheet.Cells[17, 9], worksheet.Cells[17, 10]]; this.ExcelRange(range17_9, true, false, 1); var RowCountIndex = 18;//行的开始位置 var index = 1; foreach (var tt in FamilyList) { worksheet.Cells[RowCountIndex, 2] = index.ToString(); Range range18_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]]; this.ExcelRange(range18_2, false, false, 1); worksheet.Cells[RowCountIndex, 3] = TextToHtml(tt.NumberRelation); Range range18_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]]; this.ExcelRange(range18_3, false, false, 1); worksheet.Cells[RowCountIndex, 4] = (tt.IsGuardianShip == decimal.One ? "是" : "否"); Range range18_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]]; this.ExcelRange(range18_4, false, false, 1); worksheet.Cells[RowCountIndex, 5] = TextToHtml(tt.NemberName); Range range18_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]]; this.ExcelRange(range18_5, false, false, 1); worksheet.Cells[RowCountIndex, 6] = TextToHtml(tt.Education); Range range18_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 6]]; this.ExcelRange(range18_6, false, false, 1); worksheet.Cells[RowCountIndex, 7] = "'"+TextToHtml(tt.Telephone); Range range18_7 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 7], worksheet.Cells[RowCountIndex, 7]]; this.ExcelRange(range18_7, false, false, 0); worksheet.Cells[RowCountIndex, 8] = TextToHtml(tt.Job); Range range18_8 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 8], worksheet.Cells[RowCountIndex, 8]]; this.ExcelRange(range18_8, false, false, 0); worksheet.Cells[RowCountIndex, 9] = TextToHtml(tt.WorkPlace); Range range18_9 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 9], worksheet.Cells[RowCountIndex, 10]]; this.ExcelRange(range18_9, true, false, 0); RowCountIndex++; index++; } for (int i = 0; i < 5 - FamilyList.Count; i++) { worksheet.Cells[RowCountIndex, 2] = index.ToString(); Range range18_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]]; this.ExcelRange(range18_2, false, false, 1); worksheet.Cells[RowCountIndex, 3] = ""; Range range18_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]]; this.ExcelRange(range18_3, false, false, 1); worksheet.Cells[RowCountIndex, 4] = ""; Range range18_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]]; this.ExcelRange(range18_4, false, false, 1); worksheet.Cells[RowCountIndex, 5] = ""; Range range18_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]]; this.ExcelRange(range18_5, false, false, 1); worksheet.Cells[RowCountIndex, 6] = ""; Range range18_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 6]]; this.ExcelRange(range18_6, false, false, 1); worksheet.Cells[RowCountIndex, 7] = ""; Range range18_7 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 7], worksheet.Cells[RowCountIndex, 7]]; this.ExcelRange(range18_7, false, false, 0); worksheet.Cells[RowCountIndex, 8] = ""; Range range18_8 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 8], worksheet.Cells[RowCountIndex, 8]]; this.ExcelRange(range18_8, false, false, 0); worksheet.Cells[RowCountIndex, 9] = ""; Range range18_9 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 9], worksheet.Cells[RowCountIndex, 10]]; this.ExcelRange(range18_9, true, false, 0); RowCountIndex++; index++; } //-------------------------------------------获奖情况--------------------------------------------- worksheet.Cells[23, 1] = "获奖情况"; Range range23_1 = (Range)worksheet.Range[worksheet.Cells[23, 1], worksheet.Cells[28, 1]]; this.ExcelRange(range23_1, true, true, 1); worksheet.Cells[23, 2] = "序号"; Range range23_2 = (Range)worksheet.Range[worksheet.Cells[23, 2], worksheet.Cells[23, 2]]; this.ExcelRange(range23_2, false, false, 1); worksheet.Cells[23, 3] = "获奖名称"; Range range23_3 = (Range)worksheet.Range[worksheet.Cells[23, 3], worksheet.Cells[23, 3]]; this.ExcelRange(range23_3, false, false, 1); worksheet.Cells[23, 4] = "获奖等级"; Range range23_4 = (Range)worksheet.Range[worksheet.Cells[23, 4], worksheet.Cells[23, 4]]; this.ExcelRange(range23_4, false, false, 1); worksheet.Cells[23, 5] = "获奖日期"; Range range23_5 = (Range)worksheet.Range[worksheet.Cells[23, 5], worksheet.Cells[23, 5]]; this.ExcelRange(range23_5, false, false, 1); worksheet.Cells[23, 6] = "获奖单位"; Range range23_6 = (Range)worksheet.Range[worksheet.Cells[23, 6], worksheet.Cells[23, 10]]; this.ExcelRange(range23_6, true, false, 1); RowCountIndex = 24; index = 1; foreach (var item in StudentPremiumList) { worksheet.Cells[RowCountIndex, 2] = index.ToString(); Range range24_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]]; this.ExcelRange(range24_2, false, false, 1); worksheet.Cells[RowCountIndex, 3] = TextToHtml(item.PremiumName); Range range24_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]]; this.ExcelRange(range24_3, false, false, 0); worksheet.Cells[RowCountIndex, 4] = TextToHtml(item.PremiumLevelName); Range range24_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]]; this.ExcelRange(range24_4, false, false, 1); worksheet.Cells[RowCountIndex, 5] =(item.PremiumDate == MinDateTime ? string.Empty : item.PremiumDate.ToString(DateFormat)); Range range24_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]]; this.ExcelRange(range24_5, false, false, 1); range24_5.NumberFormat = "yyyy-MM-dd"; worksheet.Cells[RowCountIndex, 6] = TextToHtml(item.PremiumAddress); Range range24_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 10]]; this.ExcelRange(range24_6, true, false, 0); RowCountIndex++; index++; } for (int i = 0; i < 5 - StudentPremiumList.Count; i++) { worksheet.Cells[RowCountIndex, 2] = index.ToString(); Range range24_2 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 2], worksheet.Cells[RowCountIndex, 2]]; this.ExcelRange(range24_2, false, false, 1); worksheet.Cells[RowCountIndex, 3] = ""; Range range24_3 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 3], worksheet.Cells[RowCountIndex, 3]]; this.ExcelRange(range24_3, false, false, 0); worksheet.Cells[RowCountIndex, 4] = ""; Range range24_4 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 4], worksheet.Cells[RowCountIndex, 4]]; this.ExcelRange(range24_4, false, false, 1); worksheet.Cells[RowCountIndex, 5] = ""; Range range24_5 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 5], worksheet.Cells[RowCountIndex, 5]]; this.ExcelRange(range24_5, false, false, 1); worksheet.Cells[RowCountIndex, 6] = ""; Range range24_6 = (Range)worksheet.Range[worksheet.Cells[RowCountIndex, 6], worksheet.Cells[RowCountIndex, 10]]; this.ExcelRange(range24_6, true, false, 0); RowCountIndex++; index++; } //-------------------------------------------高中三年规划和目标--------------------------------------------- worksheet.Cells[29, 1] = "高中三年规划和目标"; Range range29_1 = (Range)worksheet.Range[worksheet.Cells[29, 1], worksheet.Cells[29, 1]]; range29_1.RowHeight = 80; //字体大小 range29_1.Font.Size = 10; range29_1.Font.Bold = true; //设置字体颜色 range29_1.Font.ColorIndex = 0; //居左 range29_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //设置边框样式 range29_1.Borders.LineStyle = XlLineStyle.xlContinuous; worksheet.Cells[29, 2] =StudentModel.Remark; //TextToHtml() Range range29_2 = (Range)worksheet.Range[worksheet.Cells[29, 2], worksheet.Cells[29, 10]]; range29_2.RowHeight = 80; //字体大小 range29_2.Font.Size = 10; range29_2.MergeCells = true; //设置字体颜色 range29_2.Font.ColorIndex = 0; //居左 range29_2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //设置边框样式 range29_2.Borders.LineStyle = XlLineStyle.xlContinuous; range29_2.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; range29_2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; range29_2.WrapText = true;//自动换行 worksheet.Cells[30, 1] = "请仔细核对以上信息，确保真实有效，如不相符，由学生本人和监护人承担由此造成的一切后果"; Range range30_1 = (Range)worksheet.Range[worksheet.Cells[30, 1], worksheet.Cells[30, 10]]; range30_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; range30_1.Borders.LineStyle = XlLineStyle.xlContinuous; range30_1.MergeCells = true; range30_1.RowHeight = 20; range30_1.Font.ColorIndex = 0; range30_1.Font.Size = 10; worksheet.Cells[31, 1] = "学生签名: 监护人签名： "; Range range31_1 = (Range)worksheet.Range[worksheet.Cells[31, 1], worksheet.Cells[31, 10]]; range31_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight; range31_1.Borders.LineStyle = XlLineStyle.xlContinuous; range31_1.MergeCells = true; range31_1.RowHeight = 20; range31_1.Font.ColorIndex = 0; range31_1.Font.Size = 10; //excel.Visible = true; worksheet.Cells[32, 1] = "年 月 日 "; Range range32_1 = (Range)worksheet.Range[worksheet.Cells[32, 1], worksheet.Cells[32, 10]]; range32_1.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight; range32_1.Borders.LineStyle = XlLineStyle.xlContinuous; range32_1.MergeCells = true; range32_1.RowHeight = 20; range32_1.Font.ColorIndex = 0; range32_1.Font.Size = 10; //------------------------------------照片--------------------------------------------------------- Microsoft.Office.Interop.Excel.Range rangeImage = excel.Range[excel.Cells[2, 9], excel.Cells[7, 10]]; rangeImage.Borders.LineStyle = XlLineStyle.xlContinuous; rangeImage.MergeCells = true; rangeImage.Select(); Microsoft.Office.Interop.Excel.Range pic2 = excel.Range[excel.Cells[2, 9], excel.Cells[7, 10]]; pic2.Select(); float picleft, pictop; picleft = Convert.ToSingle(pic2.Left + 0.35); pictop = Convert.ToSingle(pic2.Top + 1); float width, height; width = 118; height = 115; worksheet.Shapes.AddPicture(ImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, picleft, pictop, width, height); workbook.SaveAs(ExcelPath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); workbook.RefreshAll(); worksheet = null; } catch (Exception err) { this.ShowMessage("操作Excel文件异常: " + Environment.NewLine + err.Message); } finally { if (workbook != null) { workbook.Close(false, Missing.Value, Missing.Value); workbook = null; } if (excel != null) { excel.Quit(); excel = null; } GC.Collect(); } }
