Connect with Expertise | Find Experts, Get Answers, Share Insights
bytes > c# / c sharp > c# / c sharp questions

Displaying the rest of data in Excel

 
Join Date: Dec 2009
Posts: 26
#1: Jan 21 '10
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.
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using Excel = Microsoft.Office.Interop.Excel;
  5.  
  6. namespace UncFunc
  7. {
  8.     class Report
  9.     {
  10.         public void Create(Quantity q)
  11.         {
  12.             Excel.Application objApp;
  13.             Excel._Workbook objBook;
  14.             Excel.Workbooks objBooks;
  15.             Excel.Sheets objSheets;
  16.             Excel._Worksheet objSheet;
  17.             Excel.Range range;
  18.             Dictionary<int, Quantity> rootQty = q.RootQuantities;
  19.  
  20.             try
  21.             {
  22.                 // Instantiate Excel and start a new workbook.
  23.                 objApp = new Excel.Application();
  24.                 objBooks = objApp.Workbooks;
  25.                 objBook = objBooks.Add(Type.Missing);
  26.                 objSheets = objBook.Worksheets;
  27.                 objSheet = (Excel._Worksheet)objSheets.get_Item(1);
  28.  
  29.                 //Get the range where the starting cell has the address
  30.                 //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
  31.                 range = objSheet.get_Range("A1", Type.Missing);
  32.                 range = range.get_Resize(6,4);
  33.  
  34.                 //Create an array.
  35.                 string[,] saRet = new string[rootQty.Count+2, 4];
  36.                 saRet[0, 0] = "Root Quantities";
  37.                 saRet[1, 0] = "Symbol";
  38.                 saRet[1, 1] = "Value";
  39.                 saRet[1, 2] = "Std Unc";
  40.                 saRet[1, 3] = "Unit";
  41.  
  42.                 int[] aryKeys= new int[rootQty.Keys.Count];
  43.                 Dictionary<int,Quantity>.KeyCollection oKeys = rootQty.Keys;
  44.                 oKeys.CopyTo(aryKeys, 0);
  45.  
  46.                 //Fill the array.
  47.                 for (long iRow = 0; iRow < rootQty.Count; iRow++)
  48.                 {
  49.  
  50.                     //for (long iCol = 0; iCol < 5; iCol++)
  51.                     //{
  52.                         int iKey = aryKeys[iRow];
  53.                         Quantity qty = rootQty[iKey];
  54.                         String sSymbol = qty.Symbol;
  55.                         String sValue = qty.Value.ToString();
  56.                         String sStdUnc = qty.StdUnc.ToString();
  57.                         String sUnit = qty.Unit;
  58.  
  59.                         saRet[iRow+2,0] = sSymbol;
  60.                         saRet[iRow+2,1] = sValue;
  61.                         saRet[iRow+2,2] = sStdUnc;
  62.                         saRet[iRow+2,3]  =sUnit ;
  63.  
  64.                         //Put the row and column address in the cell.
  65.                         //saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();                        
  66.                     //}
  67.                 }
  68.  
  69.                 //Set the range value to the array.
  70.                 range.set_Value(Type.Missing, saRet);
  71.  
  72.                 //Set Subscript
  73.                 range = range.get_Resize(1, 1);
  74.                 //objSheet.get_Range("A1", Type.Missing);
  75.                 //range = range.get_Resize(1, 1);
  76.                 //range.get_Characters(2, 2).Font.Subscript = true;
  77.  
  78.                 //Bold(Added)
  79.                 //range = range.get_Resize(1, 1);
  80.                 range.get_Characters(1, 1).Font.Bold = true;
  81.                 range.Font.Bold = true;
  82.  
  83.                 //Return control of Excel to the user.
  84.                 objApp.Visible = true;
  85.                 objApp.UserControl = true;
  86.  
  87.                 //Save
  88.                 objBook.SaveAs("Report Uncertainty.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
  89.                     Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
  90.                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  91.             }
  92.             catch (Exception theException)
  93.             {
  94.                 String errorMessage;
  95.                 errorMessage = "Error: ";
  96.                 errorMessage = String.Concat(errorMessage, theException.Message);
  97.                 errorMessage = String.Concat(errorMessage, " Line: ");
  98.                 errorMessage = String.Concat(errorMessage, theException.Source);
  99.  
  100.                 Console.WriteLine(errorMessage, "Error");
  101.             }
  102.         }
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's Avatar
E
C
  
Join Date: Mar 2008
Location: Arizona, USA
Posts: 3,476
#2: Jan 21 '10

re: Displaying the rest of data in Excel

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)
 
Join Date: Dec 2009
Posts: 26
#3: Jan 21 '10

re: Displaying the rest of data in Excel

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's Avatar
E
C
  
Join Date: Mar 2008
Location: Arizona, USA
Posts: 3,476
#4: Jan 21 '10

re: Displaying the rest of data in Excel

"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.
 
Join Date: Dec 2009
Posts: 26
#5: Jan 22 '10

re: Displaying the rest of data in Excel

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. :(
 
Join Date: Dec 2009
Posts: 26
#6: Feb 11 '10

re: Displaying the rest of data in Excel

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:
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using Excel = Microsoft.Office.Interop.Excel;
  5.  
  6. namespace UncFunc
  7. {
  8.     class Report
  9.     {
  10.  
  11.         public void Create(Quantity q)
  12.         {
  13.             Excel.Application objApp;
  14.             Excel._Workbook objBook;
  15.             Excel.Workbooks objBooks;
  16.             Excel.Sheets objSheets;
  17.             Excel._Worksheet objSheet;
  18.             Excel.Range range;
  19.             Dictionary<int, Quantity> rootQty = q.RootQuantities;
  20.             //object misValue = System.Reflection.Missing.Value;
  21.  
  22.  
  23.             try
  24.             {
  25.                 // Instantiate Excel and start a new workbook.
  26.                 objApp = new Excel.Application();
  27.                 objBooks = objApp.Workbooks;
  28.                 objBook = objBooks.Add(Type.Missing);
  29.                 objSheets = objBook.Worksheets;
  30.                 objSheet = (Excel._Worksheet)objSheets.get_Item(1);
  31.  
  32.                 //Get the range where the starting cell has the address
  33.                 //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
  34.                 range = objSheet.get_Range("A1", Type.Missing);
  35.                 range = range.get_Resize(6,10);
  36.  
  37.                 //2nd table
  38.                 range = objSheet.get_Range("A2", Type.Missing);
  39.                 range = range.get_Resize(6, 10);
  40.  
  41.                 //Create an array.
  42.                 string[,] saRet = new string[rootQty.Count+2,10];
  43.  
  44.                 saRet[0, 4] = "Root Quantities";
  45.                 saRet[1, 0] = "String";
  46.                 saRet[1, 1] = "Symbol";
  47.                 saRet[1, 2] = "Limit Description";
  48.                 saRet[1, 3] = "Limit Type";
  49.                 saRet[1, 4] = "Value";
  50.                 saRet[1, 5] = "Limit";
  51.                 saRet[1, 6] = "DF";
  52.                 saRet[1, 7] = "Divisor";
  53.                 saRet[1, 8] = "Std Unc";
  54.                 saRet[1, 9] = "Unit";
  55.  
  56.  
  57.  
  58.  
  59.                 int[] aryKeys= new int[rootQty.Keys.Count];
  60.                 Dictionary<int,Quantity>.KeyCollection oKeys = rootQty.Keys;
  61.                 oKeys.CopyTo(aryKeys, 0);
  62.  
  63.                 //Fill the array.
  64.                 for (long iRow = 0; iRow < rootQty.Count; iRow++)
  65.                 {
  66.  
  67.                     //for (long iCol = 0; iCol < 4; iCol++)
  68.                     //{
  69.                         int iKey = aryKeys[iRow];
  70.  
  71.                         Quantity qty = rootQty[iKey];
  72.  
  73.                         String sSymbol = qty.Symbol;//column B
  74.                         String sLimitDescription = qty.LimitDescription;// column C
  75.                         //UncType = qty.LimitType.ToString();
  76.                         String sValue = qty.Value.ToString();//column D
  77.                         String sLimit = qty.Limit.ToString();//column E
  78.                         String sDF = qty.df.ToString();//column F
  79.                         String sDivisor = qty.Divisor.ToString();//column G
  80.                         String sStdUnc = qty.StdUnc.ToString();//column H
  81.                         String sUnit = qty.Unit;// column I
  82.  
  83.                         saRet[iRow+2, 1] = sSymbol;
  84.                         saRet[iRow + 2, 2] = sLimitDescription;
  85.                         //saRet[iRow + 2, 3] = qty.LimitType.ToString();
  86.                         saRet[iRow + 2, 4] = sValue.ToString();
  87.                         saRet[iRow + 2, 5] = sLimit.ToString();
  88.                         saRet[iRow + 2, 6] = sDF.ToString();
  89.                         saRet[iRow + 2, 7] = sDivisor.ToString();
  90.                         saRet[iRow + 2, 8] = sStdUnc.ToString();
  91.                         saRet[iRow + 2, 9] = sUnit;
  92.  
  93.  
  94.                     //Previous statements                
  95.                         //saRet[iRow+2,0] = sSymbol;
  96.                         //saRet[iRow+2,1] = sValue;
  97.                         //saRet[iRow+2,2] = sStdUnc;
  98.                         //saRet[iRow+2,3] = qty.Unit; //sUnit
  99.  
  100.                         //Put the row and column address in the cell.
  101.                         //saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
  102.  
  103.  
  104.                   // }
  105.                 }
  106.  
  107.  
  108.                 //Set the range value to the array.
  109.                 range.set_Value(Type.Missing, saRet);
  110.  
  111.                 //Replace the _ with space
  112.                 string myValue = "X_2";
  113.                 string newValue = myValue.Replace("_", "");
  114.  
  115.  
  116.                 //Set Subscript
  117.                 range = range.get_Resize(6, 9);
  118.                 objSheet.get_Range("A1", Type.Missing);
  119.                 //range = range.get_Resize(0, 2);
  120.                 //range.get_Range(2, 0);
  121.  
  122.                 objSheet.get_Range("A1", "A6").EntireColumn.AutoFit();
  123.                 objSheet.get_Range("B3", "B6").get_Characters(2, 2).Font.Subscript = true;//column and character chosen respectively
  124.  
  125.                 //2nd table
  126.                 //objSheet.get_Range("A10", "A16").EntireColumn.AutoFit();
  127.                 //objSheet.get_Range("B12", "B15").get_Characters(2, 2).Font.Subscript = true;//column and character chosen respectively
  128.  
  129.                 //objSheet.get_Range("B4","B6").Font.Subscript = true;
  130.                 //objSheet.get_Range(1, 1).Font.Subscript = true;
  131.  
  132.  
  133.                 //Set Bold(Added)
  134.                 objSheet.get_Range("A1", "J1").Font.Bold = true;
  135.                 objSheet.get_Range("A2", "J2").Font.Bold = true;
  136.                 //range = range.get_Resize(1, 1);
  137.                 //range.get_Characters(1,1).Font.Bold = true;
  138.                 //range.Font.Bold = true;
  139.  
  140.  
  141.                 //Return control of Excel to the user.
  142.                 objApp.Visible = true;
  143.                 objApp.UserControl = true;
  144.  
  145.  
  146.                 // Displaying formula in spreadsheet
  147.                 //objSheet.get_Range("A8", "A8").FormulaR1C1 = "y = x1 * x2 - x3 / x2 * Quantity.Log(x4)";
  148.  
  149.                 objSheet.get_Range("A1", "J6").BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium,
  150.                             Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
  151.  
  152.  
  153.                //Save
  154.                 objBook.SaveAs("Report Uncertainty.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
  155.                     Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
  156.                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  157.             }
  158.             catch (Exception theException)
  159.             {
  160.                 String errorMessage;
  161.                 errorMessage = "Error: ";
  162.                 errorMessage = String.Concat(errorMessage, theException.Message);
  163.                 errorMessage = String.Concat(errorMessage, " Line: ");
  164.                 errorMessage = String.Concat(errorMessage, theException.Source);
  165.  
  166.                 Console.WriteLine(errorMessage, "Error");
  167.             }
  168.         }
  169.  
  170.  
 
Join Date: Dec 2009
Posts: 26
#7: Feb 24 '10

re: Displaying the rest of data in Excel

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.
C
  
Join Date: Dec 2008
Posts: 285
#8: Feb 24 '10

re: Displaying the rest of data in Excel

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
 
Join Date: Dec 2009
Posts: 26
#9: Feb 24 '10

re: Displaying the rest of data in Excel

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.
Reply

« previous question | next question »

Similar topics

More channels
Copyright 1995-2010 BYTES. All rights Reserved About Bytes | Help
Latest Expert Topics | Popular Tags
Sitemap | C# / C Sharp Answers Sitemap | C# / C Sharp Insights Sitemap