Displaying the rest of data in Excel

[rshin2020]

I have a script as below. I need to display the data in excel. This is in C#.net. Right now, the data only displays up till B5 and C5. How do I make it display till B8 and C8. The values for symbol and unit are null.

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace UncFunc
{
    class Report
    {
        public void Create(Quantity q)
        {
            Excel.Application objApp;
            Excel._Workbook objBook;
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
            Dictionary<int, Quantity> rootQty = q.RootQuantities;
 
            try
            {
                // Instantiate Excel and start a new workbook.
                objApp = new Excel.Application();
                objBooks = objApp.Workbooks;
                objBook = objBooks.Add(Type.Missing);
                objSheets = objBook.Worksheets;
                objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 
                //Get the range where the starting cell has the address
                //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
                range = objSheet.get_Range("A1", Type.Missing);
                range = range.get_Resize(6,4);
 
                //Create an array.
                string[,] saRet = new string[rootQty.Count+2, 4];
                saRet[0, 0] = "Root Quantities";
                saRet[1, 0] = "Symbol";
                saRet[1, 1] = "Value";
                saRet[1, 2] = "Std Unc";
                saRet[1, 3] = "Unit";
 
                int[] aryKeys= new int[rootQty.Keys.Count];
                Dictionary<int,Quantity>.KeyCollection oKeys = rootQty.Keys;
                oKeys.CopyTo(aryKeys, 0);
 
                //Fill the array.
                for (long iRow = 0; iRow < rootQty.Count; iRow++)
                {
 
                    //for (long iCol = 0; iCol < 5; iCol++)
                    //{
                        int iKey = aryKeys[iRow];
                        Quantity qty = rootQty[iKey];
                        String sSymbol = qty.Symbol;
                        String sValue = qty.Value.ToString();
                        String sStdUnc = qty.StdUnc.ToString();
                        String sUnit = qty.Unit;
 
                        saRet[iRow+2,0] = sSymbol;
                        saRet[iRow+2,1] = sValue;
                        saRet[iRow+2,2] = sStdUnc;
                        saRet[iRow+2,3]  =sUnit ;
 
                        //Put the row and column address in the cell.
                        //saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();                        
                    //}
                }
 
                //Set the range value to the array.
                range.set_Value(Type.Missing, saRet);
 
                //Set Subscript
                range = range.get_Resize(1, 1);
                //objSheet.get_Range("A1", Type.Missing);
                //range = range.get_Resize(1, 1);
                //range.get_Characters(2, 2).Font.Subscript = true;
 
                //Bold(Added)
                //range = range.get_Resize(1, 1);
                range.get_Characters(1, 1).Font.Bold = true;
                range.Font.Bold = true;
 
                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
 
                //Save
                objBook.SaveAs("Report Uncertainty.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
 
                Console.WriteLine(errorMessage, "Error");
            }
        }

Hope someone can help, Thanks in advance. I know i have to change the coding after the for long code. I have tried but still outputs the indifferent.

[tlhintoq]

Let me guess. You just copied this from someplace else and have no idea how it works.

I might be way off here because I don't do much with Office Interop, but line 54 looks pretty obvious since you only get through row 5 and the loop goes from 0 to 4 (that would be 5)

[rshin2020]

Nope, my boss had this example and asked me to go through it. I have added some codes inside to customize it. Its just that i couldnt figure it out where the mistake is.
"line 54 looks pretty obvious since you only get through row 5 and the loop goes from 0 to 4 (that would be 5) " meaningi have to change the loop to 4? But is that line even used? since i commanded it with //.

[tlhintoq]

"line 54 looks pretty obvious since you only get through row 5 and the loop goes from 0 to 4 (that would be 5) "

meaningi have to change the loop to 4? But is that line even used? since i commanded it with //.

You are very right. I was tired when I read that and missed that the loop was commented out.

What about line 33 where the range is selected? Like I said, I don't do much programmatically with Excel, and thought I had seen a fairly obvious place to increase the area of interest. I guess I was wrong. That's what trial and error is all about.

[rshin2020]

Its okay, trial and error i guess.... Line 33? I have tried customizing it too, but still no change. I have tried to customize many places as well, but still no change. :(

[rshin2020]

Hi. With reference to the above topic, how do I get to display another table of data in the same sheet? I managed to display the first table with data but now i need to display a second table. Where do I change or add the codings?
here's the latest:

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace UncFunc
{
    class Report
    {
 
        public void Create(Quantity q)
        {
            Excel.Application objApp;
            Excel._Workbook objBook;
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
            Dictionary<int, Quantity> rootQty = q.RootQuantities;
            //object misValue = System.Reflection.Missing.Value;
 
 
            try
            {
                // Instantiate Excel and start a new workbook.
                objApp = new Excel.Application();
                objBooks = objApp.Workbooks;
                objBook = objBooks.Add(Type.Missing);
                objSheets = objBook.Worksheets;
                objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 
                //Get the range where the starting cell has the address
                //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
                range = objSheet.get_Range("A1", Type.Missing);
                range = range.get_Resize(6,10);
 
                //2nd table
                range = objSheet.get_Range("A2", Type.Missing);
                range = range.get_Resize(6, 10);
 
                //Create an array.
                string[,] saRet = new string[rootQty.Count+2,10];
 
                saRet[0, 4] = "Root Quantities";
                saRet[1, 0] = "String";
                saRet[1, 1] = "Symbol";
                saRet[1, 2] = "Limit Description";
                saRet[1, 3] = "Limit Type";
                saRet[1, 4] = "Value";
                saRet[1, 5] = "Limit";
                saRet[1, 6] = "DF";
                saRet[1, 7] = "Divisor";
                saRet[1, 8] = "Std Unc";
                saRet[1, 9] = "Unit";
 
 
 
 
                int[] aryKeys= new int[rootQty.Keys.Count];
                Dictionary<int,Quantity>.KeyCollection oKeys = rootQty.Keys;
                oKeys.CopyTo(aryKeys, 0);
 
                //Fill the array.
                for (long iRow = 0; iRow < rootQty.Count; iRow++)
                {
 
                    //for (long iCol = 0; iCol < 4; iCol++)
                    //{
                        int iKey = aryKeys[iRow];
 
                        Quantity qty = rootQty[iKey];
 
                        String sSymbol = qty.Symbol;//column B
                        String sLimitDescription = qty.LimitDescription;// column C
                        //UncType = qty.LimitType.ToString();
                        String sValue = qty.Value.ToString();//column D
                        String sLimit = qty.Limit.ToString();//column E
                        String sDF = qty.df.ToString();//column F
                        String sDivisor = qty.Divisor.ToString();//column G
                        String sStdUnc = qty.StdUnc.ToString();//column H
                        String sUnit = qty.Unit;// column I
 
                        saRet[iRow+2, 1] = sSymbol;
                        saRet[iRow + 2, 2] = sLimitDescription;
                        //saRet[iRow + 2, 3] = qty.LimitType.ToString();
                        saRet[iRow + 2, 4] = sValue.ToString();
                        saRet[iRow + 2, 5] = sLimit.ToString();
                        saRet[iRow + 2, 6] = sDF.ToString();
                        saRet[iRow + 2, 7] = sDivisor.ToString();
                        saRet[iRow + 2, 8] = sStdUnc.ToString();
                        saRet[iRow + 2, 9] = sUnit;
 
 
                    //Previous statements                
                        //saRet[iRow+2,0] = sSymbol;
                        //saRet[iRow+2,1] = sValue;
                        //saRet[iRow+2,2] = sStdUnc;
                        //saRet[iRow+2,3] = qty.Unit; //sUnit
 
                        //Put the row and column address in the cell.
                        //saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
 
 
                  // }
                }
 
 
                //Set the range value to the array.
                range.set_Value(Type.Missing, saRet);
 
                //Replace the _ with space
                string myValue = "X_2";
                string newValue = myValue.Replace("_", "");
 
 
                //Set Subscript
                range = range.get_Resize(6, 9);
                objSheet.get_Range("A1", Type.Missing);
                //range = range.get_Resize(0, 2);
                //range.get_Range(2, 0);
 
                objSheet.get_Range("A1", "A6").EntireColumn.AutoFit();
                objSheet.get_Range("B3", "B6").get_Characters(2, 2).Font.Subscript = true;//column and character chosen respectively
 
                //2nd table
                //objSheet.get_Range("A10", "A16").EntireColumn.AutoFit();
                //objSheet.get_Range("B12", "B15").get_Characters(2, 2).Font.Subscript = true;//column and character chosen respectively
 
                //objSheet.get_Range("B4","B6").Font.Subscript = true;
                //objSheet.get_Range(1, 1).Font.Subscript = true;
 
 
                //Set Bold(Added)
                objSheet.get_Range("A1", "J1").Font.Bold = true;
                objSheet.get_Range("A2", "J2").Font.Bold = true;
                //range = range.get_Resize(1, 1);
                //range.get_Characters(1,1).Font.Bold = true;
                //range.Font.Bold = true;
 
 
                //Return control of Excel to the user.
                objApp.Visible = true;
                objApp.UserControl = true;
 
 
                // Displaying formula in spreadsheet
                //objSheet.get_Range("A8", "A8").FormulaR1C1 = "y = x1 * x2 - x3 / x2 * Quantity.Log(x4)";
 
                objSheet.get_Range("A1", "J6").BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium,
                            Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
 
 
               //Save
                objBook.SaveAs("Report Uncertainty.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                    Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
 
                Console.WriteLine(errorMessage, "Error");
            }
        }
 
 

[rshin2020]

Any replies? What part do i have to change? From a logical aspect, i understand that i have to copy the array and paste it somewhere. I'm not sure where to paste it though. Please help. Thanks a lot in advance.

[Bassem]

Hi,

Well, I don't have an answer to you, but I have a suggestion.

Three days ago, I didn't work with Excel through C#. When I decided I used XslCompiledTransform.
It is the simplest and fastest way to work with Excel like this:
1. Convert Excel file to XML.
2. Work with XML via Linq or DOM whatever you prefer.

It will not take more than few minutes to figure it and use it.

Perhaps my post is useless, but may be you give it a try and it will work for you. Working with XML is more simpler and used wider than Excel.

Hoped to help!

Thanks,
Bassem

[rshin2020]

That's a good suggestion. Will consider it. But as for now, I was asked to do it in excel. I am unsure of where to put the code. Thanks a lot, Bassem.