62,046
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
insus 2014-04-11
- 打赏
- 举报
王志威丶 2014-04-11
- 打赏
- 举报
/// <summary>
/// Excel导入获取表格数据
/// </summary>
/// <param name="filenameurl"></param>
/// <param name="table"></param>
/// <returns></returns>
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$] where 教师姓名 is not null ", conn);
odda.Fill(ds, table);
return ds;
} protected void Button_Dr_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
if (MdGridView1.Rows.Count < 1)
{
if (FileUpload.HasFile == false)//是否已选择文件
{
Sharefunc.ShowMessage("请选择文件", this, this.UpdatePanel1);
return;
}
string ISxls = System.IO.Path.GetExtension(FileUpload.FileName);//获取文件的扩展名
if (ISxls != ".xls")
{
Sharefunc.ShowMessage("只能选择Excel文件(文件格式为xls)", this, this.UpdatePanel1);
return;
}
string FileName = FileUpload.FileName;//获取文件名称
string savePath = Server.MapPath("~\\Upload\\" + FileName);//文件保存路径 *Server.MapPath获取虚拟服务器相对路径
if (!System.IO.Directory.Exists(Server.MapPath("~\\Upload\\")))
{
System.IO.Directory.CreateDirectory(Server.MapPath("~\\Upload\\"));
}
FileUpload.SaveAs(savePath);//保存文件到服务器
ds = ExecleDs(savePath, "JSXX");//调用已有方法获取Excel中的数据
}
else
{
ds = ExecleDs(ViewState["SavePath"].ToString(), "JSXX");
}
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Sharefunc.ShowMessage("Excel数据为空", this, this.UpdatePanel1);
return;
}
for (int i = 0; i < dr.Length; i++)
{
string id = GlobalClass._DbAccess.GetID();
string Code = Sharefunc.getbh("", "js_code", "T_TEACHER_PXJS", "1", "1", 10);
string Insert = " Insert into T_TEACHER_PXJS(js_id,js_code,js_name,js_sex,js_birthday,js_nation,js_nativeplace,js_idnumber,js_higheduction,js_graduatedate,JS_TRANEXPERTISE,JS_TECHNICALTIT,JS_JOBSENIORITYCARD,JS_PHONE,JS_MOBILEPHONE,JS_EMAIL,JS_WORKHISTORY,JS_REGUSERID,JS_REGUSERNAME,JS_REGISTRANTDATE,JS_REMARK) values("
+ "'" + id + "','" + Code + "','" + dr[i]["教师姓名"].ToString() + "','" + dr[i]["性别"].ToString() + "',to_date('" + dr[i]["出生年月"].ToString() + "','yyyy-MM-dd HH24:mi:ss'),'" + dr[i]["民族"].ToString() + "','" + dr[i]["籍贯"].ToString() + "','" + dr[i]["身份证号"].ToString() + "','" + dr[i]["最高学历"].ToString() + "',"
+ "to_date('" + dr[i]["毕业日期"].ToString() + "','yyyy-MM-dd HH24:mi:ss'),'" + dr[i]["培训专长"].ToString() + "','" + dr[i]["技术职称"].ToString() + "','" + dr[i]["从业资格证书"].ToString() + "','" + dr[i]["家庭电话"].ToString() + "','" + dr[i]["移动电话"].ToString() + "','" + dr[i]["Email"].ToString() + "','" + dr[i]["工作履历"].ToString() + "','" + dr[i]["备注"].ToString() + "','" + Session["_User_GUID"].ToString() + "','" + Session["_User_Name"].ToString() + "',sysdate)";
GlobalClass._DbAccess.ExecuteCommand(Insert);
}
Sharefunc.ShowMessage("导入成功", this, this.UpdatePanel1);
Sharefunc.Response("<script>window.close();</script>", this, this.UpdatePanel1);
}
catch (Exception ex)
{
Sharefunc.ShowMessage(ex.Message, this, this.UpdatePanel1);
}
}
yingyuebingya 2014-04-11
- 打赏
- 举报
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
}
yingyuebingya 2014-04-11
- 打赏
- 举报
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("请选择上传文件！");
}
}
nitaiyoucala 2014-04-11
- 打赏
- 举报
用npoi试试啊
nelazy 2014-03-25
- 打赏
- 举报
给你个思路，先把文件上传到后台服务器，在服务器上进行操作
webdiyer 2014-03-25
- 打赏
- 举报
有几种方法，一是用ODBC象连接普通数据库那样连接Excel文件并读取数据，适合于简单的表格数据的读取；二是用NPOI这个组件直接读取，适合于表格比较复杂的情况：https://npoi.codeplex.com/
========================
http://www.webdiyer.com
刘小安 2014-03-25
- 打赏
- 举报
