locked
Unable to set the Color property of the Border class RRS feed

  • Question

  • User942015580 posted

     Friends,

     I'm trying to create an excel file from dataset. I'm facing the following error "System.Runtime.InteropServices.COMException: Unable to set the Color property of the Border class". The code snippet is :

        private void GenerateExcel(DataSet ds)
        {
            strFileName = "AllocationReport.xls";
            stFolderPath = ConfigurationManager.AppSettings.Get("FolderPathtoUpload").ToString();
            path = stFolderPath + strFileName;
            File.Delete(path);

            Excel.Application oXL;
            Excel.Workbooks oWBs;
            Excel._Workbook oWB;
            Excel.Sheets oSheet;
            Excel._Worksheet oSheet1;
            Excel.Range oRng;

            oXL = new Excel.Application();
            oXL.Visible = false;
            oWBs = oXL.Workbooks;
            oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            oSheet = oWB.Worksheets;
            oSheet1 = (Excel._Worksheet)oSheet.get_Item(1);
            oSheet1.get_Range("A1", "L1").Interior.ColorIndex = 35;
            oSheet1.get_Range("A1", "L1").Interior.Pattern = Excel.XlPattern.xlPatternSolid;
            oSheet1.get_Range("A1", "L1").Font.Bold = true;

            int exRow = 1;
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    oSheet1.Cells[exRow, j + 1] = ds.Tables[0].Columns[j].ColumnName; ;
                }

                Excel.Range range = oSheet1.get_Range("A1", "L1");
                range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black.ToArgb();
                range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = Color.Black.ToArgb();
                range.Borders[Excel.XlBordersIndex.xlEdgeRight].Color = Color.Black.ToArgb();
                range.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = Color.Black.ToArgb();
                range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = Color.Black.ToArgb();
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Color = Color.Black.ToArgb();
                ++exRow;

                for (int rowNo = 0; rowNo < ds.Tables[0].Rows.Count; rowNo++, exRow++)
                {
                    int colNo;
                    for (colNo = 0; colNo < ds.Tables[0].Columns.Count; colNo++)
                    {
                        oSheet1.Cells[exRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString();
                    }
                    Excel.Range range1 = oSheet1.get_Range("A" + exRow.ToString(), "L" + colNo.ToString());
                    range1.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black.ToArgb();
                    range1.Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = Color.Black.ToArgb();
                    range1.Borders[Excel.XlBordersIndex.xlEdgeRight].Color = Color.Black.ToArgb();
                    range1.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = Color.Black.ToArgb();
                    range1.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = Color.Black.ToArgb();
                    range1.Borders[Excel.XlBordersIndex.xlInsideVertical].Color = Color.Black.ToArgb();
                }
            }
            oRng = oSheet1.get_Range("A1", "L1");
            oRng.EntireColumn.AutoFit();
            oXL.Visible = false;
            oXL.UserControl = false;
            oWB.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
            oXL.Visible = false;
            Response.Clear();
            Response.ContentType = "Text/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=AllocationReport.xls");
            //oWB.Close(false, path, null);
            //oXL.Workbooks.Close();
            //oXL.Quit();
        }

     Please help.

     Linus

    Thursday, January 8, 2009 12:03 PM

Answers

  • User-1136466523 posted

    Hi,

    Based on my knowledge, the error occurs only if they have the top, left, right, and bottom borders explicitly set to zero (0). You need to check if you have set the borders, and it is best to start with the original file in Excel, to set a top, left, right, and bottom border for the table.

     Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 13, 2009 12:55 AM