Help with Microsoft.Office.Interop and c#..........

N

NATHALY_EC

Hello, please i need help, I need to create an excel file that contains a
column of cells with data validation type list, as I do this? I tried using
"xlsRange.FormulaLabel" and "xlsWorkbook.AcceptLabelsInFormulas = true"
because my cell I need the list in another excel sheet but I always see an
error.

Here my code:
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);
}
 
Top