Creating .csv file from template

Question

3.00/5 (1 vote)

See more:

Hi guys i am having a problem with my app

i have a listbox that i want to export to excel in .csv format

the problem is a need to use a template (.xlt) file to create this file

here is my code i am using at the moment but my problem is it is not appending to the file.

string sFolder = fExcel;

                   /*Create Excel File if not exists*/
                   File.Copy(sFolder + "\\Template.xlt", sFolder+"\\"+sFileName);
                   System.IO.FileStream sFileOpen = new System.IO.FileStream(sFolder + "\\" + sFileName, System.IO.FileMode.Open);

                   /*Create Notification after file was successfully created*/
                   string crtMsg = DateTime.Now + " : EXCEL FILE CREATED, loc- " + sFolder;
                   exErrListBox.Items.Insert(0, crtMsg);

                   /*!!! NB !!! Release the resource to be able to write into the file. !!! NB !!!*/
                   sFileOpen.Close();



               try
               {
                   //MessageBox.Show(exHistListBox1.SelectedItems.Count.ToString());
                   string[] sSTexcel = new string[exHistListBox1.SelectedItems.Count];
                   sSW =new StreamWriter(sFolder + "\\" + sFileName,true);
                   sSW.AutoFlush = true;

                   int row;
                   string sST1 = "";






                   for (row = 0; row < exHistListBox1.SelectedItems.Count; row++)
                   {
                       sST1 = sST1 + "\n" + exHistListBox1.SelectedItems[row].ToString();
                       //MessageBox.Show(sST1);

                       /*Take out all the spaces, replace it with one single white space,
                         replace that one single whitespeace with a comma then write it to excel.csv file.*/
                       string myString = sST1;
                       do
                       {
                           myString = myString.Replace("  ", " ");
                       }
                       while (myString.IndexOf("  ") >= 0);
                       {
                           myString = myString.Trim();
                           myString = myString.Replace("\t", ",");
                           myString = myString.Replace(" ", ",");
                           myString = myString.Replace(",,", ",");
                           MessageBox.Show(myString);

                           sSW.WriteLine(myString + "\n");
                           sST1 = "";
                       }
                   }

                   sSW.Close();

can someone help please

Posted 19-Aug-10 22:14pm

Unforgiv3n

Add a Solution

Comments

Simon Dufour 24-Aug-10 10:05am

Reason for my vote of 3
Next time, check the preview and fix the formatting. In VS, you can shift tab your code to the left edge and replace TABS for spaces in your options.

Simon Dufour 8-Sep-10 8:09am

I'm glad it worked for you. GL

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon Dufour · Accepted Answer · 2010-08-24T04:03:00

Ok I tested this and it work. I've refactored everything and made it easier to understand.

Here's the base code-block that you posted. See, I've simplified it a bit by putting the creation of the Excel file into another function. Note that I've also used templatePath and filePath instead of creating them on the fly all the time.

//Create the Excel file if it don't already exist.
CreateExcelFile(templatePath, filePath);

try
{
  StreamWriter sSW = new StreamWriter(filePath, true);
  sSW.AutoFlush = true;

  foreach(object item in exHistListBox1.SelectedItems)
  {
    //Get the string to write in the file.
    string itemStr = item.ToString();

    /*Take out all the spaces, replace it with one single white space,
      replace that one single whitespeace with a comma then write it 
      to excel.csv file.*/
    sSW.Write("\n" + CleanString(itemStr));
  }

  sSW.Close();
}
catch(FileNotFoundException err)
{
  System.Diagnostics.Debug.WriteLine(err.Message);
}

I think the main problem of your code was the Do...Loop. I've simplified it a bit by using a foreach.

Here's the code for the two subfunction.

/// <summary>
/// Clean the string before writing it.
/// </summary>
/// StringToClean:  String that need to be clean
///
/// returns:  Cleaned string
private static string CleanString(string StringToClean)
{
  string result = StringToClean;
  while(result.IndexOf("  ") >= 0)
  {
    result = result.Trim();
    result = result.Replace("\t", ",");
    result = result.Replace(" ", ",");
    result = result.Replace(",,", ",");
  }
  System.Diagnostics.Debug.WriteLine("String cleaned result: " + result);
  return result;
}

/// <summary>
/// Copy the template over if the file don't exist. Otherwise, do nothing.
/// </summary>
/// templatePath: Path of the template
/// filePath    : Path to the target file
private static void CreateExcelFile(string templatePath, string filePath)
{
  //Verify if the template exist.
  if(File.Exists(templatePath))
  {
    //Verify that the file don't exist
    if(!File.Exists(filePath))
    {
      /*Create Excel File if not exists*/
      File.Copy(templatePath, filePath, false);
      System.Diagnostics.Debug.WriteLine(DateTime.Now + " : EXCEL FILE CREATED, loc- " + filePath);
    }
    else
    {
      System.Diagnostics.Debug.WriteLine(DateTime.Now + " : EXCEL FILE ALREADY EXISTED, loc- " + filePath);
    }
  }
  else
  {
    throw new FileNotFoundException("Template.xlt was not found");
  }
}

I've buffed you file copy algorithm. Now, it checks if the files exists before copying. If the template don't exist, you'll get an error message instead of an exception.

Test it and if it doesn't work, don't hesitate to comment and we'll try to fix things up.

DavidKiryazi · Accepted Answer · 2010-08-24T02:19:00

Hi Unforgiv3n,

The code looks fine (besides the fact there is a FileStream object (sFileOpen) that doesn't seem to be being used for anything. So tidy the code coz otherwise gremlins may be hiding in it and that means we have to think and thinking is hard! ;)

One thing to do is to first establish where the problem is, so is the problem a file writing issue or is it a string overwriting issue. So replace the code where it is cleaning white space with a simple string and see if you can get the file appending part to work. If that works, then your logic to format the string is flawed.

Also you are not appending ".csv" to your file so maybe you should do that just to make sure its not affecting anything.

Make sure you write clean code and remove resources that are just taking up screen real estate! Let me know how it goes.

Dave

Creating .csv file from template

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0