一、winform形式导出Excel
此方法适用于winform项目导出Excel，使用前需要引用Excel.dll，此处是直接用ds导出Excel，导出方法类GetExport如下：
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.IO; 5 using System.Text; 6 using System.Windows.Forms; 7 using Excel; 8 9 namespace XX.Common 10 { 11 class ExportExcel 12 { 13 14 public void GetExport(DataSet ds) 15 { 16 if (ds == null) 17 { 18 return; 19 } 20 string saveFileName = ""; 21 bool fileSaved = false; 22 SaveFileDialog saveDialog = new SaveFileDialog(); 23 saveDialog.DefaultExt = "xls"; 24 saveDialog.Filter = "Excel文件|*.xls"; 25 saveDialog.FileName = "name";//Excel文件名称 26 saveDialog.ShowDialog(); 27 saveFileName = saveDialog.FileName; 28 if (saveFileName.IndexOf(":") < 0) 29 { 30 return; 31 } 32 //被点了取消 33 34 Excel.Application xlApp = new Excel.Application(); 35 if (xlApp == null) 36 { 37 MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); 38 return; 39 } 40 Excel.Workbooks workbooks = xlApp.Workbooks; 41 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); 42 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 43 Excel.Range range; 44 //string oldCaption = DateTime.Today.ToString("yy-MM-dd"); 45 long totalCount = ds.Tables[0].Rows.Count; 46 long rowRead = 0; 47 //float percent = 0;//本次不显示进度，因此注释掉，需要的话可以放开运用 48 //worksheet.Cells[1, 1] = "评分结果"; 49 //写入字段 50 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) 51 { 52 //worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName; 53 //range = (Excel.Range)worksheet.Cells[1, i + 1]; 54 ///////此处直接将列名写死，因为ds中直接取出的是英文字段名，若取出的是中文列名可直接用上面两行代码写入(根据实际需要灵活运用即可)//////////// 55 worksheet.Cells[1, 1] = "第一行第一列"; 56 worksheet.Cells[1, 2] = "第一行第二列"; 69 //range = (Excel.Range)worksheet.Cells[1, i + 4];//此处是为列名加样式（底色，加粗等），此次不加样式 70 //range.Interior.ColorIndex = 15; 71 //range.Font.Bold = true; 72 } 73 //写入数值 74 for (int r = 0; r < ds.Tables[0].Rows.Count; r++) 75 { 76 for (int i = 0; i < ds.Tables[0].Columns.Count - 1; i++) 77 { 78 worksheet.Cells[r + 2, 1] = r + 1; 79 worksheet.Cells[r + 2, i + 2] = ds.Tables[0].Rows[r][i]; 80 } 81 rowRead++; 82 //percent = ((float)(100 * rowRead)) / totalCount; 83 //this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度. 84 85 System.Windows.Forms.Application.DoEvents(); 86 } 87 //this.lbl_process.Visible = false; //label可见性，此次不用 88 range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 1, ds.Tables[0].Columns.Count + 1]); 89 range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null); 90 91 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; 92 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; 93 94 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; 95 96 if (ds.Tables[0].Columns.Count > 1) 97 { 98 range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; 99 100 range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; 101 range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; 102 } 103 104 if (saveFileName != "") 105 { 106 try 107 { 108 109 workbook.Saved = true; 110 workbook.SaveCopyAs(saveFileName); 111 112 fileSaved = true; 113 } 114 catch (Exception ex) 115 { 116 117 fileSaved = false; 118 MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message); 119 } 120 } 121 else 122 { 123 fileSaved = false; 124 125 } 126 xlApp.Quit(); 127 GC.Collect();//强行销毁 128 if (fileSaved && File.Exists(saveFileName)) 129 { 130 //System.Diagnostics.Process.Start(saveFileName); 131 MessageBox.Show("导出成功！", "通知"); 132 } 133 134 } 135 } 136 }
二、Web形式导出Excel
Web版本导出，此处我使用了NPOI组件，首先需要到官网下载最新组件（http://npoi.codeplex.com/）。需要注意的是，NPOI组件也可以实现winform项目的Excel导出。具体实现导出功能的类如下：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data; 6 using System.IO; 7 using NPOI; 8 using NPOI.HPSF; 9 using NPOI.HSSF; 10 using NPOI.HSSF.UserModel; 11 using NPOI.POIFS; 12 using NPOI.Util; 13 using System.Text; 14 15 namespace XX.Common 16 { 17 public class ExcelHelper 18 { 19 /// <summary> 20 /// 由DataSet导出数据流Stream 21 /// </summary> 22 /// <param name="sourceDs">要导出数据的DataSet</param> 23 /// <param name="sheetName">页签名称</param> 24 /// <returns>数据流Stream</returns> 25 private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName) 26 { 27 HSSFWorkbook workbook = new HSSFWorkbook(); 28 MemoryStream ms = new MemoryStream(); 29 string[] sheetNames = sheetName.Split(‘,‘); 30 for (int i = 0; i < sheetNames.Length; i++) 31 { 32 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]); 33 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); 34 // handling header. 35 foreach (DataColumn column in sourceDs.Tables[i].Columns) 36 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 37 // handling value. 38 int rowIndex = 1; 39 foreach (DataRow row in sourceDs.Tables[i].Rows) 40 { 41 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); 42 foreach (DataColumn column in sourceDs.Tables[i].Columns) 43 { 44 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); 45 } 46 rowIndex++; 47 } 48 } 49 workbook.Write(ms); 50 ms.Flush(); 51 ms.Position = 0; 52 workbook = null; 53 return ms; 54 } 55 56 /// <summary> 57 /// 由DataSet导出Excel 58 /// </summary> 59 /// <param name="sourceDs">要导出数据的DataSet</param> 60 /// <param name="fileName">指定Excel工作表名称</param> 61 /// <param name="sheetName">指定Excel页签名称</param> 62 public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName) 63 { 64 MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream; 65 string UserAgent = HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower(); 66 // Firfox和IE下输出中文名显示正常 67 if (UserAgent.IndexOf("firefox") == -1) 68 { 69 fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8); 70 } 71 HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8"; 72 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 73 HttpContext.Current.Response.BinaryWrite(ms.ToArray()); 74 HttpContext.Current.Response.End(); 75 ms.Close(); 76 ms = null; 77 } 78 } 79 }