How to save an XLS file to CSV in C Sharp

Question

0.00/5 (No votes)

See more:

Hello,

I am trying to create and application that allows you to read a Spreadsheet from excel and to then save this out to a CSV file.

This application uses a Windows form which allows the user to select the spreadsheet and allows to name the new CSV file.

I have got to the point of writing to the CSV file, however there is an issue with writing to the CSV file as the data being written isn't correct, I'm not sure what I have done wrong and any info is gratefully appreciated.

My Code:

C#

public bool OutPutFileIsValid = false;
        public string FileLocation;
        public string OutPutFile;

        public XlSconverter()
        {
            InitializeComponent();
        }

        private void btnProcess_Click(object sender, EventArgs e)
        {
            ConvertSpreadsheet();
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                txtBxFileLocation.Text = openFileDialog1.FileName;

                FileLocation = txtBxFileLocation.Text;
            }
        }
        public void ConvertSpreadsheet()
        {
            CreateOutPutFile();
        }
        public void CreateOutPutFile()
        {
            var newFileName = txtBxNewFileName.Text + ".csv";

            if (!File.Exists(newFileName))
            {
                using (var fs = File.Create(newFileName))
                {

                    for (byte i = 0; i < 100; i++)
                    {
                        fs.WriteByte(i);
                    }

                    OutPutFileIsValid = true;

                    WriteCsv();
                }
            }
            else
            {
                OutPutFile = newFileName;
                MessageBox.Show("File Already exists", newFileName);
            }
        }

        public void WriteCsv()
        {
            var dt = new DataTable();

            OutPutFile = txtBxNewFileName.Text;

            using (var wtr = new StreamWriter(OutPutFile))

                foreach (DataRow row in dt.Rows)
                {
                    var firstLine = true;
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (!firstLine)
                        {
                            wtr.Write(",");
                        }
                        else
                        {
                            firstLine = false;
                        }
                        var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");

                        wtr.Write("\"{0}\"", data);

                        wtr.WriteLine();
                    }
                }
        }
    }
}

Posted 25-Sep-13 0:06am

Glen Childs

Add a Solution

1 solution

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)

Rick van Woudenberg · Accepted Answer · 2013-09-25T00:30:00

Hey Glen,

There are several things you could try. I tend to use the method below quite frequently :

C#

static void ConvertExcelToCSV(string excelFilePath, string csvOutputFile, int worksheetNumber = 1) {
   if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
   if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

   // connection string
   var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
   var cnn = new OleDbConnection(cnnStr);

   // get schema, then data
   var dt = new DataTable();
   try {
      cnn.Open();
      var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
      if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
      string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
      string sql = String.Format("select * from [{0}]", worksheet);
      var da = new OleDbDataAdapter(sql, cnn);
      da.Fill(dt);
   }
   catch (Exception e) {
      // ???
      throw e;
   }
   finally {
      // free resources
      cnn.Close();
   }

   // write out CSV data
   using (var wtr = new StreamWriter(csvOutputFile)) {
      foreach (DataRow row in dt.Rows) {
         bool firstLine = true;
         foreach (DataColumn col in dt.Columns) {
            if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
            var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
            wtr.Write(String.Format("\"{0}\"", data));
         }
         wtr.WriteLine();
      }
   }
}

You could also checkout the .SaveAs() method in Excel object.

C#

wbWorkbook.SaveAs("c:\filename.csv", Microsoft.Office.Interop.Excel.XlXlFileFormat.xlCSV)

Or :

C#

public static void SaveAs()
{
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Add(Type.Missing);
    Microsoft.Office.Interop.Excel.Sheets wsSheet = wbWorkbook.Worksheets;
    Microsoft.Office.Interop.Excel.Worksheet CurSheet = (Microsoft.Office.Interop.Excel.Worksheet)wsSheet[1];
    Microsoft.Office.Interop.Excel.Range thisCell = (Microsoft.Office.Interop.Excel.Range)CurSheet.Cells[1, 1];
    thisCell.Value2 = "This is a test.";
    wbWorkbook.SaveAs(@"c:\one.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    wbWorkbook.SaveAs(@"c:\two.csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    wbWorkbook.Close(false, "", true);
}

There could be issue if you're using foreign characters, like chinese but you'd have to figure that one out by trial and error I guess. Hope this helps.