c# - 将 xlxs 转换为 csv 和数据格式

Question

转换文件时遇到两个问题：

1. 我希望日期格式如下所示：

19.08.2019

它看起来像这样

8/19/2019

2.转换后，在csv文件中添加了带逗号的附加行。我该如何克服呢？

11,900011,S1,8/19/2019,11,6.90,9.90,,18.50,,8.80,,,,,,,,,,,,,,,,,,,,,,,,
12,900012,S1,8/19/2019,12,6.70,8.80,,14.50,,9.40,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
....

我使用图书馆

using Excel=Microsoft.Office.Interop.Excel;

有我的代码：

 public static void Convert()
        {
            try
            {
                Excel.Application app = new Excel.Application();
                //Load file . xlsx
                Excel.Workbook wb = app.Workbooks.Open(Program.filePaths[1]);
                //Save file .csv
                wb.SaveAs(Program.filePaths[0], Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, false, Type.Missing, Type.Missing, Type.Missing);
                wb.Close(false);
                app.Quit();


            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }


        }

预先感谢您的帮助

标签： c#.netexcelsaveconverters

Answer 1

我使用 npoi npoi从 excel 转换为 csv （对不起，因为那里丢失了字符串，这是我项目的副本）

        public Dictionary<string, string> ExceltoCsv(IWorkbook input)
    {
        var csvTrennzeichen = OutputSettings.ColumnSeparator.ToString();
        var result = new Dictionary<string, string>();
        for (var sheetIndex = 0; sheetIndex < input.NumberOfSheets; sheetIndex++)
        {
            var sheet = input.GetSheetAt(sheetIndex);
            var sheetresult = new List<string>();
            for (var row = sheet.FirstRowNum; row < sheet.LastRowNum; row++)
            {
                var rowObj = sheet.GetRow(row);
                if (rowObj.Cells.All(x => string.IsNullOrEmpty(WertAuslesen(x))))
                    continue;

                var line = string.Join(csvTrennzeichen, rowObj.Cells
                                                        .Select(cell => WertAuslesen(cell).Replace("\r", " ").Replace("\n", " "))
                                                        .Select(cell => OutputSettings.Writeinquotes ? string.Format("\"{0}\"", cell.Replace("\"", "\"\"")) : cell));

                sheetresult.Add(line);
            }

            result.Add(sheet.SheetName, string.Join("\r\n", sheetresult));
        }
        return result;
    }

    private string WertAuslesen(ICell oldCell)
    {
        switch (oldCell.CellType)
        {
            case CellType.Boolean:
                return oldCell.BooleanCellValue.ToString();
            case CellType.Error:
                return oldCell.ErrorCellValue.ToString();
            case CellType.Formula:
                return oldCell.CellFormula;
            case CellType.Numeric:
                return !DateUtil.IsCellDateFormatted(oldCell)
                    ? oldCell.NumericCellValue.ToString(OutputSettings.GetDecimalFormat(Digits(oldCell.CellStyle.GetDataFormatString())))
                    : oldCell.DateCellValue.ToString(OutputSettings.DateFormat);
            case CellType.String:
                return oldCell.RichStringCellValue.ToString();
            case CellType.Unknown:
                return oldCell.StringCellValue;
            default:
                return "";
        }
    }

    private static int Digits(string format)
    {
        var digits = format.ContainsAny(',', '.') ? format.Split(new[] { ',', '.' }).Last() : "";
        return digits.Length;
    }

我也觉得有必要添加 outputsettings 类，因为它可以解决问题，但它不是必要的

    public class OutputSettings
{
    public static readonly OutputSettings Default = new OutputSettings(Encoding.UTF8, null, "yyyyMMdd", "hh:mm:ss", ".", "", "y", "n", ',', true, "", null);
    //I am immutable
    public OutputSettings(CultureInfo culture) : this(
        Encoding.UTF8,
        null,
        culture.DateTimeFormat.ShortDatePattern,
        culture.DateTimeFormat.LongTimePattern,
        culture.NumberFormat.NumberDecimalSeparator,
        culture.NumberFormat.NumberGroupSeparator,
        "y",
        "n",
        ',',
        true,
        "",
        null)
    {
    }

    public OutputSettings(
        Encoding encoding,
        Version ioVersion,
        string dateFormat,
        string timeFormat,
        string decimalSeperator,
        string thousandSeperator,
        string yesString,
        string noString,
        char columnseperator,
        bool writeinquotes,
        string outputFolder,
        IResourceHandler resourceHandler)
    {
        Encoding = encoding;
        IOVersion = ioVersion;
        DateFormat = dateFormat;
        TimeFormat = timeFormat;
        DecimalSeperator = decimalSeperator;
        ThousandSeperator = thousandSeperator;
        YesString = yesString;
        NoString = noString;
        ColumnSeparator = columnseperator;
        Writeinquotes = writeinquotes;
        OutputFolder = outputFolder;
        ResourceHandler = resourceHandler;
    }

    public IResourceHandler ResourceHandler { get; }

    public Encoding Encoding { get; }

    public Version IOVersion { get; }

    public string DateFormat { get; }

    public string TimeFormat { get; }

    public string DateTimeFormat => DateFormat + " " + TimeFormat;

    public string DecimalSeperator { get; }

    public string ThousandSeperator { get; }

    public string DecimalFormat => GetDecimalFormat(2);

    public string YesString { get; }

    public string NoString { get; }

    private char _columnseperator;
    public char ColumnSeparator
    {
        get
        {
            return _columnseperator;
        }
        private set
        {
            if (value != ',' && value != ';')
                throw new ArgumentException(Localization.Resources.StaticTranslationResource.IO_SEPARATOR_MUSS_COMMA_ODER_SEMICOLON_SEIN);
            _columnseperator = value;
        }
    }

    public bool Writeinquotes { get; }
    public string OutputFolder { get; set; }

    public string GetDecimalFormat(int precision)
    {
        if (precision < 0)
            throw new ArgumentException(Localization.Resources.StaticTranslationResource.OUTPUT_ANZAHL_DER_STELLEN_DARF_NICHT_NEGATIV_SEIN, nameof(precision));

        var sb = new StringBuilder($"#{ThousandSeperator}##0{DecimalSeperator}");
        if (precision == 0)
        {
            sb.Append('#');
        }
        else
        {
            for (int i = 0; i < precision; i++)
            {
                sb.Append('0');
            }
        }
        return sb.ToString();
    }
}

编辑：我使用了很多扩展方法来使我的代码可读包含其中之一

        public static bool ContainsAll<T>(this IEnumerable<T> superset, params T[] subset) => !subset.Except(superset).Any();

    public static bool ContainsAll<T>(this IEnumerable<T> superset, IEnumerable<T> subset) => !subset.Except(superset).Any();

    public static bool ContainsAny<T>(this IEnumerable<T> superset, params T[] subset) => subset.Any(superset.Contains);

    public static bool ContainsAny<T>(this IEnumerable<T> superset, IEnumerable<T> subset) => subset.Any(superset.Contains);