|
Posted on
2006-04-20 01:44
西瓜K菠萝
阅读( 1551)
评论()
编辑
收藏
举报
1 using System; 2 using System.Collections; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Web; 7 using System.Web.SessionState; 8 using System.Web.UI; 9 using System.Web.UI.WebControls; 10 using System.Web.UI.HtmlControls; 11 //************************************* 12 public class DataSetToExcel 13![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 14![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) public DataSetToExcel() {} 15 public void Convert(DataSet oDS,HttpResponse Response) 16![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 17 Response.Clear(); 18 Response.Charset = ""; 19 Response.ContentType = "application/vnd.ms-excel"; 20 System.IO.StringWriter oSW = new System.IO.StringWriter(); 21 HtmlTextWriter oHW = new HtmlTextWriter(oSW); 22 DataGrid oDG = new DataGrid(); 23 oDG.DataSource = oDS.Tables[0]; 24 oDG.DataBind(); 25 oDG.RenderControl(oHW); 26 Response.Write(oSW.ToString()); 27 Response.Flush(); 28 Response.Close(); 29 } 30 } 31 //********************************************************* 32 调用这个类就OK了 33![](/Images/OutliningIndicators/None.gif) 34 35![](/Images/OutliningIndicators/None.gif) 36 (2) 37![](/Images/OutliningIndicators/None.gif) 38 using System; 39 using System.Data; 40 using System.Data.OleDb; 41 namespace GRIS.ExcelReprot 42![](/Images/OutliningIndicators/ExpandedBlockStart.gif) ![](/Images/OutliningIndicators/ContractedBlock.gif) { 43![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary> 44 /// ImportExportToExcel 的摘要说明。 45 /// </summary> 46 public class ImportExportToExcel 47![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 48 private string strConn ; 49 50 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog(); 51 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 52 53 public ImportExportToExcel() 54![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 55 // 56 // TODO: 在此处添加构造函数逻辑 57 // 58 this.openFileDlg.DefaultExt = "xls"; 59 this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; 60![](/Images/OutliningIndicators/InBlock.gif) 61 this.saveFileDlg.DefaultExt="xls"; 62 this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls"; 63![](/Images/OutliningIndicators/InBlock.gif) 64 } 65![](/Images/OutliningIndicators/InBlock.gif) 66![](/Images/OutliningIndicators/ContractedSubBlock.gif) 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet 67 // /// <summary> 68 // /// 从Excel导入文件 69 // /// </summary> 70 // /// <param name="strExcelFileName">Excel文件名</param> 71 // /// <returns>返回DataSet</returns> 72 // public DataSet ImportFromExcel(string strExcelFileName) 73 // { 74 // return doImport(strExcelFileName); 75 // } 76![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 77 /// 从选择的Excel文件导入 78 /// </summary> 79 /// <returns>DataSet</returns> 80 public DataSet ImportFromExcel() 81![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 82 DataSet ds=new DataSet(); 83 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 84 ds=doImport(openFileDlg.FileName); 85 return ds; 86 } 87![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**///// <summary> 88 /// 从指定的Excel文件导入 89 /// </summary> 90 /// <param name="strFileName">Excel文件名</param> 91 /// <returns></returns> 92 public DataSet ImportFromExcel(string strFileName) 93![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 94 DataSet ds=new DataSet(); 95 ds=doImport(strFileName); 96 return ds; 97 } 98![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 99 /// 执行导入 100 /// </summary> 101 /// <param name="strFileName">文件名</param> 102 /// <returns>DataSet</returns> 103 private DataSet doImport(string strFileName) 104![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 105 if (strFileName=="") return null; 106 107 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + 108 "Data Source=" + strFileName + ";" + 109 "Extended Properties=Excel 8.0;"; 110 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); 111![](/Images/OutliningIndicators/InBlock.gif) 112 DataSet ExcelDs = new DataSet(); 113![](/Images/OutliningIndicators/InBlock.gif) 114 try 115![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 116 ExcelDA.Fill(ExcelDs, "ExcelInfo"); 117 } 118 catch(Exception err) 119![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 120 System.Console.WriteLine( err.ToString() ); 121 } 122 return ExcelDs; 123 } 124 #endregion 125![](/Images/OutliningIndicators/InBlock.gif) 126![](/Images/OutliningIndicators/ContractedSubBlock.gif) 从DataSet到出到Excel#region 从DataSet到出到Excel 127![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 128 /// 导出指定的Excel文件 129 /// </summary> 130 /// <param name="ds">要导出的DataSet</param> 131 /// <param name="strExcelFileName">要导出的Excel文件名</param> 132 public void ExportToExcel(DataSet ds,string strExcelFileName) 133![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 134 if (ds.Tables.Count==0 || strExcelFileName=="") return; 135 doExport(ds,strExcelFileName); 136 } 137![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 138 /// 导出用户选择的Excel文件 139 /// </summary> 140 /// <param name="ds">DataSet</param> 141 public void ExportToExcel(DataSet ds) 142![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 143 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 144 doExport(ds,saveFileDlg.FileName); 145 146 } 147![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 148 /// 执行导出 149 /// </summary> 150 /// <param name="ds">要导出的DataSet</param> 151 /// <param name="strExcelFileName">要导出的文件名</param> 152 private void doExport(DataSet ds,string strExcelFileName) 153![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 154 155 Excel.Application excel= new Excel.Application(); 156 157 // Excel.Workbook obj=new Excel.WorkbookClass(); 158 // obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); 159![](/Images/OutliningIndicators/InBlock.gif) 160 int rowIndex=1; 161 int colIndex=0; 162![](/Images/OutliningIndicators/InBlock.gif) 163 excel.Application.Workbooks.Add(true); 164 165 166 System.Data.DataTable table=ds.Tables[0] ; 167 foreach(DataColumn col in table.Columns) 168![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 169 colIndex++; 170 excel.Cells[1,colIndex]=col.ColumnName; 171 } 172![](/Images/OutliningIndicators/InBlock.gif) 173 foreach(DataRow row in table.Rows) 174![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 175 rowIndex++; 176 colIndex=0; 177 foreach(DataColumn col in table.Columns) 178![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 179 colIndex++; 180 excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString(); 181 } 182 } 183 excel.Visible=false; 184 // excel.Sheets[0] = "sss"; ///////////////////////////////?????????????????????// 185 excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null,null); 186 187 188 //wkbNew.SaveAs strBookName 189![](/Images/OutliningIndicators/InBlock.gif) 190![](/Images/OutliningIndicators/InBlock.gif) 191 //excel.Save(strExcelFileName); 192 excel.Quit(); 193 excel=null; 194 195 GC.Collect();//垃圾回收 196 } 197 #endregion 198![](/Images/OutliningIndicators/InBlock.gif) 199![](/Images/OutliningIndicators/ContractedSubBlock.gif) 从XML导入到Dataset#region 从XML导入到Dataset 200![](/Images/OutliningIndicators/InBlock.gif) 201![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 202 /// 从选择的XML文件导入 203 /// </summary> 204 /// <returns>DataSet</returns> 205 public DataSet ImportFromXML() 206![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 207 DataSet ds=new DataSet(); 208 System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog(); 209 openFileDlg.DefaultExt="xml"; 210 openFileDlg.Filter= "xml文件 (*.xml)|*.xml"; 211 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 212![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) try {ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);} 213![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) catch {} 214 return ds; 215 } 216![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 217 /// 从指定的XML文件导入 218 /// </summary> 219 /// <param name="strFileName">XML文件名</param> 220 /// <returns></returns> 221 public DataSet ImportFromXML(string strFileName) 222![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 223 if (strFileName=="") 224 return null; 225 DataSet ds=new DataSet(); 226![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) try {ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);} 227![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) catch {} 228 return ds; 229 } 230 231 #endregion 232![](/Images/OutliningIndicators/InBlock.gif) 233![](/Images/OutliningIndicators/ContractedSubBlock.gif) 从DataSet导出到XML#region 从DataSet导出到XML 234![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 235 /// 导出指定的XML文件 236 /// </summary> 237 /// <param name="ds">要导出的DataSet</param> 238 /// <param name="strXMLFileName">要导出的XML文件名</param> 239 public void ExportToXML(DataSet ds,string strXMLFileName) 240![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 241 if (ds.Tables.Count==0 || strXMLFileName=="") return; 242 doExportXML(ds,strXMLFileName); 243 } 244![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 245 /// 导出用户选择的XML文件 246 /// </summary> 247 /// <param name="ds">DataSet</param> 248 public void ExportToXML(DataSet ds) 249![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 250 System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 251 saveFileDlg.DefaultExt="xml"; 252 saveFileDlg.Filter= "xml文件 (*.xml)|*.xml"; 253 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 254 doExportXML(ds,saveFileDlg.FileName); 255 } 256![](/Images/OutliningIndicators/InBlock.gif) 257![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//**//**//// <summary> 258 /// 执行导出 259 /// </summary> 260 /// <param name="ds">要导出的DataSet</param> 261 /// <param name="strExcelFileName">要导出的XML文件名</param> 262 private void doExportXML(DataSet ds,string strXMLFileName) 263![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 264 try 265![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );} 266 catch(Exception ex) 267![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;} 268 } 269![](/Images/OutliningIndicators/InBlock.gif) 270 #endregion 271 } 272 } 273![](/Images/OutliningIndicators/None.gif) 274 public void ExportResult(DataSet ds) 275![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 276 HttpContext.Current.Response.Clear(); 277 HttpContext.Current.Response.Charset = ""; 278 HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; 279 StringWriter stringWrite = new StringWriter(); 280 HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); 281![](/Images/OutliningIndicators/InBlock.gif) 282 DataGrid dg = new DataGrid(); 283 dg.DataSource = ds.Tables[0]; 284 dg.DataBind(); 285 dg.RenderControl(htmlWrite); 286 HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=result.xls"); 287![](/Images/OutliningIndicators/InBlock.gif) 288 HttpContext.Current.Response.Write(stringWrite.ToString()); 289 HttpContext.Current.Response.End(); 290 } 291![](/Images/OutliningIndicators/None.gif) 292![](/Images/OutliningIndicators/None.gif)
|
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步