一、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 try107 {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 else122 {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组件，首先需要到官网下载最新组件（）。需要注意的是，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.Common16 {17 public class ExcelHelper18 {19 /// 20 /// 由DataSet导出数据流Stream21 /// 22 /// 要导出数据的DataSet23 /// 页签名称24 /// 数据流Stream 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 /// 57 /// 由DataSet导出Excel58 /// 59 /// 要导出数据的DataSet60 /// 指定Excel工作表名称61 /// 指定Excel页签名称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 }