Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet - C# Discussion Boards

Regarding printing of Windows forms [modified]

manikandanid7-Apr-08 2:23

7-Apr-08 2:23

Hi All,
I have a project which includes multiple long windows forms. Some of these forms have around 200
fields. These forms will use a combination of custom user controls and third
party components. If it is at all possible I would like to avoid manually
adding a thousand fields to Print Documents. What would be a best practice for
sending such large windows forms to a printer? Is anyone aware of any third
party components that would provide a solution for this problem? Could this
problem be solved by looping through the controls on the form somehow? Any
advice or direction that you could give me would be greatly appreciated.

Thanks in Advance,
Mani.

modified on Tuesday, April 8, 2008 2:11 AM

Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza7-Apr-08 2:18

Walaza

7-Apr-08 2:18

Hi,

I would like to dynamically add TabPages to my form depending on a number of Sheets$ that are contained in a spredsheet I'm loading to a datagrid. At the moment I have an application that loads a spreadsheet to a datagrid, but it only loads one (the default) worksheet. How can I dynamically create tabpages depending on the number of worksheets in the spreasheet and load them to the datagrid. Below is the code of what the application can do so far:

//This takes the Excel file from a specific location
//and displays it in the datagrid
private void btnOpenFile_Click(object sender, EventArgs e)
{
// prepare open file dialog to only search for excel files
this.ofd.FileName = "*.xls";
if (this.ofd.ShowDialog() == DialogResult.OK)
{
try
{
// Here is the call to Open a Workbook in Excel
// It uses most of the default values (except for the read-only which we set to true)
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(ofd.FileName, 0, true, 5,
"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, true);

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

// get the first and only worksheet from the collection of worksheets
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

//get start and end rows
Object[] startRow = { worksheet.get_Range("A4", "O4") };
int endRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;

//Check if all columns are according to spec
if ((worksheet.get_Range("A3", "A3").Text.ToString() == "Template") &&
(worksheet.get_Range("B3", "B3").Text.ToString() == "Error Code") &&
(worksheet.get_Range("C3", "C3").Text.ToString() == "Error Name") &&
(worksheet.get_Range("D3", "D3").Text.ToString() == "Error Description") &&
(worksheet.get_Range("E3", "E3").Text.ToString() == "Error Cause") &&
(worksheet.get_Range("F3", "F3").Text.ToString() == "Source") &&
(worksheet.get_Range("G3", "G3").Text.ToString() == "Destination") &&
(worksheet.get_Range("H3", "H3").Text.ToString() == "Category") &&
(worksheet.get_Range("I3", "I3").Text.ToString() == "Severity") &&
(worksheet.get_Range("J3", "J3").Text.ToString() == "Threshold") &&
(worksheet.get_Range("K3", "K3").Text.ToString() == "Responsible for Resolution") &&
(worksheet.get_Range("L3", "L3").Text.ToString() == "Action") &&
(worksheet.get_Range("M3", "M3").Text.ToString() == "ECS Resubmit Y/N") &&
(worksheet.get_Range("N3", "N3").Text.ToString() == "ECS Auto Resubmit Y/N") &&
(worksheet.get_Range("O3", "O3").Text.ToString() == "ECS XML Editing Y/N") &&
(worksheet.get_Range("P4", "P4").Text.ToString() == "ECS Client Notification Y/N – If Yes, supply info") &&
(worksheet.get_Range("Q4", "Q4").Text.ToString() == "Notification Type Email SMS or Both") &&
(worksheet.get_Range("R4", "R4").Text.ToString() == "E-Mail Group") &&
(worksheet.get_Range("S4", "S4").Text.ToString() == "SMS Number") &&
(worksheet.get_Range("T4", "T4").Text.ToString() == "Escalation Y/N") &&
(worksheet.get_Range("U4", "U4").Text.ToString() == "Escalate to who?"))
{
//MessageBox.Show("The headings in the spreadsheet are correct! \nYou may proceed.","Headings", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The Headings should be: \nTemplate, Error Code, Error Name," +
" Error Description, Error Cause, Source, Destination, Category, Severity, Threshold, Responsible for Resolution, Action,ECS Resubmit Y/N, " +
" ECS Auto Resubmit Y/N, ECS XML Editing Y/N, ECS Client Notification Y/N – If Yes, supply info, " +
" Notification Type Email SMS or Both, E-Mail Group, SMS Number, Escalation Y/N, Escalate to who?, " +
". \n\nPlease load the correct file or Click the 'Create Blank Template' button to get a blank Template.", "Error : Headings", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

//Check if all the required tables are there
if ((worksheet.get_Range("H3", "H3").Text.ToString() == "Category") &&
(worksheet.get_Range("I3", "I3").Text.ToString() == "Severity"))
{
//MessageBox.Show("All the required tables were found \nYou may proceed.", "Required Tables", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The following tables are required: \nCategory, Severity\n\nPlease ensure that these tables exist.",
"Error : Required Tables", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.Application.Exit();
ExcelObj.Visible = false;
}

// loop through the rows of the spreadsheet and place each row in the datagrid
for (int i = 5; i <= endRow; i++)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "Z" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.Value2;

string[] strArray = ConvertToStringArray(myvalues);

//Add the rows from the spreadsheet to the datagrid
DataGridViewRow row = new DataGridViewRow();
Object[] vals = strArray;
row.CreateCells(dgOutput, vals);
dgOutput.Rows.Add(row);
}

}
catch (Exception EX)
{
MessageBox.Show("Error: " + EX.Message, "Error opening Excel..");
}
}
}
string[] ConvertToStringArray(System.Array values)
{
// create a new string array
string[] theArray = new string[values.Length];
theArray[0] = "";
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i < values.Length; i++)
{
if (values.GetValue(1, i) == null)
theArray[i] = "";
else
theArray[i] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}

Please help.

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet [modified]

lsconyer7-Apr-08 2:32

lsconyer

7-Apr-08 2:32

I'm not terribly familiar with the Microsoft.Office namespace but after you get the collection of WorkSheets called sheets, I would think you can loop through them all and create pages. Something like this:

<br />
<br />
TabPage page;<br />
DataGridView dgv;<br />
foreach(Worksheet sheet in sheets)<br />
{<br />
page = new TabPage();<br />
dgv = new DataGridView();<br />
<br />
//loop thru records in worksheet. foreach record, add row to datagridview<br />
--enter code here--<br />
<br />
//after adding records, add datagridview to tab page<br />
page.Controls.Add(dgv);<br />
<br />
//add the page to your TabControl<br />
//this line each page to the first position<br />
this.tabControl1.TabPages.Insert(0, page);<br />
}<br />

Hope this helps.

Lester
http://www.lestersconyers.com
modified on Monday, April 7, 2008 10:25 AM

modified on Monday, April 7, 2008 11:07 AM

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

darkelv7-Apr-08 4:14

darkelv

7-Apr-08 4:14

Could you remove the < - - i think it is causing the page not rendering after your post on FireFox.

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza7-Apr-08 4:54

Walaza

7-Apr-08 4:54

Hi,
Thanks for the reply. Should I have put this code in the constructor or should I put it under the button that loads the spreadsheet? Should I put the tabControl from the ToolBox or should I only use the dynamically created one? I have put in your suggested code but when I load the spreadsheet my form just becomes blank and the tabs do not get created. D'Oh! | :doh:

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer7-Apr-08 5:07

lsconyer

7-Apr-08 5:07

I would put this code under the button that loads the spreadsheet. If not, you'll get a problem. And I need to modify the code I submitted. In the code I had you create a new TabControl without adding it to the form. Hopefully, you already have a tab control on the page so the designer can set a bunch of properties. I'm going to modify my original code based on that assumption.

If not, you'll have to do something like

<br />
this.Controls.Add(tabControl1);<br />

Lester
http://www.lestersconyers.com

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza7-Apr-08 5:59

Walaza

7-Apr-08 5:59

Thank you so much, I am making so much progress in one day! The problem is, it only shows the data of only one worksheet. It shows the tabs and their worksheets names but does not add the datagrid and the data to the tabpage. What could I be doing wrong, here's the code:
//This takes the Excel file from a specific location
//and displays it in the datagrid
private void btnOpenFile_Click(object sender, EventArgs e)
{

// prepare open file dialog to only search for excel files
this.ofd.FileName = "*.xls";
if (this.ofd.ShowDialog() == DialogResult.OK)
{
try
{
// Here is the call to Open a Workbook in Excel
// It uses most of the default values (except for the read-only which we set to true)
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(ofd.FileName, 0, true, 5,
"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, true);

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

//theWorkbook.Worksheets[sheets];

// get the first and only worksheet from the collection of worksheets
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

TabControl tbControl = new TabControl();
this.Controls.Add(tbControl);
tbControl.Dock = DockStyle.Bottom;
tbControl.Height = 660;
TabPage page;
//DataGridView dgv;

foreach (Worksheet sheet in sheets)
{
page = new TabPage(sheet.Name.ToString());
//dgOutput = new DataGridView();

//get start and end rows
Object[] startRow = { worksheet.get_Range("A4", "O4") };
int endRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;

//Check the columns...Removed

// loop through the rows of the spreadsheet and place each row in the datagrid
for (int i = 5; i <= endRow; i++)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "Z" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.Value2;

string[] strArray = ConvertToStringArray(myvalues);

//Add the rows from the spreadsheet to the datagrid
DataGridViewRow row = new DataGridViewRow();
Object[] vals = strArray;
row.CreateCells(dgOutput, vals);
dgOutput.Rows.Add(row);
}

page.Controls.Add(dgOutput);
tbControl.TabPages.Insert(0, page);
}
}
catch (Exception EX)
{
MessageBox.Show("Error: " + EX.Message, "Error opening Excel..");
}
}

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer7-Apr-08 6:57

lsconyer

7-Apr-08 6:57

You have to create a new datagridview object for each new page. Then add that datagridview to the page. Now, you're just adding dgOutput to each page. Then reseting it and adding it to the next page. It'll be on the last page you inserted, which will be the first page when you .Insert(0, page). Uncomment the //dgOutput = new DataGridView();

Lester
http://www.lestersconyers.com

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza7-Apr-08 20:31

Walaza

7-Apr-08 20:31

When I uncomment the line: dgOutput = new DataGridView(); I get the ff error: 'No row can be added to a DataGridView control that does not have columns. Columns must be added first.'
Is there anything else I need to add to the code below:

TabControl tbControl = new TabControl();
this.Controls.Add(tbControl);
tbControl.Dock = DockStyle.Bottom;
tbControl.Height = 660;
TabPage page;

foreach (Worksheet sheet in sheets)
{
page = new TabPage(sheet.Name.ToString());
dgOutput = new DataGridView();

//get start and end rows
Object[] startRow = { worksheet.get_Range("A4", "O4") };
int endRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;

//Check if all columns are according to spec
if ((worksheet.get_Range("A3", "A3").Text.ToString() == "Template") &&
(worksheet.get_Range("B3", "B3").Text.ToString() == "Error Code") &&
(worksheet.get_Range("C3", "C3").Text.ToString() == "Error Name") &&
(worksheet.get_Range("D3", "D3").Text.ToString() == "Error Description") &&
(worksheet.get_Range("E3", "E3").Text.ToString() == "Error Cause") &&
(worksheet.get_Range("F3", "F3").Text.ToString() == "Source") &&
(worksheet.get_Range("G3", "G3").Text.ToString() == "Destination") &&
(worksheet.get_Range("H3", "H3").Text.ToString() == "Category") &&
(worksheet.get_Range("I3", "I3").Text.ToString() == "Severity") &&
(worksheet.get_Range("J3", "J3").Text.ToString() == "Threshold") &&
(worksheet.get_Range("K3", "K3").Text.ToString() == "Responsible for Resolution") &&
(worksheet.get_Range("L3", "L3").Text.ToString() == "Action") &&
(worksheet.get_Range("M3", "M3").Text.ToString() == "ECS Resubmit Y/N") &&
(worksheet.get_Range("N3", "N3").Text.ToString() == "ECS Auto Resubmit Y/N") &&
(worksheet.get_Range("O3", "O3").Text.ToString() == "ECS XML Editing Y/N") &&
(worksheet.get_Range("P4", "P4").Text.ToString() == "ECS Client Notification Y/N – If Yes, supply info") &&
(worksheet.get_Range("Q4", "Q4").Text.ToString() == "Notification Type Email SMS or Both") &&
(worksheet.get_Range("R4", "R4").Text.ToString() == "E-Mail Group") &&
(worksheet.get_Range("S4", "S4").Text.ToString() == "SMS Number") &&
(worksheet.get_Range("T4", "T4").Text.ToString() == "Escalation Y/N") &&
(worksheet.get_Range("U4", "U4").Text.ToString() == "Escalate to who?"))
{
//MessageBox.Show("The headings in the spreadsheet are correct! \nYou may proceed.","Headings", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The Headings should be: \nTemplate, Error Code, Error Name," +
" Error Description, Error Cause, Source, Destination, Category, Severity, Threshold, Responsible for Resolution, Action,ECS Resubmit Y/N, " +
" ECS Auto Resubmit Y/N, ECS XML Editing Y/N, ECS Client Notification Y/N – If Yes, supply info, " +
" Notification Type Email SMS or Both, E-Mail Group, SMS Number, Escalation Y/N, Escalate to who?, " +
". \n\nPlease load the correct file or Click the 'Create Blank Template' button to get a blank Template.", "Error : Headings", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

//Check if all the required tables are there
if ((worksheet.get_Range("H3", "H3").Text.ToString() == "Category") &&
(worksheet.get_Range("I3", "I3").Text.ToString() == "Severity"))
{
//MessageBox.Show("All the required tables were found \nYou may proceed.", "Required Tables", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The following tables are required: \nCategory, Severity\n\nPlease ensure that these tables exist.",
"Error : Required Tables", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.Application.Exit();
ExcelObj.Visible = false;
}

// loop through the rows of the spreadsheet and place each row in the datagrid
for (int i = 5; i <= endRow; i++)
{
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "Z" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.Value2;

string[] strArray = ConvertToStringArray(myvalues);

//Add the rows from the spreadsheet to the datagrid
DataGridViewRow row = new DataGridViewRow();
Object[] vals = strArray;
row.CreateCells(dgOutput, vals);
dgOutput.Rows.Add(row);
}

page.Controls.Add(dgOutput);
tbControl.TabPages.Insert(0, page);

}
}
catch (Exception EX)
{
MessageBox.Show("Error: " + EX.Message, "Error opening Excel..");
}

It seems like I am very close to completing this. Please help.

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer8-Apr-08 0:37

lsconyer

8-Apr-08 0:37

There's nothing else you can do. You have to create a new datagridview for each tab. Within the foreach loop, set up the columns for the datagridview. You can't use a global datagridview. Within that foreach loop, do everything you need to do for a datagridview then add it to the tabpage.

Lester
http://www.lestersconyers.com

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza8-Apr-08 2:52

Walaza

8-Apr-08 2:52

I did as you told me and the datagrid is visible in each tabpage but now the problem is that it only shows the data that is in the first sheet in all the tabs. Each tabpage should have its own data and should display its own worksheet in the datagrid.

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer8-Apr-08 3:30

lsconyer

8-Apr-08 3:30

Where does that variable named worksheet come from? Instead of using worksheet to check validity and get range, you should be using the variable name sheet. Replace the variable named worksheet with the variable named sheet inside of the foreach loop.

Lester
http://www.lestersconyers.com

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza8-Apr-08 4:33

Walaza

8-Apr-08 4:33

Thank you very much, it worked!!

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza8-Apr-08 20:08

Walaza

8-Apr-08 20:08

Hi,
Would you happen to know how I could take the data from all the dynamically created tabpages’ datagrids and export the data to excel and create a new worksheet for each tabpage as it is exporting. Currently it takes data from one dynamically created tabpage (the default)‘s datagrid and exports it to a spreadsheet. Here's the code I have so far:

Microsoft.Office.Interop.Excel._Application _appClass = new ApplicationClass();
_appClass.Visible = true;

_appClass.Caption = "Exported ECS Errors";

Microsoft.Office.Interop.Excel.Workbook wbk = _appClass.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

Worksheet obj = (Worksheet)wbk.ActiveSheet;

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Worksheets;

Range r = (Range)obj.Cells;

try
{
//Load the Headers and make them BOLD
for (int i = 0; i < ((DataGridView)this.dgOutput).Columns.Count; i++)
{
r[1, i + 1] = ((DataGridView)this.dgOutput).Columns[i].HeaderText.ToString();
((Range)r.Cells[1, i + 1]).Font.Bold = true;
}

//Load the data from the datagrid to the new spreadsheet
for (int i = 1; i < ((DataGridView)this.dgOutput).Rows.Count - 1; i++)
{
for (int j = 0; j < ((DataGridView)this.dgOutput).Columns.Count; j++)
{

r[i + 1, j + 1] = ((DataGridView)this.dgOutput).Rows[i].Cells[j].Value.ToString();

//Do the colors
if (((DataGridView)this.dgOutput).Rows[i].Cells[j].Value.ToString() == "Update OK")
{
((Range)r.Cells[i + 1, 1]).Font.Color = Color.Green.G;
}
}
}
}
catch (Exception EX) //user closes the Excel file while it is loading data from the datagrid
{
MessageBox.Show(EX.Message + "The Excel file was closed while loading data from the datagrid.",
"Error : Loading file!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet [modified]

lsconyer9-Apr-08 1:48

lsconyer

9-Apr-08 1:48

I don't see why not. You just have to loop through the tabpages. You have to know your logic. I don't have time to work on the code but here is what should work:

<br />
//loop through each tabpage in tabcontrol.tabpages<br />
foreach(TabPage page in tabcontrol.TabPages)<br />
{<br />
//get the datagrid from the tabpage's control. It may not be at the 0 index but it should be in there<br />
DataGridView dgv = (DataGridView)page.Controls[0];<br />
<br />
     //loop through each datarow in datagridview<br />
     foreach(DataGridViewRow row in dgv.Rows)<br />
     {<br />
          //create new worksheet<br />
<br />
          //load data from datagrid to worksheet<br />
<br />
          //add worksheet to workbook<br />
     }<br />
<br />
}<br />

Good luck

Lester
http://www.lestersconyers.com

modified on Wednesday, April 9, 2008 9:40 AM

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza9-Apr-08 3:30

Walaza

9-Apr-08 3:30

Hi,
These are the errors I get when I try the code you suggested:
1.
foreach statement cannot operate on variables of type 'System.Windows.Forms.TabControl' because 'System.Windows.Forms.TabControl' does not contain a public definition for 'GetEnumerator'

2.
foreach statement cannot operate on variables of type 'System.Windows.Forms.DataGridView' because 'System.Windows.Forms.DataGridView' does not contain a public definition for 'GetEnumerator'

Is there anything I am missing?

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer9-Apr-08 3:46

lsconyer

9-Apr-08 3:46

I edited the code. Try it.

Lester
http://www.lestersconyers.com

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

Walaza9-Apr-08 4:21

Walaza

9-Apr-08 4:21

I tried the code but when I click the button nothing happens. What could I be doing wrong? Here is the code:

//This exports data from the datagrid to a new EXCEL spreadsheet
private void btnExport_Click(object sender, EventArgs e)
{
//Check if the file has been added to the datagrid
if (dgOutput.RowCount == 1)
{
MessageBox.Show("Please load the file first!!", "Error : Load file!!",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
TabControl tbControl = new TabControl();

//loop through each tabpage in tabcontrol.tabpages
foreach (TabPage page in tbControl.TabPages)
{
//get the datagrid from the tabpage's control. It may not be at the 0 index but it should be in there
DataGridView dgv = (DataGridView)page.Controls[0];

//loop through each datarow in datagridview
foreach (DataGridViewRow row in dgv.Rows)
{
//create new worksheet
Microsoft.Office.Interop.Excel._Application _appClass = new ApplicationClass();
_appClass.Visible = true;

_appClass.Caption = "Exported ECS Errors";

Microsoft.Office.Interop.Excel.Workbook wbk = _appClass.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

Worksheet obj = (Worksheet)wbk.ActiveSheet;

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = wbk.Worksheets;

Range r = (Range)obj.Cells;

//_appClass.Workbooks.Add(obj);

//load data from datagrid to worksheet
try
{
//Load the Headers and make them BOLD
for (int i = 0; i < ((DataGridView)this.dgOutput).Columns.Count; i++)
{
r[1, i + 1] = ((DataGridView)this.dgOutput).Columns[i].HeaderText.ToString();
((Range)r.Cells[1, i + 1]).Font.Bold = true;
}

//Load the data from the datagrid to the new spreadsheet
for (int i = 1; i < ((DataGridView)this.dgOutput).Rows.Count - 1; i++)
{
for (int j = 0; j < ((DataGridView)this.dgOutput).Columns.Count; j++)
{

r[i + 1, j + 1] = ((DataGridView)this.dgOutput).Rows[i].Cells[j].Value.ToString();

//Do the colors
if (((DataGridView)this.dgOutput).Rows[i].Cells[j].Value.ToString() == "Update OK")
{
((Range)r.Cells[i + 1, 1]).Font.Color = Color.Green.G;
}
}
}
}
catch (Exception EX) //user closes the Excel file while it is loading data from the datagrid
{
MessageBox.Show(EX.Message + "The Excel file was closed while loading data from the datagrid.",
"Error : Loading file!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

//add worksheet to workbook
_appClass.Workbooks.Add(sheets);
}

}
}
}

Mvelo Walaza
Developer
Telkom SA

Re: Dynamically add TabPages to my form depending on the number of worksheets in a spreadsheet

lsconyer9-Apr-08 6:09

lsconyer

9-Apr-08 6:09

Just about your whole code needs to be rewritten. I think you're making this harder on yourself than you needed to. The reason nothing is happening is because tbControl is a new tabcontrol with no tabpages. Instead of instantiating a new tabcontrol, you need to loop through the tabpages of the original tabcontrol that you were working with at the beginning.

To save yourself some trouble, let the designer work for you. Simply drag and drop a tabcontrol on the page. Now, it can be accessed globally. Also, ditch that global datagridview named dgOutput. You don't need it and it shouldn't be being used. It shouldn't be being used because you create a new datagridview for each tab page that's created dynamically.

Once you get all that rewired, take a look at your code. Think about exactly what's going to happen. Right now, if that foreach loop would execute, you would only be getting data from this.dgOutput but your looping through the rows of dgv which is the datagridview on each tabpage. You want to loop through the rows of dgv to get values. You want to loop through the columns of dgv to load the headers. Then, after the headers and values are in the worksheet, you want to add it to the workbook.

Set some breakpoints within your loops and the rest of your code to debug. It'll save you alot of time and you'll learn a whole lot quicker than somebody helping you.

Lester
http://www.lestersconyers.com

How to write a text in a video files

Smithakrishnan7-Apr-08 2:16

Smithakrishnan

7-Apr-08 2:16

Hiii,

How to write a text in a video files?I want to stamp time in the video files.Plsssssssss help me.

Thanks in advance
Smithakrishnan

Custom treeview woes

lsconyer7-Apr-08 2:14

lsconyer

7-Apr-08 2:14

Hello. I'm working with a custom treeview control for a project and everything works fine. However, I have a little problem. Whenever I add add the treeview to a form and click something else, I can no longer select it with the mouse unless I add a tab page to it. This is getting pretty annoying because I'm actually working with 3 custom treeviews and they all have this same problem. This makes it pretty difficult to be productive.

Any suggestions?

Lester
http://www.lestersconyers.com

gprs

sbk197-Apr-08 1:52

sbk19

7-Apr-08 1:52

Can ne1 tell me how to synchronize my database using gprs???m usin c# .net compact framework.

Re: gprs

Dave Kreskowiak7-Apr-08 4:24

Dave Kreskowiak

7-Apr-08 4:24

Who, on this earth, is "ne1" and why have you directed your question specifically at this person??

(There'a hint buried in this post, if it wasn't obvious)

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Re: gprs

Luc Pattyn7-Apr-08 4:40

Luc Pattyn

7-Apr-08 4:40

Dave Kreskowiak wrote:
Who, on this earth,

with the advent of wireless internet, ne1 could be newhere... Big Grin | :-D

Luc Pattyn [Forum Guidelines] [My Articles]

This month's tips:
- before you ask a question here, search CodeProject, then Google;
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get;
- use PRE tags to preserve formatting when showing multi-line code snippets.

Re: gprs

Dave Kreskowiak7-Apr-08 6:43

Dave Kreskowiak

7-Apr-08 6:43

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Last Visit: 31-Dec-99 18:00 Last Update: 21-May-24 21:49

Refresh

ᐊ Prev 1...7394 7395 7396 739773987399 7400 7401 7402 7403 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.