//*************************************************************************************** //说明:1.设计 Excel报表的连接要求新建的ODBC数据源,数据源的名称为QswmExcelReport(不能用其它名称数据源) // 2.客户端应用程序如果没有注册QswmExcelReport数据源,则系统会自动注册 // 3.在设计报表时一定要使用 QswmExcelReport数据源。 //*************************************************************************************** /// <summary> /// EXCEL报表 连接 参数 注册表信息 /// </summary> public void ChangRegedit(ref string error) { try { string keyname = "QswmExcelExport"; bool exist = IsRegeditExit(keyname); if (exist) { return; } try { string strPathCC = "\"" + AppDomain.CurrentDomain.BaseDirectory + @"XLReportCom.dll" + "\""; System.Diagnostics.Process.Start("regsvr32", strPathCC + " /s"); RegistryKey hkml = Registry.CurrentUser; RegistryKey software = hkml.OpenSubKey("SOFTWARE", true); RegistryKey aimdir = software.OpenSubKey("ODBC", true); RegistryKey kk1 = aimdir.OpenSubKey("ODBC.INI", true); RegistryKey das = kk1.OpenSubKey("ODBC Data Sources", true); das.SetValue(keyname, "SQL Server"); GetConnectString con = new GetConnectString(); GetDataSouce conn = con.GetDataSouce(); List <string[]> strs = new List <string[]>(); strs.Add(new string[] { "Database", conn.Database }); strs.Add(new string[] { "Description", "导出EXECL" }); strs.Add(new string[] { "Driver", "C:\\WINDOWS\\system32\\SQLSRV32.dll" }); strs.Add(new string[] { "LastUser", conn.UseID }); strs.Add(new string[] { "Server", conn.Datasource }); WTRegedit(strs, keyname); } catch { error = "创建EXCEL报表连接ODBC数据源时发生错误!"; } } catch { } }
/// <summary> /// 生成EXCEL模板文件和执行文件 /// </summary> /// <param name="obj"></param> /// <param name="strFilePath"></param> /// <param name="strKeyWord"></param> public void ScEXCEL(SheetPrint obj, string strFilePath, string strKeyWord, ref string error) { lock (this) { try { if (obj == null) { return; } if (string.IsNullOrEmpty(obj.Parameter)) { error = "设置参数不全,无法生成模板!"; return; } if (string.IsNullOrEmpty(obj.SQLText)) { error = "设置SQL不全,无法生成模板!"; return; } if (string.IsNullOrEmpty(obj.Path)) { error = "未指定EXCEL模板!"; return; } string templeFilePath = obj.Path; if (!templeFilePath.StartsWith(@"\")) { templeFilePath = @"\" + templeFilePath; } templeFilePath = AppDomain.CurrentDomain.BaseDirectory + "ExcelTemple\\" + templeFilePath; if (!File.Exists(templeFilePath)) { error = "指定的EXCEL模板不存在!"; return; } //生成EXCEL模板文件 System.IO.FileStream fs = new System.IO.FileStream(templeFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read, FileShare.ReadWrite); byte[] bytes = new byte[(int)fs.Length]; fs.Read(bytes, 0, bytes.Length); fs.Close(); //if (!File.Exists(strFilePath)) Directory.CreateDirectory(strFilePath); string strModuleExcel = strFilePath + strKeyWord + ".xls"; File.Delete(strModuleExcel); FileStream fs_stream = new FileStream(strModuleExcel, FileMode.CreateNew); BinaryWriter writefile = new BinaryWriter(fs_stream); writefile.Write(bytes); writefile.Close(); //2.生成XLEXCEL文件 string strModuleXlt = strFilePath + strKeyWord + ".xrf"; FileStream fsxls = new FileStream(strModuleXlt, FileMode.OpenOrCreate, FileAccess.Write); StreamWriter sw = new StreamWriter(fsxls, System.Text.Encoding.GetEncoding("GB2312"));//通过指定字符编码方式可以实现对汉字的支持,否则在用记事本打开查看会出现乱码 sw.Flush(); sw.BaseStream.Seek(0, SeekOrigin.Begin); GetConnectString con = new GetConnectString(); GetDataSouce conn = con.GetDataSouce(); string strText = ""; strText = "ExcelReport Version 2.0" + "\r\n" + "[Data Source]" + "\r\n" + "Name1=QswmExcelExport" + "\r\n" + "User1=" + "sa" + "\r\n" + "Password1=" + conn.UsePassword + "\r\n" + "" + "\r\n" + "[File] " + "\r\n" + "ReportTemplateFileName=" + strKeyWord + ".xls" + "\r\n" + "ReportFileName=Report\\" + strKeyWord + ".xls" + "\r\n" + "LogFileName=" + strFilePath + strKeyWord + ".log" + "\r\n" ; strText = strText + obj.Parameter + "\r\n" + "[SQL]" + "\r\n" + obj.SQLText; sw.WriteLine(strText); sw.Flush(); sw.Close(); } catch (Exception ex) { error = "生成Excel失败!" + ex.Message; //string strModuleExcel = strFilePath + "Errorxls.txt"; //FileStream fs_stream = new FileStream(strModuleExcel, FileMode.CreateNew); //BinaryWriter writefile = new BinaryWriter(fs_stream); //writefile.Write(HttpUtility.UrlEncode(error, System.Text.Encoding.UTF8)); //writefile.Close(); throw ex; } } }