save excel sheet to a particular folder that i have created in my local machine and make this excel sheet as read only
in my application im exporting gridview data to excel sheet now i want save this sheet to a folder that i have created in my machine how can i do that
i have written code like this
using System;
using System.Configuration;
using System.Data;
using System.Linq;
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.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
public override void VerifyRenderingInServerForm(Control control)
{
}
private void ExportToExcel(string strFileName, GridView dg)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWrite开发者_如何学Cr = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
oHtmlTextWriter.WriteLine("<b><u><font size='5'><font color='blue'><center> REPORT </center></font></u></b>");
GridView1.RenderControl(oHtmlTextWriter);
Response.End();
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("");
con.Open();
int var = DropDownList1.SelectedIndex;
switch (var)
{
case 0: break;
case 1: SqlCommand cmdd = new SqlCommand("update t1 set diff1=isnull(t1.date-t2.date,0)from reporttemp t1 left join reporttemp t2 on t1.rn=t2.rn+1", con);
SqlCommand cmdd1 = new SqlCommand("update t1 set diff2=isnull(t1.date-t2.date,0)from reportpre t1 left join reportpre t2 on t1.rn=t2.rn+1", con);
SqlCommand cmdd2 = new SqlCommand("update t1 set diff3=isnull(t1.date-t2.date,0)from reportph t1 left join reportph t2 on t1.rn=t2.rn+1", con);
cmdd.ExecuteNonQuery();
cmdd1.ExecuteNonQuery();
cmdd2.ExecuteNonQuery();
GridView1.Visible = true;
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand("select Date,Temperature from reporttemp where datepart(minute,diff1)=5", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommand cmd2 = new SqlCommand("select Date,Pressure from reportpre where datepart(minute,diff2)=5", con);
SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
SqlCommand cmd3 = new SqlCommand("select Date,Ph from reportph where datepart(minute,diff3)=5", con);
SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
da.Fill(dt);
da2.Fill(dt);
da3.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
break;
case 2: GridView1.Visible = true;
SqlCommand cmd4 = new SqlCommand("update t1 set diff1=isnull(t1.date-t2.date,0)from reporttemp t1 left join reporttemp t2 on t1.rn=t2.rn+1", con);
SqlCommand cmd1 = new SqlCommand("update t1 set diff2=isnull(t1.date-t2.date,0)from reportpre t1 left join reportpre t2 on t1.rn=t2.rn+1", con);
SqlCommand cmd5 = new SqlCommand("update t1 set diff3=isnull(t1.date-t2.date,0)from reportph t1 left join reportph t2 on t1.rn=t2.rn+1", con);
cmd4.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd5.ExecuteNonQuery();
GridView1.Visible = true;
DataTable dt1 = new DataTable();
SqlCommand cmd6 = new SqlCommand("select Date,Temperature from reporttemp where datepart(minute,diff1)=2 ", con);
SqlDataAdapter daa = new SqlDataAdapter(cmd6);
SqlCommand cmd7 = new SqlCommand("select Date,Pressure from reportpre where datepart(minute,diff2)=2", con);
SqlDataAdapter daa2 = new SqlDataAdapter(cmd7);
SqlCommand cmd8 = new SqlCommand("select Date,Ph from reportph where datepart(minute,diff3)=2", con);
SqlDataAdapter daa3 = new SqlDataAdapter(cmd8);
daa.Fill(dt1);
daa2.Fill(dt1);
daa3.Fill(dt1);
GridView1.DataSource = dt1;
GridView1.DataBind();
break;
}
}
protected void Button3_Click1(object sender, EventArgs e)
{
ExportToExcel("Report.xls", GridView1);
}
}
can any1 help me on this
From the question, I had never exepected this to be a web application ?!
private void ExportToExcel(string strFileName, GridView dg)
{
//..
string text = oStringWriter.ToString();
Response.Write(text);
// OR use Response.WriteBinary() to write a byte[] directly
Of course, this being a web application, the browser is in control. What you have here is a client donwloading a file; it is up to the browser (and the user) to decide where it is stored and whether it will be made writable, or not.
If you want any more control, you need to look at (proprietary) client-side frameworks like Java Applets, Silverlight, Flex.
However, you might reconsider the purpose (why do you want to have this control? are you doing things the wrong way?)
First of all use StreamWriter to write the stream
FileStream fileStream = new FileStream(@"Location+Filename.xls", FileMode.Create);
and further if you want to save it in My Documents Folder You have to use
Environment.SpecialFolder.MyDocuments
For more Info regarding this click
Hope will help..... :-)
Please be clear of where you wish to write. ASP.NET applications are server applications expected to run on a browser of the users' machine. So, the server application CAN NOT directly write to the client's machine. The browser will prompt for a save file dialog. If you wish the sophistication of creating a readonly file, you can create the file in the server side and make it read only and then make it available for download.
SaveFileDialog oDialog = new SaveFileDialog();
oDialog.Filter = "Excel files | *.xls";
if (oDialog.ShowDialog() == DialogResult.OK)
{
string sFileName = oDialog.FileName;
}
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
if (sFileName != null)
{
workbook.SaveAs(sFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
misValue,misValue, misValue, misValue, misValue);
workbook.Close(misValue, misValue, misValue);
app.Quit();
}
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText.ToUpper();
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
精彩评论