Excel报表开发

导入到Gridview

首先我们在前台弄一个GridView控件在这就不写了

现在我们看后台代码:

public DataTable ExcelDataSource(string filepath, ref bool existsSheetname)
{
    string path=server.mappath("文件地址");

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;\";Data Source=" + path;
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

OleDbDataAdapter da = new OleDbDataAdapter ("select * from [sheet1$]",conn);

Dataset ds=Dataset();

da.Fill(ds);

gridview.DataSource=ds;

gridview.DataBind();
}

 

导入到数据库

private void AddManyData_Click(object sender, RoutedEventArgs e)

       {

           OpenFileDialog openFileDialog = new OpenFileDialog();

           openFileDialog.Filter = "Excel文件|*.xls";

 

           if ((bool)openFileDialog.ShowDialog()) 

           { 

                FileInfo fileInfo = new FileInfo(openFileDialog.FileName); 

                string filePath = fileInfo.FullName; 

                string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";

 

                using (OleDbConnection oleDbConn = new OleDbConnection(connExcel))

                {

                    oleDbConn.Open();

 

                    //获取excel表 

                    DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    //获取Excel表的表名

                    string tableName = dt.Rows[0][2].ToString().Trim();

                    //去掉空格

                    tableName = "[" + tableName.Replace("'", "") + "]";

 

                    //利用SQL语句从Excel文件里获取数据 

                     string query = @"SELECT 学号,姓名,公益劳动,电子工艺实习,操作系统 ,计算机组成,数值分析,网络设备与集成,动态网站开发实验周,动态网站开发,均分,排名 FROM " <br>　　　　　　　　　　　　　　　　　　+ tableName;

                     DataSet dataSet = new DataSet();

                  

                     using (OleDbCommand oleDbcomm = oleDbConn.CreateCommand())

                     {

                         oleDbcomm.CommandText = query;

                         OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleDbcomm);

                         oleAdapter.Fill(dataSet);

                     }

                     string connStr = "Data Source=HESHUHUA-PC;Initial Catalog=RSMSystem;Integrated Security=True";

                     //利用SqlBulkCopy批量插入数据

                    using (SqlBulkCopy sqlbc = new SqlBulkCopy(connStr))

                    {

                        sqlbc.DestinationTableName = "T_StuScore";

                        // sqlbc.ColumnMappings.Add("学号", "StuNum"),第一个参数对应数据库中的列名，

                        //第二个参数对应数据库中相应表的列名

                        sqlbc.ColumnMappings.Add("学号", "StuNum");

                        sqlbc.ColumnMappings.Add("姓名", "StuName");

                        sqlbc.ColumnMappings.Add("公益劳动", "Activity");

                        sqlbc.ColumnMappings.Add("电子工艺实习", "ElecAct");

                        sqlbc.ColumnMappings.Add("操作系统", "OprationSystem");

                        sqlbc.ColumnMappings.Add("计算机组成", "ComputerMaded");

                        sqlbc.ColumnMappings.Add("数值分析", "DataAnalyze");

                        sqlbc.ColumnMappings.Add("网络设备与集成", "NetWork");

                        sqlbc.ColumnMappings.Add("动态网站开发实验周", "WebWeek");

                        sqlbc.ColumnMappings.Add("动态网站开发", "WebMake");

                        sqlbc.ColumnMappings.Add("均分", "AvScore");

                        sqlbc.ColumnMappings.Add("排名", "StuPaiMing");

                        sqlbc.WriteToServer(dataSet.Tables[0]);

                        MessageBox.Show("数据导入成功！");

 

                    }

                } 

              

           } 

 

       }

 

数据库导出到Excel

protected void Button10_Click(object sender, EventArgs e)
{

//从数据库获得选中的数据
string ids = this.Hidden1.Value;
if (ids != "")
{
ids = ids.Substring(0, ids.LastIndexOf(','));
string[] str = ids.Split(',');
string sql = "select * from userinfo where Usid=";
for (int i = 0; i < str.Length; i++)
{
if (i == 0)
{
sql += str[i];
}
else
{
sql += " or Usid=" + str[i];
}
}
exc.Application app = new exc.Application();
// 2. 创建workbook对象
exc.Workbook book = app.Workbooks.Add(true);
// 3. 创建worksheet对象
exc.Worksheet sheet = book.Worksheets[1];
app.DisplayAlerts = true;
sheet.Name = "sheet1";
DataSet ds = sqlhelper.ExecuteDataSet(sql, null);
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
sheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
// 5. 保存到服务器中
book.SaveAs(Server.MapPath(@"~/userinfo.xls"));
// 6 退出程序
excel.SetToExcel(ds, System.AppDomain.CurrentDomain.BaseDirectory + "userinfo.xls");
app.Workbooks.Close();
app.Quit();
int generation = System.GC.GetGeneration(app);
app = null;
System.GC.Collect(generation);
}
showdata();
}