如何在Excel中更新值 [英] how to update values in excel
本文介绍了如何在Excel中更新值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的模块是在前端插入值，并希望将其保存在Excel工作表中.
在将值传递到网格中之后，我将给出save并将其保存在我的excel中.
如果我重新打开应用程序并输入值并保存，则新值不会在同一Excel工作表中更新.
我希望在同一张Excel工作表中更新这些值.
这是我的前端外观:
Hi,
My module is to insert values in the front end and want to save that in an excel sheet.. I passed the textbox values to grid and passed the grid to excel..
After rntering the values are passed in grid, i ll give save and it ll be saved in my excel..
If i reopen the application and enter the values and give save, then the new values are not updating in the same excel sheet.. Its again opening in a different sheet..
I want the values to be updated in the same excel sheet..
This is how my frontend look :
Name --------------
Age --------------
[submit] [save]
在提交提交后，将显示带有值的网格，如果我单击保存"，则将填充excel工作表.
下面是我的代码. :
after giving submit, the grid appears with the value and if i click save, the excel sheet populates.
Below is my code. :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
//Microsoft.Office.Interop.Excel.Application oXL;
//Microsoft.Office.Interop.Excel._Workbook oWB;
//Microsoft.Office.Interop.Excel._Worksheet oSheet;
private DataTable _dt;
public DataTable dt
{
get
{
return _dt;
}
set
{
_dt = value;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("CodeID", typeof(string));
dt.Columns.Add("Content", typeof(string));
dt.Columns.Add("MappingCode", typeof(string));
Session["dt"] = dt;
}
_dt = (DataTable)Session["dt"];
}
private void BindGrid()
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
//HttpContext.Current.Response.ContentType = "application/ms-word";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
// HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.doc");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
int columnscount = GridView1.Columns.Count;
for (int j = 0; j < columnscount; j++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write("");
HttpContext.Current.Response.Write(GridView1.Columns[j].HeaderText.ToString());
HttpContext.Current.Response.Write("");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
protected void Btn_Export_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
GridView1.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)Session["dt"];
DataRow drToGrid = dt.NewRow();
drToGrid["CodeID"] = txtCodeID.Text;
drToGrid["Content"] = txtContent.Text;
drToGrid["MappingCode"] = txtMappingCode.Text;
dt.Rows.Add(drToGrid);
GridView1.DataSource = dt;
GridView1.DataBind();
BindGrid();
txtCodeID.Text = txtContent.Text = txtMappingCode.Text = string.Empty;
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
}
有人可以帮我还是给我任何想法..
万分感谢. :)
Anusha
Can someone please help me or give me any idea..
Thanks a ton. :)
Anusha
推荐答案
hi ....
您可以使用与sql server或任何其他数据库相同的方式来处理excel文件中的数据.只会有一些小的变化.首先，连接字符串将更改.例如您的excel文件是D驱动器中的"test.xlsx"，然后:
Provider = Microsoft.ACE.OLEDB.12.0;数据源= D:\ test.xlsx;扩展属性=''Excel 12.0 Xml; HDR = YES'';
(希望它能起作用！！如果不起作用，那么还有更多.)
然后，您需要使用 OledbConnection 和类似的类进行连接.
表名将是工作表名称，末尾带有"
hi....
u can make manipulate data in excel file in the same way u do with sql server or any other database. There will be only some minor changes. First of all, connection string will change. for e.g. ur excel file is "test.xlsx" in D drive, then:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xlsx;Extended Properties=''Excel 12.0 Xml;HDR=YES'';
(Hope it works!! If it does not, then there are many more.)
Then u need to use OledbConnection and similar classes for connectivity.
The table name will be the Sheet name with ''
"符号.
例如要从"Sheet1"中选择所有行，查询将为:
'' sign at end.
For e.g. to select all rows from "Sheet1", query will be:
select * from [Sheet1
以类似的方式更新:
In similar way, to update:
update [Sheet1
这篇关于如何在Excel中更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
