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

浏览数：28 / 时间：2015年06月12日

一、Excel导入

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

添加Button，btnUpload

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。

/// <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";
}

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

技术分享

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();