C＃将csv转换为xls（使用现有的csv文件） [英] C# convert csv to xls (using existing csv file)

问题描述

我相信这是很多关于这个问题的讨论。
但我读了所有的帖子，尝试，但它从来不工作与c＃。
我的目标很简单，我有现有的csv文件。
只需要转换exel文件并完成。
许多人说使用spire.xls的东西，但我相信MS .office.interop.excel可以处理它。

i believe here are lot a discussion with this matter. but i read all post and try but it never work with c#. my goal is simple that i have existing csv file. just want convert exel file and done. many people said that using spire.xls something but i believe MS .office.interop.excel could handle it.

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

我读过上面的问题，这跟我的问题一样。
但是上面的代码不能在我的电脑工作..我需要导入其他dll来使用这个。
我只是从该网站复制代码。下面再次复制...

i read above issue and this is same as my problem. but above code not work in my PC.. do i need to import other dll to use this. i just copy code from that site. copy again below...

目前我使用的是作为MS.office.interop.excel和MS.office.interop.core的$ /

currently im using Lib as MS.office.interop.excel and MS.office.interop.core

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

这里有很多错误。修改代码在下面，现在我在我的引用中只使用MS.office.interop.excel和MS.office.interop.core。
它看起来像我需要使用另一个dll文件。
无论如何，我遵循该代码并制作新的代码。
它减少错误，但我不知道这是正确的方法。
以下是我现在尝试的。

here are lot a error. modify code is below and now im only using MS.office.interop.excel and MS.office.interop.core in my reference. it looks like i need to use another dll file. anyway i did follow that code and make new code. it reduce error but i don't know this is correct approach. below is what i tried now.

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        xlWorkBook = xlApp.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);
        xlWorkBook.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);
        xlWorkBook.Close(); 

这里是错误消息

   //  Error     3  The name 'XlFileFormat' does not exist in the current context   C:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.cs 528 54  DC_Test
   //   Error   4   The name 'XlSaveAsAccessMode' does not exist in the current context C:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.cs 528 142 DC_Test
   //   Error   4   No overload for method 'Close' takes '0' arguments  C:\Users\jochoi\Desktop\joseph_BT_전류_code\DC_Test - ver01\DC_Test\DC.cs 525 13  DC_Test

我的目标只是抓住存在csv文件，只是改为excel文件。
有没有人有其他解决方案，因为这个答案在我的电脑中不起作用。 （c＃）

my goal is just grab exist csv file and just change to excel file. does anyone has other solution because that answer is not work in my pc. (c#)

推荐答案

COM Interop不是最好的解决方案，特别是如果您打算在服务器环境。

COM Interop is not the best solution, especially if you're planning to run your code in a server environment.

Microsoft不目前推荐并且不支持
从任何无人参与的
非交互式客户端应用程序或组件（包括ASP，
ASP.NET，DCOM和NT服务）自动化Microsoft Office应用程序， ，因为Office在这种环境中运行时可能会出现不稳定的
行为和/或死锁。

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

另一种方法是使用适合此目的的组件。

我已经使用 EEplus ，它的工作很脏。它具有LGPL许可证，但作者似乎并未担心您在商业产品中使用它。

Another approach is to use components fit for that purpose.
I've used EEplus and it does it's dirty job. It has a LGPL licence but the author does not seem to be to worried about you using it in your commercial product.

只需安装nuget软件包：

Just install the nuget package:

Install-Package EPPlus

并使用以下代码：

using System.IO;
using OfficeOpenXml;

class Program
{
    static void Main(string[] args)
    {
        string csvFileName = @"FL_insurance_sample.csv";
        string excelFileName = @"FL_insurance_sample.xls";

        string worksheetsName = "TEST";

        bool firstRowIsHeader = false;

        var format = new ExcelTextFormat();
        format.Delimiter = ',';
        format.EOL = "\r";              // DEFAULT IS "\r\n";
        // format.TextQualifier = '"';

        using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
            worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);
            package.Save();
        }

        Console.WriteLine("Finished!");
        Console.ReadLine();
    }
}

您可以使用 ExcelTextFormat 配置CVS的结构。

You can configure the structure of you CVS using ExcelTextFormat.

我已经使用从获取的一些数据进行了测试这里。

可以找到更多的示例 here 。

更新：

一个nother选项是自己读取CSV文件作为文本文件：

Another option is to read the CSV file yourself as a text file:

private IEnumerable<string[]> ReadCsv(string fileName, char delimiter = ';')
{
    var lines = System.IO.File.ReadAllLines(fileName, Encoding.UTF8).Select(a => a.Split(delimiter));
    return (lines);
}

并使用其他开源项目，如 NPOI 或 ClosedXML 。
NPOI 和 ClosedXML 无法读取 CSV 并进行转换，但使用功能 ReadCsv 你可以自己做。

and use other open-source projects such as NPOI or ClosedXML. NPOI and ClosedXML cannot read CSV and do the conversion but using the function ReadCsv you can do it yourself.

这两个项目都有许可许可。

NPOI转换

private static bool ConvertWithNPOI(string excelFileName, string worksheetName, IEnumerable<string[]> csvLines)
{
    if (csvLines == null || csvLines.Count() == 0)
    {
    return (false);
    }

    int rowCount = 0;
    int colCount = 0;

    IWorkbook workbook = new XSSFWorkbook();
    ISheet worksheet = workbook.CreateSheet(worksheetName);

    foreach (var line in csvLines)
    {
    IRow row = worksheet.CreateRow(rowCount);

    colCount = 0;
    foreach (var col in line)
    {
        row.CreateCell(colCount).SetCellValue(TypeConverter.TryConvert(col));
        colCount++;
    }
    rowCount++;
    }

    using (FileStream fileWriter = File.Create(excelFileName))
    {
       workbook.Write(fileWriter);
       fileWriter.Close();
    }

    worksheet = null;
    workbook = null;

    return (true);
}

ClosedXML转换：

private static bool ConvertWithClosedXml(string excelFileName, string worksheetName, IEnumerable<string[]> csvLines)
{
    if (csvLines == null || csvLines.Count() == 0)
    {
    return (false);
    }

    int rowCount = 0;
    int colCount = 0;

    using (var workbook = new XLWorkbook())
    {
    using (var worksheet = workbook.Worksheets.Add(worksheetName))
    {
        rowCount = 1;
        foreach (var line in csvLines)
        {
        colCount = 1;
        foreach (var col in line)
        {
            worksheet.Cell(rowCount, colCount).Value = TypeConverter.TryConvert(col);
            colCount++;
        }
        rowCount++;
        }

    }
    workbook.SaveAs(excelFileName);
    }

    return (true);
}

如果有人感兴趣，则会有一个 github中的示例项目，并对三种产品进行了一些性能测试。

If someone is interested there's a sample project on github with some test for performances comparing the three products.

这篇关于C＃将csv转换为xls（使用现有的csv文件）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！