public MemoryGraph(MemVisualizer.MainWindow mainViewerWindow, MemManager.Log.Log log)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
mGraphVScrollBar.Enabled = false;
mGraphHScrollBar.Enabled = false;
mTextComp1.Text = mTextComp2.Text = "";
mComparePressed = mCompareProcessed = false;
//
// TODO: Add any constructor code after InitializeComponent call
//
mMainViewWindow = mainViewerWindow;
mLog = log;
CreateMemoryGraph(log);
mHighlightIndexStart = null;
mHighlightIndexEnd = null;
for (int i = 1; i <= 10; ++i)
{
mZoomComboControl.Items.Add("Zoom " + i.ToString() + "x");
}
mZoomComboControl.SelectedIndex = 0;
}
/// <summary>
/// Gets symbol from current editor selection and decides if it's applicable
/// </summary>
void GetSymbol()
{
var originalSelection = new Range(E, E.Selection.Start, E.Selection.End);
S = E.SelectWord();
E.Selection.Normalize();
var point = E.PlaceToPoint(E.Selection.Start);
var target = E.PointToScreen(point);
target.X -= E.Left;
target.Y -= Height - ClientRectangle.Height - E.Left + 3;
Location = target;
Symbol.Text = S;
var style = E.Selection.Chars.First().style;
var notApplicableForStyles = new StyleIndex[] {
EditorSyntax.StyleMap.Command,
EditorSyntax.StyleMap.Comment,
EditorSyntax.StyleMap.Keyword0,
EditorSyntax.StyleMap.Keyword1,
EditorSyntax.StyleMap.Keyword2,
EditorSyntax.StyleMap.Keyword3,
EditorSyntax.StyleMap.Keyword4,
EditorSyntax.StyleMap.Number
};
var applicable = !notApplicableForStyles.Contains(style);
if (applicable) ShowDialog();
else { E.Selection = originalSelection; Dispose(); }
}
//private class ERWhenCalled
//{
// public List<double> EquityRealized { get; set; }
// //public double[] EV { get; set; }
// public ERWhenCalled()
// {
// EquityRealized = new List<double>();
// }
// public void Add(double netWon, double PFRsize, double rake)
// {
// if (netWon > 0)
// {
// //Replace the rake when we win, because we want a true ER across stakes
// netWon += rake;
// }
// double ER = (netWon + PFRsize) / (PFRsize * 2);
// EquityRealized.Add(ER);
// }
//}
static void Main(string[] args)
{
ERWhenPFRCalled report = new ERWhenPFRCalled();
Range rangeFilter = new Range("**o");
foreach (StartingHandData<ERWhenPFRCalledData> hand in report.Result)
{
if (rangeFilter.Contains(hand.Combos[0]))
{
double mean = hand.Data.EquityRealized.ToArray().Mean();
double variance = hand.Data.EquityRealized.ToArray().Variance();
double stdDev = hand.Data.EquityRealized.ToArray().StandardDeviation();
double critValue = 1.96;
double stdErr = stdDev / Math.Sqrt(hand.Data.EquityRealized.Count);
double nonNormalCI = critValue * stdErr;
Console.Write(hand.Name + " u = " + mean.ToString("N2") + " Non-Normal: +/- " + nonNormalCI.ToString("N2"));
//Console.Write(" BCI +/- " + hand.Data.ER_CI().ToString("N2"));
Console.Write(" var = " + variance.ToString("N2"));
Console.Write(" stdDev = " + stdDev.ToString("N2"));
Console.WriteLine();
}
}
report.Result.SaveCSV(@"E:\Documents\Poker\Reports\ERWhenPFRCalled" + DateTime.Now.ToString("MM-dd-yyyy_HH-mm-ss") + ".csv");
//TODO Smooth the equities by using some sort of best fit curve
Console.ReadLine();
}
private void LoadFile(String filename)
{
sourceCodeBox.Text = File.ReadAllText(filename);
Range all = new Range(sourceCodeBox);
all.Start = new Place(0, 0);
all.End = new Place(sourceCodeBox.Lines.Last().Length, sourceCodeBox.LinesCount);
changeStyles(new Range(sourceCodeBox));
}
Range TrimRange(Range range, TrimDialog.Result result)
{
var index = range.Start;
var length = range.Length;
Data.Trim(ref index, ref length, result.TrimChars, result.Start, result.End);
if ((index == range.Start) && (length == range.Length))
return range;
return Range.FromIndex(index, length);
}
public DataSet importwIntOp(int CntSheet)
{
try
{
// get WorkSheet object
OSheet = (Microsoft.Office.Interop.Excel.Worksheet)OWB.Sheets[1];
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataSet ds = new DataSet();
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = OSheet.UsedRange.Cells.Columns.Count;
int iValue = OSheet.UsedRange.Cells.Rows.Count;
// get data columns
for (int j = 1; j <= jValue; j++)
{
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
//string colString = sb.ToString().Trim();
//string[] colArray = colString.Split(':');
// get data in cell
for (int i = 1; i <= iValue; i++)
{
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
{
ORng = (Microsoft.Office.Interop.Excel.Range)OSheet.Cells[i, j];
string strValue = ORng.Text.ToString();
dr["column" + j] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
Console.WriteLine("Debug");
}
return ds;
}
catch (Exception ex)
{
//Label1.Text = "Error: ";
//Label1.Text += ex.Message.ToString();
Console.WriteLine(ex);
return null;
}
finally
{
Console.WriteLine("debug");
//Dispose();
}
}
public void changeStyles(Range changedRange)
{
changedRange.ClearStyle();
changedRange.SetStyle(styles.KeywordStyle, styles.regexKeywords);
changedRange.SetStyle(styles.RegisterStyle, styles.regexRegisters);
changedRange.SetStyle(styles.LiteralStyle, styles.regexLiterals);
changedRange.SetStyle(styles.LabelStyle, styles.regexLabels);
changedRange.SetStyle(styles.HexPrefix, styles.regexHexPrefixes);
changedRange.SetStyle(styles.CommentStyle, styles.regexComments);
changedRange.SetStyle(styles.MacroStyle, styles.regexMacros);
}
void ApplyHighlight(Range range)
{
range.SetFoldingMarkers("{", "}");
range.SetFoldingMarkers(@"/\*", @"\*/");
range.SetStyle(TextStyles["Comments"], @"//.*$", RegexOptions.Multiline);
range.SetStyle(TextStyles["Strings"], @"""""|@""""|''|@"".*?""|(?<!@)(?<range>"".*?[^\\]"")|'.*?[^\\]'");
range.SetStyle(TextStyles["Digits"], @"\b\d+[\.]?\d*([eE]\-?\d+)?[lLdDfF]?\b|\b0x[a-fA-F\d]+\b");
range.SetStyle(TextStyles["Params"], @"^\s*(?<range>\[.+?\])\s*$", RegexOptions.Multiline);
range.SetStyle(TextStyles["Operators"], @"\b(public|private|stock|function|native)\s+(?<range>\w+?)\b");
range.SetStyle(TextStyles["Operators"], @"\b(assert|break|case|countine|default|do|else|exit|for|goto|if|return|sleep|state|switch|while|defined|sizeof|state|tagof|const|forward|native|new|operator|public|static|stock|private|function)\b|#\w+\s*[A-Za-z0-9.,=<>()_-]+");
}
IEnumerable<Range> FindRepetitions(Range inputRange)
{
var startLine = Data.GetOffsetLine(inputRange.Start);
var endLine = Data.GetOffsetLine(inputRange.End);
var lineRanges = Enumerable.Range(startLine, endLine - startLine + 1).Select(line => new Range(Math.Max(inputRange.Start, Data.GetOffset(line, 0)), Math.Min(inputRange.End, Data.GetOffset(line, Data.GetLineLength(line))))).ToList();
if ((lineRanges.Count >= 2) && (!lineRanges[lineRanges.Count - 1].HasSelection))
lineRanges.RemoveAt(lineRanges.Count - 1);
var lineStrs = lineRanges.Select(range => GetString(range)).ToList();
var lines = Enumerable.Range(1, lineStrs.Count).MaxBy(x => GetRepetitionScore(lineStrs, x));
for (var ctr = 0; ctr < lineRanges.Count; ctr += lines)
yield return new Range(lineRanges[ctr + lines - 1].End, lineRanges[ctr].Start);
}
private void HTMLSyntaxHighlight(Range range)
{
//clear style of changed range
range.ClearStyle(BlueStyle, MaroonStyle, RedStyle);
//tag brackets highlighting
range.SetStyle(BlueStyle, @"<|/>|</|>");
//tag name
range.SetStyle(MaroonStyle, @"<(?<range>[!\w]+)");
//end of tag
range.SetStyle(MaroonStyle, @"</(?<range>\w+)>");
//attributes
range.SetStyle(RedStyle, @"(?<range>\S+?)='[^']*'|(?<range>\S+)=""[^""]*""|(?<range>\S+)=\S+");
//attribute values
range.SetStyle(BlueStyle, @"\S+?=(?<range>'[^']*')|\S+=(?<range>""[^""]*"")|\S+=(?<range>\S+)");
}
private void HighlightVisibleRange()
{
//expand visible range (+- margin)
var startLine = Math.Max(0, fctb.VisibleRange.Start.iLine - margin);
var endLine = Math.Min(fctb.LinesCount - 1, fctb.VisibleRange.End.iLine + margin);
var range = new Range(fctb, 0, startLine, 0, endLine);
//clear folding markers
range.ClearFoldingMarkers();
//set markers for folding
range.SetFoldingMarkers(@"N\d\d00", @"N\d\d99");
//
range.ClearStyle(StyleIndex.All);
range.SetStyle(fctb.SyntaxHighlighter.BlueStyle, @"N\d+");
range.SetStyle(fctb.SyntaxHighlighter.RedStyle, @"[+\-]?[\d\.]+\d+");
}
IEnumerable<Range> SelectSplit(Range range, SelectSplitDialog.Result result)
{
var str = GetString(range);
var start = 0;
foreach (Match match in result.Regex.Matches(str))
{
if (match.Index != start)
yield return Range.FromIndex(range.Start + start, match.Index - start);
if (result.IncludeResults)
yield return Range.FromIndex(range.Start + match.Index, match.Length);
start = match.Index + match.Length;
}
if (str.Length != start)
yield return Range.FromIndex(range.Start + start, str.Length - start);
}
public FractalImage(IFractal fractal)
{
if (fractal == null) throw new ArgumentNullException("fractal");
InitializeComponent();
_fractal = fractal;
_defaultRange = _fractal.DefaultRange;
_maxTries = _fractal.MaxTries;
this.SetStyle(
ControlStyles.AllPaintingInWmPaint |
ControlStyles.UserPaint |
ControlStyles.DoubleBuffer |
ControlStyles.ResizeRedraw,
true);
}
Range GetPathRange(GetPathType type, Range range)
{
var path = GetString(range);
var dirLength = Math.Max(0, path.LastIndexOf('\\'));
if ((path.StartsWith(@"\\")) && (dirLength == 1))
dirLength = 0;
var dirTotal = dirLength == 0 ? 0 : dirLength + 1;
var extLen = Path.GetExtension(path).Length;
switch (type)
{
case GetPathType.FileName: return new Range(range.End, range.Start + dirTotal);
case GetPathType.FileNameWoExtension: return new Range(range.End - extLen, range.Start + dirTotal);
case GetPathType.Directory: return new Range(range.Start + dirLength, range.Start);
case GetPathType.Extension: return new Range(range.End, range.End - extLen);
default: throw new ArgumentException();
}
}
public void enforceSyntax()
{
//Syntax Enforcing
Range selection = new Range(sourceCodeBox, sourceCodeBox.Selection.Start, sourceCodeBox.Selection.End);
var scrollInfo = sourceCodeBox.VerticalScroll.Value;
string syn = sourceCodeBox.Text;
#region Spaces after commas
foreach (Match m in Regex.Matches(sourceCodeBox.Text, ",([\\w\\S]+)"))
{
//MessageBox.Show(syn);
if (Regex.IsMatch(sourceCodeBox.Lines[selection.Start.iLine], ",([\\w\\S]+)", RegexOptions.None))
{
selection = new Range(sourceCodeBox, new Place(sourceCodeBox.Selection.Start.iChar + 1, sourceCodeBox.Selection.Start.iLine), new Place(sourceCodeBox.Selection.End.iChar + 1, sourceCodeBox.Selection.End.iLine));
}
syn = syn.Substring(0, m.Index) + ", " + m.Groups[0].Value.Substring(1) + syn.Substring(m.Index + m.Length);
//MessageBox.Show(syn);
}
#endregion
#region Spaces after Keywords
foreach (Match m in Regex.Matches(sourceCodeBox.Text, CodeStyles.regexBasicKeywords + "([\\w\\S])"))
{
MessageBox.Show(syn);
if (Regex.IsMatch(sourceCodeBox.Lines[selection.Start.iLine], CodeStyles.regexBasicKeywords + "([\\w\\S])", RegexOptions.None))
{
selection = new Range(sourceCodeBox, new Place(sourceCodeBox.Selection.Start.iChar + 1, sourceCodeBox.Selection.Start.iLine), new Place(sourceCodeBox.Selection.End.iChar + 1, sourceCodeBox.Selection.End.iLine));
}
syn = syn.Substring(0, m.Index) + m.Groups[0].Value.Substring(0, m.Groups[0].Value.Length - 1) + " " + m.Groups[2].Value + syn.Substring(m.Index + m.Length);
MessageBox.Show(syn);
}
#endregion
if (syn != sourceCodeBox.Text)
{
sourceCodeBox.Text = syn;
sourceCodeBox.Selection.Start = selection.Start;// = selection;
sourceCodeBox.VerticalScroll.Value = scrollInfo;
}
}
public Form1()
{
InitializeComponent();
Range ra = new Range(1, 2);
sortChart.Series.Add("12");
Stream stream = File.Open("test.agr", FileMode.Open);
var reader = new AgrFormatReader();
var list = reader.Read(stream);
foreach (var item in list)
{
Console.WriteLine("{0}", item.PatientName);
}
//ra.
}
IEnumerable<Range> SelectRectangle(Range range)
{
var startLine = Data.GetOffsetLine(range.Start);
var endLine = Data.GetOffsetLine(range.End);
if (startLine == endLine)
{
yield return range;
yield break;
}
var startIndex = Data.GetOffsetIndex(range.Start, startLine);
var endIndex = Data.GetOffsetIndex(range.End, endLine);
for (var line = startLine; line <= endLine; ++line)
{
var length = Data.GetLineLength(line);
var lineStartOffset = Data.GetOffset(line, Math.Min(length, startIndex));
var lineEndOffset = Data.GetOffset(line, Math.Min(length, endIndex));
yield return new Range(lineEndOffset, lineStartOffset);
}
}
public void DisposeExcelObject()
{
try
{
if (workbook != null)
workbook.Close(true);
if (application != null)
{
if (application.Workbooks != null)
application.Workbooks.Close();
application.Quit();
}
if (range != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
if (workbook != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
if (workbooks != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
if (application != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
range = null;
workbook = null;
workbooks = null;
sheets = null;
worksheet = null;
application = null;
Process[] pProcess;
pProcess = Process.GetProcessesByName("Excel");
foreach (var process in pProcess)
{
process.Kill();
}
}
catch (Exception ex)
{
}
}
public override void Draw(Graphics gr, Point position, Range range)
{
foreach (Place p in range)
{
int time = (int)(DateTime.Now.TimeOfDay.TotalMilliseconds/2);
int angle = (int)(time % 360L);
int angle2 = (int)((time - (p.iChar - range.Start.iChar)*20) % 360L)*2;
int x = position.X + (p.iChar - range.Start.iChar) * range.tb.CharWidth;
Range r = range.tb.GetRange(p, new Place(p.iChar+1, p.iLine));
Point point = new Point(x, position.Y + (int)(5 + 5 * Math.Sin(Math.PI * angle2 / 180)));
gr.ResetTransform();
gr.TranslateTransform(point.X + range.tb.CharWidth / 2, point.Y +range.tb.CharHeight / 2);
gr.RotateTransform(angle);
gr.ScaleTransform(0.8f, 0.8f);
gr.TranslateTransform(- range.tb.CharWidth / 2, -range.tb.CharHeight / 2);
base.Draw(gr, new Point(0, 0), r);
}
gr.ResetTransform();
}
private void tm_Tick(object sender, EventArgs e)
{
tm.Stop();
//get place under mouse
var place = fctb.PointToPlace(lastMouseCoord);
//check distance
var p = fctb.PlaceToPoint(place);
if (Math.Abs(p.X - lastMouseCoord.X) > fctb.CharWidth * 2 ||
Math.Abs(p.Y - lastMouseCoord.Y) > fctb.CharHeight * 2)
return;
//get word under mouse
var r = new Range(fctb, place, place);
string hoverWord = r.GetFragment("[a-zA-Z]").Text;
if (hoverWord == "")
return;
//show tooltip
string text = "Help for " + hoverWord;
tt.ToolTipTitle = hoverWord;
tt.SetToolTip(fctb, text);
tt.Show(text, fctb, new Point(lastMouseCoord.X, lastMouseCoord.Y + fctb.CharHeight));
}
/// <summary>
/// Get a data table from an Excel range object
/// </summary>
/// <param name="pSelection">Excel range object</param>
/// <returns>A .NET DataTable</returns>
public static System.Data.DataTable GetTableFromExcelRange(Range pSelection)
{
Object[,] mSelection = pSelection.Cells.Value;
var mDataTable = new System.Data.DataTable();
for (int i = 1, j = pSelection.Columns.Count; i <= j; i++)
{
mDataTable.Columns.Add(new DataColumn("Kolonne " + i, typeof(string)));
}
for (int mRowNum = 1, mNumRows = pSelection.Rows.Count; mRowNum <= mNumRows; mRowNum++)
{
var mRow = mDataTable.NewRow();
for (int mColNum = 1, mNumCols = pSelection.Columns.Count; mColNum <= mNumCols; mColNum++)
{
mRow[mColNum - 1] = Table.GetNullOrString(mSelection[mRowNum, mColNum]);
}
mDataTable.Rows.Add(mRow);
}
return mDataTable;
}
private DataRow[] GetLinearFilteredRows(Range range) {
DataRow[] resultRows;
if (this.linearExpression == null) {
return index.GetRows(range);
}
List<DataRow> matchingRows = new List<DataRow>();
RBTree<int>.RBTreeEnumerator iterator = index.GetEnumerator(range.Min);
for (int i = 0; i < range.Count && iterator.MoveNext(); i++) {
if (AcceptRecord(iterator.Current)) {
matchingRows.Add(table.recordManager[iterator.Current]);
}
}
resultRows = table.NewRowArray(matchingRows.Count);
matchingRows.CopyTo(resultRows);
return resultRows;
}
private int[] GetLinearFilteredRecords(Range range) {
if (this.linearExpression == null) {
int[] resultRecords = new int[range.Count];
RBTree<int>.RBTreeEnumerator iterator = index.GetEnumerator(range.Min);
for (int i = 0; i < range.Count && iterator.MoveNext(); i++) {
resultRecords[i] = iterator.Current;
}
return resultRecords;
}
else {
List<int> matchingRecords = new List<int>();
RBTree<int>.RBTreeEnumerator iterator = index.GetEnumerator(range.Min);
for (int i = 0; i < range.Count && iterator.MoveNext(); i++) {
if (AcceptRecord(iterator.Current)) {
matchingRecords.Add(iterator.Current);
}
}
return matchingRecords.ToArray();
}
}
public DataRow[] SelectRows() {
bool needSorting = true;
InitCandidateColumns();
if (this.expression is BinaryNode) {
AnalyzeExpression((BinaryNode)this.expression);
if (!candidatesForBinarySearch) {
this.linearExpression = this.expression;
}
if (this.linearExpression == this.expression) {
for (int i=0; i<candidateColumns.Length; i++) {
if (candidateColumns[i] != null) {
candidateColumns[i].equalsOperator = false;
candidateColumns[i].expr = null;
}
}
}
else {
needSorting = !FindClosestCandidateIndex();
}
}
else {
this.linearExpression = this.expression;
}
if (index == null && (IndexFields.Length > 0 || this.linearExpression == this.expression)) {
needSorting = !FindSortIndex();
}
if (index == null) {
CreateIndex();
needSorting = false;
}
if (index.RecordCount == 0)
return table.NewRowArray(0);
Range range;
if (matchedCandidates == 0) { // [....] : Either dont have rowFilter or only linear search expression
range = new Range(0, index.RecordCount-1);
Debug.Assert(!needSorting, "What are we doing here if no real reuse of this index ?");
this.linearExpression = this.expression;
return GetLinearFilteredRows(range);
}
else {
range = GetBinaryFilteredRecords();
if (range.Count == 0)
return table.NewRowArray(0);
if (matchedCandidates < nCandidates) {
BuildLinearExpression();
}
if (!needSorting) {
return GetLinearFilteredRows(range);
}
else {
this.records = GetLinearFilteredRecords(range);
this.recordCount = this.records.Length;
if (this.recordCount == 0)
return table.NewRowArray(0);
Sort(0, this.recordCount-1);
return GetRows();
}
}
}
void tblSalesOrderDetails_SelectionChange(Range Target)
{
//select the ribbon with the test buttons
Globals.Ribbons.Ribbon1.RibbonUI.ActivateTabMso("TabAddIns");
}
private TFunc GetSelectedFunction()
{
Place place = fastColoredTextBox1.Selection.Start;
var r = new Range(fastColoredTextBox1, place, place);
string hoveredWord = r.GetFragment("[_a-zA-Z0-9]").Text;
ListViewItem itm = listView3.FindItemWithText(hoveredWord);
if (itm != null) return (itm.Tag as TFunc);
return null;
}
public void GenerateExcelWithHeaderForPdf(DataSet receiveDS, string filePath, int rowcout, string companyName, string companyAddress, int columnScape, bool istotal, int itemPerPage)
{
ds = receiveDS;
strFileName = filePath;
try
{
application = new Application();
application.Visible = false;//true;
application.DisplayAlerts = false;
workbooks = application.Workbooks;
workbook = (Workbook)application.Workbooks.Add(1);
workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false,
XlSaveAsAccessMode.xlExclusive, false, false, null, null, false);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet);
int excelSheetRow = this.WriteDataToTheSpecifiedRangeForPdf(ds.Tables[0], range, rowcout, itemPerPage, XLsheet, rowcout, companyName, columnScape);
//if (istotal)
//{
// var colCount = ds.Tables[0].Columns.Count;
// var dataRowCount = ds.Tables[0].Rows.Count;
// var headerRangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, 1], XLsheet.Cells[excelSheetRow + 1, columnScape]];
// headerRangeTotal[1, 1] = "Total";
// headerRangeTotal.Font.Bold = true;
// headerRangeTotal.Font.Size = 12;
// headerRangeTotal.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
// headerRangeTotal.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// headerRangeTotal.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
// headerRangeTotal.Merge(Type.Missing);
// headerRangeTotal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// //int i = columnScape - 1;
// for (int i = 0; i < colCount - columnScape; i++)
// {
// //var rangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1], XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1]];
// var rangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1], XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1]];
// string startColumnLetter = ExcelColumnLetter(i + columnScape);
// string endColumnLetter = ExcelColumnLetter(i + columnScape);
// rangeTotal[1, 1] = "=SUM(" + startColumnLetter + "" + (rowcout + 1) + ":" + endColumnLetter + "" + (rowcout + dataRowCount) + ")";
// rangeTotal[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
// rangeTotal[1, 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// rangeTotal[1, 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
// }
//}
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XLsheet.PageSetup.PrintTitleRows = "$1:$6";
XLsheet.Columns.AutoFit();
XLsheet.Name = ds.Tables[0].TableName;
workbook.Save();
}
catch (Exception exp)
{
throw exp;
}
finally
{
DisposeExcelObject();
}
}
public void GenerateExcel(DataSet receiveDS, string filePath, int rowcout)
{
ds = receiveDS;
strFileName = filePath;
try
{
application = new Application();
application.Visible = false;//true;
application.DisplayAlerts = false;
workbooks = application.Workbooks;
workbook = (Workbook)application.Workbooks.Add(1);
workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false,
XlSaveAsAccessMode.xlExclusive, false, false, null, null, false);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
int excelSheetRow = this.WriteDataToTheSpecifiedRange(ds.Tables[0], range, rowcout);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
//this.OverWriteDataToTheSpecifiedRange(ds.Tables[skuDataTableIndex], range);
//worksheet.get_Range("A1", "AZ1").EntireColumn.AutoFit();
//worksheet.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet);
XLsheet.Columns.AutoFit();
XLsheet.Name = ds.Tables[0].TableName;
workbook.Save();
}
catch (Exception exp)
{
//string path = System.Web.HttpContext.Current.Server.MapPath(@"");
//path = path.Substring(0, path.Length - 3);
//System.IO.File.AppendAllText("LeaveYearTemp" + "/errorLog.txt", exp.Message);
throw exp;
}
finally
{
DisposeExcelObject();
}
}
private int WriteDataToTheSpecifiedRangeForPdf(System.Data.DataTable dt, Range range, int rowount, int itemPerPage, Worksheet XLsheet, int rowcout, string companyName, int columnScape)
{
int iRow;
int iCol;
rowount = 1;
int excelSheetRow = rowount;
int pageSize = itemPerPage;
var totalPages = 0;
if (dt.Rows.Count%pageSize == 0)
{
totalPages = dt.Rows.Count / pageSize;
}
else
{
totalPages = (dt.Rows.Count / pageSize)+1;
}
for (int i = 0; i < totalPages; i++)
{
#region Main Header
var headerRange1 = XLsheet.Range[XLsheet.Cells[excelSheetRow, 1], XLsheet.Cells[(excelSheetRow + 3), 6]]; //rowount + pageSize * i
headerRange1.Font.Bold = true;
headerRange1.Font.Size = 16;
//headerRange1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// headerRange1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
headerRange1.Merge(Type.Missing);
headerRange1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
string picPath = System.Web.HttpContext.Current.Server.MapPath("../Images/report-logo.png");
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range picPosition = headerRange1;//GetPicturePosition(); // retrieve the range for picture insert
Microsoft.Office.Interop.Excel.Pictures p = XLsheet.Pictures(missing) as Microsoft.Office.Interop.Excel.Pictures;
Microsoft.Office.Interop.Excel.Picture pic = null;
pic = p.Insert(picPath, missing);
pic.Left = Convert.ToDouble(picPosition.Left);
pic.Top = picPosition.Top;
headerRange1.Style.Height = 84;
#endregion
#region Print DateTime
var headerRangeDateTimeVal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 2 , ds.Tables[0].Columns.Count - 4], XLsheet.Cells[excelSheetRow+ 2, ds.Tables[0].Columns.Count]];
headerRangeDateTimeVal[1, 1] = "Print Date & Time : " + DateTime.Now.ToString("d-MMM-yyyy HH.mm.ss tt", CultureInfo.InvariantCulture);
headerRangeDateTimeVal[2, 4] = "Page " +(i+1) + " of " + totalPages;
headerRangeDateTimeVal.Font.Bold = true;
headerRangeDateTimeVal.Font.Size = 10;
headerRangeDateTimeVal.Merge(Type.Missing);
headerRangeDateTimeVal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
#endregion
excelSheetRow = excelSheetRow + 5;
for (iCol = 0; iCol < dt.Columns.Count; iCol++)
{
range[excelSheetRow, (iCol + 1)] = iCol < 4 ? Regex.Replace(dt.Columns[iCol].ColumnName, "([a-z])_?([A-Z])", "$1 $2") : dt.Columns[iCol].ColumnName;
range.WrapText = false;
range.Font.Bold = true;
range[excelSheetRow, (iCol + 1)].Font.Size = 10;
range[excelSheetRow, (iCol + 1)].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
range[excelSheetRow, (iCol + 1)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[excelSheetRow, (iCol + 1)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
if (iCol > 3)
{
range[excelSheetRow, (iCol + 1)].Style.WrapText = true;
}
else
{
range[excelSheetRow, (iCol + 1)].Style.WrapText = false;
}
}
bool endLoop = false;
excelSheetRow = excelSheetRow + 1;
for (iRow = i * pageSize; iRow < (i + 1) * pageSize; iRow++)
{
for (iCol = 0; iCol < dt.Columns.Count; iCol++)
{
if (iRow > dt.Rows.Count-1)
{
endLoop = true;
break;
}
range[(excelSheetRow), (iCol + 1)] = dt.Rows[iRow][iCol].ToString();
range[(excelSheetRow), (iCol + 1)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[(excelSheetRow), (iCol + 1)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
if (iCol > 3)
{
range[(excelSheetRow), (iCol + 1)].WrapText = true;
}
else
{
range[(excelSheetRow), (iCol + 1)].WrapText = false;
}
}
if(endLoop)break;
excelSheetRow++;
}
// excelSheetRow++;
// rowount = rowcout+4;
}
WriteReportFooter(XLsheet, rowcout, columnScape, excelSheetRow);
return excelSheetRow;
}
private int WriteDataToTheSpecifiedRange(System.Data.DataTable dt, Range range, int rowount)
{
int iRow;
int iCol;
//Traverse through the DataTable columns to write the headers on the first row of the excel sheet.
for (iCol = 0; iCol < dt.Columns.Count; iCol++)
{
//range = (Range)worksheet.Cells[1, (iCol + 1)];
//range.Value2 = dt.Columns[iCol].ColumnName;
if (iCol == 0)
{
range[rowount, (iCol + 1)] = "SL";
range.WrapText = false;
range.Font.Bold = true;
range[rowount, (iCol + 1)].Font.Size = 10;
range[rowount, (iCol + 1)].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
range[rowount, (iCol + 1)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[rowount, (iCol + 1)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
}
range[rowount, (iCol + 2)] = iCol < 4 ? Regex.Replace(dt.Columns[iCol].ColumnName, "([a-z])_?([A-Z])", "$1 $2") : dt.Columns[iCol].ColumnName;
range.WrapText = false;
range.Font.Bold = true;
range[rowount, (iCol + 2)].Font.Size = 10;
range[rowount, (iCol + 2)].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
range[rowount, (iCol + 2)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[rowount, (iCol + 2)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
if (iCol > 3)
{
range[rowount, (iCol + 2)].Style.WrapText = true;
//range[rowount, (iCol + 1)].EntireRow.AutoFit();
//range.EntireColumn.AutoFit();
}
else
{
range[rowount, (iCol + 2)].Style.WrapText = false;
}
}
//range.AutoFit();
//Traverse through the rows and columns of the datatable to write the data in the sheet.
int excelSheetRow = rowount + 1;
int sl = 1;
for (iRow = 0; iRow < dt.Rows.Count; iRow++)
{
for (iCol = 0; iCol < dt.Columns.Count; iCol++)
{
if (iCol == 0)
{
range[(excelSheetRow), (iCol + 1)] = sl;
range[(excelSheetRow), (iCol + 1)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[(excelSheetRow), (iCol + 1)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
}
range[(excelSheetRow), (iCol + 2)] = dt.Rows[iRow][iCol].ToString();
range[(excelSheetRow), (iCol + 2)].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range[(excelSheetRow), (iCol + 2)].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
if (iCol > 3)
{
range[(excelSheetRow), (iCol + 2)].WrapText = true;
}
else
{
range[(excelSheetRow), (iCol + 2)].WrapText = false;
}
//if (dt.Columns[iCol].ColumnName == "Material")
//{
// range[(excelSheetRow), (iCol + 1)] = "'" + dt.Rows[iRow][iCol].ToString();
//}
//else
//{
// range[(excelSheetRow), (iCol + 1)] = dt.Rows[iRow][iCol].ToString();
//}
}
excelSheetRow++;
sl++;
}
return excelSheetRow;
}
