最近工作中需要做一个把用户信息作为excel导出的功能，就顺便整理搜集了一些常用的导出文件的源代码，以供以后不时之需，也希望能给大家带来一些方便
一、DataSet数据集内数据转化为Excel
// 作用：把DataSet数据集内数据转化为Excel文件
// 描述：这些关于Excel的导出方法，基本可以实现日常须要，其中有些方法可以把数据导出后
// 生成Xml格式，再导入数据库！
// 备注：请引用Office相应COM组件，导出Excel对象的一个方法要调用其中的一些方法和属性。
public void DataSetToExcel(DataSet ds,string FileName) { try { //Web页面定义 //System.Web.UI.Page mypage=new System.Web.UI.Page(); HttpResponse resp; resp=HttpContext.Current.Response; resp.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-disposition","attachment;filename="+FileName+".xls"); resp.ContentType="application/ms-excel"; //变量定义 string colHeaders=null; string Is_item=null; //显示格式定义//////////////// //文件流操作定义 //FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write); //StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("GB2312")); StringWriter sfw=new StringWriter(); //定义表对象与行对象，同时用DataSet对其值进行初始化 System.Data.DataTable dt=ds.Tables[0]; DataRow[] myRow=dt.Select(); int i=0; int cl=dt.Columns.Count; //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 for(i=0;i<cl;i++) { //if(i==(cl-1)) //最后一列，加\n // colHeaders+=dt.Columns[i].Caption.ToString(); //else colHeaders+=dt.Columns[i].Caption.ToString()+"\t"; } sfw.WriteLine(colHeaders); //sw.WriteLine(colHeaders); //逐行处理数据 foreach(DataRow row in myRow) { //当前数据写入 for(i=0;i<cl;i++) { //if(i==(cl-1)) // Is_item+=row[i].ToString()+"\n"; //else Is_item+=row[i].ToString()+"\t"; } sfw.WriteLine(Is_item); //sw.WriteLine(Is_item); Is_item=null; } resp.Write(sfw); //resp.Clear(); resp.End(); } catch(Exception e) { throw e; } }
二、DataSet数据集内数据转化为Excel文件（2）
/// <summary>
/// 作用：把DataSet数据集内数据转化为Excel文件
/// 描述：导出Excel文件
/// 备注：请引用Office相应COM组件，导出Excel对象的一个方法要调用其中的一些方法和属性。
/// </summary>
public class ExportFiles { private string filePath = ""; public ExportFiles(string excel_path) { // // TODO: 在此处添加构造函数逻辑 // filePath = excel_path; } /// <summary> /// 将指定的Dataset导出到Excel文件 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool ExportToExcel(System.Data.DataSet ds, string ReportName) { if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("数据集为空"); } Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass(); Workbook xlbook = xlapp.Workbooks.Add(true); Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1]; Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]); range.MergeCells = true; xlapp.ActiveCell.FormulaR1C1 = ReportName; xlapp.ActiveCell.Font.Size = 20; xlapp.ActiveCell.Font.Bold = true; xlapp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; int colIndex = 0; int RowIndex = 2; //开始写入每列的标题 foreach (DataColumn dc in ds.Tables[0].Columns) { colIndex++; xlsheet.Cells[RowIndex, colIndex] = dc.Caption; } //开始写入内容 int RowCount = ds.Tables[0].Rows.Count;//行数 for (int i = 0; i < RowCount; i++) { RowIndex++; int ColCount = ds.Tables[0].Columns.Count;//列数 for (colIndex = 1; colIndex <= ColCount; colIndex++) { xlsheet.Cells[RowIndex, colIndex] = ds.Tables[0].Rows[i][colIndex - 1];//dg[i, colIndex - 1]; xlsheet.Cells.ColumnWidth = ds.Tables[0].Rows[i][colIndex - 1].ToString().Length; } } xlbook.Saved = true; xlbook.SaveCopyAs(filePath); xlapp.Quit(); GC.Collect(); return true; } public bool ExportToExcelOF(System.Data.DataSet ds, string ReportName) { if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("数据集为空"); } string FileName = filePath; //System.Data.DataTable dt = new System.Data.DataTable(); FileStream objFileStream; StreamWriter objStreamWriter; string strLine = ""; objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write); objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode); strLine = ReportName; objStreamWriter.WriteLine(strLine); strLine = ""; for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { strLine = strLine + ds.Tables[0].Columns[i].ColumnName.ToString() + " " + Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine = ""; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { strLine = strLine + (i + 1) + Convert.ToChar(9); for (int j = 1; j < ds.Tables[0].Columns.Count; j++) { strLine = strLine + ds.Tables[0].Rows[i][j].ToString() + Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine = ""; } objStreamWriter.Close(); objFileStream.Close(); //Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass(); //Workbook xlbook = xlapp.Workbooks.Add(true); //Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1]; //Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]); //range.EntireColumn.AutoFit(); //xlapp.Quit(); return true; } }
三、导出GridView到Excel
//导出GridView到Excel中的关键之处
//用法： ToExcel(GVStaff, TextBox1.Text);
public static void ToExcel(System.Web.UI.Control ctl,string FileName) { HttpContext.Current.Response.Charset ="UTF-8"; HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default; HttpContext.Current.Response.ContentType ="application/ms-excel"; HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls"); ctl.Page.EnableViewState =false; System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); } 必须有下面这句！否则不会通过！ public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for }
四、DataTable导出到Excel
public class DataTableToExcel { private DataTableToExcel() { } /// <summary> /// 导出Excel /// </summary> /// <param name="dt">要导出的DataTable</param> public static void ExportToExcel(System.Data.DataTable dt ) { if (dt == null) return; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { // lblMsg.Text = "无法创建Excel对象，可能您的电脑未安装Excel"; MessageBox.Show( "无法创建Excel对象，可能您的电脑未安装Excel" ); return; } System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog(); saveDia.Filter = "Excel|*.xls"; saveDia.Title = "导出为Excel文件"; if(saveDia.ShowDialog()== System.Windows.Forms.DialogResult.OK && !string.Empty.Equals(saveDia.FileName)) { Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 Microsoft.Office.Interop.Excel.Range range = null; long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; string fileName = saveDia.FileName; //写入标题 for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; //range.Interior.ColorIndex = 15;//背景颜色 range.Font.Bold = true;//粗体 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //range.ColumnWidth = 4.63;//设置列宽 //range.EntireColumn.AutoFit();//自动调整列宽 //r1.EntireRow.AutoFit();//自动调整行高 } //写入内容 for (int r = 0; r < dt.DefaultView.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i]; range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1]; range.Font.Size = 9;//字体大小 //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); range.EntireColumn.AutoFit();//自动调整列宽 } rowRead++; percent = ((float)(100 * rowRead)) / totalCount; System.Windows.Forms.Application.DoEvents(); } range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; if (dt.Columns.Count > 1) { range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin; } try { workbook.Saved = true; workbook.SaveCopyAs(fileName); } catch (Exception ex) { //lblMsg.Text = "导出文件时出错,文件可能正被打开！\n" + ex.Message; MessageBox.Show( "导出文件时出错,文件可能正被打开！\n" + ex.Message ); return; } workbooks.Close(); if (xlApp != null) { xlApp.Workbooks.Close(); xlApp.Quit(); int generation = System.GC.GetGeneration(xlApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 #region 强行杀死最近打开的Excel进程 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); System.DateTime startTime = new DateTime(); int m, killId = 0; for (m = 0; m < excelProc.Length; m++) { if (startTime < excelProc[m].StartTime) { startTime = excelProc[m].StartTime; killId = m; } } if (excelProc[killId].HasExited == false) { excelProc[killId].Kill(); } #endregion MessageBox.Show( "导出成功!" ); } } }
五、DataTable导出到excel（2）
StringWriter stringWriter = new StringWriter(); HtmlTextWriter htmlWriter = new HtmlTextWriter( stringWriter ); DataGrid excel = new DataGrid(); System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle(); System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle(); System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle(); AlternatingStyle.BackColor = System.Drawing.Color.LightGray; headerStyle.BackColor =System.Drawing.Color.LightGray; headerStyle.Font.Bold = true; headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;; excel.AlternatingItemStyle.MergeWith(AlternatingStyle); excel.HeaderStyle.MergeWith(headerStyle); excel.ItemStyle.MergeWith(itemStyle); excel.GridLines = GridLines.Both; excel.HeaderStyle.Font.Bold = true; excel.DataSource = dt.DefaultView;//输出DataTable的内容 excel.DataBind(); excel.RenderControl(htmlWriter); string filestr = "d:\\data\\"+filePath; //filePath是文件的路径 int pos = filestr.LastIndexOf( "\\"); string file = filestr.Substring(0,pos); if( !Directory.Exists( file ) ) { Directory.CreateDirectory(file); } System.IO.StreamWriter sw = new StreamWriter(filestr); sw.Write(stringWriter.ToString()); sw.Close();