Excel data import and export implementation principle
Considering the system can fast processing of large amounts of external data, combined with the design code used in the GridView control, we can easily think of using the excel table storage of statistical data, then how to refer to excel, the class, obviously, C# have a default imported into excel sheet or export to excel table mechanism, through access to information, before the excel report in an ASP. Net application can be created and need to add a COM component in engineering. COM is the component object model, provides the interface to the outside, each interface represents an objects properties and methods, and in the application by calling these properties and methods to complete a specific logic processing. There is also that before using COM, you must install Office software. Add references to COM components of specific steps are as follows: in solving the solution explorer add reference, select the com tab, in the list to add the Microsoft Excel 8.0 object library (different versions of office COM component would be slightly different).
Excel report implementation principle: Excel report is all composed of Cell cells, so the format of the modification is relatively simple. The client only installs the Office software, can realize data import and export.
Excel data import and export operation belongs to the interaction between excel and database, in the process of data exchanging,. Net framework through the ADO. Net provides data access function, ADO. Net is the bridge between the application program and the database,. Net platform and a variety of data sources interact directly provided for the unified programming model.
Excel report data import: Excel report data import database is a complex process, need to carry out many aspects, the main carried out the following aspects of verification, process as follows:
(1) the uploaded file
(2) according to the path to the file upload, access to the file name suffix, judge whether the file is an. XLS format, if it is to get data source into, and then determine the legitimacy to the table of contents, namely progressive of each cell in the table of verification, such as length, format and verification.
In this program can read the Excel file through ASP.NET technology, and the introduction of several functions to complete the import of data functions:
Concrete code:
/ / / lt;summarygt;
Public data import
/ / / lt;/summarygt;
Name= 'sender' gt;lt;/paramgt; / / / lt;param
Name= 'e' gt;lt;/paramgt; / / / lt;param
Void Button1_Click protected (sender EventArgs, e object)
{
If (fileId.HasFile)
{
Response.Write (lt;scriptgt;alert (no or no choice to upload the file, please choose to!! lt;/scriptgt; ');
Return;
}
Ext string = System.IO.Path.GetExtension (fileId.FileName).ToLower ();
If (ext.Equals ('.Xls'))
{
Response. Write ('an style=font-size:14px; font family: Verdana; color:#ff0000; warning font-weight:boldgt;: file name suffix must is XLS! lt;/spangt; ');
Return;
}
FileName string = fileId.FileName;
SavePath string = Server.MapPath ('~/Download/');
FileOperatpr (fileName, savePath);
FileId.SaveAs (savePath fileName);
DataOperator (fileName, savePath);
/ / re binding list
BindResult ();
}
/ / / lt;summarygt;
Public data operation
/ / / lt;/summarygt;
Name= 'fileName' gt;lt;/paramgt; / / / lt;param
Name= 'savePath' gt;lt;/paramgt; / / / lt;param
Void DataOperator private (fileName string, savePath string)
{
MyString string = '';
Oconn OleDbConnection = null;
DS DataSet = null;
Try
{
MyString = 'Provider = Microsoft.Jet.OLEDB.4.0; Source Data = savePath fileName '; Properties=Excel Extended 8 ';
Oconn = OleDbConnection new (myString);
Oconn.Open ();
DS = DataSet new ();
ODA new = OleDbDataAdapter OleDbDataAdapter ('select * [Sheet1$] from', oconn);
Oda.Fill (DS);
Oconn.Close ();
DataSetOperator (DS, savePath fileName);
}
Catch (ex Exception)
{
Response. Write ('an style=font-size:14px; font family: Verdana; color:#ff0000; font-weight:boldgt; exception occurs, the data have been rollback n information. n' ex.Message 'lt;/spangt;'); and
}
}
/ / / lt;summarygt;
Public file operation
/ / / lt;/summarygt;
lt;param name= 'fileName' gt; / / / lt;/paramgt;
lt;param name= 'savePath' gt; / / / lt;/paramgt;
Void FileOperatpr private (fileName string, savePath string)
{
If (Directory.Exists (savePath))
{
Directory.CreateDirectory (savePath);
}
If (File.Exists (savePath fileName))
{
File.Delete (savePath fileName);
}
}
The above code implements the import of data in the Excel table.
Next look at the data on the GridView binding data export. Concrete code:
Void ExcelOut public (GV GridView)
{
If (gv.Rows.Count gt; 0)
{
/ / export all data, cancel the paging
Gv.AllowPaging = false;
Gv.ShowFooter = false;
BindResult ();
Response.Clear ();
Response.ClearContent ();
Response.AddHeader ('Content-Disposition', 'attachment; filename=' DateTime.Now.ToString ('_yyyyMMdd_HHmmss') '.Xls');
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = 'application/ms-excel';
SW new = StringWriter StringWriter ();
HTW new = HtmlTextWriter HtmlTextWriter (SW);
ClearControls (GV);
Gv.RenderControl (HTW);
Response.Write (sw.ToString ());
Response.Flush ();
Response.End ();
Reduction / paging display
Gv.AllowPaging
剩余内容已隐藏，支付完成后下载完整资料
Excel数据导入导出实现原理
考虑到系统能够快速处理大量的外部数据，结合设计代码中使用的gridview控件，我们很容易想到使用excel表存储统计的有效数据，那么如何引用到excel这个类，显然，C#中有一种默认的导入到excel表或者导出到excel表的机制，经过查阅资料，在ASP.NET应用程序下创建Excel报表之前，需要在工程中添加一个COM组件。COM是组件对象模型，对外提供接口，每个接口代表某个对象的属性和方法，在应用程序中通过调用这些属性和方法来完成特定逻辑处理。还有一点就是在使用COM之前，必须安装Office软件。添加COM组件引用的具体步骤如下：在解决方案资源管理器中添加引用，选中COM标签项，在列表中添加Mircrosoft Excel 8.0 Object Library（不同版本的Office COM组件会略有不同）。
Excel报表实现原理：Excel报表全部是由Cell单元格组成，因此格式的修改得比较简单。客户端只要安装Office软件，就可以实现数据的导入导出。
Excel数据导入与导出操作属于Excel与数据库之间的交互，在数据交互过程中，.NET Framwork 通过ADO.NET提供数据访问功能，ADO.NET是应用程序和数据库之间的桥梁，为.NET平台下与多种数据源直接交互提供了统一的编程模型。
Excel报表数据导入：Excel报表数据导入数据库是一个复杂的过程，需要进行多方面验证，主要进行了以下几个方面验证，过程如下：
（1）获取上传的文件
（2）根据上传文件的路径，获取文件后缀名，判断文件是否为.xls格式，如果是，就获取数据源导入，继而判断表格内容的合法性，即逐行对表格中每个单元格进行验证，如长度、格式等，验证结束。
本程序中可以通过ASP.NET技术读取Excel文件,并引入如下几个函数完成数据的导入功能：
具体实现代码：
/// lt;summarygt;
/// 数据导入
/// lt;/summarygt;
/// lt;param name='sender'gt;lt;/paramgt;
/// lt;param name='e'gt;lt;/paramgt;
protected void Button1_Click(object sender, EventArgs e)
{
if (!fileId.HasFile)
{
Response.Write('lt;scriptgt;alert(不存在或未选择要上传的文件，请重新选择！！)lt;/scriptgt;');
return;
}
string ext = System.IO.Path.GetExtension(fileId.FileName).ToLower();
if (!ext.Equals('.xls'))
{
Response.Write('lt;span style=font-size:14px; font-family:宋体; color:#ff0000; font-weight:boldgt; 警告：文件的后缀名必须是xls！lt;/spangt;');
return;
}
string fileName = fileId.FileName;
string savePath = Server.MapPath('~/Download/');
FileOperatpr(fileName, savePath);
fileId.SaveAs(savePath fileName);
DataOperator(fileName, savePath);
//重新绑定列表
BindResult();
}
/// lt;summarygt;
/// 数据操作
/// lt;/summarygt;
/// lt;param name='fileName'gt;lt;/paramgt;
/// lt;param name='savePath'gt;lt;/paramgt;
private void DataOperator(string fileName, string savePath)
{
string myString = '';
OleDbConnection oconn = null;
DataSet ds = null;
try
{
myString = 'Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = ' savePath fileName ';Extended Properties=Excel 8.0';
oconn = new OleDbConnection(myString);
oconn.Open();
ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter('select * from [Sheet1$]', oconn);
oda.Fill(ds);
oconn.Close();
DataSetOperator(ds, savePath fileName);
}
catch (Exception ex)
{
Response.Write('lt;span style=font-size:14px; font-family:宋体; color:#ff0000; font-weight:boldgt; 发生异常,数据已回滚 信息 ' ex.Message 'lt;/spangt;');
}
}
/// lt;summarygt;
/// 数据集操作
/// lt;/summarygt;
/// lt;param name='ds'gt;lt;/paramgt;
private void DataSetOperator(DataSet ds, string filePath)
{
SqlConnection conn = new SqlConnection('server=localhost;database=XSXJGL;uid=sa;pwd=123456');
conn.Open();
SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断
int k = 0;
if (ds.Tables[0].Rows.Count lt; 1)
{
Response.Write('lt;scriptgt;alert(没有数据！)lt;/scriptgt;');
return;
}
try
{
for (int i = 0; i lt; ds.Tables[0].Rows.Count; i )
{
string sqlStr = 'insert into XS(XH,PWD,XM,XB,NL,LXFS,DZ,ZY,BJ,SFZH,MZ,CSRQ,RXSJ)values(' ds.Tables[0].Rows[i][0].ToString() ',' ds.Tables[0].Rows[i][1].ToString() ',' ds.Tables[0].Rows[i][2].ToString() ',' ds.Tables[0].Rows[i][3].ToString() ',' ds.Tables[0].Rows[i][4].ToString() ',' ds.Tables[0].Rows[i][5].ToString() ',' ds.Tables[0].Rows[i][6].ToString() ',' ds.Tables[0].Rows[i][7].ToString() ',' ds.Tables[0].Rows[i][8].ToString() ',' ds.Tables[0].Rows[i][9].ToString() ',' ds.Tables[0].Rows[i][10].ToString() ',' ds.Tables[0].Rows[i][11].ToString() ',' ds.Tables[0].Rows[i][12].ToString() ')';
//sqlStr ='(' ds.Tables[0].Rows[i][0].ToString() ',';
//sqlStr = ds.Tables[0].Rows[i][1].ToString() ',';
//sqlStr = ds.Tables[0].Rows[i][2].ToString() ',';
//sqlStr ='' ds.Tables[0].Rows[i][3].ToString() ')';
SqlCommand cmd = new SqlCommand(sqlStr, conn, str);
cmd.Transaction = str;
k = cmd.ExecuteNonQuery();
}
str.Commit();
}
catch (Exception ex)
{
Response.Write('lt;span style=font-size:18px; font-family:宋体; color:#ff0000; font-weight:boldgt; 发生异常,数据已回滚/n信息/n' ex.Message 'lt;/spangt;');
str.Rollback();
}
finally
{
Response.Write('成功批量录入lt;span style=font-size:18px; font-family:宋体; color:#ff0000 ;font-weight:boldgt; ' k 'lt;/spangt; 条');
File.Delete(filePath);
}
}
/// lt;summarygt;
/// 文件操作
/// lt;/summarygt;
/// lt;param name='fileName'gt; lt;/paramgt;
/// lt;param name='savePath'gt; lt;/paramgt;
private void FileOperatpr(string fileName, string
剩余内容已隐藏，支付完成后下载完整资料
资料编号：[504725]，资料为PDF文档或Word文档，PDF文档可免费转换为Word
以上是毕业论文外文翻译，课题毕业论文、任务书、文献综述、开题报告、程序设计、图纸设计等资料可联系客服协助查找。
