直接运行这个页面，如果在本页设置reportid = "28"，直接运行本页面可以提示保存，如果不赋值直接运行本页面也能正常打印出设置的错误提示，但如果是从其他页面提交的数据过来，不论有没有在该页面设置初始值都是一样的情况，新打开的页面闪一下就自动关闭了，没有任何提示！我的源文件如下：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Net;
using System.IO;public partial class ExportExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
string reportid = Request.Form["reportid"];
reportid = "28";
if (reportid == "")
{
Response.Write("页面错误");
}
else
{
//Response.Write(reportid);
Maticsoft.OAchargeReportTitle getTle = new Maticsoft.OAchargeReportTitle();
DataSet getTINFO = getTle.GetList("id=" + reportid);
string reportTile = getTINFO.Tables[0].Rows[0]["report"].ToString();
DateTime st = Convert.ToDateTime(getTINFO.Tables[0].Rows[0]["stime"]);
DateTime et = Convert.ToDateTime(getTINFO.Tables[0].Rows[0]["etime"]);
Maticsoft.OAchargeReport getValueInfo = new Maticsoft.OAchargeReport();
DataSet getValueInfoDS = getValueInfo.GetList("reportid=" + reportid + " and sendtime between '" + st + "' and '" + et + "'"); GridView1.DataSource = getValueInfoDS;
GridView1.DataBind(); Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8"; //("GB2312");为简体中文
Response.AppendHeader("Content-Disposition", "attachment;filename=" + reportTile + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); //GB231
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
//Response.End();
}
}
catch
{
Response.Write("页面错误");
}
}
}
public override void VerifyRenderingInServerForm(Control control){}}
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Net;
using System.IO;public partial class ExportExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
string reportid = Request.Form["reportid"];
reportid = "28";
if (reportid == "")
{
Response.Write("页面错误");
}
else
{
//Response.Write(reportid);
Maticsoft.OAchargeReportTitle getTle = new Maticsoft.OAchargeReportTitle();
DataSet getTINFO = getTle.GetList("id=" + reportid);
string reportTile = getTINFO.Tables[0].Rows[0]["report"].ToString();
DateTime st = Convert.ToDateTime(getTINFO.Tables[0].Rows[0]["stime"]);
DateTime et = Convert.ToDateTime(getTINFO.Tables[0].Rows[0]["etime"]);
Maticsoft.OAchargeReport getValueInfo = new Maticsoft.OAchargeReport();
DataSet getValueInfoDS = getValueInfo.GetList("reportid=" + reportid + " and sendtime between '" + st + "' and '" + et + "'"); GridView1.DataSource = getValueInfoDS;
GridView1.DataBind(); Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8"; //("GB2312");为简体中文
Response.AppendHeader("Content-Disposition", "attachment;filename=" + reportTile + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); //GB231
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
//Response.End();
}
}
catch
{
Response.Write("页面错误");
}
}
}
public override void VerifyRenderingInServerForm(Control control){}}
或使用模板
string fileName = "";
string filePath = Server.MapPath("");//路径
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
protected void Btn_ExportClick(object sender, EventArgs e)
{
string style = @"<style> .text { mso-number-format:\@; } </script> ";
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(style);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{}
.net 对Excel的操作(对Oracle导入、导出、样式修改)(2009-09-22 10:57:05)转载标签： it 分类： 原创
一.导入：
因为是导入到Oracle，对Excel的兼容性不是很好，需要先存入DataSet作为中转
Excel到DataSet private System.Data.DataSet ExcelToDataSet(string path)
{
string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
OleDbConnection conn1 = new OleDbConnection(OledbConnectionString);
ArrayList SheetNameList = new ArrayList();
try
{
if (conn1.State == ConnectionState.Closed)
{
conn1.Open();
}
System.Data.DataTable dtExcelSchema = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string SheetName = "";
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
SheetNameList.Add(SheetName);
} }
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "edit", "alert('" + ex.Message + "');", true);
return null;
}
finally
{
conn1.Close();
}
DataSet dsExcel = new DataSet(); try
{
string strSql = ""; for (int i = 0; i < SheetNameList.Count; i++)
{
strSql = "select * from [" + (string)SheetNameList[i] + "]";
OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn1);
System.Data.DataTable dtExcel = new System.Data.DataTable((string)SheetNameList[i]);
oleExcelDataAdapter.Fill(dtExcel);
dsExcel.Tables.Add(dtExcel);
}
return dsExcel;
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "edit", "alert('" + ex.Message + "');", true);
return null;
}
} DataSet到Oracle public void DataSetToDataBase(System.Data.DataSet ds)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmd = conn.CreateCommand();
string sql = "";
int count = 0;
try
{
if (ds.Tables.Count != 0)
{
for (int i = 0; i < ds.Tables.Count; i++)
{
if (ds.Tables[i].Rows.Count != 0)
{
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
sql = @"insert into HR_PEOPLE_INFO(HR_PEOPLE_ID,HR_NAME,HR_CFID,HR_GENDER,HR_DOB,HR_HIRE_DATE,HR_AREA)
values('" + ds.Tables[i].Rows[j][0].ToString() + "','" + ds.Tables[i].Rows[j][1].ToString() +
"','" + ds.Tables[i].Rows[j][2].ToString() + "','" + ds.Tables[i].Rows[j][3].ToString() +
"',to_date('" + ds.Tables[i].Rows[j][4].ToString().Substring(0, ds.Tables[i].Rows[j][4].ToString().Length - 8) + "','yyyy-MM-dd'),to_date('" + ds.Tables[i].Rows[j][5].ToString().Substring(0, ds.Tables[i].Rows[j][5].ToString().Length - 8) +
"','yyyy-MM-dd'),'" + ds.Tables[i].Rows[j][6].ToString() + "')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
count++;
}
}
}
}
Page.ClientScript.RegisterStartupScript(this.GetType(), null, "window.returnValue=1;window.close();", true);
}
catch (Exception ex)
{
if (ex.Message.Contains("唯一约束条件"))
Page.ClientScript.RegisterStartupScript(this.GetType(), "edit", "alert('导入的数据中在系统中存在相同的数据，不允许导入！');", true);
else
Page.ClientScript.RegisterStartupScript(this.GetType(), "edit", "alert('数据操作失败！');", true);
}
} 二.导出和样式控制 1.第一种方式：逐行写入Excel。优点：对样式等控制很灵活，可以后台直接操作；缺点：导出大量数据时效率很低，大概2000条数据需要30秒左右。 /// <summary>
/// 导出Excel
/// </summary>
/// <param name="ds">需要导出的数据集</param>
/// <param name="table_name">需要导出的表名</param>
/// <returns>执行结果和异常</returns>
public static string Export(DataSet ds,string table_name,string save_path)
{
if (ds != null)
{
int countR = ds.Tables[0].Rows.Count;//读取数据的行数
int countC = ds.Tables[0].Columns.Count;//读取数据的列数
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();//实例化Excel
Excel.Workbook wb = excelApp.Application.Workbooks.Add(true);//创建WorkBook
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets["Sheet1"];//创建WorkSheet
ws.Name = table_name+"表信息";//重命名Sheet
try
{
ws.Cells[1, 1] = "CFMA-NJ " + table_name + "表信息";//写表名
ws.Cells[2, 1] = "No.";
for (int i = 0; i < countC; i++)
{
ws.Cells[2, i + 2] = ds.Tables[0].Columns[i].ColumnName;//写表头
}
for (int i = 0; i < countR; i++)
{
ws.Cells[i + 3, 1] = i + 1;
for (int j = 0; j < countC; j++)
{
ws.Cells[i + 3, j + 2] = ds.Tables[0].Rows[i].ItemArray[j];//写入数据
}
}//利用Rang接口来控制Excel的样式
Range rg = ws.get_Range(ws.Cells[2, 1], ws.Cells[countR + 2, countC + 1]);//在rg中修改所有单元格的样式
rg.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
rg.Interior.ColorIndex = 36;//设置背景色
rg.Borders.LineStyle = 1;//设置边框样式
//rg.Borders.Weight = 1;//设置边框宽度
rg = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, countC + 1]);//在rg中修改表名样式
rg.MergeCells = true;//合并单元格
rg.Font.Size = 20;//设置字体大小
rg.Font.Name = "黑体";//设置字体
rg.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;//居左
rg.Interior.ColorIndex = 24;//设置背景色
rg.RowHeight = 50;//设置行高
rg = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, countC + 1]);//在rg中修改标题样式
rg.Font.Bold = true;//字体加粗
rg.Font.Size = 13;//设置字体大小
rg.Font.Name = "黑体";
rg.Interior.ColorIndex = 15;//背景色
rg.RowHeight = 35;//设置行高
rg.ColumnWidth = 17;//设置列宽
rg = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, 1]);//在rg中修改序列样式
rg.ColumnWidth = 10;//设置列宽
wb.SaveAs(save_path, Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, System.Text.Encoding.UTF7, Type.Missing, Type.Missing);
wb.Saved = true;
excelApp.UserControl = false;
return "导出成功！";
}
catch (Exception exce)
{
return exce.Message.ToString();//捕捉并返回异常
}
finally
{
excelApp.Quit();//关闭Excel
excelApp = null;
GC.Collect();//回收资源
}
}
else
return "数据不存在！";
} 2.第二种方式：利用GridView导出。优点：因为是直接存为Excel兼容的Html格式文件，所以效率非常高，大概导出10000条只需要1~2秒；缺点：样式无法直接控制，只能直接存为GridView的样式，要修改样式只能修改Gridview的样式，具有较大的限制，例如合并单元格貌似没办法？ /// <summary>
/// 从GridView导出
/// </summary>
/// <param name="ds">导出的数据集</param>
/// <param name="path">导出的路径</param>
/// <returns></returns>
public static string Export(DataSet ds,string path)
{
GridView GV = new GridView();//实例化一个Gridview
try
{
GV.DataSource = ds;
GV.AllowPaging = false;//禁止分页
GV.DataBind();//绑定数据
GV.HeaderStyle.Height = 60;//设置表头的行高
GV.HeaderStyle.BackColor = System.Drawing.Color.Gray;//设置表头的背景色
GV.HeaderStyle.Font.Bold = true;//设置表头加粗
GV.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;//设置表头居中
GV.BackColor = System.Drawing.Color.FromArgb(255, 255, 153);//设置背景色
GV.HorizontalAlign = HorizontalAlign.Center;//设置居中
GV.RowStyle.HorizontalAlign = HorizontalAlign.Center;//设置居中
StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); GV.RenderControl(htw);//将Gridview存入HtmlTextWriter
string s = sw.ToString();
File.WriteAllText(path, s);//将文件写入服务器
return "导出成功！";
}
catch(Exception exc)
{
return exc.Message;//捕捉异常信息
}
}
补充步骤：
1、IE工具选项--> Internet选项-->安全--->可信站点-->自定义级别-->下载--->文件下载的自动提示--->启用。
2、IE工具选项--> Internet选项-->安全--->可信站点-->站点--->添加当前网站为可信站点