Asp.net 数据的导入导出，数据的导入和导出在实际的应用中非常的普遍，本文介绍了数据导入和导出的一种方法。
数据的导入和导出在实际的项目中，用的非常普遍。我在自己的项目中实现了从excel文件中导入和到处数据，现分析如下：
一、数据的导出，其基本思想是首先将要导出的内容放在Table中并拼成字符串，然后以EXCEL文件的形式导出。在我的项目中，用户可以自己选择要导出的数据字段。因为实际的字段较多，故只列出部分字段。
页面的html如下：
Code<div id="DivProjectProperty" style="text-align: center; color: White">
<fieldset>
<legend>项目导出字段</legend>
<div id="DivProjectBasic" style="margin-top: 5px">
<fieldset style="text-align: left;">
<legend>项目基本字段</legend>
<table id="tableProjectBasic" cellpadding="0" cellspacing="0" width="99%" style="margin-left: 10px">
<tr>
<td>
<input type="checkbox" id="CheckboxProjectBasicSelectAll" name="ProjectBasic" class="ProjectBasic"
onclick="selectProjectPropertyAll(this)" />选择全部
</td>
<td>
<input type="checkbox" id="CheckboxProjectName" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />名称
</td>
<td>
<input type="checkbox" id="CheckboxProjectSerialNumber" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />流水号
</td>
<td>
<input type="checkbox" id="CheckboxProjectNumber" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />编号
</td>
<td>
<input type="checkbox" id="CheckboxProjectPrincipalNumber" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />负责人工作证号
</td>
<td>
<input type="checkbox" id="CheckboxProjectPrincipalName" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />负责人姓名
</td>
</tr>
<tr>
<td>
<input type="checkbox" id="CheckboxProjectState" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />状态
</td>
<td>
<input type="checkbox" id="CheckboxProjectStartDate" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />开始日期
</td>
<td>
<input type="checkbox" id="CheckboxProjectEndDate" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />结束日期
</td>
<td>
<input type="checkbox" id="CheckboxProjectCreator" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />创立人
</td>
<td>
<input type="checkbox" id="CheckboxProjectCreateDate" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />创立日期
</td>
<td>
<input type="checkbox" id="CheckboxprojectUpdateDate" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />更新日期
</td>
</tr>
<tr>
<td>
<input type="checkbox" id="CheckboxProjectSubjectCode" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />学科代码
</td>
<td>
<input type="checkbox" id="CheckboxProjectResearchType" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />研究类型
</td>
<td>
<input type="checkbox" id="CheckboxProjectIsSerect" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />是否保密
</td>
<td>
<input type="checkbox" id="CheckboxProjectBaseName" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />基地名称
</td>
<td>
<input type="checkbox" id="CheckboxProjectCooperationType" class="ProjectBasic" checked="checked"
runat="server" onclick="changeSelectAllState(this)" />合作类型
</td>
<td>
<input type="checkbox" id="CheckboxProjectUnit" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />合作单位
</td>
</tr>
<tr>
<td>
<input type="checkbox" id="CheckboxPorjectTaskFrom" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />任务来源
</td>
<td>
<input type="checkbox" id="CheckboxProjectCorporationPlace" class="ProjectBasic"
checked="checked" runat="server" onclick="changeSelectAllState(this)" />公司地址
</td>
<td>
<input type="checkbox" id="CheckboxProjectRemark" class="ProjectBasic" runat="server"
checked="checked" onclick="changeSelectAllState(this)" />备注
</td>
</tr>
</table>
</fieldset>
</div>
</fieldset>
</div>
<div id="DivOperator" style="text-align: center; margin-top: 10px">
<asp:Button ID="ButtonExport" runat="server" Text="导出项目" Style="margin-right: 5px"
OnClick="ButtonExport_Click" /><input type="button" id="ButtonCancel" value="取 消"
style="width: 70px" onclick="history.go(-2)" />
</div>
后台代码如下：
Code
protected void ButtonExport_Click(object sender, EventArgs e) 
{
//生成内容字符串 StringBuilder projectStringBuilder = new StringBuilder(); projectStringBuilder.AppendFormat("<table id=\"tableProject\" cellpadding=\"0\" cellspacing=\"0\" class=\"listTable\" border=\"1\">\n"); projectStringBuilder.AppendFormat(" <tr>\n"); if (CheckboxProjectName.Checked) projectStringBuilder.AppendFormat(" <td>项目名称</td>\n"); if (CheckboxProjectSerialNumber.Checked) projectStringBuilder.AppendFormat(" <td>流水号</td>\n"); if (CheckboxProjectNumber.Checked) projectStringBuilder.AppendFormat(" <td>项目编号</td>\n"); if (CheckboxProjectPrincipalNumber.Checked) projectStringBuilder.AppendFormat(" <td>负责人工作号</td>\n"); if (CheckboxProjectPrincipalName.Checked) projectStringBuilder.AppendFormat(" <td>负责人</td>\n"); if (CheckboxProjectState.Checked) projectStringBuilder.AppendFormat(" <td>项目状态</td>\n"); if (CheckboxProjectStartDate.Checked) projectStringBuilder.AppendFormat(" <td>起始年月</td>\n"); if (CheckboxProjectEndDate.Checked) projectStringBuilder.AppendFormat(" <td>结束年月</td>\n"); if (CheckboxProjectCreator.Checked) projectStringBuilder.AppendFormat(" <td>建立人</td>\n"); if (CheckboxProjectCreateDate.Checked) projectStringBuilder.AppendFormat(" <td>建立日期/td>\n"); if (CheckboxprojectUpdateDate.Checked) projectStringBuilder.AppendFormat(" <td>更新日期</td>\n"); if (CheckboxProjectSubjectCode.Checked) projectStringBuilder.AppendFormat(" <td>学科代码</td>\n"); if (CheckboxProjectResearchType.Checked) projectStringBuilder.AppendFormat(" <td>研究类型</td>\n"); if (CheckboxProjectIsSerect.Checked) projectStringBuilder.AppendFormat(" <td>是否保密</td>\n"); if (CheckboxProjectBaseName.Checked) projectStringBuilder.AppendFormat(" <td>基地名称</td>\n"); if (CheckboxProjectCooperationType.Checked) projectStringBuilder.AppendFormat(" <td>合作类型</td>\n"); if (CheckboxProjectUnit.Checked) projectStringBuilder.AppendFormat(" <td>合作单位</td>\n"); if (CheckboxPorjectTaskFrom.Checked) projectStringBuilder.AppendFormat(" <td>任务来源</td>\n"); if (CheckboxProjectCorporationPlace.Checked) projectStringBuilder.AppendFormat(" <td>公司所在地</td>\n"); if (CheckboxProjectRemark.Checked) projectStringBuilder.AppendFormat(" <td>备注</td>\n"); projectStringBuilder.AppendFormat(" </tr>\n"); foreach (string projectID in projectIDArray)
{ Project project = Database.Projects.GetByID(Convert.ToInt32(projectID)); projectStringBuilder.AppendFormat(" <tr>\n"); if (CheckboxProjectName.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.Name.ToShowAsHtmlString()); if (CheckboxProjectSerialNumber.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.SerialNumber.ToShowAsHtmlString()); if (CheckboxProjectNumber.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.Number.ToShowAsHtmlString()); if (CheckboxProjectPrincipalNumber.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.PrincipalNumber.ToShowAsHtmlString()); if (CheckboxProjectPrincipalName.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.PrincipalName.ToShowAsHtmlString()); if (CheckboxProjectState.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.State.ToShowAsHtmlString()); if (CheckboxProjectStartDate.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.StartDate.ToShowAsHtmlString()); if (CheckboxProjectEndDate.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.EndDate.ToShowAsHtmlString()); if (CheckboxProjectCreator.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.Creator.ToShowAsHtmlString()); if (CheckboxProjectCreateDate.Checked) projectStringBuilder.AppendFormat(" <td>{0}/td>\n", project.CreateDate.ToShowAsHtmlString()); if (CheckboxprojectUpdateDate.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.UpdateTime.ToShowAsHtmlString()); if (CheckboxProjectSubjectCode.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.SubjectCode.ToShowAsHtmlString()); if (CheckboxProjectResearchType.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.ResearchType.ToShowAsHtmlString()); if (CheckboxProjectIsSerect.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.IsSecret.ToShowAsYesOrNo()); if (CheckboxProjectBaseName.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.BaseID.HasValue ? Database.Bases.GetByID(project.BaseID.Value).Name.ToShowAsHtmlString() : " "); if (CheckboxProjectCooperationType.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.CooperationType.ToShowAsHtmlString()); if (CheckboxProjectUnit.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.Unit.ToShowAsHtmlString()); if (CheckboxPorjectTaskFrom.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.TaskComingFrom.ToShowAsHtmlString()); if (CheckboxProjectCorporationPlace.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.CorporationPlace.ToShowAsHtmlString()); if (CheckboxProjectRemark.Checked) projectStringBuilder.AppendFormat(" <td>{0}</td>\n", project.Remark.ToShowAsHtmlString()); projectStringBuilder.AppendFormat(" </tr>\n");
} projectStringBuilder.AppendFormat("</table>"); //将生成的字符串以EXCEL文件的形式导出 Response.Buffer = true; Response.Clear(); Response.ContentType = "application/msexcel"; Response.AddHeader("Content-Disposition", "attachment;filename=project.xls"); Response.Write(projectStringBuilder.ToString()); Response.Flush(); Response.End();
}
其中核心代码就是最后的那几条语句，在导出数据的时候不会刷新页面是这种方法的有点之一。
二、数据的导入
数据导入的基本思想就是，首先将EXCEL文件上传至服务器，然后读出数据放在Dataset中，最后删除这个EXCEL文件。代码如下：
首先是上传数据代码：
Code/// <summary>
/// 从EXCEL中获取数据(放入dataset中)
/// </summary>
/// <param name="page"></param>
/// <param name="fileUploadContent"></param>
/// <param name="filename">文件名</param>
/// <returns></returns>
public static DataSet GetDataFromUploadFile(this PageBase page, FileUpload fileUploadContent, string filename)
{
//将文件上传至服务器
fileUploadContent.PostedFile.SaveAs(page.Server.MapPath("fileupload\\") + filename);
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + page.Server.MapPath("fileupload\\") + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string workSheetName = GetExcelWorkSheet(page.Server.MapPath("fileupload\\") + filename);
string sqlin = "SELECT * FROM [" + workSheetName + "$]";
OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
DataSet dsIn = new DataSet();
adapterIn.Fill(dsIn, "Finance");
return dsIn;
}
// 返回给定路径 Excel文件的workSheet名字
private static string GetExcelWorkSheet(string filePath)
{
Microsoft.Office.Interop.Excel.ApplicationClass myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
string xlName;
//'得到worksheet名字
myExcel.Workbooks.Open(filePath, Missing.Value, false, Missing.Value, null, null, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Sheets mySh = myExcel.Sheets;
Microsoft.Office.Interop.Excel.Worksheet myWs = (Microsoft.Office.Interop.Excel.Worksheet)mySh.get_Item(1);
xlName = myWs.Name;
//'关闭excel处理
myExcel.Workbooks.Close();
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mySh);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myWs);
mySh = null;
myWs = null;
myExcel = null;
GC.Collect();
//'返回名字字符串
return xlName;
}
/// <summary>
/// 删除上传的文件
/// </summary>
/// <param name="page"></param>
/// <param name="filename"></param>
public static void DeleteFile(this PageBase page, string filename)
{
FileInfo file = new FileInfo(page.Server.MapPath("fileupload/") + filename);
if (file.Exists)
file.Delete();
}
然后是页面对这个方法的调用
Codeprotected void ButtonUploadOK_Click(object sender, EventArgs e)
{
//取得上除的文件名
string filename = FileUploadContent.PostedFile.FileName.Substring(FileUploadContent.PostedFile.FileName.LastIndexOf("\\"));
DataSet dateSet = this.GetDataFromUploadFile(FileUploadContent, filename);
foreach (DataRow row in dateSet.Tables["Finance"].Rows)
{
Finance finance = new Finance();
string dateString = row[0].ToString().Substring(0, 4) + "/" + row[0].ToString().Substring(4, 2) + "/" + row[0].ToString().Substring(6, 2);
finance.ReceivedDate = Convert.ToDateTime(dateString);
finance.VoucherNumber = row[1].ToString();
finance.Description = row[2].ToString();
finance.Amount = Convert.ToInt64(Convert.ToDecimal(row[3].ToString()) * 100);
finance.Save(Database);
}
this.DeleteFile(filename);
Display();
}
这样就实现了将Excel文件中的数据导入到数据库中，代码都经过验证。
