读取EXCEL档为DataTable,后作处理导入数据库
来源:互联网 发布:个性简历 知乎 编辑:程序博客网 时间:2024/05/16 10:39
protected void btn_ToBom_Click(object sender, EventArgs e)
{
Pass = "";
Eorr = "";
txt_Pass.Text = "";
if (FileUpload1.HasFile)
{ //上传文档到服务器
if (UpLoadXls(FileUpload1))
{ //转换数据到Data表
ImportXlsToData(FileUpload1.FileName);
if ( Eorr.Trim().Length == 0&&ExcelDataTable != null)
{
//数据处理并导入数据库
SetMasterPassDT();
}
}
}
else
{
Eorr += "请选择要导入的EXCEL文档!\r\n";
}
//如果存在,删除文件
if (File.Exists(File_Name))
{
File.Delete(File_Name);
}
File_Name = "";
txt_Pass.Text = Eorr+Pass;
}
/// <summary>
/// Excel 转换成DataSet
/// </summary>
/// <param name="FileName"></param>
private void ImportXlsToData(string FileName)
{
string fileExtend = string.Empty;
try
{
if (FileName == string.Empty)
{
throw new ArgumentNullException("请选择导入的Excel文档!\r\n");
}
string oleDBConnString = String.Empty;
oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;";
oleDBConnString += "Data Source=" + Server.MapPath("../TempFileUpload/" + FileName.Trim());
oleDBConnString += ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
ds.Tables[0].Columns.Add("ID");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
if (dr != null)
{
dr["ID"] = i;
}
}
DataColumn[] keys = new DataColumn[1];
keys[0] = ds.Tables[0].Columns["ID"];
ds.Tables[0].PrimaryKey= keys;
ExcelDataTable = ds.Tables[0];
}
catch (Exception ex)
{
Eorr+= ex.Message;
}
}
///文件上传
private bool UpLoadXls(FileUpload FileUploadName)
{
string orifilename = string.Empty;
string uploadfilepath = string.Empty;
string modifyfilename = string.Empty;
string fileExtend = "";//文件扩展名
int fileSize = 0;//文件大小
try
{
if (FileUploadName.HasFile)
{
String fileName = FileUploadName.FileName;
//得到文件的大小
fileSize = FileUploadName.PostedFile.ContentLength;
if (fileSize == 0)
{
throw new Exception("导入的Excel文件大小为0,请检查是否正确!");
}
//得到扩展名
fileExtend = fileName.Substring(fileName.LastIndexOf(".") + 1);
if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
{
throw new Exception("你选择的文件格式不正确,只能导入EXCEL文件!");
}
//路径
uploadfilepath = Server.MapPath("~/TempFileUpload");
modifyfilename += "." + fileName.Substring(fileName.LastIndexOf(".") + 1);
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
orifilename = uploadfilepath + "\\" + fileName;
//如果存在,删除文件
if (File.Exists(orifilename))
{
File.Delete(orifilename);
}
// 上传文件
FileUploadName.PostedFile.SaveAs(orifilename);
File_Name = orifilename;
return true;
}
else
{
throw new Exception("请选择要导入的EXCEL文档!");
}
}
catch (Exception ex)
{
Eorr+= ex.Message;
return false;
}
}
- 读取EXCEL档为DataTable,后作处理导入数据库
- .NET读取Excel为datatable
- 导入EXCEL为DataTable的字符串
- C#读取Excel和CSV为DataTable
- 将EXCEL 中的数据导入,转化为DataTable
- DataTable导入Excel
- DataTable 导入Excel
- Excel导入DataTable
- DataTable数据导入Excel
- excel 导入到datatable
- 将DataTable导出为Excel或从Excel读取到DataTable
- OleDbDataAdapter与OleDbDataReader方式读取EXCEL并转换为DataTable
- C#Winformd读取excel文件数据转化为DataTable
- 读取Excel得到Datatable
- 读取Excel内容,导入数据库多张表!
- C# web 读取excel并导入数据库
- PHPExcel读取excel并导入数据库
- [***码农]当Excel数据慌乱数据 导入数据库后或转换数据的处理办法
- 由于录入原因库存项有而实际仓库类型及数量不一致
- 一个基于linux的聊天软件(并发服务器,sqlite3数据库,有注释)
- androd fsck分析
- poj 3625 Building Roads (最小生成树)
- source insight 常用快捷键(右键里大多功能有快捷键提示)
- 读取EXCEL档为DataTable,后作处理导入数据库
- 动态链接库
- keilc51中如何看堆栈的分配情况
- Android本地服务的启动 - zygote服务及Dalvik环境启动
- JavaScript push()方法
- Windows批处理中获取日期和时间
- Junit-学习笔记
- Android conversion to dalvik format failed with error 1的解决办法
- apache+tomcat+jk+ssl实现tomcat集群https服务(windows)