public void LoadReport() { string DocumentID = "ID".RequestStr(); string SheetIndex = "SI".RequestStr(); string SheetXML = ""; string sheetData = ""; string SheetName = ""; string SheetID = ""; string ReportSheetID = ""; SheetIndex = SheetIndex.IsNullOrEmpty() ? "0" : SheetIndex; try { JZDocument sheetDataAreaCells; List <JZCell> Cells = new List <JZCell>(); #region 查询数据 DataTable _TempTable = new DataTable(); string _TempStr = @" Select testroomcode, m.reportsheetid,d.Data from [dbo].[sys_document] d left outer join [dbo].[sys_module] m on d.ModuleId = m.ID where d.ModuleID = 'E77624E9-5654-4185-9A29-8229AAFDD68B' ANd TestRoomCode = '{0}'"; _TempStr = string.Format(_TempStr, DocumentID); BLL_Document Bll = new BLL_Document(); _TempTable = Bll.GetDataTable(_TempStr); if (_TempTable.Rows.Count > 0) { sheetData = _TempTable.Rows[SheetIndex.Toint()]["Data"].ToString(); sheetDataAreaCells = Newtonsoft.Json.JsonConvert.DeserializeObject <JZDocument>(sheetData); SheetCount = _TempTable.Rows.Count; ReportSheetID = _TempTable.Rows[SheetIndex.Toint()]["reportsheetid"].ToString(); foreach (JZSheet Jzs in sheetDataAreaCells.Sheets) { if (Jzs.ID.ToString() == ReportSheetID) { SheetName = Jzs.Name; SheetID = Jzs.ID.ToString(); Cells = Jzs.Cells; break; } } _TempStr = "select SheetXML ,SheetData from sys_sheet where ID = '{0}'"; SheetXML = Bll.ExcuteScalar(string.Format(_TempStr, ReportSheetID)).ToString(); } else { throw new Exception("占无报告数据"); } _TempTable.Clear(); _TempTable.Dispose(); #endregion #region 创建WIN组件 FarPoint.Win.Spread.FpSpread WinSp = new FarPoint.Win.Spread.FpSpread(); WinSp.Sheets.Clear(); SheetXML = JZCommonHelper.GZipDecompressString(SheetXML); int a = SheetXML.Length; FarPoint.Win.Spread.SheetView SheetView = Serializer.LoadObjectXml(typeof(FarPoint.Win.Spread.SheetView), SheetXML, "SheetView") as FarPoint.Win.Spread.SheetView; SheetView.SheetName = "Document"; if (sheetDataAreaCells != null) { foreach (JZCell cell in Cells) { // if (SheetView.Cells[cell.Name].CellType.ToString() == "图片") { SheetView.Cells[cell.Name].ResetCellType(); SheetView.Cells[cell.Name].Value = "<img src='data:image/gif;base64," + cell.Value.ToString().Replace("\r\n", "") + "' />"; } else if (SheetView.Cells[cell.Name].CellType.ToString() == "文本") { SheetView.Cells[cell.Name].Value = cell.Value; } else if (SheetView.Cells[cell.Name].CellType.ToString() == "长文本") { SheetView.Cells[cell.Name].ResetCellType(); SheetView.Cells[cell.Name].Value = cell.Value; } else if (SheetView.Cells[cell.Name].CellType.ToString() == "超链接") { SheetView.Cells[cell.Name].ResetCellType(); if (cell.Value != null) { try { string[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <string[]>(cell.Value.ToString()); SheetView.Cells[cell.Name].Value = Temp[0] + "[" + Temp[2] + "]<img src='data:image/gif;base64," + Temp[1].Replace("\r\n", "") + "' />"; } catch { } } } } } WinSp.Sheets.Add(SheetView); WinSp.LoadFormulas(true); #endregion #region 临时文件 string SitePath = Server.MapPath("~/Temp"); DirectoryInfo Dir = new System.IO.DirectoryInfo(SitePath); if (!Dir.Exists) { Dir.Create(); } #endregion #region Save HTML string FileName = SitePath + "/" + SheetID + DateTime.Now.ToString("yyMMddhhmmss") + ".html"; WinSp.ActiveSheet.SaveHtml(FileName); FileInfo FHtml = new System.IO.FileInfo(FileName); FileStream Fs = FHtml.OpenRead(); byte[] Buuff = new byte[Fs.Length]; Fs.Read(Buuff, 0, (int)Fs.Length); Fs.Close(); Fs.Dispose(); string HTML = ASCIIEncoding.UTF8.GetString(Buuff); File.Delete(FileName); #endregion #region show Element StringBuilder Nav = new StringBuilder(); StringBuilder Cont = new StringBuilder(); Nav.Append("<ul class=\"nav nav-tabs\">"); Cont.Append("<div class=\"tab-content\">"); if (SheetCount.Toint() > 1) { for (int i = 0; i < SheetCount; i++) { Nav.Append("<li class=\"" + (i == SheetIndex.Toint() ? "active" : "") + "\" ><a data=\"testroominfo.aspx?ID=" + DocumentID + "&SI=" + i + "&R=" + new Random().Next(0, 100000) + "\" href=\"#t" + i + "\" data-toggle=\"tab\" >第" + (i + 1).ToString() + "</a></li>"); Cont.Append("<div class=\"tab-pane CPane " + (i == SheetIndex.Toint() ? "active" : "") + " \" id=\"t" + i.ToString() + "\">" + ((i == SheetIndex.Toint()) ? HTML : "") + "</div>"); } Nav.Append("</ul>"); Cont.Append("</div>"); form1.InnerHtml = Nav.ToString() + Cont.ToString(); } else { form1.InnerHtml = HTML; } #endregion } catch (Exception ex) { Response.Write(ex.Message.ToString()); } }
public void LoadReport(string DSource, string DName, string UID, string PWD, string DocumentID) { try { string SheetXML = ""; string sheetData = ""; string SheetName = ""; string SheetID = ""; string ReportIndex = "0"; JZDocument sheetDataAreaCells; List <JZCell> Cells = new List <JZCell>(); #region 查询数据 DataTable _TempTable = new DataTable(); using (SqlConnection _Conn = new SqlConnection("Data Source=" + DSource + ";Initial Catalog=" + DName + ";User ID=" + UID + ";Pwd=" + PWD)) { _Conn.Open(); string _TempStr = "Select m.reportsheetid,d.Data from [dbo].[sys_document] d left outer join [dbo].[sys_module] m on d.ModuleId = m.ID where d.id = '{0}'"; using (SqlCommand _Cmd = new SqlCommand(string.Format(_TempStr, DocumentID), _Conn)) { using (SqlDataAdapter _Adp = new SqlDataAdapter(_Cmd)) { _Adp.Fill(_TempTable); if (_TempTable.Rows.Count > 0) { sheetData = _TempTable.Rows[0]["Data"].ToString(); //ReportIndex = Convert.ToInt32(_TempTable.Rows[0]["reportsheetid"].ToString()); ReportIndex = _TempTable.Rows[0]["reportsheetid"].ToString(); _TempTable.Clear(); _TempTable = new System.Data.DataTable(); sheetDataAreaCells = Newtonsoft.Json.JsonConvert.DeserializeObject <JZDocument>(sheetData); _TempStr = "select Name ,SheetXML ,SheetData from sys_sheet where ID = '{0}'"; _Cmd.CommandText = string.Format(_TempStr, ReportIndex); foreach (JZSheet Jzs in sheetDataAreaCells.Sheets) { if (Jzs.ID.ToString() == ReportIndex) { SheetName = Jzs.Name; SheetID = Jzs.ID.ToString(); Cells = Jzs.Cells; } } _Adp.SelectCommand = _Cmd; _Adp.Fill(_TempTable); SheetXML = _TempTable.Rows[0]["SheetXML"].ToString(); } else { throw new Exception("占无报告数据"); } } } _Conn.Close(); } _TempTable.Clear(); _TempTable.Dispose(); #endregion #region 创建WIN组件 FarPoint.Win.Spread.FpSpread WinSp = new FarPoint.Win.Spread.FpSpread(); WinSp.Sheets.Clear(); SheetXML = JZCommonHelper.GZipDecompressString(SheetXML); int a = SheetXML.Length; FarPoint.Win.Spread.SheetView SheetView = Serializer.LoadObjectXml(typeof(FarPoint.Win.Spread.SheetView), SheetXML, "SheetView") as FarPoint.Win.Spread.SheetView; SheetView.SheetName = SheetName; if (sheetDataAreaCells != null) { //for (int i = 0; i < SheetView.RowCount; i++) //{ // for (int j = 0; j < SheetView.ColumnCount; j++) // { // if (SheetView.Cells[i, j].CellType!=null && SheetView.Cells[i, j].CellType.ToString() == "下拉框") // { // SheetView.Cells[i, j].CellType = null; // } // } //} //SheetView.Columns.Remove(16, 4); foreach (JZCell cell in Cells) { dataCells.Add(SheetView.Cells[cell.Name]); SheetView.Cells[cell.Name].Value = cell.Value; } } WinSp.Sheets.Add(SheetView); //dtCellStyle = ModuleHelperClient.GetCellStyleBySheetID(sheetID); //for (int i = 0; i < dtCellStyle.Rows.Count; i++) //{ // if (dtCellStyle.Rows[i]["CellStyle"] != null) // { // JZCellStyle CurrentCellStyle = Newtonsoft.Json.JsonConvert.DeserializeObject<JZCellStyle>(dtCellStyle.Rows[i]["CellStyle"].ToString()); // if (CurrentCellStyle != null) // { // string strCellName = dtCellStyle.Rows[i]["CellName"].ToString(); // FarPoint.Win.Spread.Cell cell = SheetView.Cells[strCellName]; // cell.ForeColor = CurrentCellStyle.ForColor; // cell.BackColor = CurrentCellStyle.BackColor; // cell.Font = new Font(CurrentCellStyle.FamilyName, CurrentCellStyle.FontSize, CurrentCellStyle.FontStyle); // } // } //} WinSp.LoadFormulas(true); UpdateChart(WinSp); UpdateEquation(WinSp); #endregion #region 临时文件 string SitePath = Server.MapPath("~/Temp"); DirectoryInfo Dir = new System.IO.DirectoryInfo(SitePath); if (!Dir.Exists) { Dir.Create(); } #endregion #region Save HTML string FileName = SitePath + "/" + SheetID + DateTime.Now.ToString("yyMMddhhmmss") + ".html"; WinSp.ActiveSheet.SaveHtml(FileName); FileInfo FHtml = new System.IO.FileInfo(FileName); FileStream Fs = FHtml.OpenRead(); byte[] Buuff = new byte[Fs.Length]; Fs.Read(Buuff, 0, (int)Fs.Length); Fs.Close(); Fs.Dispose(); string HTML = ASCIIEncoding.UTF8.GetString(Buuff); html.InnerHtml = HTML; //WinSp.SaveExcel(SitePath + "/" + SheetID + ".xls"); File.Delete(FileName); #endregion #region Save Excel //WinSp.SaveExcel(SitePath + "/222.xls",FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat); //fp1.OpenExcel(SitePath + "/" + SheetID + ".xls"); //fp1.CommandBar.Visible = false; //fp1.Sheets[0].PageSize = fp1.Sheets[0].Rows.Count; //File.Delete(SitePath + "/" + SheetID + ".xlsx"); //fp1.Visible = true; #endregion } catch (Exception ex) { Response.Write(ex.Message.ToString()); //fp1.Visible = false; } finally { } //Response.Write(stopwatch.ElapsedMilliseconds.ToString()); }