ASP.NET Excel导入导出SQL Server整理

系统管理员 2021-12-15 01:47 228阅读 0赞

一、Excel导入

在页面层添加FileUpload控件，控件位置：Standard里面，Calendar下面两个。设置其ID。  
<asp:FileUpload ID="inputFile" runat="server" />

添加Button，btnUpload

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

protected  void  btnUpload\_Click( object  sender, EventArgs e)  
\{  
DataSet ds  =  GetExcelData();  
InsertDB(ds);  
\}  
  
  
 ///  <summary>   
 ///  该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表,此用Sheet1；  
 ///  </summary>   
 ///  <param name="ds"> ds </param>   
  private  void  InsertDB(DataSet ds)  
\{  
SqlConnection \_con  =  new  SqlConnection( @" Data Source=STKWX028\\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True " );  
SqlCommand cmd  =  new  SqlCommand();  
cmd.Connection  =  \_con;  
StringBuilder sb  =  new  StringBuilder();  
 if  (ds.Tables\[ 0 \].Rows.Count  >  0 )  
\{  
 for  ( int  i  =  0 ; i  <  ds.Tables\[ 0 \].Rows.Count; i \++ )  
\{  
sb.Append( "  INSERT INTO bookInfo(bookID,bookName,author,publisher,price,readerID,readerType,bookStatus) VALUES(' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 0 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 1 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 2 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 3 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 4 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 5 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 6 \].ToString()  \+  " ',' " );  
sb.Append(ds.Tables\[ 0 \].Rows\[i\].ItemArray\[ 7 \].ToString()  \+  " ' )  " );  
cmd.CommandText  =  sb.ToString();  
\}  
\}  
\_con.Open();  
 int  j  =  cmd.ExecuteNonQuery();  
\_con.Close();  
 if  (j  >  0 )  
\{  
lblMessage.Text  =  " Insert into DB table Sucessfully! " ;  
\}  
\}  
 ///  <summary>   
 ///  get data source from excel file  
 ///  </summary>   
 ///  <returns> dataset ds </returns>   
  private  DataSet GetExcelData()  
\{  
DataSet ds  =  new  DataSet();  
 string  filePath  =  inputFile.PostedFile.FileName;  
 string  connStr03  =  " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "  \+  filePath  \+  " ;Extended Properties=Excel 8.0; " ; ;  
 string  connStr07  =  " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "  \+  filePath  \+  " ;Extended Properties='Excel 12.0;HDR=YES' " ;  
 string  queryStr  =  " SELECT \* FROM \[Sheet1$\] " ;  
OleDbConnection conn03  =  new  OleDbConnection(connStr03);  
OleDbConnection conn07  =  new  OleDbConnection(connStr07);  
 if  (inputFile.HasFile)  
\{  
 string  fileExt  =  System.IO.Path.GetExtension(inputFile.FileName);  
 if  (fileExt  ==  " .xls " )  
\{  
OleDbDataAdapter myAdapter  =  new  OleDbDataAdapter(queryStr, conn03);  
myAdapter.Fill(ds);  
\}  
 else  if  (fileExt  ==  " .xlsx " )  
\{  
OleDbDataAdapter myAdapter  =  new  OleDbDataAdapter(queryStr, conn03);  
myAdapter.Fill(ds);  
\}  
 else   
\{  
lblMessage.Text  =  " The file is not exist! " ;  
\}  
  
\}  
 return  ds;  
\}

二、SQL Server导出Excel

这边注意，要添加引用Microsoft.Office.Interop.Excel。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

///  <summary>   
 ///  put data source into dataset  
 ///  </summary>   
 ///  <returns> DataSet </returns>   
  private  static  DataSet PutInDataSet()  
\{  
 string  connStr  =  @" Data Source=STKWX028\\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True " ;  
 string  queryStr  =  @" SELECT \* FROM bookInfo " ;  
SqlConnection \_con  =  new  SqlConnection(connStr);  
SqlDataAdapter adapter  =  new  SqlDataAdapter(queryStr, connStr);  
DataSet ds  =  new  DataSet();  
adapter.Fill(ds);  
 return  ds;  
\}  
 ///  <summary>   
 ///  save the data as excel file  
 ///  </summary>   
 ///  <param name="ds"></param>   
  private  void  SaveAsExcel(DataSet ds)  
\{  
Microsoft.Office.Interop.Excel.Application excelApp  =  new  Microsoft.Office.Interop.Excel.Application();  
 if(excelApp !=null)

\{  
Microsoft.Office.Interop.Excel.Workbook workbook  =  excelApp.Workbooks.Add(Missing.Value);  
Microsoft.Office.Interop.Excel.Worksheet worksheet  =  (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets\[ 1 \]; // get the sheet index   
  for  ( int  row  =  0 ; row  <  ds.Tables\[ 0 \].Rows.Count; row \++ )  
\{  
 for  ( int  col  =  0 ; col  <  ds.Tables\[ 0 \].Columns.Count; col \++ )  
\{  
worksheet.Cells\[row  \+  2 , col  \+  1 \]  =  ds.Tables\[ 0 \].Rows\[row\]\[col\];  
\}  
\}  
\}

else  
\{  
lblMessage.Text ="Fail to export because there's no excel installation!";  
\}

excelApp.Quit();

\}  
  
 protected  void  btnExport\_Click( object  sender, EventArgs e)  
\{  
DataSet ds  =  PutInDataSet();  
SaveAsExcel(ds);  
lblMessage.Text  =  " Saved " ;  
\}

简化后的导出：（未提取方法）

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][] 代码

SqlConnection \_con  =  new  SqlConnection( @" Data Source=STKWX028\\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True " );  
 string  queryStr  =  " SELECT \* FROM bookInfo " ;  
DataSet ds  =  new  DataSet();  
SqlDataAdapter ad  =  new  SqlDataAdapter(queryStr, \_con);  
ad.Fill(ds);  
Microsoft.Office.Interop.Excel.Application excelApp  =  new  Application();  
 if  (excelApp  ==  null )  
\{  
lblMessage.Text  =  " Fail to export because there's no excel installation! " ;  
\}  
Workbook workbook  =  excelApp.Workbooks.Add(Missing.Value);  
Worksheet worksheet  =  (Worksheet)workbook.Worksheets\[ 1 \]; // get the sheet index   
  for  ( int  row  =  0 ; row  <  ds.Tables\[ 0 \].Rows.Count;row \++  ) // write data to excel   
  \{  
 for  ( int  col  =  0 ; col  <  ds.Tables\[ 0 \].Columns.Count;col \++  )  
\{  
worksheet.Cells\[row  \+  2 , col  \+  1 \]  =  ds.Tables\[ 0 \].Rows\[row\]\[col\];  
\}  
\}  
excelApp.Quit();

转载于:https://www.cnblogs.com/eva\_2010/articles/1884365.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211214/8f795b7fb095454eab540893bff2fb10.png