Usuario
Ayuda con Microsoft.Office.Interop y c#

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);
}