C# 将 Excel 文件从 .csv 转换为 .xlsx

Converting Excel File From .csv To .xlsx

提问by William

I want my application to go and find a csvexcel file and convert it into a .xlsx file instead.

我希望我的应用程序能够找到一个csvexcel 文件并将其转换为 .xlsx 文件。

Here's what I'm currently doing;

这是我目前正在做的事情；

var fileName = @"Z:28\orders\PurchaseOrder.csv";
FileInfo f = new FileInfo(fileName);
f.MoveTo(Path.ChangeExtension(fileName, ".xlsx"));
var Newfile = @"Z:28\orders\PurchaseOrder.xlsx";

Now this does work. It changes the file extension to my desired format. However, the file then become 'corrupt' or at least Excel refuses to open it and neither will my application when I try to venture further.

现在这确实有效。它将文件扩展名更改为我想要的格式。但是，该文件随后变得“损坏”，或者至少 Excel 拒绝打开它，当我尝试进一步冒险时，我的应用程序也不会打开它。

Does anyone have a solution/work-around?

有没有人有解决方案/解决方法？

采纳答案by Mark Redman

I would parse in the CSV file and use this to write out an Excel file : https://github.com/JanKallman/EPPlus

我会解析 CSV 文件并使用它来写出一个 Excel 文件：https: //github.com/JanKallman/EPPlus

回答by cvraman

I would recommend Closed XMLwhich is a wrapper around Open XML SDK. Check out their examples. It's pretty easy to create a .xlsx file.

我会推荐Closed XML，它是 Open XML SDK 的包装器。看看他们的例子。创建 .xlsx 文件非常容易。

回答by Christian Sauer

I would recommend using the following technique:

我建议使用以下技术：

1. http://kbcsv.codeplex.com/this reads CSV files in very easily and is very robust.
2. Create a datatable from the csv via the kbcsv extensions.
3. Use the eppplus library and its LoadFromDataTable to create a valid xlsx file (https://github.com/JanKallman/EPPlus)
4. done!

1. http://kbcsv.codeplex.com/这可以非常轻松地读取 CSV 文件并且非常强大。
2. 通过 kbcsv 扩展从 csv 创建数据表。
3. 使用 eppplus 库及其 LoadFromDataTable 创建有效的 xlsx 文件 ( https://github.com/JanKallman/EPPlus)
4. 完毕！

Advantages:

好处：

• It is faster than excel interop
• KBCSV is more robust than excels csv reading methods.
• It is availabe in environments witohout office.

• 它比excel互操作更快
• KBCSV 比 csv 读取方法更强大。
• 它可以在没有办公室的环境中使用。

回答by Rakuen42

For those who want to use Interop instead of an external library, you can simply do this:

对于那些想要使用 Interop 而不是外部库的人，您可以简单地执行以下操作：

Application app = new Application();
Workbook wb = app.Workbooks.Open(@"C:\testcsv.csv", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(@"C:\testcsv.xlsx", XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();

The second argument of Workbook.SaveAs determines the true format of the file. You should make the filename extension match that format so Excel doesn't complain about corruption. You can see a list of the types and what they mean on MSDN.

Workbook.SaveAs 的第二个参数确定文件的真实格式。您应该使文件扩展名与该格式匹配，以便 Excel 不会抱怨损坏。您可以在 MSDN 上查看类型列表及其含义。

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

As always, please keep Microsoft's considerations in mind if this functionality is intended for a server environment. Interop may not be the way to go in that situation:

与往常一样，如果此功能旨在用于服务器环境，请记住 Microsoft 的注意事项。在这种情况下，Interop 可能不适合：

http://support.microsoft.com/kb/257757

http://support.microsoft.com/kb/257757

回答by arash

This code should open the file you want and save it to the format without corrupting it.

此代码应打开您想要的文件并将其保存为格式而不会损坏它。

1. Renames the file
2. Creates the Excel.Application instance
3. Opens the file
4. Does a save as to the desired format

5. Closes it

   using Excel = Microsoft.Office.Interop.Excel;
   
   private void Convert_CSV_To_Excel()
   {
   
       // Rename .csv To .xls
       System.IO.File.Move(@"d:\Test.csv", @"d:\Test.csv.xls");
   
       var _app = new Excel.Application();
       var _workbooks = _app.Workbooks;
   
       _workbooks.OpenText("Test.csv.xls",
                                DataType: Excel.XlTextParsingType.xlDelimited,
                                TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
                                ConsecutiveDelimiter: true,
                                Semicolon: true);
   
       // Convert To Excle 97 / 2003
       _workbooks[1].SaveAs("NewTest.xls", Excel.XlFileFormat.xlExcel5);
   
       _workbooks.Close();
   }
   

1. 重命名文件
2. 创建 Excel.Application 实例
3. 打开文件
4. 是否保存为所需的格式

5. 关闭它

   using Excel = Microsoft.Office.Interop.Excel;
   
   private void Convert_CSV_To_Excel()
   {
   
       // Rename .csv To .xls
       System.IO.File.Move(@"d:\Test.csv", @"d:\Test.csv.xls");
   
       var _app = new Excel.Application();
       var _workbooks = _app.Workbooks;
   
       _workbooks.OpenText("Test.csv.xls",
                                DataType: Excel.XlTextParsingType.xlDelimited,
                                TextQualifier: Excel.XlTextQualifier.xlTextQualifierNone,
                                ConsecutiveDelimiter: true,
                                Semicolon: true);
   
       // Convert To Excle 97 / 2003
       _workbooks[1].SaveAs("NewTest.xls", Excel.XlFileFormat.xlExcel5);
   
       _workbooks.Close();
   }
   

回答by costsoldier

Try this class; takes in any CSV or TXT file with any delimiter including a tab and converts to Excel (.xls)

试试这门课；接收带有任何分隔符（包括制表符）的任何 CSV 或 TXT 文件并转换为 Excel (.xls)

examples:

例子：

• convertToExcel(@"path to file", "\t", ".csv");

• convertToExcel(@"path to file", "\|", ".txt");

  public static void convertToExcel(string fileName, string splitter, string extension)
  {
      string newFileName = fileName.Replace("." + extension, ".xls");
  
      string[] lines = File.ReadAllLines(fileName, Encoding.UTF8);
  
      int columnCounter = 0;
  
      foreach (string s in lines)
      {
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          if (ss.Length > columnCounter)
              columnCounter = ss.Length;
      }           
  
      HSSFWorkbook workbook = new HSSFWorkbook();
      var sheet = workbook.CreateSheet("Data");
      var rowIndex = 0;
      var rowExcel = sheet.CreateRow(rowIndex);
  
      foreach (string s in lines)
      {
          rowExcel = sheet.CreateRow(rowIndex);
  
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          for (int i = 0; i < columnCounter; i++)
          {
              string data = !String.IsNullOrEmpty("s") && i < ss.Length ? ss[i] : "";
              rowExcel.CreateCell(i).SetCellType(CellType.String);
              rowExcel.CreateCell(i).SetCellValue(data);                    
          }
  
          rowIndex++;
      }
  
      for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
          sheet.AutoSizeColumn(i);
  
      using (FileStream file = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
      {
          workbook.Write(file);
          file.Close();
      }
  }
  

• convertToExcel(@"文件路径", "\t", ".csv");

• convertToExcel(@"文件路径", "\|", ".txt");

  public static void convertToExcel(string fileName, string splitter, string extension)
  {
      string newFileName = fileName.Replace("." + extension, ".xls");
  
      string[] lines = File.ReadAllLines(fileName, Encoding.UTF8);
  
      int columnCounter = 0;
  
      foreach (string s in lines)
      {
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          if (ss.Length > columnCounter)
              columnCounter = ss.Length;
      }           
  
      HSSFWorkbook workbook = new HSSFWorkbook();
      var sheet = workbook.CreateSheet("Data");
      var rowIndex = 0;
      var rowExcel = sheet.CreateRow(rowIndex);
  
      foreach (string s in lines)
      {
          rowExcel = sheet.CreateRow(rowIndex);
  
          string[] ss = s.Trim().Split(Convert.ToChar(splitter));
  
          for (int i = 0; i < columnCounter; i++)
          {
              string data = !String.IsNullOrEmpty("s") && i < ss.Length ? ss[i] : "";
              rowExcel.CreateCell(i).SetCellType(CellType.String);
              rowExcel.CreateCell(i).SetCellValue(data);                    
          }
  
          rowIndex++;
      }
  
      for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
          sheet.AutoSizeColumn(i);
  
      using (FileStream file = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
      {
          workbook.Write(file);
          file.Close();
      }
  }