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.