Databases
StackOverflow2013
Postsdbo
POAdd a Dynamic Total Row at the Bottom of Excel Spreadsheet Generated by VB.NET and SQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdd a Dynamic Total Row at the Bottom of Excel Spreadsheet Generated by VB.NET and SQL
    primarykey
    data
    text
    <p>I have an Excel Spreadsheet that I generated dynamically using SQL and VB.NET. The number of columns (2) will never change, however the number of records will change according to what data the user has specified. After the SQL has run and has filled the spreadsheet, I need to add a record at the bottom of the table that displays the total(int value) of the records in column 2. </p> <p>Something like:</p> <p>xlSheet.Range(bottom record, column1) = "Total" xlSheet.Range(bottom record, column2) = Sum of all above records in column 2</p> <p>I am using VS 2010 and Excel 2010.</p> <p>My tables resembles this:</p> <pre><code>*Group* *Refered Cases* 4H BOSS 0 4H BSG 0 4H SALES AND MKTG 0 ACCOUNTS RECEIVABLE 0 ASSET MANAGEMENT 0 AUDIT 0 BOSS 0 CORPORATE BSG 0 CUSTOMER SUPPORT 0 NETWORK ENGINEERING 0 PRODUCTION ENGINEERING 0 PRODUCTION OPERATIONS 0 SECURITY 0 SNFAL PRODUCT TEAM 0 VOICE SERVICES 0 XEROX 0 </code></pre> <p><strong>ANSWER:</strong></p> <p>Ok, I was able to use the following code to dynamically find the last cell of the first column and input "TOTAL" into the cell and also format it accordingly.</p> <pre><code>Dim lTotalRows As Long, lTotalCols As Long lTotalRows = xlSheet.UsedRange.Rows.Count lTotalCols = xlSheet.UsedRange.Columns.Count With xlSheet.Range("A" &amp; (lTotalRows + 1).ToString) 'Dynamically finds the last cell of the first column, and sets it equal to "Total" and formats the cell .Value = "TOTAL" .Interior.ColorIndex = 41 'Cell Background Changed to Black .Columns("A:B").EntireColumn.AutoFit() With .Font .ColorIndex = 2 'Cell Font Changed to White .Bold = True .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle End With End With </code></pre> <p>I was able to use the following code to set the last cell of the second column equal to the sum of all of all cells of the second column.</p> <pre><code>Dim totals as Integer For Each dr As DataRow In dt.Rows totals += dr.Item(1) Next xlSheet.Range("B" &amp; (lTotalRows + 1).ToString).Value = totals </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWill
    1. USJosh McKearin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload