如何在C Sharp中将XLS文件保存为CSV [英] How to save an XLS file to CSV in C Sharp

问题描述

您好，



我正在尝试创建和应用程序，允许您从Excel中读取电子表格，然后将其保存为CSV文件。



此应用程序使用Windows表单，允许用户选择电子表格并允许命名新的CSV文件。



我已经到了写入CSV文件的位置，但是由于写入的数据不正确而写入CSV文件存在问题，我不确定我做错了什么，感谢任何信息。



我的代码：

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:

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();
                    }
                }
        }
    }
}

推荐答案

嘿格伦，



有你可以尝试几件事。我倾向于经常使用下面的方法：

Hey Glen,

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

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();
      }
   }
}

您还可以在Excel对象中签出 .SaveAs（）方法。 br />

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

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

或：

Or :

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.

这篇关于如何在C Sharp中将XLS文件保存为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！