Random programming things I'd want to remember

Wednesday, December 28, 2011

Extract data from Excel into binary text file

In an Excel document (.xlsx, for .xls use
connStr
as the connection string), name one worksheet "Questions" and another "Answers" (no quotes in both cases). All columns are in Excel General format; the allowable format is noted in parentheses: Questions worksheet: QuestionID (Number), QuestionText (Text) Answers worksheet: AnswerID (Number), AnswerText (Text), QuestionID (Number), Correct (Text, but the only acceptable values are TRUE and FALSE).
var fileName = string.Format("{0}\\dbase.xlsx", Directory.GetCurrentDirectory());
var connStr = string.Format(
  "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var connectionString = string.Format(
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", fileName);
var adapter1 = new OleDbDataAdapter("SELECT * FROM [Questions$]", connectionString);
var adapter2 = new OleDbDataAdapter("SELECT * FROM [Answers$]", connectionString);

var ds = new DataSet();

adapter1.Fill(ds, "Questions");
adapter2.Fill(ds, "Answers");

var answersTable = ds.Tables["Answers"].AsEnumerable();
var questionsTable = ds.Tables["Questions"].AsEnumerable();

List answers = answersTable.Where(
  x => x.Field("AnswerID") > 0.0).Select(x =>
     new TestQuestionAnswer
     {
         AnswerID = Convert.ToInt32(x.Field("AnswerID")),
         AnswerText = x.Field("AnswerText"),
         AnswerCorrect = x.Field("Correct"),
         QuestionID = Convert.ToInt32(x.Field("QuestionID"))
     }).ToList();

List questions = questionsTable.Where(
  x => x.Field("QuestionID") > 0.0).Select(x =>
    new TestQuestion
    {
        QuestionID = Convert.ToInt32(x.Field("QuestionID")),
        QuestionText = x.Field("QuestionText")
    }).ToList();


foreach (TestQuestion tq in questions)
{
    List a = answers.Where(x => x.QuestionID == tq.QuestionID).ToList();
    tq.Answers = a;
}

using (FileStream fs = new FileStream("data.dat", FileMode.Create))
{
    using (var writer = new BinaryWriter(fs))
    {
        writer.WriteList(questions);
    }
}
//fix for Syntax Highlighter plugin glitch:
//

Adjust the label text size of radiobutton or checkbox


txtQuestion.Text = "Question text";
int answerBreak = panel1.Height / NUMBEROFANSWERS;
int runningHeightTotal = 0;

panel1.Controls.Clear();

for (int i = 0; i < NUMBEROFANSWERS; i++)
{

  Font stringFont = new System.Drawing.Font(
    SystemFonts.DefaultFont.ToString(), 12, System.Drawing.FontStyle.Regular);

  //Gotcha 1: MeasureText does not produce accurate results
  Size textSize = TextRenderer.MeasureText(
    testQuestions[currentlyTestedQuestionID].Answers[i].AnswerText, stringFont);


  RadioButton rb = new RadioButton();
  rb.Tag = testQuestions[currentlyTestedQuestionID].Answers[i].AnswerID;

  //shift down if the previous answer took too much room
  rb.Location = new System.Drawing.Point(5, 
    (i * answerBreak >= runningHeightTotal ? i * answerBreak : runningHeightTotal + 5));
  rb.Text = testQuestions[currentlyTestedQuestionID].Answers[i].AnswerText;
  rb.Size = new System.Drawing.Size(textSize.Width, textSize.Height);
  rb.Font = stringFont;

  using (Graphics g = rb.CreateGraphics())
  {
    bool height = false;
    int linesNumber = 1;
    //so the text size is measured using the Graphics object
    Size s = g.MeasureString(rb.Text, rb.Font).ToSize();
    if (rb.Size.Height <= s.Height)
      height = true;

    if (s.Width > PANEL1WIDTH)
    {
      linesNumber = s.Width / 750;
      if (s.Width % PANEL1WIDTH > 0)
        linesNumber += 1;
    }

    rb.Size = new Size(PANEL1WIDTH, 
      height ? (s.Height + 5) * linesNumber : rb.Size.Height * linesNumber);
  }

  runningHeightTotal += rb.Height;

  panel1.Controls.Add(rb);
}
If checkbox's size needs to be adjusted, declare rb as a CheckBox, and everything will work just as well.

Get Stream for a BinaryReader for a file embedded in exe


public Stream GetDataFileStream(string resourceName)
  {
    foreach (string currentResource in System.Reflection.Assembly.
        GetExecutingAssembly().GetManifestResourceNames())
      if (currentResource.LastIndexOf(resourceName) != -1)
        {
          return System.Reflection.Assembly.GetExecutingAssembly().
            GetManifestResourceStream(currentResource);
        }

        throw new Exception("Resource not found : " + resourceName);
  }


//Then call this code:

using (BinaryReader reader = new BinaryReader(GetDataFileStream("data.dat")))
  {
    yourDataContainter = reader.ReadOrWhatever();
  }

In solution explorer, right-click on the text file that you want to embed, select "Properties". Under "Build Action", select "Embedded Resource", build solution.

How to extract embedded image/Как достать картинку из скомпилированного exe

Image _image;
List resourcenames = new List();//syntax highlighter glitch fix: 
resourcenames.AddRange(Assembly.GetExecutingAssembly().GetManifestResourceNames());
string resourcename = resourcenames.Find(
   delegate(string item) 
      { return item.EndsWith("template1.jpg"); });
Stream s = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourcename);
BinaryReader br = new BinaryReader(s);

MemoryStream ms = new MemoryStream(br.ReadBytes((int)s.Length));
_image = Image.FromStream(ms);
br.Close();
s.Close();
br.Dispose();
s.Dispose();

In solution explorer, right-click on the text file that you want to embed, select "Properties". Under "Build Action", select "Embedded Resource", build solution.

How to extract embedded text file/Как достать текстовый файл, скомпилированный в exe

        public static string ExtractResource(string resourceName)
        {
            foreach (string currentResource in System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceNames())
                if (currentResource.LastIndexOf(resourceName) != -1)
                {
                    string fqnTempFile = System.IO.Path.GetTempFileName();
                    string path = System.IO.Path.GetDirectoryName(fqnTempFile);
                    string rootName = System.IO.Path.GetFileNameWithoutExtension(fqnTempFile);
                    string destFile = path + @"\" + rootName + "." +
                    System.IO.Path.GetExtension(currentResource);

                    System.IO.Stream fs = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(currentResource);

                    byte[] buff = new byte[fs.Length];
                    fs.Read(buff, 0, (int)fs.Length);
                    fs.Close();

                    System.IO.FileStream destStream = new System.IO.FileStream(destFile,FileMode.Create);
                    destStream.Write(buff, 0, buff.Length);
                    destStream.Close();

                    return destFile;
                }

            throw new Exception("Resource not found : " + resourceName);

        }
In solution explorer, right-click on the text file that you want to embed, select "Properties". Under "Build Action", select "Embedded Resource", build solution.

Thursday, September 22, 2011

How to set Google Chrome's search engine to Google US English by default

Instructions are taken from here: http://www.google.com/support/forum/p/Chrome/thread?tid=581638f51350589b&hl=en
1. Go to Wrench > Options (Preferences on Mac) > Basics > Search > Manage
2. Select Add ("+" sign on Mac)
3. For "Name" and "Keyword," type whatever
4. For "URL," enter http://www.google.com/search?q=%s
5. Click O.K. 6. Select your new search engine from the list and click "Make default"
7. Click "Close"

Generate random text in Microsoft Word

If you type in =rand(10,10) in Microsoft Word, it will generate 10 paragraphs of text with 10 sentences in each paragraph. Looks like the text is coming from Word help files, but it is a great feature.

Thursday, September 15, 2011

How to make bootable USB flash drive with just Windows

diskpart
select disk 1
clean
create partition primary
select partition 1
active
format fs=fat32
assign
exit

xcopy d:\*.* /s/e/f e:\

From: http://www.techmixer.com/install-windows-vista-from-bootable-usb-flash-memory-drive/

Sunday, July 3, 2011

Firefox 5 tab scrolling issue

I like seeing all my tabs at once in my browser, so here is how I did it in Firefox 5:

1. Open Firefox menu -> Help -> Troubleshooting information.
2. Click "Open Containing Folder" button.
3. Create "chrome" folder inside the folder that had just opened up after step 2.
4. Create a text file, call it "userChrome.css" and paste the following into it:

@namespace url("http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul"); 

.tabbrowser-tab {min-width: 016px !important;}
.tabbrowser-tab {clip-width: 016px !important;} 

Saturday, July 2, 2011

Windows Phone 7: Iterate over ListBoxItems in a list box to focus on a control

I am developing a small Windows Phone 7 application and I ran into a wall. I am updating items within a ListBox and I need to set focus on a control after the update. It turns out that it is not a trivial task -- I need to iterate over the entire list box to get to the control and then set focus on it.

Here is the XAML of my list box:

     
                
                    
                        
                            
                            
                            
                        
                    
                

            

I was using the VisualTreeHelper class to iterate over the ListBox, but I was not getting to the ListBoxItems. I was getting ListBox, ScrollViewer, Border, Grid, ContentPresenter, ItemsPresenter, VirtualizingStackPanel, and other controls, but no ListBoxItems. After reading this, the problem was fixed by calling the
UpdateLayout
method. The rest was simple. Here is my code.

void SetFocusOnTextBox(DependencyObject element, int primaryKey)
        {
            for (int i = 0; i < VisualTreeHelper.GetChildrenCount(element); i++)
            {
                if (foundItem == false) //global flag that stops processing once the control is found
                {
                    DependencyObject child = VisualTreeHelper.GetChild(element, i);
                    StackPanel s = child as StackPanel;
                    if (s != null)
                        FindAllChildrenOfAStackPanel(child, primaryKey);
                    if (foundItem == false)
                        SetFocusOnTextBox(child, primaryKey);
                    else
                        break;
                }
            }
            foundItem = false;
        }

        void FindAllChildrenOfAStackPanel(DependencyObject element, int primaryKey)
        {
            StackPanel s = element as StackPanel;
            if (s != null)
            {
                for (int i = 0; i < VisualTreeHelper.GetChildrenCount(element); i++)
                {
                    TextBox t = VisualTreeHelper.GetChild(element, i) as TextBox;
                    if (t != null)
                        if (t.Name == primaryKey)
                        {
                            t.Focus();
                            foundItem = true;
                            break;
                        }
                }
            }
        }

Friday, April 15, 2011

Excel and multithreading

I was working on an application that opens an Excel spreadsheet, pulls some data out, manipulates it, and exports it into CSV. I ran into a couple of interesting things, they are worth mentioning.

All the computers that will run the code below need to have Microsoft Office and Microsoft Office PIA (Primary Interop Assemblies) installed. More info here: http://msdn.microsoft.com/en-us/library/15s06t57.aspx. Make sure to match the version of Microsoft Office and PIA components. Then, on the development machine, in the solution, add reference to Microsoft.Office.Interop.Excel dll, which, on my computer, was located at "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll".

My form looks like this:


The use of delegates make the application a bit more complicated, but it adds a nice option to display progress bars. I got the idea and some code here: http://msdn.microsoft.com/en-us/library/ms993020.aspx. The only thing I added is the call on line 69 for a method that would start as soon as the ProcessData method finishes working.

The other thing I changed was disabling the minimize/maximize/close buttons for the form (line 19). The reason for that is if the user quits application by clicking the close button in the top right corner, Excel process still stays in memory. If the user exits the application using the "Close" button that triggers the event on line 72, Excel process is properly disposed.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;

namespace MultiThreadExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.ControlBox = false;
        }

        private Excel.Application app = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        private Excel.Sheets sheets;
        string fileName = @"D:\test.xlsx";

        delegate void ProcessDataDelegate(int linesToRead);
        delegate void ShowProgressDelegate(string progressMessage, int totalDigits, int digitsSoFar);
        
        public void AddComplete(IAsyncResult itfAR)
        {
            if (workbook != null)
                workbook.Close(false, fileName, false);
            if (app != null)
                app.Quit();
        }

        private void ProcessData(int linesToRead)
        {
            app = new Excel.Application();
            workbook = app.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            sheets = workbook.Worksheets;
            worksheet = (Excel.Worksheet)sheets.get_Item(1);
            for (int i = 0; i < linesToRead; i++)
            {
                ShowProgress("Processing...", linesToRead, (i+1));
                Thread.Sleep(50);
            }
        }

        void ShowProgress(string progressMessage, int totalDigits, int digitsSoFar)
        {
            if (progressBar1.InvokeRequired == false)
            {
                progressBar1.Maximum = totalDigits;
                progressBar1.Value = digitsSoFar;
            }
            else
            {
                ShowProgressDelegate showProgress = new ShowProgressDelegate(ShowProgress);
                BeginInvoke(showProgress, new object[] { progressMessage, totalDigits, digitsSoFar });
            }
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            ProcessDataDelegate proDa = new ProcessDataDelegate(ProcessData);
            IAsyncResult iftAR = proDa.BeginInvoke(100, new AsyncCallback(AddComplete), null);
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}