asp. net implements the method of importing multiple sheet data from Excel into SQLSERVER
- 2021-07-10 19:23:25
- OfStack
This article illustrates how asp. net implements the method of importing multiple sheet data from Excel into SQLSERVER. Share it for your reference, as follows:
public DataSet GetDataSet(string filePath)
{
string Connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbConnection Conn = new OleDbConnection(Connstr);
// Create ArrayList Object Store all sheetname
ArrayList sheetNamelist = new ArrayList();
// Get configuration Excel Medium sheet Total ( This is configured according to project requirements ) If you need to import Excel Form all sheet Data, this code is deleted
int sheetCount = Convert.ToInt32(ConfigurationManager.AppSettings["sheetCount"].ToString());
DataSet dsExcel = new DataSet();
try
{
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
DataTable dtExcelSchema = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string sheetName = string.Empty;
if (dtExcelSchema.Rows.Count > sheetCount)
{
Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' I'm sorry ! You upload Excel Documents sheet Too much total cannot be greater than 10 A sheet..!! ')
// --></mce:script>");
return;
}
else
{
for (int j = 0; j < dtExcelSchema.Rows.Count; j++)
{
sheetName = String.Format("Sheet{0}$", j + 1);
sheetNamelist.Add(sheetName);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
finally
{
Conn.Close();
}
try
{
string strSQL = string.Empty;
for (int i = 0; i < sheetNamelist.Count; i++)
{
strSQL = "select * from [" + sheetNamelist[i].ToString() + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, Conn);
DataTable dtExcel = new DataTable(sheetNamelist[i].ToString());
da.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString(), ex);
}
}
// From Excel Fetch data from a table Insert the fetched data into the database
public void InsertData(DataSet ds) {
string strSQL=string.Empty;
if (ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables.Count; j++)
{
for(int i=0;i<ds.Tables[j].Rows.Count;i++)
{
DataRow dr=ds.Tables[j].Rows[i];
// Group name
string groupname = dr[" Group name "].ToString().Trim();
// Contact person
string contactName = dr[" Contact person "].ToString().Trim();
// Mobile phone number
string mobile = dr[" Mobile phone number "].ToString().Trim();
// Company name
string companyName = dr[" Company name "].ToString().Trim();
// Public number
string officeNum = dr[" Office number "].ToString().Trim();
// Home number
string homeNum = dr[" Home number "].ToString().Trim();
// Mailbox
string Email = dr[" Post Box "].ToString().Trim();
// Contact address
string address = dr[" Contact address "].ToString().Trim();
// Creation time
string createtime = dr[" Creation time "].ToString().Trim();
// Gender
string Sex = dr[" Gender "].ToString().Trim();
// Type of mobile phone package
string mobileType = dr[" Type of mobile phone package "].ToString().Trim();
// Do you want to enable a communication assistant
string isOpen = dr[" Do you want to enable a communication assistant "].ToString().Trim();
//SQL Statement
strSQL = "insert into msm_Excel(groupName,Mobile,Name,companyName,officeNum,homeNum,Emial,address,Createtime,Sex,mobileType,isOpen)values('" + groupname + "','" + mobile + "','" + contactName + "','" + companyName + "','" + officeNum + "','" + homeNum + "','" + Email + "','" + address + "','" + createtime + "','" + Sex + "','" + mobileType + "','" + isOpen + "')";
try
{
int n = SQLHelper.SqlDataExecute(strSQL);
if (n > 0)
{
Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Data insertion succeeded !')
// --></mce:script>");
Label1.Text = "1 Co-successful insert " + ds.Tables[j].Rows.Count.ToString() + " Bar data ";
}
else
{
Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Server busy! Please try again later ..!')
// --></mce:script>");
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
else {
Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' This Excel No data in file !!!')
// --></mce:script>");
}
}
// Call
// Get the upload file name
string fileName = FileUpload1.FileName;
// Determine whether there is an uploaded file
if (FileUpload1.PostedFile.FileName.Length == 0) {
Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Please select what you want to upload Excel Documents !!')
// --></mce:script>");
}
// Determine whether the uploaded file type is correct
else if (!Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xls") && !Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xlsx"))
{
Page.RegisterStartupScript("", "<script>alert(' I'm sorry ! The file type you uploaded is incorrect ! Upload only Excel File of type !')</script.");
}
else
{
// Get the uploaded file path
filePath = Server.MapPath("TxtFiles//") + DateTime.Now.ToString("yyyyMMddhhmmss") + fileName;
this.FileUpload1.PostedFile.SaveAs(filePath);
ds = GetDataSet(filePath);
InsertData(ds);
}
I hope this article is helpful to everyone's asp. net programming.