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

问题描述

我相信这里有很多这件事情的讨论。
，但我读了所有职务，并尝试，但它从来没有使用C＃工作。
我的目标很简单，我有现有的CSV文件。
只想转换EXEL文件和done。
很多人说用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.

的Converting 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互操作是不是最好的解决方案，特别是如果你打算在运行代码<一个HREF =http://support.microsoft.com/kb/257757>服务器环境。

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

Microsoft不当前建议，并且不支持，从任何无人值守，
非交互式客户端应用程序或组件Microsoft Office应用程序
自动化（包括ASP，
ASP.NET，DCOM和NT服务） ，因为办公室可以表现出当处在该环境中运行不稳定
的行为和/或死锁。

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.

我从这里。

一些更多的样本，可以发现的这里

Some more samples can be found here.

更新：

另一种选择是读取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屋！