一个DataGrid里有两张表的数据，导出成一张表
1 protected void btnExcel_Click(object sender, EventArgs e) 2 { 3 InfoExport(); 4 } 5 6 protected void InfoExport() 7 { 8 try 9 { 10 string ExcelName = this.CreateExcel(); 11 //将服务器上的Excel导出 12 // CuteWebUIOperate.DownloadFile(HttpContext.Current, Server.MapPath("ExcelExport/") + ExcelName, ExcelName, false); 13 string strScript = "window.open('ExcelExport/" + ExcelName + "');"; 14 this.WriteAjaxMessage(strScript); 15 } 16 catch 17 { 18 throw; 19 } 20 } 21 22 #region 导出excel 23 protected string CreateExcel() //生成Excel 24 { 25 string Header = "考试题库"; 26 string strFileName = ""; 27 28 // 生成文件夹 29 string fileFolderPath = Server.MapPath("ExcelExport/"); 30 if (!System.IO.Directory.Exists(fileFolderPath)) 31 System.IO.Directory.CreateDirectory(fileFolderPath); 32 33 Workbook wb = new Workbook(); 34 35 wb.Worksheets.Add("Sheet1"); 36 37 Worksheet ws = wb.ActiveWorksheet; 38 WorksheetMergedCellsRegionCollection wm = ws.MergedCellsRegions; 39 #region 40 WorksheetMergedCellsRegion wmc = wm.Add(0, 0, 0, 12);//起始位置和终止位置 41 42 wmc.Value = Header; 43 wmc.CellFormat.Alignment = HorizontalCellAlignment.Center; 44 wmc.CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 45 wmc.CellFormat.BottomBorderColor = Color.Black; 46 wmc.CellFormat.LeftBorderColor = Color.Black; 47 wmc.CellFormat.RightBorderColor = Color.Black; 48 wmc.CellFormat.TopBorderColor = Color.Black; 49 50 wmc.CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 51 wmc.CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 52 wmc.CellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 53 wmc.CellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 54 wmc.CellFormat.WrapText = ExcelDefaultableBoolean.True; 55 wmc.CellFormat.Font.Name = "宋体"; 56 //字体大小 57 wmc.CellFormat.Font.Height = 300; 58 IWorksheetCellFormat HeadCellFormat = wb.CreateNewWorksheetCellFormat(); 59 HeadCellFormat.Alignment = HorizontalCellAlignment.Center; 60 HeadCellFormat.Font.Bold = ExcelDefaultableBoolean.True; 61 HeadCellFormat.Font.Name = "宋体"; 62 63 HeadCellFormat.BottomBorderColor = Color.Black; 64 HeadCellFormat.LeftBorderColor = Color.Black; 65 HeadCellFormat.RightBorderColor = Color.Black; 66 HeadCellFormat.TopBorderColor = Color.Black; 67 68 HeadCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 69 HeadCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 70 HeadCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 71 HeadCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 72 HeadCellFormat.WrapText = ExcelDefaultableBoolean.True; 73 74 IWorksheetCellFormat ItemCellFormat = wb.CreateNewWorksheetCellFormat(); 75 //CellFormat.Alignment = HorizontalCellAlignment.Center; 76 //CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 77 ItemCellFormat.FillPattern = FillPatternStyle.Default; 78 ItemCellFormat.ShrinkToFit = ExcelDefaultableBoolean.True; 79 ItemCellFormat.BottomBorderColor = Color.Black; 80 ItemCellFormat.LeftBorderColor = Color.Black; 81 ItemCellFormat.RightBorderColor = Color.Black; 82 ItemCellFormat.TopBorderColor = Color.Black; 83 84 ItemCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 85 ItemCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 86 ItemCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 87 ItemCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 88 ItemCellFormat.WrapText = ExcelDefaultableBoolean.True; 89 ItemCellFormat.FormatString = "##,##0.00"; 90 ItemCellFormat.Font.Name = "宋体"; 91 #endregion 92 int n; 93 n = 0; 94 #region 95 wmc = wm.Add(1, n, 1, n++); 96 wmc.Value = "序号"; 97 wmc.CellFormat.SetFormatting(HeadCellFormat); 98 99 wmc = wm.Add(1, n, 1, n++); 100 wmc.Value = "题目名称"; 101 wmc.CellFormat.SetFormatting(HeadCellFormat); 102 103 wmc = wm.Add(1, n, 1, n++); 104 wmc.Value = "题目类型"; 105 wmc.CellFormat.SetFormatting(HeadCellFormat); 106 107 wmc = wm.Add(1, n, 1, n++); 108 wmc.Value = "题目难度"; 109 wmc.CellFormat.SetFormatting(HeadCellFormat); 110 111 wmc = wm.Add(1, n, 1, n++); 112 wmc.Value = "答案1"; 113 wmc.CellFormat.SetFormatting(HeadCellFormat); 114 115 wmc = wm.Add(1, n, 1, n++); 116 wmc.Value = "答案2"; 117 wmc.CellFormat.SetFormatting(HeadCellFormat); 118 119 wmc = wm.Add(1, n, 1, n++); 120 wmc.Value = "答案3"; 121 wmc.CellFormat.SetFormatting(HeadCellFormat); 122 123 wmc = wm.Add(1, n, 1, n++); 124 wmc.Value = "答案4"; 125 wmc.CellFormat.SetFormatting(HeadCellFormat); 126 127 wmc = wm.Add(1, n, 1, n++); 128 wmc.Value = "答案5"; 129 wmc.CellFormat.SetFormatting(HeadCellFormat); 130 131 wmc = wm.Add(1, n, 1, n++); 132 wmc.Value = "答案6"; 133 wmc.CellFormat.SetFormatting(HeadCellFormat); 134 135 wmc = wm.Add(1, n, 1, n++); 136 wmc.Value = "正确答案"; 137 wmc.CellFormat.SetFormatting(HeadCellFormat); 138 139 wmc = wm.Add(1, n, 1, n++); 140 wmc.Value = "参考答案"; 141 wmc.CellFormat.SetFormatting(HeadCellFormat); 142 143 wmc = wm.Add(1, n, 1, n++); 144 wmc.Value = "答案解析"; 145 wmc.CellFormat.SetFormatting(HeadCellFormat); 146 #endregion 147 148 int Groupid = Convert.ToInt32(Request.QueryString["ParentRowID"]); 149 //List<M_Exam_Subject> list_s = bll.SelectAll().FindAll(x => x.GroupID == Groupid); 150 151 List<M_Exam_Subject> list_s = bll.SelectByGroupID(Groupid); 152 for (int i = 0; i < list_s.Count; i++) 153 { 154 string tmSubjectGuid = list_s[i].SubjectGuid.ToString(); 155 156 n = 0; 157 158 wmc = wm.Add(i + 2, n, i + 2, n++); 159 wmc.Value = Convert.ToString(i + 1); 160 wmc.CellFormat.SetFormatting(ItemCellFormat); 161 162 wmc = wm.Add(i + 2, n, i + 2, n++); 163 wmc.Value = list_s[i].Title.ToString(); 164 wmc.CellFormat.SetFormatting(ItemCellFormat); 165 166 wmc = wm.Add(i + 2, n, i + 2, n++); 167 string type = list_s[i].Type.ToString(); 168 wmc.Value = GetType(type); 169 wmc.CellFormat.SetFormatting(ItemCellFormat); 170 171 wmc = wm.Add(i + 2, n, i + 2, n++); 172 string difficult = list_s[i].Difficult.ToString(); 173 wmc.Value = GetDifficult(difficult); 174 wmc.CellFormat.SetFormatting(ItemCellFormat); 175 #region 导出Exam_Answer表答案 176 //DataView da = GetExcelDaAn(tmSubjectGuid); 177 List<M_Exam_Answer> list_a = bllAnswer.SelectBySubjectGuid(tmSubjectGuid); 178 if (list_a.Count > 0) 179 { 180 for (int j = 0; j < list_a.Count; j++) 181 { 182 wmc = wm.Add(i + 2, n, i + 2, n++); 183 wmc.Value = list_a[j].AnswerName.ToString(); 184 wmc.CellFormat.SetFormatting(ItemCellFormat); 185 } 186 for (int m = 0; m < (6 - list_a.Count); m++) 187 { 188 wmc = wm.Add(i + 2, n, i + 2, n++); 189 wmc.Value = ""; 190 wmc.CellFormat.SetFormatting(ItemCellFormat); 191 } 192 193 194 } 195 else 196 for (int j = 0; j < 6; j++) 197 { 198 wmc = wm.Add(i + 2, n, i + 2, n++); 199 wmc.Value = ""; 200 wmc.CellFormat.SetFormatting(ItemCellFormat); 201 } 202 #endregion 203 204 205 206 //导出Exam_Answer表答案IsRight 207 string isright = "1"; 208 List<M_Exam_Answer> list_aw = bllAnswer.SelectBySubjectGuid(tmSubjectGuid).FindAll(x => x.IsRight == isright); 209 string stranswer_kg = ""; 210 if (list_aw.Count > 0) 211 { 212 int j = 1; 213 for (int m = 0; m < list_aw.Count; m++) 214 { 215 stranswer_kg += j.ToString() + "." + list_aw[m].AnswerName.ToString(); 216 j++; 217 } 218 wmc = wm.Add(i + 2, n, i + 2, n++); 219 wmc.Value = stranswer_kg; 220 wmc.CellFormat.SetFormatting(ItemCellFormat); 221 } 222 else 223 for (int m = 0; m < 1; m++) 224 { 225 wmc = wm.Add(i + 2, n, i + 2, n++); 226 wmc.Value = ""; 227 wmc.CellFormat.SetFormatting(ItemCellFormat); 228 } 229 230 wmc = wm.Add(i + 2, n, i + 2, n++); 231 wmc.Value = string.IsNullOrEmpty(list_s[i].RightAnswer) ? "" : list_s[i].RightAnswer.ToString();//参考答案 232 wmc.CellFormat.SetFormatting(ItemCellFormat); 233 234 wmc = wm.Add(i + 2, n, i + 2, n++); 235 wmc.Value = string.IsNullOrEmpty(list_s[i].AnswerNote) ? "" : list_s[i].AnswerNote.ToString();// list_s[i].AnswerNote.ToString();//答案解析 236 wmc.CellFormat.SetFormatting(ItemCellFormat); 237 } 238 239 string mark = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString(); 240 strFileName = "Export_" + mark + ".xls"; 241 BIFF8Writer.WriteWorkbookToFile(wb, Server.MapPath("ExcelExport/" + strFileName)); 242 243 return strFileName; 244 } 245 #endregion
导出整个DataGrid
1 protected void InfoExport() 2 { 3 try 4 { 5 string ExcelName = this.CreateExcel(); 6 //将服务器上的Excel导出 7 // CuteWebUIOperate.DownloadFile(HttpContext.Current, Server.MapPath("ExcelExport/") + ExcelName, ExcelName, false); 8 string strScript = "window.open('ExcelExport/" + ExcelName + "');"; 9 this.WriteAjaxMessage(strScript); 10 } 11 catch 12 { 13 throw; 14 } 15 } 16 17 protected string CreateExcel() //生成Excel 18 { 19 string Header = "批次信息"; 20 string strFileName = ""; 21 22 // 生成文件夹 23 string fileFolderPath = Server.MapPath("ExcelExport/"); 24 if (!System.IO.Directory.Exists(fileFolderPath)) 25 System.IO.Directory.CreateDirectory(fileFolderPath); 26 27 Workbook wb = new Workbook(); 28 29 wb.Worksheets.Add("Sheet1"); 30 31 Worksheet ws = wb.ActiveWorksheet; 32 //first row 19cell 33 WorksheetMergedCellsRegionCollection wm = ws.MergedCellsRegions; 34 35 WorksheetMergedCellsRegion wmc = wm.Add(0, 0, 0, 6);//起始位置和终止位置 36 wmc.Value = Header; 37 wmc.CellFormat.Alignment = HorizontalCellAlignment.Center; 38 wmc.CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 39 wmc.CellFormat.BottomBorderColor = Color.Black; 40 wmc.CellFormat.LeftBorderColor = Color.Black; 41 wmc.CellFormat.RightBorderColor = Color.Black; 42 wmc.CellFormat.TopBorderColor = Color.Black; 43 44 wmc.CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 45 wmc.CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 46 wmc.CellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 47 wmc.CellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 48 wmc.CellFormat.WrapText = ExcelDefaultableBoolean.True; 49 wmc.CellFormat.Font.Name = "宋体"; 50 //字体大小 51 wmc.CellFormat.Font.Height = 300; 52 IWorksheetCellFormat HeadCellFormat = wb.CreateNewWorksheetCellFormat(); 53 HeadCellFormat.Alignment = HorizontalCellAlignment.Center; 54 HeadCellFormat.Font.Bold = ExcelDefaultableBoolean.True; 55 HeadCellFormat.Font.Name = "宋体"; 56 57 HeadCellFormat.BottomBorderColor = Color.Black; 58 HeadCellFormat.LeftBorderColor = Color.Black; 59 HeadCellFormat.RightBorderColor = Color.Black; 60 HeadCellFormat.TopBorderColor = Color.Black; 61 62 HeadCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 63 HeadCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 64 HeadCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 65 HeadCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 66 HeadCellFormat.WrapText = ExcelDefaultableBoolean.True; 67 68 IWorksheetCellFormat ItemCellFormat = wb.CreateNewWorksheetCellFormat(); 69 //CellFormat.Alignment = HorizontalCellAlignment.Center; 70 //CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 71 ItemCellFormat.FillPattern = FillPatternStyle.Default; 72 ItemCellFormat.ShrinkToFit = ExcelDefaultableBoolean.True; 73 ItemCellFormat.BottomBorderColor = Color.Black; 74 ItemCellFormat.LeftBorderColor = Color.Black; 75 ItemCellFormat.RightBorderColor = Color.Black; 76 ItemCellFormat.TopBorderColor = Color.Black; 77 78 ItemCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 79 ItemCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 80 ItemCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 81 ItemCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 82 ItemCellFormat.WrapText = ExcelDefaultableBoolean.True; 83 ItemCellFormat.FormatString = "##,##0.00"; 84 ItemCellFormat.Font.Name = "宋体"; 85 int n; 86 n = 0; 87 //wmc = wm.Add(1, n, 1, n++); 88 //wmc.Value = "序号"; 89 //wmc.CellFormat.SetFormatting(HeadCellFormat); 90 91 wmc = wm.Add(1, n, 1, n++); 92 wmc.Value = "企业名称"; 93 wmc.CellFormat.SetFormatting(HeadCellFormat); 94 95 wmc = wm.Add(1, n, 1, n++); 96 wmc.Value = "企业地址"; 97 wmc.CellFormat.SetFormatting(HeadCellFormat); 98 99 wmc = wm.Add(1, n, 1, n++); 100 wmc.Value = "所属分局"; 101 wmc.CellFormat.SetFormatting(HeadCellFormat); 102 103 wmc = wm.Add(1, n, 1, n++); 104 wmc.Value = "报名日期"; 105 wmc.CellFormat.SetFormatting(HeadCellFormat); 106 107 wmc = wm.Add(1, n, 1, n++); 108 wmc.Value = "姓名"; 109 wmc.CellFormat.SetFormatting(HeadCellFormat); 110 111 wmc = wm.Add(1, n, 1, n++); 112 wmc.Value = "性别"; 113 wmc.CellFormat.SetFormatting(HeadCellFormat); 114 115 wmc = wm.Add(1, n, 1, n++); 116 wmc.Value = "出生日期"; 117 wmc.CellFormat.SetFormatting(HeadCellFormat); 118 119 ws.Columns[n].Width = 500 * 15; 120 wmc = wm.Add(1, n, 1, n++); 121 wmc.Value = "身份证号码"; 122 wmc.CellFormat.SetFormatting(HeadCellFormat); 123 124 wmc = wm.Add(1, n, 1, n++); 125 wmc.Value = "手机号码"; 126 wmc.CellFormat.SetFormatting(HeadCellFormat); 127 128 wmc = wm.Add(1, n, 1, n++); 129 wmc.Value = "是否已交费"; 130 wmc.CellFormat.SetFormatting(HeadCellFormat); 131 132 wmc = wm.Add(1, n, 1, n++); 133 wmc.Value = "交费日期"; 134 wmc.CellFormat.SetFormatting(HeadCellFormat); 135 136 wmc = wm.Add(1, n, 1, n++); 137 wmc.Value = "是否已安排培训"; 138 wmc.CellFormat.SetFormatting(HeadCellFormat); 139 140 ws.Columns[n].Width = 500 * 15; 141 wmc = wm.Add(1, n, 1, n++); 142 wmc.Value = "培训批次"; 143 wmc.CellFormat.SetFormatting(HeadCellFormat); 144 145 wmc = wm.Add(1, n, 1, n++); 146 wmc.Value = "培训开始日期"; 147 wmc.CellFormat.SetFormatting(HeadCellFormat); 148 149 wmc = wm.Add(1, n, 1, n++); 150 wmc.Value = "培训结束日期"; 151 wmc.CellFormat.SetFormatting(HeadCellFormat); 152 153 154 DataView dv = GetExcelData();//获取报名信息 155 for (int i = 0; i < dv.Count; i++) 156 { 157 n = 0; 158 159 //wmc = wm.Add(i + 2, n, i + 2, n++); 160 //wmc.Value = Convert.ToString(i + 1); 161 //wmc.CellFormat.SetFormatting(ItemCellFormat); 162 163 wmc = wm.Add(i + 2, n, i + 2, n++); 164 wmc.Value = dv[i]["DanWeiName"].ToString(); 165 wmc.CellFormat.SetFormatting(ItemCellFormat); 166 167 wmc = wm.Add(i + 2, n, i + 2, n++); 168 wmc.Value = dv[i]["DetailAddress"].ToString(); 169 wmc.CellFormat.SetFormatting(ItemCellFormat); 170 171 wmc = wm.Add(i + 2, n, i + 2, n++); 172 wmc.Value = DB_Common.GetCodeName("PX_分局名称", dv[i]["BelongFenJu"].ToString()); 173 wmc.CellFormat.SetFormatting(ItemCellFormat); 174 175 wmc = wm.Add(i + 2, n, i + 2, n++); 176 wmc.Value = dv[i]["BMAddDate"].ToString(); 177 wmc.CellFormat.SetFormatting(ItemCellFormat); 178 179 wmc = wm.Add(i + 2, n, i + 2, n++); 180 wmc.Value = dv[i]["Name"].ToString(); 181 wmc.CellFormat.SetFormatting(ItemCellFormat); 182 183 wmc = wm.Add(i + 2, n, i + 2, n++); 184 wmc.Value = DB_Common.GetCodeName("PX_性别", dv[i]["Sex"].ToString()); 185 wmc.CellFormat.SetFormatting(ItemCellFormat); 186 187 wmc = wm.Add(i + 2, n, i + 2, n++); 188 wmc.Value = dv[i]["BirthDay"].ToString(); 189 wmc.CellFormat.SetFormatting(ItemCellFormat); 190 191 wmc = wm.Add(i + 2, n, i + 2, n++); 192 wmc.Value = dv[i]["IdentityNum"].ToString(); 193 wmc.CellFormat.SetFormatting(ItemCellFormat); 194 195 wmc = wm.Add(i + 2, n, i + 2, n++); 196 wmc.Value = dv[i]["MobilePhone"].ToString(); 197 wmc.CellFormat.SetFormatting(ItemCellFormat); 198 199 wmc = wm.Add(i + 2, n, i + 2, n++); 200 wmc.Value = DB_Common.GetCodeName("PX_是否", dv[i]["IsPay"].ToString()); 201 wmc.CellFormat.SetFormatting(ItemCellFormat); 202 203 wmc = wm.Add(i + 2, n, i + 2, n++); 204 wmc.Value = dv[i]["PayDate"].ToString(); 205 wmc.CellFormat.SetFormatting(ItemCellFormat); 206 207 wmc = wm.Add(i + 2, n, i + 2, n++); 208 wmc.Value = DB_Common.GetCodeName("PX_是否", dv[i]["IsPeiXun"].ToString()); 209 wmc.CellFormat.SetFormatting(ItemCellFormat); 210 211 wmc = wm.Add(i + 2, n, i + 2, n++); 212 wmc.Value = dv[i]["BaoMPiCi"].ToString(); 213 wmc.CellFormat.SetFormatting(ItemCellFormat); 214 215 wmc = wm.Add(i + 2, n, i + 2, n++); 216 wmc.Value = dv[i]["PXStartDate"].ToString(); 217 wmc.CellFormat.SetFormatting(ItemCellFormat); 218 219 wmc = wm.Add(i + 2, n, i + 2, n++); 220 wmc.Value = dv[i]["PXEndDate"].ToString(); 221 wmc.CellFormat.SetFormatting(ItemCellFormat); 222 223 } 224 225 string mark = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString(); 226 strFileName = "Export_" + mark + ".xls"; 227 BIFF8Writer.WriteWorkbookToFile(wb, Server.MapPath("ExcelExport/" + strFileName)); 228 229 return strFileName; 230 }
