asp.net 导入和导出Excel

导出:
  导入模板
        string filePath = Server.MapPath("~/TemplateFile/按天计薪模板.xls");
        string newpath = Server.MapPath("~/TemplateFile/" + Guid.NewGuid() + ".xls");
        File.Copy(filePath, newpath);
        File.SetAttributes(newpath, FileAttributes.Normal);
        Stream s = File.Open(newpath, FileMode.Open);
  //----------创建Excel
        HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(s);
        s.Close(); 
        File.Delete(newpath);
        HSSFSheet sheet = wb.GetSheetAt(0);
        string strAdd = string.Empty;
        string strnull = string.Empty;
        int rowCount = sheet.LastRowNum - 1;
        StringBuilder sbError = new StringBuilder();
        HSSFCell cell;
        string where = " AND Dealed=1  AND AuditingState=’finished’  AND RewardType=’day’ " + strwhere;
        DataTable dt = ProjecUsertDAL.GetPartTimeReward_WFLog(where);
      //创建行
        HSSFRow row2 = sheet.GetRow(3);
        if (row2 == null)
            row2 = new HSSFRow(wb, sheet, 3);
        if (row2 != null)
        {
            string pid = Request["pid"];
            if (pid != null)
            {
    //获取单元格
                HSSFCell cell2 = row2.GetCell(1);
                cell2.SetCellValue(ProjectNO);
                HSSFCell cell6 = row2.GetCell(6);
                cell6.SetCellValue(ProjectName);
                HSSFCell cell9 = row2.GetCell(9);
                cell9.SetCellValue(date);
                HSSFCell cell12 = row2.GetCell(12);
                cell12.SetCellValue(Request["city"]);
        
        #region 输出Excel下载
        wb.Write(Response.OutputStream);
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(Path.GetFileName("按天计薪模板.xls"), System.Text.Encoding.UTF8));
        Response.ContentType = "application/octet-stream";
        Response.Flush();
        Response.End();
        #endregion

导入:
          // 获取导入的路径
           string filePath = Path.Combine(Request.PhysicalApplicationPath, string.Format("Temp\\{0}", FileUpload1.FileName));
                Stream s = File.Create(filePath);
                s.Write(FileUpload1.FileBytes, 0, FileUpload1.FileBytes.Length);
             // 创建Excel
                HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(s);
                HSSFSheet sheet = wb.GetSheetAt(0);
                string strAdd =string.Empty;
                string strnull = string.Empty;
                int rowCount = sheet.LastRowNum -1;
                StringBuilder sbError = new StringBuilder();
                for (int i = 2; i < rowCount; i++)
                {
                    HSSFRow row = sheet.GetRow(i);
                     strnull = CheckValid(row);

                    if (string.IsNullOrEmpty(strnull) == false)
                    {
                        sbError.Append( string.Format("<tr><td style=’cursor:hand;’ onclick=\"document.all.r{0}.style.display =document.all.r{0}.style.display == ’’?’none’:’’;\">第{0}行导入失败!<div id=’r{0}’ style=’display:none’>原因为:{1}</div></td></tr>"
                            ,i+1 , strnull ));
                        continue;
                    }
                    else
                    {
                         strAdd = AddData(row);
                        if (string.IsNullOrEmpty(strAdd) == false)
                        {
                            sbError.Append(string.Format("<tr><td style=’cursor:hand;’ onclick=\"document.all.r{0}.style.display =document.all.r{0}.style.display == ’’?’none’:’’;\">第{0}行导入失败!<div id=’r{0}’ style=’display:none’>原因为:{1}</div></td></tr>"
                             , i + 1, strAdd));

                        }

                    }
                    strAdd = string.Empty;
                    strnull = string.Empty;
                }
               
                s.Close();
                File.Delete(filePath);
                if (sbError.Length < 1)
                {
                    labMsg.Text = "导入成功!";
                }
                else
                {
                    labMsg.Text = string.Format("<table class=’errorMsg’>{0}</table>",sbError.ToString());
                }
               控件下载地址:http://www.openso.net/downtool?id=264

posted @ 2009-09-15 12:21  搜源网  阅读(359)  评论(1编辑  收藏  举报