Excel導入 導出 兼容各個版本服務器不裝EXCEL也可以

  1 給出 demo源碼： http://pan.baidu.com/s/1hqGMudY            提取碼：pw4n首先要引用 NPOI.dll   （可在網上下載！）  2 //導入  3 public void OnSubmit()  4         {  5             string path = Server.MapPath("/upload/201410/27/201410271103461051.xls");  6             FileStream fs = File.Open(path, FileMode.Open);  7             System.Data.DataTable dt = ConvertToDataTable(fs);  8               9             foreach (DataRow row in dt.Rows) 10             { 11                 if (row["Mobile1"] != null) 12                 { 13                     Response.Write(row["Mobile1"].ToString() + " " + row["Mobile2"].ToString() + " 14 "); 15                 } 16             } 17             Response.End(); 18         } 19  20  21 //excel轉DataTable 22 public static DataTable ConvertToDataTable(System.IO.Stream excelFileStream) 23         { 24             HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(excelFileStream); 25             DataTable dt = new DataTable(); 26             HSSFSheet sheet = (HSSFSheet)HSSFWorkbook.GetSheetAt(0); 27             System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 28             int n = 0; 29             while (rows.MoveNext()) 30             { 31                 HSSFRow row = (HSSFRow)rows.Current; 32                 if (n == 0) 33                 { 34                     for (int i = 0; i < row.LastCellNum; i++) 35                     { 36                         HSSFCell cell = (HSSFCell)row.GetCell(i); 37                         if (cell == null) 38                             continue; 39                         DataColumn column = new DataColumn(cell.StringCellValue); 40  41                         dt.Columns.Add(column); 42                     } 43                 } 44                 else 45                 { 46                     DataRow dtRow = dt.NewRow(); 47                     string rValue = ""; 48                     for (int i = 0, j = 0; i < row.LastCellNum; i++) 49                     { 50                         HSSFCell cell = (HSSFCell)row.GetCell(i); 51                         if (cell == null) 52                         { 53                             dtRow[i] = ""; 54                         } 55                         else 56                         { 57                             dtRow[j] = cell.ToString(); 58                             rValue = cell.ToString(); 59                             j++; 60                         } 61                     } 62                     if (string.IsNullOrEmpty(rValue.Trim())) 63                         break; 64                     dt.Rows.Add(dtRow); 65                 } 66                 n++; 67             } 68             return dt; 69  70         } 71   72 //導出 73 ///  74  75         /// 將明細表導出到Excel 76  77         ///  78  79         ///  80 要導入到Excel中的中文表頭的Sql,將需要的字段通過sql as成中文名 81 /// 82  83 /// 1 成功 -1失敗 84 PRotected int ExportToExcel(string sql, HttpResponse response) 85 { 86 DataTable dt = CommOtherBLL.ExportToExcel(sql); //要導出的表 87 if (dt != null && dt.Rows.Count > 0) 88 { 89 StringBuilder strContent = new StringBuilder(); 90 for (int i = 0; i < dt.Columns.Count; i++) 91 strContent.Append(dt.Columns[i] + "/t"); 92 strContent.Append("/n"); 93 for (int i = 0; i < dt.Rows.Count; i++) 94 { 95 for (int y = 0; y < dt.Columns.Count; y++) 96 strContent.Append(dt.Rows[i][y] + "/t"); 97 strContent.Append("/n"); 98 } 99 response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("excel", System.Text.Encoding.UTF8) + ".xls");100 response.ContentType = "application/ms-excel";101 response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");102 response.Write(strContent.ToString());103 response.End();104 return 1;105 }106 else107 return -1;108 }