private static List <detallePublicado> GenerarXlsHojaHtmZip(string hoja, string archivoDestino, string rango)
{
List <detallePublicado> resultado = new List <detallePublicado>();
//copiarValoresHoja(hoja);
Excel.Range xlSourceRange = ((Excel.Worksheet)xlWorkBook.Application.ActiveWorkbook.Sheets[hoja]).get_Range("E1", "F1");
Excel.Hyperlink enlace = (Excel.Hyperlink)xlSourceRange.Hyperlinks.Add(xlSourceRange, Path.GetFileNameWithoutExtension(archivoDestino) + ".zip", Type.Missing, Type.Missing, "Descargar Archivo");
((Excel.Workbook)xlWorkBook.Application.ActiveWorkbook).PublishObjects.Add(Excel.XlSourceType.xlSourceRange,
Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".htm"), hoja, rango,
Excel.XlHtmlType.xlHtmlStatic, "1-10225", Type.Missing).Publish(true);
if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".htm")))
{
resultado.Add(new detallePublicado(Path.GetFileNameWithoutExtension(archivoDestino) + ".htm", "Local", "OK"));
}
if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), archivoDestino)))
{
UtilZip.CreaArchivoZip(Path.Combine(Path.GetDirectoryName(archivoDestino), archivoDestino), Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".zip"));
if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".zip")))
{
resultado.Add(new detallePublicado(Path.GetFileNameWithoutExtension(archivoDestino) + ".zip", "Local", "OK"));
//resultado.Add(new detallePublicado(Path.GetFileName(archivoDestino),"Local", "OK"));
}
}
return(resultado);
}
private ICollection <FileInfo> GetHyperLinks(Excel.Hyperlink link)
{
List <FileInfo> attachments = new List <FileInfo>();
if (link.Address != null)
{
FileInfo file = UriToFile(link.Address);
if (file != null)
{
attachments.Add(file);
}
}
return(attachments);
}
private void ExcelWorkbook_SheetFollowHyperlink(object Sh, Excel.Hyperlink Target)
{
try
{
if ("Drop Down Values".Equals(Target.Range.Text))
{
Excel._Worksheet ws = (Excel._Worksheet)Sh;
int rowNumber = Target.Range.Row;
int fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("FieldName", ws);
Excel.Range r = ws.Cells[rowNumber, fnColIndex] as Excel.Range;
Excel.Workbook workbook = (Excel.Workbook)ws.Parent;
Excel.Sheets oSheets = workbook.Worksheets;
Excel._Worksheet oSheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, oSheets);
int targetSheetRow = oSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
targetSheetRow++;
fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("Field Id", oSheet);
oSheet.Cells[targetSheetRow, fnColIndex] = r.Text;
}
}
catch (Exception ex)
{ throw (ex); }
}
public void FillHyperLinks(string _bbid, System.Data.DataTable _mxData, System.Data.DataTable _cellData, DataSet _hmcData)
{
int lfw = _hmcData.Tables["COL_DEFINE"].Rows.Count;
int hfw = _hmcData.Tables["ROW_DEFINE"].Rows.Count;
string _url = Report_WebAppAddress + "/Dialog/ShowReportDetial.aspx";
//处理行列表
foreach (DataRow _dr in _mxData.Rows)
{
int hx = int.Parse(_dr["HX"].ToString());
int lx = int.Parse(_dr["LX"].ToString());
if (hx > 0 && hx <= hfw && lx > 0 && lx <= lfw)
{
string _url2 = string.Format(@"{0}?HLX={1},{2}&REPORTID={3}", _url, hx, lx, _bbid);
Microsoft.Office.Interop.Excel.Hyperlink _link = (Microsoft.Office.Interop.Excel.Hyperlink)m_oSheet.Hyperlinks.Add(m_oSheet.Cells[hx + BaseRow - 1, lx + BaseCol], _url2, m_oMissing, m_oMissing, m_oMissing);
//System.Runtime.InteropServices.Marshal.ReleaseComObject (_link);
}
}
//处理特别CELL表
foreach (DataRow _dr in _cellData.Rows)
{
int _rownum = int.Parse(_dr["RPHX"].ToString());
int _colnum = int.Parse(_dr["RPLX"].ToString());
int _linkrownum = int.Parse(_dr["BBHX"].ToString());
int _linkcolnum = int.Parse(_dr["BBLX"].ToString());
string sfmx = _dr.IsNull("HASMX")? "0" : _dr["HASMX"].ToString();
if (sfmx == "1")
{
string _url2 = string.Format(@"{0}?HLX={1},{2}&REPORTID={3}", _url, _linkrownum, _linkcolnum, _bbid);
m_oSheet.Hyperlinks.Add(m_oSheet.Cells[_rownum, _colnum], _url2, m_oMissing, m_oMissing, m_oMissing);
}
}
}
internal void refreshChart(object sender, Excel.Hyperlink e)
{
string mdl = ((Excel.Worksheet) this.Application.ActiveSheet).get_Range(e.Name).Value2.ToString();
Excel.ChartObjects col = (Excel.ChartObjects)((Excel.Worksheet) this.Application.ActiveSheet).ChartObjects();
int tmp1 = col.Count;
for (int i = 1; i <= col.Count; i++)
{
Excel.ChartObject co = (Excel.ChartObject)col.Item(i);
if (co.Name.Substring(0, 5) == "Model")
{
if (co.Name == "Model " + mdl)
{
co.Visible = true;
}
else
{
co.Visible = false;
}
}
}
}
public void WriteResultsTable(int n, bool top, bool isRead)
{
// put the model results data into an object array
//object[,] amodel ;
Excel.Range header = BottomCell().get_Offset(2, 0);
string start = header.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing);
ArrayList lst = this.Source.Models.ModelSort();
int first = top ? 0 : Math.Min(n, lst.Count);
int last = top ? Math.Min(n, lst.Count) : lst.Count;
if (first == last)
{
return;
}
// write the column headers
//{ ModelNo, ModelValid, IVNames, IVCoefficients, IVses, IVpVals, R2, adjR2, pVal, RMSError, Residual, AIC, Formula };
foreach (Utilities.Constants.ModelOutputColumns col in System.Enum.GetValues(typeof(Utilities.Constants.ModelOutputColumns)))
{
string label = Globals.ThisAddIn.rsc.GetString("label" + col.ToString()) ?? col.ToString();
header.Value2 = label;
header = header.get_Offset(0, 1);
}
object[,] row;
int rowct = 1;
for (int j = first; j < last; j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
int varct = mdl.VariableNames.Length;
rowct += varct + 1;
Excel.Range target = BottomCell().get_Offset(1, 0).get_Resize(varct + 1, 13);
row = new object[1, 13];
row[0, 0] = mdl.ModelNumber;
row[0, 1] = mdl.Valid();
row[0, 6] = mdl.R2();
row[0, 7] = mdl.AdjustedR2();
row[0, 8] = mdl.ModelPValue();
row[0, 9] = mdl.RMSError;
row[0, 10] = mdl.ResidualSS();
row[0, 11] = mdl.AICFormula();
row[0, 12] = mdl.Formula();
target.get_Resize(1, 13).Value2 = row;
if (top)
{
Excel.Hyperlink hl = (Excel.Hyperlink)WS.Hyperlinks.Add(target.get_Resize(1, 1), "",
target.get_Resize(1, 1).get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing), System.Type.Missing, System.Type.Missing);
hl.ScreenTip = "Graph model " + mdl.ModelNumber.ToString();
}
row = new object[varct + 1, 4];
for (int i = 0; i < varct; i++)
{
row[i, 0] = mdl.VariableNames[i];
row[i, 1] = mdl.Coefficients[i];
row[i, 2] = mdl.StandardErrors()[i];
row[i, 3] = mdl.PValues()[i];
}
//row[varct, 0] = "(Intercept)";
row[varct, 1] = mdl.Coefficients[varct];
target.get_Offset(0, 2).get_Resize(varct + 1, 4).Value2 = row;
// put a box around
target.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, System.Type.Missing);
// highlight the best model
if (Source.BestModel() != null)
{
if (mdl.ModelNumber == Source.BestModel().ModelNumber)
{
if (mdl.Valid())
{
target.Font.Color = 0x0000AA;
//target.Style = Globals.ThisAddIn.rsc.GetString("bestModelStyle");
target.Font.Bold = true;
}
else
{
target.Font.Color = 0x0000AA;
//target.Style = "Bad";
target.Font.Bold = true;
}
}
}
}
Excel.Range tbl = WS.get_Range(start, System.Type.Missing).get_Resize(rowct, 13);
Excel.ListObject LO = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tbl, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
LO.TableStyle = "TableStyleLight8";
LO.ShowTableStyleRowStripes = false;
((Excel.Range)LO.Range[0, 4]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 5]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 10]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 11]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 12]).EntireColumn.Hidden = true;
((Excel.Range)LO.Range[0, 6]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 7]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 8]).EntireColumn.NumberFormat = "0.0000";
((Excel.Range)LO.Range[0, 9]).EntireColumn.NumberFormat = "0.0000";
}
