none
Ayuda con Microsoft.Office.Interop y c# RRS feed

  • Pregunta

  • Hola, por favor necesito ayuda, necesito crear un archivo de excel que contenga una columna de celdas con validacion de datos tipo lista, como puedo hacer esto? he intentado usar: "xlsRange.FormulaLabel" y "xlsWorkbook.AcceptLabelsInFormulas = true" debido a que mis celdas de la lista las necesito en otra hoja de excel pero siempre veo un error.

    Aqui mi codigo:

    public void ConvertXMLToExcel(string strFileName)
                {
                    int vcont=1;
                    int vrow = 1;
                    int vcol = 0;
                    int vsheet = 0;
                    int IdProcessExcell = 0;
                    XDocument xmldoc = XDocument.Load(strFileName);
                    try
                    {
                        if (xmldoc.Elements().Count() > 0)
                        {
                            xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                            IdProcessExcell=System.Diagnostics.Process.GetProcessesByName("Excel")[0].Id;
                            xlsApp.DisplayAlerts = false;

                            foreach (XElement XLibro in xmldoc.Element("Excel").Elements("Libro"))
                            {
                                xlsWorkbook = xlsApp.Workbooks.Add(true);
                                //xlsWorkbook.AcceptLabelsInFormulas = true;
                                foreach (XElement XHoja in XLibro.Elements())
                                {
                                    vsheet = vsheet + 1;
                                    if (vsheet > 1)
                                        xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                                    else
                                        xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[vsheet]);
                                    xlsWorksheet.Activate();
                                    xlsWorksheet.Name = XHoja.FirstAttribute.Value;
                                    foreach (XElement XHojaColumna in XHoja.Elements("Campos"))
                                    {
                                        vlist = new List<List<string>>();
                                        foreach (XElement XCampos in XHojaColumna.Elements("Campo"))
                                        {
                                           
                                            foreach (XAttribute XAtributte in XCampos.Attributes("Campo"))
                                            {
                                                xlsWorksheet.Cells[1, vcont] = XAtributte.Value;
                                                vcont = vcont + 1;
                                            }
                                            foreach (XAttribute XAtributte in XCampos.Attributes("ConAyuda"))
                                            {
                                                vNodelist = new List<string>();

                                                 if (XAtributte.Value=="1")
                                                 {
                                                    foreach (XElement XCampoAyuda in XCampos.Elements("Ayuda"))
                                                    {
                                                       vNodelist.Add(XCampoAyuda.Attribute("Valor").Value);                                                
                                                    }
                                                 }
                                            }
                                            vlist.Add(vNodelist);
                                        }
                                        vcont = 1;
                                    }
                                    vNodeListIndex=0;
                                    foreach (XElement XHojaFila in XHoja.Elements("Renglon"))
                                    {
                                        vrow = vrow + 1;
                                        foreach (XElement XCampos in XHojaFila.Elements("Celda"))
                                        {
                                            foreach (XAttribute XAtributte in XCampos.Attributes("Valor"))
                                            {
                                                vcol = vcol + 1;
                                                xlsWorksheet.Cells[vrow, vcol] = XAtributte.Value;
                                            }
                                           if ( vlist[vNodeListIndex].Count>0 )
                                           {
                                               CellWithList(vrow, vcol, vlist[vNodeListIndex], xlsWorkbook, xlsWorksheet);
                                              
                                           }
                                           vNodeListIndex = vNodeListIndex + 1;
                                        }
                                        vNodeListIndex = 0;
                                        vcol = 0;
                                    }
                                    vrow = 1;
                                    releaseObject(xlsWorksheet);
                                }
                                if (File.Exists(Path.GetDirectoryName(strFileName) + XLibro.Attribute("Archivo").Value))
                                {
                                    File.Delete(Path.GetDirectoryName(strFileName) + XLibro.Attribute("Archivo").Value);
                                }
                                xlsWorkbook.Close(true, Path.GetDirectoryName(strFileName) + XLibro.Attribute("Archivo").Value, null);                         
                                releaseObject(xlsWorkbook);
                            }
                            xlsApp.Quit();
                            System.Diagnostics.Process.GetProcessById(IdProcessExcell).Kill();
                        }
                    }
                    catch (Exception ex)
                    {
                        string msgerror = ex.Message;
                    }
                    finally
                    {
                         releaseObject(xlsApp);
                    }
                 
                }
                private void CellWithList(int vrow, int vcol, List<String> vTmpNodelist, Microsoft.Office.Interop.Excel.Workbook xlsWorkbook,Microsoft.Office.Interop.Excel.Worksheet  xlsWorksheet)
                {
                    Microsoft.Office.Interop.Excel.Worksheet xlsTmpWorksheet;
                    xlsTmpWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                    xlsTmpWorksheet.Name = Guid.NewGuid().GetHashCode().ToString("X");

                    Microsoft.Office.Interop.Excel.Range xlsRange;

                    xlsRange = xlsWorksheet.get_Range(xlsWorksheet.Cells[vrow, vcol], xlsWorksheet.Cells[vrow, vcol]);

                    int vtmprow = 2;
                    foreach (string vvalue in vTmpNodelist)
                    {
                        vtmprow = vtmprow + 1;
                        xlsTmpWorksheet.get_Range(xlsTmpWorksheet.Cells[2, vcol], xlsTmpWorksheet.Cells[vtmprow, vcol]).Value2 = vvalue;
                        //xlsWorksheet.get_Range(xlsWorksheet.Cells[vtmprow, vcol], xlsWorksheet.Cells[vtmprow, vcol]).Value2 = vvalue;
                    }
                    //xlsRange.Select();
                    xlsRange.FormulaLabel = Microsoft.Office.Interop.Excel.XlFormulaLabel.xlColumnLabels ;//"abc"; // xlsTmpWorksheet.Name;
                    xlsRange.Name = "aaa";
                    string values = "";
                    //values = "=" + xlsWorksheet.get_Range(xlsWorksheet.Cells[30, vcol], xlsWorksheet.Cells[vtmprow, vcol]).get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                    //values = "=" + xlsTmpWorksheet.get_Range(xlsTmpWorksheet.Cells[1, vcol], xlsTmpWorksheet.Cells[vtmprow, vcol]).get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                    values = "=" + xlsRange.Name ;
                    xlsRange.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, values, Type.Missing);
                }

    martes, 9 de marzo de 2010 15:24