C# 导出到Excel

  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

  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         }