如何将Excel文件上传到sqlserver asp.net [英] How to Upload Excel file into sqlserver asp.net

问题描述

我正在使用.ods格式的Libre office excelsheet我需要将电子表格中的所有颜色和值上传到SQL SERVER

i'm using Libre office excelsheet which was in .ods format i need to upload all the coloumns and values inside the spreadsheet into the SQL SERVER

推荐答案

在ASP.net中使用SQLBULK将Excel文件导入SQL Server

此示例说明如何在ASP.Net中使用SQLBULK上载Excel文件，读取Excel文件数据，保存Excel文件数据并导入SQL Server 。



步骤：1创建一个Excel文件：





Step ：2在数据库中创建一个Sql表：





步骤：3现在，在Default.aspx中添加代码







< asp：fileupload id =fupUploadrunat =serverxmlns：asp =＃未知>



< asp：button id =btnImportfont-bold =trueforecolor =Whitexmlns：asp =＃unknown >



BackColor =＃136671Height =23pxrunat =serverText =导入Excel数据

onclick =btnImport_Click/&g t;





步骤：4在Default.aspx.cs中添加代码



使用System.IO添加这些NameSpace



;

使用System.Data.OleDb;

使用System.Data;





在导入按钮的Click事件中编写代码



protected void btnImport_Click（object sender，EventArgs e）

{

string strFilepPath;

DataSet ds = new DataSet （）;

string strConnection = ConfigurationManager.ConnectionStrings

[connectionString]。ConnectionString;

if（fupUpload.HasFile）

{

尝试

{

FileInfo fi = new FileInfo（fupUpload.PostedFile.FileName）;

string ext = fi.Extension;

if（ext ==。xls|| ext ==。xlsx）

{

string filename = Path.GetFullPath（fupUpload.PostedFile.FileName）;

string DirectoryPath = Server.MapPath（〜/ UploadExcelFile //）;

strFilepPath = DirectoryPath + fupUpload.FileName;

Directory.CreateDirectory（DirectoryPath）;

fupUpload.SaveAs（strFilepPath）;

string strConn = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =

+ strFilepPath +;扩展属性= \Excel 12.0

Xml; HDR = YES; IMEX = 1 \;

OleDbConnection conn = new OleDbConnection（strConn）;

conn.Open（） ;

OleDbCommand cmd = new OleDbCommand（SELECT * FROM [Sheet1

How to Import Excel File into SQL Server using SQLBULK in ASP.net
This example explains how to upload excel file, read Excel file data, save Excel file data and import into SQL Server using SQLBULK in ASP.Net.

Step:1 Create a Excel file:


Step:2 Create a Sql table in database:


Step:3 Now, add the code in "Default.aspx"



<asp:fileupload id="fupUpload" runat="server" xmlns:asp="#unknown">

<asp:button id="btnImport" font-bold="true" forecolor="White" xmlns:asp="#unknown">

BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />


Step:4 Add the code in "Default.aspx.cs"

Add these NameSpace

using System.IO;
using System.Data.OleDb;
using System.Data;


Write the code in Click Event of Import Button

protected void btnImport_Click(object sender, EventArgs e)
{
string strFilepPath;
DataSet ds = new DataSet();
string strConnection = ConfigurationManager.ConnectionStrings
["connectionString"].ConnectionString;
if (fupUpload.HasFile)
{
try
{
FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
string ext = fi.Extension;
if (ext == ".xls" || ext == ".xlsx")
{
string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
strFilepPath = DirectoryPath + fupUpload.FileName;
Directory.CreateDirectory(DirectoryPath);
fupUpload.SaveAs(strFilepPath);
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ strFilepPath + ";Extended Properties=\"Excel 12.0
Xml;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1

，conn）;

OleDbDataAdapter da = new OleDbDataAdapter（ cmd）;

da.Fill（ds）;

DeleteExcelFile（fupUpload.FileName）; //删除文件日志

SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnection，
SqlBulkCopyOptions.KeepIdentity）;

sqlBulk.DestinationTableName =Table_1;

sqlBulk.WriteToServer（ds.Tables [0]）;

conn.Close（）;

sqlBulk.Close（）;

ScriptManager.RegisterStartupScript（Page，GetType（），script1，

alert（'Excel文件成功导入DB'）;，true）;

返回;

}

else

{

ScriptManager.RegisterStartupScript（Page，GetType （），script1，

alert（'请仅上传excel文件'）;，true）;

返回;

}

}

catch（exception ex）

{

DeleteExcelFile（fupUpload.FileName）;

ScriptManager.RegisterStartupScript（Page，GetType（），script1，

alert（'error occurred ::+ ex.Message.ToString（）+'） ;，true）;

返回;

}

}

其他

{

ScriptManager.RegisterStartupScript（Page，GetType（），script1，

alert（'请上传excel文件'）;，true）;

返回;

}

}



protected void DeleteExcelFile（string Name）<如果（Directory.Exists（Request.PhysicalApplicationPath +

UploadExcelFile \\））

{

string [] logList = Directory.GetFiles（Request.PhysicalApplicationPath

+UploadExcelFile \\，* .xls）;

foreach（字符串登录logList）

{

FileInfo logInfo = new FileInfo（log）;

string logInfoName = logInfo.Name.Substring（0，

logInfo.Name.LastIndexOf（'。'））;

if（logInfoName.Length> = Name.Length）

{

if（Name.Equals（logInfoName.Substring（0，Name.Length） ）））

{

logInfo.Delete（）;

}

}

}

}

}





Kishor Makwana
软件工程师

Insight Softech

www.insightsoftech.com

如何将其保存为CSV然后将其导入SQL Server（支持的格式）？

How about saving it as a CSV then importing that into SQL Server (which is a supported format)?

这篇关于如何将Excel文件上传到sqlserver asp.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！