首页 / C# / C# 导出到Excel
C# 导出到Excel
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了C# 导出到Excel,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含16153字,纯文字阅读大概需要24分钟。
内容图文
![C# 导出到Excel](/upload/InfoBanner/zyjiaocheng/1099/ad077b3aaeaa49c89ff8b80562ebddcd.jpg)
一个DataGrid里有两张表的数据,导出成一张表
![技术分享](/upload/getfiles/default/2022/11/8/20221108084421932.jpg)
![技术分享](/upload/getfiles/default/2022/11/8/20221108084421953.jpg)
1 protected void btnExcel_Click(object sender, EventArgs e) 2 { 3 InfoExport(); 4 } 5 6protectedvoid InfoExport() 7 { 8try 9 { 10string ExcelName = this.CreateExcel(); 11//将服务器上的Excel导出 12// CuteWebUIOperate.DownloadFile(HttpContext.Current, Server.MapPath("ExcelExport/") + ExcelName, ExcelName, false); 13string strScript = "window.open(‘ExcelExport/" + ExcelName + "‘);"; 14this.WriteAjaxMessage(strScript); 15 } 16catch 17 { 18throw; 19 } 20 } 21 22#region 导出excel 23protectedstring CreateExcel() //生成Excel 24 { 25string Header = "考试题库"; 26string strFileName = ""; 27 28// 生成文件夹 29string fileFolderPath = Server.MapPath("ExcelExport/"); 30if (!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 92int 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); 102103 wmc = wm.Add(1, n, 1, n++); 104 wmc.Value = "题目类型"; 105 wmc.CellFormat.SetFormatting(HeadCellFormat); 106107 wmc = wm.Add(1, n, 1, n++); 108 wmc.Value = "题目难度"; 109 wmc.CellFormat.SetFormatting(HeadCellFormat); 110111 wmc = wm.Add(1, n, 1, n++); 112 wmc.Value = "答案1"; 113 wmc.CellFormat.SetFormatting(HeadCellFormat); 114115 wmc = wm.Add(1, n, 1, n++); 116 wmc.Value = "答案2"; 117 wmc.CellFormat.SetFormatting(HeadCellFormat); 118119 wmc = wm.Add(1, n, 1, n++); 120 wmc.Value = "答案3"; 121 wmc.CellFormat.SetFormatting(HeadCellFormat); 122123 wmc = wm.Add(1, n, 1, n++); 124 wmc.Value = "答案4"; 125 wmc.CellFormat.SetFormatting(HeadCellFormat); 126127 wmc = wm.Add(1, n, 1, n++); 128 wmc.Value = "答案5"; 129 wmc.CellFormat.SetFormatting(HeadCellFormat); 130131 wmc = wm.Add(1, n, 1, n++); 132 wmc.Value = "答案6"; 133 wmc.CellFormat.SetFormatting(HeadCellFormat); 134135 wmc = wm.Add(1, n, 1, n++); 136 wmc.Value = "正确答案"; 137 wmc.CellFormat.SetFormatting(HeadCellFormat); 138139 wmc = wm.Add(1, n, 1, n++); 140 wmc.Value = "参考答案"; 141 wmc.CellFormat.SetFormatting(HeadCellFormat); 142143 wmc = wm.Add(1, n, 1, n++); 144 wmc.Value = "答案解析"; 145 wmc.CellFormat.SetFormatting(HeadCellFormat); 146#endregion147148int Groupid = Convert.ToInt32(Request.QueryString["ParentRowID"]); 149//List<M_Exam_Subject> list_s = bll.SelectAll().FindAll(x => x.GroupID == Groupid);150151 List<M_Exam_Subject> list_s = bll.SelectByGroupID(Groupid); 152for (int i = 0; i < list_s.Count; i++) 153 { 154string tmSubjectGuid = list_s[i].SubjectGuid.ToString(); 155156 n = 0; 157158 wmc = wm.Add(i + 2, n, i + 2, n++); 159 wmc.Value = Convert.ToString(i + 1); 160 wmc.CellFormat.SetFormatting(ItemCellFormat); 161162 wmc = wm.Add(i + 2, n, i + 2, n++); 163 wmc.Value = list_s[i].Title.ToString(); 164 wmc.CellFormat.SetFormatting(ItemCellFormat); 165166 wmc = wm.Add(i + 2, n, i + 2, n++); 167string type = list_s[i].Type.ToString(); 168 wmc.Value = GetType(type); 169 wmc.CellFormat.SetFormatting(ItemCellFormat); 170171 wmc = wm.Add(i + 2, n, i + 2, n++); 172string 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); 178if (list_a.Count > 0) 179 { 180for (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 } 186for (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 } 192193194 } 195else196for (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#endregion203204205206//导出Exam_Answer表答案IsRight207string isright = "1"; 208 List<M_Exam_Answer> list_aw = bllAnswer.SelectBySubjectGuid(tmSubjectGuid).FindAll(x => x.IsRight == isright); 209string stranswer_kg = ""; 210if (list_aw.Count > 0) 211 { 212int j = 1; 213for (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 } 222else223for (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 } 229230 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); 233234 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 } 238239string 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)); 242243return strFileName; 244 } 245#endregion
导出整个DataGrid
![技术分享](/upload/getfiles/default/2022/11/8/20221108084421932.jpg)
![技术分享](/upload/getfiles/default/2022/11/8/20221108084421953.jpg)
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); 8string strScript = "window.open(‘ExcelExport/" + ExcelName + "‘);"; 9this.WriteAjaxMessage(strScript); 10 } 11catch 12 { 13throw; 14 } 15 } 16 17protectedstring CreateExcel() //生成Excel 18 { 19string Header = "批次信息"; 20string strFileName = ""; 21 22// 生成文件夹 23string fileFolderPath = Server.MapPath("ExcelExport/"); 24if (!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 = "宋体"; 85int 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); 102103 wmc = wm.Add(1, n, 1, n++); 104 wmc.Value = "报名日期"; 105 wmc.CellFormat.SetFormatting(HeadCellFormat); 106107 wmc = wm.Add(1, n, 1, n++); 108 wmc.Value = "姓名"; 109 wmc.CellFormat.SetFormatting(HeadCellFormat); 110111 wmc = wm.Add(1, n, 1, n++); 112 wmc.Value = "性别"; 113 wmc.CellFormat.SetFormatting(HeadCellFormat); 114115 wmc = wm.Add(1, n, 1, n++); 116 wmc.Value = "出生日期"; 117 wmc.CellFormat.SetFormatting(HeadCellFormat); 118119 ws.Columns[n].Width = 500 * 15; 120 wmc = wm.Add(1, n, 1, n++); 121 wmc.Value = "身份证号码"; 122 wmc.CellFormat.SetFormatting(HeadCellFormat); 123124 wmc = wm.Add(1, n, 1, n++); 125 wmc.Value = "手机号码"; 126 wmc.CellFormat.SetFormatting(HeadCellFormat); 127128 wmc = wm.Add(1, n, 1, n++); 129 wmc.Value = "是否已交费"; 130 wmc.CellFormat.SetFormatting(HeadCellFormat); 131132 wmc = wm.Add(1, n, 1, n++); 133 wmc.Value = "交费日期"; 134 wmc.CellFormat.SetFormatting(HeadCellFormat); 135136 wmc = wm.Add(1, n, 1, n++); 137 wmc.Value = "是否已安排培训"; 138 wmc.CellFormat.SetFormatting(HeadCellFormat); 139140 ws.Columns[n].Width = 500 * 15; 141 wmc = wm.Add(1, n, 1, n++); 142 wmc.Value = "培训批次"; 143 wmc.CellFormat.SetFormatting(HeadCellFormat); 144145 wmc = wm.Add(1, n, 1, n++); 146 wmc.Value = "培训开始日期"; 147 wmc.CellFormat.SetFormatting(HeadCellFormat); 148149 wmc = wm.Add(1, n, 1, n++); 150 wmc.Value = "培训结束日期"; 151 wmc.CellFormat.SetFormatting(HeadCellFormat); 152153154 DataView dv = GetExcelData();//获取报名信息155for (int i = 0; i < dv.Count; i++) 156 { 157 n = 0; 158159//wmc = wm.Add(i + 2, n, i + 2, n++); 160//wmc.Value = Convert.ToString(i + 1); 161//wmc.CellFormat.SetFormatting(ItemCellFormat);162163 wmc = wm.Add(i + 2, n, i + 2, n++); 164 wmc.Value = dv[i]["DanWeiName"].ToString(); 165 wmc.CellFormat.SetFormatting(ItemCellFormat); 166167 wmc = wm.Add(i + 2, n, i + 2, n++); 168 wmc.Value = dv[i]["DetailAddress"].ToString(); 169 wmc.CellFormat.SetFormatting(ItemCellFormat); 170171 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); 174175 wmc = wm.Add(i + 2, n, i + 2, n++); 176 wmc.Value = dv[i]["BMAddDate"].ToString(); 177 wmc.CellFormat.SetFormatting(ItemCellFormat); 178179 wmc = wm.Add(i + 2, n, i + 2, n++); 180 wmc.Value = dv[i]["Name"].ToString(); 181 wmc.CellFormat.SetFormatting(ItemCellFormat); 182183 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); 186187 wmc = wm.Add(i + 2, n, i + 2, n++); 188 wmc.Value = dv[i]["BirthDay"].ToString(); 189 wmc.CellFormat.SetFormatting(ItemCellFormat); 190191 wmc = wm.Add(i + 2, n, i + 2, n++); 192 wmc.Value = dv[i]["IdentityNum"].ToString(); 193 wmc.CellFormat.SetFormatting(ItemCellFormat); 194195 wmc = wm.Add(i + 2, n, i + 2, n++); 196 wmc.Value = dv[i]["MobilePhone"].ToString(); 197 wmc.CellFormat.SetFormatting(ItemCellFormat); 198199 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); 202203 wmc = wm.Add(i + 2, n, i + 2, n++); 204 wmc.Value = dv[i]["PayDate"].ToString(); 205 wmc.CellFormat.SetFormatting(ItemCellFormat); 206207 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); 210211 wmc = wm.Add(i + 2, n, i + 2, n++); 212 wmc.Value = dv[i]["BaoMPiCi"].ToString(); 213 wmc.CellFormat.SetFormatting(ItemCellFormat); 214215 wmc = wm.Add(i + 2, n, i + 2, n++); 216 wmc.Value = dv[i]["PXStartDate"].ToString(); 217 wmc.CellFormat.SetFormatting(ItemCellFormat); 218219 wmc = wm.Add(i + 2, n, i + 2, n++); 220 wmc.Value = dv[i]["PXEndDate"].ToString(); 221 wmc.CellFormat.SetFormatting(ItemCellFormat); 222223 } 224225string 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)); 228229return strFileName; 230 }
原文:http://www.cnblogs.com/lyhsblog/p/5966128.html
内容总结
以上是互联网集市为您收集整理的C# 导出到Excel全部内容,希望文章能够帮你解决C# 导出到Excel所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。