//在服务器中找到报表模板,从数据库中选择数据,将数据写入模板中,把该文件保存在服务器的C://temp目录下;客户端再下载该文件,就能在客户端进行浏览 public string CreateExcel(string filename, string brand, string startDate, string endDate, string team, string style, string month, DateTime date, bool merge) { MSYS.Common.ExcelExport openXMLExcel = null; try { MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator(); string bookid = opt.GetSegValue("select F_ID from ht_sys_excel_book where F_NAME = '" + filename + "'", "F_ID"); string booktype = opt.GetSegValue("select F_TYPE from ht_sys_excel_book where F_NAME = '" + filename + "'", "F_TYPE"); string basedir = System.AppDomain.CurrentDomain.BaseDirectory.ToString(); string strFolderPath = basedir + @"\TEMP"; if (!System.IO.Directory.Exists(strFolderPath)) { // 目录不存在,建立目录 System.IO.Directory.CreateDirectory(strFolderPath); } DirectoryInfo dyInfo = new DirectoryInfo(strFolderPath); //获取文件夹下所有的文件 foreach (FileInfo feInfo in dyInfo.GetFiles()) { //判断文件日期是否小于今天,是则删除 if (feInfo.CreationTime < DateTime.Now.AddMinutes(-2)) { feInfo.Delete(); } } foreach (DirectoryInfo dir in dyInfo.GetDirectories()) { if (dir.CreationTime < DateTime.Now.AddMinutes(-2)) { dir.Delete(true); } } //导出文件模板所在位置 String sourcePath = basedir + @"templates\" + booktype + @"\" + filename + ".xls"; String filepath = basedir + @"TEMP\" + filename + date.ToString("HHmmss") + style; //bool isrewrite = true; // true=覆盖已存在的同名文件,false则反之 //System.IO.File.Copy(sourcePath, filepath, isrewrite); string query = "select * from ht_sys_excel_seg where F_BOOK_ID = '" + bookid + "' order by F_DES"; DataSet data = opt.CreateDataSetOra(query); //申明一个ExcelSaveAs对象,该对象完成将数据写入Excel的操作 openXMLExcel = new MSYS.Common.ExcelExport(sourcePath, false); if (data.Tables[0].Select().GetLength(0) > 0) { DataRow[] rows = data.Tables[0].Select(); foreach (DataRow row in rows) { string sqlstr = row["F_SQL"].ToString(); //设定选择的数据的日期及牌号等信息 if (brand != "") { sqlstr = sqlstr.Replace("$brand$", brand); } if (startDate != "") { sqlstr = sqlstr.Replace("$startDate$", startDate); } if (endDate != "") { sqlstr = sqlstr.Replace("$endDate$", endDate); } if (team != "") { sqlstr = sqlstr.Replace("$team$", team); } if (month != "") { sqlstr = sqlstr.Replace("$month$", month); } if (sqlstr != "") { //将选择的数据写入Excel if (sqlstr.Length > 4 && sqlstr.Substring(0, 3) == "STR") { sqlstr = sqlstr.Substring(4); Response.Write(openXMLExcel.SetCurrentSheet(Convert.ToInt32(row["F_SHEETINDEX"].ToString()))); Response.Write(openXMLExcel.WriteData(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, sqlstr)); } if (sqlstr.Length > 5 && sqlstr.Substring(0, 4) == "Proc") { int pos = sqlstr.IndexOf('@'); int pos2 = sqlstr.IndexOf('#'); string proc = sqlstr.Substring(5, pos - 5); int paracount = Regex.Matches(sqlstr, @"@").Count; List <string> seglist = new List <string>(); List <string> paralist = new List <string>(); for (int i = 0; i < paracount; i++) { seglist.Add(sqlstr.Substring(pos + 1, pos2 - pos - 1)); pos = sqlstr.IndexOf('@', pos2); if (pos > 0) { paralist.Add(sqlstr.Substring(pos2 + 1, pos - pos2 - 1)); pos2 = sqlstr.IndexOf('#', pos); } else { paralist.Add(sqlstr.Substring(pos2 + 1)); } } opt.ExecProcedures(proc, seglist.ToArray(), paralist.ToArray()); } if (sqlstr.Length > 20 && sqlstr.Substring(0, 3) == "SQL") { sqlstr = sqlstr.Substring(4).Trim(); System.Diagnostics.Debug.WriteLine(sqlstr); bool hasCaption = false; if (sqlstr.Substring(0, 1) == "$") { hasCaption = true; sqlstr = sqlstr.Substring(1); } DataSet set = new DataSet(); if (sqlstr.Substring(0, 5) == "SHIFT") { sqlstr = sqlstr.Substring(6).Trim(); set = opt.ShiftTable(sqlstr); } else { set = opt.CreateDataSetOra(sqlstr); } if (set != null) { DataTable dt = set.Tables[0]; openXMLExcel.SetCurrentSheet(Convert.ToInt32(row["F_SHEETINDEX"].ToString())); if (merge) { if (hasCaption) { openXMLExcel.WriteDataRerangeWithCaption(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt); } else { openXMLExcel.WriteDataRerange(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt); } } else { if (hasCaption) { openXMLExcel.WriteDataIntoWorksheetWithCaption(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt); } else { openXMLExcel.WriteDataIntoWorksheet(Convert.ToInt32(row["F_DESX"].ToString()), getColumn(row["F_DESY"].ToString()) + 1, dt); } } } } } } } ///客户端再下载该文件,在客户端进行浏览 FileInfo fi = new FileInfo(filepath); if (fi.Exists) //判断文件是否已经存在,如果存在就删除! { fi.Delete(); } if (style == ".xlsx" || style == ".xls") { openXMLExcel.SaveAs(filepath); } else { openXMLExcel.SaveAsHtm(filepath); System.Diagnostics.Debug.WriteLine("路径"); System.Diagnostics.Debug.WriteLine(filepath); } openXMLExcel.Dispose(); openXMLExcel = null; KillProcess("EXCEL.EXE"); return("Success"); } catch (Exception e) { if (openXMLExcel != null) { openXMLExcel.Dispose(); } return(e.Message); } finally { } }