7developers blog

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

SELECT TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

public DataGrid CloneDataGrid(DataGrid s)
{
DataGrid dg = new DataGrid();

dg.AccessKey = s.AccessKey; 
dg.AllowCustomPaging = s.AllowCustomPaging; 
dg.AllowPaging = s.AllowPaging; 
dg.AllowSorting = s.AllowSorting; 
dg.AlternatingItemStyle.CopyFrom(s.AlternatingItemStyle); 
dg.CopyBaseAttributes(s); 
dg.AutoGenerateColumns = s.AutoGenerateColumns; 
dg.BackColor = s.BackColor; 
dg.BackImageUrl = s.BackImageUrl; 
dg.BorderColor = s.BorderColor; 
dg.BorderStyle = s.BorderStyle; 
dg.BorderWidth = s.BorderWidth; 
dg.CellPadding = s.CellPadding; 
dg.CellSpacing = s.CellSpacing; 
dg.CssClass = s.CssClass; 
dg.Font.MergeWith(s.Font); 
dg.FooterStyle.CopyFrom(s.FooterStyle); 
dg.ForeColor = s.ForeColor; 
dg.GridLines = s.GridLines; 
dg.HeaderStyle.CopyFrom(s.HeaderStyle); 
dg.ItemStyle.CopyFrom(s.ItemStyle); 
dg.PagerStyle.CopyFrom(s.PagerStyle); 
dg.ShowFooter = s.ShowFooter; 
dg.ShowHeader = s.ShowHeader; 
dg.ToolTip = s.ToolTip;
dg.Width = s.Width;

foreach (DataGridColumn c in s.Columns)
{
dg.Columns.Add(c);
}

return dg;
}

// C# ExcelWriter class v1.1
// by Serhiy Perevoznyk, 2008-2009

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace XLSExportDemo
{
///

/// Produces Excel file without using Excel
///

public class ExcelWriter
{
private Stream stream;
private BinaryWriter writer;

private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
private ushort[] clEnd = { 0x0A, 00 };


private void WriteUshortArray(ushort[] value)
{
for (int i = 0; i < value.Length; i++)
writer.Write(value[i]);
}

///

/// Initializes a new instance of the class.
///

/// 
public ExcelWriter(Stream stream)
{
this.stream = stream;
writer = new BinaryWriter(stream);
}

///

/// Writes the text cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, string value)
{
ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
int iLen = value.Length;
byte[] plainText = Encoding.ASCII.GetBytes(value);
clData[1] = (ushort)(8 + iLen);
clData[2] = (ushort)row;
clData[3] = (ushort)col;
clData[5] = (ushort)iLen;
WriteUshortArray(clData);
writer.Write(plainText);
}

///

/// Writes the date format.
///

/// 
public void WriteFormat(string value)
{
ushort[] clData = { 0x001E, 0 };
byte[] plainText = Encoding.ASCII.GetBytes(value);
int iLen = plainText.Length;
clData[1] = (ushort)(1 + iLen);
WriteUshortArray(clData);
writer.Write((byte)iLen);
writer.Write(plainText);
}

///

/// Writes the integer cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, int value)
{
ushort[] clData = { 0x027E, 10, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
int iValue = (value << 2) | 2;
writer.Write(iValue);
}

///

/// Writes the cell.
///

/// 
/// 
/// 
/// 
public void WriteCell(int row, int col, DateTime value, int formatIndex)
{
DateTime baseDate = new DateTime(1899, 12, 31);
TimeSpan ts = value - baseDate;

ushort days = (ushort)(ts.Days + 1);
ushort[] clData = { 0x0002, 09, 0, 0};
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
writer.Write((byte)0x0);
byte indexValue = (byte)(formatIndex & 0x3F);
writer.Write((byte)indexValue);
writer.Write((byte)0x0);
writer.Write(days);
}

///

/// Writes the double cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, double value)
{
ushort[] clData = { 0x0203, 14, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
writer.Write(value);
}

///

/// Writes the empty cell.
///

/// 
/// 
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}

///

/// Must be called once for creating XLS file header
///

public void BeginWrite()
{
WriteUshortArray(clBegin);
}

///

/// Ends the writing operation, but do not close the stream
///

public void EndWrite()
{
WriteUshortArray(clEnd);
writer.Flush();
}
}
}

//############################ using it ################################
// This sample demonstrates how to create a file using the Microsoft Excel 
// Binary Interchange File Format (BIFF). 
// If this program works, it was written by Serhiy Perevoznyk.
// If not, I don't know who wrote it.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace XLSExportDemo
{
class Program
{
static void Main(string[] args)
{
FileStream stream = new FileStream("demo.xls", FileMode.OpenOrCreate);
ExcelWriter writer = new ExcelWriter(stream);
writer.BeginWrite();
writer.WriteFormat(@"dd\-mm\-yyyy"); //index 0
writer.WriteFormat(@"dd/mm/yyyy");
writer.WriteCell(0, 0, "ExcelWriter Demo");
writer.WriteCell(1, 0, "int");
writer.WriteCell(1, 1, 10);
writer.WriteCell(2, 0, "double");
writer.WriteCell(2, 1, 1.5);
writer.WriteCell(3, 0, "date");
writer.WriteCell(3, 1, DateTime.Now, 1);
writer.EndWrite();
stream.Close();
}
}
}