excel的导入导出
1.进行引用Microsoft.Office.Interop.Excel.dll和office.dll（但是在服务器上不行）
public void GetDataToExcel()
{
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//设置Excel标题
excel.Caption = "用户列表";
//设置Excel列名
excel.Cells[1, 1] = "ID";
excel.Cells[1, 2] = "真实姓名";
excel.Cells[1, 3] = "角色ID";
excel.Cells[1, 4] = "添加时间";
excel.Cells[1, 5] = "电话";
excel.Cells[1, 6] = "添加人";
excel.Cells[1, 7] = "用户名";
excel.Cells[1, 8] = "角色";
//设置Excel字体加粗
excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Font.Bold = true;
//设置Excel字体颜色
excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Font.ColorIndex = 0;
//设置Excel边框样式
excel.Range[excel.Cells[1, 1], excel.Cells[1, 8]].Borders.LineStyle = XlLineStyle.xlContinuous;
//循环将DataGridView中的数据赋值到Excel中
System.Data.DataTable dt1 = new BLL.Admin().GetList("");
int i;
for (i = 0; i <dt1.Rows.Count; i++)
{
excel.Cells[i + 2, 1] = dt1.Rows[i]["id"].ToString();
excel.Cells[i + 2, 2] = dt1.Rows[i]["adminname"].ToString();
excel.Cells[i + 2, 3] = dt1.Rows[i]["status"].ToString();
excel.Cells[i + 2, 4] = dt1.Rows[i]["date"].ToString();
excel.Cells[i + 2, 5] = dt1.Rows[i]["phone"].ToString();
excel.Cells[i + 2, 6] = dt1.Rows[i]["addname"].ToString();
excel.Cells[i + 2, 7] = dt1.Rows[i]["loginname"].ToString();
excel.Cells[i + 2, 8] = dt1.Rows[i]["statusname"].ToString();
}
//设置Excel水平对齐方式
excel.Range[excel.Cells[1, 1], excel.Cells[i + 2, 4]].HorizontalAlignment = XlHAlign.xlHAlignLeft;
//显示当前窗口
excel.Visible = true;
}
当出现乱码时，加上
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
2.这个在服务器上是可以的
protected void output(System.Data.DataTable dt)
{
StringBuilder sb = new StringBuilder();
sb.Append("<table cellpadding='0' cellspacing='0' border='1'>");
sb.Append(@"<tr class='tb_header'>
<td style='width:100px; background-color:#67B3DC; text-align:center'>编号</td>
<td style='width:100px; background-color:#67B3DC; text-align:center'>内容标题</td>
<td style='width:100px; background-color:#67B3DC; text-align:center'>内容文档</td>
<td style='width:100px; background-color:#67B3DC; text-align:center'>内容链接</td>
<td style='width:100px; background-color:#67B3DC; text-align:center'>添加时间</td>
</tr>");
int id2 = int.Parse(Session["id"].ToString());
string filesname = new BLL.MauClass().GetidByModel(id2).Classname;
for (int x = 0; x < dt.Rows.Count; x++)
{
sb.Append("<tr>");
for (int y = 0; y < 1; y++)
{
sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["id"]));
sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["name"]));
sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["files"]));
sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["url"]));
sb.Append(string.Format("<td style='text-align:center'>{0}</td>", dt.Rows[x]["date"]));
}
sb.Append("</tr>");
}
sb.Append("</table>");
Response.Buffer = true;
Response.Clear();
Response.ContentType = "application/msexcel";
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", filesname));
Response.Write(sb.ToString());
Response.Flush();
//Response.End();
// HttpContext.C()urrent.ApplicationInstance.CompleteRequest();
Response.Close();
}
3.导入excel数据库
public DataSet ExcelDs(string FilenamePath, string Table)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + FilenamePath + ";Extended Properties = 'Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection Odbconn = new OleDbConnection(strConn);
OleDbDataAdapter Odda = new OleDbDataAdapter("select * from [Sheet1$]", Odbconn);
DataSet Ds = new DataSet();
Odda.Fill(Ds, Table);
return Ds;
}
protected void subbtn_Click(object sender, EventArgs e)
{
//Excel 实例
string IsXls = System.IO.Path.GetExtension(FileUpload2.FileName).ToString().ToLower();
if (FileUpload2.HasFile == false)
{
base.Response.Write("<script> alert( '请您先选择后缀名为.xls或.xlsx的Excel文件 ') </script> ");
}
else if (IsXls != ".xls" && IsXls != ".xlsx")
{
base.Response.Write("<script> alert( '请选择后缀名为.xls或.xlsx的Excel文件 ') </script> ");
}
else
{
string NewFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random().Next(99, 9999) + IsXls;//新文件名
string NewPath = Server.MapPath("../../Excel/") + NewFileName;//服务器保存路径
FileUpload2.SaveAs(NewPath);
DataSet Ds = ExcelDs(NewPath, NewFileName);
DataRow[] Dr = Ds.Tables[0].Select();
int RowsNum = Ds.Tables[0].Rows.Count;
if (RowsNum.Equals(0))
{
base.Response.Write("<script> alert( '该excel为空表，请重新上传！ ') </script> ");
}
else
{
for (int i = 0; i < Dr.Length-1; i++)//遍历Excel中的考核范围并添加到数据库
{
Model.Material m = new Model.Material();
m.Name = Dr[i]["产品名称"].ToString();
m.Supplier = Dr[i]["供应方"].ToString();
m.Norms= Dr[i]["规格"].ToString();
m.Units = Dr[i]["单位"].ToString();
if (Dr[i]["采购时间"].ToString() != "")
{
m.Date = Convert.ToDateTime(Dr[i]["采购时间"].ToString());
}
else
{
m.Date = DateTime.Parse(DateTime.Now.ToString("yy-MM-dd"));
}
m.Pname = Dr[i]["项目名称"].ToString();
m.Remarks = Dr[i]["备注"].ToString();
if (Dr[i]["投标价"].ToString() != "")
{
//m.Bprice = Convert.ToDouble(Dr[i]["投标价"]);
m.Bprice = Double.Parse(Dr[i]["投标价"].ToString());
}
else
{
m.Bprice = 0;
}
if (Dr[i]["信息价"].ToString() != "")
{
m.Iprice = Convert.ToDouble(Dr[i]["信息价"]);
}
else
{
m.Iprice = 0;
}
if (Dr[i]["采购指导价"].ToString() != "")
{
m.Puprice = Convert.ToDouble(Dr[i]["采购指导价"]);
}
else
{
m.Puprice = 0;
}
m.Classid = int.Parse(ddl.SelectedValue.Split(',')[0]);
m.Classname = ddl.SelectedItem.Text;
m.Parentid = new BLL.MaClass().GetidByModel(int.Parse(ddl.SelectedValue.Split(',')[0])).Parentid;
m.Parentname = new BLL.MaClass().GetidByModel(int.Parse(ddl.SelectedValue.Split(',')[0])).Parentname;
m.Username = "管理员";
//添加方法
if (new BLL.Materials().Add(m) <= 0)
{
base.Response.Write("<script> alert( '添加失败！ ') </script> ");
return;
}
}
}
}
}
