xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="30"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<Button Content="Button" x:Name="btnApply" Grid.Row="0" VerticalAlignment="Top" Width="75" Click="Button_Click"/>
<DataGrid x:Name="dg" Grid.Row="1" ></DataGrid>
<Popup Name="popup1" HorizontalAlignment="Left" Height="90" Width="436" VerticalAlignment="Top" Placement="Center" >
<Border BorderBrush="Black" BorderThickness="2">
<Grid Background="White" >
<Grid.RowDefinitions>
<RowDefinition Height="35"/>
<RowDefinition Height="45"/>
</Grid.RowDefinitions>
<ProgressBar x:Name="pg" Grid.Row="0" HorizontalAlignment="Left" IsIndeterminate="False"
Height="25"
VerticalAlignment="Center" Width="450"
Foreground="Green"
Maximum="100" ForceCursor="False" />
<Button Content="Cancel" x:Name="btnCancel" Click="btnCancel_Click" Width="120" Height="35" Grid.Row="1" HorizontalAlignment="Center" VerticalAlignment="Center"/>
</Grid>
</Border>
</Popup>
<Button Content="Export" x:Name="btnExport" HorizontalAlignment="Left" Margin="85,8,0,0" VerticalAlignment="Top" Width="75" Click="Button_Click_1"/>
</Grid>
</Window>
public partial class MainWindow : Window
{
DataDR DrClass = new DataDR();
ProgressBar prbar = new ProgressBar();
private static string CONNECTION_STRING = "User Id=tpl_owner;Password=TPL_OWNER2;Data Source=(DESCRIPTION=" + "(ADDRESS=(PROTOCOL=TCP)(HOST=lntpld1.uk.db.com)(PORT=1801))" + "(CONNECT_DATA=(SID=LNTPLD1)));";
DateTime dt1 = DateTime.Now;
DataTable Dt = new DataTable();
//http://devtoolshed.com/content/c-download-file-progress-bar
public MainWindow()
{
InitializeComponent();
worker.DoWork += worker_DoWork;
worker.RunWorkerCompleted += worker_RunWorkerCompleted;
worker.ProgressChanged += worker_ProgressChanged;
worker.WorkerSupportsCancellation = true;
dg.ItemsSource = GetTable().AsDataView();
}
#region
private static readonly BackgroundWorker worker = new BackgroundWorker();
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
string btn = (string)e.Argument;
// here is logic
GetData(worker, btn);
if (worker.CancellationPending)
{
e.Cancel = true;
return;
}
}
private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
pg.Value = e.ProgressPercentage;
}
private void worker_RunWorkerCompleted(object sender,
RunWorkerCompletedEventArgs e)
{
DateTime dt2 = DateTime.Now;
TimeSpan sp = dt2.Subtract(dt1);
popup1.IsOpen = false;
}
private static void mouseWaitTimer_Tick(object sender, EventArgs e)
{
worker.WorkerReportsProgress = true;
if (progress == 0) progress = 100;
else progress = 0;
worker.ReportProgress(progress);
// Handle Single Click Actions
}
private static DispatcherTimer myClickWaitTimer =
new DispatcherTimer(
new TimeSpan(0, 0, 0, 1),
DispatcherPriority.Background, mouseWaitTimer_Tick,
Dispatcher.CurrentDispatcher);
static DataTable GetTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Sex", typeof(string));
table.Columns.Add("CreatedDate", typeof(string));
table.Columns.Add("City", typeof(string));
//
// Here we add five DataRows.
//
table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now, "Noida");
table.Rows.Add(50, "Nikhil Vats", "M", DateTime.Now, "Noida");
table.Rows.Add(10, "Heena Sharma", "F", DateTime.Now, "Delhi");
table.Rows.Add(21, "Nancy Sharma", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "Avinash", "M", DateTime.Now, "Delhi");
table.Rows.Add(25, "Devesh gupta", "M", DateTime.Now, "Delhi");
table.Rows.Add(50, "Nikhil gupta", "M", DateTime.Now, "Noida");
table.Rows.Add(10, "HS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(21, "VS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "RJ gupta", "M", DateTime.Now, "Delhi");
return table;
}
static int progress = 10;
#endregion
void GetData(BackgroundWorker worker,object sender)
{
try
{
//start Database call
//OracleConnection conn = new OracleConnection();
//conn.ConnectionString = CONNECTION_STRING;
//conn.Open();
//using (var cmd = new OracleCommand())
//{
// cmd.Connection = conn;
// DataSet ds = new DataSet();
// OracleDataAdapter oDa = new OracleDataAdapter();
// cmd.CommandType = CommandType.Text;
// oDa.SelectCommand = cmd;
// cmd.CommandText = "SELECT * FROM UNDERLYING WHERE ROWNUM between 0 and 5000";
// oDa = new OracleDataAdapter();
// oDa.SelectCommand = cmd;
// ds = new DataSet();
// oDa.Fill(ds, "dtMy");
// Dt = ds.Tables["dtMy"];
// //txtStatus.Text = "Data retrived from Database.";
// DataTable dt12 = new DataTable();
// var missing = from table1 in Dt.AsEnumerable()
// where !dt12.AsEnumerable().Contains(table1)
// select table1;
//}
string str = (sender).ToString();
if (str == "BtnApply")
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Stop();
}
if (str == "BtnExport")
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Stop();
if (DrClass.DV.Rows.Count > 0)
{
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Export(BackgroundWorker worker)
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Start();
if (DrClass.DV.Rows.Count > 0)
{
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
}
}
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
worker.CancelAsync();
popup1.IsOpen = false;
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
if (!worker.IsBusy)
{
popup1.IsOpen = true;
popup1.StaysOpen = true;
worker.WorkerReportsProgress = true;
worker.RunWorkerAsync("BtnApply");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
if (!worker.IsBusy)
{
popup1.IsOpen = true;
popup1.StaysOpen = true;
worker.WorkerReportsProgress = true;
worker.RunWorkerAsync("BtnExport");
}
}
}
}
{
DataDR DrClass = new DataDR();
ProgressBar prbar = new ProgressBar();
private static string CONNECTION_STRING = "User Id=tpl_owner;Password=TPL_OWNER2;Data Source=(DESCRIPTION=" + "(ADDRESS=(PROTOCOL=TCP)(HOST=lntpld1.uk.db.com)(PORT=1801))" + "(CONNECT_DATA=(SID=LNTPLD1)));";
DateTime dt1 = DateTime.Now;
DataTable Dt = new DataTable();
//http://devtoolshed.com/content/c-download-file-progress-bar
public MainWindow()
{
InitializeComponent();
worker.DoWork += worker_DoWork;
worker.RunWorkerCompleted += worker_RunWorkerCompleted;
worker.ProgressChanged += worker_ProgressChanged;
worker.WorkerSupportsCancellation = true;
dg.ItemsSource = GetTable().AsDataView();
}
#region
private static readonly BackgroundWorker worker = new BackgroundWorker();
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
string btn = (string)e.Argument;
// here is logic
GetData(worker, btn);
if (worker.CancellationPending)
{
e.Cancel = true;
return;
}
}
private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
pg.Value = e.ProgressPercentage;
}
private void worker_RunWorkerCompleted(object sender,
RunWorkerCompletedEventArgs e)
{
DateTime dt2 = DateTime.Now;
TimeSpan sp = dt2.Subtract(dt1);
popup1.IsOpen = false;
}
private static void mouseWaitTimer_Tick(object sender, EventArgs e)
{
worker.WorkerReportsProgress = true;
if (progress == 0) progress = 100;
else progress = 0;
worker.ReportProgress(progress);
// Handle Single Click Actions
}
private static DispatcherTimer myClickWaitTimer =
new DispatcherTimer(
new TimeSpan(0, 0, 0, 1),
DispatcherPriority.Background, mouseWaitTimer_Tick,
Dispatcher.CurrentDispatcher);
static DataTable GetTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Sex", typeof(string));
table.Columns.Add("CreatedDate", typeof(string));
table.Columns.Add("City", typeof(string));
//
// Here we add five DataRows.
//
table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now, "Noida");
table.Rows.Add(50, "Nikhil Vats", "M", DateTime.Now, "Noida");
table.Rows.Add(10, "Heena Sharma", "F", DateTime.Now, "Delhi");
table.Rows.Add(21, "Nancy Sharma", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "Avinash", "M", DateTime.Now, "Delhi");
table.Rows.Add(25, "Devesh gupta", "M", DateTime.Now, "Delhi");
table.Rows.Add(50, "Nikhil gupta", "M", DateTime.Now, "Noida");
table.Rows.Add(10, "HS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(21, "VS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "RJ gupta", "M", DateTime.Now, "Delhi");
return table;
}
static int progress = 10;
#endregion
void GetData(BackgroundWorker worker,object sender)
{
try
{
//start Database call
//OracleConnection conn = new OracleConnection();
//conn.ConnectionString = CONNECTION_STRING;
//conn.Open();
//using (var cmd = new OracleCommand())
//{
// cmd.Connection = conn;
// DataSet ds = new DataSet();
// OracleDataAdapter oDa = new OracleDataAdapter();
// cmd.CommandType = CommandType.Text;
// oDa.SelectCommand = cmd;
// cmd.CommandText = "SELECT * FROM UNDERLYING WHERE ROWNUM between 0 and 5000";
// oDa = new OracleDataAdapter();
// oDa.SelectCommand = cmd;
// ds = new DataSet();
// oDa.Fill(ds, "dtMy");
// Dt = ds.Tables["dtMy"];
// //txtStatus.Text = "Data retrived from Database.";
// DataTable dt12 = new DataTable();
// var missing = from table1 in Dt.AsEnumerable()
// where !dt12.AsEnumerable().Contains(table1)
// select table1;
//}
string str = (sender).ToString();
if (str == "BtnApply")
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Stop();
}
if (str == "BtnExport")
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Stop();
if (DrClass.DV.Rows.Count > 0)
{
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Export(BackgroundWorker worker)
{
myClickWaitTimer.Start();
DrClass.GetSBData();
myClickWaitTimer.Start();
if (DrClass.DV.Rows.Count > 0)
{
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
}
}
private void btnCancel_Click(object sender, RoutedEventArgs e)
{
worker.CancelAsync();
popup1.IsOpen = false;
}
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
if (!worker.IsBusy)
{
popup1.IsOpen = true;
popup1.StaysOpen = true;
worker.WorkerReportsProgress = true;
worker.RunWorkerAsync("BtnApply");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
if (!worker.IsBusy)
{
popup1.IsOpen = true;
popup1.StaysOpen = true;
worker.WorkerReportsProgress = true;
worker.RunWorkerAsync("BtnExport");
}
}
}
}
namespace Excel
{
public class ExcelUtlity
{
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, BackgroundWorker worker)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
//txtStatus.Text = "Report generating..";
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
int iProgressPercentage = 15;
// loop through each row and add values to our sheet
int rowcount = 2;
int dTotal = dataTable.Rows.Count;
foreach (DataRow datarow in dataTable.Rows)
{
// txtStatus.Text = "Report generating.." + rowcount +"/" + dataTable.Rows.Count;
rowcount += 1;
double dIndex = (double)(rowcount);
// run all background tasks here
double dProgressPercentage = (dIndex / dTotal);
iProgressPercentage = (int)(dProgressPercentage * 100);
worker.ReportProgress(iProgressPercentage);
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
// txtStatus.Text = "Report generation done..";
excel.Visible=true;
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
/// <summary>
/// FUNCTION FOR FORMATTING EXCEL CELLS
/// </summary>
/// <param name="range"></param>
/// <param name="HTMLcolorCode"></param>
/// <param name="fontColor"></param>
/// <param name="IsFontbool"></param>
public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
range.Font.Bold = IsFontbool;
}
}
}
}
No comments:
Post a Comment