Export Dynamic Pivot Table To Excel
Feb 26, 2015
I know that this is an Excel question, but I guess it is much more likely that an SQL person using dynamic pivot tables had stepped on this, rather than any advanced Excel user.
I am exporting a dynamic pivot table to Excel through a Stored Procedure. If the Stored Procedure that executes the dynamic pivot table returns 7 columns in one run, and 4 columns in the following update, then I have 3 orphaned columns that are still displayed in the spreadsheet. There isn't any content related to them, but the empty columns with their headers are bothering enough.
I've been trying to play with the data connection properties, but nothing deletes unused columns from former data executions.
View 1 Replies
ADVERTISEMENT
Jul 8, 2015
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies
View Related
Sep 11, 2015
We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies
View Related
Jan 17, 2008
hi need help
how to add to pivot table the day name dynamic for all the month(but i need a short name) like instead Sunday =sun, monday=mon
1-sun 2-mon 3-Tue
+ how can i add the day name in my language
like instead Sunday ="ר×?", monday="×©× "
03/2008
empid
1-st
2-sun
3-mon
4-Tue
5 -wen
6-Thur
7 -fr
8-st
9 -mon
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
11111
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @Numdays > 28
BEGIN
SELECT p.ID,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END
TNX
View 2 Replies
View Related
Feb 15, 2008
I have the following Pivot Table:
Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable
What I'm wondering is if it's possible to have the columns change dynamically. For example:
If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show
EquipmentID ProcessorType
23 P4M
Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following
EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17
So in essence a column will be added based on the equipmentID. Thanks in advanced.
View 4 Replies
View Related
May 23, 2014
I have to export the records of a table in a .txt file but in a strange way (in a column). For example I have this table with 3 fields:
create table prova
(cod varchar(1),
des1 varchar(2),
des2 Varchar (2)
)
insert into prova values (1, 'A1', 'A2')
insert into prova values (2, 'B1', 'B2')
I need to have the output in this way:
1 cod 1
1 des1 A1
1 des2 A2
8 S
1 cod 2
1 des1 B1
1 des2 B2
8 S
So, I have to put '1' at the beginning of every field and '8 S' at the end of every record.
View 9 Replies
View Related
Aug 19, 2015
I have some data in Excel - sheet1 which would be static.
Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.
Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.
How to merge above SQL data with existing static data?
(SQL Server 2012)
View 6 Replies
View Related
May 19, 2015
How to pass dynamic values in xml path query?
WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI Â
INNER JOIN TERMINALGROUP TGÂ ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TLÂ ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
View 7 Replies
View Related
Nov 14, 2007
I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.
CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)
INSERT INTO #owner VALUES ('ME');
INSERT INTO #owner VALUES ('Other');
CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)
INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');
CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)
INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');
DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property
| NameId | Name |
| 1 | ME|
| 2 | other |
| TypeId | Name |
| 1 | Home |
| 2 | Car |
| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |
Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:
[Owner | Home | Car ]
[ME | Blue | Black ]
[Other| Red | Black ]
The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.
View 9 Replies
View Related
Mar 26, 2014
I have a query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)
[Code] ....
and so on.
I've tried putting my group by everywhere but it's not working. I'm missing something but what?
View 9 Replies
View Related
Jul 2, 2014
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
View 6 Replies
View Related
May 29, 2008
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
KenS
Ken
View 1 Replies
View Related
May 11, 2008
Hello,
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID
Name
Location
Start
End
1
Joe
NY
2000
2001
1
Joe
CA
2002
2004
1
Joe
MA
2005
2008
2
Sue
NJ
2003
2004
2
Sue
FL
2004
2008
3
Bob
CA
1999
2000
3
Bob
WA
2001
2004
3
Bob
OR
2005
2006
3
Bob
MI
2007
2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID
Name
Location1
Start1
End1
Location2
Start2
End2
Location3
Start3
End3
Location4
Start4
End4
1
Joe
NY
2000
2001
CA
2002
2004
MA
2005
2008
2
Sue
NJ
2003
2004
FL
2004
2008
3
Bob
CA
1999
2000
WA
2001
2004
OR
2005
2006
MI
2007
2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
Thank you.
-Gumbatman
View 11 Replies
View Related
Apr 16, 2008
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
View 31 Replies
View Related
Jan 12, 2006
Hi,
I have several pivot tables in Excel that access data to a SQL 2000. We install SQL 2005, we change the ODBC from the 2000 server to the 2005 server. Now when we try to run the pivot tables I've got the following message:
"User 'public' does not have permission to run DBCC TRACEON"
Any idea on how to fix this problem?
Thanks,
Arty
View 25 Replies
View Related
Oct 10, 2007
Hi,
Im using Sql server 2000&MS Excel 2003. i've recently moved to Sql server,i was using MS Access.
i've created an odbc connectivity and a View . i've created pivot tables in excel. my intension was once i upload the data into sql server and when i refresh the view/excel pivot, the pivot table should updated automatically with the newly entered data( i used to do the same process in Access).
pls advise me how to update the Excel pivot table.
Rgds
RPU
View 1 Replies
View Related
Jan 23, 2014
I am trying to pivot table DYNAMICALLY but couldn't get the desired result .
Here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
[Code] .....
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
View 1 Replies
View Related
Jul 23, 2005
I am facing problem like;We generate reports thru Excel Pivot table. The data is coming from salescube. Let?s assume that there are 2 products A and B. The user wants tofilter for each product and view ?stock in hand? and ?total sales? forparticular month (as two columns).From the Pivot table filtering, when we choose product A and month ?Jan?,it is showing values for ?stock in hand? and ?total sales? columns for thatmonth.But when we choose product B and month ?Jan?, it is only showing ?stock inhand? value and the ?total sales? column was suppressed/unseen. We knowthat there is no sales transaction for ?Jan? month. But we still want toshow the column title even if there are no records in the database / cube.I am not using any scripting, this pivot table was generated just thruPivot table wizard.Any way to achieve this, please advise me.Advanced thanks for u r help.--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Feb 3, 2015
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
View 3 Replies
View Related
May 29, 2015
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
[Code] ....
View 7 Replies
View Related
Oct 16, 2002
The excel pivot table is very powerful for the user, with dragging and dropping fields at will on the web page, but I can only get it work when run from the web server directly. The data is in SS2000 300,000 rows, get external data in Excel, then publish pivot table as HTM with interactivity.
It seems to look for c:myodbc.dsn in the C: drive of the pc the user is on rather than the server, and I cannot use IP Addressing to relate to the dsn file.
any ideas ?
TIA
Neil.
View 1 Replies
View Related
Feb 5, 2007
Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.
Thanks
View 1 Replies
View Related
Oct 5, 2005
Wonder if you could help me ?
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
Thanks very much
Mark
View 3 Replies
View Related
Nov 18, 2015
have just implement Microsoft Dynamics NAV. and I'm trying to learn how to use this Excellent tool powerpivot add on, But when I'm trying to connect it to any SQL table that support Dynamics NAV I have an error message while downloading the table :"do not have enough memory or I should use Excel 64 bits instead 32 bits".when I check CPU memory Utilization we are just using less tha 20%. I'm working on a remote server and the using Excel 2010.
View 3 Replies
View Related
Mar 13, 2008
I am trying to create a pivot table in excel using Activex scripting but i am facing some problems in doing so, below is the code that I have written
The line of the code where i am encountering error is highlighted in RED
Set ActiveWorkBook = oExcel.Workbooks.Open (sFile)
Set oSheet = ActiveWorkBook.WorkSheets(1)
oSheet.Range("A1:T1").Font.Bold = True
oSheet.Cells.Select
oSheet.Cells.EntireColumn.AutoFit
oSheet.Range("A1").Select
oSheet.Name = "Data_" & strName
Set oSheet = ActiveWorkBook.WorkSheets(2)
oSheet.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!A$:T$").CreatePivotTable TableDestination:=Range("A3"), TableName :="PivotTable2"
oSheet.PivotTables("PivotTable2").SmallGrid = False
With oSheet.PivotTables("PivotTable2").PivotFields("Supervisor")
.Orientation = xlRowField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Operator")
.Orientation = xlRowField
.Position = 2
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Tag")
.Orientation = xlDataField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Dept")
.Orientation = xlPageField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Branch")
.Orientation = xlPageField
.Position = 2
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Location")
.Orientation = xlPageField
.Position = 3
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Type")
.Orientation = xlPageField
.Position = 4
End With
oSheet.PivotTables("PivotTable2").RefreshTable
ActiveWorkBook.WorkSheets(3).Delete
'ActiveWorkBook.WorkSheets(2).Delete
oExcel.ActiveWorkBook.Save
'With ActiveWorkBook
' .SaveAs sFile
' .save
'End With
oExcel.quit
View 3 Replies
View Related
Sep 25, 2007
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
View 3 Replies
View Related
Aug 1, 2007
how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3.
I'm fairly new to using SQL server, and not experienced at all with Transact-SQL.
View 12 Replies
View Related
Jun 6, 2008
hi ,
Can any body help me export data from sql database to excel? I am using datagrid method but it is not transferring complete table's data at a time through LAN .i.e. it works on local host ,but not working if I accessing this project from other computer.
public class gridviewexport
{
public gridviewexport()
{
//
// TODO: Add constructor logic here
//
}
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
gridviewexport.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
gridviewexport.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
gridviewexport.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
gridviewexport.PrepareControlForExport(current);
}
}
}
}
View 4 Replies
View Related
Jan 27, 2015
while exporting database table to ms excel, i m getting yellow marked icon on selected columns. Why is it so?
View 3 Replies
View Related
Dec 18, 2005
Hi All,
I am new to SQL program. I did little management for SQL 2000 before.
I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?
Thanks all for your help.
Perry Yang
View 11 Replies
View Related
Jan 12, 2007
Hi,
I was wondering if there is a way to export just the table names of all the tables in a sql database to an excel spreadsheet. Can I also see the table structure, like which are the parent tables and which are the child tables? Please let me know. I'm using SQL server 2005.
Thanks.
View 5 Replies
View Related
Feb 7, 2008
Hello,
I am trying to export bunch of tables to excel files, and some table columns contain leading 0s. I tried to use bcp and found that it's not possible to directly export to excel file with leading 0s. I don't want to export to txt or csv file format since the client needs to update excel file as well.
We now think to use SSIS to do the transformation, but got problem. I used OLF DB as source, Excel as destination. I directly connect OLF DB to Excel destination, but always got error message which said unicode cannot convert to non-unicode. Can anyone explain how I get this error since we don't use unicode in the tables, and I make sure the table excel created used varchar and smallint?
Thanks in advance.
View 7 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
