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 }

 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         /// <summary>20         /// 由DataSet导出数据流Stream21         /// </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导出Excel58         /// </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 }