首页 > 代码库 > C# Excel导入
C# Excel导入
两张表导入到一个DataGrid里面（题目表和答案表）
前台代码
1 <asp:Content ID="Content1" ContentPlaceHolderID="cphToolBar" runat="server"> 2 <epoint:Button ID="btnImport" runat="server" Text="题库信息导入" /> 3 <span> 4 <span style="color: red;">模板下载：</span><a target="_blank" 5 href=http://www.mamicode.com/"题库模版.xls"><span style="color: blue;">点击下载文件</span> </a> 6 </span> 7 </asp:Content> 8 9 <asp:UpdatePanel runat="server" ID="UpdatePanel_Upload"> 10 <ContentTemplate> 11 <epoint:CuteWebUIUpload_NoBut ID="upload1" AllowFileList="xlsx;xls" runat="server" 12 MaxAttachCount="-1" MaxAttachCountOneTime="1" UseCustomSaveMethod="true" OnFileUploadCompleted_Custom="upload1_FileUploadCompleted_Custom" /> 13 </ContentTemplate> 14 </asp:UpdatePanel>
后台代码
1 /// <summary> 2 /// 导入 3 /// </summary> 4 /// <param name="sender"></param> 5 /// <param name="args"></param> 6 7 protected void upload1_FileUploadCompleted_Custom(object sender, EventArgsOperate.AttachEventArgs[] args) 8 { 9 if (!Directory.Exists(Server.MapPath(@"ImportExcel"))) 10 Directory.CreateDirectory(Server.MapPath(@"ImportExcel")); 11 string mark = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString(); 12 string oldfileName = args[0].FileName; 13 string documentType = oldfileName.Substring(oldfileName.LastIndexOf(‘.‘), oldfileName.Length - oldfileName.LastIndexOf(‘.‘)); 14 string fileName = "Import_" + mark + documentType; 15 args[0].CuteArgs.CopyTo(Server.MapPath(@"ImportExcel\") + fileName); 16 17 ReadExcel(Server.MapPath(@"ImportExcel\") + fileName); 18 } 19 20 21 public void ReadExcel(string ExcelFile) 22 { 23 DataSet ds; 24 string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";" + "Extended Properties=‘Excel 12.0‘;"; 25 OleDbConnection conn = new OleDbConnection(strConn); 26 DataTable dtExcelSchema = new DataTable(); 27 try 28 { 29 conn.Open(); 30 dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 31 new object[] { null, null, null, "Table" });//获取需要上传的Excel的Sheet 32 conn.Close(); 33 } 34 catch 35 { 36 throw; 37 } 38 for (int k = 1; k <= dtExcelSchema.Rows.Count; k++) 39 { 40 try 41 { 42 ds = new DataSet(); 43 OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet" + k + "$]", strConn); 44 oada.Fill(ds); 45 } 46 catch 47 { 48 throw; 49 } 50 51 DataTable dt = ds.Tables[0]; 52 if (dt.Rows.Count > 0) 53 { 54 55 for (int i = 0; i < dt.Rows.Count; i++) 56 { 57 M_Exam_Subject m_es = new M_Exam_Subject(); 58 M_Exam_Answer m_ea = new M_Exam_Answer(); 59 #region 插入题目 60 m_es.SubjectGuid = NewSubjectGuid(); 61 m_es.Title = Convert.ToString(dt.Rows[i][0]);//题目名称 62 string type = Convert.ToString(dt.Rows[i][1]);//题目类型 63 switch (type) 64 { 65 case "单选": m_es.Type = "0"; 66 break; 67 case "多选": m_es.Type = "1"; 68 break; 69 case "判断": m_es.Type = "2"; 70 break; 71 case "填空": m_es.Type = "3"; 72 break; 73 case "简答": m_es.Type = "4"; 74 break; 75 } 76 77 string difficult = Convert.ToString(dt.Rows[i][2]);//题目难度 78 switch (difficult) 79 { 80 case "简单": m_es.Difficult = 0; 81 break; 82 case "一般": m_es.Difficult = 1; 83 break; 84 case "难": m_es.Difficult = 2; 85 break; 86 } 87 m_es.AnswerNote = Convert.ToString(dt.Rows[i][21]);//答案解析 88 m_es.GroupID = Convert.ToInt32(ParentRowID); 89 b_examsubject.Insert(m_es); 90 #endregion 91 //插入主观题答案 92 if ((type == "填空") || (type == "简答")) 93 { 94 m_es.SubjectGuid = m_es.SubjectGuid; 95 m_es.RightAnswer = Convert.ToString(dt.Rows[i][19]);//正确答案 96 b_examsubject.Update(m_es); 97 } 98 //插入客观题答案 99 else 100 { 101 //for (int j = 3; j < 7; j++) 102 //{ 103 // m_ea.SubjectGuid = m_es.SubjectGuid; 104 // m_ea.AnswerGuid = Guid.NewGuid().ToString(); 105 // m_ea.AnswerName = Convert.ToString(dt.Rows[i][j]);//答案 106 // b_examanswer.Insert(m_ea); 107 //} 108 DataView dvRecord = dt.DefaultView; 109 string answerid1 = Guid.NewGuid().ToString(); 110 string answerid2 = Guid.NewGuid().ToString(); 111 string answerid3 = Guid.NewGuid().ToString(); 112 string answerid4 = Guid.NewGuid().ToString(); 113 string answerid5 = Guid.NewGuid().ToString(); 114 string answerid6 = Guid.NewGuid().ToString(); 115 if (Convert.ToString(dvRecord[i][3]) != "") 116 b_examanswer.InsertAnswer(answerid1, Convert.ToString(dvRecord[i][3]), false, m_es.SubjectGuid); 117 if (Convert.ToString(dvRecord[i][4]) != "") 118 b_examanswer.InsertAnswer(answerid2, Convert.ToString(dvRecord[i][4]), false, m_es.SubjectGuid); 119 if (Convert.ToString(dvRecord[i][5]) != "") 120 b_examanswer.InsertAnswer(answerid3, Convert.ToString(dvRecord[i][5]), false, m_es.SubjectGuid); 121 if (Convert.ToString(dvRecord[i][6]) != "") 122 b_examanswer.InsertAnswer(answerid4, Convert.ToString(dvRecord[i][6]), false, m_es.SubjectGuid); 123 if (Convert.ToString(dvRecord[i][7]) != "") 124 b_examanswer.InsertAnswer(answerid5, Convert.ToString(dvRecord[i][7]), false, m_es.SubjectGuid); 125 if (Convert.ToString(dvRecord[i][8]) != "") 126 b_examanswer.InsertAnswer(answerid6, Convert.ToString(dvRecord[i][8]), false, m_es.SubjectGuid); 127 128 //添加正确答案 129 int num = 0; 130 if (Convert.ToString(dvRecord[i][18]) != "") 131 { 132 string strright = Convert.ToString(dvRecord[i][18]).Trim(); 133 if (strright.IndexOf(‘A‘) >= 0 || strright.IndexOf(‘a‘) >= 0) 134 { 135 num++; 136 b_examanswer.UpdateAnswer_isRight(answerid1, true); 137 } 138 if (strright.IndexOf(‘B‘) >= 0 || strright.IndexOf(‘b‘) >= 0) 139 { 140 num++; 141 b_examanswer.UpdateAnswer_isRight(answerid2, true); 142 } 143 if (strright.IndexOf(‘C‘) >= 0 || strright.IndexOf(‘c‘) >= 0) 144 { 145 num++; 146 b_examanswer.UpdateAnswer_isRight(answerid3, true); 147 } 148 if (strright.IndexOf(‘D‘) >= 0 || strright.IndexOf(‘d‘) >= 0) 149 { 150 num++; 151 b_examanswer.UpdateAnswer_isRight(answerid4, true); 152 } 153 if (strright.IndexOf(‘E‘) >= 0 || strright.IndexOf(‘e‘) >= 0) 154 { 155 num++; 156 b_examanswer.UpdateAnswer_isRight(answerid5, true); 157 } 158 if (strright.IndexOf(‘F‘) >= 0 || strright.IndexOf(‘f‘) >= 0) 159 { 160 num++; 161 b_examanswer.UpdateAnswer_isRight(answerid6, true); 162 } 163 } 164 } 165 } 166 } 167 } 168 BindGrid(); 169 }
单表导入到一个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 18 19 protected string CreateExcel() //生成Excel 20 { 21 string Header = "报名信息"; 22 string strFileName = ""; 23 24 // 生成文件夹 25 string fileFolderPath = Server.MapPath("ExcelExport/"); 26 if (!System.IO.Directory.Exists(fileFolderPath)) 27 System.IO.Directory.CreateDirectory(fileFolderPath); 28 29 Workbook wb = new Workbook(); 30 31 wb.Worksheets.Add("Sheet1"); 32 33 Worksheet ws = wb.ActiveWorksheet; 34 //first row 19cell 35 WorksheetMergedCellsRegionCollection wm = ws.MergedCellsRegions; 36 37 WorksheetMergedCellsRegion wmc = wm.Add(0, 0, 0, 6);//起始位置和终止位置 38 wmc.Value =http://www.mamicode.com/ Header; 39 wmc.CellFormat.Alignment = HorizontalCellAlignment.Center; 40 wmc.CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 41 wmc.CellFormat.BottomBorderColor = Color.Black; 42 wmc.CellFormat.LeftBorderColor = Color.Black; 43 wmc.CellFormat.RightBorderColor = Color.Black; 44 wmc.CellFormat.TopBorderColor = Color.Black; 45 46 wmc.CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 47 wmc.CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 48 wmc.CellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 49 wmc.CellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 50 wmc.CellFormat.WrapText = ExcelDefaultableBoolean.True; 51 wmc.CellFormat.Font.Name = "宋体"; 52 //字体大小 53 wmc.CellFormat.Font.Height = 300; 54 IWorksheetCellFormat HeadCellFormat = wb.CreateNewWorksheetCellFormat(); 55 HeadCellFormat.Alignment = HorizontalCellAlignment.Center; 56 HeadCellFormat.Font.Bold = ExcelDefaultableBoolean.True; 57 HeadCellFormat.Font.Name = "宋体"; 58 59 HeadCellFormat.BottomBorderColor = Color.Black; 60 HeadCellFormat.LeftBorderColor = Color.Black; 61 HeadCellFormat.RightBorderColor = Color.Black; 62 HeadCellFormat.TopBorderColor = Color.Black; 63 64 HeadCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 65 HeadCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 66 HeadCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 67 HeadCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 68 HeadCellFormat.WrapText = ExcelDefaultableBoolean.True; 69 70 IWorksheetCellFormat ItemCellFormat = wb.CreateNewWorksheetCellFormat(); 71 //CellFormat.Alignment = HorizontalCellAlignment.Center; 72 //CellFormat.Font.Bold = ExcelDefaultableBoolean.True; 73 ItemCellFormat.FillPattern = FillPatternStyle.Default; 74 ItemCellFormat.ShrinkToFit = ExcelDefaultableBoolean.True; 75 ItemCellFormat.BottomBorderColor = Color.Black; 76 ItemCellFormat.LeftBorderColor = Color.Black; 77 ItemCellFormat.RightBorderColor = Color.Black; 78 ItemCellFormat.TopBorderColor = Color.Black; 79 80 ItemCellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; 81 ItemCellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; 82 ItemCellFormat.RightBorderStyle = CellBorderLineStyle.Thin; 83 ItemCellFormat.TopBorderStyle = CellBorderLineStyle.Thin; 84 ItemCellFormat.WrapText = ExcelDefaultableBoolean.True; 85 ItemCellFormat.FormatString = "##,##0.00"; 86 ItemCellFormat.Font.Name = "宋体"; 87 int n; 88 n = 0; 89 wmc = wm.Add(1, n, 1, n++); 90 wmc.Value = http://www.mamicode.com/"序号"; 91 wmc.CellFormat.SetFormatting(HeadCellFormat); 92 93 wmc = wm.Add(1, n, 1, n++); 94 wmc.Value = http://www.mamicode.com/"姓名"; 95 wmc.CellFormat.SetFormatting(HeadCellFormat); 96 97 wmc = wm.Add(1, n, 1, n++); 98 wmc.Value = http://www.mamicode.com/"身份证号"; 99 wmc.CellFormat.SetFormatting(HeadCellFormat); 100 101 wmc = wm.Add(1, n, 1, n++); 102 wmc.Value = http://www.mamicode.com/"单位名称"; 103 wmc.CellFormat.SetFormatting(HeadCellFormat); 104 105 wmc = wm.Add(1, n, 1, n++); 106 wmc.Value = http://www.mamicode.com/"计划名称"; 107 wmc.CellFormat.SetFormatting(HeadCellFormat); 108 109 wmc = wm.Add(1, n, 1, n++); 110 wmc.Value = http://www.mamicode.com/"报名项"; 111 wmc.CellFormat.SetFormatting(HeadCellFormat); 112 113 wmc = wm.Add(1, n, 1, n++); 114 wmc.Value = http://www.mamicode.com/"准考证号"; 115 wmc.CellFormat.SetFormatting(HeadCellFormat); 116 117 wmc = wm.Add(1, n, 1, n++); 118 wmc.Value = http://www.mamicode.com/"成绩"; 119 wmc.CellFormat.SetFormatting(HeadCellFormat); 120 121 ws.Columns[n].Width = 500 * 15; 122 wmc = wm.Add(1, n, 1, n++); 123 wmc.Value = http://www.mamicode.com/"是否合格"; 124 wmc.CellFormat.SetFormatting(HeadCellFormat); 125 126 DataView dv = GetExcelData();//获取报名信息 127 for (int i = 0; i < dv.Count; i++) 128 { 129 n = 0; 130 131 wmc = wm.Add(i + 2, n, i + 2, n++); 132 wmc.Value = http://www.mamicode.com/Convert.ToString(i + 1); 133 wmc.CellFormat.SetFormatting(ItemCellFormat); 134 135 wmc = wm.Add(i + 2, n, i + 2, n++); 136 wmc.Value = http://www.mamicode.com/dv[i]["Name"].ToString(); 137 wmc.CellFormat.SetFormatting(ItemCellFormat); 138 139 wmc = wm.Add(i + 2, n, i + 2, n++); 140 wmc.Value = http://www.mamicode.com/dv[i]["IdentityNum"].ToString(); 141 wmc.CellFormat.SetFormatting(ItemCellFormat); 142 143 wmc = wm.Add(i + 2, n, i + 2, n++); 144 wmc.Value = http://www.mamicode.com/dv[i]["DanWeiName"].ToString(); 145 wmc.CellFormat.SetFormatting(ItemCellFormat); 146 147 wmc = wm.Add(i + 2, n, i + 2, n++); 148 wmc.Value = http://www.mamicode.com/dv[i]["PlanName"].ToString(); 149 wmc.CellFormat.SetFormatting(ItemCellFormat); 150 151 wmc = wm.Add(i + 2, n, i + 2, n++); 152 wmc.Value = http://www.mamicode.com/dv[i]["ItemName"].ToString(); 153 wmc.CellFormat.SetFormatting(ItemCellFormat); 154 155 wmc = wm.Add(i + 2, n, i + 2, n++); 156 wmc.Value = http://www.mamicode.com/dv[i]["ZhunKZNum"].ToString(); 157 wmc.CellFormat.SetFormatting(ItemCellFormat); 158 159 wmc = wm.Add(i + 2, n, i + 2, n++); 160 wmc.Value = http://www.mamicode.com/dv[i]["Score"].ToString(); 161 wmc.CellFormat.SetFormatting(ItemCellFormat); 162 163 wmc = wm.Add(i + 2, n, i + 2, n++); 164 wmc.Value = http://www.mamicode.com/dv[i]["IsPass"].ToString(); 165 wmc.CellFormat.SetFormatting(ItemCellFormat); 166 167 } 168 169 string mark = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString(); 170 strFileName = "Export_" + mark + ".xls"; 171 BIFF8Writer.WriteWorkbookToFile(wb, Server.MapPath("ExcelExport/" + strFileName)); 172 173 return strFileName; 174 } 175 176 protected DataView GetExcelData() 177 { 178 int TotalNum = 0; 179 string where = " where 1=1 "; 180 if (!String.IsNullOrEmpty(txtIdentityNum.Text)) 181 { 182 where += "and IdentityNum like ‘%" + txtIdentityNum.Text + "%‘"; 183 } 184 where += "and PlanGuid = ‘" + PlanGuid + "‘"; 185 where += "and Status != ‘" + 3 + "‘"; 186 string connectionStringName = "DJG_PeiXun_ConnectionString"; 187 string fields = "*"; 188 string sortExpression = "order by Row_ID desc"; 189 190 DataTable DvPaging = new DB_Common().GetData_Page_Table( 191 fields, 192 DataGrid1.PageSize, 193 DataGrid1.CurrentPageIndex + 1, 194 "View_Score_UserType", 195 "Row_ID", 196 where, 197 sortExpression, 198 out TotalNum, 199 connectionStringName 200 ); 201 return DvPaging.DefaultView; 202 }
C# Excel导入
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。