http://www.cnblogs.com/eleven11/archive/2011/08/29/2158244.html
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
using System; using System.Collections.Generic; using System.Text; using System.Data; using Net.SourceForge.Koogra.Excel2007; using Net.SourceForge.Koogra; namespace Test_Koogra.Excel2007 { public class Excel2007Utils { private Workbook book; public Excel2007Utils(string path) { this.book = new Workbook(path); } public Excel2007Utils(System.IO.Stream stream) { this.book = new Workbook(stream); } protected DataTable SaveAsDataTable(Worksheet sheet) { DataTable dt = new DataTable(); uint minRow = sheet.CellMap.FirstRow; uint maxRow = sheet.CellMap.LastRow; Row firstRow = sheet.GetRow(minRow); uint minCol = sheet.CellMap.FirstCol; uint maxCol = sheet.CellMap.LastCol; for (uint i = minCol; i <= maxCol; i++) { dt.Columns.Add(firstRow.GetCell(i).GetFormattedValue()); } for (uint i = minRow + 1; i <= maxRow; i++) { Row row = sheet.GetRow(i); if (row != null) { DataRow dr = dt.NewRow(); for (uint j = minCol; j <= maxCol; j++) { ICell cell = row.GetCell(j); if (cell != null) { dr[Convert.ToInt32(j)] = cell.Value != null ? cell.Value.ToString() : string.Empty; } } dt.Rows.Add(dr); } } return dt; } public DataTable ToDataTable(int index) { Worksheet sheet = this.book.GetWorksheet(index); if (sheet == null) { throw new ApplicationException(string.Format("索引[{0}]所指定的電子表格不存在!", index)); } return this.SaveAsDataTable(sheet); } public DataTable ToDataTable(string sheetName) { Worksheet sheet = this.book.GetWorksheetByName(sheetName); if (sheet == null) { throw new ApplicationException(string.Format("名稱[{0}]所指定的電子表格不存在!", sheetName)); } return this.SaveAsDataTable(sheet); } #region 靜態方法 /// <summary> /// 單元格格式為日期時間,使用此方法轉換為DateTime類型,若解析失敗則返回『0001-01-01』 /// </summary> public static DateTime ParseDateTime(string cellValue) { DateTime date = default(DateTime); double value = default(double); if (double.TryParse(cellValue, out value)) { date = DateTime.FromOADate(value); } else { DateTime.TryParse(cellValue, out date); } return date; } /// <summary> /// 轉換為DataTable(文件路徑+表名) /// </summary> public static DataTable TranslateToTable(string path, string sheetName) { Excel2007Utils utils = new Excel2007Utils(path); return utils.ToDataTable(sheetName); } /// <summary> /// 轉換為DataTable(文件路徑+表索引) /// </summary> public static DataTable TranslateToTable(string path, int sheetIndex) { Excel2007Utils utils = new Excel2007Utils(path); return utils.ToDataTable(sheetIndex); } /// <summary> /// 轉換為DataTable(文件路徑) /// </summary> public static DataTable TranslateToTable(string path) { Excel2007Utils utils = new Excel2007Utils(path); return utils.ToDataTable(0); } /// <summary> /// 轉換為DataTable(內存流+表名) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName) { Excel2007Utils utils = new Excel2007Utils(stream); return utils.ToDataTable(sheetName); } /// <summary> /// 轉換為DataTable(內存流+表索引) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex) { Excel2007Utils utils = new Excel2007Utils(stream); return utils.ToDataTable(sheetIndex); } /// <summary> /// 轉換為DataTable(內存流) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream) { Excel2007Utils utils = new Excel2007Utils(stream); return utils.ToDataTable(0); } #endregion } }
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
protected void btnImport_Click(object sender, EventArgs e) { if (fuFile.HasFile) { DataTable dt = null; try { using (System.IO.MemoryStream stream = new System.IO.MemoryStream(fuFile.FileBytes)) { string filename = fuFile.PostedFile.FileName; //獲取初始文件名 int i = filename.LastIndexOf("."); //取得文件名中最後一個"."的索引 string newext = filename.Substring(i); //獲取文件擴展名 if (newext != ".xsl" && newext != ".xlsx") { Response.Write("文件格式不正確!"); Response.End(); } if (newext != ".xslx") dt = Excel2007Utils.TranslateToTable(stream, "sheet1"); else dt = ExcelUtils.TranslateToTable(stream, "sheet1"); } this.ImportData(dt); } catch (Exception ex) { lblMessage.Text = "<p><span style=\"color:Red;\">處理數據文件錯誤:</span></p>"; lblMessage.Text += "<div style=\"color:Red;\">" + Server.HtmlEncode(ex.Message) + "</div>"; } } else { lblMessage.Text = "<p><span style=\"color:Red;\">請選擇數據文件!</span></p>"; //Response.Write("<script language=\"javascript\">alert('請選擇數據文件!')</script>"); } }