.NET数据导出到Excle表格

待我称王封你为后i 2022-10-07 08:59 191阅读 0赞

简述一下整个思路，具体见下文代码，我已经贴上比较详细的代码注释：  
**注意这里的思路并不是将表格下到客户端，而是下载到服务端，返回给前端服务端存放文件的地址，让前端去访问这个表格，就可以自行保存到本地了。**

public JsonResult  OutExcel()
            { 
                try
                {  
                	//这是获取前端传过来的查询条件
                    var Startime = Request.QueryString["Startime"];
                    var Endtime = Request.QueryString["Endtime"];
    				//设置好文件名：GUID唯一标识防止重复
                    string name = string.Format("{0}{1}", Guid.NewGuid(), "导出"); ;
                    
                    DateTime STime = DateTime.Now;
                    DateTime ETime = DateTime.Now;
                    //获取当前相对路径如果没有该文件夹，自动创建，用来保存下载好的表格
                    string ipath = Server.MapPath("DownLoad") + "\\";
                    string ipath1 = Server.MapPath("DownLoad");
                    if (!Directory.Exists(ipath1))
                    { 
                        Directory.CreateDirectory(ipath1);
                    }
                    MemberBLL bll = new MemberBLL(); 
    
                    #region 判断条件
                
    
                    if (!string.IsNullOrEmpty(Startime.ToString()))
                    { 
    
                        STime = Convert.ToDateTime(Startime);
                    }
                    if (!string.IsNullOrEmpty(Endtime.ToString()))
                    { 
    
                        ETime = Convert.ToDateTime(Endtime);
                    }
                    #endregion
    
                    DataSet ds = null;
                    try
                    { 
                   		//执行查询获取到要导出的数据
                        ds = bll.GetList(STime,ETime);
                    }
                    catch (Exception ex)
                    { 
                        LogHelper.Info("查询语句" + ex.Message);
                    }
                     
                    decimal record = 0;
                    if (ds==null)
                    { 
    
                        return Json(new {  b = 1, p = "没有需要导出的数据" });
                    }
                    else
                    { 
                         //接下来我做了一下数据二次处理
                       
                        ds.Tables[0].Columns.Remove("DIVISIONNAME");//可移除DIVISIONNAME这一列
                        foreach (DataColumn dc in ds.Tables[0].Columns)
                        {  
    						//这里我将列名修改为我将要展示的，避免展示数据库字段
                            if (dc.ColumnName == "CHINESE_NAME")
                            { 
                                dc.ColumnName = "中文名";//可以将列名改变
                            }
                            if (dc.ColumnName== "ENGLISH_NAME")
                            { 
                                dc.ColumnName = "英文名";
                            } 
                            if(dc.ColumnName == "COMMENTS")
                            { 
                                dc.ColumnName = "备注";
                            } 
                            if (dc.ColumnName == "REGISTERED_DATE")
                            { 
                                dc.ColumnName = "注册时间";
                            }
                            if (dc.ColumnName == "CREATE_BY")
                            { 
                                dc.ColumnName = "创建人";
                            }
                        }
    
                    }
                    DataTable dt = ds.Tables[0];
    
                    //dt.Columns.Remove("RECORD_ID");
                    DataToExcel ec = new DataToExcel();
                    string path = string.Empty;
    
                    try
                    { 
                    	//最后我会贴出将DT换为表格的方法
                        path = ec.DataExcelPath(dt, name, ipath); 
                    }
                    catch (Exception ex)
                    { 
                        LogHelper.Info(ex.Message + "生成excle");
                    }
                    string fileName = name;//客户端保存的文件名
    
                    string userid = LoginUser.UserID;
    
                     
    				//你的服务器地址
                    string iPSover =  IP ;
                    string filePath = "http://" + iPSover + "/Member" + "/DownLoad/" + path;//路径
                    if (string.IsNullOrEmpty(path))
                    { 
                        return Json("");
    
    
                    }
                    var result = "true";
                    if (!string.IsNullOrEmpty(result))
                    { 
                        if (result == "表中无数据")
                        { 
                            return Json(new {  b = 1, p = "表中无数据" });
                        }
                        else
                        { 
                            return Json(new {  b = 0, p = filePath });
                        }
                    }
                    return Json(new {  b = 1, p = "服务器异常，请稍后再试" });
                }
                catch (Exception ex)
                { 
                    LogHelper.Info(ex.Message);
                    return Json(new {  b = 1, p = "服务器异常，请稍后再试" });
                }
            }

这是将DataTable的数据导出显示为报表(不使用Excel对象)的方法：

public string DataExcelPath(System.Data.DataTable dt, string strTitle, string FilePath)
            { 
                COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
                ClearFile(FilePath);
                string filename = strTitle + ".xls";
                excel.CreateFile(FilePath + filename);
                excel.PrintGridLines = false;
                COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
                COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
                COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
                COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
                double height = 1.5;
                excel.SetMargin(ref mt1, ref height);
                excel.SetMargin(ref mt2, ref height);
                excel.SetMargin(ref mt3, ref height);
                excel.SetMargin(ref mt4, ref height);
                COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsBold;
                COM.Excel.cExcelFile.FontFormatting contentFont = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
                string font = "宋体";
                short fontsize = 9;
                excel.SetFont(ref font, ref fontsize, ref ff);
                byte b1 = 1,
                    b2 = 12;
                short s3 = 12;
                excel.SetColumnWidth(ref b1, ref b2, ref s3);
                string header = "页眉";
                string footer = "页脚";
                excel.SetHeader(ref header);
                excel.SetFooter(ref footer);
    
                COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
                COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
                COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
                COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
                // 报表标题
                int cellformat = 1; 
                int rowIndex = 1;//起始行
                int colIndex = 0;
    
                //取得列标题 
                foreach (DataColumn colhead in dt.Columns)
                { 
                    colIndex++;
                    object name = colhead.ColumnName.Trim();
                    
                    excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref name, ref cellformat);
                }
                //取得表格中的数据 
                excel.SetFont(ref font, ref fontsize, ref contentFont);     //正文字体调成普通样式
                foreach (DataRow row in dt.Rows)
                { 
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    { 
                        colIndex++;
                        if (col.DataType == System.Type.GetType("System.DateTime"))
                        { 
                            object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); ;
                            excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        }
                        else
                        { 
                            object str = (object)row[col.ColumnName].ToString();
                            excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                        }
                    }
                }
                int ret = excel.CloseFile();
                // if(ret!=0)
                // { 
                // //MessageBox.Show(this,"Error!");
                // }
                // else
                // { 
                // //MessageBox.Show(this,"请打开文件c:\\test.xls!");
                // }
                return filename;
            }