asp.net操作excel表格 asp.netexcel数据库 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 有几种方法,一是用ODBC象连接普通数据库那样连接Excel文件并读取数据,适合于简单的表格数据的读取;二是用NPOI这个组件直接读取,适合于表格比较复杂的情况:https://npoi.codeplex.com/========================http://www.webdiyer.com protected void UploadBtn_Click(object sender, EventArgs e) { if (AlarmInfo.HasFile) { DataTable dt = null; try { using (MemoryStream stream = new MemoryStream(AlarmInfo.FileBytes)) { int num = 0; dt = ExcelUtils.TranslateToTable(stream, 0); //TODO 操作DataTable就好 } } catch (Exception ex) { log4net.LogManager.GetLogger(GetType()).Error("数据上传出现错误!", ex); throw ex; } } else { JQueryAlert("请选择上传文件!"); } }最近刚做了个,ExcelUtils这个就好用着 using System;using System.Collections.Generic;using System.Web;using System.Data;using Net.SourceForge.Koogra.Excel;/// <summary> /// Excel工具类 /// </summary> public class ExcelUtils { private Net.SourceForge.Koogra.Excel.Workbook book; //private Net.SourceForge.Koogra.Excel2007.Workbook book2007; public ExcelUtils(string path) { this.book = new Workbook(path); } public ExcelUtils(System.IO.Stream stream) { this.book = new Workbook(stream); } protected DataTable SaveAsDataTable(Worksheet sheet) { DataTable dt = new DataTable(); uint minRow = sheet.Rows.MinRow; uint maxRow = sheet.Rows.MaxRow; Row firstRow = sheet.Rows[minRow]; uint minCol = firstRow.Cells.MinCol; uint maxCol = firstRow.Cells.MaxCol; for (uint i = minCol; i <= maxCol; i++) { dt.Columns.Add(firstRow.Cells[i].FormattedValue()); } for (uint i = minRow + 1; i <= maxRow; i++) { Row row = sheet.Rows[i]; if (row != null && row.Cells[0] != null && row.Cells[0].Value.ToString() != "") { DataRow dr = dt.NewRow(); for (uint j = minCol; j <= maxCol; j++) { Cell cell = row.Cells[j]; if (cell != null) { dr[Convert.ToInt32(j)] = cell.Value != null ? cell.FormattedValue() : string.Empty; } } dt.Rows.Add(dr); } } return dt; } public DataTable ToDataTable(int index) { Worksheet sheet = this.book.Sheets[index]; if (sheet == null) { throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在!", index)); } //新增自定义判断日期格式是否转换 2014.2.26 Gt double value = default(double); try { if (sheet.Rows.MaxRow > 0) { double.TryParse(sheet.Rows[1].Cells[2].FormattedValue(), out value); } } catch (Exception ex) { throw new ApplicationException("文件中日期列未调整单元格格式为日期!",ex); } return this.SaveAsDataTable(sheet); } public DataTable ToDataTable(string sheetName) { Worksheet sheet = this.book.Sheets.GetByName(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 = DateTime.MaxValue; 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) { ExcelUtils utils = new ExcelUtils(path); return utils.ToDataTable(sheetName); } /// <summary> /// 转换为DataTable(文件路径 表索引) /// </summary> public static DataTable TranslateToTable(string path, int sheetIndex) { ExcelUtils utils = new ExcelUtils(path); return utils.ToDataTable(sheetIndex); }/// <summary> /// 转换为DataTable(文件路径) /// </summary> public static DataTable TranslateToTable(string path) { ExcelUtils utils = new ExcelUtils(path); return utils.ToDataTable(0); } /// <summary> /// 转换为DataTable(内存流 表名) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName) { ExcelUtils utils = new ExcelUtils(stream); return utils.ToDataTable(sheetName); } /// <summary> /// 转换为DataTable(内存流 表索引) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex) { ExcelUtils utils = new ExcelUtils(stream); return utils.ToDataTable(sheetIndex); } /// <summary> /// 转换为DataTable(内存流) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream) { ExcelUtils utils = new ExcelUtils(stream); return utils.ToDataTable(0); } #endregion } /// <summary> /// Excel工具类 /// </summary> public class ExcelUtils2007{ private Net.SourceForge.Koogra.Excel2007.Workbook book; public ExcelUtils2007(string path) { this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(path); } public ExcelUtils2007(System.IO.Stream stream) { this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(stream); } protected DataTable SaveAsDataTable(Net.SourceForge.Koogra.Excel2007.Worksheet sheet) { DataTable dt = new DataTable(); uint minRow = sheet.CellMap.FirstRow; uint maxRow = sheet.CellMap.LastRow; Net.SourceForge.Koogra.Excel2007.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++) { Net.SourceForge.Koogra.Excel2007.Row row = sheet.GetRow(i); if (row != null) { DataRow dr = dt.NewRow(); for (uint j = minCol; j <= maxCol; j++) { Net.SourceForge.Koogra.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) { Net.SourceForge.Koogra.Excel2007.Worksheet sheet = this.book.GetWorksheet(0); if (sheet == null) { throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在!", index)); } return this.SaveAsDataTable(sheet); } public DataTable ToDataTable(string sheetName) { Net.SourceForge.Koogra.Excel2007.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) { ExcelUtils2007 utils = new ExcelUtils2007(path); return utils.ToDataTable(sheetName); } /// <summary> /// 转换为DataTable(文件路径 表索引) /// </summary> public static DataTable TranslateToTable(string path, int sheetIndex) { ExcelUtils2007 utils = new ExcelUtils2007(path); return utils.ToDataTable(sheetIndex); }/// <summary> /// 转换为DataTable(文件路径) /// </summary> public static DataTable TranslateToTable(string path) { ExcelUtils2007 utils = new ExcelUtils2007(path); return utils.ToDataTable(0); } /// <summary> /// 转换为DataTable(内存流 表名) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName) { ExcelUtils2007 utils = new ExcelUtils2007(stream); return utils.ToDataTable(sheetName); } /// <summary> /// 转换为DataTable(内存流 表索引) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex) { ExcelUtils2007 utils = new ExcelUtils2007(stream); return utils.ToDataTable(sheetIndex); } /// <summary> /// 转换为DataTable(内存流) /// </summary> public static DataTable TranslateToTable(System.IO.Stream stream) { ExcelUtils2007 utils = new ExcelUtils2007(stream); return utils.ToDataTable(0); } #endregion}using Net.SourceForge.Koogra.Excel; Refer this:http://www.cnblogs.com/insus/archive/2012/06/12/2545801.html 导出 vml问题 一个关于sql 批量Update的问题 JS设置flash的路径 cookie 加密?????????? 关于提交时验证的问题~ 请教一个GridView中动态绑定数据的问题 如何在DetailsView中实现CheckBoxList的编辑问题? IDbDataParameter是什么型的啊? 关于odbc连接oracle 8I js post html页面请求疑问 实际开发中entity framework code first模式和普通模式哪个用的多? 请问这段ios上传图片到C#
http://www.webdiyer.com
protected void UploadBtn_Click(object sender, EventArgs e)
{
if (AlarmInfo.HasFile)
{
DataTable dt = null;
try
{
using (MemoryStream stream = new MemoryStream(AlarmInfo.FileBytes))
{
int num = 0;
dt = ExcelUtils.TranslateToTable(stream, 0);
//TODO 操作DataTable就好
}
}
catch (Exception ex)
{
log4net.LogManager.GetLogger(GetType()).Error("数据上传出现错误!", ex);
throw ex;
}
}
else
{
JQueryAlert("请选择上传文件!");
}
}最近刚做了个,ExcelUtils这个就好用着
using System.Collections.Generic;
using System.Web;
using System.Data;
using Net.SourceForge.Koogra.Excel;/// <summary>
/// Excel工具类
/// </summary>
public class ExcelUtils
{
private Net.SourceForge.Koogra.Excel.Workbook book; //private Net.SourceForge.Koogra.Excel2007.Workbook book2007; public ExcelUtils(string path)
{
this.book = new Workbook(path);
}
public ExcelUtils(System.IO.Stream stream)
{
this.book = new Workbook(stream);
}
protected DataTable SaveAsDataTable(Worksheet sheet)
{
DataTable dt = new DataTable();
uint minRow = sheet.Rows.MinRow;
uint maxRow = sheet.Rows.MaxRow;
Row firstRow = sheet.Rows[minRow];
uint minCol = firstRow.Cells.MinCol;
uint maxCol = firstRow.Cells.MaxCol;
for (uint i = minCol; i <= maxCol; i++)
{
dt.Columns.Add(firstRow.Cells[i].FormattedValue());
}
for (uint i = minRow + 1; i <= maxRow; i++)
{
Row row = sheet.Rows[i];
if (row != null && row.Cells[0] != null && row.Cells[0].Value.ToString() != "")
{
DataRow dr = dt.NewRow();
for (uint j = minCol; j <= maxCol; j++)
{
Cell cell = row.Cells[j];
if (cell != null)
{
dr[Convert.ToInt32(j)] = cell.Value != null ? cell.FormattedValue() : string.Empty;
}
}
dt.Rows.Add(dr);
}
}
return dt;
} public DataTable ToDataTable(int index)
{
Worksheet sheet = this.book.Sheets[index];
if (sheet == null)
{
throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在!", index));
}
//新增自定义判断日期格式是否转换 2014.2.26 Gt
double value = default(double);
try
{
if (sheet.Rows.MaxRow > 0)
{
double.TryParse(sheet.Rows[1].Cells[2].FormattedValue(), out value);
}
}
catch (Exception ex)
{
throw new ApplicationException("文件中日期列未调整单元格格式为日期!",ex);
}
return this.SaveAsDataTable(sheet);
}
public DataTable ToDataTable(string sheetName)
{
Worksheet sheet = this.book.Sheets.GetByName(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 = DateTime.MaxValue;
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)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.ToDataTable(sheetName);
}
/// <summary>
/// 转换为DataTable(文件路径 表索引)
/// </summary>
public static DataTable TranslateToTable(string path, int sheetIndex)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.ToDataTable(sheetIndex);
}/// <summary>
/// 转换为DataTable(文件路径)
/// </summary>
public static DataTable TranslateToTable(string path)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.ToDataTable(0);
}
/// <summary>
/// 转换为DataTable(内存流 表名)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
{
ExcelUtils utils = new ExcelUtils(stream);
return utils.ToDataTable(sheetName);
}
/// <summary>
/// 转换为DataTable(内存流 表索引)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex)
{
ExcelUtils utils = new ExcelUtils(stream);
return utils.ToDataTable(sheetIndex);
}
/// <summary>
/// 转换为DataTable(内存流)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream)
{
ExcelUtils utils = new ExcelUtils(stream); return utils.ToDataTable(0);
}
#endregion
}
/// <summary>
/// Excel工具类
/// </summary>
public class ExcelUtils2007
{
private Net.SourceForge.Koogra.Excel2007.Workbook book; public ExcelUtils2007(string path)
{
this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(path);
}
public ExcelUtils2007(System.IO.Stream stream)
{
this.book = new Net.SourceForge.Koogra.Excel2007.Workbook(stream);
}
protected DataTable SaveAsDataTable(Net.SourceForge.Koogra.Excel2007.Worksheet sheet)
{
DataTable dt = new DataTable();
uint minRow = sheet.CellMap.FirstRow;
uint maxRow = sheet.CellMap.LastRow;
Net.SourceForge.Koogra.Excel2007.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++)
{
Net.SourceForge.Koogra.Excel2007.Row row = sheet.GetRow(i);
if (row != null)
{
DataRow dr = dt.NewRow();
for (uint j = minCol; j <= maxCol; j++)
{
Net.SourceForge.Koogra.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)
{
Net.SourceForge.Koogra.Excel2007.Worksheet sheet = this.book.GetWorksheet(0);
if (sheet == null)
{
throw new ApplicationException(string.Format("索引[{0}]所指定的电子表格不存在!", index));
}
return this.SaveAsDataTable(sheet);
}
public DataTable ToDataTable(string sheetName)
{
Net.SourceForge.Koogra.Excel2007.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)
{
ExcelUtils2007 utils = new ExcelUtils2007(path);
return utils.ToDataTable(sheetName);
}
/// <summary>
/// 转换为DataTable(文件路径 表索引)
/// </summary>
public static DataTable TranslateToTable(string path, int sheetIndex)
{
ExcelUtils2007 utils = new ExcelUtils2007(path);
return utils.ToDataTable(sheetIndex);
}/// <summary>
/// 转换为DataTable(文件路径)
/// </summary>
public static DataTable TranslateToTable(string path)
{
ExcelUtils2007 utils = new ExcelUtils2007(path);
return utils.ToDataTable(0);
}
/// <summary>
/// 转换为DataTable(内存流 表名)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
{
ExcelUtils2007 utils = new ExcelUtils2007(stream);
return utils.ToDataTable(sheetName);
}
/// <summary>
/// 转换为DataTable(内存流 表索引)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex)
{
ExcelUtils2007 utils = new ExcelUtils2007(stream);
return utils.ToDataTable(sheetIndex);
}
/// <summary>
/// 转换为DataTable(内存流)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream)
{
ExcelUtils2007 utils = new ExcelUtils2007(stream); return utils.ToDataTable(0);
}
#endregion
}using Net.SourceForge.Koogra.Excel;
http://www.cnblogs.com/insus/archive/2012/06/12/2545801.html