ASP.NET EXCEL数据的导出和导入

    数据的导入和导出在实际的项目中，用的非常普遍。我在自己的项目中实现了从excel文件中导入和到处数据，现分析如下：

     一、数据的导出，其基本思想是首先将要导出的内容放在Table中并拼成字符串，然后以EXCEL文件的形式导出。在我的项目中，用户可以自己选择要导出的数据字段。因为实际的字段较多，故只列出部分字段。

     页面的html如下：

 <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>

后台代码如下：

 

 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() : "&nbsp");
                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文件。代码如下：

    首先是上传数据代码：

 

/// <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();
        }

 

然后是页面对这个方法的调用

protected 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文件中的数据导入到数据库中，代码都经过验证。